Количество сессий на схему:
SELECT schemaname,
COUNT(*) sessions
FROM V$SESSION
WHERE schemaname <> 'SYS'
GROUP BY schemaname
ORDER BY COUNT(*) DESC;
Resource utilization history
select to_char(BEGIN_INTERVAL_TIME,'dd-mm-yyyy HH24:MI'),
to_char(END_INTERVAL_TIME,'dd-mm-yyyy HH24:MI'),
ss.SNAP_ID,
l.CURRENT_UTILIZATION,
l.MAX_UTILIZATION
from DBA_HIST_RESOURCE_LIMIT l,
DBA_HIST_SNAPSHOT ss
where ss.SNAP_ID = l.SNAP_ID
and upper(l.RESOURCE_NAME) like upper('%&resource_name%')
order by BEGIN_INTERVAL_TIME;
/
Top SQL by Buffer Gets
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by buffer_gets desc nulls last;
Top SQL by Buffer Gets / Rows Proc
select substr(sql_text,1,500) "SQL",
round((cpu_time/1000000),3) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by (buffer_gets/nvl(replace(rows_processed,0,1),1)) desc nulls last;
Top SQL by Disk Reads
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by disk_reads desc nulls last;
Top SQL by CPU
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by cpu_time desc nulls last;
Top SQL by Executions
select substr(sql_text,1,500) "SQL",
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
module "Module"
from v$sql s
order by executions desc nulls last;
Top SQL by Waits
select INST_ID,
(cpu_time/1000000) "CPU_Seconds",
disk_reads "Disk_Reads",
buffer_gets "Buffer_Gets",
executions "Executions",
case when rows_processed = 0 then null
else round((buffer_gets/nvl(replace(rows_processed,0,1),1)))
end "Buffer_gets/rows_proc",
round((buffer_gets/nvl(replace(executions,0,1),1))) "Buffer_gets/executions",
(elapsed_time/1000000) "Elapsed_Seconds",
--round((elapsed_time/1000000)/nvl(replace(executions,0,1),1)) "Elapsed/Execution",
substr(sql_text,1,500) "SQL",
module "Module",SQL_ID
from gv$sql s
where sql_id in (
select distinct sql_id from (
WITH sql_class AS
(select sql_id, state, count(*) occur from
(select sql_id
, CASE WHEN session_state = 'ON CPU' THEN 'CPU'
WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') THEN 'IO'
ELSE 'WAIT' END state
from gv$active_session_history
where session_type IN ( 'FOREGROUND')
and sample_time between trunc(sysdate,'MI') - 15/24/60 and trunc(sysdate,'MI') )
group by sql_id, state),
ranked_sqls AS
(select sql_id, sum(occur) sql_occur , rank () over (order by sum(occur)desc) xrank
from sql_class
group by sql_id )
select sc.sql_id, state, occur from sql_class sc, ranked_sqls rs
where rs.sql_id = sc.sql_id
--and rs.xrank <= 15
order by xrank, sql_id, state ))
order by elapsed_time desc nulls last;
select s.parsing_schema_name,
inst_id,
sql_id,
plan_hash_value,
child_number,
round(nullif(s.ELAPSED_TIME, 0) / nullif(s.EXECUTIONS, 0) / 1000000, 4) elap_per_exec,
round(s.USER_IO_WAIT_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) io_wait_pct,
round(s.CLUSTER_WAIT_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) cluster_wait_pct,
round(s.application_wait_time / nullif(s.ELAPSED_TIME, 0) * 100, 2) app_wait_pct,
round(s.CPU_TIME / nullif(s.ELAPSED_TIME, 0) * 100, 2) cpu_time_pct,
round(s.PHYSICAL_READ_BYTES / nullif(s.EXECUTIONS, 0) / 1024 / 1024, 2) pio_per_exec_mb,
round(s.PHYSICAL_READ_BYTES / nullif(s.PHYSICAL_READ_REQUESTS, 0), 2) / 1024 read_per_request_kbytes,
round(s.buffer_gets / nullif(s.executions, 0), 4) BufferGets_per_Exec,
s.executions,
to_char(s.last_active_time,'dd/mm/yyyy hh24:mi:ss') last_act_time,
s.first_load_time,
s.sql_fulltext,
s.sql_profile,
s.sql_patch,
s.sql_plan_baseline
FROM gv$sql s
WHERE 1=1
and s.parsing_schema_name in ('ANGOR')
order by s.last_active_time desc;
Используем табличную функцию DBMS_SQLTUNE.SELECT_CURSOR_CACHE:
SELECT buffer_gets,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY buffer_gets desc;
SELECT disk_reads,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
ORDER BY disk_reads desc;
SELECT cpu_time / 1000000 as cpu_time_secs,
executions,
last_exec_start_time,
parsing_schema_name,
module, action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY cpu_time desc;
SELECT elapsed_time / 1000000 as elapsed_time_secs,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY elapsed_time desc;
SELECT executions,
last_exec_start_time,
parsing_schema_name,
module, action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY executions desc;
SELECT direct_writes,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 1000'))
ORDER BY direct_writes desc;
SELECT rows_processed,
executions,
last_exec_start_time,
parsing_schema_name,
module,
action,
sql_id,
plan_hash_value,
sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 10000'))
ORDER BY rows_processed desc;
List out SQL Tuning Set contents to check we got what we wanted
SELECT
first_load_time,
executions as execs,
parsing_schema_name,
elapsed_time / 1000000 as elapsed_time_secs,
cpu_time / 1000000 as cpu_time_secs,
buffer_gets,
disk_reads,
direct_writes,
rows_processed,
fetches,
optimizer_cost,
sql_plan,
plan_hash_value,
sql_id,
sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'SAMPLE_TUNING_SET'));
Database Time and Average Active Sessions
Active Session History (ASH)
Oracle для выборочных активных сеансов каждые 1 секунду в кольцевом буфере в SGA.
Можно представить ASH как историю DB time.
Так как ASH связан с активными сеансами, то мы можем извлечь DB time из ASH:
ASH COUNT (*) = DB time.
select count(*) DB_TIME_SEC
from v$active_session_history
where session_type = 'FOREGROUND';
DB_TIME_SEC
-----------
993
Формула для Average Active Sessions:
DB Time / Elapsed Time = Average Active Sessions
Данные для активных сеансов доступны из представления V$ACTIVE_SESSION_HISTORY.
Поскольку данные хранятся в памяти (SGA) в круговом буфере, если у вас очень загруженная система,
то возможно, что эти данные будут сброшены на диск в таблицу SYS.DBA_HIST_ACTIVE_SESS_HISTORY,
но в неё попадет только 1 из 10 образцов из V$ACTIVE_SESSION_HISTORY.
Так что DB_TIME из DBA_HIST_ACTIVE_SESS_HISTORY, будет ASH COUNT (*) * 10 = DB TIME.
Average Active Sessions = DB Time/Elapsed Time
Если мы хотим рассчитать Average Active Sessions между 09:05 и 09:30
т.е. в течение заданного периода в 25 минут (1500 секунд).
DB Time = 3144
Elapsed Time = 25 minutes = (25 * 60) = 1500 seconds
Average Active Sessions would be: 3144 / 1500 = 2.096
select count(*) DB_TIME,
count(*) / (25 * 60) AAS
from v$active_session_history
where session_type = 'FOREGROUND'
and sample_time between to_date('08012015 09:05:00','ddmmyyyy hh24:mi:ss')
and to_date('08012015 09:30:00','ddmmyyyy hh24:mi:ss')
/
DB_TIME AAS
---------- ----------
3144 2.096
Например, мы можем найти TOP активных sql_id, упорядоченных по DB time:
select count(*), sql_id
from v$active_session_history
where session_type = 'FOREGROUND'
and sql_id is not null
group by sql_id
order by 1 desc
/
COUNT(*) SQL_ID
---------- -------------
29274 3t431hgtbs8t7
11 4ztz048yfq32s
5 0uuczutvk6jqj
3 f5yun5dynkskv
2 4mua4wc69sxyf
1 2ft0vv13g0xkd
1 3y5p8203p74hn
1 934ur8r7tqbjx
1 459f3z9u4fb3u
1 72cjd89q7d2s5
1 65gavqa851ymj
11 rows selected.
To find the session ids that executed sql with high DB time we can use the following.
SQL> select count(*), sql_id, session_id
2 from v$active_session_history
3 where session_type = 'FOREGROUND'
4 and sql_id is not null
5 group by sql_id,session_id
6 order by 1 desc
7 /
COUNT(*) SQL_ID SESSION_ID
---------- ------------- ----------
14637 3t431hgtbs8t7 135
14637 3t431hgtbs8t7 373
5 0uuczutvk6jqj 125
4 4ztz048yfq32s 5
3 4ztz048yfq32s 19
3 4ztz048yfq32s 7
3 f5yun5dynkskv 7
2 4mua4wc69sxyf 125
2 65gavqa851ymj 7
1 72cjd89q7d2s5 125
1 3y5p8203p74hn 374
1 934ur8r7tqbjx 125
1 459f3z9u4fb3u 5
1 2ft0vv13g0xkd 125
1 4ztz048yfq32s 14
15 rows selected.
DB time
col name format a10
select statistic#, name, class, value
from v$sysstat
where name = 'DB time'
/
STATISTIC# NAME CLASS VALUE
---------- ---------- ---------- ----------
20 DB time 1 13828791
select to_char(begin_time,'dd.mm.yyy hh24:mi:ss') begin_time,
to_char(end_time,'dd.mm.yyy hh24:mi:ss') end_time,
intsize_csec interval_size,
group_id,
metric_name,
value
from v$sysmetric
where metric_name = 'Database Time Per Sec'
/
BEGIN_TIME END_TIME INTERVAL_SIZE GROUP_ID METRIC_NAME VALUE
------------------ ------------------ ------------- ---------- ------------------------- ----------
09.01.015 10:25:48 09.01.015 10:26:48 6001 2 Database Time Per Sec .296650558
09.01.015 10:27:03 09.01.015 10:27:18 1500 3 Database Time Per Sec .300146667
There are 2 records for two different groups because of short duration and long duration intervals.
System Metrics Long Duration (group_id= 2) : 60 second interval
System Metrics Short Duration (group_id = 3) : 15 second interval
V$SYSMETRIC_SUMMARY (with appropriate DBA_HIST_SYSMETRIC_SUMMARY for on disk snapshots) contains long duration metric
(1 hour interval) information such as avg,min,max.
SQL> select maxval,minval,average,standard_deviation
2 from v$sysmetric_summary
3 where metric_name = 'Database Time Per Sec'
4 /
MAXVAL MINVAL AVERAGE STANDARD_DEVIATION
---------- ---------- ---------- ------------------
3.12933844 0 .282698591 .595884377
V$SYSMETRIC_HISTORY (with appropriate DBA_HIST_SYSMETRIC_SUMMARY on disk snapshots) contains short duration and long duration metrics.
Примеры использования V$ACTIVE_SESSION_HISTORY:
SELECT *
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1
AND session_type <> 'BACKGROUND';
SELECT *
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time BETWEEN TO_DATE('2018-09-03 00:00:00','YYYY-MM-DD HH24:MI:SS')
AND TO_DATE('2018-09-03 23:59:59','YYYY-MM-DD HH24:MI:SS')
AND session_type <> 'BACKGROUND';
SELECT sql_id
,session_id,session_serial#
,COUNT(*) seconds
FROM v$active_session_history
WHERE sample_time > SYSDATE-1/24/12
AND sql_id = '2vp4k2kgy2wm4'
GROUP BY sql_id, session_id,session_serial#
ORDER BY seconds DESC;
/
SELECT sql_id,
session_id,
session_serial#,
COUNT(*) seconds
FROM v$active_session_history
WHERE sample_time > SYSDATE-1/24/12
AND sql_id = '2vp4k2kgy2wm4'
GROUP BY sql_id, session_id, session_serial#
ORDER BY seconds DESC;
SELECT * FROM DBA_HIST_SQLTEXT HIST_SQLTEXT
WHERE sql_id IN
(
SELECT sql_id
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1
AND session_type <> 'BACKGROUND'
AND sql_id is not NULL
);
select NVL(sql_id,'NULL') as sql_id
,count(*) as DB_time
,ROUND(100*count(*)/SUM(count(*)) OVER (), 2) as Pct_load
from v$active_session_history
where sample_time > sysdate - 5/24/60
and session_type <> 'BACKGROUND'
group by sql_id
order by count(*) desc;
/
select NVL(event,'NULL') as event
,count(*) as samples
,ROUND(count(*)/600,4) as AvgActiveSess
from v$active_session_history
where sql_id IS NULL
and sample_time > sysdate - 10/24/60
and session_type <> 'BACKGROUND'
group by event;
select inst_id as instance
,event
,COUNT(distinct current_obj#) as objcount
,SUM(1) as dbtime_secs
from gv$active_session_history
where sample_time > sysdate - 1/24/60 -- pick up last minute only
and wait_class = 'User I/O'
group by inst_id,event
order by event,instance;
SELECT sql_id,
count(*),
round(count(*) / sum(count(*)) over(), 2) pctload
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1/ (24 * 60)
AND session_type <> 'BACKGROUND'
AND session_state = 'ON CPU'
GROUP BY sql_id
ORDER BY count(*) desc;
SELECT ash.sql_id,
count(*)
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EVT
WHERE ash.sample_time > sysdate -1/(24*60)
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'USER I/O'
GROUP BY ash.sql_id
ORDER BY count(*) desc;
SELECT session_id,
count(*)
FROM V$ACTIVE_SESSION_HISTORY
WHERE session_state = 'ON CPU'
AND sample_time > sysdate -1/(24*60)
GROUP BY session_id
ORDER BY count(*) desc;
Current CPU usage by this instance
select round(100*num_cpus_in_use/num_cpus_total,1) cpu_usage_pct
from
(
select count(*) num_cpus_in_use
from v$active_session_history
where sample_time =
(select max(sample_time)
from v$active_session_history
)
and session_state = 'ON CPU'
),
(select value num_cpus_total
from v$osstat
where stat_name = 'NUM_CPUS'
) num_cpus;
Top 5 events for an arbitrary interval
select *
from
(
select nvl(event, session_state) event,
round(100*count(*)/
(sum(count(1)) over ()), 2) "DB time (%)"
from v$active_session_history
where sample_time between to_date(:start_time, 'dd-mon-yyyy hh24:mi:ss')
and to_date(:end_time, 'dd-mon-yyyy hh24:mi:ss')
group by nvl(event, session_state)
order by count(*) desc
)
where rownum<=5;
Database activity as a function of time
select sample_time time,
count(*) activity
from v$active_session_history
group by sample_time
order by sample_time;
Wait profile for a single SQL statement
select nvl(event, session_state) event,
round(100*count(*)/
(sum(count(1)) over ()), 2) "% query time"
from dba_hist_active_sess_history
where sql_id = :sql_id
group by nvl(event, session_state)
order by count(*) desc;
Top SQL for a module
select nvl(ash.sql_id, 'N/A') sql_id,
replace(nvl(txt.sql_text_short, 'N/A'), chr(10), '\n') sql_text,
count(*) "DB time(s)",
round(100*count(*)/
sum(count(1)) over(), 2) "DB time(%)"
from v$active_session_history ASH,
(select sql_id,
dbms_lob.substr(sql_text, :len) sql_text_short
from v$sql
union all
select sql_id,
dbms_lob.substr(sql_text, :len) sql_text_short
from dba_hist_sqltext ) txt
where ash.sql_id = txt.sql_id (+)
and module = :module
group by ash.sql_id, txt.sql_text_short
order by count(*) desc;
Profiling PL/SQL calls in a module
select P.OBJECT_NAME ||
case when p.object_name is not null
and p.procedure_name is not null
then '.'
end ||
P.PROCEDURE_NAME top_plsql_object_name,
count(*) "DB time (s)"
from v$active_session_history ash,
dba_procedures p
where ASH.PLSQL_ENTRY_OBJECT_ID = P.OBJECT_ID (+)
and ASH.PLSQL_ENTRY_SUBPROGRAM_ID = P.SUBPROGRAM_ID (+)
and module = :module
group by p.object_name, p.procedure_name
order by count(*) desc;
Accessed objects
This query gives the breakdown of DB time within the interval of interest by database objects accessed.
select nvl(o.owner ||
case when o.object_name is not null
then '.'
end ||
o.object_name, 'N/A') object_name,
round(100*count(*)/
sum(count(1)) over (), 2) "DB time (%)"
from dba_hist_active_SESS_HISTORY ash,
dba_objects o
where ash.CURRENT_OBJ# = o.object_id (+)
and ash.sample_time between to_date(:start_period, 'dd-mon-yyyy hh24:mi:ss')
and to_date(:end_period, 'dd-mon-yyyy hh24:mi:ss')
group by o.owner || case when o.object_name is not null
then '.' end
|| o.object_name
order by count(*) desc;
Datafile access during last N hours
select nvl(f.file_name, 'not in I/O waits'),
10*count(*) "DB time (s)",
round(100*count(*)/
sum(count(1)) over (), 2) "DB time (%)"
from DBA_HIST_ACTIVE_SESS_HISTORY ash,
DBA_DATA_FILES f where ash.current_file# = f.file_id (+)
and ash.sample_time > sysdate - :hours/24
group by f.file_name
order by count(*) desc;
Breakdown of query DB time by plan operations
SELECT ash.sql_plan_line_id,
ash.sql_plan_operation,
ash.sql_plan_options,
p.object_name,
round(100*COUNT(*)/
sum(count(1)) over(), 2) "% time"
FROM v$active_session_history ash,
v$sql_plan p
WHERE ash.sql_id = p.sql_id
AND ash.sql_plan_hash_value = p.plan_hash_value
AND ash.sql_plan_line_id = P.id
AND ash.sql_id = :sql_id
AND ash.sql_plan_hash_value = :plan_hash_value
GROUP BY ASH.SQL_PLAN_LINE_ID,
ASH.SQL_PLAN_OPERATION,
ASH.SQL_PLAN_OPTIONS,
p.object_name
ORDER BY count(*) DESC;
Long-running queries at a specific moment in time
select distinct sql_ID,
24*60*60*(sysdate - sql_exec_start) seconds_running
FROM v$active_session_history
where sample_time = (select max(sample_time)
from v$active_session_history
where sample_time < to_date(:time,
'dd-mon-yyyy hh24:mi'))
and sql_id is not null
order by 24*60*60*(sysdate - sql_exec_start) desc;
Time model statistics query
This query gives breakdown of DB time by session state, similar to the time model statistics section of the AWR report.
select status,
round(100*count(*)/
sum(count(1)) over(), 2) "% DB time"
from
(
select case when in_connection_mgmt='Y'
then 'IN_CONNECTION_MGMT'
when in_parse = 'Y'
then 'IN_PARSE'
when in_hard_parse = 'Y'
then 'IN_HARD_PARSE'
when in_sql_execution = 'Y'
then 'IN_sql_execution'
when in_plsql_execution = 'Y'
then 'IN_plsql_execution'
when in_plsql_compilation = 'Y'
then 'IN_plsql_compilation'
when in_plsql_rpc = 'Y'
then 'IN_plsql_rpc'
when in_java_execution = 'Y'
then 'IN_JAVA_EXECUTION'
when in_bind = 'Y'
then 'IN_BIND'
when in_cursor_close = 'Y'
then 'IN_CURSOR_CLOSE'
when in_sequence_load = 'Y'
then 'IN_SEQUENCE_LOAD'
else 'N/A'
end status
FROM dba_hist_active_sess_history
)
group by status
order by count(*) desc;
SQL statements consuming most PGA at a specific time
select ash.sql_id,
replace(nvl(txt.sql_text_short, 'N/A'),
chr(10), '\n'
) sql_text,
round(ASH.PGA_ALLOCATED/1024/1024) pga_mb
from dba_hist_active_sess_history ash,
(
select sql_id, dbms_lob.substr(sql_text, :len) sql_text_short
from v$sql
union all
select sql_id, dbms_lob.substr(sql_text, :len) sql_text_short
from dba_hist_sqltext
) txt
where ash.sql_id = txt.sql_id (+)
and ash.sql_id is not null
and ash.sample_time = (select max(sample_time)
from dba_hist_active_sess_history
where sample_time < to_date(:time, 'dd-mon-yyyy hh24:mi:ss'))
order by ash.pga_allocated desc;
SELECT ash.sql_id,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAIT",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE SQL_ID is not null and en.event#=ash.event# and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.sql_id
ORDER BY sum(decode(ash.session_state,'ON CPU',1,1)) desc;
SELECT ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "WAITING",
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)) "IO",
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
FROM V$ACTIVE_SESSION_HISTORY ASH,V$EVENT_NAME EN
WHERE en.event# = ash.event#
and ash.sample_time > sysdate -1/(24*60)
GROUP BY ash.session_id,ash.user_id,ash.session_serial#,ash.program
ORDER BY sum(decode(ash.session_state,'ON CPU',1,1));
TOP SQL:
SET LONG 2000;
SELECT ALL_USERS.username AS user_name,
V$SQL.module,
V$SQL.sql_text,
V$SQL.sql_fulltext,
NVL(TOP_SQLS.sql_id,'NULL') AS sql_id,
ROUND(pct_load, 1) AS pct_load
FROM(
SELECT user_id,
sql_id,
count(*),
count(*)*100/SUM(count(*)) OVER() AS PCT_LOAD
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate - 1/24
GROUP BY user_id, sql_id
)TOP_SQLS,
V$SQL,
ALL_USERS
WHERE V$SQL.sql_id(+) = TOP_SQLS.sql_id
AND ALL_USERS.user_id(+) = TOP_SQLS.user_id
AND pct_load > 1
AND username <> 'SYS'
ORDER BY pct_load DESC;
select SQL_ID,round(PGA_MB,1) PGA_MB,
percent,
rpad('*',percent*10/100,'*') star
from (select SQL_ID,
sum(DELTA_PGA_MB) PGA_MB ,
(ratio_to_report(sum(DELTA_PGA_MB)) over ())*100 percent,
rank() over(order by sum(DELTA_PGA_MB) desc) rank
from (select SESSION_ID,
SESSION_SERIAL#,
sample_id,
SQL_ID,
SAMPLE_TIME,
IS_SQLID_CURRENT,
SQL_CHILD_NUMBER,
PGA_ALLOCATED,
greatest(PGA_ALLOCATED - first_value(PGA_ALLOCATED) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_PGA_MB"
from v$active_session_history
where IS_SQLID_CURRENT='Y'
and sample_time > sysdate-60/86400
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_PGA_MB) > 0
)
where rank < 11
order by rank;
/
select SQL_ID,
TEMP_MB,
percent,
rpad('*',percent*10/100,'*') star
from(select SQL_ID,
sum(DELTA_TEMP_MB) TEMP_MB ,
(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from(select SESSION_ID,
SESSION_SERIAL#,
sample_id,
SQL_ID,
SAMPLE_TIME,
IS_SQLID_CURRENT,
SQL_CHILD_NUMBER,
temp_space_allocated,
greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from v$active_session_history
where IS_SQLID_CURRENT='Y'
and sample_time > sysdate-1
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < 11
order by rank;
/
select ash.sql_id,
ash.event,
trunc(sum(TM_DELTA_TIME) / 1000000,2) TM_DELTA_TIME,
trunc(sum(TM_DELTA_CPU_TIME) / 1000000,2) TM_DELTA_CPU_TIME,
trunc(sum(TM_DELTA_DB_TIME) / 1000000,2) TM_DELTA_DB_TIME,
SUM(DELTA_READ_IO_REQUESTS) DELTA_READ_IO_REQUESTS,
SUM(DELTA_WRITE_IO_REQUESTS),
trunc(SUM(DELTA_READ_IO_BYTES)/1024/1024,2) DELTA_READ_IO_M,
trunc(SUM(DELTA_WRITE_IO_BYTES)/1024/1024,2) DELTA_WRITE_IO_M,
trunc(SUM(DELTA_INTERCONNECT_IO_BYTES)/1024/1024,2) DELTA_INTERCONNECT_IO_M,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
trunc(sum(decode(ash.session_state,'WAITING',1,0)) -
trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0))),2) "WAITING",
trunc(sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'USER I/O',1,0),0)),2) "IO",
trunc(sum(decode(ash.session_state,'ON CPU',1,1)),2) "TOTAL"
from v$active_session_history ash,V$EVENT_NAME EN
where ash.sample_id > 190681
and ash.sample_id < (190681 + 2527)
AND ash.event#=en.event#(+)
group by ash.sql_id, ash.event
order by 2 desc;
SQL execution history (ASH)
set linesize 230
set pagesize 9999
select cpu_time_delta,
elapsed_time_delta,
iowait_delta,
plan_hash_value,
EXECUTIONS_DELTA,
round(elapsed_time_delta/EXECUTIONS_DELTA/1000/1000, 4) avg_exec_time_sec,
(select to_char(end_interval_time, 'dd/mm/yyyy hh24:mi')
from dba_hist_snapshot hs
where hs.snap_id = a.snap_id) end_date
from dba_hist_sqlstat a,
dba_hist_sqltext b
where a.sql_id=b.sql_id and
snap_id in (select distinct snap_id from dba_hist_snapshot where end_interval_time>=sysdate-&days_back)
and a.sql_id = '&sql_id'
and EXECUTIONS_DELTA != 0
order by snap_id;
SELECT
sysmetric_history.sample_time,
cpu,
bcpu,
DECODE(SIGN((cpu+bcpu)-cpu_ora_consumed), -1, 0, ((cpu+bcpu)-cpu_ora_consumed)) AS cpu_ora_wait,
scheduler,
uio,
sio,
concurrency,
application,
COMMIT,
configuration,
administrative,
network,
queueing,
clust,
other
FROM
(SELECT
TRUNC(sample_time,'MI') AS sample_time,
SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',0,1),0))/60 AS cpu,
SUM(DECODE(session_state,'ON CPU',DECODE(session_type,'BACKGROUND',1,0),0))/60 AS bcpu,
SUM(DECODE(wait_class,'Scheduler',1,0))/60 AS scheduler,
SUM(DECODE(wait_class,'User I/O',1,0))/60 AS uio,
SUM(DECODE(wait_class,'System I/O',1,0))/60 AS sio,
SUM(DECODE(wait_class,'Concurrency',1,0))/60 AS concurrency,
SUM(DECODE(wait_class,'Application',1,0))/60 AS application,
SUM(DECODE(wait_class,'Commit',1,0))/60 AS COMMIT,
SUM(DECODE(wait_class,'Configuration',1,0))/60 AS configuration,
SUM(DECODE(wait_class,'Administrative',1,0))/60 AS administrative,
SUM(DECODE(wait_class,'Network',1,0))/60 AS network,
SUM(DECODE(wait_class,'Queueing',1,0))/60 AS queueing,
SUM(DECODE(wait_class,'Cluster',1,0))/60 AS clust,
SUM(DECODE(wait_class,'Other',1,0))/60 AS other
FROM v$active_session_history
WHERE sample_time>sysdate- INTERVAL '1' HOUR
AND sample_time<=TRUNC(SYSDATE,'MI')
GROUP BY TRUNC(sample_time,'MI')) ash,
(SELECT
TRUNC(begin_time,'MI') AS sample_time,
VALUE/100 AS cpu_ora_consumed
FROM v$sysmetric_history
WHERE GROUP_ID=2
AND metric_name='CPU Usage Per Sec') sysmetric_history
WHERE ash.sample_time (+)=sysmetric_history.sample_time
ORDER BY sample_time;
Top 20 resource intensive SQLs for today
SELECT *
FROM
(SELECT RANK () OVER (PARTITION BY "Snap Day" ORDER BY "CPU Time"+"Disk Reads"+"Buffer Gets"+"Writes"+"Sorts"+"Parses" DESC) AS "Rank",
i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time,'MM/DD/YY') "Snap Day",
shs.sql_id "Sql id",
REPLACE(CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40)),CHR (10),'') "Sql",
SUM(shs.executions_delta) "Execs",ROUND((SUM(shs.elapsed_time_delta)/1000000)/SUM (shs.executions_delta),1) "Time Ea Sec",
ROUND((SUM(shs.cpu_time_delta)/1000000)/SUM (shs.executions_delta),1) "CPU Ea Sec",
ROUND((SUM(shs.iowait_delta)/1000000)/SUM (shs.executions_delta),1) "IO/Wait Ea Sec",
SUM(shs.cpu_time_delta) "CPU Time",
SUM(shs.disk_reads_delta) "Disk Reads",
SUM(shs.buffer_gets_delta) "Buffer Gets",
SUM(shs.direct_writes_delta) "Writes",
SUM(shs.parse_calls_delta) "Parses",
SUM(shs.sorts_delta) "Sorts",
SUM(shs.elapsed_time_delta) "Elapsed"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id,TO_CHAR(hs.begin_interval_time,'MM/DD/YY'),CAST(DBMS_LOB.SUBSTR(sht.sql_text,40) AS VARCHAR (40))
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 20
AND "Snap Day"=TO_CHAR(SYSDATE-1,'MM/DD/YY');
History of running sessions between 2 dates (dd/mm/yyyy hh24:mi)
set linesize 250
set verify off
set pagesize 9999
COLUMN Time FORMAT a20 HEADING 'Sample time'
COLUMN Username FORMAT a12 HEADING 'User name'
COLUMN Session_id FORMAT 99999 HEADING 'Sess ID'
COLUMN sql_id FORMAT a14 HEADING 'SQL ID'
COLUMN program FORMAT a33 HEADING 'Program'
COLUMN module FORMAT a20 HEADING 'Module'
COLUMN event FORMAT a25 HEADING 'Event name'
COLUMN time_waited FORMAT 999,999,999 HEADING 'Time waited'
COLUMN owner1 FORMAT a15 HEADING 'Owner 1'
COLUMN object_name FORMAT a15 HEADING 'Object name 1'
COLUMN owner1 FORMAT a15 HEADING 'Owner 2'
COLUMN p2 FORMAT a15 HEADING 'Object name 2'
SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time,
u.username,
h.blocking_session block_sid,
h.session_id,
h.sql_id,
h.program,
h.module,
h.event,
h.time_waited,
o.owner,
o.object_name,
o1.owner,
o1.object_name p2
FROM DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_OBJECTS o,
dba_users u,
dba_objects o1
where sample_time between to_date('&begin_date', 'dd/mm/yyyy hh24:mi') and to_date('&end_date', 'dd/mm/yyyy hh24:mi')
and session_type != 'BACKGROUND'
and o.object_id (+) = h.CURRENT_OBJ#
and h.p2 = o1.object_id (+)
and time_waited > 0
and u.user_id = h.user_id
and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time;
/
Sessions history for last NNN minutes
set linesize 250
set verify off
set pagesize 9999
COLUMN Time FORMAT a24 HEADING 'Sample time'
COLUMN User_id FORMAT 99999 HEADING 'User ID'
COLUMN Session_id FORMAT 99999 HEADING 'Sess ID'
COLUMN sql_id FORMAT a14 HEADING 'SQL ID'
COLUMN program FORMAT a33 HEADING 'Program'
COLUMN module FORMAT a33 HEADING 'Module'
COLUMN event FORMAT a25 HEADING 'Event name'
COLUMN time_waited FORMAT 999,999,999 HEADING 'Time waited'
COLUMN owner FORMAT a15 HEADING 'Owner'
COLUMN object_name FORMAT a35 HEADING 'Object name'
SELECT to_char(h.SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') time,
h.user_id,
h.session_id,
h.sql_id,
h.program,
h.module,
h.event,
h.time_waited,
o.owner,
o.object_name
FROM v$ACTIVE_SESSION_HISTORY h,
DBA_OBJECTS o
where sample_time between sysdate-1/(24*60/&MINUTES_BACK) and sysdate
and session_type != 'BACKGROUND'
and o.object_id (+) = h.CURRENT_OBJ#
and time_waited > 0
and upper(event) like upper('%&EVENT_TO_FIND%')
order by sample_id, sample_time;
/
Blocking history:
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');
Top 10 SQL_ID's for the last 7 days as identified by ADDM
col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit"
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7
and a.dbid = (select dbid from v$database)
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance)
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24')
and b.advisor_name = 'ADDM'
and b.task_id = l.task_id
and l.status = 'COMPLETED')
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;
alter session set nls_date_format='YYYY:MM:DD:HH24:MI:SS';
select M.end_time
,ROUND(M.value / 100,3) as Metric_AAS
,ROUND(SUM(DECODE(A.session_type,'FOREGROUND',1,0))/((M.end_time - M.begin_time) * 86400 ),3) as ASH_AAS
,COUNT(1) as ASH_count
from v$active_session_history A
,v$sysmetric_history M
where A.sample_time between M.begin_time and M.end_time
and M.metric_name = 'Database Time Per Sec' -- 10g metric
and M.group_id = 2
group by M.end_time,M.begin_time, M.value
order by M.end_time;
/
Какие объекты ожидались дольше вмего за последние 15 минут
select o.owner, o.object_name, o.object_type, a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, dba_objects o
where a.sample_time between sysdate-30/2880 and sysdate
and a.current_obj# = o.object_id
group by o.owner, o.object_name, o.object_type, a.event
order by total_wait_time desc;
Какие события ожидания экземпляра были в топе за последние 15 минут
select a.event,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a
where a.sample_time between sysdate-30/2880 and sysdate
group by a.event
order by total_wait_time desc;
Какие пользователи дольше всего ждали за последние 15 мин
select s.sid, s.username,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, v$session s
where a.sample_time between sysdate-30/2880 and sysdate
and a.session_id = s.sid
group by s.sid, s.username
order by total_wait_time desc;
Какие SQL- запросы выполнялись дольше всего за последние 15 мин
select a.user_id, d.username, s.sql_text,
sum(a.wait_time + a.time_waited) total_wait_time
from v$active_session_history a, v$sqlarea s, dba_users d
where a.sample_time between sysdate-30/2880 and sysdate
and a.sql_id = s.sql_id
and a.user_id = d.user_id
group by a.user_id, s.sql_text, d.username
order by total_wait_time desc;
В EM GRID CONTROL
В окне активных сеансов можно наблюдать DB time сессий сгруппированных по различным критериям:
select ... count(*)
where sample_time between ...
group by sample_time
group by wait_class
group by sql_id
group by session_id, username
Можно написать такой скрипт:
ashtop.sql
SELECT
&1
, COUNT(*) "TotalSeconds"
, SUM(CASE WHEN wait_class IS NULL THEN 1 ELSE 0 END) "CPU"
, SUM(CASE WHEN wait_class ='User I/O' THEN 1 ELSE 0 END) "User I/O"
, SUM(CASE WHEN wait_class ='Application' THEN 1 ELSE 0 END) "Application"
, SUM(CASE WHEN wait_class ='Concurrency' THEN 1 ELSE 0 END) "Concurrency"
, SUM(CASE WHEN wait_class ='Commit' THEN 1 ELSE 0 END) "Commit"
... ...
, SUM(CASE WHEN wait_class ='Queueing' THEN 1 ELSE 0 END) "Queueing"
, SUM(CASE WHEN wait_class ='Other' THEN 1 ELSE 0 END) "Other"
FROM v$active_session_history a , dba_users u
WHERE a.user_id = u.user_id(+)
AND &2
AND sample_time BETWEEN &3 AND &4
GROUP BY &1
ORDER BY "TotalSeconds" DESC, &1
SQL> @ashtop
SQL> @ashtop username,sql_id session_type='FOREGROUND' sysdate-1/24 sysdate
SQL> @ashtop session_state,event sql_id='c13sma6rkr27c' sysdate-1/24 sysdate
SQL> @ashtop event,blocking_session_status,blocking_inst_id,blocking_session,blocking_session_serial# "wait_class in ('Concurrency','Cluster')" sysdate-1/24 sysdate
SQL> @ashtop sql_id "session_id=201 and session_serial#=2045" sysdate-1/24 sysdate
SQL> @ashtop sql_plan_hash_value "session_id=201 and session_serial#=2045" sysdate-1/24 sysdate
SQL> @ash/time_modelusername "session_id=201 and session_serial#=2045" sysdate-1/24 sysdate
SQL> @ash/time_model_phases sql_id "session_id=201 and session_serial#=2045" sysdate-1/24/60 sysdate
SQL> @ash/time_model_phases sql_id,session_state,event "session_id=201 and session_serial#=2045" sysdate-1/24/60 sysdate
SQL> @ashtop username,program "event='log file sync'" sysdate-1/24 sysdate
SQL> @ashtop event,blocking_session_status,blocking_session,blocking_session_serial# wait_class='Application' sysdate-1/24 sysdate
SQL> @ashtop event,blocking_session_status,blocking_session wait_class='Concurrency' sysdate-1/24 sysdate
SQL> @ashtop p1text,p1,p2text,p2,p3text,p3 "event='buffer busy waits'" sysdate-1/24 sysdate
SQL> @ashtop p1text,p1,p2text,TO_NUMBER(TRIM(SUBSTR(TO_CHAR(p2,'0XXXXXXXXXXXXXXX'),1,8)),'XXXXXXXXXXXXXXXX') "event='library cache: mutex X'" sysdate-1/24 sysdate
TOP_LEVEL_SQL_ID
select top_level_Sql_id, module, action, sum(10) ash_Secs
from dba_hist_Active_Sess_history h
where sql_id != top_level_sql_id
group by top_level_sql_id, module, action
order by ash_Secs desc;
/
with x as (
select sql_id, sum(10) ash_Secs
from dba_hist_active_sess_history
where top_level_sql_id = 'b6usrg82hwsa3'
group by sql_id
)
select x.*
, (select sql_text from dba_hist_sqltext where sql_id = x.sql_id and rownum = 1) sql_text
from x order by ash_Secs desc;
Количество процессоров и памяти на сервере:
SET SQLFORMAT ANSICONSOLE
SET PAGESIZE 10000
SELECT
STAT_NAME,
DECODE(STAT_NAME,'PHYSICAL_MEMORY_BYTES',(ROUND(VALUE/1024/1024/1024,2))
|| ' GB','FREE_MEMORY_BYTES',(ROUND(VALUE /1024/1024/1024,2))
|| ' GB',VALUE ) VALUE
FROM
v$osstat
WHERE
stat_name IN ( 'FREE_MEMORY_BYTES', 'LOAD', 'NUM_CPUS', 'NUM_CPU_CORES',
'NUM_CPU_SOCKETS', 'PHYSICAL_MEMORY_BYTES' );
STAT_NAME VALUE
---------------------------------
NUM_CPUS 8
NUM_CPU_CORES 4
NUM_CPU_SOCKETS 1
PHYSICAL_MEMORY_BYTES 24 GB
FREE_MEMORY_BYTES 16.8 GB
Использование cpu на данный момент:
select sum(sm.cpu) "CPU",
to_char(sm.session_id) "SID",
to_char(sm.session_serial_num) "SERIAL",
p.spid "OS_PID", s.username "DB_User",
s.osuser "OS_User",
s.machine "Machine",
substr(s.module,1,20) "Module"
from v$sessmetric sm, v$session s, v$process p
where begin_time>sysdate-0.0035 and
s.paddr = p.addr(+)
and sm.session_id = s.sid
group by sm.session_id,
sm.session_serial_num,
p.spid,
s.username,
s.osuser,
s.machine,
s.module
order by cpu desc;
статистически
select st.value "DB_CPU_value",
to_char(s.sid) "Sid",
to_char(s.serial#) "Serial",
p.spid "OS_PID",
s.username "DB_User",
s.osuser "OS_User",
s.machine "Machine",
s.module "Module",
s.logon_time
from v$sess_time_model st,
v$session s, v$process p
where stat_name = 'DB CPU' and st.sid = s.sid
and s.paddr = p.addr(+) order by value desc;
