# Expert Oracle Practices # Choosing a Performance Optimization Method

I am reading Expert Oracle Practices by Oracle Database Administration from the Oak Table. I’d like to summary and share what I learned.

This chapter, Choosing a Performance Optimization Method by Charles Hooper and Randolf Geist again. It’s a chapter to tell us how to make the right choice when there is a problem. Finding the right direction is the first very important step. Oracle performance issues are either system level or specified SQL/Module level most of the time. Sometimes system level problem may be caused by some SQL/Module level. Sometimes they are mixed.

The time model views V$SYS_TIME_MODEL and V$SESS_TIME_MODEL are very important to performance analysis because performance is all about time. I think OEM (oracle enterprise management) used it a lot. I didn’t pay enough attention to it at the past. I will revisit the “Sampling Performance with Low Overhead” script by the same authors in previous chapter.

Once again, this chapter mentioned “GATHER_PLAN_STATISTICS DISPLAY_CURSOR ALLSTATS LAST” method again and again

SQL>  ALTER SESSION SET STATISTICS_LEVEL='ALL';
SQL>  run the sql
SQL>  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
--add hint /*+ GATHER_PLAN_STATISTICS */ to the SQL also works if you cannot set statistics_level to all
--This one is one of my favorite. By comparing the oracle estimated rows and actual rows of each step, you can quickly know where is wrong in the plan.

Some hints mentioned

/*+ ORDERED */ (Alternatively, use a/*+ LEADING */ hint with the first couple of table aliases specified.)
/*+ OPTIMIZER_FEATURES_ENABLE(‘9.2.0’) */
/*+ CARDINALITY(t 200) */, undocumented, but in V$SQL_HINT
/*+ OPT_ESTIMATE(TABLE, t, ROWS=200) */, used by SQL profile, should be safe to use as long as SQL profile is not retired
/*+ dynamic_sampling(t 10) */
NO_EXPAND hint, which allows the optimizer in recent Oracle releases to use the IN-LIST ITERATOR operation instead.

Set table statistics
EXEC DBMS_STATS.SET_TABLE_STATS(OWNNAME=>USER, TABNAME=>’TEMP_PART_PRICE_DATE’, NUMROWS=>200, NUMBLKS=>15, NO_INVALIDATE=>FALSE);.

Clearly describe a problem
a. How the problem was identified (for example, end-user complaints, order system hanging)
b. The severity of the problem (for example, orders cannot be processed)
c. A description of the steps required to reproduce the issue
d. Clear distinction between what is working and what is not working (for example, the order entry form is hanging, whereas the invoice processing is working fine)
e. The expected or acceptable behavior
f. What has been done so far to rectify the issue (for example, object statistics have been checked, but no anomalies were identified)

Reproduces the issue at will 
a. Covers the inefficient SQL identified
b. Is as generic as possible, allowing you to easily transfer the identified issue to Oracle Support if required
c. Shows evidence that the statement identified is inefficient (for example, the SQL*Plus timing facility reports that the statement takes 306 seconds to complete instead of expected subsecond execution time)

Others
“Verify That the Issue Is a Database Issue” is also very important. It didn’t have to be a database issue but we absolutely need prove that before say it.

Starting with release 10.1, the default METHOD_OPT value is FOR ALL COLUMNS SIZE AUTO, which might generate histograms on columns that do not improve the join selectivity/cardinality estimates

Oracle 11.2 introduced the automatic parallel degree (AUTO DOP) feature, which handles the degree of parallelism differently (controlled by the new parameters PARALLEL_DEGREE_POLICY and PARALLEL_MIN_TIME_THRESHOLD). The session and systemwide statistics in V$SESSTAT, V$MYSTAT, and V$SYSSTAT may be checked to determine the number of parallel executions that have been downgraded by examining the delta values for the statistics like ‘Parallel operations%’

The optimizer environment that existed during the initial hard parse (visible in the view V$SQL_OPTIMIZER_ENV) must match that of the session (visible in the view V$SES_OPTIMIZER_ENV) submitting the SQL statement to the database instance.

Metalink Doc ID 744664.1 “High Parse Time and Memory Usage for Query with Complex OR Predicates or IN-Lists After Upgrade to 10.2 or 11.1

Metalink Doc ID 32895.1, “SQL Parsing Flow Diagram”

Metalink Doc IDs 604022.1 and 144194.1 Use hints to obtain the desired execution plan and lock the plan by using an outline