Friday, September 29, 2006

Lab: Using Statspack Under Oracle XE



1. Install - Run the create scripts (under Oracle 10g XE version)

SQL> connect / as sysdba
SQL> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>spcreate.sql

Notes: prompted for enter password (oracle), the default tablespace SYSAUX
was chosed, the temporary tablespace was also chosen as TEMP

2. Adjusting the STATSPACK Collection Level

STATSPACK has two types of collection options: level and threshold.
level - controls the type of data collected from Oracle
threshold - as a filter for the collection of SQL statements into
the stats$sql_summary table

To show the description of different level, issue the following statement:
SQL> select * from stats$level_description order by snap_level;

To change the level of a snapshot, you can use statspack.snap function.
The i_modify_parameter=> 'true' changes the level permanent for all
snapshots in the future. e.g.
SQL> exec statspace.snap(i_snap_level => 6, i_modify_parameter => 'true');

3. Create, View and Delete Snapshots


SQL> connect perfstat/oracle
Connected.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.


SQL> select name, snap_id, to_char(snap_time, 'MM-DD-YYYY:HH24:MI:SS')
2 "Date/Time" from stats$snapshot, v$database;

NAME SNAP_ID Date/Time
--------- ---------- -------------------
XE 1 09-29-2006:15:03:40
XE 2 09-29-2006:15:04:31
XE 11 09-29-2006:15:06:09



To delete
SQL> @?\rdbms\admin\sppurge;

Notes: prompted to enter low and high snapshot ID


4. Create the Report


SQL> @?\rdbms\admin\spreport.sql

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11
Begin Snapshot Id specified: 11

Enter value for end_snap: 21
End Snapshot Id specified: 21

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_21. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:


5. Statspack at a Glance - understading the report



5.1. Statspack Report Header

STATSPACK report for

Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
2481462586 xe 1 29-Sep-06 14:10 10.2.0.1.0 NO

Host Name: NTBK Num CPUs: 1 Phys Memory (MB): 494
~~~~

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 11 29-Sep-06 15:06:09 16 5.4
End Snap: 21 29-Sep-06 15:14:59 16 3.8
Elapsed: 8.83 (mins)

Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 72M Std Block Size: 8K
Shared Pool Size: 52M Log Buffer: 2,792K


Notes:
Curs/Sess column - shows the number of open cursors per session.
the item we are most interested in is the elapsed time. We want that to be
large enough to be meaningful, but small enough to be relevant (15 to 30
minutes is OK).

5.2 Statspack Load Profile

Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 5,410.54 716,896.00
Logical reads: 71.40 9,460.25
Block changes: 33.27 4,408.75
Physical reads: 0.03 4.50
Physical writes: 4.69 621.25
User calls: 0.21 28.25
Parses: 6.08 805.50
Hard parses: 0.86 114.50
Sorts: 3.84 509.25
Logons: 0.01 0.75
Executes: 13.62 1,804.00
Transactions: 0.01

% Blocks changed per Read: 46.60 Recursive Call %: 99.88
Rollback per transaction %: 0.00 Rows per Sort: 6.65

Notes:
Three items are important:
- The Hard parses (we want very few of them)
- Executes (how many statements we are executing per second/transaction
- Transactions (how many transactions per second we process).

This gives an overall view of the load on the server.
a fairly light system load (1-4 transactions per second is low).

5.3 Statspack Instance Efficiency Percentage

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.95 In-memory Sort %: 100.00
Library Hit %: 86.15 Soft Parse %: 85.79
Execute to Parse %: 55.35 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 77.17 % Non-Parse CPU: 68.03

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 77.74 79.31
% SQL with executions>1: 91.71 70.29
% Memory for SQL w/exec>1: 98.44 90.15


Notes:

Most important: Library Hit, Soft Parse and Execute to Parse. All of these
have to do with how well the shared pool is being utilized.

If the library Hit ration was low, it could be indicative of a shared pool
that is too small, or just as likely, that the system did not make correct
use of bind variables in the applicaiton

OLTP System
The Soft Parase % value is one of the most important ratio in the database.
For a typical OLTP system, it should be as near to 100% as possible. You
quite simply do not hard parse after the database has been up for a while
in your typical transactional / general-purpose database. The way you
achieve that is with bind variables.

Data Warehouse
In a data warehouse, we would like to generally see the Soft Parse ratio
lower. We don't necessarily want to use bind variables in a data warehouse.
This is because they typically use materialized views, hustograms, and
other things that are easily thwarted by bind variables. In a data warehouse,
we may have many seconds between executions, so hard parsing is not evil;
in fact, it is good in those environments


5.4 Statspack Top 5 Timed Events

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write 487 7 15 45.7
CPU time 6 37.4
log file parallel write 116 1 9 6.5
control file parallel write 177 1 4 4.8
control file sequential read 247 1 3 3.8
-------------------------------------------------------------


Notes:
CPU time - the sum of the CPU used by this session, or the amount of CPU
time used during the snapshot window. In a heavily loaded system,
if the CPU time event is the biggest event, that could point to
some CPU-intensive processing

5.5 SQL ordered by Gets

Here you will find the most CPU-Time consuming SQL statements

No comments: