Tuesday, June 14, 2011

Heavy Index Skip Scan wait

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
Statement resources are spent performing Range index skip scans on the index specified in the Object column.
Heavy Index Skip Scan finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate the objects used and their structure in the Objects tab.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
To reduce the Range Skip Scan resource consumption   consider:
n   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 workspace.

    
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Index Range Scan --

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


Statement resources are spent performing Range index scans on the index specified in the Object column.
Heavy Index Range Scan finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate the objects used and their structure in the Objects tab.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
To reduce the Range Scan resource consumption consider:
n   Checking the matching level of the Index to discover if there is a mismatch between existing Where predicates and operators with column order in the Index.
n   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 workspace.


    
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Index Full Scan

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


Statement resources are spent performing Full index scans on the index specified in the Object column
Heavy Index Full Scan finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate the objects used and their structure in the Objects tab.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
To reduce the Full Scan resource consumption consider:
n   Creating an index matching the statement’s predicates.
n   If the full scan is using I/O heavily and the step name is a fast full scan, consider changing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter 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.
.


    
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Hash wait

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


Statement resources are spent performing hash operation
Heavy Hash finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
Try to solve the problem at the statement level by one of the following options:
n   If the hash operation involves I/O against temporary tablespace, you can reduce the I/O 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.
.


      
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Merge wait

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

Statement resources are spent performing merge operation.
Heavy Merge finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
Try to solve the problem at the statement level by one of the following options:
n   If the hash operation involves I/O against temporary tablespace, you can reduce the I/O consumption for the hash operation by changing 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 area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.




Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Sort Wait

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

Statement resources are spent performing sort operation.
Heavy Sort finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.
Advice
Try to solve the problem at the statement level by one of the following options:
n   Add an index to prevent a sort.
n   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.
n   If the sort involves I/O on the temporary tablespace, you can reduce the I/O consumption for the sort operation by changing 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 area size problems. You can either change the values for a specific session using the Alter Session command, or for the entire instance.



    
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Step Wait

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

Statement resources are spent performing the specified step.
Heavy step finding


Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate step statistics and resource consumption in the Statistics tab.

    
Get Oracle Certifications for all Exams
Free Online Exams.com

Heavy Table Full Scan Wait

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

Statement resources are spent performing Full table scans on the table specified in the Object column.

Heavy Table Full Scan finding

Description
What to do next
Perform one of the following options:
n   Click the Locate icon to focus on the relevant step in the execution plan.
n   Select the finding type to investigate the objects used and their structure in the Objects tab.
n   Select the finding type to investigate the step statistics, step resource consumption and step vs. plan over time.
Advice
To reduce the Full Scan resource consumption consider:
n   Creating an index matching the statement's predicates.
n   If full scan is using I/O heavily consider changing the DB_FILE_MULTIBLOCK_READ_COUNT init.ora parameter 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.


    
Get Oracle Certifications for all Exams
Free Online Exams.com

CPU Used for Table Scattered read Wait

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


Statement 's I/O may indicate table scattered read operation (often full scan) on the table specified in the Object column.
CPU Used for Table Scattered read

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 CPU 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 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.


    
Get Oracle Certifications for all Exams
Free Online Exams.com

CPU Used for Table Sequential read Wait

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

Statement 's I/O may indicate a Table Sequential read operation (often ROWID access) on the table specified in the Object column.
CPU Used for Table Sequential read

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 CPU 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 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.


    

Get Oracle Certifications for all Exams
Free Online Exams.com

CPU Used for Index Scattered read Wait

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



Statement 's I/O may indicate Index Scattered read operation (often full scan) on the index specified in the Object column.
CPU Used for Index Scattered read

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 CPU 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.


     

Get Oracle Certifications for all Exams
Free Online Exams.com