BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








STATSPACK Collection Options

Oracle Tips by Burleson Consulting

(1)Adjusting the STATSPACK Collection Thresholds

As we have already mentioned, STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table.

(2)Snapshot Levels

There are three snapshot levels used in STATSPACK, and level 5 is the default:

·        Level 0: General Performance StatisticsThis level collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics.

·        Level 5: Add SQL StatementsThis level includes all level 0 statistics plus SQL statements into the stats$sql_summary table.

·        Level 10: Add Child Latch StatisticsThe level 10 snapshot includes everything in the level 5 statistics plus the addition of child latches into the stats$latch_children table. You rarely, if ever, need this level of detail, and you should only do a level 10 snapshot when directed by Oracle technical support.

The default for a snapshot is always level 5, but you can use the STATSPACK package to change the default or request a special snapshot at a different level. You can change the default level of a snapshot with the statspack.snap function. In the example here, we remove all SQL collection from all future snapshots by specifying a default level 1 snapshot:

L 3-13

     SQL>  execute statspack.snap -
           (i_snap_level=>0, i_modify_parameter=>'true');

Once set, all future snapshots will be level 0. If you want a single snapshot at a different level without changing the default, you just omit the i_modify_parameter argument. In the next example, we take a single snapshot at level 10, while leaving the default at level 5 for all subsequent snapshots:

L 3-14

     SQL>  execute statspack.snap -

(2)Snapshot Thresholds

The snapshot thresholds only apply to the SQL statements that are captured in the stats$sql_summary table. The stats$sql_summary table can easily become the largest tables in the STATSPACK schema because each snapshot might collect several hundred rows, one for each SQL statement that was in the library cache at the time of the snapshot.

The thresholds are stored in the stats$statspack_parameter table. Let's take
a look at each threshold:

·        executions_thThis is the number of executions of the SQL statement (default is 100).

·        disk_reads_thThis is the number of disk reads performed by the SQL statement (default is 1,000).

·        parse_calls_thThis is the number of parse calls performed by the SQL statement (default is 1,000).

·        buffer_gets_thThis is the number of buffer gets performed by the SQL statement (default is 10,000).

(3)New Oracle9i SQL thresholds

In Oracle9i there are new threshold parameters, but they have not yet been activated in STATSPACK.

·        p_def_num_sql – This is a threshold for the number of SQL statements.

·        p_def_executions_th – This is a threshold for the number of SQL executions.

·        p_def_parse_calls_th  – This is a threshold for the number of SQL parse calls.

·        p_def_disk_reads_th    – This is a threshold for the number of disk reads.

·        p_def_buffer_gets_th  – This is a threshold for the number of SQL buffer gets.

·        p_def_sharable_mem_th  – This is a threshold for the SQL sharable memory.

·        p_def_version_count_th   – This is a threshold for the SQL child cursors.

It is important to understand that each SQL statement will be evaluated against all of these thresholds, and the SQL statement will be included in the stats$sql_summary table if any one of the thresholds is exceeded. In other words, these thresholds are not AND'ed together as we might expect, but they are OR'ed together such that any value exceeding any of the thresholds will cause a row to be populated.

The main purpose of these thresholds is to control the rapid growth of the stats$sql_summary table that will occur when a highly active database has hundreds of SQL statements in the library cache. In the next chapter, we will be discussing clean-up strategies for removing unwanted snapshots from the database.

You can change the threshold defaults by calling the statspack.modify_statspack_ parameter function. In the example here, we change the default threshold for buffer_gets and disk_reads to 100,000. In all subsequent snapshots, we will only see SQL that exceeds 100,000 buffer gets or disk reads.

L 3-15

     SQL>  execute statspack.modify_statspack_parameter -
            (i_buffer_gets_th=>100000, i_disk_reads_th=>100000);

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter