Performance Tuning Oracle 19c (Finding CPU Consuming Query) - Multitenant DB

### Find the SNAPID

select CON_ID,SNAP_ID,STARTUP_TIME,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

####Below Query will find the sqlid which consumes the most CPU during mention time.######

SELECT
   p.name AS pdb_name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id,
   COUNT(*) AS cpu_samples
FROM
   dba_hist_active_sess_history ash,
   v$pdbs p
WHERE
   ash.sample_time BETWEEN
      to_date('11-JAN-2026 23:00','DD-MON-YYYY HH24:MI')
      AND
      to_date('12-JAN-2026 00:30','DD-MON-YYYY HH24:MI')
AND ash.session_state = 'ON CPU'
AND ash.con_id = p.con_id
GROUP BY
   p.name,
   ash.session_id,
   ash.session_serial#,
   ash.user_id,
   ash.program,
   ash.sql_id
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

######Once we have details of SQLID we can get the details of sql text fron history view as below:#######

SELECT sql_id, sql_text FROM dba_hist_sqltext WHERE sql_id='2yhkvj9bru81z';

###For Current session: 

select con_id,SERVICE_NAME,sid from v$session where sql_id='247cszx5pfbqa';

or

SELECT

   s.sql_id,
   s.executions_total,
   ROUND(s.cpu_time_total/1000000,2) cpu_seconds,
   t.sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t
WHERE
   s.sql_id = t.sql_id
AND s.sql_id = '2yhkvj9bru81z';

#######Search SQL text by keyword: Suppose you remember only part of query#######

SELECT sql_id, sql_text
FROM dba_hist_sqltext
WHERE LOWER(sql_text) LIKE '%material_records%';

###To find which SQL was running at a certain time:###

--This gives the most active SQLs in last 1 hour with full text.

SELECT
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text,
    COUNT(*) AS occurrences
FROM
    dba_hist_active_sess_history h,
    dba_hist_sqltext t
WHERE
    h.sql_id = t.sql_id
AND h.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
    h.sql_id,
    DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY occurrences DESC
FETCH FIRST 5 ROWS ONLY;

####Find SQL Text from Specific Snapshot--If you know snapshot range from AWR. Here we have snap_id 58 and 59.##

SELECT
t.sql_id,

   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
   );

###########If you want only SQL from a specific PDB:########

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT sql_id
      FROM dba_hist_sqlstat
      WHERE snap_id BETWEEN 58 AND 59
      AND con_id = 3        -- your PDB con_id
   );

   

####If You Know the name of PDB:####

--Query for PDB = TRAINING

SELECT
   t.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqltext t
WHERE
   t.sql_id IN (
      SELECT DISTINCT s.sql_id
      FROM dba_hist_sqlstat s,
           v$pdbs p
      WHERE s.con_id = p.con_id
      AND p.name = 'TRAINING'
      AND s.snap_id BETWEEN 58 AND 59
   );

   

###If You Want Top CPU SQL in TRAINING PDB###

SELECT
   s.sql_id,
   ROUND(SUM(s.cpu_time_delta)/1000000,2) AS cpu_seconds,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_sqlstat s,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   s.sql_id = t.sql_id
AND s.con_id = p.con_id
AND p.name = 'TRAINING'
AND s.snap_id BETWEEN 58 AND 59
GROUP BY
   s.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

###If you want ALL SQL executed in last 1 hour in TRAINING###

SELECT
   ash.sql_id,
   COUNT(*) AS samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND p.name = 'TRAINING'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY samples DESC
FETCH FIRST 10 ROWS ONLY;

###If you want to know who ran those SQLs:###

SELECT
   ash.sql_id,
   u.username,
   ash.program,
   COUNT(*) AS cpu_samples,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1) AS sql_text
FROM
   dba_hist_active_sess_history ash,
   dba_hist_sqltext t,
   v$pdbs p,
   dba_users u
WHERE
   ash.sql_id = t.sql_id
AND ash.con_id = p.con_id
AND ash.user_id = u.user_id
AND p.name = 'TRAINING'
AND ash.session_state = 'ON CPU'
AND ash.sample_time BETWEEN SYSDATE - (1/24) AND SYSDATE
GROUP BY
   ash.sql_id,
   u.username,
   ash.program,
   DBMS_LOB.SUBSTR(t.sql_text, 4000, 1)
ORDER BY cpu_samples DESC
FETCH FIRST 10 ROWS ONLY;

Important Notes:

DBA_HIST_SQLTEXT keeps SQL as long as AWR retention is set