Oracle SQL性能以及监控脚本
一、执行计划
预估的执行计划:
1、explain plan for获取；
SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
或者：
SQL> select * from table(dbms_xplan.display);
2、 SET ATUOTRACE TRACEONLY获取
实际的执行计划：
1、根据游标
(1)运行sql
(2)获取sql_id
SELECT SQL_ID,CHILD_NUMBER FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT ENAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO =D.DEPTNO AND D.LOC=''CHICAGO''%';
SQL_ID CHILD_NUMBER
------------- ------------
7wga0v6nhkjug 0
(3)最后查看库缓存（library cache）中的实际执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('5pcq8qmf42g3g',0));
另外还可以通过gather_plan_statistics获取更多执行计划信息
select /*+gather_plan_statistics*/id,filename from T_SHARE_FILE where
id='20150402141815000808'
select * from table(dbms_xplan.display_cursor(sql_id => '5pcq8qmf42g3g',format => 'ALLSTATS LAST'));
最后通过如下sql获取当前会话最后一条执行计划
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,format => 'ALLSTATS LAST'));
备注：
/+gather_plan_statistics/会记录每一步操作中真实返回的行数（A-ROWS）、逻辑读（buffers），和消耗的时间（A-TIME)，预估返回的行数E-Rows
二、10046 trace跟踪
三、awrsqrpt.sql
四、set autotrace on ；
备注：statistics_level=all或者gather_plan_statistics提示可以获得执行计划中实时的统计信息
—从执行计划中获取绑定变量值
SQL>
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));
二、AWR、负载、监控
—从awr获取绑定变量历史值
select name,value_string,last_captured From dba_hist_SQLbind Where SQL_id='' Order by last_captured desc
--and snap_id='20433';
—从快照中查找某条sql执行效率（逻辑读、物理读、执行次数、快照）
select *
from (select BEGIN_INTERVAL_TIME,
end_INTERVAL_TIME,
a.instance_number,
plan_hash_value,
EXECUTIONS_DELTA exec,
round(BUFFER_GETS_DELTA / EXECUTIONS_DELTA) per_get,
round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 1) per_rows,
round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000000, 2) time_s,
round(DISK_READS_DELTA / EXECUTIONS_DELTA, 2) per_read
from dba_hist_SQLstat a, DBA_HIST_SNAPSHOT b
where a.snap_id = b.snap_id
and EXECUTIONS_DELTA <> 0
and a.instance_number = b.instance_number
and a.SQL_id = 'g2zth4nnmqzuf'
order by 1 desc)
where rownum < 30;
–查看数据分布
select id,count(*) from test where id in ('1','2') group by id;
–查看当前（或最近）等待事件最多的事件 按照事件种类统计次数
select event,count(1) from v$session_wait group by event order by 2 desc;
----------查找当前根据确定事件查对应的sql以及具体oracle用户
select sid,
s.username,
s.program,
s.action,
s.machine,
s.process,
s.port,
logon_time,
q.sql_text,
q.SQL_FULLTEXT,
q.sql_id
from gv$session s
left join gv$sql q on s.sql_hash_value = q.hash_value
where s.sid in (select sid
from v$session_wait
where event in ('SQL*Net message from client')) --and s.LOGON_TIME >= sysdate-10/1440 查看10min内的
---------查找历史根据确定等待事件查对应SQL以及事件次数统计
select b.* ,a.sql_fulltext from v$sqlarea a,
(select
count(*),
sql_id
from v$active_session_history ash
where event ='enq: TX - row lock contention' --and ash.sample_time >= sysdate-10/1440 --10min内的事件
--and ash.sample_time between to_date('2018-08-15 13:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-08-15 14:00','yyyy-mm-dd hh24:mi:ss')
group by sql_id
order by count(*) desc ) b
where a.sql_id=b.sql_id;
------根据sid查看正在执行的SQL
select machine,status,program,sql_text from v$session a,v$sqlarea b where a.sql_address=b.address and a.sid=&sid;
----查看当前等待事件以及sql语句
select A.sid,
A.serial#,
A.event,
A.status,
A.sql_id,
(select sql_text from v$sqlarea where sql_id = A.SQL_ID) AS sql_text
from gv$session A, gv$process B
where A.PADDR = B.ADDR
and status = 'ACTIVE'
and A.sql_id is not null;
–查询oracle负载
SELECT *
FROM ( SELECT A.SNAP_ID,
A.INSTANCE_NUMBER,
B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
B.END_INTERVAL_TIME + 0 END_TIME,
ROUND(VALUE - LAG( VALUE, 1 , '0')
OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
FROM (SELECT B.SNAP_ID,
INSTANCE_NUMBER,
SUM(VALUE ) / 1000000 / 60 VALUE
FROM DBA_HIST_SYS_TIME_MODEL B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
DBA_HIST_SNAPSHOT B
WHERE A.SNAP_ID = B.SNAP_ID
AND B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') >= TO_CHAR(SYSDATE-7, 'YYYY-MM-DD')
ORDER BY BEGIN_TIME DESC;
----根据快照号来查看特定时间的等待事件
SELECT EVENT,
WAITS,
TIME,
DECODE(WAITS,
NULL,
TO_NUMBER(NULL),
0,
TO_NUMBER(NULL),
TIME / WAITS * 1000) AVGWT,
PCTWTT,
WAIT_CLASS
FROM (SELECT EVENT, WAITS, TIME, PCTWTT, WAIT_CLASS
FROM (SELECT E.EVENT_NAME EVENT,
E.TOTAL_WAITS - NVL(B.TOTAL_WAITS, 0) WAITS,
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
1000000 TIME,
100 *
(E.TIME_WAITED_MICRO - NVL(B.TIME_WAITED_MICRO, 0)) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB time')) PCTWTT,
E.WAIT_CLASS WAIT_CLASS
FROM DBA_HIST_SYSTEM_EVENT B, DBA_HIST_SYSTEM_EVENT E
WHERE B.SNAP_ID(+) = &beg_snap
AND E.SNAP_ID = &end_snap
AND B.DBID(+) = &DBID
AND E.DBID = &DBID
AND B.INSTANCE_NUMBER(+) = &INST_NUM
AND E.INSTANCE_NUMBER = &INST_NUM
AND B.EVENT_ID(+) = E.EVENT_ID
AND E.TOTAL_WAITS > NVL(B.TOTAL_WAITS, 0)
AND E.WAIT_CLASS != 'Idle'
UNION ALL
SELECT 'CPU time' EVENT,
TO_NUMBER(NULL) WAITS,
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) / 1000000 TIME,
100 * ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) /
((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &L_DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB time') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB time')) PCTWTT,
NULL WAIT_CLASS
from dual
WHERE ((SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL e
WHERE e.SNAP_ID = &end_snap
AND e.DBID = &DBID
AND e.INSTANCE_NUMBER = &INST_NUM
AND e.STAT_NAME = 'DB CPU') -
(SELECT sum(value)
FROM DBA_HIST_SYS_TIME_MODEL b
WHERE b.SNAP_ID = &beg_snap
AND b.DBID = &DBID
AND b.INSTANCE_NUMBER = &INST_NUM
AND b.STAT_NAME = 'DB CPU')) > 0)
ORDER BY TIME DESC, WAITS DESC)
WHERE ROWNUM <= 5;
–根据当前游标以及sql_id查看执行计划
获取sql_id以及child_number
select sql_id,child_number,sql_text,parsing_schema_name from v$sql where sql_text like '%select count(*) from zz%'
查看执行计划
select * from table(dbms_xplan.display_cursor('657upa2bwz8rw',0,'ALL -BYTES -COST -ROWS -IOSTATS'));
–根据历史AWR以及sql_id查看执行计划
获取sql_id
select snap_id,dbid,instance_number,sql_id,PLAN_HASH_VALUE,parsing_schema_name from dba_hist_sqlstat
select * from table(dbms_xplan.display_awr('3hqvmkqka0684'));
–输出某段snap间隔sql执行计划
txt
select output
from table(dbms_workload_repository. AWR_SQL_REPORT_TEXT(:dbid,
:inst_num,
:BEG_SNAP,
:END_SNAP,
:sql_id,
:rpt_options)); --default value:0 or1
html
select output
from table(dbms_workload_repository. AWR_SQL_REPORT_HTML(:dbid,
:inst_num,
:BEG_SNAP,
:END_SNAP,
:sql_id,
:rpt_options)); --default value:0 or1
–AWR对比
select output
from table(dbms_workload_repository. AWR_DIFF_REPORT_HTML
(1499390015,
1,
6066,
6067,
1499390015,
1,
6034,
6035
)
);
FUNCTION AWR_DIFF_REPORT_HTML(
DBID1 IN NUMBER,
INST_NUM1 IN NUMBER,
BID1 IN NUMBER,
EID1 IN NUMBER,
DBID2 IN NUMBER,
INST_NUM2 IN NUMBER,
BID2 IN NUMBER,
EID2 IN NUMBER
)
RETURN AWRRPT_HTML_TYPE_TABLE
PIPELINED;
–sqlplus 输出文件
set echo off;
set feedback off;
set verify off;
set term off;
set trimspool on;
set linesize 3000;
set newpage none;
set heading off;
spool d:\awrdiff.html;
select output from table(dbms_workload_repository. AWR_DIFF_REPORT_HTML(1499390015,1,6066,6067,1499390015,1,6034,6035));
spool off;
–查询最消耗io的sql
select parsing_schema_name,sql_id,sql_text,executions,plan_hash_value from v$sql where sql_id='5ykznakx45sgv'
SELECT * FROM
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC
)
WHERE ROWNUM<30;
10046 trace
----current session trace
alter session set event '10046 trace name context forever,level 12';
alter session set events '10046 trace name context off';
select distinct(m.sid),s.serial#,p.spid,p.tracefile from v$mystat m,v$session s,v$process p where m.sid=s.sid and s.paddr=p.addr;
-----other session sid trace
1、先获取其他seesion sid,serial#
select b.sid,b.serial#,a.sql_text from v$sqlarea a,v$session b where sql_text like 'sql text%';
2、执行跟踪
开始：
exec dbms_system.set_ev(1371,17,10046,12,'');
执行sql statement
结束：
exec dbms_system.set_ev(1371,17,10046,0,'');
3、根据其他会话的sid查找10046trace日志
select p.tracefile from V$PROCESS p,v$session s where s.paddr=p.addr and sid=(1371);
4、格式化trace
tkprof /u02/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_20788.trc 10046_a.tkp sys=no waits=yes
可选explain=admin/oracle
----sql_id trace
ALTER SYSTEM SET EVENTS 'SQL_trace [SQL:&&SQL_id] level 12';
alter system set events 'sql_Trace [SQL:&&SQL_id] off';
上述level 12包含如下
ALTER SYSTEM SET EVENTS 'SQL_trace [SQL:&&SQL_id] bind=true, wait=true';
ALTER SYSTEM SET EVENTS 'SQL_trace [SQL:&&SQL_id] off';
132
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
