Initial use of statspack

3 statspack management and adjustment
Oracle also provides a system script used to truncate the statistical information tables. The Script Name Is sptrunc. SQL (8i and 9i are the same)
Execute statspack. modify_statspack_parameter-
(I _snap_level => 10 ,-
I _buffer_gets_th = & gt; 10000 ,-
I _disk_reads_th => 1000 );

Sppurge. SQL-Purge a range of snapshot ID's between the specified begin and end snap ID's
Sptrunc. SQL-truncates all data in statspack tables

You can use the dbms_ijob package to delete jobs of other users.

Configure statspack Parameters

-Snapshot level

Statspack can change the number of statistics collected by setting the level. The following information is collected at various levels:

Levels = 0 General Performance Statistics
Statistics:
At this level and higher, general performance statistics will be collected, such as waiting, system events, system statistics, rollback segment data, SGA, background events, session events, locks, and buffer pool statistics, parent latch statistics.

Levels = 5 added: SQL statement
It contains all low-level statistics and SQL statement information with high resource usage.

SQL 'threshoals'
The SQL statement counts based on the predefined thresholds:
-Number of executions of the SQL statement (default 100)
-Number of disk reads saved med by the SQL statement (default 1,000)
-Number of parse cballs stored med by the SQL statement (default 1,000)
-Number of buffer gets stored med by the SQL statement (default 10,000)

When the resource used by the SQL statement exceeds any of the above thresholds, it will be counted into the snapshot.
The SQL statement threshold uses the records in the stats $ statspack_parameter table or the parameters entered when the snapshot is executed.

Levels = 6
The execution plan and usage information of top SQL statements are higher than the previous one. Applicable to changes in execution plans. To collect all the SQL statements in the Shared Pool, set the threshold value to 0.

Level = 7
Levels> = 7 increase: Segment-level statistics
With the segment statistics, you can know which segments are frequently used, adjust the physical layout of the segments, and optimize the I/O load. For the RAC environment, you can find hotspot objects for instance contention.
Level 7 includes the following segment statistics:

Logical reads
Db block changes
Physical reads
Physical writes
Physical reads direct
Physical writes direct
Global cache consistent read blocks served (RAC specific)
Global cache Current Blocks served (RAC specific)
Buffer busy waits
ITL waits
Row lock waits

Levels = 10 added: Child latches
The most comprehensive and time-consuming information collected at this level is generally not recommended unless the child latch information is required.

-Snapshot SQL thresholds

There are other parameters which can be configured in addition to the level.
These parameters are used as thresholds when collecting SQL statements;
If any SQL statements breach the threshold, these are the statements which
Are captured during the snapshot.

Snapshot level and threshold information used by the package is stored
In the stats $ statspack_parameter table.

-Changing the default values for snapshot level and SQL thresholds

The default parameters used for taking snapshots can be adjusted/modified so
That they better capture data about an instance's workload.

This can be done either:

O taking a snapshot, and specifying the new defaults to be saved to
Database (using statspack. Snap, and using the I _modify_parameter
Input variable ).

SQL> execute statspack. Snap-
(I _snap_level => 10, I _modify_parameter => 'true ');

Setting the I _modify_parameter value to true will save the new
Thresholds in the stats $ statspack_parameter table; these thresholds
Will be used for all subsequent snapshots.

If the I _modify_parameter was false or omitted, the snapshot taken
That point will use the specified values, any subsequent snapshots
Use the preexisting values in the stats $ statspack_parameter table.

O changing the defaults immediately without taking a snapshot, using
Statspack. modify_statspack_parameter procedure. For example to change
The Snapshot level to 10, and the SQL thresholds for buffer_gets and
Disk_reads, the following statement can be issued:

SQL> execute statspack. modify_statspack_parameter-
(I _snap_level => 10, I _buffer_gets_th => 10000, I _disk_reads_th => 1000 );

This procedure changes the values permananently, but does not
Take a snapshot.

The full list of parameters which can be passed into
Modify_statspack_parameter procedure are the same as those
The snap procedure.

-Specifying a session ID

If session statistics are needed for a special session, it is possible
Specify the session ID in the call to statspack. The statistics gathered
The session will include session statistics, session events and lock activity.
The default behavior is not to gather session level statistics.

SQL> execute statspack. Snap (I _session_id => 3 );

How to automatically gather statspack snapshots:
----------------

To be able to make comparisons of performance from one day, week or year
The next, there must be multiple snapshots taken over a period of time.
Minimum of two snapshots are required before any performance characteristics
The application and database can be made.

The best method to gather snapshots is to automate the collection on
Regular time interval. It is possible to do this:

-Within the database, using the Oracle dbms_job procedure to schedule
Snapshots

-Using operating system utilities (such as 'cron' on Unix or 'at' on NT)
Schedule the snapshot. Please contact the system administrator for more
Information about using the OS utilities for automating this data collection.

-Scheduling statspack snapshots using dbms_job package

To use an oracle-automated method for collecting statistics, you can use
Dbms_job. A sample script on how to do this is supplied in spauto. SQL,
Which schedules a snapshot every hour, on the hour.

In order to use dbms_job to schedule snapshots, The job_queue_processes
Initialization parameter must be set to greater than 0 in the configuration
File used to start the instance for the job to be run automatically.

Example of an init. ora entry:
# Set to enable the Job Queue process to start. This allows dbms_job
# To schedule automatic statistics collection using statspack
Job_queue_processes = 1

If using statsauto. SQL in Ops environment, the statsauto. SQL script must be
Run Once on each instance in the cluster. Similarly, the job_queue_processes
Parameter must also be set for each instance.

Changing the interval of statistics collection
----------------
To change the interval of Statistics Collection Use the dbms_job.interval
Procedure

E.g.
Execute dbms_job.interval (, 'sysdate + (1/48 )');

Where 'sysdate + (1/48) 'will result in the statistics being gathered each 1/48
Hours (I. e. Every half hour ).

To force the job to run immediately,
Execute dbms_job.run ();

To remove the autocollect job,
Execute dbms_job.remove ();

For more information on dbms_job, see the supplied packages reference manual.

To gather a statspack report:
====================================

SQL> connect perfstat/perfstat
SQL> @? /Rdbms/admin/spreport

You will be prompted:
1. The beginning snapshot ID
2. The ending snapshot ID
3. The name of the report text file to be created