Advanced Query Tuning

These referenced steps provide the guidelines for dealing with query tuning problems.

Queries can run slowly for any number of reasons

1.       Identify the query which causing the problem

We can identify the query in various methods (e.g OEM, STATSPACK, sql query)

1.       a) Find resource hungry SQL interactively as follows

SELECT address, SUBSTR(sql_text,1,20) Text, buffer_gets, executions,            buffer_gets/executions AVG     FROM   v$sqlarea     WHERE  executions  > 0     AND    buffer_gets > 100000     ORDER BY 5;

Note: the 'buffer_gets' value of > 100000 needs to be varied for the individual system being tuned

Sample OUTPUT:

ADDRESS          TEXT                                                         BUFFER_GETS EXECUTIONS        AVG ---------------- ------------------------------------------------------------ ----------- ---------- ---------- 000000010514C048 BEGIN DBMS_SCHEDULER                                             4135073          9 459452.556 000000016641A1E8 DELETE FROM PLANT_SC                                             6513536         14 465252.571 000000017D292A08 call Sync_Notificati                                            34439016         65 529831.015 00000000EA47E060 BEGIN DBMS_SCHEDULER                                            15370793         29 530027.345 000000013872CB68 SELECT T.COI,                                                   67165717         78 861098.936 00000000DCDEB490 /* MV_REFRESH (INS)                                             15187589         15 1012505.93 00000000EA38CD78 MERGE INTO PLANT_SCH                                            17829484         17 1048793.18 0000000105821DF0 call xxsap.Dashboard                                            25739712         17 1514100.71 000000017D8952B0 SELECT T.COI,                                                   28207138         14 2014795.57 0000000157FD77B8 select       st.coi,                                            57612995         28 2057606.96 00000000EA233F18 select st.coi, p.cci                                             4117702          2    2058851

The ADDRESS value retrieved above can then be used to lookup the whole statement in the v$sqltext view:

SELECT sql_text FROM v$sqltext WHERE address = '00000000EA233F18' ORDER BY piece;

Sample OUTPUT

SQL_TEXT ---------------------------------------------------------------- select st.coi, p.ccid, sts.code AS STATUS, d.last_name || ', ' | | d.first_name || ' ' || d.middle_name AS PROVIDER, aph.name AS APH_SITE,    inf.name AS  INF_SITE, plant.name As PLANT, plant.i d AS PLANT_ID, CAST(aac.scheduled_start AS DATE) AS APH_COL_SCH_ START,    CAST(aac.actual_start AS DATE) AS APH_COL_ACT_START, a ac.start_tzd AS APH_COL_START_TZD, CAST(ar.scheduled_finish AS D ATE) AS APH_RINSEBACK_SCH_FINISH,    CAST(ar.actual_finish AS DA TE) AS APH_RINSEBACK_ACT_FINISH, ar.finish_tzd AS APH_RINSEBACK_ FINISH_TZD,    CAST(ait.scheduled_start AS DATE) AS IN_TRANS_SCH _START, CAST(ait.actual_start AS DATE) AS IN_TRANS_ACT_START,  ait.start_tzd AS IN_TRANS_START_TZD, CAST(ait.scheduled_finish

Note: Once query is identified we can tune to reduce the resource usage

1.       b) If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits.

SELECT name ,statistic#       FROM   v$statname     WHERE  name LIKE '%CPU%session';

Sample OUTPUT

NAME                                                             STATISTIC# ---------------------------------------------------------------- ---------- CPU used by this session                                                 13 IPC CPU used by this session                                             48 global enqueue CPU used by this session                                  51 gc CPU used by this session                                             169

Then determine which session using most of the cpu.

SELECT * FROM v$sesstat WHERE statistic# = 13;

Sample OUTPUT

SID STATISTIC#      VALUE ---------- ---------- ----------       1489         13          0       1490         13       3595       1493         13          0       1494         13          0       1496         13        717       1505         13       8138       1507         13       1989       1510         13       1278       1516         13        577       1517         13       1228       1518         13          0

Look up details for the session

SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,            buffer_gets/executions AVG     FROM   v$sqlarea a, v$session s     WHERE  sid = 16      AND    s.sql_address = a.address     AND    executions > 0     ORDER BY 5;

Use the v$sqltext to extract whole SQL text

Use autotrace to determine access paths

The AUTOTRACE system variable.  SET AUTOTRACE OFF           - No AUTOTRACE report is generated. This is the default.  SET AUTOTRACE ON EXPLAIN    - The AUTOTRACE report shows only the optimizer execution path. SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics.  SET AUTOTRACE ON            - The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.  SET AUTOTRACE TRACEONLY     - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

Sample OUTPUT

SQL> SET AUTOTRACE ON SQL> / DNAME          ENAME             SAL JOB -------------- ---------- ---------- --------- ACCOUNTING     CLARK            2450 MANAGER ACCOUNTING     KING             5000 PRESIDENT ACCOUNTING     MILLER           1300 CLERK RESEARCH       SMITH             800 CLERK RESEARCH       ADAMS            1100 CLERK RESEARCH       FORD             3000 ANALYST RESEARCH       SCOTT            3000 ANALYST RESEARCH       JONES            2975 MANAGER SALES          ALLEN            1600 SALESMAN SALES          BLAKE            2850 MANAGER SALES          MARTIN           1250 SALESMAN SALES          JAMES             950 CLERK SALES          TURNER           1500 SALESMAN SALES          WARD             1250 SALESMAN 14 rows selected. Execution Plan ----------------------------------------------------------- 0      SELECT STATEMENT Optimizer=CHOOSE 1    0   MERGE JOIN 2    1     SORT (JOIN) 3    2       TABLE ACCESS (FULL) OF 'DEPT' 4    1     SORT (JOIN) 5    4       TABLE ACCESS (FULL) OF 'EMP' Statistics ---------------------------------------------------------- 148  recursive calls   4  db block gets  24  consistent gets   6  physical reads  43  redo size 591  bytes sent via SQL*Net to client 256  bytes received via SQL*Net from client  33  SQL*Net roundtrips to/from client   2  sorts (memory)   0  sorts (disk)  14  rows processed

1.       c)  Poor disk contention/performance

Use STATSPACK/AWR - focusing on “TBS IO stats” / “OS IO reports” are able to capture the statement activity

We can generate AWR report from OEM for selected snaps if we know the performance lagging time frame

We can monitor IO by setting 10046 level 8 trace to capture all the waits for a particular session

alter session set events '10046 trace name context forever, level 8';

Excessing i/o can be found by examining the resultant trace file and  looking for i/o related waits such as

'db file sequential read' (Single-Block i/o - Index, Rollback Segment or Sort) 'db file scattered read' (Multi-Block i/o - Full table Scan).

Note:  set TIMED_STATISTICS = TRUE to capture the timing information otherwise comparisons will be meaningless.

1.       d)  Unnecessary sorting:

The first question to ask is “Does the DATA really need to be sorted” if sorting really needed try to allocate enough memory to prevent the sorts from spilling disks causing an IO problems

 Sorting is very expensive operation

-          High CPU usage

-          Potentially large disk usage

Notes:

-          Indexes may be used to provided presorted data

-          Sort merge joins inherently need to be sort

-          Some sorts doesn’t needed sorts, in this case explain plan should show NOSORT for this operation

-          Increase the sort area size to promote in memory sorts

-          Modify query to process less rows (less to sort)

-          Use sort_direct_writes to avoid flooding the buffer cache with sort blocks

-          If Pro*C  use release_cursor=yes to free up temporary segments held open

1.       e) Over parsing:

Over parsing implies that cursors are not being shared – if statements are referenced multiple times share rather than fill up the shared pool with the multiple copies of essentially the same statement

 Parsing is two types:

o   Hard parsing – if a new sql statement is issued which doesn’t exist in the shared pool, then this has to be parsed fully , Oracle has to allocate memory for the statement from shared pool to check syntactically and semantically, it is expensive  in terms of CPU usage and in the number of latch gets

o   Soft Parsing- If a session issues a sql statement which is already in the shared pool and it can be existed version that statement

Shared pool:

Oracle keeps sql statements, packages, object information and many other items in an area of SGA called shared pool; this sharable area of memory is managed as a sophisticated cache and heap manager rolled into one. It has 3 fundamental problems to over come

o   The unit of memory allocation is not constant – memory allocation from the pool can be bytes to kilobytes

o   Not all memory can be “freed” when a user finishes with it as the aim of the shared pool is to maximize the share ability of information, - the information in the memory may be useful to another session – Oracle cannot know in advance if the items will be use to anyone else or not

o   There is no disk area to page out to so that it is not like a traditional cache where there is a file backing store, only re creatable information can be discarded from the cache and it has to be re created when next needed.

 We can reduce the load on shared pool by below

Parse once execute many	This approach to use in OLTP type applications is to parse a statement only once and hold the curser open
Eliminating Literal SQL
Avoid Invalidations
CURSOR_SHARING parameter set to FORCE
SESSION_CACHED_CURSOR=nnn	Can be set at instance or session level
CURSOR_SPACE_FOR_TIME parameter
CLOSE_CACHED_OPEN_CURSORS
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_RESERVED_MIN_ALLOC
SHARED_POOL_SIZE
_SQLEXEC_PROGRESSION_COST	Set it to 0 to avoid SQL sharing issues
Precompiler HOLD_CURSOR and RELEASE_CURSOR options
Pinning cursors in the shared pool
DBMS_SHARED_POOL.KEEP	Objects should be KEPT immediately after instance startup
Flushing the SHARED POOL
DBMS_SHARED_POOL.PURGE

Diagnostics:

Once the statement has been determined then the next stage is to the reason for the query running slow

 Gather the explain plan for both slow and not slow queries and the best way of getting explain plan is to use AUTO TRACE

n  Obtaining an Explain plan

-          We can collect explain plan in manual method or using SQLT (SQLTXPLAIN)

è Manual methods of displaying the plan

Note: Oracle doesn’t support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables, with bind variables in general, the EXPLAIN PLAN output might not represent the real execution plan

a)       Get SQL_ID and HASH_VALUE of the SQL (we can find the SQL_ID of a statement from AWR or ASH report or select using V$SQL view)

SELECT /* TARGET SQL */ * FROM dual; SELECT sql_id, hash_value, substr(sql_text,1,40) sql_text  FROM  v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%' Example: SQL_ID        SQL_TEXT ------------- ---------------------------------------- 0xzhrtn5gkpjs SELECT /* TARGET SQL */ * FROM dual

b)      Last executed SQL: we can pull execution plans from the library cache if the SQL has already been executed, get the plan of the last executed SQL by following sql

SQL> set linesize 150 SQL> set pagesize 2000 SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

c)       Gather_plan_statistics hint

SQL> select /*+ gather_plan_statistics */ col1, col2 etc..... SQL> set linesize 150 SQL> set pagesize 2000 SQL> select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))

d)      Plan from memory

For SQL ID : select * from table(dbms_xplan.display_cursor('&sql_id')); select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL')); For SQL ID, Child Cursor : select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL')); For SQL Text : select t.* from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t where s.sql_text like '%&querystring%';

e)      From AWR

For SQL ID : select * from table(dbms_xplan.display_awr('&sql_id')) ; select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ; For SQL ID, Plan Hash Value in the current database : select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ; For SQL ID, Plan Hash Value in a different database ID : select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ; For SQL ID that was executed during a certain period : select t.* from (select distinct sql_id, plan_hash_value, dbid from dba_hist_sqlstat where sql_id = '&sql_id' and snap_id between &begin_snap and &end_snap) s, table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t; For SQL Text : select t.* from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid from dba_hist_sqltext q, dba_hist_sqlstat r where q.sql_id = r.sql_id and q.sql_text like '%&querystring%') s, table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;

f)       From SQL plan Baseline

For SQL Handle : select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle')); For SQL Handle, Plan Name : select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL')); For SQL Text : select t.* from (select distinct sql_handle, plan_name from dba_sql_plan_baselines where sql_text like '%&querystring%') s, table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;

è Using SQLT Diagnostic tool

There are 4 main ways to use SQLT

XPLAIN - Generates Explain Plan, SQL is not executed XTRACT- Generates Explain Plan for SQL that is already in memory or has been captured by AWR XECUTE -SQL is executed, then Execution Plan is obtained XTRXEC -This method combines the features of XTRACT and XECUTE.

è Using enhanced explain plan script

Enhanced Explain Plan Script (Doc ID 104824.1)

Once we have explain plan, review alongside the query text looking for anything unusual, the goal to identify any problem areas or anything that could be modified to run more efficiently.

-          Identify operations that may cause problems in system , these are general hints

Online Transaction Processing (OLTP) system	Full Table Scans Hash or Sort merge joins Index fast full scans or (large) index range scans Parallel query Nested loop joins with high cardinality outer table
Batch/Warehouse system	Nested loop joins Index lookups

-          Identify expensive operations: if the explain plan has steps that show large cardinality or cost figures then these may be areas where the biggest benefits of tuning can be reaped

2.       Statistics and analysis:

The CBO requires accurate statistics to enable it to work efficiently, the NUM_ROWS and LAST_ANALYZED columns in DBA_TABLES can be used to determine the statistics information on objects.

Recommendations for gathering CBO statistics: Different systems need different levels of statistical analysis due to differences in data

o   The accuracy of the stats depends on the sample size

o   The frequency of the gather statistics really depends on how frequently the objects involved in change and statistics have become inaccurate

o   Determine the best sample size by gathering statistics using different sample sizes (5% sample is generally sufficient for most of the tables although if possible 100% is suggested)

o   Gather statistics on indexes with compute only, since in index data already been sorted , compute on table is not acceptable

o   When considering when to gather statistics choose quite a period as possible, although gathering will work, if you gather statistics on objects that are actively being updated and inserted into, you are much more likely to encounter contention

o   It is recommended that users should store critical statistics to allow them to revert back to a working configuration in the event of a statistics change that significantly affects application performance (from 10g onwards database retains 31 days statistics, if gathering statistics causes critical statements to perform badly you can revert to the pre-analyze stats - by default 31 days)

o   In DSS/ warehouse environment, queries are generally not predictable so there is no stable environment/ query set to upset by gathering statistics

3.       Query text:

Review the query looking for any constructs which may cause you problems, these are few areas we can look for

o   Large INlists / statements

o   Outer joins –

o   Set operators (UNION etc) – incorrect usage of set operations can be very inefficient

o   No where clause or missing join predicates

o   Aggregate functions – cpu intensive functions

o   Sorting issues

o   Implicit type conversion

o   Any other strange or unexpected constructs

o   Views, inline views or sub queries

4.       Finding an acceptable plan:

Examination of an explain plan for the query can only give information on the query as it running currently.

Trace statistics:

I/O and timing stats (elapsed and cpu) can be very useful in determining which part of a query is the root cause of the problem.

Below are some potential causes for excessive I/O:

. Full table scans and Index Fast Full Scans

. Index range or Full scans

.Join order

.Excessive sort areas

Below are potential areas which cause high CPU:

Ø  Index range full scan

Ø  Join order

Ø  Excessive sort/hash areas

Ø  Nested Loops

5.       Break Query down into components parts:

Break the query into its constituent parts and tune

6.       Application Knowledge:

Good to gather the information about how a particular query should run based on application design

 Ref: Diagnostics for Query Tuning Problems (Doc ID 68735.1)