全表扫描SQL捕获

今天在观察系统里全表扫描的SQL的过程中，发现了X$KSULOP，记录一下。

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

 

-- 1.查看全表扫描对象的次数

SQL> select  l.TARGET, count(1)
  2    from gv$session_longops l
  3    where l.TARGET is not null
  4      and l.OPNAME not like'RMAN%'
  5   group by  l.TARGET
  6   order by  2 desc;

 

--2.查看全表扫描对象的相关信息

SQL> select username,
  2         sid,
  3         serial#,
  4         start_time,
  5         last_update_time,
  6        ( last_update_time -start_time)*86400 "run_time ",
  7         sql_address,
  8         sql_hash_value,
  9         sql_id
 10    from gv$session_longops l
 11   where TARGET = 'CAICPROD.PRPJINVOICELOAN'
 12   order by last_update_time desc;

 

 

3.根据sql_address，sql_hash_value 找到相关的sql。

SQL>  select
  2    a.INST_ID,
  3    a.SQL_TEXT
  4     from gv$sqltext a
  5    where a.HASH_VALUE = '3583241227'
  6      and a.ADDRESS = '948mau3at7v0b'
  7    ORDER BY address, hash_value, piece;

 

 

-- 查看执行计划

SQL> set autotrace  trace exp;

SQL> select  l.TARGET, count(1)
  2    from gv$session_longops l
  3    where l.TARGET is not null
  4      and l.OPNAME not like'RMAN%'
  5   group by  l.TARGET
  6   order by  2 desc;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |    68 |     1 (100)|
|   1 |  SORT ORDER BY     |          |     1 |    68 |     1 (100)|
|   2 |   HASH GROUP BY    |          |     1 |    68 |     1 (100)|
|   3 |    FIXED TABLE FULL| X$KSULOP |     1 |    68 |     0   (0)|
--------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

 

 

 

-- 看到了X$KSULOP，想去看看这个10g里x$表数量的变化，到了617个。

 

SQL> SELECT COUNT(1) FROM V$FIXED_TABLE;

  COUNT(1)
----------
      1393

 

SQL> SELECT COUNT(1) FROM v$fixed_table WHERE NAME LIKE 'X$%' ORDER BY NAME;

  COUNT(1)
----------
       617

 

 

 

SQL> SELECT NAME, OBJECT_ID, TYPE, TABLE_NUM
  2    FROM v$fixed_table
  3   WHERE NAME = 'X$KSULOP';

NAME                              OBJECT_ID       TYPE   TABLE_NUM
-----------------------------   ----------------    -------   --------------
X$KSULOP                       4294951244    TABLE               39