Page History
...
■ Examine high Redo Log Buffer Wait statements in the Activity tab.
Table 12-6 High Redo Log Buffer Wait
Description
Advice Use any one of the typical problem scenarios described below.
...
Your instance has spent much of its In Oracle time waiting for the group event Log Switch and Clear.
Table 12-7 High 7 High Log Switch and Clear Wait
...
Your instance has spent much of its In Oracle time waiting for RAC or OPS.
Table 12-8 High 8 High RAC/OPS Wait
Description
...
Your instance has spent much of its In Oracle time waiting for a latch.
Table 12-9 High 9 High Other Lock Wait
Description
...
Your instance has spent much of its In Oracle time waiting for the group event Background Process Wait.
Table 12-10 High 10 High Background Process Wait
Description
...
Your instance has spent much of its In Oracle time waiting for the group event Parallel Query Server Wait.
Table 12-11 High 11 High Parallel Query Server Wait
...
Your instance has spent much of its In Oracle time waiting for the group event Other Wait.
Table 12-12 High 12 High Other Wait
Description
...
Your instance has spent much of its In Oracle time waiting for database buffers.
Table 12-13 High 13 High Buffer Wait
Description
...
Your instance has spent much of its In Oracle time waiting for remote queries to complete.
Table 12-14 High 14 High Remote Query Wait
Description
...
Your instance has spent much of its In Oracle time waiting for data from the Oracle server.
Table 12-15 High 15 High Client Communication Wait
...
Your instance has spent much of its In Oracle time waiting for the group event Resource Manager Wait.
Table 12-16 High 16 High Resource Manager Wait
Description
...
Your instance has spent much of its In Oracle time waiting for MTS Wait.
Table 12-17 High 17 High MTS Wait
Description
What to do next Perform one of the following options:
...
The statement is a major consumer of Oracle resources. By tuning the statement, you may free resources needed by other statements and processes.
Table 12-18 Heavy 18 Heavy Statement
Description
...
The statement is a major consumer of Oracle resources. This statement is frequently executed with a low In Oracle time average.
Table 12-19 Frequently 19 Frequently Executed Statement
Description
...
Much of the instance In Oracle time was spent on waits (lock, I/O, Buffer, and so on) for the object.
Table 12-20 Heavily 20 Heavily Accessed Object
Description
...
Much of the instance In Oracle time is spent waiting for a lock on the table.
Table 12-21 Locked 21 Locked Object
Description
What to do next Perform one of the following options:
...
The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
Table 12-22 High 22 High Sorts on Disk
Description
...
Much of the instance I/O is spent waiting for the Undo object.
Table 12-23 High 23 High Undo Activity
Description
...
Much of the instance I/O is spent waiting for the cluster.
Table 12-24 Heavy 24 Heavy Cluster Activity
Description
...
Much of the instance time is spent waiting for a lock on the cluster.
Table 12-25 Cluster 25 Cluster Locks
Description
What to do next Examine the lock for the statement in the Activity tab.
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 12-26 26 Storage Contention On Device
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 12-27 27 Storage Contention On Device
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 12-28 28 Storage Contention On Device
...
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Table 12-29 29 Storage Contention On Device
...
As these files are considered heavy I/O consumers, it is highly recommended to place them on a separate disk without other database files. Separating the Redo/Transaction Logs files by placing them on different volumes (e.g. E:/ and F:/) may not be enough, as the storage devices (LUNs) and physical disks may be shared between several file systems and volumes.
Table 12-30 Storage 30 Storage Contention on Redo Logs and DB Files
...
As these files are considered heavy I/O consumers, it is highly recommended to place them on a separate disk without other database files. Separating the temporary tablespace files by placing them on different volumes (e.g. E:/ and F:/) may not be enough, as the storage devices (LUNs) and physical disks may be shared between several file systems and volumes.
Table 12-31 Storage 31 Storage Device on Temporary Objects
...
As these files are considered heavy I/O consumers, it is highly recommended to place them on a separate disk without other database files. Separating the undo tablespace files by placing them on different volumes (e.g. E:/ and F:/) may not be enough, as the storage devices (LUNs) and physical disks may be shared between several file systems and volumes.
Table 12-32 Heavy 32 Heavy Storage Device Holding Undo Objects
...
There are several storage devices (LUNs) allocated to the instance. However, the I/O activity is not spread evenly across these storage devices. The contention on the heavy storage devices increases the response time for the activities run on them. Such a situation can be caused by imbalanced internal database activity, contention on the storage device by other applications or an inefficient RAID policy.
Table 12-33 Unbalanced 33 Unbalanced Storage Devices Activity
...
The J2EE Caller Service is a major consumer of Oracle resources. By tuning it, you may free resources needed by other Caller Services and processes.
Table 12-34 Heavy 34 Heavy J2EE Caller Service
Description
...
The J2EE Caller Service is a major consumer of Oracle resources, issuing and exceptionally high number of SQL Statements executions. By tuning it and reducing the number of executions, you may free resources needed by other Caller Services and processes.
Table 12-35 High 35 High SQL Executions for J2EE Caller Service
...
The statement is identified with a high average time in the chosen context. By tuning the statement, you may improve the application response time.
Table 12-36 Slow 36 Slow Statement
Description
...
The J2EE Caller Service in the chosen context, spent a high amount of its resources on performing disk related full scans on the referenced object.
Table 12-37 Heavy 37 Heavy Full Scan
Description
...
The J2EE Caller Service in the chosen context, spent much of its In Oracle time waiting for the Redo Log events.
Table 12-38 High 38 High Redo Log Buffer Wait
Description
...
The J2EE Caller Service in the chosen context, spent much of its In Oracle time waiting for database buffer events.
Table 12-39 High 39 High Buffer Wait
Description
...
The J2EE Caller Service in the chosen context, spent much of its In Oracle time waiting for the waiting on sort or hash operations performed on a temporary tablespace.
Table 12-40 High 40 High Temporary I/O
Description
...
The statement is identified as a major consumer of the selected J2EE Caller's Oracle resources. By tuning the statement, you may improve the application response time.
Table 12-41 Heavy 41 Heavy Statement
Description
...
The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
Table 12-42 Sorts 42 Sorts Performed on Disk
Description
...
Some of the executions for the statement were not run in parallel; they worked serially. Oracle has reached the threshold of the MAX_PARALLEL_SERVERS and was not able to allocate parallel processes for the statement.
Table 12-43 No 43 No Parallel Processes Available
...
Your statement has spent much of its In Oracle time waiting for a remote query to complete.
Table 12-44 Bottleneck 44 Bottleneck in Remote Access
Description
...
Statement I/O is spent on scattered I/O (usually representing a full scan) on the index specified in the Object column.
Table 12-45 Heavy 45 Heavy Scattered I/O on Index
Description
...
Statement I/O is spent on sequential I/O (usually representing a range scan) on the index specified in the Object column. If the statement is DML and the index is not used in the execution plans, then the I/O represents the index maintenance overhead, caused by fetching the index blocks for update to memory.
Table 12-46 Heavy 46 Heavy Sequential I/O on Index
Description
...
Statement I/O is spent on scattered I/O (usually representing a full scan) on the table specified in the Object column.
Table 12-47 Heavy 47 Heavy Scattered I/O on Table
Description
...
Statement I/O is spent on sequential I/O (usually representing table access by rowid following an index range scan) on the table specified in the Object column.
Table 12-48 Heavy 48 Heavy Sequential I/O on Table
Description
...
Statement I/O is spent on direct I/O (usually representing the SQL Loader in the direct path), on the object specified in the Object column.
Table 12-49 Heavy 49 Heavy I/O Due to Direct Access
...
Statement I/O is spent on another I/O on the object specified in the Object column.
Table 12-50 Heavy 50 Heavy I/O Due to Other Access
Description
...
Much of the statement I/O is spent on waiting for a lock on the table specified in the Object column.
Table 12-51 Statement 51 Statement State Row Lock
Description
...
Lack of free buffers when trying to load blocks from a disk (Free Buffer Wait)
Table 12-52 Buffer 52 Buffer Wait Contention
Description
...
Your statement has spent much of its In Oracle time waiting for Redo Log Wait.
Table 12-53 Redo 53 Redo Log Activity
Description
...
Your statement has spent much of its In Oracle time waiting for undo.
Table 12-54 Undo 54 Undo Activity
Description
What to do next Perform one of the following options:
...
Your instance has spent much of its In Oracle time waiting for a RAC activity to complete on the object specified in the Object column.
Table 12-55 RAC 55 RAC Wait
Description
What to do next Select the findings type to see the instances consuming object RAC Wait in the Activity tab.
...
Bind sets were collected for the statement.
Table 12-56 Bind 56 Bind Variables Were Collected
...
More than one real plan was collected for the statement during the selected time frame.
Table 12-57 More 57 More Than One Real Plan Was Detected
...
The cost for the statement has changed over the last month.
Table 12-58 Costs 58 Costs Have Changed Over the Last Month
...
(indicating executions run many times).
Table 12-59 Frequently 59 Frequently Executed Statement
Description
...
Much of the stats In Oracle time is spent on CPU usage, and the average In Oracle time is high.
Table 12-60 The 60 The Average Execution Uses CPU Heavily
...
Most I/O wait on indexes is due to fetching index pages from the disk, reflecting changes made by the DML statement. The indexes do not appear in the execution plan.
Table 12-61 Heavy 61 Heavy Index Overhead
Description
...
It indicates that some bind values may lead Oracle to choose a different execution plan than others. This does not mean that when those bind values are used for the statement that their relevant plan will be used. This depends on which version of Oracle is being used, whether the statement’s plan exists in memory, and whether the init.ora parameter "_optim_peek_user_binds" is set to TRUE or FALSE.
Table 12-62 Preferable 62 Preferable Plan Detected by Oracle
Description
...
The end of fetch count is identified by the number of times the specified cursor was fully executed since the cursor was brought into the library cache. Its value is not incremented when the cursor is partially executed, either because it failed during execution or because only the first few rows produced by this cursor were fetched before the cursor was closed or re-executed.
Table 12-63 Low 63 Low End of Fetch Count
Description
...
This finding may appear when the difference between the best and worse plans, within a selected time frame, is significant in terms of in Oracle time. This indicates that some plans were used, and the best plan consumed significantly less resources of Oracle than the worse plan.
Table 12-64 Significant 64 Significant Differentiation between Best and Worse Plans
...
A newer execution plan than the one you are viewing was collected for the statement during the selected time frame, or during a later time frame.
Table 12-65 Newer 65 Newer Execution Plan Exists
Description
...
A Cartesian Join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.
Table 12-66 Cartesian 66 Cartesian Join Used
Description
...
I/O found on temporary tablespace may indicate sort operations are consuming CPU time of the statement.
Table 12-67 CPU 67 CPU Used for Sorts
Description
...
Statement 's I/O may indicate Index Scattered read operation (often full scan) on the index specified in the Object column.
Table 12-68 CPU 68 CPU Used for Index Scattered Read
...
Statement 's I/O may indicate Index Sequential read operation (often range scan) on the index specified in the Object column. If the statement is DML and the index is not used in the execution plans, then the I/O represents the index maintenance overhead, caused by fetching the index blocks for update to memory.
Table 12-69 CPU 69 CPU Used for Index Sequential Read
...
Statement 's I/O may indicate table scattered read operation (often full scan) on the table specified in the Object column.
Table 12-70 CPU 70 CPU Used for Table Scattered Read
...
Statement 's I/O may indicate a Table Sequential read operation (often ROWID access) on the table specified in the Object column.
Table 12-71 CPU 71 CPU Used for Table Sequential Read
...
Statement resources are spent performing Full table scans on the table specified in the Object column.
Table 12-72 Heavy 72 Heavy Table Full Scan
Description
...
Statement resources are spent performing the specified step.
Table 12-73 Heavy 73 Heavy step
Description
What to do next Perform one of the following options:
...
Statement resources are spent performing sort operation.
Table 12-74 Heavy 74 Heavy Sort
Description
What to do next Perform one of the following options:
...
Statement resources are spent performing hash operation.
Table 12-75 Heavy 75 Heavy Hash
Description
What to do next Perform one of the following options:
...
Statement resources are spent performing merge operation.
Table 12-76 Heavy 76 Heavy Merge
Description
What to do next Perform one of the following options:
...
Statement resources are spent performing Full index scans on the index specified in the Object column.
Table 12-77 Heavy 77 Heavy Index Full Scan
Description
...
Statement resources are spent performing Range index scans on the index specified in the Object column.
Table 12-78 Heavy 78 Heavy Index Range Scan
Description
...
Statement resources are spent performing Range index skip scans on the index specified in the Object column.
Table 12-79 Heavy 79 Heavy Index Skip Scan
Description
...
Statement resources are spent performing Cartesian join.
Table 12-80 Heavy 80 Heavy Cartesian Join
Description
...
Most of the I/O wait on indexes is due to the fetching of index pages from disk that reflect changes made by INSERT, DELETE, UPDATE, or MERGE statements. The index does not appear in the execution plan.
Table 12-81 Heavy 81 Heavy Index Overhead
Description
...
Table extensively accessed using full table scans.
Table 12-82 Extensive 82 Extensive Full Table Scan Access
Description
...
Table containing many deleted blocks is extensively accessed using full table scans.
Table 12-83 Full 83 Full Scan Reading Deleted Blocks
...
Intensive I/O wait activity on table due to a range scan carried out by an index with a bad clustering factor (mismatch between physical order of rows in table and order of ROWIDs from the index range scan leads to re-reading of table blocks).
Table 12-84 84 Index Clustering Factor Very High
...
The figure below shows an example of an index with a good clustering factor. In this example, the root is read first, followed by the first leaf page. Then the first data block that serves the first three keys matching the three rows in the data block is fetched. In this way the keys and data blocks that follow are read. The I/O operations required by this scan include five index blocks and six data blocks, which is the equivalence of 11 I/O operations.
Figure 12-1 Index 1 Index with good clustering factor (low = number of table blocks)
...
The index with the bad clustering factor starts in the same way as the index with the good clustering factor. However, when the second key in the index is read, the row for the second key in the first data block has not yet been fetched, so another block must be fetched. By the time Oracle accesses the index key matching the second row in the first table block, it has already been swapped out of memory and needs to be re-read. In the worse case scenario, I/O for the table blocks will be required for every index key. The I/O operations required by this scan include five index blocks and 16 table blocks, which is equivalence of 21 I/O operations. When the difference between the number of blocks and number of rows is great, performance can be greatly impacted.
Figure 12-2 Index 2 Index with bad clustering factor (high = number of rows)
...
Lack of free buffer space when trying to load blocks from a disk (Free Buffer wait).
Table 12-85 Buffer 85 Buffer Wait Contention
Description
...
Much of the objects (table and indexes) I/O time is spent waiting for a lock on the object specified in the Object column.
Table 12-86 Object 86 Object Or Row Lock Contention
Description
...
The object (table and indexes) spent much of its In Oracle time waiting for a RAC activity to complete on the object specified in the Object column.
Table 12-87 Bottleneck 87 Bottleneck in RAC Wait
Description
...
Access to table deteriorated as a result of chained rows.
Table 12-88 Many 88 Many Chained Rows
Description
...
A significant block change occurred since the last time the object was analyzed, for at least one of the objects related to table.
Table 12-89 Statistics 89 Statistics Not Updated on Object
Description
...
Partitions or subpartitions were added or dropped. Table was altered (columns were added).
Table 12-90 Changes 90 Changes Detected in Object Structure
...
The table is considerably larger than it was at the start of the time frame.
Table 12-91 Table 91 Table Grew Considerably
Description
...
A large percentage of In Oracle time for the object is spent accessing one partition.
Table 12-92 Partition 92 Partition Is Accessed Extensively
Description
...
The hit ratio, for at least one of the objects related to the table, is very low.
Table 12-93 Segment 93 Segment Hit Ratio Very Low
Description
...
Extensive activity on statements that were not explained.
Table 12-94 Extensive 94 Extensive Activity on Non-explained Statements
...
Extensive I/O wait was experienced, as a result of range scans on the index. Although this may be normal, it can often indicate a matching level problem, indicating that the structure of the index can be improved.
Table 12-95 Extensive 95 Extensive "Index Range Scan" Access
...
Index is extensively accessed using full index scans. This is sometimes done to avoid sorts, when the sort order matches the leading portion of the index key, or to avoid accessing table blocks, when all the columns required by the query exist in the index key.
Table 12-96 Extensive 96 Extensive “Full Index Scan” Access
...
Index is extensively accessed using fast full index scans.
Table 12-97 Extensive 97 Extensive "Fast Full Index Scan" Access
...
Index extensively accessed using index skip scans which often means that the index structure does not fit the query in the best possible way, and leads Oracle to perform heavy activity against the index
Table 12-98 Extensive 98 Extensive "Index Skip Scan" Access
...