获得执行计划

来源：互联网发布：js拖动滑块实现验证码编辑：程序博客网时间：2024/05/16 14:37
参考盖国强《深入解析oracle》
2.1 v$sql_plan通过这个视图，可以获取正在执行中或者仍然缓存着的 SQL 执行计划通过 HASH_VALUE（可以通过 V$SESSION 或者 V$SQL、V$SQL_PLAN 视图获得 SQL的 HASH_VALUE）输入来获取 SQL 及其执行计划：SQL> alter session set sql_trace=true;Session altered.SQL> select * from test.worker;WNO       WNAME--------- --------------------001       adam此时不断开会话，进入udump目录，在相应trc文件中可以发现如下内容PARSING IN CURSOR #20 len=25 dep=0 uid=0 oct=3 lid=0 tim=1468637238547630 hv=3081506255 ad='a9861100'select * from test.workerEND OF STMT得到HASH_VALUE值create table t as select * from v$sql_plan where hash_value=3081506255;查看执行计划SQL> set linesize 300SQL> select operation,OBJECT_NAME,BYTES,COST from t;OPERATION                                                    OBJECT_NAME                         BYTES       COST------------------------------------------------------------ ------------------------------ ---------- ----------SELECT STATEMENT                                                                                                3TABLE ACCESS                                                 WORKER                                 33          32.2 EXPLAIN PLAN FOR 与 DBMS_XPLANEXPLAIN PLAN 命令可以在后台对 SQL 进行解析，并将 SQL 执行计划加载到执行计划表中（默认名称为 PLAN_TABLE）执行DBMS_XPLAN.DISPLAY功能需要对V$SESSION、V$SQL、V$SQL_PLAN 、V$SQL_PLAN_STATISTICS_ALL 具有访问权限，授权语句如下grant select on v_$session to scott;grant select on v_$sql_plan to scott;grant select on v_$sql to scott;SQL> explain plan for select * from test.worker;Explained.SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 435679406----------------------------------------------------------------------------| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------|   0 | SELECT STATEMENT  |        |     1 |    33 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| WORKER |     1 |    33 |     3   (0)| 00:00:01 |----------------------------------------------------------------------------Note-----PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   - dynamic sampling used for this statement再看diaplay_cursor的输出SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  7yhu6xmqzq6fm, child number 0-------------------------------------SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)Plan hash value: 2137789089-------------------------------------------------------------| Id  | Operation                         | Name    | Cost  |-------------------------------------------------------------|   0 | SELECT STATEMENT                  |         |    24 ||   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Note-----   - cpu costing is off (consider enabling it)可以通过 V$SESSION 或 V$SQL 等视图来获取不同会话的 SQL_ID 以及 SQL_CHILD_NUMBER 来获得其 SQL 执行计划某些 SQL 的执行计划可能老化无法获取SQL> select sid,username,sql_id,sql_child_number from v$session where sql_id is not null;       SID USERNAME                       SQL_ID        SQL_CHILD_NUMBER---------- ------------------------------ ------------- ----------------      1623                                4gd6b1r53yt88                0      1644 SYS                            gyhjra6q6xc8m                0      1646                                4gd6b1r53yt88                0SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('gyhjra6q6xc8m',0,'ALL'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  gyhjra6q6xc8m, child number 0-------------------------------------select sid,username,sql_id,sql_child_number from v$session where sql_idis not nullPlan hash value: 3733760267---------------------------------------------------------------------------------| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)|---------------------------------------------------------------------------------|   0 | SELECT STATEMENT         |                 |       |       |     1 (100)|PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   1 |  NESTED LOOPS            |                 |     1 |   116 |     0   (0)||*  2 |   FIXED TABLE FULL       | X$KSUSE         |     1 |   103 |     0   (0)||*  3 |   FIXED TABLE FIXED INDEX| X$KSLED (ind:2) |     1 |    13 |     0   (0)|---------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$5C160134   2 - SEL$5C160134 / S@SEL$3   3 - SEL$5C160134 / E@SEL$3PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("S"."KSUSESQI" IS NOT NULL AND              "S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND              BITAND("S"."KSUSEFLG",1)<>0))   3 - filter("S"."KSUSEOPC"="E"."INDX")Column Projection Information (identified by operation id):-----------------------------------------------------------PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   1 - "S"."INDX"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],       "S"."KSUSESQI"[VARCHAR2,13], "S"."KSUSESCH"[NUMBER,22]   2 - "S"."INDX"[NUMBER,22], "S"."INST_ID"[NUMBER,22],       "S"."KSSPAFLG"[NUMBER,22], "S"."KSUUDLNA"[VARCHAR2,30],       "S"."KSUSEFLG"[NUMBER,22], "S"."KSUSESQI"[VARCHAR2,13],       "S"."KSUSESCH"[NUMBER,22], "S"."KSUSEOPC"[NUMBER,22]   3 - "E"."INDX"[NUMBER,22]42 rows selected.2.3通过AWR获取SQL执行计划select * from table(dbms_xplan.display_awr('4gd6b1r53yt88'));通过 dbms_xplan.display_awr 函数获取的 SQL 执行计划来自 dba_hist_sql_plan 视图，通过历史数据记录，甚至一些被老化的 SQL 执行计划仍然可以被查询到
阅读全文
0 0