Neil Chandler's DB Blog

-- perf_advice.sql v1.0 n chandler
--
-- pull advisor information from current PDB
set lines 400 pages 5000 verify on
col cdb                     for a15
col pdb                     for a15
col name                    for a60
col this_inst               for a12
col value                   for 999999999999999999999
col pga_target_for_estimate for 999999999999999999999
col STATISTIC                     for a80 wrap
col JAVA_POOL_SIZE_FOR_ESTIMATE   heading JAVA_POOL_SZ_ESTIMATE
col PGA_TARGET_FOR_ESTIMATE       heading PGA_TARG_ESTIMATE
col SHARED_POOL_SIZE_FOR_ESTIMATE heading S_POOL_SIZE_ESTIMATE
col SHARED_POOL_SIZE_FACTOR       heading SIZE_FACTOR
col this_pdb_name new_value this_pdb_nm for a15
col this_cdb_name new_value this_cdb_nm for a15
SELECT sys_context('userenv','cdb_name') AS this_cdb_name,sys_context('userenv','con_name') AS this_pdb_name FROM dual;
spool perf_advice.out append
prompt ==========================================================================================
prompt remember - this is an estimate and the larger the allocation, the less accurate it becomes
prompt            e.g. if your SGA is over 50G, its pretty unreliable
prompt ==========================================================================================
prompt
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,SYS_CONTEXT('userenv','instance') this_inst,inst_id,name,open_mode FROM gv$pdbs order by inst_id,name;
PROMPT gv$aq_message_cache_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gva.* FROM gv$aq_message_cache_advice gva ORDER BY inst_id,size_for_estimate;
PROMPT gv$java_pool_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvjpa.* FROM gv$java_pool_advice gvjpa ORDER BY inst_id,java_pool_size_for_estimate;
PROMPT gv$memory_target_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvmta.* FROM gv$memory_target_advice gvmta ORDER BY inst_id,memory_size;
PROMPT gv$mttr_target_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvmttr.* FROM gv$mttr_target_advice gvmttr ORDER BY inst_id,mttr_target_for_estimate;
PROMPT gv$pga_target_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvpga.* FROM gv$pga_target_advice gvpga ORDER BY inst_id,pga_target_for_estimate;
PROMPT gv$px_buffer_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvpx.* FROM gv$px_buffer_advice gvpx ORDER BY inst_id,statistic;
PROMPT gv$sga_target_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvsga.* FROM gv$sga_target_advice gvsga ORDER BY inst_id,sga_size;
PROMPT gv$shared_pool_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvspa.* FROM gv$shared_pool_advice gvspa ORDER BY inst_id,shared_pool_size_for_estimate;
PROMPT gv$streams_pool_advice
SELECT '&&this_cdb_nm' cdb, '&&this_pdb_nm' pdb,gvstream.* FROM gv$streams_pool_advice gvstream ORDER BY inst_id,streams_pool_size_for_estimate;
spool off

-- perf_blockers.sql v1.0 n chandler.
--                   v1.1 n chandler. materialize gv$session for improved performance and consistency - it will change across the 2 queries
--
-- show blocking sessions, and then again with the SQL text
set lines 250 pages 200
col username_program for a30
col wait_time_sec    for 999,999.00
col logon_time       for a19
col wait_class       for a11 trunc
col event            for a64 trunc
col status           for a5  trunc
col final_blocker    for a13 trunc
col program          for a20 trunc
col machine          for a20 trunc
col module           for a10 trunc
col service_name     for a15 trunc
col sid_serial_inst  for a15
col sql_text         for a80 wrap
col osuser           for a10 truncate
spool perf_blockers.out append
prompt gv$session only
WITH mysess AS (SELECT /*+ MATERIALIZE */ * FROM GV$SESSION)
SELECT gvs.sid||','||gvs.serial#||'@'||gvs.inst_id sid_serial_inst
       ,LPAD(' ', (level-1)*2, '>') || NVL(username,REGEXP_REPLACE(program,'^.* ','')) username_program
       ,gvs.osuser
       ,gvs.lockwait
       ,gvs.status
       ,gvs.sql_id
       ,gvs.event
       ,DECODE(gvs.final_blocking_session||'@'||gvs.final_blocking_instance,'@','blocker!',gvs.final_blocking_session||'@'||gvs.final_blocking_instance) final_blocker
       ,TO_CHAR(gvs.logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time
       ,wait_time_micro/1000000 wait_time_sec
       ,WAIT_CLASS
from   mysess gvs
where  level > 1
       or exists (SELECT 1 FROM mysess WHERE blocking_session = gvs.sid AND blocking_instance=gvs.inst_id)
connect by prior gvs.sid = gvs.blocking_session
           and prior gvs.inst_id=gvs.blocking_instance
start with gvs.blocking_session is null
/
set lines 250
prompt gv$session with sql_text
WITH mysess AS (SELECT /*+ MATERIALIZE */ * FROM GV$SESSION),
     blocks as
    (SELECT /*+ MATERIALIZE */  gvs.sid||','||gvs.serial#||'@'||gvs.inst_id sid_serial_inst
       ,LPAD(' ', (level-1)*2, '>') || NVL(username,REGEXP_REPLACE(program,'^.* ','')) username_program
       ,gvs.osuser
       ,gvs.status
       ,gvs.sql_id
       ,gvs.prev_sql_id
       ,gvs.module
       ,gvs.machine
       ,gvs.program
       ,TO_CHAR(gvs.logon_time,'YYYY-MM-DD HH24:MI:SS') logon_time
       ,wait_time_micro/1000000 wait_time_sec
       ,wait_class
       ,gvs.service_name
  FROM mysess gvs
 WHERE level > 1
    OR EXISTS (SELECT 1 FROM mysess WHERE blocking_session = gvs.sid AND blocking_instance=gvs.inst_id)
 CONNECT BY PRIOR gvs.sid     = gvs.blocking_session
        AND PRIOR gvs.inst_id = gvs.blocking_instance
  START WITH gvs.blocking_session is null
     )
SELECT  blk.sid_serial_inst
       ,blk.username_program
       ,blk.status
       ,blk.module
       ,blk.machine
       ,blk.program
       ,blk.service_name
       ,blk.sql_id
       ,blk.prev_sql_id
       ,'| '||replace(gvsql.sql_text,CHR(13),' ') as sql_text
  FROM blocks blk LEFT OUTER JOIN gv$sql gvsql ON (blk.sql_id=gvsql.sql_id)
/
spool off

-- perf_longquery.sql v1.0 n chandler 2025-10-10
--                    v1.1 n chandler 2025-10-14 better grouping
--
-- look through ASH history and see where we have queries - outside of transactions - running long
-- excluding any Oracle internal users
-- queries are also grouped by SQL_EXEC_ID and SQL_START_TIME as well as SID/SERIAL#/INST_ID to minimise
-- catching the wrong start/edn times across repeated queries with the same SQL_ID.
-- qc_sid_serial_instance shows if there was a query coordinator, and therefore if it's a parallel query slave
set line 2000 pages 5000 head on feed off trimspool on
col awr_retention          for 9999999999
col this_host              for a30
col this_cdb               for a15
col this_pdb               for a15
col transaction_id         for a16
col start_day              for a9
col start_time             for a19
col end_day                for a9
col end_time               for a19
col duration               for a25
col duration_seconds       for 999,999,999
col sid_serial_instance    for a20
col qc_sid_serial_instance for a20
set colsep ","
spool perf_longquery.out append
SELECT (SELECT EXTRACT(DAY FROM retention) AS awr_retention FROM dba_hist_wr_control
         WHERE con_id = SYS_CONTEXT('userenv','con_id') )                                awr_retention
       ,SYS_CONTEXT('userenv','host')                                                    this_host
       ,SYS_CONTEXT('userenv','cdb_name')                                                this_cdb
       ,SYS_CONTEXT('userenv','con_name')                                                this_pdb
       ,sql_id                                                                           sql_id
       ,sql_exec_id                                                                      execution_number
       ,session_id||'.'||session_serial#||' @'||instance_number                          sid_serial_instance
       ,DECODE(qc_session_id||'.'||qc_session_serial#||' @'||qc_instance_id
              ,'. @',null
              ,qc_session_id||'.'||qc_session_serial#||' @'||qc_instance_id)             qc_sid_serial_instance
--       ,TO_CHAR(sql_exec_start,'YYYY-MM-DD HH24:MI:SS')                                  sql_exec_start
--       ,TO_CHAR(MIN(sample_time),'DAY')                                                  start_day
       ,TO_CHAR(MIN(sample_time),'YYYY-MM-DD HH24:MI:SS')                                start_time
--       ,TO_CHAR(MAX(sample_time),'DAY')                                                  end_day
       ,TO_CHAR(MAX(sample_time),'YYYY-MM-DD HH24:MI:SS')                                end_time
       ,MAX(sample_time)-MIN(sample_time)                                                duration
       ,EXTRACT(DAY     FROM MAX(sample_time)-MIN(sample_time)) * 86400 +
        EXTRACT(HOUR    FROM MAX(sample_time)-MIN(sample_time)) *  3600 +
        EXTRACT(MINUTE  FROM MAX(sample_time)-MIN(sample_time)) *    60 +
        EXTRACT(SECOND  FROM MAX(sample_time)-MIN(sample_time))                          duration_seconds
  FROM dba_hist_active_sess_history
--  FROM gv$active_session_history
 WHERE xid IS NULL
   AND sql_id IS NOT NULL
   AND sql_exec_id IS NOT NULL
    -- exclude oracle internal users
   AND user_id NOT IN (SELECT user_id  FROM dba_users WHERE oracle_maintained='Y')
    -- restrict to last 32 days
   AND sample_time > systimestamp - INTERVAL '33' DAY
 GROUP BY sql_id,instance_number,session_id,session_serial#,qc_instance_id,qc_session_id,qc_session_serial#,sql_exec_id,sql_exec_start
 HAVING max(sample_time)-min(sample_time) > INTERVAL '20' SECOND
 ORDER BY duration,start_time
/
spool off
set head on feed on colsep " "

-- perf_longtrans.sql v1.1 n chandler 2025-10-10
--
-- look through ASH history and see where we have transactions (XID) running longer than 3 minutes
-- and show their SQL ID list so we can see what's going on
-- useful for TAC processing and drain_timeout
-- NOTE: queries outside of transactions are excluded (XID IS NULL) as are internal Oracle users
--       this is a sample of a sample. the information is approximate. the exact start and end,
--       and some mid-transaction SQL may be missed
set line 2000 pages 0 trimspool on head off feed off verify off echo off
col awr_retention    for 9999999999
col this_host        for a30
col this_cdb         for a15
col this_pdb         for a15
col transaction_id   for a16
col start_day        for a9
col start_time       for a19
col end_day          for a9
col end_time         for a19
col duration         for a25
col duration_minutes for 999,999,999
col sql_id_list      for a80 wrap
set colsep ","
spool perf_longtrans.out append
prompt AWR_RETENTION,THIS_HOST                     ,THIS_CDB       ,THIS_PDB       ,TRANSACTION_ID  ,START_DAY,START_TIME         ,END_DAY  ,END_TIME           ,DURATION                 ,DURATION_MINUTES,SQL_ID_LIST
SELECT (SELECT EXTRACT(DAY FROM retention) AS awr_retention FROM dba_hist_wr_control
         WHERE con_id = SYS_CONTEXT('userenv','con_id') )                                awr_retention
       ,SYS_CONTEXT('userenv','host')                                                    this_host
       ,SYS_CONTEXT('userenv','cdb_name')                                                this_cdb
       ,SYS_CONTEXT('userenv','con_name')                                                this_pdb
       ,xid                                                                              transaction_id
       ,TO_CHAR(MIN(sample_time),'DAY')                                                  start_day
       ,TO_CHAR(MIN(sample_time),'YYYY-MM-DD HH24:MI:SS')                                start_time
       ,TO_CHAR(MAX(sample_time),'DAY')                                                  end_day
       ,TO_CHAR(MAX(sample_time),'YYYY-MM-DD HH24:MI:SS')                                end_time
       ,MAX(sample_time)-MIN(sample_time)                                                duration
       ,EXTRACT(DAY     FROM MAX(sample_time)-MIN(sample_time)) * 1440 +
        EXTRACT(HOUR    FROM MAX(sample_time)-MIN(sample_time)) *   60 +
        EXTRACT(MINUTE  FROM MAX(sample_time)-MIN(sample_time))                          duration_minutes
       ,LISTAGG(DISTINCT sql_id,' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY sql_id) sql_id_list
  FROM dba_hist_active_sess_history
--  FROM gv$active_session_history
 WHERE xid IS NOT NULL
    -- exclude oracle internal users
   AND user_id NOT IN (SELECT user_id  FROM dba_users WHERE oracle_maintained='Y')
    -- maybe restrict to last 32 days
-- AND sample_time > systimestamp - INTERVAL '33' DAY
 GROUP BY xid
 HAVING max(sample_time)-min(sample_time) > INTERVAL '3' MINUTE
 ORDER BY duration,start_time
/
set lines 250 pages 250 head on feed on colsep " "
spool off

-- perf_pga.sql v0.9 n chandler
--
-- check the overall settings, generic SQL and ASH by process
-- needs to run as sysdba either from the CDB$ROOT or the container but will switch between the 2 to show different PGA
-- aspects - PDB related and CDB related, which is a higher allocation
-- IF you have multiple PDBs, you need to consider the allocation in those PDBs too
col HOST           for a30
col DT             for a10
col CDB            for a12
col PDB_NAME       for a12
col DISPLAY_VALUE  for a30
col CDB_VALUE      for a30
col SPFILE_VALUE   for a30
col PDB_VALUE      for a30
col inst_id        for 9999
col name           for a40
spool perf_pga.out append
-- get the current pdb
col pdb_nm new_value con_name for a15
select systimestamp,sys_context('userenv','con_name') pdb_nm from dual;
prompt switch to CDB to get full parameter set
alter session set container=CDB$ROOT;
SELECT SYS_CONTEXT('userenv','host') host
      ,to_char(sysdate,'YYYY-MM-DD') dt
      ,SYS_CONTEXT('userenv','cdb_name') CDB
      ,vp.name pdb_name
      ,gvp.name
      ,gvp.inst_id
      ,gvp.display_value
      ,gvp.value cdb_value
      ,gvsp.sid||'.'||gvsp.value spfile_value
      ,decode(to_char(pdbs.spare2),'1','deleted ('||pdbs.value$||')','0',decode(pdbs.value$,null,'not set in PDB',pdbs.value$)) pdb_value
      ,gvp.isdefault
FROM gv$parameter gvp
INNER      JOIN gv$pdbs vp          ON (gvp.inst_id=vp.inst_id)
LEFT OUTER JOIN gv$spparameter gvsp ON (gvp.name=gvsp.name AND gvp.inst_id=gvsp.inst_id)
LEFT OUTER JOIN pdb_spfile$    pdbs ON (gvp.name=pdbs.name AND pdbs.pdb_uid = vp.con_uid)
WHERE 1=1
  AND vp.name != 'PDB$SEED'
  AND gvp.name in ( 'sga_target'
                   ,'sga_max_size'
                   ,'db_name'
                   ,'pga_aggregate_target'
                   ,'pga_aggregate_limit'
                   ,'parallel_max_server'
                   ,'cpu_count'
                   ,'parallel_threads_per_cpu'
                   ,'db_cache_size'
                   ,'shared_pool_size'
                   ,'target_pdbs'
                   ,'streams_pool_size'
                   ,'large_pool_size'
                   ,'java_pool_size'
                   ,'parallel_force_local')
 ORDER BY cdb,vp.name,gvp.name,gvp.inst_id,gvsp.sid
/
prompt switch back to &&con_name
alter session set container=&&con_name;
col host       for a30
col cdb_name   for a15
col pdb_name   for a15
col time_now   for a20
col gb         for 999,999,999,999.00
col name       for a64
col value      for 999,999,999,999,999
prompt Get overall PGA information
prompt display gv$pgastat - only shows current container &&con_name (CDB$ROOT contains the full memory alloc)
select sys_context('userenv','host') host
      ,sys_context('userenv','cdb_name') cdb_name
      ,sys_context('userenv','con_name') pdb_name
      ,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') time_now
      ,gvp.inst_id
      ,gvp.name
      ,gvp.con_id
      ,gvp.value
      ,gvp.unit
      ,DECODE(unit,'bytes',VALUE/1024/1024/1024) GB
  FROM gv$pgastat gvp
 ORDER BY host,cdb_name,pdb_name,inst_id,name
/
prompt MAX PGA Allocation for &&con_name
select sys_context('userenv','host') host
      ,sys_context('userenv','cdb_name') cdb_name
      ,sys_context('userenv','con_name') pdb_name
      ,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') time_now
      ,gvp.inst_id
      ,gvp.name
      ,gvp.con_id
      ,gvp.value
      ,gvp.unit
      ,DECODE(unit,'bytes',VALUE/1024/1024/1024) GB
  FROM gv$pgastat gvp
 WHERE name = 'maximum PGA allocated'
 ORDER BY host,cdb_name,pdb_name,inst_id,name
/
alter session set container=CDB$ROOT;
prompt MAX PGA allocation for the CDB$ROOT
select sys_context('userenv','host') host
      ,sys_context('userenv','cdb_name') cdb_name
      ,sys_context('userenv','con_name') pdb_name
      ,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') time_now
      ,gvp.inst_id
      ,gvp.name
      ,gvp.con_id
      ,gvp.value
      ,gvp.unit
      ,DECODE(unit,'bytes',VALUE/1024/1024/1024) GB
  FROM gv$pgastat gvp
 WHERE name = 'maximum PGA allocated'
 ORDER BY host,cdb_name,pdb_name,inst_id,name
/
prompt switch back to &&con_name
alter session set container=&&con_name;
col max_pga_allocated     for 999,999,999,999,999
col max_pga_allocated_GB  for 999,999.0
col max_temp_space_allocated_gb for 999,999.0
col inst_id                     for 99999999
col day_group                   for a10
prompt look for peak PGA for sessions
prompt Top PGA consuming SQL from gv$active_session_history grouped by day, sql_id, phv
SELECT inst_id
      ,to_char(sample_time,'YYYY-MM-DD') day_group
      ,sys_context('userenv','con_name') pdb_name
      ,sql_id
      ,sql_plan_hash_value
      ,MAX(pga_allocated) max_pga_allocated
      ,MAX(pga_allocated/1024/1024/1024) max_pga_allocated_GB
      ,MAX(temp_space_allocated/1024/1024/1024) max_temp_space_allocated_gb
  FROM gv$active_session_history
 GROUP BY inst_id,sys_context('userenv','con_name'),to_char(sample_time,'YYYY-MM-DD'), sql_id, sql_plan_hash_value
 ORDER BY max_pga_allocated DESC,sql_id
 FETCH FIRST 30 ROWS ONLY
/
prompt Top PGA consuming SQL from dba_hist_active_sess_history grouped by day, sql_id, phv
SELECT to_char(sample_time,'YYYY-MM-DD') day_group
      ,sys_context('userenv','con_name') pdb_name
      ,sql_id
      ,sql_plan_hash_value
      ,MAX(pga_allocated) max_pga_allocated
      ,MAX(pga_allocated/1024/1024/1024) max_pga_allocated_GB
      ,MAX(temp_space_allocated/1024/1024/1024) max_temp_space_allocated_gb
  FROM dba_hist_active_sess_history
 WHERE pga_allocated IS NOT NULL
 GROUP BY sys_context('userenv','con_name'),to_char(sample_time,'YYYY-MM-DD'), sql_id, sql_plan_hash_value
 ORDER BY max_pga_allocated DESC,sql_id
 FETCH FIRST 30 ROWS ONLY
/
spool off

-- perf_planflip.sql v1.0 n chandler.
--
-- go through wherever there is a plan hash value, pull it out and show all against all sql_ids
-- where there is more than one plan across all locations
-- DBA_HIST_SQL_PLAN does not have a parser, so we get all of those but for everywhere else, excluse
-- all of the oracle maintained users, like SYS and SYSTEM as we probably don't care about those.
set pages 200 lines 200
col plan_hashes         for a150
col parsing_schema_name for a30
spool perf_planflip.out append
prompt Show all SQL with multiple different PLAN_HASH_VALUES, ignoring ORACLE_MAINTAINED users except with plans from DBA_HIST_SQL_PLAN
prompt Data from GV$SQL, GV$ACTIVE_SESSION_HISTORY, DBA_HIST_ACTIVE_SESS_HISTORY and DBA_HIST_SQL_PLAN
WITH source_data AS ( SELECT distinct(plan_hash_value) phv, sql_id, parsing_schema_name
                        FROM gv$sql gvs
                       WHERE plan_hash_value != 0 AND parsing_schema_name NOT IN (SELECT username FROM dba_users WHERE oracle_maintained='Y')
                      UNION ALL
                      SELECT distinct(sql_plan_hash_value) phv, sql_id, (SELECT username FROM dba_users WHERE user_id = gvsh.user_id) parsing_schema_name
                        FROM gv$active_session_history gvsh
                       WHERE sql_plan_hash_value != 0 AND user_id NOT IN (SELECT user_id FROM dba_users WHERE oracle_maintained = 'Y')
                      UNION ALL
                      SELECT distinct(sql_plan_hash_value) phv, sql_id,  (SELECT username FROM dba_users WHERE user_id = dhash.user_id) parsing_schema_name
                        FROM dba_hist_active_sess_history dhash
                       WHERE sql_plan_hash_value != 0 AND user_id NOT IN (SELECT user_id FROM dba_users WHERE oracle_maintained = 'Y')
                      UNION ALL
                      SELECT distinct(plan_hash_value) phv, sql_id, '.from DBA_HIST_SQL_PLAN' parsing_schema_name
                        FROM dba_hist_sql_plan WHERE plan_hash_value != 0
                     ),
     grouped_data AS (SELECT parsing_schema_name,sql_id ,LISTAGG(distinct RPAD(phv,12), ': ' ON OVERFLOW TRUNCATE) WITHIN GROUP (ORDER BY phv) AS plan_hashes
                        FROM source_data
                       WHERE sql_id is not null
                       GROUP BY parsing_schema_name,sql_id
                     )
SELECT parsing_schema_name,sql_id,plan_hashes
  FROM grouped_data
 WHERE LENGTH(plan_hashes) > 12
 --AND sql_id = '&sql_id'
 ORDER BY parsing_schema_name, LENGTH(plan_hashes), sql_id
/
prompt Investigate individually with perf_sqlcost.sql
spool off

-- perf_sesstemp.sql v0.1 n chandler 2025-10-25 show temp usage by session, group parallel sessions by qc to sum them together
set lines 250 pages 200
col sid                  for a15
col qc_sid               for a15
col osuser               for a15 trunc
col program              for a15 trunc
col temp_size_mb         for 999,999,999
col "PGA_ALLOC_MB (MAX)" for a20 trunc
col total_temp_size_mb   for 999,999,999,999
col curr_time            for a22 trunc
col name                 for a15
col db_unique_name       for a15
col this_pdb             for a15
spool perf_sesstemp.out append
SELECT TO_CHAR(systimestamp,'YYYY-MM-DD HH24:MI:SS') curr_time
       ,name
       ,db_unique_name
       ,SYS_CONTEXT('userenv','con_name') this_pdb
  FROM v$database;
SELECT
         vs.SID||','||vs.serial#||'@'||vs.inst_id sid
        ,vs.username
        ,vs.osuser
       ,vs.sql_id
        ,REGEXP_REPLACE(vs.program,'.* ') program
        ,vs.status
        ,vsu.TABLESPACE
        ,ROUND ((( vsu.blocks * param.VALUE ) /1024/1024 ) ,2 ) temp_size_mb
        ,LPAD(ROUND(vp.PGA_ALLOC_MEM/1024/1024),7,' ')|| ' ('|| LPAD(ROUND(vp.PGA_MAX_MEM/1024/1024),7,' ')||')' "PGA_ALLOC_MB (MAX)"
        ,gpx.qcsid||','||gpx.qcserial#||'@'||gpx.qcinst_id qc_sid
        ,gpx.degree
        ,gpx.req_degree
    FROM gv$session vs INNER JOIN gv$process vp      ON (vs.inst_id = vp.inst_id AND vs.paddr=vp.addr)
                       INNER JOIN gv$parameter param ON (vs.inst_id = param.inst_id AND param.name='db_block_size')
                  LEFT OUTER JOIN gv$sort_usage vsu  ON (vs.inst_id = vsu.inst_id AND vs.saddr=vsu.session_addr)
                  LEFT OUTER JOIN gv$px_session gpx  ON (gpx.saddr=vs.saddr)
   WHERE vs.type <> 'BACKGROUND'
ORDER BY vsu.TABLESPACE, vsu.blocks,vs.status desc
/
prompt Sessions grouped by Query Coordinator so all parallel slaves are added up
with base_data as (
SELECT
         vs.SID||','||vs.serial#||'@'||vs.inst_id sid
        ,vs.username
        ,vs.osuser
        ,vs.sql_id
        ,REGEXP_REPLACE(vs.program,'.* ') program
        ,vs.status
        ,substr(status,1,1) status_bit
        ,vsu.TABLESPACE
        ,ROUND ((( vsu.blocks * param.VALUE ) /1024/1024 ) ,2 ) size_mb
        ,ROUND(vp.PGA_ALLOC_MEM/1024/1024) pga_alloc_mb
        ,ROUND(vp.PGA_MAX_MEM/1024/1024) pga_max_mb
        ,gpx.qcsid
        ,gpx.qcserial#
        ,gpx.qcinst_id
    FROM gv$session vs INNER JOIN gv$process vp      ON (vs.inst_id = vp.inst_id AND vs.paddr=vp.addr)
                       INNER JOIN gv$parameter param ON (vs.inst_id = param.inst_id AND param.name='db_block_size')
                  LEFT OUTER JOIN gv$sort_usage vsu  ON (vs.inst_id = vsu.inst_id AND vs.saddr=vsu.session_addr)
                  LEFT OUTER JOIN gv$px_session gpx  ON (gpx.saddr=vs.saddr)
   WHERE vs.type <> 'BACKGROUND'
ORDER BY vsu.TABLESPACE
        ,vsu.segfile#
        ,vsu.segblk#
        ,vsu.blocks )
select qcsid
      ,qcserial#
      ,qcinst_id
      ,sql_id
      ,LISTAGG(DISTINCT status_bit,' ' ON OVERFLOW TRUNCATE) statuses
      ,sum(pga_alloc_mb)
      ,sum(pga_max_mb)
      ,sum(size_mb) total_temp_size_mb
      ,count(*) num_parallel_procs
  FROM base_data
 WHERE qcsid IS NOT NULL
 GROUP BY qcsid,qcserial#,qcinst_id,sql_id
 ORDER BY qcsid,qcserial# NULLS FIRST,qcinst_id
/
spool off

-- perf_sga.sql v0.8 n chandler 2025-10-28
--
-- check the overall settings, generic SQL and ASH by process
-- needs to run as sysdba either from the CDB$ROOT or the container but will switch between the 2 to show different SGA
-- aspects - PDB related and CDB related, which is a higher allocation
-- If you have multiple PDBs, you need to consider the allocation in those PDBs too
col HOST           for a30
col DT             for a10
col CDB            for a12
col PDB_NAME       for a12
col DISPLAY_VALUE  for a30
col CDB_VALUE      for a30
col SPFILE_VALUE   for a30
col PDB_VALUE      for a30
col inst_id        for 9999
col name           for a40
col mb             for 999,999,999,999
col gets           for 999,999,999,999
spool perf_sga.out append
-- get the current pdb
col pdb_nm new_value con_name for a15
select systimestamp,sys_context('userenv','con_name') pdb_nm from dual;
prompt switch to CDB to get full parameter set
alter session set container=CDB$ROOT;
SELECT SYS_CONTEXT('userenv','host') host
      ,to_char(sysdate,'YYYY-MM-DD') dt
      ,SYS_CONTEXT('userenv','cdb_name') CDB
      ,vp.name pdb_name
      ,gvp.name
      ,gvp.inst_id
      ,gvp.display_value
      ,gvp.value cdb_value
      ,gvsp.sid||'.'||gvsp.value spfile_value
      ,decode(to_char(pdbs.spare2),'1','deleted ('||pdbs.value$||')','0',decode(pdbs.value$,null,'not set in PDB',pdbs.value$)) pdb_value
      ,gvp.isdefault
FROM gv$parameter gvp
INNER      JOIN gv$pdbs vp          ON (gvp.inst_id=vp.inst_id)
LEFT OUTER JOIN gv$spparameter gvsp ON (gvp.name=gvsp.name AND gvp.inst_id=gvsp.inst_id)
LEFT OUTER JOIN pdb_spfile$    pdbs ON (gvp.name=pdbs.name AND pdbs.pdb_uid = vp.con_uid)
WHERE 1=1
  AND vp.name != 'PDB$SEED'
  AND gvp.name in ( 'sga_target'
                   ,'sga_max_size'
                   ,'db_name'
                   ,'pga_aggregate_target'
                   ,'pga_aggregate_limit'
                   ,'parallel_max_server'
                   ,'cpu_count'
                   ,'parallel_threads_per_cpu'
                   ,'db_cache_size'
                   ,'shared_pool_size'
                   ,'target_pdbs'
                   ,'streams_pool_size'
                   ,'large_pool_size'
                   ,'java_pool_size'
                   ,'parallel_force_local')
 ORDER BY cdb,vp.name,gvp.name,gvp.inst_id,gvsp.sid
/
prompt switch back to &&con_name
alter session set container=&&con_name;
col value for a30
prompt FROM gv$sga_info
select gvsga.inst_id
      ,gvsga.con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id =gvsga.con_id AND gvp.inst_id=gvsga.inst_id),'CDB') pdb_name
      ,gvsga.name
      ,gvsga.bytes
      ,ROUND(gvsga.bytes/1024/1024) mb
      ,gvsga.resizeable
  FROM gv$sgainfo gvsga
 ORDER BY inst_id,con_id,name;
prompt FROM gv$sgastat
SELECT gvsga.inst_id
      ,gvsga.con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id =gvsga.con_id AND gvp.inst_id=gvsga.inst_id),'CDB') pdb_name
      ,gvsga.pool
      ,'free'
      ,round(sum(gvsga.bytes/1024/1024)) MB
  FROM gv$sgastat gvsga
 WHERE pool IS NOT NULL
   AND name = 'free memory'
 GROUP BY inst_id,con_id,pool
UNION ALL
SELECT gvsga.inst_id
      ,gvsga.con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id =gvsga.con_id AND gvp.inst_id=gvsga.inst_id),'CDB') pdb_name
      ,gvsga.pool
      ,'used'
      ,round(sum(gvsga.bytes/1024/1024)) mb
 FROM gv$sgastat gvsga
WHERE pool IS NOT NULL
  AND name != 'free memory'
GROUP BY gvsga.inst_id,gvsga.con_id,gvsga.pool
UNION ALL
SELECT gvsga.inst_id
      ,gvsga.con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id =gvsga.con_id AND gvp.inst_id=gvsga.inst_id),'CDB') pdb_name
      ,gvsga.pool
      ,gvsga.name
      ,round(gvsga.bytes/1024/1024) MB
 FROM gv$sgastat  gvsga
WHERE gvsga.pool IS NULL
ORDER BY 1,4 NULLS LAST,2
/
prompt How many subpools in shared pool (this instance only)
SELECT COUNT(DISTINCT kghluidx) num_subpools FROM sys.x$kghlu WHERE kghlushrpool = 1;
prompt latch children for shared pool (which subpools are used)
prompt is one exponentially more used than the others? if so, why?
SELECT inst_id,child#, gets
  FROM gv$latch_children
 WHERE name = 'shared pool'
 ORDER BY inst_id,child#;
prompt get subpool breakdown from CDB$ROOT
alter session set container=CDB$ROOT;
prompt X$KSMSS subpool high level info ON THIS NODE
prompt Are the sizes fairly balanced across subpools or is one dominating
SELECT xksmss.inst_id
      ,xksmss.con_id
      ,(SELECT NVL(name,'CDB') FROM v$pdbs gvp WHERE gvp.con_id = xksmss.con_id) pdb_name
      ,xksmss.ksmdsidx subpool
      ,'shared pool' pool
      ,'free memory' name
      ,SUM(xksmss.ksmsslen)/1024/1024 MB
  FROM x$ksmss xksmss
 WHERE xksmss.ksmssnam='free memory'
 GROUP BY xksmss.inst_id,xksmss.con_id,xksmss.ksmdsidx
UNION ALL
SELECT xksmss.inst_id
      ,xksmss.con_id
      ,(SELECT NVL(name,'CDB') FROM v$pdbs gvp WHERE gvp.con_id =xksmss.con_id) pdb_name
      ,xksmss.ksmdsidx subpool
      ,'shared pool' pool
      ,'used' name
      ,SUM(xksmss.ksmsslen)/1024/1024 MB
  FROM x$ksmss xksmss
 WHERE xksmss.ksmssnam!='free memory'
 GROUP BY xksmss.inst_id,xksmss.con_id,xksmss.ksmdsidx
 ORDER BY 1,4,2;
prompt first 50 biggest chunks in the SGA subpools on THIS NODE, excluding con_id 1 and 2
SELECT xksmss.inst_id
      ,xksmss.con_id
      ,(SELECT NVL(name,'CDB') FROM v$pdbs gvp WHERE gvp.con_id =xksmss.con_id) pdb_name
      ,xksmss.ksmdsidx subpool
      ,'shared pool' pool
      ,xksmss.ksmssnam name
      ,xksmss.ksmsslen/1024/1024 MB
  FROM x$ksmss xksmss
 WHERE xksmss.ksmssnam!='free memory'
   AND xksmss.con_id not in (1,2)
 ORDER BY inst_id,mb desc,subpool,con_id
FETCH FIRST 50 ROWS ONLY
/
col db_block_size for a15
prompt an overview of the db_cache from the CDB
prompt We are mainly looking to see if the db_cache is underused (lots of free space)
SELECT gvbh.inst_id
      ,gvbh.con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id = gvbh.con_id AND gvp.inst_id=gvbh.inst_id),'CDB') pdb_name
      ,gvbh.status
      ,count(*) num_blocks
      ,(count(*) * gvp.value)/1024/1024 MB
      ,gvp.value db_block_size
  FROM gv$bh gvbh LEFT OUTER JOIN gv$parameter gvp ON (gvp.name = 'db_block_size' AND gvp.inst_id = gvbh.inst_id)
 GROUP BY gvbh.inst_id,gvbh.con_id,gvbh.status,gvp.value
 ORDER BY gvbh.inst_id,gvbh.con_id,gvbh.status,gvp.value
/
prompt back to PDB &&con_name
alter session set container=&&con_name;
prompt resize operations, transferring memory between pools
prompt check the size of the db_cache(s) and ensure they have not become too small
prompt Check for IMMEDIATE GROW operations of a pool, which indicates it was going to run out of memory
prompt Check that the resizes were successful.
prompt ERROR status can indicate memory leaks, lack of overall memory for this system and/or other memory issues
col component for a40
col parameter for a40
SELECT inst_id
      ,con_id
      ,NVL((SELECT name FROM gv$pdbs gvp WHERE gvp.con_id = gvsro.con_id AND gvp.inst_id=gvsro.inst_id),'CDB') pdb_name
      ,component
      ,oper_type
      ,oper_mode
      ,parameter
      ,ROUND(initial_size/1024/1024) initial_size_mb
      ,ROUND(target_size/1024/1024) target_size_mb
      ,ROUND(final_size/1024/1024) final_size_mb
      ,ROUND(final_size-initial_size)/1024/1024 change_mb
      ,status
      ,start_time
      ,end_time
  FROM gv$sga_resize_ops gvsro
 ORDER BY inst_id,start_time,component,parameter
/
spool off

-- perf_spm_hints.sql n chandler. v1.1 2025-09-11: show all SPM and the SPM hints
--                    n chandler. v1.2 2025-11-11: comment
set lines 250 pages 5000
col typ           for a30
col name          for a50
col signature     for 99999999999999999999
col category      for a14
col outline_hints for a140 wrap
col comp_data     for a100
col con_name      for a15
col status        for a20
set long 199
col sp1 new_value sp2
select name||'-'||sys_context('userenv','con_name') sp1 from v$database;
spool perf_spm_hints.&&sp2..out
prompt Show all profiles, patches and baselines
WITH spm_list as (
                   SELECT 'dba_sql_profiles'       typ, con_id,1 as obj_type, name,signature,status,category
                     FROM CONTAINERS(dba_sql_profiles)
                   UNION ALL
                   SELECT 'dba_sql_plan_baselines' typ, con_id, 2 as obj_type, plan_name as name,signature,DECODE(enabled,'YES','ENABLED','NOT ENABLED')||':'||DECODE(accepted,'YES','ACCEPTED','NOT ACCEPTED') as status,null as category
                     FROM CONTAINERS(dba_sql_plan_baselines)
                   UNION ALL
                   SELECT 'dba_sql_patches'        typ, con_id,3 as obj_type, name,signature,status,category
                     FROM CONTAINERS(dba_sql_patches)
                 )
SELECT s.con_id, s.obj_type, s.typ, s.name, s.signature, s.status, s.category
  FROM spm_list s
ORDER BY s.obj_type,s.con_id,s.status,s.name
/
WITH spm_list as (
                   SELECT 'dba_sql_profiles'       typ, con_id, 1 as obj_type, name,signature,status,category
                     FROM CONTAINERS(dba_sql_profiles)
                   UNION ALL
                   SELECT 'dba_sql_plan_baselines' typ, con_id, 2 as obj_type, plan_name as name,signature,DECODE(enabled,'YES','ENABLED','NOT ENABLED')||':'||DECODE(accepted,'YES','ACCEPTED','NOT ACCEPTED') as status,null as category
                     FROM CONTAINERS(dba_sql_plan_baselines)
                   UNION ALL
                   SELECT 'dba_sql_patches'        typ, con_id, 3 as obj_type, name,signature,status,category
                     FROM CONTAINERS(dba_sql_patches)
                 ),
      profiles as (
select name,signature,category,obj_type,hint as outline_hints
   from (select p.name, p.signature, p.category, sd.obj_type, row_number()
         over (partition by sd.signature, sd.category order by sd.signature) row_num,
         extractValue(value(t), '/hint') hint
         from CONTAINERS(sys.sqlobj$data) sd JOIN spm_list p on (p.signature=sd.signature and p.con_id = sd.con_id and p.obj_type=sd.obj_type),
              table(xmlsequence(extract(xmltype(sd.comp_data), '/outline_data/hint'))) t
         )
   order by row_num
                  ),
      baselines as (
select name,signature,category,obj_type,hint as outline_hints
   from (select p.name, p.signature, p.category, sd.obj_type, row_number()
         over (partition by sd.signature, sd.category order by sd.signature) row_num,
         extractValue(value(t), '/hint') hint
         from CONTAINERS(sys.sqlobj$plan) sd JOIN spm_list p on (p.signature=sd.signature and p.con_id = sd.con_id and p.obj_type=sd.obj_type),
              table(xmlsequence(extract(xmltype(sd.other_xml), '/other_xml/outline_data/hint'))) t
   where 1=1
     and other_xml is not null
         )
   order by row_num
                   ),
      patches as (
select name,signature,category,obj_type,hint as outline_hints
   from (select p.name, p.signature, p.category, sd.obj_type, row_number()
         over (partition by sd.signature, sd.category order by sd.signature) row_num,
         extractValue(value(t), '/hint') hint
         from CONTAINERS(sys.sqlobj$plan) sd JOIN spm_list p on (p.signature=sd.signature and p.con_id = sd.con_id and p.obj_type=sd.obj_type),
              table(xmlsequence(extract(xmltype(sd.other_xml), '/other_xml/outline_data/hint'))) t
   where 1=1
     and other_xml is not null
         )
   order by row_num
                   )
SELECT * FROM profiles
UNION ALL
SELECT * FROM baselines
UNION ALL
SELECT * FROM patches
order by 1,3
/
spool off
prompt example dbms_xplan to show hints: select * from dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(plan_name=>'SQL_PLAN_g0t8ntd7ck5kd8a6d2c10',FORMAT=>'OUTLINE');

-- perf_sqlcost.sql v1.0 n chandler
--                  v1.1 n chandler 2025-11-11 use NULLIF, add elapsed time per execution per row
--                  v1.2 n chandler 2025-11-11 add dba_hist_sqlstat
--                  v1.3 n chandler 2025-11-12 add extra fudge of figures if executions or row_processed is 0
--                  v1.4 n chandler 2025-11-28 add per-execution info from ASH
--                  v1.5                       and bug fix
--                  v1.6 n chandler 2025-12-24 sort elapsed per row. ignore null sql_exec_id
--                  v1.7 n chandler 2025-12-30 tweak output from micro to ms
--
-- show plans and time for a given sql_id
set lines 250 pages 200 verify off
col sql_id              for a15
col profile             for a7
col patch               for a5
col baseline            for a8
col is_bind_sensitive   for a17
col is_bind_aware       for a13
col is_shareable        for a12
col inst_id             for 9999
col sql_profile         for a10 truncate
col PARSING_SCHEMA_NAME for a12 truncate
col "SQL_ID (CHILD)"    for a18 truncate
set long 1000000
define sql_id
undefine sql_id
spool perf_sqlcost.&&sql_id..out append
prompt SQL Full Text
set long 200000
col sql_text for a199
select sql_text from dbA_hist_sqltext where sql_id = '&&sql_id' AND rownum=1;
col cost      for 999,999,999,999
col cpu_cost  for 999,999,999,999
col io_cost   for 999,999,999,999
col timestamp for a25
prompt Get some execution history dba_hist_sqlstat
SELECT TO_CHAR(snap.end_interval_time,'YYYY-MM-DD HH24:MI:SS') end_interval_time
      ,dhs.instance_number
      ,snap.snap_id
      ,snap.con_id
      ,dhs.dbid
      ,dhs.sql_id
      ,dhs.plan_hash_value
      ,dhs.optimizer_cost
      ,dhs.sql_profile
      ,dhs.parsing_schema_name
      ,dhs.executions_delta
      ,dhs.invalidations_delta
      ,dhs.rows_processed_delta
      ,dhs.elapsed_time_delta
      ,ROUND(dhs.elapsed_time_delta/NULLIF(dhs.executions_delta,0)/NULLIF(dhs.rows_processed_delta,0)) elapsed_per_exec_per_row_micro
 --     ,dhs.io_offload_elig_bytes_delta
 --     ,dhs.io_interconnect_bytes_delta
  FROM dba_hist_sqlstat dhs JOIN dba_hist_snapshot snap on (dhs.snap_id = snap.snap_id AND dhs.instance_number = snap.instance_number)
                            JOIN v$pdbs            pdbs ON (pdbs.dbid = dhs.dbid)
 WHERE sql_id = '&&sql_id'
   AND snap.con_id = sys_context('userenv','con_id')
   AND dhs.executions_delta > 0
 ORDER BY snap.end_interval_time,dhs.dbid,snap.snap_id;
prompt Plan and cost from DBA_HIST_SQL_PLAN for &&sql_id
SELECT sql_id
      ,plan_hash_value
      ,cost
      ,cpu_cost
      ,io_cost
      ,to_char(timestamp,'YYYY-MM-DD HH24:MI:SS') timestamp
  FROM dba_hist_sql_plan
 WHERE sql_id = '&&sql_id'
   AND other_xml is not null
 ORDER BY cost,sql_id,timestamp;
prompt Plans and basic info from DBA_HIST_ACTIVE_SESS_HISTORY
SELECT instance_number inst_id
      ,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
      ,sql_plan_hash_value
      ,to_char(sample_time,'YYYY-MM-DD HH24:MI') sample_time_minute_group
      ,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
      ,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
      ,count(*) ash_count
  FROM dba_hist_active_sess_history 
 WHERE sql_id = '&&sql_id'
 GROUP BY instance_number,sql_id||' ('||sql_child_number||')',sql_plan_hash_value,to_char(sample_time,'YYYY-MM-DD HH24:MI')
 ORDER BY 1,2,4,3
/
col sample_time_minute_group for a25
prompt Plans and basic info from GV$ACTIVE_SESSION_HISTORY
SELECT inst_id
      ,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
      ,sql_plan_hash_value
      ,to_char(sample_time,'YYYY-MM-DD HH24:MI') sample_time_minute_group
      ,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
      ,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
      ,count(*) ash_count
  FROM gv$active_session_history 
 WHERE sql_id = '&&sql_id'
 GROUP BY inst_id,sql_id||' ('||sql_child_number||')',sql_plan_hash_value,to_char(sample_time,'YYYY-MM-DD HH24:MI')
 ORDER BY 1,2,4,3
/
col duration          for a30
col start_time_approx for a30
col end_time_approx   for a30
prompt Plans and basic info from GV$ACTIVE_SESSION_HISTORY EVERY RUN
SELECT inst_id
      ,sql_id||' ('||sql_child_number||')' "SQL_ID (CHILD)"
      ,sql_plan_hash_value
      ,sql_exec_id
      ,sql_exec_start
      ,MIN(sample_time) start_time_approx
      ,MIN(sample_time) end_time_approx
      ,(MAX(sample_time)-MIN(sample_time)) duration
      ,ROUND(MAX(pga_allocated)/1024/1024) max_pga_allocated_mb
      ,ROUND(MAX(temp_space_allocated)/1024/1024) max_temp_space_allocated_mb
      ,count(*) ash_count
  FROM gv$active_session_history 
 WHERE sql_id = '&&sql_id'
   AND sql_exec_id IS NOT NULL
 GROUP BY inst_id,sql_exec_id,sql_exec_start,sql_id||' ('||sql_child_number||')',sql_plan_hash_value
 ORDER BY 1,2,4,3
/
prompt Plans and speed in GV$SQL for &&sql_id - total per child, per row and per execution
SELECT inst_id
      ,sql_id||' ('||child_number||')' "SQL_ID (CHILD)"
      ,plan_hash_value
      ,parsing_schema_name
      ,DECODE(sql_profile,null,'N','Y') profile
      ,DECODE(sql_patch,null,'N','Y') patch
      ,DECODE(sql_plan_baseline,null,'N','Y') baseline
      ,is_bind_sensitive
      ,is_bind_aware
      ,is_shareable
      ,executions
      ,rows_processed
      ,ROUND(rows_processed/NULLIF(executions,0))rows_per_exec
      ,ROUND(elapsed_time/1000,1) elapsed_time_ms
      ,ROUND(elapsed_time/NULLIF(rows_processed,0)/1000,1) elapsed_per_row_ms
      ,ROUND(elapsed_time/NULLIF(executions,0)/1000,1) elapsed_per_exec_ms
  FROM gv$sql
 WHERE sql_id = '&&sql_id'
 ORDER BY inst_id,sql_id,child_number,plan_hash_value
/
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col IO_CELL_OFFLOAD_RETURNED_MB for 999,999,999,999
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col IO_CELL_UNCOMPRESSED_MB     for 999,999,999,999
col PHYSICAL_READ_MB            for 999,999,999,999
col IO_CELL_OFFLOAD_ELIGIBLE_MB for 999,999,999,999
col OPTIMIZED_PHY_READ_REQUESTS for 999,999,999,999
--PHYSICAL_READ_BYTES — number of bytes read by the SQL
--PHYSICAL_READ_REQUESTS — number of read requests issued by the SQL
--PHYSICAL_WRITE_BYTES — number of bytes written by the SQL
--PHYSICAL_WRITE_REQUESTS — number of write requests issued by the SQL
--IO_CELL_OFFLOAD_ELIGIBLE_BYTES — number of bytes eligible for predicate offload to Exadata storage server
--IO_CELL_OFFLOAD_RETURNED_BYTES — number of bytes returned by smart scans
--IO_INTERCONNECT_BYTES — number of bytes exchanged between the database and the storage servers
--IO_CELL_UNCOMPRESSED_BYTES — number of uncompressed bytes read, where uncompressed bytes is the size after decompression
--OPTIMIZED_PHY_READ_REQUESTS — number of read requests satisfied from Exadata Smart Flash Cache, or read requests avoided due to storage index or columnar cache
prompt Offloading
prompt hard to be conclusive for multiple executions...
prompt could have been 1 good and 2 bad, etc...
SELECT sql_id
       ,child_number
       ,plan_hash_value phv
       ,executions execs
       ,ROUND(IO_CELL_OFFLOAD_ELIGIBLE_BYTES /1024/1024) IO_CELL_OFFLOAD_ELIGIBLE_MB
       ,ROUND(IO_CELL_OFFLOAD_RETURNED_BYTES /1024/1024) IO_CELL_OFFLOAD_RETURNED_MB
       ,ROUND(IO_CELL_UNCOMPRESSED_BYTES /1024/1024)     IO_CELL_UNCOMPRESSED_MB
       ,ROUND(IO_INTERCONNECT_BYTES/1024/1024)           IO_INTERCONNECT_MB
       ,ROUND(PHYSICAL_READ_BYTES/1024/1024)             PHYSICAL_READ_MB
       ,ROUND(OPTIMIZED_PHY_READ_REQUESTS)             OPTIMIZED_PHY_READ_REQUESTS
       ,100 - (100*(IO_INTERCONNECT_BYTES) / NULLIF(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0)) PCT_OFFLOADED
  from gv$sql s
where executions > 0
   AND sql_id = '&&sql_id'
/
spool off

-- perf_sqlobj.sql v1.0 n chandler.
--                 v1.1 n chandler. 2025-11-20 account for covering indexes
--                 v1.2 n chandler. 2025-11-28 formatting some columns
--
prompt show object relevant statistics for tables/indexes accessed by a sql_id
prompt object names are pulled out of dba_hist_sql_plan and basic info shown
prompt this should help you determine staleness, selectivity and cardinality, and identify alternate execution options
prompt also ensure SAMPLE is 0 or 100% - anything else is using ESTIMATE which is less stable and can lead to bad histograms
set pages 200 lines 250
col OWNER              for a30
col TABLE_NAME         for a30
col OBJECT_OWNER       for a30
col COLUMN_NAME        for a30
col PARTITION_NAME     for a15
col SUBPARTITION_NAME  for a30
col STALE_STATS        for a5
col SAMPLE             for a5
col OBJECTS            for a80
col plan_hash_values   for a80 wrap
col object_type        for a10
col density            for 0.999999999999999
col num_distinct       for 999999999999999
col num_nulls          for 999999999999999
define sql_id
undefine sql_id
break on index_name
prompt Enter the sql_id of the query to pull all objects out of the execution plans
prompt &&sql_id
spool perf_sqlstats.&&sql_id..out append
prompt
prompt What objects are in the execution plans:
prompt all objects may not appear in all executions plans
prompt - different indexes can be selected
prompt - objects can be transformed out of plans
prompt - covering indexes can eleminate table access
WITH objects as ( SELECT distinct dhsp.object_type||': '||dhsp.object_owner||'.'||dhsp.object_name objects
                        ,dhsp.sql_id
                        ,dhsp.plan_hash_value
                    FROM dba_hist_sql_plan dhsp
                   WHERE 1=1
                     AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                  UNION
                  SELECT distinct gvp.object_type||': '||gvp.object_owner||'.'||gvp.object_name objects
                        ,gvp.sql_id
                        ,gvp.plan_hash_value
                    FROM gv$sql_plan gvp
                   WHERE 1=1
                     AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                   ORDER BY 1)
SELECT objects,sql_id,LISTAGG(plan_hash_value,' ' ON OVERFLOW TRUNCATE) WITHIN GROUP (order by plan_hash_value) plan_hash_values
  FROM objects
 GROUP BY objects,sql_id
 ORDER BY objects,sql_id
/
prompt
prompt object_stats for table and indexes used in the query
prompt if you are using a covering index on a partitioned table, partition stats will not show
WITH objects AS ( SELECT dhsp.object_type
                        ,dhsp.object_owner
                        ,dhsp.object_name
                        ,dhsp.sql_id
                    FROM dba_hist_sql_plan dhsp
                   WHERE 1=1
                     AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                  UNION
                  SELECT gvp.object_type
                        ,gvp.object_owner
                        ,gvp.object_name
                        ,gvp.sql_id
                    FROM gv$sql_plan gvp
                   WHERE 1=1
                     AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                   ORDER BY 1)
SELECT objects.object_type
      ,NVL(dts.owner,dis.owner) owner
      ,NVL(dts.table_name,dis.table_name) table_name
      ,dis.index_name
      ,NVL(dts.partition_name,dis.partition_name) partition_name
      ,NVL(dts.subpartition_name,dis.subpartition_name) subpartition_name
      ,NVL(dts.global_stats,dis.global_stats) global_stats
      ,NVL(dts.num_rows,dis.num_rows) num_rows
      ,NVL(dts.blocks,dis.leaf_blocks) blocks
      ,NVL(dts.last_analyzed,dis.last_analyzed) last_analyzed
      ,NVL(dts.stale_stats,dis.stale_stats) stale_stats
      ,NVL(dts.stattype_locked,dis.stattype_locked) locked
      ,dts.avg_row_len
      ,dis.clustering_factor
      -- DECODE to prevent divide by 0 error
      ,TO_CHAR(ROUND((NVL(dts.sample_size,dis.sample_size)/NVL(DECODE(dts.num_rows,0,1,dts.num_rows),DECODE(dis.num_rows,0,1,dis.num_rows)))*100,2))||'%' sample
 FROM objects LEFT OUTER JOIN dba_tab_statistics dts ON (dts.owner = objects.object_owner AND dts.table_name=objects.object_name)
              LEFT OUTER JOIN dba_ind_statistics dis ON (dis.owner = objects.object_owner AND dis.index_name=objects.object_name)
WHERE objects.sql_id = '&&sql_id'
ORDER BY dts.owner,dts.table_name,dts.partition_name NULLS FIRST,dts.subpartition_name NULLS FIRST,dis.index_name
/
col index_owner for a30
col table_owner for a30
col is_used_in_plan for a16
prompt
prompt now show every index definition on the relevant tables so we can see if there could be an alternative
prompt you will need to look at the predicates / FILTER conditions in the execution plan to determine
prompt if other indexes could be suitable for the query
-- get all tables and indexes from the plan
-- pull all table names associated with those objects
-- and show indexes related to the table names
WITH feed    AS ( SELECT dhsp.object_type
                        ,dhsp.object_owner
                        ,dhsp.object_name
                        ,dhsp.sql_id
                    FROM dba_hist_sql_plan dhsp
                   WHERE 1=1
                     AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                  UNION
                  SELECT gvp.object_type
                        ,gvp.object_owner
                        ,gvp.object_name
                        ,gvp.sql_id
                    FROM gv$sql_plan gvp
                   WHERE 1=1
                     AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                   ORDER BY 1),
       -- now we need to add all of the tables for the indexes mentioned above in case of covering index
       objects AS ( SELECT ind.table_owner,
                           ind.table_name,
                           ind.index_name
                      FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) )
                   )
SELECT dic.table_owner
      ,dic.table_name
      ,dic.index_owner
      ,dic.index_name
      ,dic.column_name
      ,dic.column_position
      ,dic.descend
      ,(select 'YES' from feed  WHERE dic.index_name IN (SELECT feed.object_name FROM feed) AND rownum = 1) is_used_in_plan
  FROM dba_ind_columns dic JOIN objects ob ON (dic.table_owner=ob.table_owner AND (dic.table_name = ob.table_name OR dic.index_name = ob.index_name))
 GROUP BY dic.table_owner,dic.table_name,dic.index_owner,dic.index_name,dic.column_name,dic.column_position,dic.descend
 ORDER BY dic.table_owner,dic.table_name,is_used_in_plan desc NULLS LAST,dic.index_owner,dic.index_name,dic.column_position
/
col column_name   for a30
col data_type     for a10 truncate
col nullable      for a4
col histogram     for a30
prompt and lets have a look at those columns, just in the table (global stats if partitioned)
WITH feed    AS ( SELECT dhsp.object_type
                        ,dhsp.object_owner
                        ,dhsp.object_name
                        ,dhsp.sql_id
                    FROM dba_hist_sql_plan dhsp
                   WHERE 1=1
                     AND ( dhsp.object_type like '%TABLE%' OR dhsp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                  UNION
                  SELECT gvp.object_type
                        ,gvp.object_owner
                        ,gvp.object_name
                        ,gvp.sql_id
                    FROM gv$sql_plan gvp
                   WHERE 1=1
                     AND ( gvp.object_type like '%TABLE%' OR gvp.object_type like '%INDEX%' )
                     AND sql_id = '&&sql_id'
                   ORDER BY 1),
       -- now we need to add all of the tables for the indexes mentioned above in case of covering index
       objects AS ( SELECT ind.table_owner,
                           ind.table_name,
                           ind.index_name
                      FROM dba_indexes ind JOIN feed obj ON (obj.object_owner=ind.table_owner AND (obj.object_name = ind.table_name OR obj.object_name = ind.index_name) )
                   )
SELECT dtc.owner
      ,dtc.table_name
      ,dtc.column_name
      ,dtc.data_type
      ,dtc.nullable
      ,dtc.density
      ,dtc.num_distinct
      ,dtc.num_nulls
      ,dtc.histogram
      ,dtc.num_buckets
  FROM dba_tab_columns dtc JOIN objects ON (dtc.owner=objects.table_owner AND dtc.table_name = objects.table_name)
 GROUP BY dtc.owner ,dtc.table_name ,dtc.column_name ,dtc.data_type ,dtc.nullable ,dtc.density ,dtc.num_distinct ,dtc.num_nulls ,dtc.histogram,dtc.num_buckets,dtc.column_id
 ORDER BY dtc.owner,dtc.table_name,dtc.column_id
/
-- it would be handy to extract predicates from the filter to concentrate on the ones I care about
spool off
break on xxx_xxx

-- perf_stathist.sql n chandler v1.0 2025-11-11 show history of stats for a table
--                              v2.0 2026-06-28 also view statistic operation history so you can see how long each bit took
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SS';
define table_name
define owner
undefine table_name
undefine owner
col owner             for a30
col table_name        for a30
col partition_name    for a30
col subpartition_name for a30
prompt Locate table and owner
SELECT owner,table_name FROM dba_tables WHERE UPPER(table_name) LIKE UPPER('%&&table_name%')
 ORDER BY owner,table_name
/
prompt stats history
undefine table_name
prompt Enter Owner and table_name. Wildcards accepted.
prompt &&owner
prompt &&table_name
spool perf_stathist.&&owner..&&table_name..out append
with jpl_tab_history (
        owner, table_name, partition_name, subpartition_name,
        num_rows, blocks, avg_row_len, sample_size, last_analyzed
) as (
  select u.name, o.name, null, null, h.rowcnt, h.blkcnt, h.avgrln,
         h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
    and  h.savtime <= systimestamp
  union all
  -- partitions
  select u.name, o.name, o.subname, null, h.rowcnt, h.blkcnt,
         h.avgrln, h.samplesize, h.analyzetime
  from   sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
  where  h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
    and  h.savtime <= systimestamp
  union all
  -- sub partitions
  select u.name, osp.name, ocp.subname, osp.subname, h.rowcnt,
         h.blkcnt, h.avgrln, h.samplesize, h.analyzetime
  from  sys.user$ u,  sys.obj$ osp, sys.obj$ ocp,  sys.tabsubpart$ tsp,
        sys.wri$_optstat_tab_history h
  where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj# and
        tsp.pobj# = ocp.obj# and osp.owner# = u.user#
    and h.savtime <= systimestamp
)
SELECT owner
      ,table_name
      ,partition_name
      ,subpartition_name
      ,blocks
      ,num_rows
      ,avg_row_len
      ,sample_size
      ,last_analyzed
  FROM jpl_tab_history
 WHERE 1=1
   AND owner      LIKE NVL('%&&owner%','%')
   AND table_name LIKE NVL('%&&table_name%','%')
ORDER BY owner,table_name,partition_name NULLS FIRST,subpartition_name NULLS FIRST,last_analyzed;
col notes        for a10 trunc
col target       for a80
col start_time   for a20
col end_time     for a20
col operation    for a30
col tgt          for a80
col duration     for a23 truncate
col status       for a12
col target_type  for a15
col job_name     for a22
prompt Show statistics gathering operations (the last 20 operations)
prompt The automatic jobs show with a target of "AUTO"
SELECT *
  FROM dba_optstat_operations
 ORDER BY start_time desc
FETCH FIRST 20 ROWS ONLY
/
define opid
undefine opid
PROMPT Enter the operation id for details of that operation
SELECT opid
      ,target
      ,target_objn
      ,target_type
      ,target_size
      ,start_time
      ,end_time
      ,end_time-start_time duration
      ,status
      ,job_name
      ,priority
     -- ,notes
  FROM dba_optstat_operation_tasks
 WHERE opid = &&op_id
 ORDER BY start_time
/
prompt Priority grouping for targets, ignoring SYS, for end-to-end duration for that operation (tab/idx/part/etc)
SELECT MIN(target) tgt
      ,MIN(start_time) start_time
      ,MAX(end_time) end_time
      ,max(end_time)-min(start_time) duration
      ,priority
  FROM dba_optstat_operation_tasks
 WHERE opid = &&op_id
   AND target not like '"SYS".%'
 GROUP BY priority
 ORDER BY min(target)
/
prompt do we have any synopsis for incremental partitioning?
col obj for a40 truncate
col spare2 for a40 truncate
select do.owner||','||do.object_name||'.'||do.subobject_name obj,do.object_type,ANALYZETIME,SPARE2 from SYS.WRI$_OPTSTAT_SYNOPSIS_HEAD$ wosh
 join dba_objects do on (wosh.group#/2)=do.object_id
 join dba_tab_columns dtc on (do.owner=dtc.owner and do.objecT_name=dtc.table_name and wosh.intcol#=dtc.column_id)
where do.owner = '&&schema_name'
order by 1,2
/
spool off

-- perf_sqlplan.sql v1.0 n chandler.
--                  v1.1 n chandler. 2025-11-11 extrat XML in a readable format for manual side of plans
--
-- show execution plans
set lines 250 pages 200
col sql_id            for a15
col profile           for a7
col patch             for a5
col baseline          for a8
col is_bind_sensitive for a17
col is_bind_aware     for a13
col is_shareable      for a12
col inst_id           for 9999
define sql_id
undefine sql_id
spool perf_sqlplan.&&sql_id..out append
prompt Plans and speed in GV$SQL for &&sql_id
SELECT inst_id
      ,sql_id
      ,child_number
      ,plan_hash_value
      ,DECODE(sql_profile,null,'N','Y') profile
      ,DECODE(sql_patch,null,'N','Y') patch
      ,DECODE(sql_plan_baseline,null,'N','Y') baseline
      ,is_bind_sensitive
      ,is_bind_aware
      ,is_shareable
      ,executions
      ,rows_processed
      ,ROUND(rows_processed/decode(executions,0,1,executions)) rows_per_exec
      ,ROUND(elapsed_time/1000,1) elapsed_time_ms
      ,ROUND(elapsed_time/decode(executions,0,1,executions)/1000,1) elapsed_per_exec_ms
  FROM  gv$sql
 WHERE sql_id = '&&sql_id'
 ORDER BY inst_id,sql_id,child_number,plan_hash_value;
prompt get all current plans in GV$SQL for sql_id &&sql_id
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&&sql_id',null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));
prompt get all plans in AWR for sql_id &&sql_id
pause [p] Press enter to continue or CTRL+C to stop
-- display_awr is deprecated
--SELECT * FROM TABLE(dbms_xplan.display_awr('&&sql_id',null,null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_WORKLOAD_REPOSITORY('&&sql_id',null,'TYPICAL +ALIAS +NOTE +PEEKED_BINDS +OUTLINE +HINT_REPORT +ADAPTIVE'));
prompt Sometimes the AWR SQL Plan does not extract.
prompt This is some manual code to extract from DBA_HIST_SQL_PLAN
prompt Fruther SQL will extract XML info such as outline and notes from the other_xml CLOB.
pause [p] Press enter to continue or CTRL+C to stop
col operation            for a30
col object               for a40
col partition_start_stop for a20
col mb                   for 999,999,999.00
col temp_space_mb        for 999,999,999.00
select sql_id
      ,plan_hash_value
      ,position
      ,lpad(' ',depth)||operation operation
      ,object_owner||'.'||object_name||' ('||object_type||')' AS object
      ,parent_id
      ,cost
      ,bytes/1024/1024 mb
      ,cpu_cost
      ,io_cost
      ,temp_space/1024/1024 temp_space_mb
      ,partition_id||' ('||partition_start||'-'||partition_stop||')' partition_start_stop
  FROM dba_hist_sql_plan
 WHERE sql_id = '&&sql_id'
 ORDER BY sql_id,plan_hash_value,parent_id NULLS FIRST, position
/
col bindval for a50  wrap
col hint    for a150 wrap
prompt Info
SELECT sql_id,plan_hash_value,tag, typ, val
FROM   dba_hist_sql_plan dhsp, XMLTABLE(
         '/other_xml/*'
         PASSING XMLTYPE( dhsp.other_xml )
         COLUMNS
           tag   VARCHAR2(20) PATH 'name()',
           typ   VARCHAR2(20) PATH '@type',
           val    VARCHAR2(20) PATH 'text()'
       )
WHERE sql_id = '&&sql_id'
   AND other_xml is not null
/
prompt binds
SELECT sql_id,plan_hash_value,bind,bindnam,bindpos,binddty,bindcsi,bindfrm,bindmxl,bindval
FROM   dba_hist_sql_plan dhsp,
       XMLTABLE( '/other_xml/peeked_binds/*'
         PASSING XMLTYPE( dhsp.other_xml )
         COLUMNS
           bind      VARCHAR2(20) PATH 'name()',
           bindnam   VARCHAR2(20) PATH '@nam',
           bindpos   VARCHAR2(20) PATH '@pos',
           binddty   VARCHAR2(20) PATH '@dty',
           bindcsi   VARCHAR2(20) PATH '@csi',
           bindfrm   VARCHAR2(20) PATH '@frm',
           bindmxl   VARCHAR2(20) PATH '@mxl',
           bindval   VARCHAR2(300) PATH 'text()' )
WHERE sql_id = '&&sql_id'
   AND other_xml is not null
ORDER BY 1,2,3,4
/
prompt outline hints to recreate plan
SELECT sql_id,plan_hash_value,decode(outline,'hint','outline_hint',outline) outline,hint
FROM   dba_hist_sql_plan dhsp,
       XMLTABLE( '/other_xml/outline_data/*'
         PASSING XMLTYPE( dhsp.other_xml )
         COLUMNS
           outline    VARCHAR2(20) PATH 'name()',
           hint       VARCHAR2(300) PATH 'text()' )
WHERE sql_id = '&&sql_id'
   AND other_xml is not null
ORDER BY 1,2,3
/
prompt hint_usage
SELECT sql_id,plan_hash_value,tab,origin,status,f,n,t,x,h
FROM   dba_hist_sql_plan dhsp,
       XMLTABLE( '/other_xml/hint_usage/q/*'
         PASSING XMLTYPE( dhsp.other_xml )
         COLUMNS
           tab           VARCHAR2(30) PATH 'text()',
           origin        VARCHAR2(10) PATH './h/@o',
           status        VARCHAR2(10) PATH './h/@st',
           f             VARCHAR2(20) PATH './f/text()',
           n             VARCHAR2(20) PATH './n/text()',
           t             VARCHAR2(20) PATH './t/text()',
           x             VARCHAR2(20) PATH './x/text()',
           h             VARCHAR2(20) PATH './h/x/text()'
                )
WHERE sql_id = '&&sql_id'
   AND other_xml is not null
/
spool off

-- perf_statsprefs.sql v1.0 2025-12-30 show global and table prefs which are set
--                     v1.1 2026-05-07 tweak output formatting
column sp1 new_value sp_name
SELECT SYS_CONTEXT('userenv','CDB_NAME')||'-'||DECODE(SYS_CONTEXT('userenv','CON_NAME'),'CDB$ROOT','CDB',SYS_CONTEXT('userenv','CON_NAME')) AS sp1 FROM dual;
spool perf_statsprefs.&&sp_name..out append
column today format a20
column cdb_name format a20
column pdb_name format a20
column owner   format a30
column table_name format a30
column preference_name format a50
SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS') today,SYS_CONTEXT('userenv','CDB_NAME') AS cdb_name,SYS_CONTEXT('userenv','CON_NAME') AS pdb_name FROM dual;
column preference_value                format a120
column ANDV_ALGO_INTERNAL_OBSERVE      format a80 wrap heading "ANDV_ALGO_INTERNAL_OBSERVE    - FALSE"
column APPROXIMATE_NDV_ALGORITHM       format a80 wrap heading "APPROXIMATE_NDV_ALGORITHM     - REPEAT OR HYPERLOGLOG"
column APPROXIMATE_NDV                 format a80 wrap heading "APPROXIMATE_NDV               - TRUE"
column AUTO_STAT_EXTENSIONS            format a80 wrap heading "AUTO_STAT_EXTENSIONS          - OFF"
column AUTOSTATS_TARGET                format a80 wrap heading "AUTOSTATS_TARGET              - AUTO"
column AUTO_TASK_INTERVAL              format a80 wrap heading "AUTO_TASK_INTERVAL            - 900"
column AUTO_TASK_MAX_RUN_TIME          format a80 wrap heading "AUTO_TASK_MAX_RUN_TIME        - 3600"
column AUTO_TASK_STATUS                format a80 wrap heading "AUTO_TASK_STATUS              - OFF"
column CASCADE                         format a80 wrap heading "CASCADE                       - DBMS_STATS.AUTO_CASCADE"
column CONCURRENT                      format a80 wrap heading "CONCURRENT                    - FALSE"
column COORDINATOR_TRIGGER_SHARD       format a80 wrap heading "COORDINATOR_TRIGGER_SHARD     - FALSE"
column DEBUG                           format a80 wrap heading "DEBUG                         - 0"
column DEGREE                          format a80 wrap heading "DEGREE                        - NULL"
column ENABLE_HYBRID_HISTOGRAMS        format a80 wrap heading "ENABLE_HYBRID_HISTOGRAMS      - 3"
column ENABLE_TOP_FREQ_HISTOGRAMS      format a80 wrap heading "ENABLE_TOP_FREQ_HISTOGRAMS    - 3"
column ESTIMATE_PERCENT                format a80 wrap heading "ESTIMATE_PERCENT              - DBMS_STATS.AUTO_SAMPLE_SIZE"
column GATHER_AUTO                     format a80 wrap heading "GATHER_AUTO                   - AFTER_LOAD"
column GATHER_SCAN_RATE                format a80 wrap heading "GATHER_SCAN_RATE              - HADOOP_ONLY"
column GLOBAL_TEMP_TABLE_STATS         format a80 wrap heading "GLOBAL_TEMP_TABLE_STATS       - SESSION"
column GRANULARITY                     format a80 wrap heading "GRANULARITY                   - AUTO"
column INCREMENTAL_INTERNAL_CONTROL    format a80 wrap heading "INCREMENTAL_INTERNAL_CONTROL  - TRUE"
column INCREMENTAL                     format a80 wrap heading "INCREMENTAL                   - FALSE"
column INCREMENTAL_LEVEL               format a80 wrap heading "INCREMENTAL_LEVEL             - PARTITION"
column INCREMENTAL_STALENESS           format a80 wrap heading "INCREMENTAL_STALENESS         - ALLOW_MIXED_FORMAT"
column JOB_OVERHEAD_PERC               format a80 wrap heading "JOB_OVERHEAD_PERC             - 1"
column JOB_OVERHEAD                    format a80 wrap heading "JOB_OVERHEAD                  - .5"
column MAINTAIN_STATISTICS_STATUS      format a80 wrap heading "MAINTAIN_STATISTICS_STATUS    - FALSE"
column METHOD_OPT                      format a80 wrap heading "METHOD_OPT                    - FOR ALL COLUMNS SIZE AUTO"
column NO_INVALIDATE                   format a80 wrap heading "NO_INVALIDATE                 - DBMS_STATS.AUTO_INVALIDATE"
column OPTIONS                         format a80 wrap heading "OPTIONS                       - GATHER"
column PREFERENCE_OVERRIDES_PARAMETER  format a80 wrap heading "PREFERENCE_OVERRIDES_PARAMETER- FALSE"
column PUBLISH                         format a80 wrap heading "PUBLISH                       - TRUE"
column ROOT_TRIGGER_PDB                format a80 wrap heading "ROOT_TRIGGER_PDB              - FALSE"
column SCAN_RATE                       format a80 wrap heading "SCAN_RATE                     - 0"
column STALE_PERCENT                   format a80 wrap heading "STALE_PERCENT                 - 10"
column STAT_CATEGORY                   format a80 wrap heading "STAT_CATEGORY                 - OBJECT_STATS, REALTIME_STATS"
column SYS_FLAGS                       format a80 wrap heading "SYS_FLAGS                     - 1"
column TABLE_CACHED_BLOCKS             format a80 wrap heading "TABLE_CACHED_BLOCKS           - 1"
column TRACE                           format a80 wrap heading "TRACE                         - 0"
column WAIT_TIME_TO_UPDATE_STATS       format a80 wrap heading "WAIT_TIME_TO_UPDATE_STATS     - 0.0033 FROM 19.22. Was 1"
prompt STATS:
prompt Global Parameters (12.2 - inc hidden - so expect errors)
SELECT dbms_stats.get_param('ANDV_ALGO_INTERNAL_OBSERVE') as ANDV_ALGO_INTERNAL_OBSERVE  FROM dual;
SELECT dbms_stats.get_param('APPROXIMATE_NDV_ALGORITHM') as APPROXIMATE_NDV_ALGORITHM  FROM dual;
SELECT dbms_stats.get_param('APPROXIMATE_NDV') as APPROXIMATE_NDV  FROM dual;
SELECT dbms_stats.get_param('AUTO_STAT_EXTENSIONS') as AUTO_STAT_EXTENSIONS  FROM dual;
SELECT dbms_stats.get_param('AUTOSTATS_TARGET') as AUTOSTATS_TARGET  FROM dual;
SELECT dbms_stats.get_param('AUTO_TASK_INTERVAL') as AUTO_TASK_INTERVAL  FROM dual;
SELECT dbms_stats.get_param('AUTO_TASK_MAX_RUN_TIME') as AUTO_TASK_MAX_RUN_TIME  FROM dual;
SELECT dbms_stats.get_param('AUTO_TASK_STATUS') as AUTO_TASK_STATUS  FROM dual;
SELECT dbms_stats.get_param('CASCADE') as CASCADE  FROM dual;
SELECT dbms_stats.get_param('CONCURRENT') as CONCURRENT  FROM dual;
SELECT dbms_stats.get_param('COORDINATOR_TRIGGER_SHARD') as COORDINATOR_TRIGGER_SHARD  FROM dual;
SELECT dbms_stats.get_param('DEBUG') as DEBUG  FROM dual;
SELECT dbms_stats.get_param('DEGREE') as DEGREE  FROM dual;
SELECT dbms_stats.get_param('ENABLE_HYBRID_HISTOGRAMS') as ENABLE_HYBRID_HISTOGRAMS  FROM dual;
SELECT dbms_stats.get_param('ENABLE_TOP_FREQ_HISTOGRAMS') as ENABLE_TOP_FREQ_HISTOGRAMS  FROM dual;
SELECT dbms_stats.get_param('ESTIMATE_PERCENT') as ESTIMATE_PERCENT  FROM dual;
SELECT dbms_stats.get_param('GATHER_AUTO') as GATHER_AUTO  FROM dual;
SELECT dbms_stats.get_param('GATHER_SCAN_RATE') as GATHER_SCAN_RATE  FROM dual;
SELECT dbms_stats.get_param('GLOBAL_TEMP_TABLE_STATS') as GLOBAL_TEMP_TABLE_STATS  FROM dual;
SELECT dbms_stats.get_param('GRANULARITY') as GRANULARITY  FROM dual;
SELECT dbms_stats.get_param('INCREMENTAL_INTERNAL_CONTROL') as INCREMENTAL_INTERNAL_CONTROL  FROM dual;
SELECT dbms_stats.get_param('INCREMENTAL') as INCREMENTAL  FROM dual;
SELECT dbms_stats.get_param('INCREMENTAL_LEVEL') as INCREMENTAL_LEVEL  FROM dual;
SELECT dbms_stats.get_param('INCREMENTAL_STALENESS') as INCREMENTAL_STALENESS  FROM dual;
SELECT dbms_stats.get_param('JOB_OVERHEAD_PERC') as JOB_OVERHEAD_PERC  FROM dual;
SELECT dbms_stats.get_param('JOB_OVERHEAD') as JOB_OVERHEAD  FROM dual;
SELECT dbms_stats.get_param('MAINTAIN_STATISTICS_STATUS') as MAINTAIN_STATISTICS_STATUS  FROM dual;
SELECT dbms_stats.get_param('METHOD_OPT') as METHOD_OPT  FROM dual;
SELECT dbms_stats.get_param('NO_INVALIDATE') as NO_INVALIDATE  FROM dual;
SELECT dbms_stats.get_param('OPTIONS') as OPTIONS  FROM dual;
SELECT dbms_stats.get_param('PREFERENCE_OVERRIDES_PARAMETER') as PREFERENCE_OVERRIDES_PARAMETER  FROM dual;
SELECT dbms_stats.get_param('PUBLISH') as PUBLISH  FROM dual;
SELECT dbms_stats.get_param('ROOT_TRIGGER_PDB') as ROOT_TRIGGER_PDB  FROM dual;
SELECT dbms_stats.get_param('SCAN_RATE') as SCAN_RATE  FROM dual;
SELECT dbms_stats.get_param('STALE_PERCENT') as STALE_PERCENT  FROM dual;
SELECT dbms_stats.get_param('STAT_CATEGORY') as STAT_CATEGORY  FROM dual;
SELECT dbms_stats.get_param('SYS_FLAGS') as SYS_FLAGS  FROM dual;
SELECT dbms_stats.get_param('TABLE_CACHED_BLOCKS') as TABLE_CACHED_BLOCKS  FROM dual;
SELECT dbms_stats.get_param('TRACE') as TRACE  FROM dual;
SELECT dbms_stats.get_param('WAIT_TIME_TO_UPDATE_STATS') as WAIT_TIME_TO_UPDATE_STATS  FROM dual;
prompt STATS: dba_tab_stat_prefs
SELECT * FROM DBA_TAB_STAT_PREFS
WHERE owner in (select username FROM dba_users where oracle_maintained='N')
ORDER BY owner,table_name,preference_name;
spool off

-- perf_tabfrag.sql v1.0 n chandler 2024-01-15 use dbms_space to check how fragmented a table is - where the data is and how much
--
define schema
define table_name
undefine schema
undefine table_name
set verify off
set serveroutput on size unlimited
promtp Enter the schema owner and the table_name for the table to check
prompt &&schema
prompt &&table_name
spool perf_tabfrag.&&schema..&&table_name..out append
col systimestamp for a40
col pdb          for a12
select systimestamp,sys_context('userenv','con_name') PDB from dual;
prompt DBA_SEGMENTS
select owner,segment_name,tablespace_name,blocks from dba_segments where owner = '&&schema' and segment_name = '&&table_name';
prompt DBA_EXTENTS
select owner,segment_name,tablespace_name,sum(blocks),count(*) from dba_extents  where owner = '&&schema' and segment_name = '&&table_name' group by owner,segment_name,tablespace_name;
prompt DBMS_SPACE
set serveroutput on size unlimited
declare
 tvpd_unformatted_blocks number;
 tvpd_unformatted_bytes number;
 tvpd_fs1_blocks number;
 tvpd_fs1_bytes number;
 tvpd_fs2_blocks number;
 tvpd_fs2_bytes number;
 tvpd_fs3_blocks number;
 tvpd_fs3_bytes number;
 tvpd_fs4_blocks number;
 tvpd_fs4_bytes number;
 tvpd_full_blocks number;
 tvpd_full_bytes number;
 l_part varchar2(3);
begin
 select partitioned into l_part from dba_tables where owner = '&&schema' and table_name = '&&table_name';
 if l_part = 'NO'
 then
  sys.dbms_space.space_usage('&&schema','&&table_name','TABLE',
  tvpd_unformatted_blocks,
  tvpd_unformatted_bytes,
  tvpd_fs1_blocks,
  tvpd_fs1_bytes,
  tvpd_fs2_blocks,
  tvpd_fs2_bytes,
  tvpd_fs3_blocks,
  tvpd_fs3_bytes,
  tvpd_fs4_blocks,
  tvpd_fs4_bytes,
  tvpd_full_blocks,
  tvpd_full_bytes);
  dbms_output.put_line('&&schema..&&table_name: unformatted='||tvpd_unformatted_blocks||', 75-99% free='||tvpd_fs4_blocks||' ,50-75% free='||tvpd_fs3_blocks||' ,25-50% free='||tvpd_fs2_blocks||', 00-25% free='||tvpd_fs1_blocks||', full='||tvpd_full_blocks);
  elsif l_part = 'YES' then
   dbms_output.put_line('yes');
   for ii in (select partition_name from dba_tab_partitions where table_owner = '&&schema' and table_name = '&&table_name' order by partition_name)
   loop
     sys.dbms_space.space_usage('&&schema','&&table_name','TABLE PARTITION',
     tvpd_unformatted_blocks,
     tvpd_unformatted_bytes,
     tvpd_fs1_blocks,
     tvpd_fs1_bytes,
     tvpd_fs2_blocks,
     tvpd_fs2_bytes,
     tvpd_fs3_blocks,
     tvpd_fs3_bytes,
     tvpd_fs4_blocks,
     tvpd_fs4_bytes,
     tvpd_full_blocks,
     tvpd_full_bytes,
     ii.partition_name);
     dbms_output.put_line('&&schema..&&table_name..'||ii.partition_name||' : unformatted='||tvpd_unformatted_blocks||', 75-99% free='||tvpd_fs4_blocks||' ,50-75% free='||tvpd_fs3_blocks||' ,25-50% free='||tvpd_fs2_blocks||', 00-25% free='||tvpd_fs1_blocks||', full='||tvpd_full_blocks);
   end loop;
 else
  dbms_output.put_line('Partitioned: '||l_part);
 end if;
end;
/

-- perf_gatherstale.sql n chandler 2023-07-11 gathers stale objects for a schema
--                                            WARNING - this changes stats!
set serveroutput on size unlimited
prompt WARNING! this changes stats
pause [p]
col owner             for a30
col table_name        for a30
col partition_name    for a30
col subpartition_name for a30
col stale_stats       for a12
prompt All databases non-oracle users with tables
SELECT OWNER,COUNT(*)
  FROM dba_tab_statistics
 WHERE owner in ( SELECT username FROM dba_users WHERE oracle_maintained = 'N' )
 GROUP BY owner
 ORDER BY owner;
define schema
undefine schema
prompt Enter Schema &&schema
spool perf_gatherstale.&&schema..out append
select name,db_unique_name,sysdate from v$database;
prompt list potential objects
SELECT owner,table_name,partition_name,subpartition_name,num_rows,last_analyzed,NVL(stale_stats,'NULL') stale_stats
  FROM dba_tab_statistics
 WHERE owner = '&&schema'
   AND NVL(stale_stats,'YES')='YES'
ORDER BY stale_stats NULLS LAST,owner,table_name,partition_name,subpartition_name
/
DECLARE
 obj_list dbms_stats.ObjectTab;
BEGIN
 -- Performa a GATHER_AUTO, then output what was actually gathered. 
 -- NOTE: This is commented out for safety. Uncomment the next line to actually gather stale stats
 -- DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'&&schema',OBJLIST=>obj_list,OPTIONS=>'GATHER AUTO');
 --get list of objects where stats were gathered
 IF obj_list.first IS NOT NULL
 THEN
   FOR ii in obj_list.FIRST..obj_list.LAST
   LOOP
   dbms_output.put_line(obj_list(ii).ownname||'.'||obj_list(ii).objname||' '||obj_list(ii).objtype||' '||obj_list(ii).partname);
   END LOOP;
 ELSE
  dbms_output.put_line('nothing to do');
 END IF;
END;
/
prompt list objects for &&schema stale and OK
SELECT owner,table_name,partition_name,subpartition_name,num_rows,last_analyzed,NVL(stale_stats,'NULL') stale_stats
  FROM dba_tab_statistics
 WHERE owner = '&&schema'
ORDER BY stale_stats NULLS LAST,owner,table_name,partition_name,subpartition_name
/
prompt Anything till stale for &&schema
SELECT owner,table_name,count(*) stale_object_count
  FROM dba_tab_statistics
 WHERE owner = '&&schema'
   AND NVL(stale_stats,'YES') = 'YES'
 GROUP BY owner,table_name
 ORDER BY owner,table_name,table_name;
spool off

-- perf_sys_stats.sql v1.0 n chandler 2020.03.03 (based on a script by Franck Pachot, based on research by Chris Antognini)
-- show the current system statistics and the relationship between single bytew reads (cost) and multibytes reads in terms of single byte reads
-- which is a key relationship for the optimizer
-- bug - I'm pretty sure this is an old version but it's the only one I can find now
--       and it needs debugging as there's a couple of minor mistakes in there
-- ALSO, there is defensive coding in System Stats, for example, if your SREADTIM > MREADTIM, or your MBRC is 0, Oracle will apply some default calcs instead
--       I DO NOT TAKE THE DEFENSIVE CODING INTO ACCOUNT!!! Understand where your stats are impossible.
set linesize 200
column pname format a30
column sname format a20
column pval2 format a20
column CALCULATED format 999999999.9999
column narrative   format a150
column reset_systats_command format a80
select sname,pname,pval1 from sys.aux_stats$ order by 1,2;
spool perf_sysstat.out append
select name from v$database;
select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
reference sga on (
--select name,value from v$sga
select 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')
) dimension by (name) measures(value)
reference parameter on (
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
union all
SELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count'
)
dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(120)) as formula) rules(
calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
calculated['_single block Cost per block']=1,
formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)' else '= not sure so used 8' end,
formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'*'||calculated['MBRC']||'/'||pval1['IOTFRSPEED']||')',
formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size        / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'/'||pval1['IOTFRSPEED']||')',
formula['_multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM = 1/'||calculated['MBRC']||' * '||calculated['MREADTIM']||'/'||calculated['SREADTIM'],
calculated['_maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),
formula['_maximum mbrc']='= buffer cache blocks/sessions (small cache limiter) = ' || sga.value['Database Buffers']/parameter.value['db_block_size']||'/'||parameter.value['sessions'],
formula['_single block Cost per block']='relative to the multi block cost per block. Always 1!',
formula['CPUSPEED']='overrides CPUSPEEDNW when set',
formula['CPUSPEEDNW']='CPU speed Ghz - non workload',
formula['IOSEEKTIM']='IO seek time in ms',
formula['IOTFRSPEED']='IO transfer speed in KB/s',
formula['MAXTHR']='Maximum IO system throughput',
formula['SLAVETHR']='average parallel slave IO throughput'
) order by 1;
prompt Capture current System Statistics
select sname,pname,pval1,'exec dbms_stats.set_system_stats('''||pname||''','||decode(pval1,null,'NULL',pval1)||');' as reset_systats_command
  from sys.aux_stats$
 where sname = 'SYSSTATS_MAIN'
 order by 1,2;
prompt Simplified check for an 8k block size
with stats as( select pname,pval1,calculated from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
reference sga on (
--select name,value from v$sga
select 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')
) dimension by (name) measures(value)
reference parameter on (
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
union all
SELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count'
)
dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated) rules(
calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
calculated['_single block Cost per block']=1
)
)
select pname
       ,calculated
       ,case when calculated < .2708 then 'System Stats have been set for execution plan costing '||round(abs((1-(calculated/0.2708)))*100)||'% lower for multiblock reads (favours SCANS).'
             when calculated > .2708 then 'System Stats have been set for execution plan costing '||round(abs((1-(calculated/0.2708)))*100)||'% higher for multiblock reads (favours INDEXES).'
             else                         'Stats calculations shows this to be the DEFAULT'
        end as Narrative
       from stats where pname = '_multi block Cost per block'
/
--examples
--PNAME                               CALCULATED NARRATIVE
--_multi block Cost per block              .2708 Stats calculations shows this to be the DEFAULT
--PNAME                               CALCULATED NARRATIVE
--_multi block Cost per block              .0119 System Stats have been set for execution plan costing 96% lower for multiblock reads (favours SCANS).
prompt NOTE: 0.2708 is for an 8k block size! 16k block size is slightly different.
prompt Running a "gather_system_stats(exadata)" will generally result in a very low multi-block cost per block (e.g. 0.0119) meaning indexes
prompt need to be extremely accurate and selective to be considered by the optimizer instead of full table scans
prompt IF IT IS NOT THE DEFAULT (above), it should be heavily documented WHY and with proofs of why the different value is better than the default
prompt Warning: changing it can affect a lot of SQL Optimizations. Proceed with caution.
spool off