--环境,下面的演示基于Oracle 10g scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production --下面的查询awr配置 scott@CNMMBO> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- ------------------------- ---------- 938506715 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT --发布sql查询 scott@CNMMBO> select * from dept where loc='CHICAGO'; DEPTNO DNAME LOC ---------- -------------- ------------- 30 SALES CHICAGO --获得sql_id scott@CNMMBO> @my_last_sql ADDRESS HASH_VALUE SQL_ID COMMAND_TYPE PIECE SQL_TEXT ---------------- ---------- ------------- ------------ ---------- ------------------------------------------ 000000009F942760 2626775672 2jbkb5qf92ums 3 0 select * from dept where loc='CHICAGO' --从v$sql_plan获得缓冲区的执行计划 scott@CNMMBO> get sql_plan_curr.sql 1 set linesize 135 2 col id format 99 3 col operation format a25 4 col options format a25 5 col object_name format a25 wrap 6 SELECT id, 7 operation, 8 options, 9 object_name, 10 bytes, 11 cpu_cost, 12 io_cost 13 FROM v$sql_plan 14 WHERE sql_id = '&input_sql_id' 15* ORDER BY id; scott@CNMMBO> @sql_plan_curr.sql -->此时可以查询到对应sql的执行计划 Enter value for input_sql_id: 2jbkb5qf92ums ID OPERATION OPTIONS OBJECT_NAME BYTES CPU_COST IO_COST --- ------------------------- ------------------------- --------------- ------- ---------- ---------- 0 SELECT STATEMENT 1 TABLE ACCESS FULL DEPT 20 36567 3 --下面尝试从dba_hist_sql_plan获得执行计划 scott@CNMMBO> get sql_plan_his.sql 1 set linesize 135 2 col id format 99 3 col operation format a25 4 col object_name format a25 wrap 5 SELECT id, 6 operation, 7 options, 8 object_name, 9 bytes, 10 cpu_cost, 11 io_cost 12 FROM dba_hist_sql_plan 13 WHERE sql_id = '&input_sql_id' 14* ORDER BY id; scott@CNMMBO> @sql_plan_his --查询无法获得执行计划 Enter value for input_sql_id: 2jbkb5qf92ums no rows selected scott@CNMMBO> exec dbms_workload_repository.create_snapshot(); -->执行一次快照,写入缓冲区的内容倒snapsho PL/SQL procedure successfully completed. scott@CNMMBO> @sql_plan_his -->依旧无法获得执行计划 Enter value for input_sql_id: 2jbkb5qf92ums no rows selected |