How to get SQL ID STAT information in Oracle Database 12c
Please use the following Query to get the information of SQL ID statistics in oracle database. It will provide you the information about SQL ID and their Plan hash value information along with execution details. Don't forget to remove comments and format the SQL before running.
WITH sql_id_list AS
(
SELECT /*+ MATERIALIZE */ regexp_substr(:SQL_ID, '[^,]+', 1, LEVEL) SQL_ID
FROM dual
CONNECT BY LEVEL <= length(:SQL_ID) - length(REPLACE(:SQL_ID, ',', '')) + 1
), sqlstat
AS
(
SELECT TRUNC(last_active_Time,:TRUNC_DATE_FORMAT) last_active_Time
--,s.instance_number inst_num
,s.sql_id
,s.plan_hash_value
,MAX(s.child_number) max_child_number
,command_type
,parsing_schema_name schema
--,module
,ROUND((SUM(s.elapsed_time)/1000000)/60,2) elapsed_time_minutes
,ROUND(SUM(s.elapsed_time)/1000000,2) elapsed_time_seconds
,SUM(s.EXECUTIONS) executions_total
,SUM(s.parse_calls) parse_total
,SUM(s.rows_processed) rows_processed_total
,ROUND(SUM(s.rows_processed)/(SUM(s.elapsed_time)/1000000),2) rows_per_sec
,ROUND(SUM(s.EXECUTIONS)/(SUM(s.elapsed_time)/1000000),2) exec_per_sec
,SUM(s.ELAPSED_TIME)/(SUM(s.EXECUTIONS))/1000 avg_elap_stmt_ms
,ROUND(SUM(s.concurrency_wait_time)/sum(EXECUTIONS)/1000,2) avg_cc_wait
,ROUND(SUM(s.cluster_wait_time)/sum(EXECUTIONS)/1000,2) avg_cl_wait
,ROUND(SUM(s.user_io_wait_time)/sum(EXECUTIONS)/1000,2) avg_io_wait
,ROUND(SUM(s.cpu_time)/sum(EXECUTIONS)/1000,2) avg_cpu_time
,ROUND(SUM(s.application_wait_time)/sum(EXECUTIONS)/1000,2) avg_app_wait
,ROUND(SUM(s.buffer_gets)/sum(EXECUTIONS)/1000,2) avg_buffer_gets
,ROUND(SUM(s.direct_writes)/sum(EXECUTIONS),2) avg_direct_writes
,ROUND(SUM(s.disk_reads)/sum(EXECUTIONS),2) avg_disk_reads
,SUBSTR(s.sql_text,1,2000) sql_text
FROM v$sql s
WHERE s.elapsed_time > 0
AND EXECUTIONS > 0
--AND ( s.sql_id=:SQL_ID OR :SQL_ID IS NULL)
AND ( s.sql_id IN (SELECT sql_id FROM sql_id_list) OR :SQL_ID IS NULL)
AND (parsing_schema_name LIKE '%'||:SCHEMA_NAME_LIKE||'%' OR :SCHEMA_NAME_LIKE IS NULL)
AND (last_active_Time >= SYSDATE -:NUMBER_OF_HOURS/24 OR :NUMBER_OF_HOURS IS NULL)
AND (UPPER(sql_text) LIKE UPPER(:SQL_TEXT_START)||'%' OR :SQL_TEXT_START IS NULL)
AND (UPPER(sql_text) LIKE '%'||UPPER(:SQL_TEXT_CONTAINS)||'%' OR :SQL_TEXT_CONTAINS IS NULL)
GROUP BY TRUNC(last_active_time,:TRUNC_DATE_FORMAT)
--,s.instance_number
--,s.snap_id
,s.sql_id
,s.plan_hash_value
,command_type
,s.parsing_schema_name
-- ,s.module
,SUBSTR(s.sql_text,1,2000)
ORDER BY 1 DESC, 2
)
SELECT s.*
FROM sqlstat s
WHERE (avg_elap_stmt_ms > :AVG_ELAP_STMT_MS OR :AVG_ELAP_STMT_MS IS NULL)
AND (executions_total > :EXECUTIONS_TOTAL OR :EXECUTIONS_TOTAL IS NULL)
ORDER BY &AWR_ORDER_COL1 DESC, s.SQL_ID
;
Comments
Post a Comment