Tuning SQL not running as expected.

Scenario: 

Many times we see complains from application team, as till yesterday query execution was perfectly right and suddenly they started observing lag in execution or and some cases not even giving any result. When DBA check, mostly he see plan change and that is due many reasons. Below are few checks DBA can try to find why sql plan got changed. 

Though to Oracle Optimizer, you can just give hint if its a small query and you understand the execution flow as well as you know the data. But for many large queries its not possible to fix with hint, you have to live with Oracle Optimizer to decide the SQL plan. 

You can also forcefully map any plan to particular SQL ID, but that option need to consider carefully.

Why Plan got changed? or Why SQL is slow check few option mentioned below.

1) Check for any stale statistics.
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

2) Check any invalid index/Partition

col TABLE_NAME for a30
SYS@orcl>  select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');

no rows selected

SYS@orcl>  select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';

no rows selected

3) Check free memory shared pool area. Check too much hard parsing.

SYS@orcl> select * from (select SQL_ID,PARSING_SCHEMA_NAME, count(1) from v$sql group by SQL_ID,PARSING_SCHEMA_NAME order by  3 desc,2) where rownum<=10;

SQL_ID        PARSING_SCHEMA_NAME                     COUNT(1)
------------- ------------------------------ -----------------
9p6bq1v54k13j SYS                                            4
f8pavn1bvsj7t SYS                                            3
bgjhtnqhr5u9h SYS                                            3
ga9j9xk5cy9s0 SYS                                            3
05sghzkq6r6yv SYS                                            3
53saa2zkr6wc3 SYS                                            3
32bhha21dkv0v SYS                                            3
87gaftwrm2h68 SYS                                            3
b1wc53ddd6h3p SYS                                            3
asvzxj61dc5vs SYS                                            3

10 rows selected.

5) Wait/ Blocking analysis.

-- Display blocked session and their blocking session details.
SELECT sid, serial#, blocking_session_status, blocking_session
FROM   v$session
WHERE  blocking_session IS NOT NULL;


-- Display the resource or event the session is waiting for more than 1 minutes
SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait
FROM   v$session
where (seconds_in_wait/1000000) > 60
ORDER BY sid;

select sid,seq#,event,state,SECONDS_IN_WAIT from v$session_wait where SECONDS_IN_WAIT > 60;


--Monitor Top Waiting Event Using Active Session History (ASH)
SELECT h.event,
SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC;

6) Tablespace Usage


--Monitor Overall Oracle Tablespace
SELECT d.STATUS "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
d.initial_extent "Initial Extent",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;

7) Check for Memory parameters usage




--Estimation Of Shared Memory Pool Size vs. Time Saved
SELECT shared_pool_size_for_estimate "Pool Size (MB)",
estd_lc_size "Lib Cache Size (MB)",
estd_lc_time_saved/1000000 "Lib Cache Time Saved (Sec)"
FROM v$shared_pool_advice;


--Estimation Of Buffer Cache Size vs. Physical Reads
SELECT size_for_estimate "Cache Size (MB)",
buffers_for_estimate "Buffers",
estd_physical_read_factor "Estd Phys|Read Factor",
estd_physical_reads "Estd Phys| Reads"
FROM v$db_cache_advice
WHERE name = 'DEFAULT'
AND block_size = (SELECT VALUE FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';

---10g onwards
SYS@orcl> ---10g onwards
SYS@orcl> --SGA Advisor
SYS@orcl> select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

         SGA_SIZE   SGA_SIZE_FACTOR      ESTD_DB_TIME
----------------- ----------------- -----------------
              512                 1              3405
              256                 1              3724
              384                 1              3489
              640                 1              3385
              768                 2              2583
              896                 2              2547
             1024                 2              2547

7 rows selected.

SYS@orcl> ---10g onwards
SYS@orcl> --SGA Advisor
SYS@orcl> select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

         SGA_SIZE   SGA_SIZE_FACTOR      ESTD_DB_TIME
----------------- ----------------- -----------------
              512                 1              3405
              256                 1              3724
              384                 1              3489
              640                 1              3385
              768                 2              2583
              896                 2              2547
             1024                 2              2547

7 rows selected.

SYS@orcl>
SYS@orcl> ---PGA Advisor
SYS@orcl> select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ESTD_EXTRA_BYTES_RW
----------------------- ----------------- -------------------
               40370176                 0           157892608
               80740352                 0           157892608
              161480704                 1           157892608
              242221056                 1                   0
              322961408                 1                   0
              387553280                 1                   0
              452145152                 1                   0
              516738048                 2                   0
              581329920                 2                   0
              645922816                 2                   0
              968884224                 3                   0
             1291845632                 4                   0
             1937768448                 6                   0
             2583691264                 8                   0

14 rows selected.

8) Tuning SQL using 

SQL> @?/rdbms/admin/sqltrpt

9)  AWR various Report.

SQL> @?/rdbms/admin/awrsqrpt.sql --> awr report for only single sql_id
SQL> @?/rdbms/admin/awrrpt.sql   --> Traditional awr report for instance.

10) RAC Related awr Report

In 11gR2 there are two new scripts awrgrpt.sql AND awrgdrpt.sql for RAC

SQL> @?/rdbms/admin/awrgrpt.sql -- AWR Global Report (RAC) (global report)
SQL> @?/rdbms/admin/awrgdrpt.sql -- AWR Global Diff Report (RAC)
 
Other important scripts under $ORACLE_HOME/rdbms/admin

SQL> @?/rdbms/admin/spawrrac.sql -- Server Performance RAC report
SQL> @?/rdbms/admin/awrsqrpt.sql -- Standard SQL statement Report
SQL> @?/rdbms/admin/awrddrpt.sql -- Period diff on current instance
SQL> @?/rdbms/admin/awrrpti.sql -- Workload Repository Report Instance (RAC)

9) To more analysis why plan has changed one can check support.oracle.com