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:
CREATE TABLESPACE perfstat
DATAFILE 'C:\oracle1\oradata\SHAAN\perfstat01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT ONLINE;
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
@C:\oracle1\rdbms\admin\spdrop
To analyze statspack Report:
http://www.statspackanalyzer.com/analyze090630.asp
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
Conclusion:
§ 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
| |
Event
|
Percentage of Total Timed Events
|
control file sequential read
|
43.80%
|
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
|
10.83%
|
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
|
10.79%
|
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
|
10.38%
|
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
|
9.35%
|
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:
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.
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.
Comments
Post a Comment