Oracle sql优化语句整理

–查看总消耗时间最多的前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.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;

–查看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;

–查看消耗磁盘读取最多的前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;

–查看执行计划

–配合explain plan使用 查询特定的sql的执行计划

explain plan for 
+sql
select * from table(dbms_xplan.display);

–查看刚刚执行过的SQL的执行计划

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

–只要目标SQL的执行计划所在的Child Cursor没有被age out出Shared Pool

select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select * from emp%';

–用于查看指定SQL的执行计划

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number, 'advanced'));

–VERSION_COUNT代表有几种不同的执行计划

select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like '%select * from emp%';

–查看指定SQL的所有历史执行计划,前提是该SQL的执行计划被采集到AWR Repository中

select * from table(dbms_xplan.display_awr('sql_id'));

–查看锁（lock）情况

select ls.osuser os_user_name,ls.username user_name,
decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX',
'Transaction enqueue lock','UL','User supplied lock') lock_type,
o.object_name object,decode(ls.lmode,1,null,2,'Row Share',3,
'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null)
lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2
from sys.dba_objects o,
(
  select s.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 
  from v$session s,v$lock l 
  where s.sid=l.sid
) ls
where o.object_id=ls.id1 and o.owner<>'SYS'
order by o.owner, o.object_name;

–查看被锁的表

select 
b.owner,
b.object_name,
a.session_id,
a.locked_mode 
from 
v$locked_object a,
dba_objects b 
where b.object_id = a.object_id;

–查看锁表

select 
b.owner,
b.object_name,
a.session_id,
a.locked_mode,
c.serial#,
c.sid||','||c.serial#
from v$locked_object a,dba_objects b ,v$session c
where b.object_id = a.object_id 
and a.session_id = c.sid;

–杀死锁表

alter system kill session'session_id,serial#'immediate;