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,
pressto 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
Everything Changes
1 week ago
