Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 10 April 2012

Using Statspack Report in oracle 9i

Statspack is a set of performance monitoring and reporting utilities. It also supports application tuning activities by providing data which identifies high-load SQL statements. STATSPACK can be used both proactively to monitor the changing load on a system, and also reactively to investigate a performance problem.

Create PERFSTAT Tablespace:
     DATAFILE 'C:\oracle1\oradata\SHAAN\perfstat01.dbf' SIZE 1000M REUSE

To Install statspack:
sqlplus / as sysdba
@C:\oracle1\rdbms\admin\spcreate.sql   -- C:\oracle1 is our Oracle-Home

When you run the Statspack installation script, the PERFSTAT user is created automatically. PERFSTAT owns all objects needed by the Statspack package. Here at the time of PERFSTAT user creation you have to provide password for user and default and temporary tablespace name.

To take a snapshot manually:
sqlplus perfstat/perfstat
exec statspack.snap;

Adjusting the STATSPACK Collection Level:
SELECT * FROM stats$level_description ORDER BY snap_level;
exec statspack.snap(i_snap_level => 6, i_modify_parameter => 'true');

To schedule an hourly snapshot:
sqlplus perfstat/perfstat
@C:\oracle1\rdbms\admin\spauto.sql   --automated report

To Create statspack report:
sqlplus perfstat/perfstat
@C:\oracle1\rdbms\admin\spreport.sql  -- General Report
@C:\oracle1\rdbms\admin\SPREPINS.SQL  --with the database and instance specified
@C:\oracle1\rdbms\admin\SPREPSQL.SQL  --with specific SQL hash value.

Note: you can find your report file oracle-home\bin with the report name you have given.

To delete snapshots:
sqlplus perfstat/perfstat
@C:\oracle1\rdbms\admin\sppurge;   --limited range of snap
@C:\oracle1\rdbms\admin\sptrunc.sql   --Truncate all

To list all generated snapshots:
sqlplus perfstat/perfstat
select snap_id, snap_level, to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Snap_Time"
from stats$snapshot, v$database order by snap_id;
select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot,v$database;

To uninstall statspack
sqlplus / as sysdba

To analyze statspack Report:
Just open this link and paste your report file contents here click on submit and continue button.
then the result will display........Now take necessary decision to tune the effected parameters...

Load Profile Section:
Redo size: This is the amount of redo generated during this report.
Logical Reads: This is calculated as Consistent Gets + DB BlockGets = Logical Reads
Block changes: The number of blocks modified during the sample interval
Physical Reads: The number of requests for a block that caused aphysical I/O.
Physical Writes: The number of physical writes issued.
User Calls: The number of queries generated
Parses: Total of all parses: both hard and soft
Hard Parses: Those parses requiring a completely new parse of the SQL statement. A ‘hard parse’ rate of
greater than 100 per second indicates there is a very high amount of hard parsing on the system. High hard parse rates cause serious performance issues, and must be investigated. A high hard parse rate is usually accompanied by latch contention on the shared pool and library cache latches. Check whether waits for ‘latch free’ appear in the top-5 wait events, and if so, examine the latching sections of the Statspack report.
Soft Parses: Not listed but derived by subtracting the hard parses from parses. A high soft parse rate could be anywhere in the rate of 300 or more per second. Unnecessary soft parses also limit application scalability; optimally a SQL statement should be soft-parsed once per session, and executed many times.
Executes: How many statements we are executing per second /transaction.
Transactions: How many transactions per second we process

§         A significant increase in ‘redo size’, ‘block changes’ and ‘pct of blocks changed per read’ would indicate the instance is performing more inserts/updates/deletes.
§         An increase in the ‘redo size’ without an increase in the number of ‘transactions per second’ would indicate a changing transaction profile.

Instance Efficiency Percentages Section:
Execute to Parse: If value is negative, it means that the number of parses is larger than the number of executions. Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed. This is another form of thrashing which also degrades performance tremendously. This is very BAD!!

Buffer Nowait Ratio: This ratio relates to requests that a server process makes for a specific buffer; it gives the percentage of those requests in which the requested buffer is immediately available. All buffer types are included in this statistic. If the ratio is low, check the Buffer Wait Statistics section of there port for more detail on which type of block is being contended for.

Buffer Hit Ratio: This ratio gives the percentage of block requests that were satisfied within the cache without requiring physical I/O. Normally (99%) indicates that the cache is adequately sized but this assumption may not always valid. Sometimes you can identify a too-small buffer cache by the appearance of the write complete waits event, which indicates that hot blocks (that is, blocks that are still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event.

Library Hit Ratio: It gives the percentage of pin requests that result in pin hits. A pin hit occurs when the SQL or PL/SQL code to be executed is already in the library cache and is valid to execute. If the "Library Hit ratio" is low, it could be indicative of a shared pool that is too small (SQL is prematurely aging out), or just as likely, that the system did not make correct use of bind variables in the application. If the soft parse ratio is also low, check whether there's a parsing issue.

Redo Nowait Ratio: This ratio indicates the amount of redo entries generated for which there was space available in the redo log. The percentage is calculated as follows:
100 x (1- (redo-log space requests/redo entries)). This value closer to 100% indicates minimal time spent waiting for redo logs become either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up. If your alert log shows that you are switching logs frequently (that is, more than once every 15minutes), you may be able to reduce the amount of switching by increasing the size of the online redologs. If the log switches are not frequent, check the disks on which the redo logs reside to see why these witches are not happening quickly. If these disks are not overloaded, they may be slow, which means you could put the files on faster disks.

In-Memory Sort Ratio: This ratio gives the percentage of sorts that were performed in memory, rather than requiring a disk-sort segment to complete the sort. Optimally, in an OLTP environment, this ratio should be high. Setting the PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE) initialization parameter effectively will eliminate this problem.

Soft parse ration: A soft parse occurs when a session attempt to execute an SQL statement is already in the shared pool on the other hand a hard parse occurs when the current SQL statement is either not in the shared pool or not there is a shareable form. Ideally, the soft parse ratio should be greater than 95% when the soft parse ratio falls much below 80% then investigate whether you can share SQL by using bind variables. Another possible reason for low soft parse ration could be non-CPU-related parse time was spent waiting for latches, which might indicate a parsing or latching problem. The Soft Parse % 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.

Latch Hit Ratio: This is the ratio of the total number of latch misses to the number of latch gets for all latches. A low value for this ratio indicates a latching problem, whereas a high value is generally good. Cross-check this value with the Top 5 Wait Events and refer the latch section of the report. Latch Hit % of less than 99 percent is
usually a big problem. Also check the "Shared Pool Statistics", if the "End" value is in the high 95%-100% range, this is a indication that the shared pool needs to be increased (especially if the "Begin" value is much smaller).

Top 5 Time Event Section:
This section is among the most important and relevant sections in the Statspack report. Here actually you will find out what events (typically wait events) are consuming the most time. 

Top 5 Events

Percentage of Total Timed Events
control file sequential read
The control file sequential read Oracle metric indicates the process is waiting for blocks to be read from a control file.
This happens in many cases. For example, you can see a "control file sequential read" when you are making a backup of the controlfiles, sharing information (between instances) from the controlfile, reading other blocks from the controlfiles, and most importantly, when you are reading the header block for a data file.
db file parallel write
The DBWR process produces this wait event as it writes dirty blocks to the datafiles. This event can cause poor read performance, and the writes may interfere with reads from the data files.
Moving the tables that are experiencing the highest write activity to solid state disks may help to alleviate this wait event.
db file sequential read
The sequential read event occurs when Oracle reads single blocks of a table or index. Look at the tablespace IO section of the report for tablespaces with less than 2 average blocks per read, high response time, and a large percentage of the total IO. Improving the response time of these tables with faster storage will help reduce this wait event and speed up the database.
Moving the data files with the largest amount of time spend waiting on single-block reads to faster storage can significantly reduce the amount of time spent waiting on this event. By reducing the time spent waiting on this event, the database performance could increase 12%.
log file parallel write
This event occurs when Oracle is waiting for the completion of writes to the redo log files.
Moving some or all copies of your redo logs logs to the WriteAccelerator can reduce the amount of time spent waiting for this event.
log file sync
This event is caused by waiting for the LGWR to post after a session performs a commit. This can be tuned by reducing the number of commits.
Moving some or all copies of your redo logs logs onto the WriteAccelerator can reduce the amount of time spent waiting for this event.

A critical activity in Database Performance Tuning is Response Time Analysis. This consists of finding out where time is being spent in a database.
Response Time = Service Time + Wait Time
"Service Time" is measured using the statistic CPU used by this session and "Wait Time" is measured by summing up time spent on Wait Events. For Example of above Top 5 wait Event.

Enqueue Wait: This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode.
Identification and Recommendations: Enqueue waits and their types can be identified by looking at the “Enqueue activity” section of the Statspack report. For the WLI application, enqueue waits are primarily found for indexed monotonic keys and data block access on the WLI_PROCESS_INSTANCE_INFO table. Enqueue waits can be reduced on these objects by using reverse-key indexes and by partitioning the WLI_PROCESS_INSTANCE_INFO table.

Log File Sync: When a user session COMMITs (or rolls back), session REDO information needs to be flushed to the REDO log file. The user session will post the log writer (LGWR) to write all REDO information required from the log buffer to the REDO log file. When the LGWR has finished, it posts the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all the REDO changes are safely on disk.
Identification and Recommendations: Waits on log file sync can be identified by looking at the “Top 5 Timed Events” or “Wait Events” section of the Statspack report. These waits can be reduced by moving log files to the faster disks or by reducing COMMIT frequency by performing batch transactions.

Buffer Busy Waits: Buffer busy waits happen when a session needs to access a database block in the buffer cache but cannot, because the buffer is “busy”. The two main cases where this can occur are:
- Another session is reading the block into the buffer.
- Another session holds the buffer in an incompatible mode to this request.
Identification and Recommendations: Segments with high buffer busy waits can be identified by looking in the “Top 5 Buffer Busy Waits per Segment” section of the Statspack report. Buffer busy waits can be reduced by using reverse-key indexes for busy indexes and by partitioning busy tables.

Log File Parallel Writes: Log file parallel write waits occur when waiting for writes of REDO records to the REDO log files to complete. The wait occurs in log writer (LGWR) as part of normal activity of copying records from the REDO log buffer to the current online log. The actual wait time is the time taken for all the outstanding I/O requests to complete.
Identification and Recommendations: Waits for log file parallel writes can be identified by looking at the “Top 5 Timed Events” or “Wait Events” section of the Statspack report. Log file parallel write waits can be reduced by moving log files to the faster disks and/or separate disks where there will be less contention.

DB File Sequential Reads
DB file sequential read waits signify a wait for an I/O read request to complete. If the time spent waiting for reads is significant, then it can be helpful to determine which segments Oracle is performing the reads against.
Identification and Recommendations: Segments that are excessive on reads can be identified by looking at the “Top 5 Physical Reads per Segment” and “SQL ordered by Reads” sections of the Statspack report. Block reads are fairly inevitable so the aim should be to minimize unnecessary I/O. I/O for sequential reads can be reduced by tuning SQL calls that result in full table scans and using the partitioning option for large tables.

Some of the General Approach to handle the I/O Problem:
§         Reduce the I/O requirements of the database by tuning SQL.
§         Reduce the I/O requirements of the database by tuning instance parameters such as using memory cache to limit I/O, Tuning the Size of multiblock I/O.
§         Balancing the database I/O by usage of Striping, RAID, SAN or NAS.
§         Redistribute database I/O by manual placement of database files across different filesystems,controllers and physical devices.

The below query will show the biggest contributors of total wait time:
select event, total_waits,time_waited from V$system_event
where event NOT IN ('pmon timer', 'smon timer', 'rdbms ipc reply', 'parallel dequewait',
'virtual circuit', '%SQL*Net%', 'client message', 'NULL event')order by time_waited desc;

DB File Scattered Reads: DB file scattered read waits happens when a session is waiting for a multi-block I/O to complete. This typically occurs during full table scans or index fast full scans.
Identification and Recommendations: Segments that are excessive on reads can be identified by looking at the “Top 5 Physical Reads per Segment” and “SQL ordered by Reads” sections of the Statspack report. Ideally, applications should not repeatedly perform full table scans of the online portions of application data when there is a faster and more selective way to retrieve the data. Query tuning should be used to optimize online SQL to use indexes.

Buffer Hit Ratio: The buffer hit ratio metric shows how often processes are finding data blocks in memory vs. retrieving them from disk.
Identification and Recommendations: Buffer hit ratio can be found in the “Instance Efficiency Percentages” section for the Statspack report. The exact value of the buffer hit ratio is of less importance than the ability to monitor it over time and notice any significant changes in the profile of activity on the database. If the ratio falls below 80%, then more memory should be allocated to the database by increasing the value of the DB_CACHE_SIZE parameter.
In some cases, the ratio can be low due to poorly performing SQL statements. In this case, the buffer hit ratio may not increase after increasing DB_CACHE_SIZE. These SQL statements should be tuned to avoid excessive physical I/O.

Row Lock Waits: Row lock waits occur when a process requests an incompatible lock for a row that is currently locked by another process. These lock waits can usually be attributed to high volume inserts on a table with a primary key index.
Identification and Recommendations:
Segments where performance suffers from excessive row lock waits can be identified in the “Top 5 Row Lock Waits per Segment” section of the Statspack report. These waits can be avoided by partitioning tables or by using reverse-key indexes. For WLI, these waits can be found on the WLI_PROCESS_INSTANCE_INFO table and on the primary key index of this table.

Library Hit Ratio: The library cache hit ratio indicates how often Oracle retrieves a parsed SQL or PL/SQL statement from the library cache. When an application makes a SQL or stored procedure call, Oracle checks the library cache to determine if a parsed version of the statement is already stored there. If the parsed statement is stored in the library cache, Oracle executes the statement immediately. If not, Oracle parses the statement and allocates a shared SQL area within the library cache for it. A low library cache hit ratio can result in additional parsing, which decreases performance and increases CPU consumption for the database.
Identification and Recommendations: The library hit ratio can be found in the “Instance Efficiency Percentages” section of the Statspack report. If this ratio falls below 80%, increasing the size of shared pool area can help. This can be done by changing the value of the SHARED_POOL_SIZE parameter

Web tools to analyze Reports

Good Links with more Information


  1. A debt of gratitude is in order Hadoop Training Chennai for sharing the information..i need to modifying more than bundle data for this data.

  2. Thanks for splitting your comprehension with us. It’s really useful to me & I hope it helps the people who in need of this vital information.
    Salesforce training institute in Chennai

  3. hai,i have to learned to lot of information about java Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with java.
    Java Training in Chennai

  4. hybernet is a framework Tool which helps in Functional and Regression testing of an application. If you are interested in hybernet training, our real time working.
    Hibernate Training in Chennai,

  5. Looking for real-time training institue.Get details now may if share this link visit
    Spring Training in chennai

  6. Nice site.... refer this site .if Our vision succes!Training are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with Realtime methodologies.
    Oracle Rac Training Chennai

  7. Job oriented form_reports training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    forms-reports Training in Chennai

  8. hai you have to learned to lot of information about c# .net Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with


  9. hai If you are interested in training, our real time working. Training in Chennai.

  10. Amazing blog if our training additional way as an silverlight training trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog

  11. Awesome Job oriented sharepoint training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    if you have more details visit this blog.


  12. if share valuable information about cloud computing training courses, certification, online resources, and private training for Developers, Administrators, and Data Analysts may visit

  13. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    QTP Training in Chennai

  14. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.
    sap fico Training in Chennai

  15. Great post and informative was awesome to read, thanks for sharing this great content to my vision.
    Informatica Training In Chennai
    Hadoop Training In Chennai
    Oracle Training In Chennai
    SAS Training In Chennai


  16. Thanks for posting useful information.You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...Really it was an awesome article...very interesting to read..
    please sharing like this information......
    Android training in chennai
    Ios training in chennai

  17. It's interesting that many of the bloggers your tips helped to clarify a few things for me as well as giving.. very specific nice content. And tell people specific ways to live their lives.Sometimes you just have to yell at people and give them a good shake to get your point across.
    Web Design Company
    Web Development Company
    Mobile App Development Company


  18. Really it was an awesome article...very interesting to read..You have provided an nice article....Thanks for sharing..
    Web Design Company
    Web Development Company

  19. Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving..
    Android App Development Company

  20. great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    web design Company
    web development Company
    web design Company in chennai
    web development Company in chennai
    web design Company in India
    web development Company in India

  21. it is really amazing...thanks for sharing....provide more useful information...
    Mobile app development company

  22. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.

    Fitness SMS
    Salon SMS
    Investor Relation SMS

  23. Nice it seems to be good post... It will get readers engagement on the article since readers engagement plays an vital role in every blog.i am expecting more updated posts from your hands.
    iOS App Development Company