Home » RDBMS Server » Performance Tuning » sqlID time for every Sql id execution (RDBMS 12.2..0.1 on linux)
sqlID time for every Sql id execution [message #678978] Fri, 24 January 2020 11:05 Go to next message
evoradba
Messages: 126
Registered: April 2005
Location: Canada
Senior Member
Hello
i need to find out how long a sqlID took every time it run , like every time a select statment run with the sqlid SQL ID: ckruqmxyu1xsz

thank you !
Re: sqlID time for every Sql id execution [message #678979 is a reply to message #678978] Fri, 24 January 2020 12:37 Go to previous messageGo to next message
evoradba
Messages: 126
Registered: April 2005
Location: Canada
Senior Member
I figure out - in case anyone needs it - Im sharing it

alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format='YYYY/MM/DD HH24:MI:SS';
spool query.out
select sql_id,
starting_time,
end_time,
(EXTRACT(HOUR FROM run_time) * 3600
+ EXTRACT(MINUTE FROM run_time) * 60
+ EXTRACT(SECOND FROM run_time)) run_time_sec,
READ_IO_BYTES,
PGA_ALLOCATED PGA_ALLOCATED_BYTES,
TEMP_ALLOCATED TEMP_ALLOCATED_BYTES
from (
select
sql_id,
max(sample_time - sql_exec_start) run_time,
max(sample_time) end_time,
sql_exec_start starting_time,
sum(DELTA_READ_IO_BYTES) READ_IO_BYTES,
sum(DELTA_PGA) PGA_ALLOCATED,
sum(DELTA_TEMP) TEMP_ALLOCATED
from
(
select sql_id,
sample_time,
sql_exec_start,
DELTA_READ_IO_BYTES,
sql_exec_id,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_PGA,
greatest(TEMP_SPACE_ALLOCATED - first_value(TEMP_SPACE_ALLOCATED) over (partition by sql_id,sql_exec_id order by sample_time rows 1 preceding),0) DELTA_TEMP
from
dba_hist_active_sess_history
where
sample_time >= to_date ('2019/12/29 00:00:00','YYYY/MM/DD HH24:MI:SS')
and sample_time < to_date ('2020/01/23 03:10:00','YYYY/MM/DD HH24:MI:SS')
and sql_exec_start is not null
and IS_SQLID_CURRENT='Y'
)
group by sql_id,SQL_EXEC_ID,sql_exec_start
order by sql_id
)
where sql_id = '5z81aku4zu4k0'
order by sql_id, run_time_sec desc;
Re: sqlID time for every Sql id execution [message #678982 is a reply to message #678979] Sat, 25 January 2020 02:07 Go to previous message
John Watson
Messages: 8344
Registered: January 2010
Location: Global Village
Senior Member
Where did you find the script that you "figured out"? There is a copy here
https://bdrouvot.wordpress.com/2013/04/19/drill-down-to-sql_id-execution-details-in-ash/
but I don't know if he wrote it or also plagiarized it.


Previous Topic: Database performance tuning (merged)
Next Topic: Select query too slow
Goto Forum:
  


Current Time: Fri Sep 18 15:59:24 CDT 2020