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
http://www.free-online-exams.com
High Background Process Wait
Your instance has spent much of its In Oracle time waiting for the group event Background Process Wait.
| Description | |
| What to do next | Perform one of the following options: n Examine the Oracle events that are grouped in the Background Process Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n Examine high Background Process Wait statements in the Activity workspace. |
| 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.
| Description | |
| What to do next | Perform one of the following options: n Examine the Oracle events that are grouped in the Parallel Query Server Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n Examine high Parallel Query Server Wait statements in the Activity workspace. |
| 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.
| Description | |
| What to do next | Perform one of the following options: n Examine the Oracle events that are grouped as Other Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n Examine high Other Wait statements in the Activity workspace. |
| Advice | In the Statistics workspace, 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.
| Description | |
| What to do next | Perform one of the following options: n Examine the Oracle events that are grouped in the Buffer Wait. Determine the dominant Oracle event and follow the tuning scenario set by this event in the Statistics workspace. n If the dominant Oracle event is buffer busy, launch into the Activity workspace and locate the objects with the highest buffer wait. |
| Advice | There are two typical scenarios relevant to buffer wait that are determined by the dominant Oracle event: n Buffer Busy wait event There is high contention on specific table blocks. To reduce contention, increase the Freelists or the Pctfree for the table. n Free Buffer Wait event There are no available buffers in the buffer cache. This is usually an I/O wait related problem. Do the following: Try and tune the heaviest statements or objects. If this event persists after statement tuning, try and increase DBWR throughput by adding more 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.
| Description | |
| What to do next | Perform one of the following solutions: n Examine DBLink relevant statistics in the Statistics workspace. n Try to identify the statement suffering from a high wait for Oracle Comm Wait in the Activity 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: 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.
| Description | |
| What to do next | Perform one of the following options: n Examine SQL*Net relevant statistics in the Statistics workspace. n Identify top statements that suffer from the high Oracle Client Comm Wait in the Activity workspace. |
| Advice | To reduce the amount of data transferred via SQL*Net, consider the following solutions: n Change the SQL statements so that only needed data is transferred to the client. For example, select only columns that are actually used in the client application and only retrieve required rows. n Check that you are not suffering implicit delays in your TCP/IP network by specifying TCP.NODELAY=yes in the SQLNET.ORA files for the Oracle listener and for the application client. n Specify the parameters TDU and SDU in the connection description on the application client. 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.
| Description | |
| 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 workspace. Try to identify the most dominant events related to Resource Manager Wait in the Statistics workspace. |
| Advice | To reduce the high Resource Manager Wait, consider the following solutions: Examine the Overtime graph for the instance, in order 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 workspace, 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.
Description | |
What to do next | Perform one of the following options: n Examine high MTS wait by programs in the Activity workspace. n Try to identify the most dominant events related to MTS Wait in the Statistics workspace. |
Advice | To reduce high MTS wait, consider the following solutions: n Examine the Overtime graph for the instance, in order to measure the severity of the MTS Wait. Look for top programs suffering from MTS Wait. n Examine the memory usage of MTS connections by querying v$sesstat and v$sessions. Measure the maximum amount of UGA memory used at any given moment and divide that amount by the number of current user sessions. This determines the average amount of memory each connection allocates. n If your environment 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.
Description | |
What to do next | Try to determine what is causing the statement's high resource consumption. In the SQL workspace, examine the text of the relevant statement, and its findings, execution plan, change data and statistics. |
Advice | For resource consumption, these are the possible scenarios: n Object wait on statement objects. Use SQL findings to identify the heaviest waiting object. n High CPU consumption without object wait. Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time). n Instance-related wait (such as: internal lock wait, shared pool wait, and redo log wait). In this case, switch to the Statistics workspace and examine the breakdown of this state in Oracle events. n A change in the execution plan. If this is responsible for the statement's performance degradation, check the History tab and the All Plans view to identify the actual change that caused the slowdown. n 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.
Description | |
What to do next | Go to the Activity workspace and examine the statement executors (programs and users). |
Advice | Examine the Activity workspace for statement exaggerated usage patterns. Try tuning scenarios for resource consumption from the following list: n Object wait on the statement objects. Use SQL findings to identify the heaviest waiting object. n High CPU consumption without object wait. Check the number of executions (to identify possible infinite loops) or intensive statements (with a low average time but a high aggregated CPU time). n Instance related wait (such as: Internal lock wait, Shared pool wait, Redo log wait). In this case, switch to the Statistics workspace and examine the breakdown of this state in Oracle events. n A change in the execution plan. This is responsible for the statements performance degradation. Use history and all plans to identify the actual change that caused the slowdown. n 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.
Description | |
What to do next | Perform one of the following options: n To tune the object, go to the Tune Object workspace. n Examine the waits for the object in the Activity workspace. |
Advice | To reduce the Waits for the object, follow the findings instructions in the Object workspace. In the Object workspace, you can examine the following object data: n Access path pattern to the object (full scans, range scans, and so on) n Read-Write access patterns n Partition usage (such as whether there are extreme cases in use) n Object changes versus performance changes |
Locked Object
Much of the instance In Oracle time is spent waiting for a lock on the table.
Description | |
What to do next | Perform one of the following options: n To extensively tune the object, go to the Tune Object workspace. n Examine the Lock for the statement in the Activity workspace. |
Advice | To reduce the lock wait for the table, consider the following solutions: n Check if the lock appears in the Current workspace. If so, examine the lock chain to discover which statement is holding the lock. n Try to identify the locking statement in the Activity workspace, 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.
Description | |
What to do next | In the Activity workspace, examine temporary tablespace overtime I/O consumption, and the statements using temporary tablespace I/O. |
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 doing one of the following: • 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, check the effect of adding that index in the What-If workspace. • Identify the heavy sort or hash consumer step. 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.
Description | |
What to do next | Examine Undo activity over time and the statement accessing it, in the Activity workspace. |
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.
Description | |
What to do next | Examine Cluster activity over time and the statement accessing it, in the Activity workspace. |
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.
Description | |
What to do next | Examine the lock for the statement in the Activity workspace. |
Advice | To reduce the lock wait for the table, consider the following solutions: n Check if the lock appears in the Current workspace. If so, examine the lock chain to discover the statement holding the lock. n Try to identify the locking statement in the Activity workspace, 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. |
SmarTune Instance Findings
SmarTune instance findings were detected.
Description | |
What to do next | Examine the SmarTune instance findings in the SmarTune workspace. |
Advice | Examine the instance detected. |
Storage Contention On Device
The fact that a storage device (LUN) is causing a lot of I/O waits could be caused from an intensive load or as a result of two sorts of contentions: a logical contention (e.g. imbalanced activity of the database) or a physical contention (e.g. one of the underlying physical devices is being shared with another heavy I/O consuming activity).
Storage Contention On Device findings |
Description | |
What to do next | n Examine the device activity over time and database files contention. n Examine storage device statistics and contention on the Raid Group and Physical Disks. |
Advice | n 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). n To relieve inter application logical contention, check if the database's I/O activity is balanced. Spread heavy I/O consuming files across the storage devices, to avoid a situation in which few heavy files reside on the same storage device. n To relieve intra application logical contention, check whether there are additional applications using the storage device. For example, if the number of I/O requests processed by the storage device is significantly higher that the requests sent by the database, it means that the storage device is being used by an additional application. n To relieve physical contention, check whether there is significant I/O activity in the underlying shared physical disks and raid group. Another potential cause of contention are the EMC adapters (front director and disk director). If the load is imbalanced, consult with the storage administrator about relocating the information to other disks which reside on a more vacant location. n Consider storage tiering - a faster device may reduce the I/O wait time significantly. |
Storage Contention on Redo Logs and DB Files
Redo/Transaction Log files are frequently accessed by the database. The majority of the operations performed are writing commands, which cause a heavy load on the underlying disks.
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.
Storage Contention on Redo Logs and DB Files |
Description | |
What to do next | n Examine the device activity over time and database files contention. n Examine storage device statistics and contention on the Raid Group and Physical Disks. |
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
Temporary tablespace files are frequently accessed by the database. The majority of the operation performed are writing commands, which cause a heavy load on the underlying disks.
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.
Storage Device on Temporary Objects |
Description | |
What to do next | n Examine the device activity over time and database files contention. n Examine storage device statistics and contention on the Raid Group and Physical Disks. |
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
Undo tablespace files are frequently accessed by the database. The majority of the operation performed are writing commands, which cause a heavy load on the underlying disks.
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.
Heavy Storage Device Holding Undo Objects |
Description | |
What to do next | n Examine the device activity over time and database files contention. n Examine storage device statistics and contention on the Raid Group and Physical Disks. |
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.
Unbalanced Storage Devices Activity |
Description | |
What to do next | n Compare the storage devices activity over time. n Examine the storage devices statistics. |
Advice | n In the activity workspace, check which database files are the most I/O consuming and spread them evenly across the storage devices. n Consult with the storage administrator and check for other applications using the same storage devices or their underlying physical disks. n Consult with the storage administrator about the RAID policy. A different striping may spread the I/O load across the storage devices. |
