1、ORACLE实时TOP SESSION查询
with tsql as
(
select
s.sid, s.BLOCKING_SESSION lk_sid, s.machine,s.osuser, S.SQL_ID, Q.CHILD_NUMBER,
S.EVENT, Q.EXECUTIONS EXECS,
round(Q.ELAPSED_TIME / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS) / 1000 / 1000,3) ELTM,
round(Q.BUFFER_GETS / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) buffget,
round(Q.CPU_TIME / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) CPUTM,
round(Q.DISK_READS / decode(Q.EXECUTIONS,0,1,Q.EXECUTIONS),2) DISKRDS,
case when s.lockwait is not null then substr(s.p2raw,-8)||substr(s.p3raw,-8) end lk_xid
,Q.LAST_ACTIVE_TIME,Q.SQL_TEXT
from v$session s, v$sql q where s.STATUS = 'ACTIVE'
AND S.SQL_ID = Q.SQL_ID
AND S.SQL_CHILD_NUMBER = Q.CHILD_NUMBER
)
select * from
(
select 'cpu' item,rownum sn,a.* from
(
select
sid, lk_sid, machine, osuser, SQL_ID, CHILD_NUMBER, EVENT, EXECS,
ELTM, buffget, CPUTM, DISKRDS, lk_xid, LAST_ACTIVE_TIME,SQL_TEXT
from tsql order by CPUTM desc
)a
) where sn <=10
union all
select * from
(
select 'ram' item,rownum sn,a.* from
(
select
sid, lk_sid , machine, osuser, SQL_ID, CHILD_NUMBER, EVENT, EXECS,
ELTM, buffget, CPUTM, DISKRDS, lk_xid, LAST_ACTIVE_TIME,SQL_TEXT
from tsql order by buffget desc
)a
) where sn <=10
union all
select * from
(
select 'i/o' item,rownum sn,a.* from
(
select
sid, lk_sid , machine, osuser, SQL_ID, CHILD_NUMBER, EVENT, EXECS,
ELTM, buffget, CPUTM, DISKRDS, lk_xid, LAST_ACTIVE_TIME,SQL_TEXT
from tsql order by DISKRDS desc
)a
) where sn <=10
- 查詢RMAN備份信息
SELECT t.COMMAND_ID as "备份名",t.STATUS as "狀態",t.START_TIME as "開始時間",t.TIME_TAKEN_DISPLAY as "所用時間",t.END_TIME as "結束時間"
,t.INPUT_TYPE as "類型",t.OUTPUT_DEVICE_TYPE as "輸出設備",t.INPUT_BYTES_DISPLAY as "輸入大小"
,t.OUTPUT_BYTES_DISPLAY as "輸出大小",t.OUTPUT_BYTES_PER_SEC_DISPLAY as "輸出速率(每秒)"
FROM V$RMAN_BACKUP_JOB_DETAILS t where START_TIME>=trunc(sysdate)-7 ORDER BY START_TIME DESC;
- 近5分鐘SQL 活躍度查詢
select
round(100*(count/sum(count) over ()),2) as "PCT",
sql_id as "SQL ID",
sql_opname as "SQL Type"
from (
SELECT sql_id, sql_opname, count(*) count
FROM v$active_session_history
where sql_id is not null
and (sysdate - cast(sample_time as date))* 24 * 60 < = 5
group by sql_id, sql_opname
)
order by 1 desc
- TOP 會話
[oracle@DB01 ~]$ sql SAS/SAS123SAS456@127.0.0.1:1521/MPSAS_UAT;
SQL> SET SQLFORMAT ansiconsole
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
c.value AS &1,
a.lockwait,
a.status,
a.module,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
ORDER BY c.value DESC;
Enter value for 1: EXECS
Enter value for 1: EXECS
USERNAME OSUSER SID SERIAL# EXECS LOCKWAIT STATUS MODULE MACHINE PROGRAM LOGON_TIME
(oracle) oracle 2 13829 118965523 ACTIVE DB01 oracle@DB01 (RSM0) 07-SEP-2019 13:13:51
(oracle) oracle 390 40531 39243652 ACTIVE DB01 oracle@DB01 (MMON) 07-SEP-2019 13:13:35
(oracle) oracle 1158 46732 10021323 ACTIVE DB01 oracle@DB01 (CJQ0) 07-SEP-2019 13:13:59
(oracle) oracle 1153 26136 2433878 ACTIVE DB01 oracle@DB01 (DMON) 07-SEP-2019 13:13:35
(oracle) oracle 1150 1597 71196 ACTIVE DB01 oracle@DB01 (SMON) 07-SEP-2019 13:13:35
(oracle) oracle 23 59132 39206 ACTIVE Streams DB01 oracle@DB01 (Q008) 07-SEP-2019 13:14:03
(oracle) oracle 769 22070 25888 ACTIVE DB01 oracle@DB01 (RECO) 07-SEP-2019 13:13:35
(oracle) oracle 8 36460 20880 ACTIVE DB01 oracle@DB01 (MMNL) 07-SEP-2019 13:13:35
(oracle) oracle 764 52351 7429 ACTIVE DB01 oracle@DB01 (GEN0) 07-SEP-2019 13:13:35
(oracle) oracle 766 59233 3294 ACTIVE DB01 oracle@DB01 (DBRM) 07-SEP-2019 13:13:35
SAS mppay 406 33918 1268 INACTIVE JDBC Thin Client sas1 JDBC Thin Client 22-NOV-2019 14:07:48
SAS mppay 403 63413 1074 INACTIVE JDBC Thin Client sas2 JDBC Thin Client 22-NOV-2019 14:15:45
(oracle) oracle 436 56305 676 ACTIVE KTSJ DB01 oracle@DB01 (W003) 21-NOV-2019 19:05:32
SAS Alvinkam 1177 58517 658 INACTIVE PL/SQL Developer WORKGROUP\SH-PC plsqldev.exe 22-NOV-2019 11:39:39
TOP SQL
SQL>
SELECT *
FROM (SELECT Substr(a.sql_text,1,50) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address,
a.SQL_ID
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= &1;
Enter value for 1: 10
SQL_TEXT READS_PER_EXECUTION BUFFER_GETS DISK_READS EXECUTIONS SORTS ADDRESS SQL_ID
UPDATE RPT_VMPOP_I7_MERCHANT T SET T.TRANS_CHANNEL 17842 724025 17842 1 0 00000002CF0F0FF0 6atw83s0wazta
UPDATE RPT_VMPOP_I7_FINANCE T SET T.TRANS_CHANNEL 7492 405441 7492 1 0 000000010B0719D0 0cdsknyntc5wt
UPDATE ABS_ORDER_BIZ_INF T SET T.CHANNEL_NO = DECO 1130 5219 1130 1 0 00000000B13A2050 f4jdrmfxujata
UPDATE CSS_RECON_RESULT_HIS T SET T.PAYCHANNEL_NO 805 1502 805 1 0 00000001D9552B58 ab4amdjavt2bh
call dbms_stats.gather_database_stats_job_proc ( 551 377658388 582927 1057 0 000000010ADB9630 b6usrg82hwsa3
select count(1) from (SELECT TO_CHAR(a.rec 194 2994 194 1 0 000000027063B100 fn95gqy87s8s7
delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_samplin 127 208970 1023 8 0 0000000071FDB628 1j6tnz8fcm4c3
call dbms_space.auto_space_advisor_job_proc ( ) 45 2140476989 47874 1057 0 00000000D3A81278 6mcpb06rctk0x
select s.synonym_name as object_name, o.object_ty 30 158701 61 2 1664 00000001D94F82F8 5jvf84zg4c49n
select * from ( SELECT to_char(a.work_date 27 94 27 1 1 00000002CD21D208 fauv3rf864kt6
重做日誌大小評估
SELECT
(
SELECT
ROUND(AVG(BYTES)/1024/1024,2) FROM V$LOG) AS "當前重做日誌大小(MB)"
,ROUND((20/AVERAGE_PERIOD) * (SELECT AVG(BYTES) FROM V$LOG)/1024/1024,2) AS "推建重做日誌大小(MB)"
FROM
(
SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
FROM V$ARCHIVED_LOG WHERE FIRST_TIME > trunc(SYSDATE)-3
);
image.png
5.IO & 內存消耗排名
#IO
select b.username,a.DISK_READS as reads
,a.EXECUTIONS as exec
,trunc(a.DISK_READS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS),2) as rds_exec_ratio
,a.SQL_TEXT as Statement
from v$sqlarea a,dba_users b where a.PARSING_USER_ID=b.user_id
and a.DISK_READS>100000
order by a.DISK_READS desc;
#內存
select b.username,a.BUFFER_GETS as reads
,a.EXECUTIONS as exec
,trunc(a.BUFFER_GETS/decode(a.EXECUTIONS,0,1,a.EXECUTIONS),2) as rds_exec_ratio
,a.SQL_TEXT as Statement
from v$sqlarea a,dba_users b where a.PARSING_USER_ID=b.user_id
and a.BUFFER_GETS>100000
order by a.DISK_READS desc;
6.通过v$sql查询最占用资源的sql (其中 address 换成sql_text 可以插叙具体sql)
select * from
(select sql_id,address, rank() over(order by buffer_gets desc) as rank_bufgets
,to_char(100*ratio_to_report(buffer_gets) over(),'999.99') pct_bufgets
from v$sql
)
where rank_bufgets<=10;
7.从V$SESSMETRIC 视图中查出当前最占用资源的会话
select to_char(m.END_TIME,'yyyy-mm-dd hh24:mi:ss') as e_dttm, -- Interval End Time
m.INTSIZE_CSEC/100 ints, -- Interval size in sec
s.USERNAME as usr,
m.SESSION_ID sid,
m.SESSION_SERIAL_NUM as ssn,
round(m.cpu) as cpu100, --cpu usage 100th sec
m.PHYSICAL_READS as prds, --Number of physical reads
m.LOGICAL_READS as lrds, --Number of logical reads
m.PGA_MEMORY as pga, --pga size at end of interval
m.HARD_PARSES as hp,
m.SOFT_PARSES as sp,
m.PHYSICAL_READ_PCT as prp,
m."LOGICAL_READ_PCT" as lrp,
s.SQL_ID
from v$sessmetric m,v$session s
where (m.PHYSICAL_READS > 10 or m.CPU > 10 or m.LOGICAL_READS >10)
and m.SESSION_ID=s.SID and m.SESSION_SERIAL_NUM = s.SERIAL#
order by m.PHYSICAL_READS desc,m.CPU desc,m.LOGICAL_READS desc;
8.查看可用的AWR快照
select snap_id
,to_char(t.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') as b_dttm
,to_char(t.end_interval_time,'yyyy-mm-dd hh24:mi:ss') as e_dttm
from dba_hist_snapshot t where t.begin_interval_time>trunc(sysdate);
9.從 dba_hist_sqlstat視圖中發現最糟糕的SQL
select t.snap_id,t.sql_id,t.disk_reads_delta as reads_delta
,t.executions_delta as exec_delta
,trunc(t.disk_reads_delta / decode(t.executions_delta,0,1,t.executions_delta),2) as rds_exec_ratio
from dba_hist_sqlstat t
where t.disk_reads_delta >100000
order by t.disk_reads_delta;
#通過sql_id 查詢sql文本
select command_type,sql_text
from dba_hist_sqltext t where t.sql_id='30cp09r373xng';
#通過sql_id查詢執行計劃
select * from table(dbms_xplan.display_awr('30cp09r373xng'));
10.DBA_HIST_SQLSTAT (比如查看某条sql语句的性能历史)
select ss.instance_number node,
to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') as "開始時間",
to_char(END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss') as "結束時間",
sql_id,
plan_hash_value,
nvl(executions_delta, 0) as "执行次数",
trunc(elapsed_time_delta / 1000000) as "执行时间(秒)",
trunc((elapsed_time_delta / decode(nvl(executions_delta, 0), 0, 1, executions_delta)) / 1000) as "平均执行时间(毫秒)",
buffer_gets_delta as "逻辑读",
trunc((buffer_gets_delta / decode(nvl(buffer_gets_delta, 0), 0, 1, executions_delta))) as "平均逻辑读"
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '9xu89rhmakhv8'--问题sql语句的sql_id值
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and s.instance_number=1
and executions_delta > 0
order by NODE,BEGIN_INTERVAL_TIME
11.最近7天，指定时间段（8:00-16:00）最消耗CPU的10条语句
select a.*,b.sql_text
from
(
select * from
(
select
s.SQL_ID,
trunc(sum(s.CPU_TIME_DELTA)/1000000) as CPU_TIME_DELTA_SUM,
sum(nvl(executions_delta, 0)) as "执行次数",
sum(s.DISK_READS_DELTA) as DISK_READS_DELTA_SUM,
count(*) as icount
from DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT p
where 1=1
and s.SNAP_ID = p.SNAP_ID
and EXTRACT(HOUR FROM p.END_INTERVAL_TIME) between 8 and 16
and p.END_INTERVAL_TIME between SYSDATE-7 and SYSDATE
group by s.SQL_ID
order by sum(s.CPU_TIME_DELTA) desc
)
where rownum < 11
) a left join DBA_HIST_SQLTEXT b
on(a.SQL_ID = b.sql_id and b.COMMAND_TYPE != 47) -- != 47 Exclude PL/SQL blocks from output
order by 2 desc
网友评论