今天在观察系统里全表扫描的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