我想自己写程序获取这些信息,方便批量分析sql不知道sqlplus是怎么获取这些信息的执行计划
----------------------------------------------------------
Plan hash value: 272002086--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
7 consistent gets
3 physical reads
0 redo size
419 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----------------------------------------------------------
Plan hash value: 272002086--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
7 consistent gets
3 physical reads
0 redo size
419 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
dbms_Xplan也可以的哦。
再select * from table(dbms_xplan.display);sys@ORCL1> explain plan for select * from dual;Explained.sys@ORCL1> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
Plan hash value: 272002086--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------8 rows selected.
因为有一批sql要分析 要写程序统计 出报告sqlplus提供的返回结果是文本流,不是结构化的东西,程序很难识别但如果知道它的原理,那我也可以跟它一样到数据库里面查询库表 获取结果这样比较方便
DISPLAY Function
This table function displays the contents of the plan table.
In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included). You can apply a predicate on the specified table to select rows of the plan to display.-----------------------------
举个例子:select * from dual;1.用autotarce:
SQL> set autot traceonly;
SQL> select * from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------2.通过V$SQL_PLAN_STATISTICS_ALL查询:SQL> select sql_id,sql_text from v$sql where sql_text like 'select * from dual%';SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
a5ks9fhw2v9s1
select * from dualSQL> select sql_id,OPERATION,options from V$SQL_PLAN_STATISTICS_ALL
where sql_id='a5ks9fhw2v9s1'; 2SQL_ID OPERATION OPTIONS
------------- ------------------------------ ------------------------------
a5ks9fhw2v9s1 SELECT STATEMENT
a5ks9fhw2v9s1 TABLE ACCESS FULL