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

Daily Scripts

 1) To find I/O order by Tablespces:

select name, phyrds, phyrds * 100 / (select sum(phyrds) from v$filestat)
phywrts,  phywrts * 100 /(select sum(phywrts) from v$filestat)
from  v$datafile df, v$filestat fs
where df.file# = fs.file# order by phyrds desc;

2) Memory Hungry Oracle Process:


select   sid,name,value from   v$statname n,v$sesstat s where   n.STATISTIC# = s.STATISTIC# and   name like 'session%memory%'order by 3 desc;

select sum(bytes)/1024/1024 Mb from (select bytes from v$sgastat union select value bytes from 
v$sesstat s,v$statname n where n.STATISTIC# = s.STATISTIC# and  n.name = 'session pga memory');

3) To see OS Version using PL/SQL

select DBMS_UTILITY.PORT_STRING from dual;

Script to find Top CPU consuming PDBs (last few minutes)

 SET LINES 200

COL pdb_name FORMAT A20

SELECT

    p.name            AS pdb_name,

    ROUND(SUM(s.value)/100,2) AS cpu_seconds,

    COUNT(*)          AS sessions

FROM

    v$sesstat s,

    v$statname n,

    v$session se,

    v$pdbs p

WHERE

    s.statistic# = n.statistic#

AND s.sid = se.sid

AND n.name = 'CPU used by this session'

AND se.con_id = p.con_id

GROUP BY p.name

ORDER BY cpu_seconds DESC;

==============or================

SET LINES 200
COL pdb_name   FORMAT A15
COL username   FORMAT A15
COL program    FORMAT A30
COL service    FORMAT A20
COL cpu_mins   FORMAT 99990.99
SELECT
    ROWNUM AS rank,
    x.*
FROM
(
    SELECT
        p.name              AS pdb_name,
        s.sid,
        s.serial#,
        s.username,
        s.service_name      AS service,
        s.program,
        s.sql_id,
        ROUND(v.value / (100*60), 2) AS cpu_mins
    FROM
        v$statname  n,
        v$sesstat  v,
        v$session  s,
        v$pdbs     p
    WHERE
        n.name = 'CPU used by this session'
    AND v.statistic# = n.statistic#
    AND v.sid = s.sid
    AND s.con_id = p.con_id
    AND v.value > 0
    ORDER BY v.value DESC
) x
WHERE ROWNUM <= 10;

Automation to execute revoke statement

#!/bin/bash

CSV_FILE="revoke_access.csv"
ORACLE_HOME="/u01/app/oracle/product/19c/dbhome_1"
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_HOME PATH
# Skip header
tail -n +2 "$CSV_FILE" | while IFS=',' read -r CDB PDB GRANTEE OWNER OBJECT PRIV
do
  echo "Processing: $CDB | $PDB | $GRANTEE | $OWNER.$OBJECT | $PRIV"
  export ORACLE_SID=$CDB
  sqlplus -s / as sysdba <<EOF
SET FEEDBACK OFF
SET HEADING OFF
SET ECHO OFF
SET SERVEROUTPUT ON
ALTER SESSION SET CONTAINER=$PDB;
DECLARE
  v_status VARCHAR2(10);
  v_error  VARCHAR2(4000);
BEGIN
  BEGIN
    EXECUTE IMMEDIATE
      'REVOKE $PRIV ON $OWNER.$OBJECT FROM $GRANTEE';
    v_status := 'SUCCESS';
    v_error  := NULL;
  EXCEPTION
    WHEN OTHERS THEN
      v_status := 'FAILED';
      v_error  := SUBSTR(SQLERRM,1,4000);
  END;
  INSERT INTO SEC_AUDIT.REVOKE_CONTROL_LOG
  (
    CDB_NAME,
    PDB_NAME,
    GRANTEE,
    OBJECT_OWNER,
    OBJECT_NAME,
    PRIVILEGE,
    STATUS,
    ERROR_MESSAGE
  )
  VALUES
  (
    '$CDB',
    '$PDB',
    '$GRANTEE',
    '$OWNER',
    '$OBJECT',
    '$PRIV',
    v_status,
    v_error
  );
  COMMIT;
x
END;
/
EXIT;
EOF
done