正文
优化SQL集一
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
-->寻找低效的SQL语句 ,下面的语句主要适用于从视图v$sqlarea中获得当前运行下且耗用buffer_gets较多的SQL语句
SELECT executions
, disk_reads
, buffer_gets
, ROUND( ( buffer_gets
- disk_reads )
/ buffer_gets, 2 )
hit_ratio
, ROUND( disk_reads / executions, 2 ) reads_per_run
, sql_text
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND ( buffer_gets
- disk_reads )
/ buffer_gets < 0.80
ORDER BY 4 DESC;
--查询低效的sql
SELECT EXECUTIONS, DISK_READS, BUFFER_GETS,
ROUND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS, 2) Hit_radio,
ROUND (DISK_READS/EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
1.查看总消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
last_load_time,
v.PARSING_USER_ID,
ROUND(v.ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
v.SQL_FULLTEXT,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v ) a
where elapsed_rank <= 100 and last_load_time > to_char(sysdate - 1/1440, 'YYYY-MM-DD/HH:MI:SS') order by "执行时间'S'" desc
查询最近一分钟内最慢的SQL:
select executions, cpu_time/1e6 as cpu_sec, elapsed_time/1e6 as elapsed_sec, round(elapsed_time/sqrt(executions)) as important, v.*
from v$sql v
where executions > 10 and last_load_time > to_char(sysdate - 1/1440, 'YYYY-MM-DD/HH:MI:SS')
order by important desc
2.查看CPU消耗时间最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
3.查看消耗磁盘读取最多的前10条SQL语句
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
一、查询执行最慢的sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
二、查询次数最多的 sql
select *
from(selects.SQL_TEXT,
s.EXECUTIONS"执行次数",
s.PARSING_USER_ID"用户名",
rank() over(orderbyEXECUTIONS desc) EXEC_RANK
fromv$sql s
leftjoinall_users u
onu.USER_ID = s.PARSING_USER_ID) t
whereexec_rank <= 100;
select * from v$sql_monitor where sql_text is not null;
select * from v$sql t
where t.LAST_ACTIVE_TIME>(sysdate - interval '1' MINUTE) --执行1分钟内的SQL语句
-- and t.PARSING_SCHEMA_NAME = 'LSBIKE' --数据库
-- and (t.MODULE is null or t.MODULE not like '%PL/SQL%') --不是在某些终端里的执行
-- and lower(t.SQL_TEXT) like '%select%' --查询某类SQL语句
order by t.LAST_ACTIVE_TIME desc;
--TOP SQL
select *
from (select round(nvl((sqt.elap / 1000000), to_number(null)),2) "Elapsed Time (s)",
round( nvl((sqt.cput / 1000000), to_number(null)),2) "CPU Time (s)",
sqt.exec,
round(decode(sqt.exec,
0,
to_number(null),
(sqt.elap / sqt.exec / 1000000)),2) "Elap per Exec (s)",
round((100 *
(sqt.elap / (select sum(e.value) - sum(b.value)
from dba_hist_sys_time_model b,
dba_hist_sys_time_model 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
e.stat_name = 'DB time' and
b.stat_name = 'DB time'))) ,2)norm_val,
sqt.sql_id,
decode(sqt.module, null, null, 'Module: ' || sqt.module) SqlModule,
nvl(to_nchar(SUBSTR(st.sql_text,1,2000)) , (' ** SQL Text Not Available ** ')) SqlText
from (select sql_id,
max(module) module,
sum(elapsed_time_delta) elap,
sum(cpu_time_delta) cput,
sum(executions_delta) exec
from dba_hist_sqlstat
where dbid = &dbid and
instance_number = &inst_num and
snap_id > &beg_snap and
snap_id <= &end_snap
group by sql_id) sqt,
dba_hist_sqltext st
where st.sql_id(+) = sqt.sql_id and
st.dbid(+) = &dbid
order by nvl(sqt.elap, -1) desc,
sqt.sql_id)
where rownum < 65 and
(rownum <= 10 or norm_val > 1);
--
select a.EVENT, a.SQL_ID, a.MACHINE, b.SQL_TEXT, b.SQL_FULLTEXT,b.FIRST_LOAD_TIME,b.LAST_LOAD_TIME,b.LAST_ACTIVE_TIME
from v$session a, v$sql b
where a.SQL_ID = b.SQL_ID
and a.USERNAME is not null
and a.STATUS = 'ACTIVE';
--查看正在执行的sql执行计划
--display_cursor 为第一步查出来的SQL_ID
select * from table(dbms_xplan.display_cursor('7k0dhtw1zudrw'))
--等待事件以及语句情况
select event,sql_id, mi, count(mi)
from (select substrb(event,1,30) event, sql_id, to_char(sample_time, 'yyyymmdd hh24mi') mi --,
--session_id
from dba_hist_active_sess_history
where sql_id = '5s1x1tmt570pn'
and sample_time > to_date('20190513 0940', 'yyyymmdd hh24mi')
and sample_time < to_date('20190513 1910', 'yyyymmdd hh24mi'))
group by event,sql_id, mi
order by mi ;
enq: SQ - contention
5s1x1tmt570pn SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410299 || LPAD(SEQ_BKE010.NEXTVAL, 10, '0') ) FROM DUAL
7pycct8f0sur2 SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410299 || LPAD(SEQ_AAZ217.NEXTVAL, 10, '0') ) FROM DUAL
2bxcdvtcvykcv SELECT ( TO_CHAR(SYSDATE, 'YYYYMMDDHH24miss') || 410200 || LPAD(SEQ_BKZ522.NEXTVAL, 10, '0') ) FROM DUAL
--看等待事件的
SELECT t2.sid,
t2.SERIAL#,
t1.SPID OS_PID,
t3.SQL_ID,
t2.EVENT,
t2.P1TEXT,
t2.P1,
t2.p2TEXT,
t2.P2,
t2.p3TEXT,
t2.P3,
t3.SQL_FULLTEXT
FROM v$process t1,
v$session t2,
v$sql t3
WHERE t1.ADDR = t2.PADDR
AND t2.STATUS = 'ACTIVE' -- 'INACTIVE'
and t2.SQL_ID = t3.SQL_ID
and t2.event not like 'SQL%';
select t2.sid, t2.SERIAL#, -- 库级唯一定位一个session t1.SPID OS_PID, -- 操作系统的PID t3.SQL_ID, t2.EVENT,-- SQLID和等待事件 t2.P1TEXT, t2.P1, -- 等待事件的P1信息 t2.p2TEXT, t2.P2, -- 等待事件的P2信息 t2.p3TEXT, t2.P3, -- 等待事件的P3信息 t3.SQL_FULLTEXT -- 被执行的SQL完整内容 from v$process t1, v$session t2, v$sql t3 where t1.ADDR = t2.PADDR and t2.STATUS = 'ACTIVE' -- 表示当前正在执行SQL的会话
-- and t2.STATUS = 'INACTIVE'
-- 表示当前等待执行SQL的会话
-- and t2.STATUS = 'KILLED'
-- 表示当前会话正在被杀掉，未提交事务强制回滚
and t2.SQL_ID = t3.SQL_ID; -- t2.sid, t2.SERIAL# 可以用作库级杀死会话：alter system kill session 't2.sid, t2.SERIAL#';
-- t1.SPID OS_PID 可以用作系统级杀死会话：kill -9 t1.SPID
-- t2.EVENT 和 P1、P2、P3 不同的等待事件的P1、P2和P3的信息是不尽相同的，具体要查官档
-- t3.SQL_FULLTEXT 是一个CLOB类型的字段
--1.从V$SQLAREA视图中选出最糟糕的查询
select b.username username,a.disk_reads reads,
a.executions exec,a.disk_reads / decode (a.executions, 0, 1,a.executions) rds_exec_ratio,
a.sql_text STATEMENT
from v$sqlarea a, dba_users b
where a.parsing_user_id = b.user_id
and a.disk_reads > 10000
order by a.disk_reads desc;
--2.从V$SQL视图中选出最糟糕的查询
select *
from (select address, --address替换sql_text
rank() over (order by buffer_gets desc ) as rank_bufgets,
to_char(100 * ratio_to_report(buffer_gets) over (), '999.99') pct_buf
from v$sql )
where rank_bufgets < 11;
declare
tune_task varchar2(30);
tune_sql clob;
begin
tune_task := dbms_sqltune.create_tuning_task(
sql_id => '6v864r3vc9qbc',
task_name => 'tune_test2',
description => 'Provide SQL ID'
);
end;
/
--执行DBMS_SQLTUNE并查看建议
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');
set long 10000 longchunksize 10000 linesize 150 pagesize 200
select dbms_sqltune.report_tuning_task('tune_test2') from dual;
--查看和删除调优任务
select owner,task_name,advisor_name,created from dba_advisor_tasks order by created;
exec dbms_sqltune.drop_tuning_task(task_name => '&&task_name');
