脚本之查询曾经在某个表上执行过的所有SQL

查询运行在某个表上的所有SQL

我们在查询和分析时候常会要查看某个表上运行的所有SQL，这里就简要举三个方法，其实就是对三个动态性能表的熟练查询。

申明：这里说的所有SQL指的是存在于v$sql中还没有被age out（Ps：老化移出）出去的SQL。

第一种方法最简单，也最不准确，就是直接查询sql_text：

select * from v$sql where lower(sql_text) like ‘%TABLE_NAME%’

最不准确是因为：

1. table_name可能会折行，like就无法匹配。

2. table_name可能同名，但是owner不一样。

3. 如果用户查询的是view或者synonym，而SQL语句中没有真实的表名。

使用这种方法主要是在当你要查询某个已知SQL的统计信息的时候。

第二种方法是通过查询v$sql_plan：

select * from v$sql where hash_value in (select hash_value from v$sql_plan where object_owner=’xxx’ and object_name=’TABLE_NAME’);

SQL被分析后，执行计划会被存储在v$sql_plan中，object_name就是执行计划里面的name那一列。So......此法可避免上面的三种情况。

但是此法也有个问题，就是当SQL执行计划中没有查询表的时候，SQL不会被显示。

例：下面SQL的执行计划中没有表名，只有索引名。

SYS@XFAN: SQL> explain plan for select * from test where x=1;

Explained.

SYS@XFAN: SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT

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

Plan hash value: 1416057887

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

| Id  | Operation                   | Name     |

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

|   0 | SELECT STATEMENT   |                 |

|*  1 |  INDEX RANGE SCAN | TEST_IDX |

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

这时候查询表名是得不到该SQL的，必须查询索引名字。所以须将表名和索引名都加到object_name中：

select * from v$sql where hash_value in (select hash_value from v$sql_plan where

object_owner=’xxx’ and object_name in (‘TABLE_NAME’,'INDEX1_NAME’,'INDEX2_NAME’,…));

另外这种方法也可以用于查询哪些SQL使用了该索引。

第三种方法是查询 v$object_dependency表：

select * from v$sql where hash_value in (select FROM_HASH from v$object_dependency where TO_OWNER=’table owner’ and TO_NAME=’table name’);

此法是比较准确的，即使SQL中使用了view或者synonym，还是可以找到SQL。但它不支持第二种的索引查询，dependency关系只是和表有关。

关于作者

Oracle

觉人之诈，不愤于言； 受人之侮，不动于色； 察人之过，不扬于他； 施人之惠，不记于心。