Oracle Instance Related Waits 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

Wait for a undo record" & "Wait for stopper event to be increased

Sometimes Parallel Rollback of Large Transaction may become very slow. After killing a large running transaction (either by killing the shadow process or aborting the database) then database seems to hang, or SMON and parallel query servers taking all the available CPU. 

In fast-start parallel rollback, the background process SMON acts as a coordinator and rolls back a set of transactions in parallel using multiple server processes.

Fast start parallel rollback is mainly useful when a system has transactions that run a long time before a commit, especially parallel Inserts, Updates, Deletes operations. When SMON discovers that the amount of recovery work is above a certain threshold, it automatically begins parallel rollback by dispersing the work among several parallel processes.

There are cases where parallel transaction recovery is not as fast as serial transaction recovery, because the PQ slaves are interfering with each other. It looks like the changes made by this transaction cannot be recovered in parallel without causing a performance problem. The parallel rollback slave processes are most likely contending for the same resource, which results in even worse rollback performance compared to a serial rollback. 

Solution

======

To disable the parallel rollback by setting the following parameter

fast_start_parallel_rollback = false

Refer to Oracle Note ID 464246.1

High CPU Wait

Your instance has spent much of its In Oracle time waiting for CPU.

High CPU Wait findings Oracle Wait Event Tuning: Optimization with Oracle Wait Interface and Wait Event Analysis Search Amazon.com for oracle waits

	Description
What to do next	Perform one of the following options: n   Examine high wait for CPU statements in the Activity workspace. n  Examine high n   CPU statements usage in the Activity workspace. n   Examine CPU utilization in the Statistics workspace. n   Try to identify the system processes consuming CPU resources using the Insight Savvy for OS tool.
Advice	Perform one of the following options: n   Identify other processes in the system. n   The instance has spent much of its In Oracle time waiting for CPU; every process running in your system affects the available CPU resources. Effort spent tuning non-Oracle factors can improve Oracle performance. n   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.

High Other Host Wait findings

	Description
What to do next	Perform one of the following options: n   Examine heavy wait for Other Host Wait statements in the Activity workspace. n   Try to identify the system processes consuming OS resources using the Insight Savvy for OS tool.
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.

High Memory Wait findings

	Description
What to do next	Perform one of the following options: n   Examine high Memory Wait statements in the Activity workspace. n   Try to identify the system processes consuming memory using the Insight Savvy for OS tool.
Advice	Perform one of the following options: n   Identify other processes in the system. The instance has spent much of its In Oracle time waiting for memory; every process running in your system affects the available memory. Effort spent tuning non-Oracle factors can improve Oracle performance. For example: the result of setting a high number of MAX_PARALLEL_SERVERS when using a parallel query option. n   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.

High Shared Pool Wait findings

	Description
What to do next	Perform one of the following options: n   Examine the Oracle events that are grouped into the Wait for Shared Pool in the Statistics workspace. Determine the dominant Oracle event and follow the tuning scenario set by this event. n   Examine high Shared Pool Wait statements in the Activity workspace.
Advice	Common scenarios for this wait occur when the shared pool is either too small or too big. Make sure 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.

High Rollback Segment Wait findings

	Description
What to do next	Perform one of the following options: n   Examine the Oracle events that are grouped into the Wait for Rollback segment. Determine the dominant Oracle event and follow the tuning scenario set by this event. n   Examine the statements or objects with the highest values of Rollback Segment Wait and determine which applications are creating this wait.
Advice	To reduce contention on the rollback segment, consider one of the following solutions: n   Add rollback segments, moving them into a less busy tablespace. n   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.

High Redo Log Buffer Wait findings

	Description
What to do next	Perform one of the following options: n   Examine the related Oracle events (lower area), Redo Activity (upper area), to determine the problem type in the Statistics workspace. n   Examine high Redo Log Buffer Wait statements in the Activity workspace.
Advice	Use any one of the typical problem scenarios described below. n   If the log buffer size is too small, this usually results in long waits for the Log Buffer Space event. Consider increasing the Log_buffer parameter. n   If the log buffer size is too big, this usually results in a low number of user   commits, high redo wastage statistics, and long waits for the Log File Sync   event. Consider decreasing the Log_buffer parameter and/or the hidden LOG_I/O_SIZE parameter. n   If there are too many commits, this usually results in long waits for the Log File Sync event and the number of user commits is very high. Consider changing the application flow and logic (by decreasing the commit frequency or using bulk commits [resulting in larger transactions]). n   If the LGWR is too slow, check whether Log File Sync is still the dominant event. This may be due to high values for Log File Parallel Write, or because there are not many commits. This may mean that the LGWR is underperforming. 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.

High Log Switch and Clear Wait findings

	Description
What to do next	Perform one of the following options: n   Examine the Oracle events that are grouped into the Wait   for Log Switch and Clear. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n   Examine the statements with the highest values for this wait and determine which applications are creating this wait in the Activity workspace.
Advice	If the related Oracle events show too many log switches, try and reduce them by one of the following options: n   Increase the Redo log size (when the Log File Switch (checkpoint incomplete) and/or Log File Switch Completion event is dominant). n   Change the application flow or logging policy (by changing the commit frequency or using No Logging on specific objects). 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.

High RAC/OPS Wait findings

	Description
What to do next	Perform one of the following options: n   Examine the Oracle events that are grouped in the Oracle RAC/OPS Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n   Examine Load balancing between RAC instances in the Activity workspace. n   Examine Heavy objects suffering from RAC Waits in the Activity workspace.
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: n   If there is a balancing instances issue, launch to the Activity workspace and identify the root cause for this unbalanced issue. n   If there is a RAC Wait event, compare them to other events in the database in the Statistics workspace. n   If there are Objects suffering from RAC Waits, launch to the Activity workspace 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.

High Other Lock Wait findings

	Description
What to do next	Perform one of the following options: n   Examine Latching view overtime and the Oracle latches that are grouped in the Other Lock Wait. Then determine the dominant Oracle latch or enqueue and follow the tuning scenario set by this latch in the Statistics workspace. n   Examine high Other Lock Wait statements in the Activity workspace.
Advice	Examine the Oracle latches that are grouped into the Other Lock Wait. Determine the dominant Oracle latch or enqueue and follow the tuning scenario set by this event.

Get Oracle Certifications for all Exams
Free Online Exams.com

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