Page History
This section includes the following topics:
...
- About the Oracle Findings view
...
- About Instance findings
...
- About Activity tab findings
...
- About SQL tab findings
...
- About Object Findings
...
About the Oracle Findings view
The Findings view displays recommendations that can be used to create a better execution plan and improve the performance of a statement. The Findings vary according to the type of operation in a statement, the Precise product and the technology.
Precise for Oracle is capable of providing additional insight into a problem by helping you focus on the item or object that is causing the problem. When you click on certain findings in the SQL tab or the Object tab, and you launch to the tab that can provide additional information on the problem, the row representing the item or object that is most likely causing the problem is highlighted. This provides a further indication as to where you should focus your analysis.
About the Highlights area
...
The What to do next area displays one or more recommended steps to identify the cause of the problem. Carefully review all data for the finding before continuing.
About the Advice area
The Advice area displays one or more recommended options to resolve or reduce the problem for the selected finding. Carefully review all data for the finding and then perform the advice that best suits your needs.
How to investigate findings
When you start investigating the findings, it is good practice to start with the finding that has the highest severity ranking in the Findings table.
To investigate a finding
...
- Identify the finding with the highest severity ranking in the Findings table.
...
- Select the finding type to view additional information on the selected type of operation.
...
- Read the Highlights and What to do next areas for the finding.
...
- After you have studied all of the information provided, read the Advice area and perform the recommendation that best suits your needs.
...
- Follow up on performance to verify that the problem was resolved.
About Instance findings
Several Dashboard tab findings exist in the table to help the user. The Dashboard tab for an instance has the following findings:■ High
- High CPU Wait
...
- High Other Host Wait
...
- High Memory Wait
...
- High Shared Pool Wait
...
- High Rollback Segment Wait
...
- High Redo Log Buffer Wait
...
- High Log Switch and Clear Wait
...
- High RAC/OPS Wait
...
- High Other Lock Wait
...
- High Background Process Wait
...
- High Parallel Query Server Wait
...
- High Buffer Wait
...
- High Other Wait
...
- High Remote Query Wait
...
- High Client Communication Wait
...
- High Resource Manager Wait
...
- High MTS Wait
...
- Heavy Statement
...
- Frequently Executed Statement
...
- Heavily Accessed Object
...
- Locked Object
...
- High Sorts on Disk
...
- High Undo Activity
...
- Heavily Accessed Cluster
...
- Locked Cluster
...
- Storage Contention on Device (Clariion)
...
- Storage Contention on Device (Symmetrix Thick)
...
- Storage Contention on Device (Symmetrix Thin)
...
- Storage Contention on Device (Symmetrix F.A.S.T. VP)
...
- Storage Contention on Redo Logs and DB Files
...
- Storage Contention on Temporary Objects
...
- Heavy Storage Device Holding Undo Objects
...
- Unbalanced Storage Devices Activity
...
- Heavy J2EE Caller Service
...
- High SQL Executions for J2EE Caller Service
...
High CPU Wait
Your instance has spent much of its In Oracle time waiting for CPU.
Table 12-1 High 1 High CPU Wait
Description
What to do next Perform one of the following options:
...
■ Identify heavy statements using CPU or Waiting for CPU and try to tune them.
High Other Host Wait
Your instance has spent much of its In Oracle time in Other Host Wait.
Table 12-2 High 2 High Other Host Wait
Description
...
Advice Other Host Wait can result from any of the following causes: asynchronous I/O, gateways, or the use of NFS and TP monitors. Check the statements and programs suffering from this state and check whether the above resources are being utilized efficiently.
High Memory Wait
Your instance has spent much of its In Oracle time waiting for memory.
Table 12-3 High 3 High Memory Wait
Description
...
■ Identify heavy statements using High Memory Wait and try to tune them.
High Shared Pool Wait
Your instance has spent much of its In Oracle time waiting for the group event Shared Pool Wait.
Table 12-4 High 4 High Shared Pool Wait
Description
...
Advice Common scenarios for this wait occur when the shared pool is either too small or too big. Verify that your shared pool is sized according to the type of application being used (cursor sharing, literals usage, and so on.)
High Rollback Segment Wait
Your instance has spent much of its In Oracle time waiting for the group event Rollback Segment Wait.
Table 12-5 High 5 High Rollback Segment Wait
Description
...
■ Change the application flow or change the rollback policy (using no logging on specific objects).
High Redo Log Buffer Wait
Your instance has spent much of its In Oracle time waiting for the Redo Log.
Table 12-6 High 6 High Redo Log Buffer Wait
Description
...
■ Examine high Redo Log Buffer Wait statements in the Activity tab.
Table 12-6 High 6 High Redo Log Buffer Wait
Description
...
Consider moving the log file to a faster, dedicated device.
Whenever the Log Buffer Space and Log File Sync events occur together, consider changing the hidden LOG_I/O_SIZE parameter.
High Log Switch and Clear Wait
Your instance has spent much of its In Oracle time waiting for the group event Log Switch and Clear.
Table 12-7 High Log Switch and Clear Wait
...
There can be other reasons for a high Log Switch and Clear wait, such as an LGWR delay where the files cannot be switched until ARCH archiving is completed. This is usually caused by the Log File Switch (archiving needed) event.
High RAC/OPS Wait
Your instance has spent much of its In Oracle time waiting for RAC or OPS.
Table 12-8 High RAC/OPS Wait
...
■ Examine Heavy objects suffering from RAC Waits in the Activity tab.
Table 12-8 High RAC/OPS Wait
Description
Advice There are two typical scenarios relevant to RAC Waits. Launch to the Dashboard RAC Database view. This view compares the selected instance with other instances in the same database. From the RAC Database view, examine each of the following issues:
...
■ If there are Objects suffering from RAC Waits, launch to the Activity tab and identify their use across Database instances.
High Other Lock Wait
Your instance has spent much of its In Oracle time waiting for a latch.
Table 12-9 High Other Lock Wait
...
Oracle latch or enqueue and follow the tuning scenario set by this event.
High Background Process Wait
Your instance has spent much of its In Oracle time waiting for the group event Background Process Wait.
Table 12-10 High Background Process Wait
...
Advice Examine the Oracle events that are grouped into the Background Processes Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Parallel Query Server Wait
Your instance has spent much of its In Oracle time waiting for the group event Parallel Query Server Wait.
Table 12-11 High Parallel Query Server Wait
...
Advice Examine the Oracle events that are grouped into the Parallel Query Server Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Other Wait
Your instance has spent much of its In Oracle time waiting for the group event Other Wait.
Table 12-12 High Other Wait
...
Advice In the Statistics tab, examine the Oracle events that are grouped as Other Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High Buffer Wait
Your instance has spent much of its In Oracle time waiting for database buffers.
Table 12-13 High Buffer Wait
...
DBWR processes or DBWR_I/O_SLAVES. Increase the buffer cache size.
High Remote Query Wait
Your instance has spent much of its In Oracle time waiting for remote queries to complete.
Table 12-14 High Remote Query Wait
...
■ Try to identify the statement suffering from a high wait for Oracle Comm Wait in the Activity tab.
Table 12-14 High Remote Query Wait
Description
Advice To reduce remote access wait time, consider the following options:
...
For example: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.
High Client Communication Wait
Your instance has spent much of its In Oracle time waiting for data from the Oracle server.
Table 12-15 High Client Communication Wait
...
For example: in the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file on the Oracle database server.
High Resource Manager Wait
Your instance has spent much of its In Oracle time waiting for the group event Resource Manager Wait.
Table 12-16 High Resource Manager Wait
...
What to do next Perform one of the following options:
Examine the top statements that suffer from high Resource Manager Wait, and the influence of the wait on the instance in the Activity tab.
...
Advice To reduce the high Resource Manager Wait, consider the following solutions:
Examine the Overtime graph for the instance, to measure the severity of the Resource Manager Wait. Also check the top statements that suffer from Resource Manager Wait. Checking the statements can reveal whether the problem relates to a specific statement.
In the Statistics tab, examine the Oracle events that are grouped to Resource Manager Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event.
High MTS Wait
Your instance has spent much of its In Oracle time waiting for MTS Wait.
Table 12-17 High MTS Wait
...
■ If your application is not suited to MTS, use "dedicated" connections which create a separate server process for each user connection.
Heavy Statement
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 Statement
...
■ Check the Binds tab for possible offensive values resulting in differing execution plans and performance.
Frequently Executed Statement
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 Executed Statement
...
What to do next Go to the Activity tab and examine the statement executors (programs and users).
Table 12-19 Frequently Executed Statement
Description
Advice Examine the Activity tab for statement exaggerated usage patterns. Try tuning scenarios for resource consumption from the following list:
...
■ Check the binds tab for possible offensive values resulting in differing execution plans and performance.
Heavily Accessed Object
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 Accessed Object
...
■ Object changes versus performance changes
Locked Object
Much of the instance In Oracle time is spent waiting for a lock on the table.
Table 12-21 Locked Object
...
■ Try to identify the locking statement in the Activity tab, using narrow time frames that match the lock periods. Focus on the locked table and its associated statements. The immediate suspect is the DML statements (and update queries) that are not waiting for locks.
High Sorts on Disk
The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
Table 12-22 High Sorts on Disk
...
Run a statement with statistics_level=all. Click the Run and Compare tab. Examine LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended section of the run results. Change the SORT_AREA_SIZE to a higher value.
High Undo Activity
Much of the instance I/O is spent waiting for the Undo object.
Table 12-23 High Undo Activity
...
Advice Examine Undo behavior over time, identify the statement accessing it, and try to tune them.
Heavily Accessed Cluster
Much of the instance I/O is spent waiting for the cluster.
Table 12-24 Heavy Cluster Activity
...
Advice Examine Cluster behavior over time, identify the statement accessing it, and try to tune them.
Locked Cluster
Much of the instance time is spent waiting for a lock on the cluster.
Table 12-25 Cluster Locks
...
What to do next Examine the lock for the statement in the Activity tab.
Table 12-25 Cluster Locks
Description
Advice To reduce the lock wait for the table, consider the following solutions:
...
■ Try to identify the locking statement in the Activity tab, using narrow time frames that match the lock periods. Focus on the locked table and associated statements. The immediate suspect is the DML statements (and update queries) that are not waiting for locks.
Storage Contention on Device (Clariion)
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 Storage Contention On Device
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thick)
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 Storage Contention On Device
...
■ Examine storage device statistics and contention on the Raid Group and Physical Disks.
Table 12-27 Storage Contention On Device
Description
Raid Group and Physical Disks.
Advice ■ If the device is loaded by the monitored database only and by a singular entity (e.g. a file, object, or partition), consider splitting this load (e.g. separating the objects in the file, partitioning the object, etc).
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix Thin)
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 Storage Contention On Device
Description
What to do next ■ Examine the device activity over time and database files contention.
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
Storage Contention on Device (Symmetrix F.A.S.T. VP)
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 Storage Contention On Device
...
■ Consider storage tiering - a faster device may reduce the I/O wait time significantly.
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 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 Contention on Redo Logs and DB Files
...
Advice It has been detected that the Redo/Transaction Log files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Storage Contention 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 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 Device on Temporary Objects
...
Advice It has been detected that the temporary tablespace files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Heavy Storage Device Holding Undo 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 Storage Device Holding Undo Objects
...
Advice It has been detected that the undo tablespace files share the storage devices (LUNs) with other database files. Consult the storage administrator about provisioning the storage devices (LUNs) better to avoid this.
Unbalanced Storage Devices Activity
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 Storage Devices Activity
...
■ Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices.
Heavy J2EE Caller Service
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 J2EE Caller Service
...
■ Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait). In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
High SQL Executions for J2EE Caller Service
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 SQL Executions for J2EE Caller Service
...
■ Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait). In this case, switch to the Statistics tab and examine the breakdown of this state in Oracle events.
About Activity tab findings
Several Activity tab findings exist in the table to help the user. These findings are available in Service Caller and Web
...
The Activity tab has the following findings:
■ Slow Statement
■ Heavy Full Scan
■ High Redo Log Buffer Wait
■ High Buffer Wait
■ High Temporary I/O
■ Heavy Statement
Slow Statement
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 Statement
...
■ Check the Binds tab for possible offensive values resulting in differing execution plans and performance.
Heavy Full Scan
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 Full Scan
...
■ Move the objects to another tablespace with a higher block size.
High Redo Log Buffer Wait
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 Redo Log Buffer Wait
...
Consider moving the log file to a faster, dedicated device.
Whenever the Log Buffer Space and Log File Sync events occur together, consider changing the hidden LOG_I/O_SIZE parameter.
High Buffer Wait
The J2EE Caller Service in the chosen context, spent much of its In Oracle time waiting for database buffer events.
Table 12-39 High Buffer Wait
...
DBWR processes or DBWR_I/O_SLAVES. Increase the buffer cache size.
High Temporary I/O
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 Temporary I/O
...
Run a statement with statistics_level=all. Click the Run and Compare tab. Examine LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended section of the run results. Change the SORT_AREA_SIZE to a higher value.
Heavy Statement
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 Statement
...
■ Check the Binds tab for possible offensive values resulting in differing execution plans and performance.
About SQL tab findings Several SQL tab findings exist to help the user. The SQL tab has the following findings:
■ Sorts Performed on Disk
■ No Parallel Processes Available
■ Bottleneck in Remote Access
■ Heavy Scattered I/O on Index
■ Heavy Sequential I/O on Index
■ Heavy Scattered I/O on Table
■ Heavy Sequential I/O on Table
■ Heavy I/O Due to Direct Access
■ Heavy I/O Due to Other Access
■ Statement State Row Lock
■ Buffer Wait Contention
■ Redo Log Activity
■ Undo Activity
■ RAC Wait
■ Bind Variables Were Collected
■ More Than One Real Plan Was Detected
■ Costs Have Changed Over the Last Month
■ Frequently Executed Statement
■ The Average Execution Uses CPU Heavily
■ Heavy Index Overhead
■ Preferable Plan Detected by Oracle
■ Low End of Fetch Count
■ Major Difference Between Plans
■ Newer Execution Plan Exists
■ Cartesian Join Used
■ CPU Used for Sorts
■ CPU Used for Index Scattered Read
■ CPU Used for Index Sequential Read
■ CPU Used for Table Scattered Read
■ CPU Used for Table Sequential Read
■ Heavy Table Full Scan
■ Heavy Step
■ Heavy Sort
■ Heavy Hash
■ Heavy Merge
■ Heavy Index Full Scan
■ Heavy Index Range Scan
■ Heavy Index Skip Scan
■ Heavy Cartesian Join
Sorts Performed on Disk
The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
Table 12-42 Sorts Performed on Disk
...
Run a statement with statistics_level=all. Click the Run & Compare tab. Examine the LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended section of the run results, and set parameters according to the first advice.
No Parallel Processes Available
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 Parallel Processes Available
...
■ Set PARALLEL_AUTOMATIC_TUNING parameter in INIT.ORA to TRUE. This will make Oracle use more sophisticated algorithms in determining the number of PQ processes for each session.
Bottleneck in Remote Access
Your statement has spent much of its In Oracle time waiting for a remote query to complete.
Table 12-44 Bottleneck in Remote Access
...
For example: the TNSNAMES.ORA configuration file and in the LISTENER.ORA configuration file in the Oracle database server.
Heavy Scattered I/O on Index
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 Scattered I/O on Index
...
■ Move the index to another tablespace with a higher block size.
Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is due to an index overhead), switch to another plan and locate the relevant step.
Heavy Sequential I/O on Index
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 Sequential I/O on Index
...
■ Create a cluster with just one table in it and a cluster key used as the index key.
If this is due to an index overhead, consider reducing the index overhead by deleting unused indices, or unused columns in used indices.
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Heavy Scattered I/O on Table
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 Scattered I/O on Table
...
■ Move the table to another tablespace with a higher block size.
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Heavy Sequential I/O on Table
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 Sequential I/O on Table
...
■ Create a cluster with just one table in it and a cluster key used as the index key.
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Heavy I/O Due to Direct Access
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 I/O Due to Direct Access
...
■ Use the SINGLEROW option.
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Heavy I/O Due to Other Access
Statement I/O is spent on another I/O on the object specified in the Object column.
Table 12-50 Heavy I/O Due to Other Access
...
■ Select the Activity tab, locate the statement associated with objects, and drill to the object (table/index) consumption for the statement in the Activity tab.
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Statement State Row Lock
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 State Row Lock
...
Findings refer to the whole statement - not to a specific execution plan. If a step doesn't exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Buffer Wait Contention
Your statement has spent much of its In Oracle time on Buffer Wait. This usually occurs because one of two possible scenarios:
...
Lack of free buffers when trying to load blocks from a disk (Free Buffer Wait)
Table 12-52 Buffer Wait Contention
...
■ Examine Buffer Wait sub-state events in the Statistics tab.
Table 12-52 Buffer Wait Contention
Description
Advice When Buffer Busy Wait is the more dominant Oracle event, consider the following options:
...
■ If this is a global instance problem, increase the number of DBWR processes or I/O slaves.
Redo Log Activity
Your statement has spent much of its In Oracle time waiting for Redo Log Wait.
Table 12-53 Redo Log Activity
...
■ Check Dashboard findings and overtime graphs to see the overall Redo Log activity for the instance.
Undo Activity
Your statement has spent much of its In Oracle time waiting for undo.
Table 12-54 Undo Activity
...
■ Check Dashboard findings and overtime graphs to see the overall undo activity for the instance.
RAC Wait
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 Wait
Description
...
Advice The object is suffering from a RAC Wait because several instances are using it simultaneously. To solve the problem, identify all programs currently accessing the object and try to avoid accessing it concurrently.
Bind Variables Were Collected
Bind sets were collected for the statement.
Table 12-56 Bind Variables Were Collected
...
Advice If the Oracle optimizer finds a better execution plan, try to evaluate one plan in relation to another for a set of captured binds and try to stabilize it by using outlines.
More Than One Real Plan Was Detected
More than one real plan was collected for the statement during the selected time frame.
Table 12-57 More Than One Real Plan Was Detected
...
Advice Try to identify the source for the different execution plans.
To resolve the multiple plans, consider the following solutions:
...
■ go to the Activity tab to see whether it is being run by different programs.
Costs Have Changed Over the Last Month
The cost for the statement has changed over the last month.
Table 12-58 Costs Have Changed Over the Last Month
...
Advice Try to identify the source for the different execution plan costs.
Go to the History tab to check whether there were any changes (such as schema or statistics changes).
Frequently Executed Statement
...
(indicating executions run many times).
Table 12-59 Frequently Executed Statement
...
Advice Try to determine if the large number of executions is valid or if it is derived from redundant executions as a result of inefficient program design (e.g. if the statement is enabled in an inefficient loop).
The Average Execution Uses CPU Heavily
Much of the stats In Oracle time is spent on CPU usage, and the average In Oracle time is high.
Table 12-60 The Average Execution Uses CPU Heavily
...
■ Explain the statement and use the SQL tab tools to improve performance.
Heavy Index Overhead
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 Index Overhead
...
■ If the index is not used in execution plans, consider dropping the index or unused columns from the index to reduce index overhead. If the index is used in execution plans, launch to the What-If tab to see which statements may be effected by this change.
Note: The Statements tab shows the activity and execution plans that were detected during the selected time frame and may not reflect the activity of all the statements and execution plans executed during this time frame. Proceed with caution when determining whether to drop an index or delete a column from an index.
...
Statement: Insert into INSERTED_TABLE values (:h1,:h2,:h3,:h4)
In this example, Oracle fetches the relevant index blocks of the two indexes, for the new rows, even though the indexes do not appear in the execution plan. The I/O wait accumulated while fetching these index blocks is considered to be an index update.
Preferable Plan Detected by Oracle
...
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 Plan Detected by Oracle
...
■ Consider using literals instead of bind variables.
Note: Setting "_optim_peek_user_binds" to FALSE will influence all statements running in the selected instance.
Low End of Fetch Count
The query activator doesn't fully read the query result set.
...
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 End of Fetch Count
...
■ Consider changing the application’s logic to improve filtering. This will lead to smaller scans.
Major Difference Between Plans
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 Differentiation between Best and Worse Plans
...
■ Using literals instead of bind variables.
If the significant difference between the best and worse plans were derived from a change of one of the objects within the plan:
...
■ Consider using outlines or profiles for checking improvements to the worst plan.
Note: Notice that setting the "_optim_peek_user_binds" parameter to FALSE will influence all statements running in this instance.
Newer Execution Plan Exists
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 Execution Plan Exists
...
■ In the Activity tab, observe whether the execution plan is being run by different programs.
Cartesian Join Used
The use of a merge join Cartesian is very expensive for Oracle. Cartesian joins can be caused by a missing Table Join condition to the WHERE clause.
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 Join Used
...
Advice To avoid a Cartesian Join verify that you have provided the proper join conditions in the statement’s
WHERE clause.
CPU Used for Sorts
I/O found on temporary tablespace may indicate sort operations are consuming CPU time of the statement.
Table 12-67 CPU Used for Sorts
...
LAST_TEMPSEG_SIZE and MAX_TEMPSEG_SIZE in the extended
CPU Used for Index Scattered Read
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 Used for Index Scattered Read
...
■ Select the Activity tab, locate the statement associated with objects, and drill to the index consumption for the statement in the Activity tab.
Table 12-68 CPU Used for Index Scattered Read
Description
Advice To reduce the CPU consumption, consider the following solutions:
...
■ Move the index to another tablespace with a higher block size.
Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is due to an index overhead), switch to another plan and locate the relevant step.
CPU Used for Index Sequential 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 Used for Index Sequential Read
...
■ Create a cluster with just one table in it and a cluster key used as the index key.
If this is due to an index overhead, consider reducing the index overhead by deleting unused indices, or unused columns in used indices.
Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
CPU Used for Table Scattered 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 Used for Table Scattered Read
...
■ Select the Activity tab, locate the statement associated with objects, and drill to the table consumption for the statement in the Activity tab.
Table 12-70 CPU Used for Table Scattered Read
Description
Advice To reduce the CPU consumption for the table, consider the following solutions:
...
■ Move the table to another tablespace with a higher block size.
Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
CPU Used for Table Sequential 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 Used for Table Sequential Read
...
■ Create a cluster with just one table in it and a cluster key used as the index key.
Findings refer to the whole statement - not to a specific execution plan. If a step does not exist in the selected execution plan (unless this is the result of an index overhead), switch to another plan and locate the relevant step.
Heavy Table Full Scan
Statement resources are spent performing Full table scans on the table specified in the Object column.
Table 12-72 Heavy Table Full Scan
...
■ Select the finding type to investigate the step statistics, step resource consumption and step vs. plan over time.
Table 12-72 Heavy Table Full Scan
Description
Advice To reduce the Full Scan resource consumption consider:
...
■ If full scan is using I/O heavily consider changing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter to a higher value.
...
■ Move the table to another tablespace with a higher block size.
Heavy Step
Statement resources are spent performing the specified step.
Table 12-73 Heavy step
...
■ Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Heavy Sort
Statement resources are spent performing sort operation.
Table 12-74 Heavy Sort
...
If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid sort area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.
Heavy Hash
Statement resources are spent performing hash operation.
Table 12-75 Heavy Hash
...
■ Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Table 12-75 Heavy Hash
Description
Advice Try to solve the problem at the statement level by one of the following options:
...
consumption for the hash operation by changing the HASH_AREA_SIZE to a higher value.
If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid hash area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.
Heavy Merge
Statement resources are spent performing merge operation.
Table 12-76 Heavy Merge
...
If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid sort area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.
Heavy Index Full Scan
Statement resources are spent performing Full index scans on the index specified in the Object column.
Table 12-77 Heavy Index Full Scan
...
■ Move the table to another tablespace with a higher block size.
Heavy Index Range Scan
Statement resources are spent performing Range index scans on the index specified in the Object column.
Table 12-78 Heavy Index Range Scan
...
■ Changing column order in the Index for better matching level or adding a new index. Check the global effect of this change by launching to WhatIF tab.
Heavy Index Skip Scan
Statement resources are spent performing Range index skip scans on the index specified in the Object column.
Table 12-79 Heavy Index Skip Scan
...
■ Changing column order in the Index for better matching level or adding a new index. Check the global effect of this change by launching to WhatIF tab.
Heavy Cartesian Join
Statement resources are spent performing Cartesian join.
Table 12-80 Heavy Cartesian Join
...
Advice To avoid a Cartesian Join verify that you have provided the proper Join conditions in the statement's WHERE clause.
About Object Findings Several Object tab findings exist to help the user. The Objects tab includes the following findings:
■ Heavy Index Overhead
■ Extensive Full Table Scan Access
■ Full Scan Reading Deleted Blocks
■ Index Clustering Factor Very High
■ Buffer Wait Contention
■ Object or Row Lock Contention
■ Bottleneck in RAC Wait
■ Many Chained Rows
■ Statistics Not Updated on Object
■ Changes Detected in Object Structure
■ Table Grew Considerably
■ Partition Is Accessed Extensively
■ Segment Hit Ratio Very Low
■ Extensive Activity on Non-explained Statements
■ Extensive Index Range Scan Access
■ Extensive Full Index Scan Access
■ Extensive Fast Full Index Scan Access
■ Extensive Index Skip Scan Access
Heavy Index Overhead
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 Index Overhead
...
Statement: Insert into INSERTED_TABLE values (:h1,:h2,:h3,:h4)
In this example, Oracle fetches the relevant index blocks of the two indexes, for the new rows, even though the indexes do not appear in the execution plan. The I/O wait accumulated while fetching these index blocks is considered to be an index update.
Extensive Full Table Scan Access
Table extensively accessed using full table scans.
Table 12-82 Extensive Full Table Scan Access
...
Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
Full Scan Reading Deleted Blocks
Table containing many deleted blocks is extensively accessed using full table scans.
Table 12-83 Full Scan Reading Deleted Blocks
...
Increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
Index Clustering Factor Very High
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 Index Clustering Factor Very High
...
■ If no primary key exists and the index is the most used or essential index for the table, consider sorting the table data according to the index key.
The following example shows the effect that a bad clustered index can have on performance when an index is scanned:
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 with good clustering factor (low = number of table blocks)
Root
Index B ocks
Leaf Leaf Leaf Leaf
Row Row Row
Row Row Row
Row Row Row
Row Row Row
Row Row Row
Row
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15 16
Data B ock Data B ock Data B ock Data B ock Data B ock Data B ock
The following figure shows an example of an index with a bad clustering factor.
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 with bad clustering factor (high = number of rows)
Root
Index B ocks
Leaf Leaf Leaf Leaf
Row Row Row
Row Row Row
Row Row Row
Row Row Row
Row Row Row
Row
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15 16
Data B ock Data B ock Data B ock Data B ock Data B ock Data B ock
Buffer Wait Contention
Object (table and indexes) spent much of its In Oracle time on Buffer wait. This usually occurs as a result of one of the following:
...
Lack of free buffer space when trying to load blocks from a disk (Free Buffer wait).
Table 12-85 Buffer Wait Contention
...
Advice Perform one of the following options:
If Buffer Busy wait is the more dominant Oracle event, consider doing the following:
...
■ If the problem is a global instance problem, increase the number of DBWR processes or I/O
slaves.
Object or Row Lock Contention
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 Or Row Lock Contention
...
■ Try to identify the locking statement in the Activity tab using smaller time frames that match the lock periods. Focus on the locked table and associated statements. The DML statements (and update queries) that are NOT waiting for locks should be the immediate suspects.
Bottleneck in RAC Wait
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 in RAC Wait
...
Advice The object is suffering RAC wait because several instances are using it simultaneously. To solve this problem, identify all programs currently accessing the object and try to avoid accessing it concurrently.
Many Chained Rows
Access to table deteriorated as a result of chained rows.
Table 12-88 Many Chained Rows
...
■ Move table to tablespace with higher block size.
Statistics Not Updated on Object
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 Not Updated on Object
...
Advice To reduce potential access type problems resulting from statistics that are not up-to-date, consider analyzing the table and checking it periodically.
Changes Detected in Object Structure
...
Table 12-90 Changes Detected in Object Structure
Description
What to do next Examine the changes in the Changes graph in the Read/Write Operations tab.
Table 12-90 Changes Detected in Object Structure
Description
Advice Perform one of the following options:
...
■ Consider adjusting index structure and execution plans.
Table Grew Considerably
The table is considerably larger than it was at the start of the time frame.
Table 12-91 Table Grew Considerably
...
Advice Verify that full scans are not widely used for the table, that the existing indexes correlate with the table growth, and that no new indexes are required. You can also check Materialized Views usage.
Partition Is Accessed Extensively
A large percentage of In Oracle time for the object is spent accessing one partition.
Table 12-92 Partition Is Accessed Extensively
...
■ Add more columns to the index to improve filtering.
Segment Hit Ratio Very Low
The hit ratio, for at least one of the objects related to the table, is very low.
Table 12-93 Segment Hit Ratio Very Low
...
Advice If there are no outstanding contentions on the buffer cache consider moving the object into Keep or Recycle buffer cache pools.
Extensive Activity on Non-explained Statements
Extensive activity on statements that were not explained.
Table 12-94 Extensive Activity on Non-explained Statements
...
Advice Perform an explain on the non-explained statements.
Extensive Index Range Scan Access
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 "Index Range Scan" Access
...
In this statement the matching level of the index is 1. This means that Oracle uses only C1 to filter index leaf pages, it cant match C5=10 against the index tree because of the absence of a C2= predicate. Because C1 is not selective, many irrelevant index leaf pages can be read. Oracle will apply the C5=10 predicate on the index keys to screen irrelevant table ROWIDs. An index on C1 followed by C5 would be more efficient for the query.
Extensive Full Index 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 “Full Index Scan” Access
...
In this case a full index scan is the best option. Because there are no filtering predicates, there is no need to access the table blocks and the sort operation is avoided.
Extensive Fast Full Index Scan Access
Index is extensively accessed using fast full index scans.
Table 12-97 Extensive "Fast Full Index Scan" Access
...
Increasing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter.
Extensive Index Skip 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 "Index Skip Scan" Access
...
Execution plan uses Index Skip Scan on IX1.
In this case Oracle has to perform two range scans on the index—one with a key of (Yes,10) and another with a key of (No,10)and then unite the results. The more distinct values defined for C1, the more index scanning required.
Defining a new index on (C2), or changing the column sequence in IX1 to be (C2,C1), enables a more efficient access path for the Index Range Scan.
Precise. Performance intelligence from click to storage. Learn more > >
...