SQL性能的度量 |
您所在的位置:网站首页 › 导入vcard › SQL性能的度量 |
该工具需要访问一张特殊的表plan_table,该表用于存储执行计划,在Oracle 10g之前需要用脚本utlxplan.sql创建: 建表: @?\rdbms\admin\utlxplan.sql 建同义词: create public synonym plan_table for plan_table; 授权: grant all on plan_table to public; Oracle 10g之后不再需要创建表plan_table,而是增加了数据字典表plan_table$,然后基于plan_table$创建了公共同义词供用户使用。 explain基本语法: explain plan [set statement_id = 'stmt_id'] for sql_statement; explain指令的执行结果存储于表plan_table中,有几种方法获取执行计划的详细信息: 1、直接查询plan_table表 解释计划 explain plan for select count(*) from scott.emp; 查询结果 col id for 999 col operation for a50 col options for a20 col object_name for a20 select id, lpad(' ', 2 * depth) || operation || ' ' || options || decode(id, 0, substr(optimizer, 1, 6) || ' Cost = ' || to_char(cost)) operation, options, object_name, position from plan_table where plan_id = (select max(plan_id) from plan_table) order by id; ID OPERATION OPTIONS OBJECT_NAME POSITION ---- -------------------------------------------------- -------------------- -------------------- ---------- 0 SELECT STATEMENT ALL_RO Cost = 1 1 1 SORT AGGREGATE AGGREGATE 1 2 INDEX FULL SCAN FULL SCAN PK_EMP 1 或者用以下查询,包含了执行计划树的level层次关系 col "Execute Plan" for a100 select id || ' ' || parent_id || ' ' || lpad(' ', 2 * level - 1) || operation || ' ' || options || ' ' || object_name || ' (Cost=' || cost || ')' as "Execute Plan" from plan_table start with id = 0 connect by prior id = parent_id; Execute Plan ---------------------------------------------------------------------------------------------------- 0 SELECT STATEMENT (Cost=1) 1 0 SORT AGGREGATE (Cost=) 2 1 INDEX FULL SCAN PK_EMP (Cost=1) 2、通过程序包dbms_xplan获得执行计划 1)获得最近一次explain的执行计划 col "PLAN_TABLE_OUTPUT" for a100 select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 2937609675 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 | ------------------------------------------------------------------- 2)通过指定的语句ID来查询 select * from table(dbms_xplan.display('plan_table', 'stmt_id')); 3)通过SQL_ID和子游标来查询,该函数并不要求先做explain,显示的信息也较详细,另外还有一个format参数可以做更详细的定制。 select * from table(dbms_xplan.display_cursor('sql_id', 'child_number', 'format')); sql_id为null时显示最近一次执行的SQL的执行计划,但注意要保持set serveroutput off,否则最后一句SQL将不是你运行的SQL,child number为null,则返回所有子游标的执行计划。 可以通过在SQL语句中加入注释,方便的获取SQL_ID和CHILD_NUMBER信息,如以下SQL,先执行一次 select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2 from cmes.c_material_t m where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or regexp_like('641234HNGA080001A', m.validaterule2, 'c')) and m.deleted_flag = '0' and rownum = 1; 查出它的ID: select sql_id, child_number from v$sql where sql_text like '%12345%'; SQL_ID CHILD_NUMBER ------------- ------------ 9jk2r7a64s470 0 cc274s1r7ab6w 0 因为以上包含"12345"注释的语句被执行了两条,所以因取先执行的第一条为实际的ID。 查看执行计划: col PLAN_TABLE_OUTPUT for a100; select * from table(dbms_xplan.display_cursor('9jk2r7a64s470', 0)); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 9jk2r7a64s470, child number 0 ------------------------------------- select /*12345*/ m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2 from cmes.c_material_t m where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or regexp_like('641234HNGA080001A', m.validaterule2, 'c')) and m.deleted_flag = '0' and rownum = 1; Plan hash value: 1524529232 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | COUNT STOPKEY | | | | | | |* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 2 | 116 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - filter(("M"."DELETED_FLAG"='0' AND ( REGEXP_LIKE ('641234HNGA080001A',"M"."VALIDATERULE",'c',) 查看更详细的执行计划信息,需要先设置统计级别为ALL,否则没有A-ROWS等信息 alter session set statistics_level = all; 或者在语句级别使用HINT select /*+gather_plan_statistics*/ select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2 from cmes.c_material_t m where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or regexp_like('641234HNGA080001A', m.validaterule2, 'c')) and m.deleted_flag = '0' and rownum = 1; select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS')); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID fmu73t3umxm1r, child number 0 ------------------------------------- select m.material_id, m.material_no, m.part_no, m.material_name, m.validaterule, m.validaterule2 from cmes.c_material_t m where (regexp_like('641234HNGA080001A', m.validaterule, 'c') or regexp_like('641234HNGA080001A', m.validaterule2, 'c')) and m.deleted_flag = '0' and rownum = 1 Plan hash value: 1524529232 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------- |* 1 | COUNT STOPKEY | | 1 | | 0 |00:00:00.01 | 16 | |* 2 | TABLE ACCESS FULL| C_MATERIAL_T | 1 | 2 | 0 |00:00:00.01 | 16 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM=1) 2 - filter((( REGEXP_LIKE ('641234HNGA080001A',"M"."VALIDATERULE",'c',???) 这里E-Rows是预估的返回行数,A-Rows是实际的返回行数。 format的高级应用可以显示更多的信息,特别是可以显示绑定变量的具体值,这个非常有用。以下是推荐的使用格式 select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); allstats:iostats + memstats,iostats显示该游标累计执行的io统计信息(buffers, reads),memstats显示累计执行的pga使用信息(omem 1mem used-mem) last:仅显示最后一次执行的统计信息 advanced:显示outline、query block name、column projection等信息 peeked_binds:打印解析时使用的绑定变量 如以下操作可以显示非常详细的执行计划和绑定变量等信息 alter session set statistics_level = all; var a varchar2(20); exec :a := 'EMP'; select object_id from dba_objects where object_name = :a; select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS')); dbms_xplan程序包还有一个函数display_awr可以获取AWR报告中指定SQL_ID的执行计划 col PLAN_TABLE_OUTPUT for a100; select * from table(dbms_xplan.display_awr('9jk2r7a64s470')); 该函数获取的执行计划来自dba_hist_sql_plan视图,通过历史数据记录,甚至一些被老化的SQL执行计划仍然可以被查询到。 3、通过脚本utlxpls.sql或utlxplp.sql获得执行计划 @?\rdbms\admin\utlxpls.sql 或 @?\rdbms\admin\utlxplp.sql PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 2937609675 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| PK_EMP | 14 | 1 (0)| 00:00:01 | ------------------------------------------------------------------- 看一下这两个脚本的内容,该方法实际是调用了方法2中的程序包 get ?\rdbms\admin\utlxpls.sql ...... 41* select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial')); 42 get ?\rdbms\admin\utlxplp.sql ...... 40* select * from table(dbms_xplan.display()); 41 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |