Query_1
select SNAP_ID,
INSTANCE_NUMBER,
PARSING_SCHEMA_NAME,
SQL_ID,
BEGIN_INTERVAL_TIME,
END_INTERVAL_TIME,
PLAN_HASH_VALUE,
SQL_PROFILE,
EXECUTIONS,
round( ( ( ( (TOTAL_CPU_TIME/1000000)/3600 )/32 ) * 100 ), 2 ) PERCENTAGE_OF_TOTAL_CPU,
TOTAL_LIO,
ROWS_PER_EXEC,
DISK_READS_PER_EXEC PIO_PER_EXEC,
BUFFER_GETS_PER_EXEC LIO_PER_EXEC,
round( CPU_TM_IN_SECONDS, 2 ) CPU_SECS,
round( IO_WAITS_IN_SECONDS, 2 ) PIO_SECS,
round( OTHER_WAITS, 2 ) OTHER_SECS,
round( ELAPSED_TM_IN_SECONDS, 2 ) ELAPSED_SECS,
round( ELAPSED_TM_MINUTES, 2 ) ELAPSED_MINUTES,
round( MAX_CPU_UTILIZATION_FOR_HOUR, 2 ) MAX_CPU,
round( AVG_CPU_UTILIZATION_FOR_HOUR, 2 ) AVG_CPU
from
(
select SNAP_ID,
INSTANCE_NUMBER,
PARSING_SCHEMA_NAME,
SQL_ID,
BEGIN_INTERVAL_TIME,
END_INTERVAL_TIME,
PLAN_HASH_VALUE,
SQL_PROFILE,
EXECUTIONS,
TOTAL_CPU_TIME,
TOTAL_LIO,
TOTAL_DISK_READS,
ROWS_PER_EXEC,
PARSES_PER_EXEC,
DISK_READS_PER_EXEC,
BUFFER_GETS_PER_EXEC,
CPU_TM_IN_SECONDS,
IO_WAITS_IN_SECONDS,
ELAPSED_TM_IN_SECONDS,
OTHER_WAITS,
ELAPSED_TM_MINUTES,
MAX_CPU_UTILIZATION_FOR_HOUR,
AVG_CPU_UTILIZATION_FOR_HOUR,
LIO_RESPONSE_TIME_MS --,
--avg(ST_RAW_MATERIAL) over (partition by SQL_ID) INFERRED_SERVICE_TIME
from
(
select A.SNAP_ID,
A.INSTANCE_NUMBER,
A.PARSING_SCHEMA_NAME,
A.BEGIN_INTERVAL_TIME,
A.END_INTERVAL_TIME,
A.SQL_ID,
A.PLAN_HASH_VALUE,
A.SQL_PROFILE,
A.EXECUTIONS,
A.TOTAL_CPU_TIME,
A.TOTAL_LIO,
A.TOTAL_DISK_READS,
A.ROWS_PER_EXEC,
A.PARSES_PER_EXEC,
A.DISK_READS_PER_EXEC,
A.BUFFER_GETS_PER_EXEC,
A.CPU_TM_IN_SECONDS,
A.IO_WAITS_IN_SECONDS,
A.ELAPSED_TM_IN_SECONDS,
A.OTHER_WAITS,
A.ELAPSED_TM_MINUTES,
B.MAXVAL MAX_CPU_UTILIZATION_FOR_HOUR,
B.AVERAGE AVG_CPU_UTILIZATION_FOR_HOUR,
case
when BUFFER_GETS_PER_EXEC = 0
then 0
else ( A.CPU_TM_IN_SECONDS/A.BUFFER_GETS_PER_EXEC ) * 1000
end LIO_RESPONSE_TIME_MS --,
--case
-- when B.AVERAGE < 81
-- then ( ( A.CPU_TM_IN_SECONDS/A.BUFFER_GETS_PER_EXEC ) * 1000 )
-- else null
-- end ST_RAW_MATERIAL
from
(
select SNAP_ID,
INSTANCE_NUMBER,
PARSING_SCHEMA_NAME,
BEGIN_INTERVAL_TIME,
END_INTERVAL_TIME,
SQL_ID,
PLAN_HASH_VALUE,
SQL_PROFILE,
EXECUTIONS,
CPU_TIME_DELTA TOTAL_CPU_TIME,
BUFFER_GETS_DELTA TOTAL_LIO,
DISK_READS_DELTA TOTAL_DISK_READS,
ROWS_PER_EXEC,
PARSES_PER_EXEC,
DISK_READS_PER_EXEC,
BUFFER_GETS_PER_EXEC,
CPU_TM_IN_SECONDS,
IO_WAITS_IN_SECONDS,
ELAPSED_TM_IN_SECONDS,
ELAPSED_TM_IN_SECONDS - ( CPU_TM_IN_SECONDS + IO_WAITS_IN_SECONDS ) OTHER_WAITS,
ELAPSED_TM_IN_SECONDS/60 ELAPSED_TM_MINUTES
from
(
select SNAP.SNAP_ID,
SNAP.INSTANCE_NUMBER,
PARSING_SCHEMA_NAME,
SQL_ID,
SQL_PROFILE,
SNAP.BEGIN_INTERVAL_TIME,
SNAP.END_INTERVAL_TIME,
PLAN_HASH_VALUE,
EXECUTIONS_DELTA EXECUTIONS,
CPU_TIME_DELTA,
BUFFER_GETS_DELTA,
DISK_READS_DELTA,
case
when EXECUTIONS_DELTA = 0
then 0
else ROWS_PROCESSED_DELTA/EXECUTIONS_DELTA
end ROWS_PER_EXEC,
case
when EXECUTIONS_DELTA = 0
then 0
else PARSE_CALLS_DELTA/EXECUTIONS_DELTA
end PARSES_PER_EXEC,
case
when EXECUTIONS_DELTA = 0
then DISK_READS_DELTA
else DISK_READS_DELTA/EXECUTIONS_DELTA
end DISK_READS_PER_EXEC,
case
when EXECUTIONS_DELTA = 0
then BUFFER_GETS_DELTA
else BUFFER_GETS_DELTA/EXECUTIONS_DELTA
end BUFFER_GETS_PER_EXEC,
case
when EXECUTIONS_DELTA = 0
then CPU_TIME_DELTA/1000000
else (CPU_TIME_DELTA/EXECUTIONS_DELTA)/1000000
end CPU_TM_IN_SECONDS,
case
when EXECUTIONS_DELTA = 0
then ELAPSED_TIME_DELTA/1000000
else (ELAPSED_TIME_DELTA/EXECUTIONS_DELTA)/1000000
end ELAPSED_TM_IN_SECONDS,
case
when EXECUTIONS_DELTA = 0
then IOWAIT_DELTA/1000000
else (IOWAIT_DELTA/EXECUTIONS_DELTA)/1000000
end IO_WAITS_IN_SECONDS
from DBA_HIST_SQLSTAT,
DBA_HIST_SNAPSHOT SNAP
where SNAP.SNAP_ID = DBA_HIST_SQLSTAT.SNAP_ID
and SNAP.INSTANCE_NUMBER = DBA_HIST_SQLSTAT.INSTANCE_NUMBER
and FORCE_MATCHING_SIGNATURE = (select distinct FORCE_MATCHING_SIGNATURE from DBA_HIST_SQLSTAT where SQL_ID = '&SQL_ID' )
--and SQL_ID = '7j9rh7m0bkdpu'
--and SNAP.SNAP_ID between 35545 and 35561
--and PLAN_HASH_VALUE = 1200263200
--and SNAP.SNAP_ID = 35578
--and SNAP.INSTANCE_NUMBER = 6
)
) A,
(
select *
from DBA_HIST_SYSMETRIC_SUMMARY
where METRIC_NAME = 'Host CPU Utilization (%)'
) B
where A.SNAP_ID = B.SNAP_ID
and A.INSTANCE_NUMBER = B.INSTANCE_NUMBER
)
)
--where SQL_PROFILE is not null
order by SNAP_ID desc
No comments:
Post a Comment