Hi All,
In this post i will show how to Troubleshoot Long running SQL In Oracle 19C Database Step by step Lab, with explanation.
Most of the times we will receive complains from application team,saying that , as till yesterday query execution was right and all of suddenly they started observing lag in execution or and some cases not even giving any result.
as a DBA when we check from our end, mostly we will see plan change and that is due many reasons.
Below are few checks DBA can try to find why sql plan got changed.
Though the 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.
Course if action as DBA we should check below 11 steps one by one, all steps are not mandatory but as per the issue troubleshoot, we have to check until the issue cause and fix identified.
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';
Next
Check any invalid index/Partition :
col TABLE_NAME for a30
oracle@ggnode1> select owner,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED,STATUS from dba_indexes where status not in ('VALID','N/A');
oracle@ggnode1> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,SUBPARTITION_COUNT,LAST_ANALYZED,STATUS from dba_ind_partitions where status <> 'USABLE';
Check free memory shared pool area. Check too much hard parsing.
oracle@ggnode1> 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;
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;
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;
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';
SGA Advisor :::
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
PGA Advisor ::::
select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;
SQL Tuning using ::::
SQL> @?/rdbms/admin/sqltrpt
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.
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)
Note ::: To more analysis why plan has changed one can check support.oracle.com.
Thanks,
Srini
