CPU사용율이 높을 때의 문제되는 SQL문장 찾는 쿼리

[ CPU사용율이 높을 때의 문제되는 SQL문장 찾는 쿼리 ] 

 

   - 해당 쿼리를 실행하기 위해서는 DBA 권한이 있어야 함

 

 

select s.sid SID

      ,s.username

      ,s.program

      ,p.spid "OS-Pid"

       ,w.seconds_in_wait as "W_time(Sec)"

       ,decode(w.wait_time, 0, 'Wai-ting', 'Waited') Status

       ,w.ename event

       ,q.sql_text

  from (select a.*

              ,decode(a.event

                     ,'latch free'

                     ,'latch free (' || b.name || ')'

                     ,'row cache lock'

                     ,'row cache lock (' || c.parameter || ')'

                     ,'enqueue'

                     ,'enqueue (' || chr(bitand(p1, -16777216) / 16777215) ||

                      chr(bitand(p1, 16711680) / 65535) || ':' ||

                      decode(bitand(p1, 65535)

                            ,1

                            ,'N'

                            ,2

                            ,'SS'

                            ,3

                            ,'SX'

                            ,4

                            ,'S'

                            ,5

                            ,'SSX'

                            ,6

                            ,'X') || ')'

                     ,a.event) ename

          from v$session_wait a, v$latchname b, v$rowcache c

         where a.p2 = b.latch#(+)

           and a.p1 = c.cache#(+)

           and c.type(+) = 'PARENT'

           and a.event not in ('rdbms ipc message'

                              ,'smon timer'

                              ,'pmon timer'

                              ,'slave wait'

                              ,'pipe get'

                              ,'null event'

                              ,'SQL*Net message from client'

                              ,'SQL*Net message to client'

                              ,'PX Idle Wait'

                              ,'PX Deq: Execution Msg'

                              ,'KXFQ: kxfqdeq - normal deqeue'

                              ,'ges remote message'

                              ,'wakeup time manager'

                              , /* idle event 적절히 수정 */'lock manager wait for remote message'

                              ,'single-task message')) w

      ,v$session s

      ,v$process p

      ,v$sql q

where w.sid = s.sid

   and s.paddr = p.addr

   and s.sql_hash_value = q.hash_value(+)

   and s.sql_address = q.address(+)

order by w.ename;