Another statement hat (hopefully) takes longer 10 seconds:
--
-- An SQL statement that takes a while to finish.
--
-- Note how SQL_ID changes when SQL changes.
--
set timing on
select
avg(kurs)
from
bewertungvalor
where
--IT:
-- stichtag between date '2011-01-01' and date '2011-03-22' and
--66er:
stichtag between
date '2013-01-01' and
date '2013-02-10'
and kurs > 1000;
set timing off
--
-- Wouldn't it be nice if all this were automated...
--
-- ASH = Active Session History
--
-- 2MB memory per CPU reserved for collected data
select
pool,
bytes/1024/1024 "Size in MB"
from
v$sgastat
where
name = 'ASH buffers';
-- Compare
-- select cpu_count_current from v$license;
--
-- Find oldest and most recent timestamp of
-- such gathered statements.
--
-- Oldest records are purged from memory when
-- SQL activity writes new records.
--
select
to_char(min(sample_time), 'dd.mm.yyyy hh24:mi:ss') min_time,
to_char(max(sample_time), 'dd.mm.yyyy hh24:mi:ss') max_time,
count(*) cnt
from
v$active_session_history;
--
-- How many seconds is spent for each SQL statement
--
select
top.sql_id,
top.cnt,
top.pct,
replace(cast(substr(sql.sql_fulltext, 1, 100) as varchar2(100)), chr(10), '') text
from (
select
sql_id,
count(*) cnt,
to_char(ratio_to_report(count(*)) over () * 100, '90.99') pct,
row_number() over (order by count(*) desc) r
from
v$active_session_history
where
session_type <> 'BACKGROUND' and
sample_time > sysdate - 20/24/60 -- Last twenty minutes
group by
sql_id
order by
count(*) desc
) top left join
v$sqlarea sql on top.sql_id = sql.sql_id
where
top.r < 50
order by
top.cnt desc
;
--
-- v$active_session_history is flushed every
-- hour or when 66%(?) full.
--
-- Only records where is_awr_sample = 'Y'
--
select
snap_id,
to_char(sample_time, 'dd.mm.yyyy hh24:mi:ss') sample_time,
session_serial#,
sql_id,
sql_exec_start,
time_waited
from
dba_hist_active_sess_history
where
session_id = &1 and
session_serial# = &2
order by
sample_time;
select
snap_id,
dbid,
instance_number I,
'|' || to_char(startup_time , 'dd.mm.yyyy hh24:mi:ss'),
'|' || to_char(begin_interval_time, 'dd.mm.yyyy hh24:mi:ss'),
'|' || to_char(end_interval_time , 'dd.mm.yyyy hh24:mi:ss'),
--snap_level, -- ?
snap_flag, -- 0: Automatic, 1. Manually, 2: Imported
flush_elapsed -- How long did it take to take snapshot
from
dba_hist_snapshot
order by
begin_interval_time;
Execute a few SQL statements. Which one took most time to execute?
--
-- A procedure that takes a while to run...
-- which SQL statement contributes most to
-- spent time?
--
set timing on
declare
start_time date;
begin
start_time := sysdate;
<<ONE_MINUTE>> while true loop
for valor in (
select valorennr, valorenzusatznr
from valorgattung
) loop
if start_time + 1 * 1/24/60 < sysdate then
exit ONE_MINUTE;
end if;
------------------------------------------------------------
declare
r bewertungvalor%rowtype;
begin
-- 407918886kfkv
select * into r from bewertungvalor
where valorennr = valor.valorennr and
valorenzusatznr = valor.valorenzusatznr and
stichtag = date '2012-12-20';
exception
when no_data_found or
too_many_rows then
null;
end;
------------------------------------------------------------
/*
declare
r bewertungvalor%rowtype;
begin
execute immediate 'select * from bewertungvalor ' ||
'where valorennr = ''' || valor.valorennr || ''' and ' ||
'valorenzusatznr = ''' || valor.valorenzusatznr || ''' and ' ||
'stichtag = date ''2012-12-20'' ' into r;
exception
when no_data_found or
too_many_rows then
null;
end;
*/
------------------------------------------------------------
declare
r valorenkurs%rowtype;
begin
select * into r from valorenkurs
where valorennr = valor.valorennr and
valorenzusatznr = valor.valorenzusatznr and
gueltigabdatum = date '2012-12-20';
exception
when no_data_found or
too_many_rows then
null;
end;
end loop;
end loop;
end;
/