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

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 Cache	292M
Shared Pool Size	116M
Std Block Size	8K
Log Buffer	256K

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

Ratio	Percentage	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 CPU	87.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 time 177 66.07 CPU used by this session

control file parallel write 21,308 53 19.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,916 16 6.08 The 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 startup 248 9 3.17 Wait for a shared server, Dispatcher, or other background process to start.

log file sync 954 6 2.29 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.

[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 write	21,308	0	53	2	3.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
db file sequential read	10,916	0	16	1	1.8	The 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 startup	248	0	9	34	0.0	Wait for a shared server, Dispatcher, or other background process to start.
log file sync	954	0	6	6	0.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 read	875	0	5	5	0.1	The 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	18	0	1	38	0.0	The process waits for a latch that is currently busy (held by another process).
control file sequential read	15,281	0	1	0	2.5	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 switch completion	27	0	1	19	0.0	Waiting for a log switch to complete.
latch: library cache	15	0	0	8	0.0
log file parallel write	9,377	0	0	0	1.5	Writing redo records to the redo log files from the log buffer.
LGWR wait for redo copy	45	1	0	2	0.0	LFWR background process waits for redo copy latches.
log buffer space	9	0	0	8	0.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 write	22	0	0	3	0.0	Waiting 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 write	45	0	0	1	0.0	This 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,592	0	0	0	1.4	This 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 client	109	0	0	0	0.0	The 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	3	0	0	7	0.0
log file sequential read	22	0	0	1	0.0	Waiting for the read from this logfile to return. This is used to read redo records from the log file.
Queue Monitor Task Wait	212	0	0	0	0.0
SQL*Net break/reset to clien	32	0	0	0	0.0	The 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 reply	61	0	0	0	0.0	This 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	1	0	0	4	0.0
buffer busy waits	11	0	0	0	0.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 write	1	0	0	1	0.0	This event is used to wait for the writing of the file headers.
direct path write	185	0	0	0	0.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 allocation	24	0	0	0	0.0
direct path read	55	0	0	0	0.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 temp	44	0	0	0	0.0
ksfd: async disk IO	15	0	0	0	0.0
latch: shared pool	1	0	0	0	0.0
jobq slave wait	63,517	62,383	190,587	3001	10.5
Queue Monitor Wait	2,120	2,120	63,401	29906	0.3
virtual circuit status	2,171	2,170	63,375	29191	0.4	The session waits for a virtual circuit to return a message type indicated by status.
wakeup time manager	214	2	55,495	######	0.0
SQL*Net message from client	1,633	0	5,611	3436	0.3	The server process (foreground process) waits for a message from the client process to arrive.
SQL*Net more data from clien	261	0	0	1	0.0	The server is performing another send to the client. The previous operation was also a send to the client.
SQL*Net message to client	1,640	0	0	0	0.3	The 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 write	21,306	0	53	2	3.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 startup	248	0	9	34	0.0	Wait for a shared server, Dispatcher, or other background process to start.
control file sequential read	2,470	0	0	0	0.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,378	0	0	0	1.5	Writing redo records to the redo log files from the log buffer.
LGWR wait for redo copy	45	1	0	2	0.0	LFWR background process waits for redo copy latches.
db file sequential read	50	0	0	1	0.0	The 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	22	0	0	3	0.0	Waiting 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 completion	4	0	0	13	0.0	Waiting for a log switch to complete.
control file single write	44	0	0	1	0.0	This 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,592	0	0	0	1.4	This 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 cache	4	0	0	7	0.0
log file sequential read	22	0	0	1	0.0	Waiting for the read from this logfile to return. This is used to read redo records from the log file.
Queue Monitor Task Wait	212	0	0	0	0.0
rdbms ipc reply	60	0	0	0	0.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 waits	11	0	0	0	0.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).
latch: redo allocation	24	0	0	0	0.0
direct path read	55	0	0	0	0.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).
latch: cache buffers lru cha	1	0	0	0	0.0
direct path write	55	0	0	0	0.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: shared pool	1	0	0	0	0.0
rdbms ipc message	190,258	181,670	493,983	2596	31.4	The 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 Wait	2,120	2,120	63,401	29906	0.3
smon timer	220	207	60,956	######	0.0	This 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.

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_INTERVAL4)/(246060)
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 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

[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,736	0.3	2.9	Amount 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,736	0.3	2.9	The CPU time used when the call is started
CR blocks created	1,048	0.0	0.2	Number 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,342	0.0	0.2	Useful only for internal debugging purposes
Commit SCN cached	3	0.0	0.0	Number of times the system change number of a commit operation was cached
DB time	12,994,709	199.5	2,141.9
DBWR checkpoint buffers written	18,614	0.3	3.1	Number of buffers that were written for checkpoints
DBWR checkpoints	11	0.0	0.0	Number 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 written	0	0.0	0.0
DBWR revisited being-written buff	0	0.0	0.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 wr	1,391	0.0	0.2
DBWR transaction table writes	528	0.0	0.1	Number 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,564	0.1	0.9	Number of rollback segment blocks written by DBWR
IMU CR rollbacks	10	0.0	0.0
IMU Flushes	140	0.0	0.0
IMU Redo allocation size	3,225,924	49.5	531.7
IMU commits	4,197	0.1	0.7
IMU contention	0	0.0	0.0
IMU pool not allocated	1,947	0.0	0.3
IMU recursive-transaction flush	8	0.0	0.0
IMU undo allocation size	9,709,056	149.1	1,600.3
IMU- failed to get a private stra	1,947	0.0	0.3
SQL*Net roundtrips to/from client	1,604	0.0	0.3	Total number of Net8 messages sent to and received from the client
active txn count during cleanout	1,722	0.0	0.3
application wait time	3	0.0	0.0
background checkpoints completed	11	0.0	0.0	Number of checkpoints completed by the background process. This statistic is incremented when the background process successfully advances the thread checkpoint.
background checkpoints started	11	0.0	0.0	Number 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 timeouts	181,714	2.8	30.0
buffer is not pinned count	1,192,577	18.3	196.6	Number of times a buffer was free when visited. Useful only for internal debugging purposes.
buffer is pinned count	1,144,650	17.6	188.7	Number of times a buffer was pinned when visited. Useful only for internal debugging purposes.
bytes received via SQL*Net from c	1,143,723	17.6	188.5	Total number of bytes received from the client over Net8
bytes sent via SQL*Net to client	890,899	13.7	146.8	Total number of bytes sent to the client from the foreground processes.
calls to get snapshot scn: kcmgss	573,881	8.8	94.6	Number of times a snapshot system change number (SCN) was allocated. The SCN is allocated at the start of a transaction.
calls to kcmgas	23,441	0.4	3.9	Number of calls to routine kcmgas to get a new SCN
calls to kcmgcs	1,453	0.0	0.2	Number of calls to routine kcmgcs to get a current SCN
change write time	1,010	0.0	0.2	Elapsed 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 calls	2,458	0.0	0.4
cleanouts and rollbacks - consist	6	0.0	0.0	Number of consistent gets that require both block rollbacks and block cleanouts.
cleanouts only - consistent read	529	0.0	0.1	Number of consistent gets that require only block cleanouts, no rollbacks.
cluster key scan block gets	94,133	1.5	15.5	Number of blocks obtained in a cluster scan
cluster key scans	47,233	0.7	7.8	Number of cluster scans that were started
commit cleanout failures: callbac	44	0.0	0.0	Number of times the cleanout callback function returns FALSE
commit cleanouts	30,505	0.5	5.0	Total number of times the cleanout block at commit function was performed
commit cleanouts successfully com	30,461	0.5	5.0	Number of times the cleanout block at commit function completed successfully
commit txn count during cleanout	1,535	0.0	0.3
concurrency wait time	15	0.0	0.0
consistent changes	2,572	0.0	0.4	Number 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 gets	2,487,628	38.2	410.0	Number of times a consistent read was requested for a block.
consistent gets - examination	1,076,732	16.5	177.5
consistent gets from cache	2,487,628	38.2	410.0
cursor authentications	1,087	0.0	0.2	Number of privilege checks conducted during execution of an operation
data blocks consistent reads - un	2,493	0.0	0.4	Number of undo records applied to data blocks that have been rolled back for consistent read purposes
db block changes	594,454	9.1	98.0	Closely 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 gets	473,515	7.3	78.1	Number of times a CURRENT block was requested.
db block gets direct	354	0.0	0.1
db block gets from cache	473,161	7.3	78.0
deferred (CURRENT) block cleanout	20,333	0.3	3.4	Number of times cleanout records are deferred, piggyback with changes, always current get
dirty buffers inspected	1	0.0	0.0	Number of dirty buffers found by the user process while the it is looking for a buffer to reuse
enqueue conversions	13,742	0.2	2.3	Total number of conversions of the state of table or row lock
enqueue releases	551,900	8.5	91.0	Total number of table or row locks released
enqueue requests	551,901	8.5	91.0	Total number of table or row locks acquired
enqueue timeouts	0	0.0	0.0	Total number of table and row locks (acquired and converted) that timed out before they could complete
enqueue waits	0	0.0	0.0	Total number of waits that occurred during an enqueue convert or get because the enqueue get was deferred
execute count	349,835	5.4	57.7	Total number of calls (user and recursive) that executed SQL statements
free buffer inspected	3,679	0.1	0.6	Number 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 requested	28,285	0.4	4.7	Number of times a reusable buffer or a free buffer was requested to create or load a block
heap block compress	871	0.0	0.1
hot buffers moved to head of LRU	2,175	0.0	0.4	When 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 app	535	0.0	0.1	Number of times cleanout records are applied immediately during consistent-read requests
immediate (CURRENT) block cleanou	4,684	0.1	0.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)	124	0.0	0.0	Number of fast full scans initiated for full segments
index fetch by key	733,177	11.3	120.9
index scans kdiixs1	220,326	3.4	36.3
leaf node 90-10 splits	185	0.0	0.0
leaf node splits	778	0.0	0.1	Number of times an index leaf node was split because of the insertion of an additional value
logons cumulative	1,361	0.0	0.2	Total number of logons since the instance started. Useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.
messages received	17,146	0.3	2.8	Number of messages received between background processes
messages sent	17,146	0.3	2.8	Number of messages sent between background processes
no buffer to keep pinned count	0	0.0	0.0	Number 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 gets	991,140	15.2	163.4	Number consistent gets that require neither block cleanouts nor rollbacks.
opened cursors cumulative	62,737	1.0	10.3	Total number of cursors opened.
parse count (failures)	47	0.0	0.0
parse count (hard)	2,790	0.0	0.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.0	10.7	Total 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 cpu	2,254	0.0	0.4	Total CPU time used for parsing (hard and soft) in 10s of milliseconds
parse time elapsed	2,428	0.0	0.4	Total 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,754	0.2	1.9
physical reads	20,431	0.3	3.4	Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
physical reads cache	20,376	0.3	3.4
physical reads cache prefetch	8,677	0.1	1.4
physical reads direct	55	0.0	0.0	Number 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 t	0	0.0	0.0
physical reads prefetch warmup	1,088	0.0	0.2
physical write IO requests	9,168	0.1	1.5
physical writes	19,024	0.3	3.1	Total number of data blocks written to disk. This number equals the value of "physical writes direct" plus all writes from buffer cache.
physical writes direct	409	0.0	0.1	Number of writes directly to disk, bypassing the buffer cache (as in a direct load operation)
physical writes direct (lob)	71	0.0	0.0
physical writes direct temporary	283	0.0	0.1
physical writes from cache	18,615	0.3	3.1
physical writes non checkpoint	5,384	0.1	0.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,118	18,414,492.4	############	The last time this process executed
recursive calls	2,005,496	30.8	330.6	Number 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 usage	19,547	0.3	3.2	Total 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 written	210,944	3.2	34.8	Total number of redo blocks written. This statistic divided by "redo writes" equals number of blocks per write.
redo buffer allocation retries	32	0.0	0.0	Total 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 entries	293,954	4.5	48.5	Number of times a redo entry is copied into the redo log buffer
redo log space requests	27	0.0	0.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 time	51	0.0	0.0	Total elapsed waiting time for "redo log space requests" in 10s of milliseconds
redo ordering marks	0	0.0	0.0	Number 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 size	101,745,252	1,562.3	16,770.3	Total amount of redo generated in bytes
redo synch time	596	0.0	0.1	Elapsed time of all "redo synch writes" calls in 10s of milliseconds
redo synch writes	1,158	0.0	0.2	Number 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 wastage	2,718,936	41.8	448.2	Number 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 time	1,834	0.0	0.3	Total elapsed time of the write from the redo log buffer to the current redo log file in 10s of milliseconds
redo writer latching time	6	0.0	0.0	Elapsed time in 10s of milliseconds needed by LWGR to obtain and release each copy latch
redo writes	9,379	0.1	1.6	Total 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 a	8	0.0	0.0	Number of undo records applied to user-requested rollback changes (not consistent-read rollbacks)
rollbacks only - consistent read	1,041	0.0	0.2	Number of consistent gets that require only block rollbacks, no block cleanouts.
rows fetched via callback	379,557	5.8	62.6	Rows fetched via callback. Useful primarily for internal debugging purposes.
session connect time	1,199,225,404,118	18,414,492.4	############	The connect time for the session in 10s of milliseconds.
session logical reads	2,961,143	45.5	488.1	The sum of "db block gets" plus "consistent gets"
session pga memory	2,005,580	30.8	330.6	Current PGA size for the session.
session pga memory max	9,983,944	153.3	1,645.6	Peak PGA size for the session.
session uga memory	29,725,856	456.5	4,899.6	Current UGA size for the session.
session uga memory max	636,437,704	9,772.7	104,901.6	Peak UGA size for a session.
shared hash latch upgrades - no w	203,494	3.1	33.5
sorts (memory)	74,178	1.1	12.2	Number 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,307	92.0	987.4	Total number of rows sorted
summed dirty queue length	1	0.0	0.0	The 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,180	0.0	0.4	Number of times the CURRENT block moved to a different buffer, leaving a CR block in the original buffer
table fetch by rowid	607,782	9.3	100.2	Number of rows that are fetched using a ROWID (usually recovered from an index)
table fetch continued row	368	0.0	0.1	Number of times a chained or migrated row is encountered during a fetch
table scan blocks gotten	666,378	10.2	109.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,392	1,051.0	11,281.1	Number of rows that are processed during scanning operations
table scans (long tables)	10	0.0	0.0	Long (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,377	1.2	12.9	Long (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 rollbacks	5	0.0	0.0	Number of transactions being successfully rolled back
undo change vector size	32,242,540	495.1	5,314.4
user I/O wait time	2,101	0.0	0.4
user calls	6,271	0.1	1.0	Number 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 commits	5,923	0.1	1.0	Number 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.

Event	Waits	Time (s)	% Total Call Time	Wait Event Description
CPU time		177	66.07	CPU used by this session
control file parallel write	21,308	53	19.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,916	16	6.08	The 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 startup	248	9	3.17	Wait for a shared server, Dispatcher, or other background process to start.
log file sync	954	6	2.29	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.