CPU Used for Sorts 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

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

   

Get Oracle Certifications for all Exams
Free Online Exams.com

Cartesian Join Used 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

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.

    Oracle SQL by Example, 4TH EDITION

Get Oracle Certifications for all Exams
Free Online Exams.com

Newer Execution Plan Exists 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

            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.

    

Get Oracle Certifications for all Exams
Free Online Exams.com

Low End of Fetch Count 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

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.

    Oracle SQL by Example, 4TH EDITION

Get Oracle Certifications for all Exams
Free Online Exams.com

Major Difference Between Plans 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

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.

      

Get Oracle Certifications for all Exams
Free Online Exams.com

Preferable Plan Detected by Oracle 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

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

The Average Execution Uses CPU Heavily 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

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.

    

Get Oracle Certifications for all Exams
Free Online Exams.com