WISE Analyzer Report - 1/28/2005 5:19:11 PM

WISE Analyzer Report

Version 1.0.25
Copyright (C) 2003 by Rampant TechPress. Visit us at http://www.ion-dba.com

Generated by registered version. Licensed to Rampant TechPress (1 Database License).

Processed STATSPACK Report File - sp_1_24.LST


Content

Summary Information

Instance Cache Information

Load Profile Information

Instance Efficiency Ratios

Instance Response Time Summary

Top 5 Wait Events

Foreground Wait Events

Background Wait Events

SQL Information

Instance Activity

I/O Activity

Buffer Cache Activity

Enqueue Activity

Undo(Rollback) Information

Latch Information

Top Segment Activity (Only 9.2.0)

Dictionary Cache Information

Library Cache Information

SGA Summary

INIT.ORA Summary


Introduction

The Oracle STATSPACK utility is a set of scripts which capture elapsed time statistics for over 100 performance metrics of Oracle instance and store the data in a special repository. Oracle STATSPACK utility generates report file using performance data stored in the STATSPACK repository. This formatted HTML report generated by WISE Analyzer utility is based on the report generated by Oracle STATSPACK. WISE Analyzer tool produces some database performance tuning tips and hints and gives you highlights of potential problem areas of database performance.

Also analysis of several STATSPACK report sections is missing. In order to get a fully functional version of WISE Analyzer utility, you have to register the software. Please visit our web site to find out more information on software licensing and pricing details.

Summary Information

The identification of the database on which the statspack report was run along with the time interval of the statspack report.

Database Information

DB Name DB Id Instance Inst Num Release OPS/RAC Host
ORASVS10 3215860515 orasvs10 1 10.1.0.2.0 NO svs

Report Snapshot Interval Information

... Snap Id Snap Time Sessions Curs/Sess Comment
Begin Snap: 1 04-Mar-04 16:45:45 17 5.2
End Snap: 24 05-Mar-04 10:51:09 18 8.6
Elapsed: 1,085.40 (mins)

[Content]

Instance Workload Information

This section describes the instance's workload profile and instance metrics that may help to determine the instance efficiency.

Cache Sizes Information

Cache Size
Buffer Cache292M
Shared Pool Size116M
Std Block Size8K
Log Buffer256K

Std Block size indicates the primary block size of the instance. Note that the buffer cache size is that of the standard buffer cache. If you have multiple buffer caches, you will need to calculate the others separately.


[Content]

Load Profile Information

... Per Second Per Transaction
Redo size: 1,562.33 16,770.27
Logical reads: 45.47 488.07
Block changes: 9.13 97.98
Physical reads: 0.31 3.37
Physical writes: 0.29 3.14
User calls: 0.10 1.03
Parses: 0.99 10.68
Hard parses: 0.04 0.46
Sorts: 1.14 12.23
Logons: 0.02 0.22
Executes: 5.37 57.66
Transactions: 0.09

% Blocks changed per Read: 20.08
Recursive Call %: 99.69
Rollback per transaction %: 2.37
Rows per Sort: 80.76

Statistic Descriptions

Redo size is the amount of redo generated during this report.
Logical Reads is calculated as Consistent Gets + DB Block Gets = Logical Reads.
Block changes is the number of blocks modified during the sample interval.
Physical Reads is the number of requests for a block that caused a physical I/O operation.
Physical Writes is number of physical writes performed.
User Calls is number of user queries generated.
Parses is the total of all parses: both hard and soft.
Hard Parses is the parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.
Soft Parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse and hence consumes far fewer resources.
Sorts, Logons, Executes and Transactions are all self explanatory.


[Content]

Instance Efficiency Ratios

Efficiency Percentages

RatioPercentage Description
Buffer Hit %99.31 Buffer Hit Ratio measures how many times a required block was found in memory rather than having to execute a expensive read operation on disk to get the block.
Buffer Nowait %100.00
Buffer Nowait % shows the percentage when data buffers were accessed directly without any wait time.
Library Hit %98.56
Library Hit % shows the percentage when SQL statements, PL/SQL packages were found in shared pool size..
Execute to Parse %81.48
Execute to Parse % shows how often your parsed SQL statements are reused without reparsing them.
Parse CPU to Parse Elapsd % 92.83
Parse CPU to Parse Elapsd % gives you the ratio of CPU time spent to parse SQL statements.
Redo NoWait %99.99
Redo NoWait % shows whether your redo log buffer has sufficient size.
In-memory Sort %100.00
In-memory Sort % shows you the percentage when sorts performed in memory instead of using temporary tablespaces.
Soft Parse %95.69
Soft Parse % shows you how often sessions issued a SQL statement which is already in the shared pool AND it can use an existing version of that statement.
Latch Hit %100.00
Latch Hit % shows how often latches were acquired without to have to wait for them.
% Non-Parse CPU87.29
% Non-Parse CPU shows the percentage of how much CPU resources were spent on actual SQL execution.

Hit ratios are calculations that may provide information regarding different structures and operations in the Oracle instance. Database tuning never must be driven by hit ratios. They only provide additional information to understand how the instance is operating. For example, in a DSS systems a low cache hit ratio may be acceptable due the amount of recycling needed due the large volume of data accesed. So if you increase the size of the buffer cache based on this number, the corrective action may not take affect and you may be wasting memory resources.

Shared Pool Statistics

Shared Pool Statistic Begin End
Memory Usage % 74.79 93.53
% SQL with executions>1 66.89 34.47
% Memory for SQL w/exec>1 55.06 32.55

Tuning Tips

Buffer Hit Ratio

Buffer Hit Ratio is OK.

Buffer NoWait Ratio

Buffer NoWait Ratio is OK.

Library Hit Ratio

Library Hit Ratio is OK.

Redo NoWait Ratio

Redo NoWait Ratio is OK.

In Memory Sort Ratio

In Memory Sort Ratio is OK.

Shared Pool Tuning Tips

  • Parse Once / Execute Many: By far the best approach to use in OLTP type applications is to parse a statement only once and hold the cursor open, executing it as required. This results in only the initial parse for each statement (either soft or hard). Obviously there will be some statements which are rarely executed and so maintaining an open cursor for them is a wasteful overhead. Note that a session only has OPEN_CURSORS cursors available and holding cursors open is likely to increase the total number of concurrently open cursors. In precompilers the HOLD_CURSOR parameter controls whether cursors are held open or not while in OCI developers have direct control over cursors.
  • Eliminating Literal SQL: If you have an existing application it is unlikely that you could eliminate all literal SQL but you should be prepared to eliminate some if it is causing problems. By looking at the V$SQLAREA view it is possible to see which literal statements are good candidates for converting to use bind variables. The following query shows SQL in the SGA where there are a large number of similar statements:

    SELECT substr(sql_text,1,40) "SQL",count(*) , sum(executions) "TotExecs" FROM v$sqlarea WHERE executions < 5 GROUP BY substr(sql_text,1,40) HAVING count(*) > 30 ORDER BY 2;
  • Avoid Invalidations: Some specific orders will change the state of cursors to INVALIDATE. These orders modify directly the context of related objects associated with cursors. That is orders are TRUNCATE, ANALYZE or DBMS_STATS.GATHER_XXX on tables or indexes, grants changes on underlying objects. The associated cursors will stay in the SQLAREA but when it will be reference next time, it should be reloaded and reparsed fully, so the global performance will be impacted. The following query could help us to better identify the concerned cursors:

    SELECT substr(sql_text, 1, 40) "SQL", invalidations from v$sqlarea order by invalidations DESC;
  • CURSOR_SHARING parameter: If this parameter is set to FORCE then literals will be replaced by system generated bind variables where possible. For multiple similar statements which differ only in the literals used this allows the cursors to be shared even though the application supplied SQL uses literals.
  • SESSION_CACHED_CURSORS parameter: Whenever a statement is parsed Oracle first looks at the statements pointed to by your private session cache - if a sharable version of the statement exists it can be used. This provides a shortcut access to frequently parsed statements that uses less CPU and uses far fewer latch gets than a soft or hard parse. To get placed in the session cache the same statement has to be parsed 3 times within the same cursor - a pointer to the shared cursor is then added to your session cache. If all session cache cursors are in use then the least recently used entry is discarded. If you do not have this parameter set already then it is advisable to set it to a starting value of about 50. SESSION_CACHED_CURSORS are particularly useful with Oracle Forms applications when forms are frequently opened and closed.
  • CURSOR_SPACE_FOR_TIME parameter: controls whether parts of a cursor remain pinned between different executions of a statement. This may be worth setting if all else has failed as it can give some gains where there are sharable statements that are infrequently used, or where there is significant pinning / unpinning of cursors (see Latch Information) - if most latch waits are due to "kglpnc: child" and "kglupc: child" this is due to pinning / unpinning of cursors). You must be sure that the shared pool is large enough for the work load otherwise performance will be badly affected and ORA-4031 eventually signalled. If you do set this parameter to TRUE be aware that:

    If the SHARED_POOL is too small for the workload then an ORA-4031 is much more likely to be signalled.
    If your application has any cursor leak then the leaked cursors can waste large amounts of memory having an adverse effect on performance after a period of operation.
  • SHARED_POOL_SIZE parameter: controls the size of the shared pool itself. The size of the shared pool can impact performance. If it is too small then it is likely that sharable information will be flushed from the pool and then later need to be reloaded (rebuilt). If there is heavy use of literal SQL and the shared pool is too large then over time a lot of small chunks of memory can build up on the internal memory freelists causing the shared pool latch to be held for longer which in-turn can impact performance. In this situation a smaller shared pool may perform better than a larger one. The shared pool itself should never be made so large that paging or swapping occur as performance can then decrease by many orders of magnitude.
  • Precompiler HOLD_CURSOR and RELEASE_CURSOR options: When using Oracle Precompiler the behavior of the shared pool can be modified by using parameters RELEASE_CURSOR and HOLD_CURSOR when precompiling the program. These parameters will determine the status of a cursor in the library cache and the session cache once the execution of the cursor ends
  • DBMS_SHARED_POOL.KEEP procedure: This procedure can be used to KEEP objects in the shared pool. DBMS_SHARED_POOL.KEEP allows one to keep packages, procedures, functions, triggers and sequences. It is generally desirable to mark frequently used packages such that they are always KEPT in the shared pool. Objects should be KEPT shortly after instance startup since the database does not do it automatically after a shutdown was issued.
  • Flushing the SHARED POOL: On systems which use a lot of literal SQL the shared pool is likely to fragment over time such that the degree of concurrency which can be achieved diminishes. Flushing the shared pool will often restore performance for a while as it can cause many small chunks of memory to be coalesced

[Content]

Database Instance Response Time Summary

Component Time (cs) Per Execution (cs) Per Transaction (cs) Per User Call (cs) Percentage Description
Service Time 17,736 0.01 2.92 2.83 4.48% Service Time is comprised of time spent on the CPU for Parsing, Recursive CPU usage (for PLSQL and recursive SQL) and CPU used for execution of SQL statements (CPU Other).
Wait Time 378,561 0.19 62.4 60.37 95.52% Wait Time is non-idle time spent away from the CPU waiting for an event to complete or a resource to become available.
Total Response Time 396,297 0.2 65.32 63.2 100% Response Time is a fundamental statistic of performance tuning: Response Time = Service Time + Wait Time

Wait Time is the most significant component of total Response Time. Here you can breakdown to components composing your Wait Time. These include I/O waits for reading blocks from disk as measured by the Wait Events db file sequential read for single-block reads and db file scattered read for multi-block reads. When such Wait Events are found to be significant components of Wait Time, you might want to check SQL statements with high disk read activity that read the most blocks from disk. Also check I/O Section for additional tuning tips.

Service Time Breakdown

Service Time is comprised of time spent on the CPU for Parsing, Recursive CPU usage (for PLSQL and recursive SQL) and CPU used for execution of SQL statements ("CPU Other").

Service Time = CPU Parse + CPU Recursive + CPU Other

Component Time (cs) Per Execution (cs) Per Transaction (cs) Per User Call (cs) Percentage Description
CPU Parse 2,254 0.00 0.37 0.36 12.71% The time spent by CPU to parse SQL statements.
CPU Recursive 19,547 0.01 3.22 3.12 110.21% The recursive cpu usage is the total CPU time used by non-user calls (recursive calls).
CPU Other -4,065 0.00 -0.67 -0.65 -22.92% The time spent by CPU to actually execute SQL statements.
Total Service Time 17,736 0.01 2.92 2.83 100% The total Service Time spent by CPU.

CPU Recursive component is the most significant component of total Service Time. Here you can breakdown to analysis of your CPU Recursive component.

CPU Parse Time Component Analysis

Your database instance performed 64,788 parses including 2,790 hard parses. Execute to Parse percent is 81.48% , soft parse ratio is 95.69%. See Tuning Shared Pool section to see some hits on tuning shared pool and reducing parse count.

CPU Recursive Time Component Analysis

Too many data dictionary statements are parsed and executed. See Data Dictionary section for more detailed information.

CPU Other Time Component Analysis

Other CPU time can be spent on several tasks:

  • SQL statements which cause Oracle to make too many block gets during execution.
  • Too heavy read consistency block activity - this could be caused by many simultaneous active transactions during query exections. Refer to instance statistics CR blocks created, consistent changes, consistent gets, cleanouts and rollbacks - consistent read gets, cleanouts only - consistent read gets to see statistic details in consistent read activity.
  • On average Oracle needed to scan 1 buffers in order to find free buffer. Too high value of this statistic could indicate that you need to increase your buffer cache.
  • The average number of reusable buffers at the end of each LRU is 1 Try to keep this statistic high enough.
  • The average number of blocks gotten per table scan is 8.5 , the average number of rows gotten per table scan is 873.13 Try to keep these statistics as low as possible.
  • Too many rollback count during timing period. Your database performed 5 rollbacks during timing period.


[Content]

Top 5 Wait Events

The most significant foreground wait events ordered by wait time.

This section shows the Top 5 timed events that must be considered to focus the tuning efforts.

Event Waits Time (s) % Total Call Time Wait Event Description
CPU time177 66.07CPU used by this session
control file parallel write21,3085319.75 This event occurs while the session is writing physical blocks to all control files. This happens when:
* The session starts a control file transaction (to make sure that the control files are up to date in case the session crashes before committing the control file transaction)
* The session commits a transaction to a control file
* Changing a generic entry in the control file, the new value is being written to all control files
db file sequential read 10,916166.08The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.
process startup248 93.17Wait for a shared server, Dispatcher, or other background process to start.
log file sync954 62.29When a user session commits, the sessionā€™s redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.


[Content]


Wait Events Detailed Information

Foreground Wait Events

Foreground wait events are those associated with a session or client process waiting for a resource.

Event Waits Timeouts Total Wait Time (s) Average Wait Time (ms) Waits per Transaction Wait Event Description
control file parallel write21,308053 23.5This event occurs while the session is writing physical blocks to all control files. This happens when:
* The session starts a control file transaction (to make sure that the control files are up to date in case the session crashes before committing the control file transaction)
* The session commits a transaction to a control file
* Changing a generic entry in the control file, the new value is being written to all control files
db file sequential read10,9160161 1.8The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.
process startup2480 9340.0Wait for a shared server, Dispatcher, or other background process to start.
log file sync9540660.2 When a user session commits, the sessionā€™s redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session.
db file scattered read875055 0.1The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers. Similar to db file sequential read, except that the session is reading multiple data blocks.
latch free 1801380.0The process waits for a latch that is currently busy (held by another process).
control file sequential read15,28101 02.5Reading from the control file. This happens in many cases. For example, while:
* Making a backup of the controlfiles
* Sharing information (between instances) from the controlfile
* Reading other blocks from the controlfiles
* Reading the header block
log file switch completion270119 0.0Waiting for a log switch to complete.
latch: library cache15 0080.0
log file parallel write9,377000 1.5Writing redo records to the redo log files from the log buffer.
LGWR wait for redo copy45 1020.0LFWR background process waits for redo copy latches.
log buffer space90080.0 Waiting for space in the log buffer because the session is writing data into the log buffer faster than LGWR can write it out.
log file single write22003 0.0Waiting for the write to this logfile to complete. This event is used while updating the header of the logfile. It is signaled when adding a log file member and when incrementing sequence numbers.
control file single write45001 0.0This wait is signaled while the control fileā€™s shared information is written to disk. This is an atomic operation protected by an enqueue (CF), so that only one session at a time can write to the entire database.
db file parallel write8,592000 1.4This event occurs in the DBWR. It indicates that the DBWR is performing a parallel write to files and blocks. The parameter requests indicates the real number of I/Os that are being performed. When the last I/O has gone to disk, the wait ends
SQL*Net more data to client109000 0.0The server process is sending more data/messages to the client. The previous operation to the client was also a send.
latch: cache buffers lru cha 30070.0
log file sequential read22001 0.0Waiting for the read from this logfile to return. This is used to read redo records from the log file.
Queue Monitor Task Wait 2120000.0
SQL*Net break/reset to clien32000 0.0The server sends a break or reset message to the client. The session running on the server waits for a reply from the client.
rdbms ipc reply610 000.0This event is used to wait for a reply from one of the background processes. type The process type that was started
latch: library cache lock 10040.0
buffer busy waits110000.0 Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).
db file single write10010.0 This event is used to wait for the writing of the file headers.
direct path write1850000.0 During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if, during a direct write, no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).
latch: redo allocation24 0000.0
direct path read550000.0 During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).
direct path write temp44 0000.0
ksfd: async disk IO15 0000.0
latch: shared pool10 000.0
jobq slave wait63,517 62,383190,587300110.5
Queue Monitor Wait2,120 2,12063,401299060.3
virtual circuit status 2,1712,17063,375291910.4 The session waits for a virtual circuit to return a message type indicated by status.
wakeup time manager214 255,495######0.0
SQL*Net message from client 1,63305,61134360.3 The server process (foreground process) waits for a message from the client process to arrive.
SQL*Net more data from clien261001 0.0The server is performing another send to the client. The previous operation was also a send to the client.
SQL*Net message to client1,640000 0.3The server (foreground process) is sending a message to the client.

Tuning Tips

control file parallel write - Frequency of Controlfile access is governed by activities such as Redo Logfile switching and Checkpointing. Therefore it can only be influenced indirectly by tuning these activities. This occurs when a server process is updating all copies of the controlfile. If it is significant, check for bottlenecks on the I/O paths (controllers, physical disks) of all of the copies of the controlfile. Possible solutions:

  • Reduce the number of controlfile copies to the minimum that ensures that not all copies can be lost at the same time.
  • Use Asynchronous I/O if available on your platform.
  • Move the controlfile copies to less saturated storage locations.

db file sequential read - This signifies a wait for an I/O read request to complete. Datafile I/O is one of the most important things to tune. This call differs from db file scattered read in that a sequential read reads data into contiguous memory (whilst a scattered read reads multiple blocks and scatters them into different buffers in the SGA). A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block. This wait may also be seen for reads from datafile headers. IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the Tablespace IO and File IO to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance. Tweaking at system level usually only achieves percentage gains. The following points may help: Check for SQL using unselective index scans.

  • A larger buffer cache can help - test this by actually increasing parameter DB_BLOCK_BUFFERS and not by using DB_BLOCK_LRU_EXTENDED_STATISTICS. (Never increase the SGA size if it may induce additional paging or swapping on the system. ).
  • A less obvious issue which can affect the IO rates is how well data is clustered physically. Eg: Assume that you frequently fetch rows from a table where a column is between two values via an index scan. If there are 100 rows in each index block then the two extremes are: Each of the table rows is in a different physical block (100 blocks need to be read for each index block) The table rows are all located in the few adjacent blocks (a handful of blocks need to be read for each index block) Pre-sorting or re-organising data can help to tackle this in severe situations.
  • See if partitioning can be used to reduce the amount of data you need to look at.
  • It can help to place files which incur frequent index scans on disks which have are buffered by an O/S file system cache. Often this will allow some of Oracles read requests to be satisfied from the OS cache rather than from a real disk IO.

log file sync - You need to consider the following actions:

  • Give the checkpoint process more time to cycle through the logs - add more redo log groups, increase the size of the redo logs.
  • Reduce the frequency of checkpoints - increase LOG_CHECKPOINT_INTERVAL, increase size of online redo logs.
  • Improve the efficiency of checkpoints enabling the CKPT process with CHECKPOINT_PROCESS=TRUE
  • Set LOG_CHECKPOINT_TIMEOUT = 0. This disables the checkpointing based on time interval.
  • Another means of solving this error is for DBWR to quickly write the dirty buffers on disk.
  • Decrease the init.ora parameter PROCESSES to the value that is close to actual number of processes you need.

db file scattered read - This is a very common Wait Event. It occurs when Oracle performs multiblock reads from disk into non-contiguous ('scattered') buffers in the Buffer Cache. Such reads are issued for up to DB_FILE_MULTIBLOCK_READ_COUNT blocks at a time. These typically happen for Full Table Scans and for Fast Full Index scans. Datafile I/O is one of the most important things to tune. Multiple tuning advices are available:

  • Find which SQL statements perform Full Table or Fast Full Index scans and tune them to make sure these scans are necessary and not the result of a suboptimal plan.
  • Starting with Oracle9i the new view V$SQL_PLAN view can help: (ignore data dictionary SQL in the output of these queries) For Full Table scans: select sql_text from v$sqltext t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='TABLE ACCESS' and p.options='FULL' order by p.hash_value, t.piece; For Fast Full Index scans: select sql_text from v$sqltext t, v$sql_plan p where t.hash_value=p.hash_value and p.operation='INDEX' and p.options='FULL SCAN' order by p.hash_value, t.piece;
  • In Oracle8i a possible approach is to find sessions performing multiblock reads by querying V$SESSION_EVENT for this Wait Event and then SQL Tracing them. Alternatively, the Top SQL statements with high Physical Reads can be investigated to see if their execution plans contain Full Table or Fast Full Index scans.
  • In cases where such multiblock scans occur from optimal execution plans it is possible to tune the size of multiblock I/Os issued by Oracle by setting the instance parameter DB_FILE_MULTIBLOCK_READ_COUNT so that DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system
  • As blocks read using Full Table and Fast Full Index scans are placed on the least recently used end of the Buffer Cache replacement lists, sometimes it may help to use Multiple Buffer Pools and place such segments in the KEEP pool.
  • Partitioning can also be used to reduce the amount of data to be scanned as Partition Pruning can restrict the scan to a subset of the segment's partitions.
  • Finally, you can consider reducing the data held in the most frequently accessed segments (by moving older unneeded data out of the database) or moving these segments to new faster disks to reduce the response time on their I/Os.

latch free - Refer to Latch Tuning section to see tuning recommendations available.

control file sequential read - Frequency of Controlfile access is governed by activities such as Redo Logfile switching and Checkpointing. Therefore it can only be influenced indirectly by tuning these activities. This occurs on I/O to a single copy of the controlfile. If they are significant find out whether the waits are on particular copy of the controlfile and if so whether its I/O path is saturated. The following query can be used to find which controlfile is being accessed. It has to be run when the problem is occuring: select P1 from V$SESSION_WAIT where EVENT like 'control file%' and STATUS='WAITING'; Possible solutions:

  • Move the problematic controlfile copy to a less saturated storage location.
  • Use Asynchronous I/O if available on your platform.

log file switch completion - You need to consider the following actions:

  • Give the checkpoint process more time to cycle through the logs - add more redo log groups, increase the size of the redo logs.
  • Reduce the frequency of checkpoints - increase LOG_CHECKPOINT_INTERVAL, increase size of online redo logs.
  • Improve the efficiency of checkpoints enabling the CKPT process with CHECKPOINT_PROCESS=TRUE
  • Set LOG_CHECKPOINT_TIMEOUT = 0. This disables the checkpointing based on time interval.
  • Another means of solving this error is for DBWR to quickly write the dirty buffers on disk.

log file parallel write - This wait event is used when waiting for the writes of redo records to the redo log files to complete. The waits occur 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. Even though the writes may be issued in parallel, LGWR needs to wait for the last I/O to be on disk before the parallel write is considered complete. Hence the wait time depends on the time it takes the OS to complete all requests.

You might want to reduce "log file parallel write" wait times in order to reduce user waits which depend on LGWR.

  • Ensure tablespaces are NOT left in HOT BACKUP mode longer than needed. Tablespaces in HOT BACKUP mode cause more redo to be generated for each change which can vastly increase the rate of redo generarion.
  • Redo log members should ideally be on high speed disks eg: RAID5 is not a good candidate for redo log members.
  • Redo log members should be on disks with little/no IO activity from other sources. (including low activity from other sources against the same disk controller)
  • RAW devices can be faster file system files.
  • NOLOGGING / UNRECOVERABLE operations may be possible for certain operations to reduce the overall rate of redo generation.

log buffer space - Consider making the log buffer bigger if it is small, or moving the log files to faster disks such as striped disks. Watch the statistic redo log space requests which reflects the number of times a user process waits for space in the redo log file, not the buffer space The value of "redo log space requests" should be near 0. If this value increments consistently, processes have had to wait for space in the buffer. This may be caused the checkpointing or log switching. Improve thus the checkpointing or archiving process.

log file single write - See log file parallel write details.

control file single write - Frequency of Controlfile access is governed by activities such as Redo Logfile switching and Checkpointing. Therefore it can only be influenced indirectly by tuning these activities. This occurs on I/O to a single copy of the controlfile. If they are significant find out whether the waits are on particular copy of the controlfile and if so whether its I/O path is saturated. The following query can be used to find which controlfile is being accessed. It has to be run when the problem is occuring: select P1 from V$SESSION_WAIT where EVENT like 'control file%' and STATUS='WAITING'; Possible solutions:

  • Move the problematic controlfile copy to a less saturated storage location.
  • Use Asynchronous I/O if available on your platform.

db file parallel write - This Wait Events occur because of Buffer Cache operations involving the DBWR process(es) and I/O Slaves. This wait shows up in database writer. DBWR waits on "db file parallel write" when waiting for a parallel write to files and blocks to complete. The wait lasts until all submitted IOs are complete. DBWR throughput is very platform and version specific so only general observations can be made here. The following items may influence the rate at which DBWR can clear blocks from the cache: Physical disk attributes (stripe size, speed, layout etc..) Raw devices versus File System Files Spreading written data across more disks/files Using Asynchronous writes where available Using multiple database writers where asynch. IO is not available. DBWR_IO_SLAVES in Oracle8/9. Using multiple DB Writer gatherer processes in Oracle8 DB_WRITER_PROCESSES Setting _DB_BLOCK_WRITE_BATCH to a large number. This parameter is obsoleted in 8.1. Using the "Multiple buffer pools" feature in Oracle8 and Higher. Note that there many port specific issues which affect the optimal setup for DBWR on a given platform. These range from choosing a DB_BLOCK_SIZE which is a multiple of the page size used by the operating system for IO operations to configuring Asynchronous IO correctly

SQL*Net more data to client - The client side is selecting large records and/or is using array fetch. Basically the Oracle Server is sending more data thand will fit in a SQL
*Net package. Increasing the packet size will help to reduce this event and will help to improve performance.

log file sequential read - See log file parallel write details.

SQL*Net break/reset to clien - The client is sending some bundled calls or doing an array operation that resulted in an error at the server side. The server can't receive the remaining data and notifies the client to reset the connection.

buffer busy waits - This wait happens when a session wants to access a database block in the buffer cache but it cannot as 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 our request.
  • buffer busy wait occurs if multiple processes want to access a buffer in the buffer cache concurrently.
As buffer busy waits are due to contention for particular blocks then you cannot take any action until you know which blocks are being competed for and why. Eliminating the cause of the contention is the best option. Note that "buffer busy waits" for data blocks are often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index) - the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk - the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in. The following hints may be useful for particular types of contention - these are things that MAY reduce contention for particular situations:
  • data blocks - Eliminate HOT blocks from the application. Check for repeatedly scanned / unselective indexes. Change PCTFREE and/or PCTUSED. Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes). Increase INITRANS. Reduce the number of rows per block.
  • segment header - Increase of number of FREELISTs. Use FREELIST GROUPs (even in single instance this can make a difference).
  • freelist blocks - Add more FREELISTS. In case of Parallel Server make sure that each instance has its own FREELIST GROUP(s).
  • undo header - Add more rollback segments.

db file single write - This Wait Events occur because of Buffer Cache operations involving the DBWR process(es) and I/O Slaves. This wait shows up in database writer. DBWR waits on "db file single write" when waiting for a single write operation to files and blocks to complete. DBWR throughput is very platform and version specific so only general observations can be made here. The following items may influence the rate at which DBWR can clear blocks from the cache: Physical disk attributes (stripe size, speed, layout etc..) Raw devices versus File System Files Spreading written data across more disks/files Using Asynchronous writes where available Using multiple database writers where asynch. IO is not available. DBWR_IO_SLAVES in Oracle8/9. Using multiple DB Writer gatherer processes in Oracle8 DB_WRITER_PROCESSES Setting _DB_BLOCK_WRITE_BATCH to a large number. This parameter is obsoleted in 8.1. Using the "Multiple buffer pools" feature in Oracle8 and Higher. Note that there many port specific issues which affect the optimal setup for DBWR on a given platform. These range from choosing a DB_BLOCK_SIZE which is a multiple of the page size used by the operating system for IO operations to configuring Asynchronous IO correctly

direct path write - Direct path writes allow a session to queue an IO write request and continue processing whilst the OS handles the IO. If the session needs to know if an outstanding write is complete then it waits on this waitevent. This can happen because the session is out of free slots and just needs an empty buffer (it waits on the oldest IO) or because it needs to ensure all writes are flushed. If asynchronous IO is not being used then the IO write request blocks until completed but this dies not show as a wait at the time the IO is issued. The session returns later to pick up the completed IO data but can then show a wait on "direct path write" even though this wait will return immediately.

Hence this wait event is very misleading as:

  • The total number of waits does not reflect the number of IO requests.
  • The total time spent in "direct path write" does not always reflect the true wait time.

This style of write request is typically used for:

  • Direct load operations (eg: Create Table as Select (CTAS) may use this)
  • Parallel DML operations
  • Sort IO (when a sort does not fit in memory)
  • Writes to uncached "LOB" segments (later releases wait on "direct path write (lob)" )

direct path read - Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache). If asynchronous IO is supported (and in use) then Oracle can submit IO requests and continue processing. It can then pick up the results of the IO request later and will wait on "direct path read" until the required IO completes. If asynchronous IO is not being used then the IO requests block until completed but these do not show as waits at the time the IO is issued. The session returns later to pick up the completed IO data but can then show a wait on "direct path read" even though this wait will return immediately. Hence this wait event is very misleading as:

  • The total number of waits does not reflect the number of IO requests
  • The total time spent in "direct path read" does not always reflect the true wait time.

This style of read request is typically used for:

  • Sort IO (when a sort does not fit in memory)
  • Parallel Query slaves
  • Readahead (where a process may issue an IO request for a block it expects to need in the near future)

Multiple advices available:

  • Check temporary tablespaces for unexpected disk sort operations.
  • Ensure parameter DISK_ASYNCH_IO is TRUE . This is unlikely to reduce wait times from the wait event timings but may reduce sessions elapsed times (as synchronous direct IO is not accounted for in wait event timings).
  • Ensure the OS asynchronous IO is configured correctly.
  • Check for IO heavy sessions / SQL and see if the amount of IO can be reduced.
  • Ensure no disks are IO bound.

SQL*Net more data from clien - The client side is inserting large records and/or is using array insert. Basically the client side is sending more data than will fit in one SQL
*Net package. Increasing the packet size will help to reduce this event and will help to improve performance.

SQL*Net message to client - The Oracle Server is experiencing some delays in sending data to the client side. This could happen if the network connection is slow or has some other performance problem. This send send should work without a delay.



[Content]

Background Wait Events

Background wait events are those not associated with a client process. They indicate waits encountered by system and non-system processes. Examples of background system processes are LGWR and DBWR. An example of a non-system background process would be a parallel query slave.
Note that it is possible for a wait event to appear in both the foreground and background wait events statistics. Examples of this are the enqueue and latch free events. The idle wait events appear at the bottom of both sections and can generally safely be ignored. Typically these type of events keep record of the time while the client is connected to the database but not requests are being made to the server.

Event Waits Timeouts Total Wait Time (s) Average Wait Time (ms) Waits per Transaction Wait Event Description
control file parallel write21,30605323.5 This event occurs while the session is writing physical blocks to all control files. This happens when:
* The session starts a control file transaction (to make sure that the control files are up to date in case the session crashes before committing the control file transaction)
* The session commits a transaction to a control file
* Changing a generic entry in the control file, the new value is being written to all control files
process startup24809340.0 Wait for a shared server, Dispatcher, or other background process to start.
control file sequential read2,4700000.4 Reading from the control file. This happens in many cases. For example, while:
* Making a backup of the controlfiles
* Sharing information (between instances) from the controlfile
* Reading other blocks from the controlfiles
* Reading the header block
log file parallel write 9,3780001.5 Writing redo records to the redo log files from the log buffer.
LGWR wait for redo copy45102 0.0LFWR background process waits for redo copy latches.
db file sequential read 500010.0The session waits while a sequential read from the database is performed. This event is also used for rebuilding the control file, dumping datafile headers, and getting the database file headers.
log file single write 220030.0Waiting for the write to this logfile to complete. This event is used while updating the header of the logfile. It is signaled when adding a log file member and when incrementing sequence numbers.
log file switch completion400130.0 Waiting for a log switch to complete.
control file single write 440010.0This wait is signaled while the control fileā€™s shared information is written to disk. This is an atomic operation protected by an enqueue (CF), so that only one session at a time can write to the entire database.
db file parallel write 8,5920001.4This event occurs in the DBWR. It indicates that the DBWR is performing a parallel write to files and blocks. The parameter requests indicates the real number of I/Os that are being performed. When the last I/O has gone to disk, the wait ends
latch: library cache40070.0
log file sequential read 220010.0Waiting for the read from this logfile to return. This is used to read redo records from the log file.
Queue Monitor Task Wait212000 0.0
rdbms ipc reply600000.0 This event is used to wait for a reply from one of the background processes. type The process type that was started
buffer busy waits11 0000.0Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).
latch: redo allocation24000 0.0
direct path read55 0000.0During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).
latch: cache buffers lru cha1000 0.0
direct path write55 0000.0During Direct Path operations, the data is asynchronously written to the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if, during a direct write, no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).
latch: shared pool10000.0
rdbms ipc message190,258181,670493,983 259631.4The background processes (LGWR, DBWR, LMS0) use this event to indicate that they are idle and are waiting for the foreground processes to send them an IPC message to do some work.
Queue Monitor Wait2,1202,12063,401 299060.3
smon timer22020760,956###### 0.0This is the main idle event for SMON. SMON will be waiting on this event most of the time until it times out or is posted by another process.


[Content]

SQL Information

SQL ordered by Gets

Resources reported for PL/SQL code includes the resources used by all SQL

This section reports the contents of the SQL area ordered by the number of buffer gets and can be used to identify CPU Heavy SQL. The statements of interest are those with a large number of gets per execution especially if the number of executions is high. High buffer gets generally correlates with heavy CPU usage.

Buffer Gets Executions Gets per Exec % Total CPU Time (s) Elapsed Time (s) Hash Value
892,384 1 892,384.0 30.1 65.76 80.32 125601442
call dbms_stats.gather_database_stats_job_proc ( )
482,938 28,608 16.9 16.3 21.21 21.49 3715989406
Module: spvpro.exeSELECT VALUE FROM STATS$PARAMETER WHERE SNAP_ID = :B4 AND DBID =:B3 AND INSTANCE_NUMBER = :B2 AND ( NAME = '__' || :B1 OR NAME= :B1 ) ORDER BY NAME
357,773 119,250 3.0 12.1 4.72 4.77 1864097893
Module: spvpro.exeSELECT VALUE FROM STATS$SYSSTAT WHERE SNAP_ID = :B4 AND DBID = :B3 AND INSTANCE_NUMBER = :B2 AND NAME = :B1
317,844 5 63,568.8 10.7 15.45 15.48 2968069127
Module: spvpro.exeselect b.name "B.NAME" , e.value - b.value "Total" , round((e.value - b.value)/sp101.getEla(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),2)"Per Second" , round((e.value - b.value)/sp101.getTran(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO'),2) "Per Transaction" f
219,566 1,871 117.4 7.4 1.69 1.95 4003280836
Module: MMON_SLAVESELECT SU.NAME, SO.NAME, A.STATSTYPE#, A.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, OBJ$ TY WHERE O.OWNER#=U.USER# AND A.OBJ#=TY.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# AND CT.TOID=TY.OID$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2
218,020 69 3,159.7 7.4 11.67 12.65 3931244260
Module: MMON_SLAVEbegin dbms_stats.gather_table_stats('SYS', :bind1, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE); end;
183,373 3 61,124.3 6.2 8.60 9.00 3680459707
Module: spvpro.exeselect e.old_hash_value "E.OLD_HASH_VALUE", e.module "Module" , e.buffer_gets - nvl(b.buffer_gets,0) "Buffer Gets" , e.executions - nvl(b.executions,0) "Executions" , decode ((e.executions - nvl(b.executions, 0)), 0, to_number(NULL)
161,650 1,060 152.5 5.5 20.76 22.14 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
134,823 19 7,095.9 4.6 20.31 20.78 3025159973DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN STATSPACK.SNAP; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
119,457 2 59,728.5 4.0 5.63 6.55 2976718527
Module: spvpro.exeselect e.old_hash_value "E.old_hash_value", e.module "Module" , e.parse_calls - nvl(b.parse_calls,0) "Parse Calls" ,(e.parse_calls - nvl(b.parse_calls,0))/sp101.getPrse(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap,'NO') "Parses" , e.executions - nvl(b.exec
118,450 4,770 24.8 4.0 4.35 4.36 2428346637
Module: spvpro.exeSELECT SUM(GETS), SUM(MISSES) FROM STATS$LATCH WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1
76,711 1,871 41.0 2.6 0.70 0.72 1085462914
Module: MMON_SLAVESELECT SU.NAME, SO.NAME, A.STATSTYPE#, A.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, USER$ U, COL$ C, OBJ$ SO, USER$ SU WHERE O.OWNER#=U.USER# AND A.OBJ#=O.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=A.INTCOL# AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 AND U.NAME=:B3 AND O.NAME=:B2 AND C.NAME=:B1
63,653 763 83.4 2.1 4.78 4.95 3142066274
CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:tguid, :mguid, :result)
59,758 1 59,758.0 2.0 2.81 2.83 637379631
Module: spvpro.exeselect e.old_hash_value "E.old_hash_value", e.module "Module" , e.disk_reads - nvl(b.disk_reads,0) "Reads" , e.executions - nvl(b.executions,0) "Executions" , Round(decode ((e.executions- nvl(b.executions, 0)), 0, to_number(NULL)
51,204 19 2,694.9 1.7 4.53 6.44 451073132
insert into wrh$_sqlbind (snap_id, dbid, instance_number, sql_id, child_number, name, position, dup_position, datatype,datatype_string, character_sid, precision, scale, max_length,was_captured, last_captured, value_string, value_anydata) SELECT /*+ ordered use_nl(b) index(b sql_id) */ :snap_id, :d
48,742 3,705 13.2 1.6 1.26 1.32 2120239928
Module: MMON_SLAVESELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON#= CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND CD.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND ROWNUM <= 1 UNION ALL SE
35,388 3 11,796.0 1.2 1.68 1.80 329516106
Module: spvpro.exeSELECT StatName,StatValue "Statistic Value" FROM ( SELECT 'RedoSize/Sec' StatName, sp101.getRedoSizePerSec(:pDbID,:pInstNum,:pBgnSnap,:pEndSnap, 'NO' ) StatValue FROM Dual UNION ALL SELECT'Redo Size/Tx' StatName, sp101.getRedoSizePerTr(:pDbID,:pInstNu


[Content]

SQL ordered by Reads

End Disk Reads Threshold: 1000 Total Disk Reads: 20,431

This section reports the contents of the SQL area ordered by the number of reads from the data files and can be used to identify SQL causing IO bottlenecks which consume the following resources.

  • CPU time needed to fetch unnecessary data.
  • File IO resources to fetch unnecessary data.
  • Buffer resources to hold unnecessary data.
  • Buffer resources to hold unnecessary data.
  • Additional CPU time to process the query once the data is retrieved into the buffer.

Physical Reads Executions Reads per Exec % Total CPU Time (s) Elapsed Time (s) Hash Value
11,755 1 11,755.0 57.5 65.76 80.32 125601442
call dbms_stats.gather_database_stats_job_proc ( )
1,745 1 1,745.0 8.5 0.31 0.77 3398738531
select /*+ cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count(distinct "TOID"),sum(vsize("TOID")),substrb(dump(min("TOID"),16,0,32),1,120),substrb(dump(max("TOID"),16,0,32),1,120),count(distinct "VERSION#"),sum(vsize("VERSION#")),substrb(dump(min("VERSION#"),16,0,32),1,120),su
559 69 8.1 2.7 11.67 12.65 3931244260
Module: MMON_SLAVEbegin dbms_stats.gather_table_stats('SYS', :bind1, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE); end;
221 1,060 0.2 1.1 20.76 22.14 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;


[Content]

SQL ordered by Executions

End Executions Threshold: 100 Total Executions: 349,835

This section reports the contents of the SQL area ordered by the number of query executions. It is primarily useful in identifying the most frequently used SQL within the database so that they can be monitored for efficiency.  Generally speaking, a small performance increase on a frequently used query provides greater gains than a moderate performance increase on an infrequently used query.

Executions Rows Processed Rows per Exec CPU per Execution (s) Elapsed Time per Execution (s) Hash Value
119,250 119,250 1.0 0.00 0.00 1864097893
Module: spvpro.exeSELECT VALUE FROM STATS$SYSSTAT WHERE SNAP_ID = :B4 AND DBID = :B3 AND INSTANCE_NUMBER = :B2 AND NAME = :B1
28,608 28,608 1.0 0.00 0.00 3715989406
Module: spvpro.exeSELECT VALUE FROM STATS$PARAMETER WHERE SNAP_ID = :B4 AND DBID =:B3 AND INSTANCE_NUMBER = :B2 AND ( NAME = '__' || :B1 OR NAME= :B1 ) ORDER BY NAME
12,719 1,097 0.1 0.00 0.00 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= next_date) and (next_date < :2)) or ((last_date is null) and(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) order by next_date, job
11,625 11,625 1.0 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (next_date < (sysdate+5/86400))
9,089 5,901 0.6 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival,density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
6,009 0 0.0 0.00 0.00 1053795750
COMMIT
4,770 4,770 1.0 0.00 0.00 538836789
Module: spvpro.exeSELECT BYTES FROM STATS$SGASTAT WHERE SNAP_ID = :B4 AND DBID = :B3 AND INSTANCE_NUMBER = :B2 AND POOL IN ('shared pool', 'all pools') AND NAME = :B1
4,770 4,770 1.0 0.00 0.00 718584351
Module: spvpro.exeSELECT SUM(TIME_WAITED_MICRO) FROM STATS$SYSTEM_EVENT WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1 AND EVENT NOTIN (SELECT EVENT FROM STATS$IDLE_EVENT)
4,770 4,770 1.0 0.00 0.00 804181686
Module: spvpro.exeSELECT SUM(WAIT_COUNT) FROM STATS$WAITSTAT WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1
4,770 4,770 1.0 0.00 0.00 2428346637
Module: spvpro.exeSELECT SUM(GETS), SUM(MISSES) FROM STATS$LATCH WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value4,770 4,770 1.0 0.00 0.00 3539850956Module: spvpro.exeSELECT SESSION_ID , SERIAL# FROM STATS$SNAPSHOT WHERE SNAP_ID =:B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1
4,770 4,770 1.0 0.00 0.00 3547062907
Module: spvpro.exeSELECT SUM(BYTES) FROM STATS$SGASTAT WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1 AND POOL IN ('shared pool','all pools')
4,770 4,770 1.0 0.00 0.00 3628651449
Module: spvpro.exeSELECT SUM(PINS), SUM(PINHITS) FROM STATS$LIBRARYCACHE WHERE SNAP_ID = :B3 AND DBID = :B2 AND INSTANCE_NUMBER = :B1
3,705 3,705 1.0 0.00 0.00 2120239928
Module: MMON_SLAVESELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 AND CC.INTCOL# = :B1 AND CD.CON#= CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED IS NOT NULL AND CD.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND ROWNUM <= 1 UNION ALL SE


[Content]

SQL ordered by Parse Calls

End Parse Calls Threshold: 1000 Total Parse Calls: 64,788

This section shows the number of times a statement was parsed as compared to the number of times it was executed. One to one parse/executions may indicate that:

  • bind variables are not being used.
  • on RDBMS version 8.1.7.2 and higher the init.ora parameter session_cached_cursors was not set in the init.ora (100 is usually the suggested starting value).
  • the shared pool may be too small and the parse is not being retained long enough for multiple executions.
  • cursor_sharing is set to exact (this should NOT be changed without considerable testing on the part of the client).

Parse Calls Executions % Total Parses Hash Value
3,529 6,009 5.45 1053795750
COMMIT
2,795 2,795 4.31 4143084494
select privilege#,level from sysauth$ connect by grantee#=priorprivilege# and privilege#>0 start with grantee#=:1 and privilege#>0
2,359 2,359 3.64 2803285
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
2,349 2,349 3.63 2396279102
lock table sys.mon_mods$ in exclusive mode nowait
2,136 2,136 3.30 1403276364
select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, deftiniexts, defextsize,defminexts, defmaxexts, defextpct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(trunc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpar
1,116 1,116 1.72 3840591838
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0
1,098 1,098 1.69 297937389
update sys.job$ set this_date=:1 where job=:2
1,098 1,097 1.69 4075357577
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date = greatest(:3, sysdate), total=total+(sysdate-nvl(this_date,sysdate)) where job=:4
1,061 1,061 1.64 3521705928
Module:SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AND UPPER(PARAMETER_VALUE)='TRUE'
1,060 1,060 1.64 718529565
SELECT C.TARGET_GUID, C.METRIC_GUID, C.STORE_METRIC, C.SCHEDULE,C.COLL_NAME, M.METRIC_NAME, M.EVAL_FUNC FROM MGMT_METRIC_COLLECTIONS_REP R, MGMT_METRIC_COLLECTIONS C, MGMT_METRICS M WHERE C.SUSPENDED = 0 AND C.IS_REPOSITORY = 1 AND (C.LAST_COLLECTED_TIMESTAMP IS NULL OR C.LAST_COLLECTED_TIMESTAMP + C.SCHEDULE / 1440 <
1,060 1,060 1.64 884862163
Module: EM_PINGDELETE FROM MGMT_JOB_EMD_STATUS_QUEUE
Parse Calls Executions Parses Hash Value1,060 1,060 1.64 923291638select sysdate + 1 / (24 * 60) from dual
1,060 1,060 1.64 1133235621
Module: EM_PINGSELECT COUNT(FAILOVER_ID) FROM MGMT_FAILOVER_TABLE
1,060 2,094 1.64 1200253560
INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL)VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 ,1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,128), SUBSTR(:B2,1,128), SUBSTR(:B1 ,1,256))
1,060 1,060 1.64 1667689875
Module: SQL*PlusSELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL
1,060 1,060 1.64 1794066809
Module: SEVERITY EVALUATIONSELECT UNIQUE(TARGET_GUID) FROM MGMT_METRIC_DEPENDENCY WHERE CAN_CALCULATE = 1 AND DISABLED = 0
1,060 1,060 1.64 2100170746
Module: EM_PINGSELECT EMD_URL, EVENT_TYPE, OCCUR_TIME FROM MGMT_JOB_EMD_STATUS_QUEUE ORDER BY OCCUR_TIME
1,060 1,060 1.64 2561692322
Module: EM_PINGSELECT COUNT(*) FROM MGMT_FAILOVER_TABLE WHERE SYSDATE-LAST_TIME_STAMP > (HEARTBEAT_INTERVAL*4)/(24*60*60)
1,060 1,060 1.64 2603722335
Module: EM_PINGSELECT /*+ RULE */ STEP_ID FROM MGMT_JOB_EXECUTION E, MGMT_JOB JWHERE E.JOB_ID=J.JOB_ID AND STEP_STATUS IN (:B6 , :B5 , :B4 , :B3 , :B2 ) AND STEP_TYPE=:B1 AND J.EXECUTION_TIMEOUT > 0 AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE)-E.START_TIME) > (J.EXECUTION_TIMEOUT/24)
1,060 1,060 1.64 2689373535
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
1,060 1,060 1.64 2921587358
Module: EM_PINGSELECT /*+ RULE */ STATUS, EXECUTION_ID FROM MGMT_JOB_EXEC_SUMMARY E WHERE STATUS IN (:B2 , :B1 ) AND SUSPEND_TIMEOUT > 0 AND (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE)-SUSPEND_TIME) > (SUSPParse Calls Executions Parses Hash ValueEND_TIMEOUT/(24*60))
1,060 1,060 1.64 3966248571
alter session set NLS_LANGUAGE='RUSSIAN' NLS_TERRITORY='RUSSIA'NLS_CURRENCY='?.' NLS_ISO_CURRENCY='RUSSIA' NLS_NUMERIC_CHARACTERS=', ' NLS_DATE_FORMAT='DD.MM.RR' NLS_DATE_LANGUAGE='RUSSIAN' NLS_SORT='RUSSIAN'
843 843 1.30 492173694
delete from tab_stats$ where obj#=:1
806 806 1.24 1932955448
delete from superobj$ where subobj# = :1
800 800 1.23 3940748077
declare vsn varchar2(20); beginvsn := dbms_rcvman.getPackageVersion; :pkg_vsn:pkg_vsn_i := vsn; if vsn is not null then

Tuning Tips

  • Verify that SQL statements have optimal exectuon plans.
  • If indexes are missing on key columns then queries will have to use Full Table Scans to retrieve data. Usually indexes for performance should be added to support selective predicates included in queries.,/li>
  • Insufficiently high sample rate for CBO - If the CBO does not have the correct statistical information then it cannot be expected to produce accurate results. Usually a sample size of 5% will be sufficient, however in some cases it may be necessary to have more accurate statistics at its' disposal.
  • Try to minimize unnecessary sorting. Sorting is a very expensive operation which causes:
    • High CPU usage
    • Potentially large disk usage
    Try to make the query sort the data as late in the access path as possible. The idea behind this is to make sure that the smallest number of rows possible are sorted. Remember that:
    • Indexes may be used to provided presorted data.
    • Sort merge joins inherently need to do a sort.
    • Some sorts don't actually need a sort to be performed. In this case the explain plan should show NOSORT for this operation.
    In summary:
    • Increase sort area size to promote in memory sorts.
    • Modify the query to process less rows -> Less to sort.
    • Use an index to retrieve the rows in order and avoid the sort.
    • use sort_direct_writes to avoid flooding the buffer cache with sort blocks.
    • If Pro*C use release_cursor=yes as this will free up any temporary segments held open.
  • Late row elimination - Queries are more likely to be performant if the bulk of the rows can be eliminated early in the plan. If this does happen then unnecessary comparisons may be made on rows that are simply eliminated later. This tends to increase CPU usage with no performance benefits. If these rows can be eliminated early in the access path using a selective predicate then this may significantly enhance the query performance.
  • Wrong plan or join order selected - If the wrong plan has been selected then you may want to force the correct one.
  • Over parsing - Over parsing implies that cursors are not being shared. If statements are referenced multiple times then it makes sense to share then rather than fill up the shared pool with multiple copies of essentially the same statement.
  • Skewed data - If column data distribution is non uniform, then the use of column statistics in the form of histograms should be considered. Histogram statistics do not help with uniformly distributed data or where no information about the column predicate is available such as with bind variables.
  • Consider the usage of following features forcing use of CBO:
    • Degree of parallelism set on any table in the query.
    • Index-only tables.
    • Partition Tables
    • Materialised views.


[Content]

Instance Activity Information

The statistics section shows the overall database statistics. These are the statistics that the summary information is derived from.

Statistic Total per Second per Trans Description
CPU used by this session 17,7360.32.9Amount of CPU time (in 10s of milliseconds) used by a session from the time a user call starts until it ends. If a user call completes within 10 milliseconds, the start and end user-call time are the same for purposes of this statistics, and 0 milliseconds are added. A similar problem can exist in the reporting by the operating system, especially on systems that suffer from many context switches.
CPU used when call started 17,7360.32.9The CPU time used when the call is started
CR blocks created1,048 0.00.2Number of CURRENT blocks cloned to create CR (consistent read) blocks. The most common reason for cloning is that the buffer is held in a incompatible mode.
Cached Commit SCN referenced 1,3420.00.2Useful only for internal debugging purposes
Commit SCN cached30.0 0.0Number of times the system change number of a commit operation was cached
DB time12,994,709199.5 2,141.9
DBWR checkpoint buffers written18,6140.33.1Number of buffers that were written for checkpoints
DBWR checkpoints110.0 0.0Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint or the end of recovery. This statistic is always larger than "background checkpoints completed".
DBWR object drop buffers written00.00.0
DBWR revisited being-written buff00.00.0 Number of times that DBWR tried to save a buffer for writing and found that it was already in the write batch. This statistic measures the amount of "useless" work that DBWR had to do in trying to fill the batch. Many sources contribute to a write batch. If the same buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" because the buffer is already marked as being written.
DBWR thread checkpoint buffers wr1,3910.00.2
DBWR transaction table writes5280.00.1Number of rollback segment headers written by DBWR. This statistic indicates how many "hot" buffers were written, causing a user process to wait while the write completed.
DBWR undo block writes 5,5640.10.9Number of rollback segment blocks written by DBWR
IMU CR rollbacks100.0 0.0
IMU Flushes1400.0 0.0
IMU Redo allocation size 3,225,92449.5531.7
IMU commits4,1970.1 0.7
IMU contention00.0 0.0
IMU pool not allocated 1,9470.00.3
IMU recursive-transaction flush80.00.0
IMU undo allocation size 9,709,056149.11,600.3
IMU- failed to get a private stra1,9470.00.3
SQL*Net roundtrips to/from client1,6040.00.3 Total number of Net8 messages sent to and received from the client
active txn count during cleanout1,7220.00.3
application wait time3 0.00.0
background checkpoints completed110.00.0Number of checkpoints completed by the background process. This statistic is incremented when the background process successfully advances the thread checkpoint.
background checkpoints started110.00.0Number of checkpoints started by the background process. This statistic can be larger than "background checkpoints completed" if a new checkpoint overrides an incomplete checkpoint or if a checkpoint is currently under way. This statistic includes only checkpoints of the redo thread. It does not include: Individual file checkpoints for operations such as offline or begin backup Foreground (user-requested) checkpoints (for example, performed by ALTER SYSTEM CHECKPOINT LOCAL statements)
background timeouts181,714 2.830.0
buffer is not pinned count 1,192,57718.3196.6Number of times a buffer was free when visited. Useful only for internal debugging purposes.
buffer is pinned count 1,144,65017.6188.7Number of times a buffer was pinned when visited. Useful only for internal debugging purposes.
bytes received via SQL*Net from c1,143,72317.6188.5 Total number of bytes received from the client over Net8
bytes sent via SQL*Net to client890,89913.7146.8 Total number of bytes sent to the client from the foreground processes.
calls to get snapshot scn: kcmgss573,8818.894.6 Number of times a snapshot system change number (SCN) was allocated. The SCN is allocated at the start of a transaction.
calls to kcmgas23,4410.4 3.9Number of calls to routine kcmgas to get a new SCN
calls to kcmgcs1,4530.0 0.2Number of calls to routine kcmgcs to get a current SCN
change write time1,010 0.00.2Elapsed redo write time for changes made to CURRENT blocks in 10s of milliseconds. This statistic is populated only if the TIME_STATISTICS parameter is set to TRUE.
cleanout - number of ktugct calls2,4580.00.4
cleanouts and rollbacks - consist60.00.0Number of consistent gets that require both block rollbacks and block cleanouts.
cleanouts only - consistent read5290.00.1 Number of consistent gets that require only block cleanouts, no rollbacks.
cluster key scan block gets 94,1331.515.5Number of blocks obtained in a cluster scan
cluster key scans47,233 0.77.8Number of cluster scans that were started
commit cleanout failures: callbac440.00.0 Number of times the cleanout callback function returns FALSE
commit cleanouts30,505 0.55.0Total number of times the cleanout block at commit function was performed
commit cleanouts successfully com30,4610.55.0 Number of times the cleanout block at commit function completed successfully
commit txn count during cleanout1,5350.00.3
concurrency wait time15 0.00.0
consistent changes2,572 0.00.4Number of times a user process has applied rollback entries to perform a consistent read on the block Work loads that produce a great deal of consistent changes can consume a great deal of resources. The value of this statistic should be small in relation to the "consistent gets" statistic.
consistent gets2,487,628 38.2410.0Number of times a consistent read was requested for a block.
consistent gets - examination1,076,73216.5177.5
consistent gets from cache 2,487,62838.2410.0
cursor authentications 1,0870.00.2Number of privilege checks conducted during execution of an operation
data blocks consistent reads - un2,4930.00.4 Number of undo records applied to data blocks that have been rolled back for consistent read purposes
db block changes594,454 9.198.0Closely related to "consistent changes", this statistic counts the total number of changes that were part of an update or delete operation that were made to all blocks in the SGA. Such changes generate redo log entries and hence become permanent changes to the database if the transaction is committed. This approximates total database work. It statistic indicates the rate at which buffers are being dirtied (on a per-transaction or per-second basis, for example).
db block gets473,5157.3 78.1Number of times a CURRENT block was requested.
db block gets direct354 0.00.1
db block gets from cache 473,1617.378.0
deferred (CURRENT) block cleanout20,3330.33.4 Number of times cleanout records are deferred, piggyback with changes, always current get
dirty buffers inspected1 0.00.0Number of dirty buffers found by the user process while the it is looking for a buffer to reuse
enqueue conversions13,7420.22.3 Total number of conversions of the state of table or row lock
enqueue releases551,9008.591.0 Total number of table or row locks released
enqueue requests551,9018.591.0 Total number of table or row locks acquired
enqueue timeouts00.00.0 Total number of table and row locks (acquired and converted) that timed out before they could complete
enqueue waits00.00.0Total number of waits that occurred during an enqueue convert or get because the enqueue get was deferred
execute count349,8355.4 57.7Total number of calls (user and recursive) that executed SQL statements
free buffer inspected3,679 0.10.6Number of buffers skipped over from the end of an LRU queue in order to find a reusable buffer. The difference between this statistic and "dirty buffers inspected" is the number of buffers that could not be used because they had a user, a waiter, or were being read or written, or because they were busy or needed to be written after rapid aging out.
free buffer requested28,285 0.44.7Number of times a reusable buffer or a free buffer was requested to create or load a block
heap block compress871 0.00.1
hot buffers moved to head of LRU2,1750.00.4When a hot buffer reaches the tail of its replacement list, Oracle moves it back to the head of the list to keep it from being reused. This statistic counts such moves.
immediate (CR) block cleanout app5350.00.1 Number of times cleanout records are applied immediately during consistent-read requests
immediate (CURRENT) block cleanou4,6840.10.8 Number of times cleanout records are applied immediately during current gets. Compare this statistic with "deferred (CURRENT) block cleanout applications"
index fast full scans (full) 1240.00.0Number of fast full scans initiated for full segments
index fetch by key733,177 11.3120.9
index scans kdiixs1220,326 3.436.3
leaf node 90-10 splits185 0.00.0
leaf node splits7780.0 0.1Number of times an index leaf node was split because of the insertion of an additional value
logons cumulative1,361 0.00.2Total number of logons since the instance started. Useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.
messages received17,146 0.32.8Number of messages received between background processes
messages sent17,1460.3 2.8Number of messages sent between background processes
no buffer to keep pinned count00.00.0Number of times a visit to a buffer attempted, but the buffer was not found where expected. Like "buffer is not pinned count" and "buffer is pinned count", this statistic is useful only for internal debugging purposes.
no work - consistent read gets991,14015.2163.4 Number consistent gets that require neither block cleanouts nor rollbacks.
opened cursors cumulative 62,7371.010.3Total number of cursors opened.
parse count (failures)47 0.00.0
parse count (hard)2,7900.00.5 Total number of parse calls (real parses). A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate a workheap and other memory structures and then build a parse tree.
parse count (total)64,788 1.010.7Total number of parse calls (hard and soft). A soft parse is a check on an object already in the shared pool, to verify that the permissions on the underlying object have not changed.
parse time cpu2,2540.0 0.4Total CPU time used for parsing (hard and soft) in 10s of milliseconds
parse time elapsed2,428 0.00.4Total elapsed time for parsing, in 10s of milliseconds. Subtract "parse time cpu" from the this statistic to determine the total waiting time for parse resources.
physical read IO requests 11,7540.21.9
physical reads20,4310.3 3.4Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
physical reads cache20,376 0.33.4
physical reads cache prefetch8,6770.11.4
physical reads direct55 0.00.0Number of reads directly from disk, bypassing the buffer cache. For example, in high bandwidth, data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.
physical reads direct temporary t00.00.0
physical reads prefetch warmup1,0880.00.2
physical write IO requests 9,1680.11.5
physical writes19,0240.3 3.1Total number of data blocks written to disk. This number equals the value of "physical writes direct" plus all writes from buffer cache.
physical writes direct409 0.00.1Number of writes directly to disk, bypassing the buffer cache (as in a direct load operation)
physical writes direct (lob) 710.00.0
physical writes direct temporary2830.00.1
physical writes from cache 18,6150.33.1
physical writes non checkpoint5,3840.10.9 Number of times a buffer is written for reasons other than advancement of the checkpoint. Used as a metric for determining the I/O overhead imposed by setting the FAST_START_IO_TARGET parameter to limit recovery I/Os. Essentially this statistic measures the number of writes that would have occurred had there been no checkpointing. Subtracting this value from "physical writes" gives the extra I/O for checkpointing.
process last non-idle time 1,199,225,404,11818,414,492.4############ The last time this process executed
recursive calls2,005,496 30.8330.6Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
recursive cpu usage19,547 0.33.2Total CPU time used by non-user calls (recursive calls). Subtract this value from "CPU used by this session" to determine how much CPU time was used by the user calls.
redo blocks written210,944 3.234.8Total number of redo blocks written. This statistic divided by "redo writes" equals number of blocks per write.
redo buffer allocation retries320.00.0Total number of retries necessary to allocate space in the redo buffer. Retries are needed either because the redo writer has fallen behind or because an event such as a log switch is occurring.
redo entries293,9544.5 48.5Number of times a redo entry is copied into the redo log buffer
redo log space requests270.00.0 Number of times the active log file is full and Oracle must wait for disk space to be allocated for the redo log entries.
redo log space wait time510.00.0 Total elapsed waiting time for "redo log space requests" in 10s of milliseconds
redo ordering marks0 0.00.0Number of times that a system change number was allocated to force a redo record to have an higher SCN than a record generated in another thread using the same block
redo size101,745,2521,562.3 16,770.3Total amount of redo generated in bytes
redo synch time5960.0 0.1Elapsed time of all "redo synch writes" calls in 10s of milliseconds
redo synch writes1,158 0.00.2Number of times a change being applied to the log buffer must be written out to disk due to a commit. The log buffer is a circular buffer that LGWR periodically flushes. Usually, redo that is generated and copied into the log buffer need not be flushed out to disk immediately.
redo wastage2,718,93641.8 448.2Number of bytes wasted because redo blocks needed to be written before they are completely full. Early writing may be needed to commit transactions, to be able to write a database buffer, or to switch logs.
redo write time1,8340.0 0.3Total elapsed time of the write from the redo log buffer to the current redo log file in 10s of milliseconds
redo writer latching time 60.00.0Elapsed time in 10s of milliseconds needed by LWGR to obtain and release each copy latch
redo writes9,3790.1 1.6Total number of writes by LGWR to the redo log files. redo blocks written divided by this statistic equals the number of blocks per write
rollback changes - undo records a80.00.0Number of undo records applied to user-requested rollback changes (not consistent-read rollbacks)
rollbacks only - consistent read1,0410.00.2 Number of consistent gets that require only block rollbacks, no block cleanouts.
rows fetched via callback 379,5575.862.6Rows fetched via callback. Useful primarily for internal debugging purposes.
session connect time 1,199,225,404,11818,414,492.4############ The connect time for the session in 10s of milliseconds.
session logical reads 2,961,14345.5488.1The sum of "db block gets" plus "consistent gets"
session pga memory2,005,580 30.8330.6Current PGA size for the session.
session pga memory max 9,983,944153.31,645.6Peak PGA size for the session.
session uga memory29,725,856 456.54,899.6Current UGA size for the session.
session uga memory max 636,437,7049,772.7104,901.6Peak UGA size for a session.
shared hash latch upgrades - no w203,4943.133.5
sorts (memory)74,1781.1 12.2Number of sort operations that were performed completely in memory and did not require any disk writes You cannot do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.
sorts (rows)5,990,30792.0 987.4Total number of rows sorted
summed dirty queue length 10.00.0The sum of the dirty LRU queue length after every write request. Divide by write requests to get the average queue length after write completion.
switch current to new buffer 2,1800.00.4Number of times the CURRENT block moved to a different buffer, leaving a CR block in the original buffer
table fetch by rowid607,7829.3100.2 Number of rows that are fetched using a ROWID (usually recovered from an index)
table fetch continued row3680.00.1 Number of times a chained or migrated row is encountered during a fetch
table scan blocks gotten666,37810.2109.8 During scanning operations, each row is retrieved sequentially by Oracle. This statistic counts the number of blocks encountered during the scan.
table scan rows gotten 68,442,3921,051.011,281.1Number of rows that are processed during scanning operations
table scans (long tables) 100.00.0Long (or conversely short) tables can be defined as tables that do not meet the short table criteria as described in table scans (short tables)
table scans (short tables) 78,3771.212.9Long (or conversely short) tables can be defined by optimizer hints coming down into the row source access layer of Oracle. The table must have the CACHE option set.
transaction rollbacks5 0.00.0Number of transactions being successfully rolled back
undo change vector size 32,242,540495.15,314.4
user I/O wait time2,101 0.00.4
user calls6,2710.1 1.0Number of user calls such as login, parse, fetch, or execute When determining activity, the ratio of user calls to RPI calls, give you an indication of how much internal work gets generated as a result of the type of requests the user is sending to Oracle.
user commits5,9230.1 1.0Number of user commits. When a user commits a transaction, the redo generated that reflects the changes made to database blocks must be written to disk. Commits often represent the closest thing to a user transaction rate.
user rollbacks1440.0 0.0Number of times users manually issue the ROLLBACK statement or an error occurs during a userā€™s transactions
workarea executions - optimal24,6580.44.1
write clones created in backgroun00.00.0Number of times a background process clones a CURRENT buffer that is being written. The clone becomes the new, accessible CURRENT buffer, leaving the original buffer (now the clone) to complete writing.
write clones created in foregroun160.00.0Number of times a foreground process clones a CURRENT buffer that is being written. The clone becomes the new, accessible CURRENT buffer, leaving the original buffer (now the clone) to complete writing.

Tuning Tips

enqueue conversions - enqueue conversions is how many times an session is holding a lock in one mode and then wants to change the mode (for example, first the session is doing an SELECT
* FROM emp FOR UPDATE and then a UPDATE emp SET sal=9876 WHERE empno=7839).

enqueue releases - enqueue releases shows how many times an enqueue was released (freed).

enqueue requests - enqueue requests minus enqueue releases shows how many locks that are held just now (which is equal to the number of rows in V$LOCK).

enqueue timeouts - Shows the total number of enqueue operations (get and convert) that timed out before they could complete. Indicates possible enqueue contention.

enqueue waits - Shows how many times a session had to wait for an enqueue. Indicates possible enqueue contention.

parse count (hard) - Too high value of this statistic could indicate that you should consider tuning of your SQL statements. See Top SQL section for details.

redo log space requests - Such space is created by performing a log switch. Log files that are small in relation to the size of the SGA or the commit rate of the work load can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing. Also examine the log file space and log file space switch wait events. See here some tuning tips.

redo log space wait time - If this statistic is high, you should consider tuning your redo log. See here some tuning tips.

table fetch by rowid - This occurrence of table scans usually indicates either non-optimal queries or tables without indexes. Therefore, this statistic should increase as you optimize queries and provide indexes in the application.

table fetch continued row - Retrieving rows that span more than one block increases the logical I/O by a factor that corresponds to the number of blocks than need to be accessed. Exporting and re-importing may eliminate this problem. Evaluate the settings for the storage parameters PCTFREE and PCTUSED. This problem cannot be fixed if rows are larger than database blocks (for example, if the LONG datatype is used and the rows are extremely large).

table scan blocks gotten - This statistic tells you the number of database blocks that you had to get from the buffer cache for the purpose of scanning. Compare this value with the value of consistent gets to determine how much of the consistent read activity can be attributed to scanning.



[Content]

I/O Activity

Input/Output Activity statistics for the instance are listed in the following sections/formats:
  • Tablespace IO statistics for database ordered by total IO per tablespace.
  • File IO statistics for database ordered alphabetically by tablespace, filename.

Tablespace I/O Activity

Tablespace Reads Average Reads/s Average Read (ms) Average Blocks/Read Writes Average Writes/s Buffer Waits Average Buffer Wait Time(ms)
SYSAUX6,37001.61.24,348 000.0
SYSTEM5,28602.02.31,585 000.0
TOOLS1100.01.01,831 000.0
UNDOTBS11100.01.01,368 0110.0
TEMP3400.68.3250 00.0
USERS4201.71.4110 00.0

Datafile I/O Activity

Tablespace Datafile Reads Average Reads/s Average Read (ms) Max Read Bucket Average Blocks/Read Writes Average Writes/s Buffer Waits Average Buffer Wait Time(ms)
SYSAUX /debian-cd/oracle10g/oradata/orasvs10/sysaux01.dbf6,370 01.6###1.24,34800
SYSTEM /debian-cd/oracle10g/oradata/orasvs10/system01.dbf5,286 02.0###2.31,58500
TEMP /debian-cd/oracle10g/oradata/orasvs10/temp01.dbf340 0.618.32500
TOOLS /debian-cd/oracle10g/oradata/orasvs10/tools01.dbf11 00.01.01,83100
UNDOTBS1 /debian-cd/oracle10g/oradata/orasvs10/undotbs01.dbf11 00.01.01,368011 0.0
USERS /debian-cd/oracle10g/oradata/orasvs10/users01.dbf42 01.7161.41100

Tuning Tips

Note that Oracle considers average read times of greater than 20 ms unacceptable. If a datafile consistently has average read times of 20 ms or greater then a number of possible approaches can be followed:

  • A database with no user SQL being run generates little or no I/O. Ultimately all I/O generated by a database is directly or indirectly due to the nature and amount of user SQL being submitted for execution. This means that it is possible to limit the I/O requirements of a database by controlling the amount of I/O generated by individual SQL statements. This is accomplished by tuning SQL statements so that their execution plans result in a minimum number of I/O operations. Typically in a problematic situation there will only be a few SQL statements with suboptimal execution plans generating a lot more physical I/O than necessary and degrading the overall performance for the database.
  • Using memory caching to limit I/O - The amount of I/O required by the database is limited by the use of a number of memory caches e.g. the Buffer Cache, the Log Buffer, various Sort Areas etc. Increasing the Buffer Cache, up to a point, results in more buffer accesses by database processes (logical I/Os) being satisfied from memory instead of having to go to disk (physical I/Os). With larger Sort Areas in memory, the likelihood of them being exhausted during a sorting operation and having to use a temporary tablespace on disk is reduced.
  • Tuning the size of multiblock I/O - The size of individual multiblock I/O operations can be controlled by instance parameters. Up to a limit, multiblock I/Os are executed faster when there are fewer larger I/Os than when there are more smaller I/Os.
  • If the tablespace contains indexes, another option is to compress the indexes so that they require less space and hence, less I/O.
  • Optimizing I/O at the Operating System level - This involves making use of I/O capabilities such as Asynchronous I/O or using Filesystems with advanced capabilities such as Direct I/O (bypassing the Operating System's File Caches). Another possible action is to raise the limit of maximum I/O size per transfer.
  • Balancing the database I/O by usage of Striping, RAID, SAN or NAS - This approach relies on storage technologies such as Striping, RAID, Storage Area Networks (SAN) and Network Attached Storage (NAS) to automatically load balance database I/O across multiple available physical disks in order to avoid disk contention and I/O bottlenecks when there is still available unused disk throughput in the storage hardware.
  • Database I/O by manual placement of database files across different filesystems, controllers and physical devices - This is an approach used in the absence of advanced modern storage technologies. Again the aim is to distribute the database I/O so that no single set of disks or controller becomes saturated from I/O requests when there is still unused disk throughput. It is harder to get right than the previous approach and most often less successful.
  • Reducing the data volumes of the current database by moving older data out.
  • Investing in more modern and faster hardware.


[Content]

Buffer Cache Activity

Buffer Pools Statistics

Pools D: default pool, K: keep pool, R: recycle pool

Pool Number of Buffers Cache Hit % Buffer Gets Physical Reads Physical Writes Free Buffer Waits Write Complete Waits Buffer Busy Waits
D 36,573 99 2,960,086 20,376 18,615 0 0 11

Buffer Wait Statistics

Class Waits Total Wait Time (s) Average Time (ms)
undo header 11 0 0

Tuning Tips

Consider tuning your buffer cache, if your system shows any of the following:

You could refer to Wait Events tuning section to find some tuning tips.



[Content]

Enqueue Activity

An enqueue is simply a locking mechanism. This section is very useful and must be used when the wait event "enqueue" is listed in the "Top 5 timed events".


Enqueue Activity section not found.



[Content]

Rollback Segments Information

Rollback Segments Activity

A high value for "Pct Waits" suggests more rollback segments may be required.

RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed.

RBS No Trans Table Gets Pct Waits Undo Bytes Written Wraps Shrinks Extends
0 244.0 0.00 0 0 0 0
1 7,094.0 0.04 8,802,700 21 5 4
2 6,819.0 0.03 7,170,434 17 4 5
3 7,076.0 0.06 8,488,130 24 3 4
4 9,937.0 0.02 8,268,660 33 3 14
5 371.0 0.00 1,660 0 1 0
6 353.0 0.00 0 0 0 0
7 353.0 0.00 0 0 0 0
8 353.0 0.00 0 0 0 0
9 353.0 0.00 0 0 0 0
10 353.0 0.00 0 0 0 0

Rollback Segments Storage

Optimal Size should be larger than Avg Active.

RBS No Segment Size Avg Active Optimal Size Maximum Size
0 385,024 0 385,024
1 3,268,608 437,365 3,334,144
2 4,317,184 496,581 4,317,184
3 3,268,608 470,550 14,671,872
4 2,220,032 389,620 2,220,032
5 188,416 0 319,488
6 122,880 0 122,880
7 122,880 0 122,880
8 122,880 0 122,880
9 122,880 0 122,880
10 122,880 0 122,880

Undo Segments Summary

Undo segment block stats:
uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
eS - expired Stolen, eR - expired Released, eU - expired reUsed

Undo TS# Undo Blocks Num Trans Max Qry Len (s) Max Tx Concurcy Snapshot Too Old Out of Space uS/uR/uU/
eS/eR/eU
1 4,973 12,231 0 4 0 0 0/0/0/0/0/0

Undo Segment Statistics

End Time Undo Blocks Num Trans Max Qry Len (s) Max Tx Concurcy Snapshot Too Old Out of Space uS/uR/uU/
eS/eR/eU
05-Mar 10:42 5 45 0 1 0 0 0/0/0/0/0/0
05-Mar 10:32 4 59 0 1 0 0 0/0/0/0/0/0
05-Mar 10:22 2 63 0 1 0 0 0/0/0/0/0/0
05-Mar 10:12 7 54 0 2 0 0 0/0/0/0/0/0
05-Mar 10:02 85 219 0 2 0 0 0/0/0/0/0/0
05-Mar 09:52 83 90 0 2 0 0 0/0/0/0/0/0
05-Mar 09:42 6 50 0 1 0 0 0/0/0/0/0/0
05-Mar 09:32 3 71 0 1 0 0 0/0/0/0/0/0
05-Mar 09:22 11 183 0 3 0 0 0/0/0/0/0/0
05-Mar 09:12 6 57 0 2 0 0 0/0/0/0/0/0
05-Mar 09:02 763 2,482 0 4 0 0 0/0/0/0/0/0
05-Mar 08:52 94 72 0 2 0 0 0/0/0/0/0/0
05-Mar 08:42 5 65 0 1 0 0 0/0/0/0/0/0
05-Mar 08:32 2 56 0 1 0 0 0/0/0/0/0/0
05-Mar 08:22 3 47 0 1 0 0 0/0/0/0/0/0
05-Mar 08:12 5 121 0 1 0 0 0/0/0/0/0/0
05-Mar 08:02 89 138 0 2 0 0 0/0/0/0/0/0
05-Mar 07:52 90 69 0 2 0 0 0/0/0/0/0/0
05-Mar 07:42 6 64 0 1 0 0 0/0/0/0/0/0
05-Mar 07:32 4 58 0 2 0 0 0/0/0/0/0/0
05-Mar 07:22 4 49 0 2 0 0 0/0/0/0/0/0
05-Mar 07:12 6 133 0 1 0 0 0/0/0/0/0/0
05-Mar 07:02 106 149 0 3 0 0 0/0/0/0/0/0
05-Mar 06:52 91 72 0 2 0 0 0/0/0/0/0/0
05-Mar 06:42 5 61 0 1 0 0 0/0/0/0/0/0
05-Mar 06:32 2 57 0 1 0 0 0/0/0/0/0/0
05-Mar 06:22 3 44 0 1 0 0 0/0/0/0/0/0
05-Mar 06:12 8 153 0 2 0 0 0/0/0/0/0/0
05-Mar 06:02 88 143 0 3 0 0 0/0/0/0/0/0
05-Mar 05:52 79 66 0 2 0 0 0/0/0/0/0/0
05-Mar 05:42 5 64 0 1 0 0 0/0/0/0/0/0
05-Mar 05:32 2 57 0 1 0 0 0/0/0/0/0/0
05-Mar 05:22 1 28 0 1 0 0 0/0/0/0/0/0
05-Mar 05:12 7 151 0 2 0 0 0/0/0/0/0/0

Rollback Tuning Tips

If your database is configured to use Auto Undo managment, you could ignore this section.

  • It is recommended to have at least 1 rollback segment for every 4 transactions.
  • One large rollback segment is recommended for long running queries.
  • The usage of SYSTEM tablespace for the rollback segments should be avoided. Create a separate tablespace for the rollback segments.
  • The dynamic expansion and reduction of rollback space should be avoided. You should to adjust OPTIMAL setting of your rollback segments. For a system that executes long running transactions frequently, OPTIMAL should be large so that Oracle does not have to shrink and allocate extents frequently. Also, for a system that executes long queries on active database, OPTIMAL should be large to avoid "snapshot too old" ORA-1555 errors. OPTIMAL should be smaller for a system that mainly executes short transactions and queries so that the rollback segments remain small enough to be cached in memory, thus improving system performance.


[Content]

Latch Information

Latch Activity

"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests.
"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests.
"Pct Misses" for both should be very close to 0.0

Latch Name Get Requests Pct Get Miss Avg Slps/Miss Wait Time (s) NoWait Requests Pct NoWait Miss
Consistent RBA9,3900.0 00
FAL subheap alocation0 020.0
FIB s.o chain latch4 0.000
FOB s.o list latch431 0.000
In memory undo latch29,264 0.007,8940.0
JOX SGA heap latch396 0.000
JS mem alloc latch4 0.0020.0
JS queue access latch4 0.000
JS queue state obj latch 457,9520.000
JS slv state obj latch4 0.000
KTF sga enqueue2120.0 020,3480.0
KWQMN job cache list lat 2400.000
KWQP Prop Status230.0 00
MQL Tracking Latch0 01,2850.0
Memory Management Latch 317,0700.0021,1440.0
NLS data objects40.0 00
PL/SQL warning settings 1,4210.000
SQL memory manager latch 410.0021,1410.0
SQL memory manager worka 1,426,8800.000
SWRF Alerted Metric Elem 206,6000.000
Shared B-Tree520.0 00
XDB unused session pool1 0.000
active checkpoint queue 29,7920.0020.0
active service list107,662 0.000
address list10.0 00
alert log latch0 020.0
archive control1810.0 00
begin backup scn array66 0.000
cache buffer handles9,721 0.000
cache buffers chains6,044,2560.00 35,5360.0
cache buffers lru chain77,0440.01.00 46,9540.0
channel handle pool latc 5250.000
channel operations paren 213,8650.000
checkpoint queue latch 474,8790.0016,0220.0
child cursor hash table 26,2650.000
client/application info 4,4920.000
commit callback allocati 20.000
compile environment latc 3,5460.000
cursor bind value captur 2,1000.004,2400.0
dictionary lookup50.0 00
dml lock allocation46,123 0.000
dummy allocation2,721 0.000
enqueue hash chains1,117,742 0.000
enqueues1,037,7330.0 00
event group latch262 0.000
file cache latch1,557 0.000
global KZLD latch for me 140.000
hash table column usage 1,4910.0075,9960.0
hash table modification 1,7370.0020.0
i/o slave adaptor0 020.0
internal temp table obje 60.000
job workq parent latch0 02,1960.0
job_queue_processes free 10.0020.0
job_queue_processes para 4,3350.000
kmcptab latch40.0 00
kmcpvec latch0 040.0
ksfv messages0 040.0
ksuosstats global area 4,4240.000
ktm global data2420.0 00
kwqbsn:qsga460.0 00
kwqbsn:qxl0 020.0
lgwr LWN SCN28,1850.0 00
library cache 2,303,3500.01.201,9070.0
library cache load lock 6,5700.000
library cache lock378,898 0.01.000
library cache lock alloc 20,9890.000
library cache pin1,166,794 0.000
library cache pin alloca 21,5890.000
list of block allocation 2,7530.000
loader state object free 4180.000
longop free list parent 3880.004080.0
message pool operations2 0.000
messages406,9950.0 00
mostly latch-free SCN28,185 0.000
multiblock read objects 1,8120.0020.0
name-service memory obje 0020.0
name-service namespace o 0020.0
ncodef allocation latch 1,0370.000
object queue header heap 3,5050.000
object queue header oper 431,5580.000
object stats modificatio 3,3100.0020.0
parallel query alloc buf 8,4560.000
parameter table allocati 2,7210.0020.0
post/wait queue1,6760.0 09900.0
presentation list10.0 00
process allocation262 0.002620.0
process group creation523 0.000
qm_init_sga10.0 00
qmn state object latch424 0.000
qmn task queue latch636 0.000
redo allocation54,0800.01.00 293,9770.0
redo copy2 0.00294,0070.0
redo writing 102,3950.000
row cache objects2,296,6190.002,397 0.0
sequence cache1,0610.0 00
session allocation1,170,016 0.000
session idle bit14,728 0.000
session switching1,037 0.000
session timer21,7950.0 00
shared pool 1,135,7890.01.000
simulator hash latch108,406 0.000
simulator lru latch1,241 0.008070.0
slave class2480.0 040.0
slave class create992 1.81.010
sort extent pool1,695 0.000
state object free list36 0.000
statistics aggregation 2,5200.000
temporary table state ob 20.000
threshold alerts latch 2,5690.000
trace latch20.0 00
transaction allocation 2,5300.000
transaction branch alloc 1,0370.000
undo global data52,163 0.000
user lock2,2510.0 00
vecio buf des0 020.0
virtual circuit buffers19 0.000
virtual circuit queues12 0.000
virtual circuits40.0 00

This section is particularly useful for determining latch contention on an instance. Latch contention generally indicates resource contention and supports indications of it in other sections.

Latch contention is indicated by a Pct Miss of greater than 1.0% or a relatively high value in Avg Sleeps/Miss.
While each latch can indicate contention on some resource, the more common latches to watch are:

- cache buffer chains: Contention on this latch confirms a hot block issue.

- shared pool: Contention on this latch in conjunction with reloads in the SQL Area of the library cache section indicates that the shared pool is too small. Contention on this latch indicates that one of the following is happening:

- The library cache, and hence, the shared pool is too small.

- Literal SQL is being used.

- On versions 8.1.7.2 and higher, session_cached_cursors might need to be set.

Latch Sleep Breakdown

Latch Name Get Requests Misses Sleeps Spin & Sleeps 1->4
redo allocation 54,080 24 24 0/24/0/0
slave class create 992 18 18 0/18/0/0
library cache 2,303,350 13 15 0/12/0/1
cache buffers lru chain 77,044 3 3 0/3/0/0
library cache lock 378,898 1 1 0/1/0/0
shared pool 1,135,789 1 1 0/1/0/0

Tuning Tips

Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.

A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).

List of latches that are of most concern to a DBA

  • Cache buffers chains latch - This latch is needed when user processes try to scan the SGA for database cache buffers. Contention in this latch might be related with the Buffer cache size, but it might be present due to a "hot block" (meaning a block highly accessed). Before of incrementing the parameter DB_BLOCK_BUFFER check that specific blocks are not causing the contention avoiding memory wasting.
  • Cache buffers LRU chain latch - This latch is needed when user processes try to scan the LRU (least recently used) chain containing all the dirty blocks in the buffer cache. Consider to have Multiple Buffer pools.
  • Redo allocation latch - This latch controls the allocation of space for redo entries in the redo log buffer. There is one redo allocation latch per instance. You need to consider to increase the size of the LOG_BUFFER or reduce the load of the log buffer using NOLOGGING features when possible.
  • Redo writing latch - This unique latch prevent multiple processes posting the LGWR process requesting log switch simultaneously. A process that needs free space must acquire the latch before of deciding whether to post the LGWR to perform a write, execute a log switch or just wait.
  • Redo copy latch - This latch is used to write redo records into the redolog buffer. On multi-cpu systems, contention can be reduced by increasing the value of LOG_SIMULTANEOUS_COPIES (Hidden in Oracle8i).
  • Row cache objects latch - This latch comes into play when user processes are attempting to access the cached data dictionary values. In order to reduce contention for this latch, we need to tune the data dictionary cache. This basically means increasing the size of the shared pool SHARED_POOL_SIZE as the dictionary cache is a part of the shared pool.
  • Library cache latch - It serialize access to the objects in the library cache. Every time a SQL statements, PL/SQL blocks or a stored objects (Procedures, packages, functions, triggers) is executed this latch is acquired. The first resource to reduce contention on this latch is to ensure that the application is reusing as much as possible SQL statement representation. If the application is already tuned the SHARED_POOL_SIZE can be increased. Be aware that if the application is not using appropriately the library cache the contention might be worst with a larger structure to be handled.
  • Shared pool latch - This latch protects the allocation of memory in the library cache. There is just one latch to the entire database. The first resource to reduce contention on this latch is to ensure that the application is reusing as much as possible SQL statement representation. If the application is already tuned the SHARED_POOL_SIZE can be increased. Be aware that if the application is not using appropriately the library cache the contention might be worst with a larger structure to be handled.

[Content]

Top Segment Activity

Top Logical Reads Segments section not found.

Top Physical Reads Segments section not found.

Top Buffer Waits Segments section not found.

Top Row Lock Waits Segments section not found.



[Content]

Dictionary Cache Statistics

This is an interesting section to monitor but about which you can do very little as the only way to change the size of the dictionary cache is to change the shared pool size as the dictionary cache is a percentage of the shared pool.

Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
dc_awr_control 1,148 0.0 0 37 1
dc_constraints 60 33.3 0 60 1
dc_files 20 0.0 0 0 5
dc_global_oids 202,017 0.0 0 0 144
dc_histogram_data 4,115 14.4 0 812 1,363
dc_histogram_defs 33,294 28.0 0 4,609 2,613
dc_object_ids 224,004 0.2 0 5 1,488
dc_objects 14,863 7.3 0 21 1,989
dc_profiles 1,116 0.0 0 0 1
dc_rollback_segments 7,410 0.0 0 0 22
dc_segments 9,617 5.4 0 119 1,523
dc_sequences 17 17.6 0 17 8
dc_table_scns 3 100.0 0 0 0
dc_tablespace_quotas 3 0.0 0 3 3
dc_tablespaces 18,568 0.0 0 0 7
dc_usernames 4,508 0.2 0 0 22
dc_users 235,000 0.0 0 0 75
outstanding_alerts 756 0.0 0 0 6

You could review Shared Pool Tuning section to find some tips on tuning shared pool.



[Content]

Library Cache Activity

"Pct Misses" should be very low.

Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invalidations
BODY 11,860 0.2 16,074 0.1 1 0
CLUSTER 145 1.4 474 0.4 0 0
INDEX 487 75.6 2,960 25.4 0 0
JAVA DATA 18 0.0 0 0 0
SQL AREA 44,098 5.7 451,015 1.1 446 233
TABLE/PROCEDURE 6,593 6.3 97,230 2.6 977 0
TRIGGER 287 3.5 2,001 0.5 1 0

Values in Pct Misses or Reloads in the SQL Area, Tables/Procedures or Trigger rows indicate that the shared pool may be too small. To confirm this, consistent values (not sporadic) in Pct Misses or Reloads in the Index row indicate that the buffer cache is too small. (No longer available in 9i.) Values in Invalidations in the SQL Area indicate that a table definition changed while a query was being run against it or a PL/SQL package being used was recompiled.

You could review Shared Pool Tuning section to find some tips on tuning shared pool.



[Content]

SGA Memory Summary

SGA Regions Size in Bytes
Database Buffers 306,184,192
Fixed Size 779,260
Redo Buffers 262,144
Variable Size 137,370,628
sum 444,596,224


[Content]

INIT.ORA Parameter Settings

Parameter Name Begin Value End Value (if different)
background_dump_dest /debian-cd/oracle10g/product/10.1
compatible10.1.0.2.0
control_files /debian-cd/oracle10g/oradata/oras
core_dump_dest /debian-cd/oracle10g/product/10.1
db_block_size8192
db_domainleaves.ru
db_file_multiblock_read_count16
db_nameorasvs10
db_recovery_file_dest /debian-cd/oracle10g/product/10.1
db_recovery_file_dest_size 2147483648
db_unique_nameorasvs10g
dispatchers(PROTOCOL=TCP) (SERVICE=orasvs10X
job_queue_processes10
nls_languageRUSSIAN
nls_territoryRUSSIA
open_cursors300
pga_aggregate_target 147849216
processes250
remote_login_passwordfile EXCLUSIVE
sga_target444596224
sort_area_size65536
undo_managementAUTO
undo_tablespaceUNDOTBS1
user_dump_dest /debian-cd/oracle10g/product/10.1


[Content]

Report generated at 1/28/2005 5:19:12 PM by WISE Analyzer Version 1.0.25
Copyright (C) 1996-2009 by Rampant TechPress, All rights reserved.