How to Troubleshoot Long running SQL In Oracle 19C Database Step by step Lab

 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