oracle常用性能分析语句总结

oralce查询当前执行耗资源的语句

select t.*, s.sid, s.serial#, s.machine, s.program, s.osuser

  from(select c.USERNAME,

              a.event,

              to_char(a.cnt)as seconds,

              a.sql_id,

              dbms_lob.substr(b.sql_fulltext,3500,1) sqltext

          from(selectrownum rn, t.*

                 from(selectdecode(s.session_state,

                                     'WAITING',

                                     s.event,

                                     'Cpu + Wait For Cpu') Event,

                              s.sql_id,

                              s.user_id,

                              count(*) CNT

                         fromv$active_session_history s

                        where sample_time >sysdate-30/1440

                        groupby s.user_id,

                                 decode(s.session_state,

                                        'WAITING',

                                        s.event,

                                        'Cpu + Wait For Cpu'),

                                 s.sql_id

                        orderby CNT desc) t

                whererownum<20) a,

              v$sqlarea b,

              dba_users c

         where a.sql_id = b.sql_id

          and a.user_id = c.user_id

         orderby CNT desc) t,

       v$session s

where t.sql_id = s.sql_id(+);

 

查询Oracle正在执行的sql语句及执行该语句的用户

SELECTb.sidoracleID,

       b.username登录Oracle用户名,

       b.serial#,

       spid操作系统ID,

       paddr,

       sql_text正在执行的SQL,

       b.machine计算机名

FROMv$processa,v$sessionb,v$sqlareac

WHEREa.addr=b.paddr

   ANDb.sql_hash_value=c.hash_value

 

--查看正在执行sql的发起者的发放程序

 

SELECTOSUSER电脑登录身份,

       PROGRAM发起请求的程序,

       USERNAME登录系统的用户名,

       SCHEMANAME,

       B.Cpu_Time花费cpu的时间,

       STATUS,

       B.SQL_TEXT执行的sql

FROMV$SESSIONA

LEFTJOINV$SQLBONA.SQL_ADDRESS=B.ADDRESS

                   ANDA.SQL_HASH_VALUE=B.HASH_VALUE

ORDERBYb.cpu_timeDESC

--查出oracle当前的被锁对象

 

SELECTl.session_idsid,

       s.serial#,

       l.locked_mode锁模式,

       l.oracle_username登录用户,

       l.os_user_name登录机器用户名,

       s.machine机器名,

       s.terminal终端用户名,

       o.object_name被锁对象名,

       s.logon_time登录数据库时间

FROMv$locked_objectl,all_objectso,v$sessions

WHEREl.object_id=o.object_id

   ANDl.session_id=s.sid

ORDERBYsid,s.serial#;

数据库等待时间查询等等

 

--查询enq和latch的数量

select * from v$session_wait where eventlike 'enq%' or event like 'lat%' or event like 'cursor: pin S%';

--查询锁表SID

select * from v$lock where block=1 ;

 

--查询死锁的源头和被锁的会话：

select s.sid blocker,substr(s.program,1,40) program, w.username, w.sid blocked from v$session s,v$session w

where w.blocking_session = s.sid andw.blocking_session_status='VALID';

 

--查看进程直接锁的关系，锁的源头

select /*+rule*/ l1.sid, ' IS BLOCKING ', l2.sidfrom v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0 andl1.id1=l2.id1 and l1.id2=l2.id2;

 

--查询sid对应的sql_id

selectstatus,machine,sql_id,prev_sql_id,client_info from v$session where sid=1105;

 

--查询完整sql

select sql_text from v$sql where sql_id='bydf32qgqdwdu';

 

 

---查询锁表

SELECT l.session_id sid, 

       s.serial#, 

       l.locked_mode, 

       l.oracle_username, 

       l.os_user_name, 

       s.machine, 

       s.terminal, 

       o.object_name, 

       s.logon_time 

  FROM v$locked_object l,all_objects o, v$session s 

 WHERE l.object_id = o.object_id 

   AND l.session_id =s.sid 

 ORDER BY sid, s.serial#; 

 

--性能相关，enq latch"cursor: pin S wait on X"

select sid,TIME_REMAINING tr,ELAPSED_SECONDSes,a.SQL_ID,a.* from v$session_longops a  where a.TIME_REMAINING>0order by a.sid;

select count(*),event from v$session_waitgroup by event;where event like 'enq%' or event like 'lat%' or event like'cursor: pin S%';

select * from v$session_wait whereevent='db file scattered read';

--到系统杀状态为killed的锁

select 'kill -9 '||b.spid||' ' fromv$session a,v$process b where a.sid=2193 and a.paddr=b.addr;

select * from v$session where sid=4864;

select * from v$session a ,v$process bwhere paddr=addr and a.sid=4864;

select * from v$lock where block=1 order bysid;

select * from v$locked_object;

select username,sid,serial#,logon_time fromv$locked_object,v$session where v$locked_object.session_id=v$session.sid;

select * from v$session where sid in

  (select sid from v$session_waitwhere event like 'enq%' or event like 'lat%' or event like 'cursor: pin S%');

--查看具体sql

select * from v$sql wheresql_id='8q4uycb97mcpa'

select sql_text,sql_fulltext,t2.client_info,t2.program,t2.SQL_ID, t2.sid, t2.event, t2.sql_hash_value,t2.sql_id,t2.username,t2.wait_class,t2.p1, t2.p2, t2.p3, t2.state

  from v$sqlstats t1 inner joinv$session t2 on t1.SQL_ID = t2.sql_id

  inner join v$session_wait t3 ont2.sid = t3.sid where t3.wait_class <> 'Idle'; 

--查看具体session等待问题

selectusername,sid,client_info,sql_id,machine,logon_time,event

from v$session

where status='ACTIVE'

      and usernameis not null

      and eventlike 'lat%'

      or event like 'enq%'

      or event like 'cursor: pin S%';

--查询process和session：

select * from v$resource_limit;

--查出长时间操作的语句

select * from v$session_longops wheretime_remaining>0;

select a.* from v$sql a,v$session b wherea.sql_id=b.sql_id and b.sid=262;

select/*+rule*/ username,sid,opname,

      round(sofar*100/ totalwork,0) || '%' as progress,

      time_remaining,sql_text

from v$session_longops , v$sql

where time_remaining <> 0 andsql_address = address and sql_hash_value = hash_value;

--查询锁问题

--查看锁sid和被锁sid

select s.sid blocker,substr(s.program,1,40) program, w.username, w.sid blocked from v$session s,v$session w

  where w.blocking_session =s.sid and w.blocking_session_status='VALID' order by 1,4;

--查询被锁原因

SELECTo.owner,o.object_name,s.sid,s.serial#,l.locked_mode,s.MACHINE,s.SQL_ID,s.PREV_SQL_ID,

  'ALTER SYSTEM KILL SESSION'''||s.sid||','||s.serial#||''' immediate;'

  FROM v$locked_object l, dba_objectso, v$session s

  WHERE l.object_id = o.object_id ANDl.session_id = s.sid

/*  and owner='AHTFM'*/

  and s.sid in (3827,3714)

order by s.sid;

select * from v$session where sidin(3827,3714) ;

--查看sqltext

select sq.sql_text from v$sql sq ,v$sessionse where sq.sql_id = se.sql_id and sid=2057;

select * from v$sql wheresql_id='b20vhuaqgdtz9' or sql_id='a8566shvpf62v';  a8566shvpf62v

select a.spid,b.sid,b.serial#,b.usernamefrom v$process a,v$session b where a.addr=b.paddr and b.sid=388;

-------oracle 查看已经执行过的sql 这些是存在共享池中的--------->

select * from v$sqlarea t order byt.LAST_ACTIVE_TIME desc;

-------------查看oracle的权限角色------------------------------>

select   *  from   dba_role_privs;    授予用户和其他角色的角色 

select   *  from   dba_sys_privs;     授予用户和其他角色的系统权限 

select   *  from   dba_tab_privs;     数据库中对象的所有授权

select * fromuser_role_privs;         查看当前用户的角色

--查询哪台机子引起的锁

SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,

l.os_user_name,s.machine, s.terminal,o.object_name, s.logon_time,s.client_info

FROM v$locked_object l, all_objects o,v$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid

and l.session_id=2764

ORDER BY sid, s.serial# ;

--根据操作系统top进程的PID找到相应的session和正在执行的sql

--有时是没有显示的，说明是系统运行的sql(此时sql_address为00，sql_hash_value为0，SQL_ID为null)，若是用户运行的sql是有显示的

selectb.spid,a.sid,a.username,s.hash_value,s.sql_text

from v$session a , v$process b, v$sqlarea s

where a.PADDR = b.ADDR and a.sql_hash_value= s.hash_value and b.spid=&PID;

--根据操作系统top进程的PID找到相应的session和该session执行过的sql

selectb.spid,a.sid,a.username,c.hash_value,c.SQL_ID,c.sql_text

from v$session a,v$process b,v$open_cursorc

where  a.sid=c.sid

and b.ADDR=a.PADDR

and b.spid=&PID;

--查看临时表空间占用语句

select se.username,

      se.sid,

      su.extents,

      su.blocks * to_number(rtrim(p.value)) as Space,

      tablespace,

      segtype,

      sql_text

  from v$sort_usage su, v$parameter p,v$session se, v$sql s

where p.name = 'db_block_size'

   and su.session_addr = se.saddr

   and s.hash_value = su.sqlhash

   and s.address = su.sqladdr

order by se.username, se.sid;

--------------------

SELECT S.sid,'alter system kill session'''||S.sid || ',' || S.serial#||''';' sid_serial, S.username,s.status,

T.blocks * TBS.block_size / 1024 / 1024mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlareaQ, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

 ORDER BY mb_used desc;

--停掉长时间运行的job

select * from dba_jobs_running;

select b.SID,b.SERIAL#,c.SPID

from dba_jobs_running a,v$sessionb,v$process c

where a.sid = b.sid and b.PADDR = c.ADDR;

EXEC DBMS_JOB.BROKEN(18,TRUE);

 

 

 

 

查看oracle数据库最近执行了哪些sql语句

SELECTb.sql_text,                                           --content of SQL

                 a.machine,                    --which machine run this code

                           a.username, a.module,

                                                -- the method to run this SQL

                                                c.sofar / totalwork * 100,

                                                           --conplete percent

      c.elapsed_seconds,                                          --run time

                        c.time_remaining                --remain to run time

  FROM v$session a, v$sqlarea b,v$session_longops c

WHERE a.sql_hash_value = b.hash_value(+)AND a.SID = c.SID(+)

       ANDa.serial# = c.serial#(+)

       --ANDa.sid=139

 

 

 

SELECT  sql_text, last_load_time

    FROM v$sql

   WHERE last_load_time ISNOTNULL

ORDERBY last_load_time DESC

 

 

 

----监控concurrent 正在执行的sql --

SELECT a.sid, a.serial#, b.sql_text

  FROM v$session a, v$sqltext b

 WHERE a.sql_address = b.address

  --AND a.sid = <...>

 ORDER BY b.piece

 

查询oracle效率底下的语句

v$sqltext：存储的是完整的SQL,SQL被分割

v$sqlarea：存储的SQL 和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息（统计）

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）

select opname, target, to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, elapsed_seconds elapsed, 

executions execs, buffer_gets/decode(executions, 0, 1, executions) bufgets, module, sql_text

from v$session_longops sl, v$sqlarea sa

where sl.sql_hash_value = sa.hash_value

and upper(substr(module, 1, 4)) <> 'RMAN'

and substr(opname, 1, 4 ) <> 'RMAN'

and sl.start_time > trunc(sysdate)

 

order by start_time;

 

根据sid查找完整sql语句：

select sql_text from v$sqltext a wherea.hash_value = (select sql_hash_value from v$session b where b.sid ='&sid'    )
order by piece asc

 

select a.CPU_TIME,--CPU时间百万分之一（微秒）
       a.OPTIMIZER_MODE,--优化方式
       a.EXECUTIONS,--执行次数
       a.DISK_READS,--读盘次数
       a.SHARABLE_MEM,--占用shared pool的内存多少
       a.BUFFER_GETS,--读取缓冲区的次数
       a.COMMAND_TYPE,--命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)
       a.SQL_TEXT,--Sql语句
       a.SHARABLE_MEM,
       a.PERSISTENT_MEM,
       a.RUNTIME_MEM,
       a.PARSE_CALLS,
       a.DISK_READS,
       a.DIRECT_WRITES,
       a.CONCURRENCY_WAIT_TIME,
       a.USER_IO_WAIT_TIME
  from SYS.V_$SQLAREA a
 WHERE PARSING_SCHEMA_NAME = 'CHEA_FILL'--表空间
 order by a.CPU_TIME desc

 

引用：http://jenniferok.iteye.com/blog/700985

从V$SQLAREA中查询最占用资源的查询

select b.username username,a.disk_reads reads, 
    a.executionsexec,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 > 100000 
order by a.disk_reads desc;

用buffer_gets列来替换disk_reads列可以得到占用最多内存的sql语句的相关信息。

 

v$sql：内存共享SQL区域中已经解析的SQL语句。（即时）

列出使用频率最高的5个查询：

select sql_text,executions 
from (select sql_text,executions, 
   rank() over 
    (order by executions desc) exec_rank 
   from v$sql) 
where exec_rank <=5;

消耗磁盘读取最多的sql top5：
selectdisk_reads,sql_text 
from (select sql_text,disk_reads, 
   dense_rank() over 
     (order by disk_reads desc) disk_reads_rank 
   from v$sql) 
where disk_reads_rank <=5;

找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text 
from (select sql_text,buffer_gets, 
   dense_rank() over 
     (order by buffer_gets desc)buffer_gets_rank 
   from v$sql) 
where buffer_gets_rank<=5;