Wednesday, June 22, 2011

SQL Workspace Wait Events 1/2

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com


Sorts Performed On Disk


The result table for a sort operation could not be completed in memory and was performed on a temporary tablespace.
Sorts Performed On Disk findings

Description
What to do next
In the Activity workspace, examine temporary tablespace overtime I/O consumption for the statement, and the programs activating the statement.
Advice
To reduce the I/O consumption for the sort operation, consider the following solutions:
n   Change the SORT_AREA_SIZE to a higher value.
If you are using PGA settings, you can change the PGA_Aggregated_Target, so as to avoid sort and hash area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.
n   Try to identify statement originated sorts.
If there are many sorts located in a few statements, try to solve the problem at the statement level by one of the following options:
   Add an index to prevent a sort.
If your statement has an Order by clause that has columns for a single table, check whether you can add an index. In some   cases, you may get an index recommendation that prevents a sort. If you are considering adding an index, you must check the effect of that index.
To analyze that effect, launch to the What if workspace.
   Identify the heavy sort or hash consumer step.
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.
No Parallel Processes Available findings

Description
What to do next
Select the finding type to see the minimum and maximum parallel processes available for the statement in the Activity workspace.
Advice
To prevent this problem, consider one of the following solutions:
n   Increase the MAX_PARALLEL_SERVERS parameter in INIT.ORA while closely monitoring the effect on wait for memory. This will lower the chances for a serial execution, but may cause a high memory consumption.
n   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.
Bottleneck in Remote Access findings

Description
What to do next
Perform one of the following options:
n   Select the findings type to see the SQL text sent to the remote database on the Statistics tab, Other column.
n   Examine DBLink relevant statistics in the Statistics workspace.
Advice
To reduce remote access wait time, consider the following options:
n   Launch Precise for Oracle on the remote instance, locate the remote query, and tune the statement.
n   Control the driving instance executing the statement by using the DRIVING_SITE hint.
n   Tune SQL*Net throughput by checking SDU and TDU settings.
n   Include TCP.NODELAY=yes parameter in the SQLNET.ORA   configuration file.
n   Specify the parameters TDU and SDU in the connection description on the application client.
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.
Heavy Scattered I/O on Index findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to find the relevant step in the execution plan
n   Select the findings type to investigate the objects used and their structure in the Objects tab
n   Select the Activity workspace, locate the statement associated with objects, and drill to the index consumption for the statement in the Activity workspace
Advice
To reduce the index I/O consumption, consider the following solutions:
n   If the index is not used to prevent a sort operation, consider adding the INDEX_FFS hint and change DB_FILE_MULTIBLOCK_READ_COUNT parameter to a higher value.
n   Switch to another index or add a new index.
n   Use a parallel query option.
n   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.
Heavy Sequential I/O on Index findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to see if the index is used in the execution plan.
n   Select the findings type to investigate the objects used and their structure in the Objects tab.
n   Select the Activity workspace, locate the statement associated with objects, and drill to the index overtime consumption for the statement in the Activity workspace.
Advice
If this is the result of a range scan, consider one of the following solutions:
n   If the index is not used to prevent a sort operation, consider adding the INDEX_FFS hint and change DB_FILE_MULTIBLOCK_READ_COUNT (now standing on X) to a higher value.
n   Switch to another index, add a new index, or change the index structure.
n   Add columns to the index to enable index only access.
n   Switch to a full table scan
n   Partition the table or the index
n   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.
Heavy Scattered I/O on Table findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to find the relevant step in the execution plan.
n   Select the findings type to investigate the objects used and their structure in the Objects tab.
n   Select the Activity workspace, locate the statement associated with objects, and drill to the table consumption for the statement in the Activity workspace
Advice
To reduce the I/O consumption for the table, consider the following solutions:
n   Create an index matching the statement's predicates.
n   Change the DB_FILE_MULTIBLOCK_READ_COUNT parameter in INIT.ORA to a higher value.
n   Partition the table according to the best predicates existing in the statement.
n   Use a parallel query option.
n   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.
Heavy Sequential I/O on Table findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to find the relevant step in the execution plan.
n   Select the findings type to investigate the objects used and their structure in the Objects tab.
n   Select the Activity workspace, locate the statement associated with objects, and drill to the table consumption for the statement in the Activity workspace.
Advice
To reduce the I/O consumption for the table, consider the following solutions:
n   Change the index structure by adding a better filtering column, or change the column sequence to improve the matching and screening performed in the index tree.
n   Add columns to the index to enable index only access.
n   Switch to a full table scan.
n   Partition the table or the index.
n   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.
Heavy I/O Due To Direct Access findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to find the relevant step in the execution plan.
n   Select the findings type to investigate the objects used and their structure in the Objects tab.
n   Select the Activity workspace, locate the statement associated with objects, and drill to the object (table/index) consumption for the statement in the Activity workspace.
Advice
To reduce the I/O consumption for the object, consider the following solutions:
n   If this is the result of an SQL loader utility in a direct path, make sure the temporary segments for the index updates are on a fast device.
n   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.
Heavy I/O Due to Other Access findings

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to find the relevant step in the execution plan.
n   Select the findings type to investigate the objects used and their structure in the Objects tab.
n   Select the Activity workspace, locate the statement associated with objects, and drill to the object (table/index) consumption for the statement in the Activity workspace.
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.
Statement State Row Lock findings

Description
What to do next
Select the findings type to examine lock for the statement in the Activity workspace.
Advice
To reduce the lock wait for the table, consider the following solutions:
n   Check to see if the lock appears in the Current workspace. If so, examine the lock chain to identify the statement holding the lock.
n   Try to identify the locking statement in the Activity workspace using the narrow time frames matching the lock periods. Now focus on the locked table and associated statements. The DML statement (and update queries) that are not waiting for locks are the immediate suspects.
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:
Contention on a table buffer in Insert statements (Buffer Busy Wait), or
Lack of free buffers when trying to load blocks from a disk (Free Buffer Wait)
Buffer Wait Contention findings

Description
What to do next
Perform one of the following options:
n   Select the findings type to identify the tables suffering from Buffer Wait in the Objects tab, or
n   Examine Buffer Wait sub-state events in the Statistics workspace.
Advice
When Buffer Busy Wait is the more dominant Oracle event, consider the following options:
n   Increase the free lists for the table to reduce the chances for contention.
n   Increase the PCTFREE parameter or decrease the block size for the table in order to spread the data among many blocks and reduce the chances for contention.
When Free Buffer Wait is the more dominant Oracle event, consider the following options:
n   Tune the object's access, so as to reduce the number of blocks fetched.
n   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.
Redo Log Activity findings

Description
What to do next
Perform one of the following options:
n   Examine Redo Log Wait overtime I/O consumption for the   statement in the Activity workspace, or
n   Examine Redo Log Wait relevant statistics in the Statistics workspace.
Advice
To reduce Redo Log Wait, consider the following solution:
n   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.
Undo Activity findings

Description
What to do next
Perform one of the following options:
n   Examine Undo Wait overtime I/O consumption for the statement in the Activity workspace, or
n   Examine Undo Wait relevant statistics in the Statistics workspace.
Advice
To reduce Undo Wait time, consider the following solutions:
n   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.
RAC Wait findings

Description
What to do next
Select the findings type to see the instances consuming object RAC Wait in the Activity workspace.
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.
Bind Variables Were Collected findings

Description
What to do next
Perform one of the following options:
n   Select the findings type to examine the bind values in the Bind Variables tab.
n   Use the Estimate Cost button to check whether the Oracle optimizer identified a better execution plan for   a provided set of binds.
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.
More Than One Real Plan Was Detected findings

Description
What to do next
Select the findings type and use the All Plans tab to examine the different captured execution plans and their real resource consumption.
Advice
Try to identify the source for the different execution plans.
To resolve the multiple plans, consider the following solutions:
n   Go to the History tab to see whether there were any changes (such as schema or statistics changes), or
n   go to the Activity workspace 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.
Costs Have Changed Over the Last Month findings

Description
What to do next
Select the findings type and use the History tab to examine the different costs caught for the statement.
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

Much of the statements In Oracle time is spent on CPU usage, even though the average In Oracle time is low (indicating executions run many times).
Frequently Executed Statement findings

Description
What to do next
Perform one of the following options:
n   Launch to the Activity workspace to examine the scalability of the statement.
n   Examine the activity of the program activating the 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 activated 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.
The Average Execution Uses CPU Heavily findings

Description
What to do next
Run the statement with statistics_level=ALL.
Advice
Perform one of the following:
n   Examine run results in the Extended Statistics tab. Analyze problematic data (such as many logical reads, table scans and more).
n   Explain the statement and use the SQL workspace 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.
Heavy Index Overhead

Description
What to do next
Perform one of the following options:
n   In the Objects tab, observe the I/O breakdown between the different indexes. Focus on the heaviest index and launch to the Tune Object workspace.
n   Check if this index is being used in other access types other than Index Overhead. If the only access type is Index Overhead, this may indicate that the index is not being used to filter table rows.
n   In the Statements tab, examine the different statements using the index, to learn about index usage patterns.
n   In the Read/Write Operations tab, in the In Oracle graph, try to identify index update patterns (such as, day or night).
Advice
Perform one of the following options:
n   When inserts are part of a load, batch, or night activity, consider dropping the index before performing the activity, and recreating it afterwards.
n   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 workspace 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.
Example
Table: INSERTED_TABLE (C1 number,C2 date, C3 varchar2(128), C4 number)
Indexes on table: IX1 (C1,C2) IX2(C4,C3)
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


This finding can only appear after running the "Get Best Plan" command in the Bind Variables tab.
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.
Preferable Plan Detected by Oracle

Description
What to do next
Perform one of the following options:
n   Check the PLAN_HASH_VALUE field that appears in the left pane in the Bind Variables tab and examine the difference between plans of different bind sets.
n   Consider running the statement using a different bind set leading to a different plan, and compare their run time.
Advice
If different execution plans result in a fluctuation in run time consider the following:
n   Fixing an optimal plan using outlines or profiles.
n   Disabling bind peeking by setting the "_optim_peek_user_binds" parameter to FALSE
n   Consider using literals instead of bind variables.
Note: Notice that setting "_optim_peek_user_binds" to FALSE will influence all statements running in the selected instance.


Get Oracle Certifications for all Exams
Free Online Exams.com

No comments: