脚本之查出library cache中所有语句的查询计划

视图V$SQL_PLAN包含了library cache中所有游标的执行计划。通过结合v$sqlarea可以查出library cache中所有语句的查询计划。先从v$sqlarea中得到语句的地址，然后在由v$sql_plan查出它的查询计划：

SQL> select lpad(' ', 2*(level-1))||operation "Operation",

  2         options "Options",

  3         decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name",

  4         substr(optimizer, 1, 6) "Optimizer"

  5   from v$sql_plan a

  6  start with address = 'C0000000FCCDEDA0'

  7  and id = 0

  8  connect by prior id = a.parent_id

  9  and prior a.address = a.address

 10  and prior a.hash_value = a.hash_value;

Operation           Options              Object Name          Optimizer

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

SELECT STATEMENT                         Cost=0               CHOOSE

  NESTED LOOPS 

    INDEX           RANGE SCAN           CSS_BL_CNTR_IDX1     ANALYZ

    INDEX           RANGE SCAN           CSS_BKG_BL_ASSN_UQ1  ANALYZ

SQL>