support/oracle
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
oracle/vg/sqlstats.sql
root 93039b8489 2026-03-12 20:23:15
2026-03-12 21:23:47 +01:00

182 lines
10 KiB
SQL
Raw Permalink Blame History

@@header
/*
*
* Author : Vishal Gupta
* Purpose : Display SQLStats from SQLArea
* Version : 10.1.0.4 and above
* Parameters : 1 - SQL_ID
* 2 - PLAN_HASH_VALUE (Use % as wildcard)
* 3 - Hours (Default 72 hours)
*
*
* Revision History:
* ===================
* Date Author Description
* --------- ------------ -----------------------------------------
* 10-Feb-14 Vishal Gupta Changed default hours from 24 to 72 hours on a request from friend
Now force_matching_signature is taken from gv$sqlstats instead of gv$sqlarea.
* 04-Oct-13 Vishal Gupta Added hour as input, last_active_time in output
Sorted output by last_active_time
* 23-Apr-12 Vishal Gupta Added PLAN_HASH_VALUE as the input variable
* 20-Mar-12 Vishal Gupta Added PLAN_HASH_VALUE in output
* 08-Mar-12 Vishal Gupta Created
*
*/
/************************************
* INPUT PARAMETERS
************************************/
UNDEFINE sql_id
UNDEFINE plan_hash_value
UNDEFINE hours
DEFINE sql_id="&&1"
DEFINE plan_hash_value="&&2"
DEFINE hours="&&3"
COLUMN _plan_hash_value NEW_VALUE plan_hash_value NOPRINT
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
COLUMN _hours NEW_VALUE HOURS NOPRINT
set term off
SELECT DECODE('&&plan_hash_value','','%','&&plan_hash_value') "_plan_hash_value"
, DECODE('&&sql_id','%','%','') "_force_matching_signature"
, DECODE('&&hours','','72','&&hours') "_hours"
FROM DUAL;
set term on
/************************************
* CONFIGURATION PARAMETERS
************************************/
DEFINE COUNT_SMALL_FORMAT=9,999
DEFINE COUNT_SMALL_DIVIDER="1"
DEFINE COUNT_SMALL_HEADING="#"
--DEFINE COUNT_DIVIDER="1000"
--DEFINE COUNT_HEADING="#1000"
DEFINE COUNT_FORMAT=999,999,999
DEFINE COUNT_DIVIDER="1"
DEFINE COUNT_HEADING="#"
--DEFINE COUNT_DIVIDER="1000"
--DEFINE COUNT_HEADING="#1000"
DEFINE BYTES_FORMAT="999,999"
--DEFINE BYTES_DIVIDER="1024"
--DEFINE BYTES_HEADING="KB"
--DEFINE BYTES_DIVIDER="1024/1024"
--DEFINE BYTES_HEADING="MB"
DEFINE BYTES_DIVIDER="1024/1024/1024"
DEFINE BYTES_HEADING="GB"
DEFINE TIME_FORMAT=9,999
DEFINE TIME_DIVIDER="60"
DEFINE TIME_HEADING="min"
/************************************
* MAIN Section
************************************/
DEFINE force_matching_signature=""
COLUMN _force_matching_signature NEW_VALUE force_matching_signature NOPRINT
set term off
set numf 99999999999999999999999
select to_char(sql.force_matching_signature) "_force_matching_signature"
from gv$sqlstats sql
where sql.sql_id = '&&sql_id'
and rownum <= 1;
set term on
PROMPT **************************************************************
PROMPT * SQL Executions Statistics
PROMPT * (Force Matching Signature = '&&force_matching_signature')
PROMPT *
PROMPT * Input Parameters
PROMPT * - SQL Id = '&&sql_id'
PROMPT * - Plan Hash Value = '&&plan_hash_value'
PROMPT * - Hours = '&&hours'
PROMPT **************************************************************
COLUMN first_load_time HEADING "First Load Time" FORMAT a20
COLUMN last_active_time HEADING "Last Active Time" FORMAT a20
COLUMN instance_number HEADING "I#" FORMAT 99
COLUMN SQL_ID HEADING "SQLId" FORMAT a13
COLUMN plan_hash_value HEADING "Plan|Hash|Value" FORMAT 999999999999
COLUMN invalidations HEADING "Invalid|Total|(&COUNT_HEADING)" FORMAT 9,999
COLUMN iowait HEADING "IO|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN apwait HEADING "Appl|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN ccwait HEADING "Conc|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN clwait HEADING "Cluster|Wait|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN plsexec_time HEADING "PLsexec|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN cpu_time HEADING "CPU|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN elapsed_time HEADING "Elapsed|Time|Total|(&TIME_HEADING)" FORMAT &TIME_FORMAT
COLUMN executions HEADING "Exec|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
COLUMN px_servers_executions HEADING "Par'l|Serv|Exec|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
COLUMN end_of_fetch_count HEADING "End Of|Fetch|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
COLUMN rows_processed HEADING "Rows|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
COLUMN sorts HEADING "Sorts|Total|(&COUNT_SMALL_HEADING)" FORMAT &COUNT_SMALL_FORMAT
COLUMN buffer_gets HEADING "Buffer|Gets|Total|(&COUNT_HEADING)" FORMAT &COUNT_FORMAT
COLUMN direct_writes HEADING "Direct|Write|Total|(&COUNT_HEADING)" FORMAT &TIME_FORMAT
COLUMN io_interconnect_bytes HEADING "IO|Inter|Connect|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
COLUMN physical_read_bytes HEADING "Phy|Read|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
COLUMN physical_write_bytes HEADING "Phy|Write|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
COLUMN optimized_physical_reads HEADING "Optimized|Phy|ReadReq|(&COUNT_HEADING)" FORMAT &TIME_FORMAT
COLUMN io_offload_elig_bytes HEADING "IO|Offload|Elig|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
COLUMN io_offload_return_bytes HEADING "IO|Offload|Return|Total|(&BYTES_HEADING)" FORMAT &BYTES_FORMAT
COLUMN sql_profile HEADING "sql_profile" FORMAT a29
COLUMN sql_plan_baseline HEADING "sql_plan_baseline" FORMAT a30
set lines 300
SELECT TO_CHAR(TO_DATE(sql.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') first_load_time
, TO_CHAR(ss.last_active_time,'DD-MON-YYYY HH24:MI:SS') last_active_time
, ss.inst_id instance_number
, ss.sql_id
, ss.plan_hash_value
, ROUND(ss.user_io_wait_time/1000000/&TIME_DIVIDER) iowait
, ROUND(ss.application_wait_time/1000000/&TIME_DIVIDER) apwait
, ROUND(ss.concurrency_wait_time/1000000/&TIME_DIVIDER) ccwait
, ROUND(ss.cluster_wait_time/1000000/&TIME_DIVIDER) clwait
, ROUND(ss.plsql_exec_time/1000000/&TIME_DIVIDER) plsexec_time
, ROUND(ss.cpu_time/1000000/&TIME_DIVIDER) cpu_time
, ROUND(ss.elapsed_time/1000000/&TIME_DIVIDER) elapsed_time
, ss.executions/&COUNT_SMALL_DIVIDER executions
, ss.px_servers_executions/&COUNT_SMALL_DIVIDER px_servers_executions
, ss.end_of_fetch_count/&COUNT_SMALL_DIVIDER end_of_fetch_count
-- , ss.invalidations/&COUNT_DIVIDER invalidations
&&_IF_ORA_10gR2_OR_HIGHER , ss.rows_processed/&COUNT_DIVIDER rows_processed
&&_IF_ORA_10gR2_OR_HIGHER , ss.sorts/&COUNT_SMALL_DIVIDER sorts
&&_IF_ORA_10gR2_OR_HIGHER , ss.buffer_gets/&COUNT_DIVIDER buffer_gets
&&_IF_ORA_10gR2_OR_HIGHER , ROUND(ss.direct_writes/&COUNT_DIVIDER) direct_writes
&&_IF_ORA_11107_OR_HIGHER , ss.io_interconnect_bytes/&BYTES_DIVIDER io_interconnect_bytes
&&_IF_ORA_11gR2_OR_HIGHER , ss.physical_read_bytes/&BYTES_DIVIDER physical_read_bytes
&&_IF_ORA_11gR2_OR_HIGHER , ss.physical_write_bytes/&BYTES_DIVIDER physical_write_bytes
--&&_IF_ORA_11gR2_OR_HIGHER , ss.optimized_physical_reads/&COUNT_DIVIDER optimized_physical_reads
&&_IF_ORA_11gR2_OR_HIGHER , ss.io_cell_offload_eligible_bytes/&BYTES_DIVIDER io_offload_elig_bytes
&&_IF_ORA_11gR2_OR_HIGHER , ss.io_cell_offload_returned_bytes/&BYTES_DIVIDER io_offload_return_bytes
&&_IF_ORA_10gR2_OR_HIGHER , sql.sql_profile
&&_IF_ORA_11gR1_OR_HIGHER , sql.sql_plan_baseline
from gv$sqlstats ss
, gv$sqlarea sql
where ss.inst_id = sql.inst_id (+)
AND ss.sql_id = sql.sql_id (+)
AND ss.plan_hash_value = sql.plan_hash_value (+)
AND ss.plan_hash_value LIKE '&&plan_hash_value'
AND ss.sql_id LIKE CASE ss.force_matching_signature
WHEN 0 THEN '&&sql_id'
ELSE '%'
END
AND ss.force_matching_signature = '&&force_matching_signature'
AND ss.last_active_time > sysdate - (&&hours/24)
order by ss.last_active_time
, ss.inst_id
;
@@footer
Reference in New Issue View Git Blame Copy Permalink