如何查找当前占用cpu较高的sql

以下举例说明如何查找当前占用cpu较高的sql

1.从topas中获得占cpu较高的oracle进程的进程号：
例如：
5279778

2.在v$process视图中查找该session的地址：
SQL> select addr,pid,spid,username,terminal from v$process where spid='5279778';

ADDR                    PID SPID         USERNAME
---------------- ---------- ------------ ---------------
TERMINAL
------------------------------
0700000339663A80        411 5279778      oracle_a
UNKNOWN

 

3.在v$session 视图中查找是哪个session：
SQL> select sql_id,SID,PADDR from v$session where PADDR='0700000339663A80';

SQL_ID               SID PADDR
------------- ---------- ----------------
2yyyw5anv9jzy        812 0700000339663A80

4.通过sql_id或sql_hash_value查找sql语句的内容：
SQL> set long 999999999
SQL> select sql_fulltext from v$sql where sql_id='2yyyw5anv9jzy';

SQL_FULLTEXT
--------------------------------------------------------------------------------
select c.F2034ID as F1_46_, c.F2034KHBH as F2_46_, c.F2034ZCLY as F3_46_, c.F203
4PC as F4_46_, c.F2034CZBH as F5_46_, c.F2034FXJLX as F6_46_, c.F2034FXJLXMC as
F7_46_, c.F2034SJHSFXJ as F8_46_ from AMS.D2034FXJHS c where (c.F2034KHBH='37000
0e05668' )and(c.F2034CZBH='370000o006133' )

5.整理一下格式：
select c.F2034ID as F1_46_,
c.F2034KHBH as F2_46_,
c.F2034ZCLY as F3_46_,
c.F2034PC as F4_46_,
c.F2034CZBH as F5_46_,
c.F2034FXJLX as F6_46_,
c.F2034FXJLXMC as F7_46_,
c.F2034SJHSFXJ as F8_46_
from AMS.D2034FXJHS c where
(c.F2034KHBH='370000e05668' )and(c.F2034CZBH='370000o006133' );

 

6.查看该SQL语句的执行计划：
SQL> set autotrace traceonly;
SQL> set line 9999
SQL> select c.F2034ID as F1_46_,
  2  c.F2034KHBH as F2_46_,
  3  c.F2034ZCLY as F3_46_,
  4  c.F2034PC as F4_46_,
  5  c.F2034CZBH as F5_46_,
  6  c.F2034FXJLX as F6_46_,
  7  c.F2034FXJLXMC as F7_46_,
  8  c.F2034SJHSFXJ as F8_46_
  9  from AMS.D2034FXJHS c where
 10  (c.F2034KHBH='370000e05668' )and(c.F2034CZBH='370000o006133' );

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2998981848

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    57 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| D2034FXJHS     |     1 |    57 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_D2034_KHBH |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C"."F2034CZBH"='370000o006133')
   2 - access("C"."F2034KHBH"='370000e05668')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        771  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed