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
Low End of Fetch Count
The query activator doesn't fully read the query result set.
Consider speeding up the first set of rows fetch time. This is usually done by using the "FIRST_ROWS" hint.
Verify that the application is not performing a huge data scan for no reason. Unnecessarily large scans are expensive (in terms of CPU and I/O time).
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.
| Low end of fetch count |
| Description | |
| What to do next | n Examine the statement's execution plan and make sure that unnecessary scans are not being performed. n Observe which program is calling the statement in the Activity Workspace, and check your Application to verify that there is a true need to scan all data and fetch only part of it. |
| Advice | n Consider tuning the statement so that it scans fewer rows, by adding a "FIRST_ROWS" hint. Compare the result execution plan to the original, and run both statements to evaluate the influence of the change. n 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.
| Significant differentiation between best and worse plans |
| Description | |
| What to do next | Perform one of the following options: n Compare the execution plan of the best plan with the execution plan of the worse plan (also includes a comparison of costs). In the Bind variables tab, check if the difference can be attributed to using a different bind set. If not, check if the significant difference can be derived from a change of one of the objects. n If you find that the difference between the best and worse plans is derived from a different bind set, consider running the statement using a different bind set and compare their run times. |
| Advice | If the significant difference between the best and worse plans was derived from using a different bind set consider: n Fixing an optimal plan using outlines or profiles. n Disabling bind peeking by setting the "_optim_peek_user_binds" parameter to FALSE. n 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: n Explore the objects that are being accessed inefficiently in the worst plan, in the Objects workspace. Check if the table grew significantly or if one of the indexes was dropped and can be rebuilt. n 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.
| Newer execution plan exists |
| Description | |
| What to do next | Perform one of the following options: n Expand the time frame you are viewing, using the time frame list box, to identify the new execution plan. Expand the time frame until it includes the current time so that the newer execution plan is loaded. n Examine the different execution plans that were captured, and their real resource consumption. |
| Advice | Try to identify the source of the different execution plans. To resolve the multiple plans, consider the following solutions: n In the All Changes section view, observe whether any changes took place (such as schema or statistics changes). n In the Activity workspace, 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.
| Cartesian Join Used |
| Description | |
| What to do next | Examine the predicates in the statement’s WHERE clause. |
| Advice | To avoid a Cartesian Join make sure 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.
| CPU Used for Sorts |
| Description | |
| What to do next | In the Activity workspace, examine temporary tablespace overtime I/O consumption for the statement and for 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 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 |
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.
| 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. |
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.
| 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. |
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.
| 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. |
Heavy Table Full Scan
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. |
Heavy Step
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. |
Heavy Sort
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. |
Heavy Hash
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. |
.
Heavy Merge
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. |
Heavy Index Full Scan
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. |
.
Heavy Index Skip Scan
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. |
Heavy Index Range Scan
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. |
No comments:
Post a Comment