优化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');