Home > Database > Mysql Tutorial > body text

Oracle 如何根据动态性能视图来查看某条SQL语句的执行计划

WBOY
Release: 2016-06-07 17:13:23
Original
1072 people have browsed it

Oracle 如何根据动态性能视图来查看某条SQL语句的执行计划

1、授权某用户查看 sys.v$sql_plan 的权限

2、创建类plan_table的视图      

SQL> create or replace view dynamic_plan_table
  2  as
  3  select rawtohex(address)|| '_' ||child_number statement_id,
  4  sysdate timestamp,operation,options,object_node,
  5  object_owner,object_name,0 object_instance,
  6  optimizer,search_columns,id,parent_id,position,
  7  cost,cardinality,bytes,other_tag,partition_start,
  8  partition_stop,partition_id,other,distribution,
  9  cpu_cost,io_cost,temp_space,access_predicates,
 10  filter_predicates
 11  from v$sql_plan;

视图已创建。

3、查看执行计划

SQL> select plan_table_output
  2  from table(dbms_xplan.display
  3     ('dynamic_plan_table',
  4      (select rawtohex(address)||'_'||child_number x
  5         from v$sql where sql_text=
  6         'select * from user_tables'),
  7         'serial'))
  8  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |   703 (100)|
|*  1 |  HASH JOIN                   |          |  2513 |  5700K|   703   (7)|
|   2 |   FIXED TABLE FULL           | X$KSPPCV |   100 |   196K|     0   (0)|
|   3 |   MERGE JOIN CARTESIAN       |          |  2513 |   755K|   703   (7)|
|*  4 |    HASH JOIN OUTER           |          |  2513 |   620K|   661   (1)|
|*  5 |     HASH JOIN RIGHT OUTER    |          |  2513 |   547K|   473   (2)|
|   6 |      TABLE ACCESS FULL       | USER$    |    84 |  1428 |     3   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  7 |      HASH JOIN OUTER         |          |  2513 |   505K|   470   (2)|
|*  8 |       HASH JOIN              |          |  2513 |   485K|   415   (1)|
|   9 |        TABLE ACCESS FULL     | TS$      |     7 |   133 |     4   (0)|
|* 10 |        HASH JOIN OUTER       |          |  2513 |   439K|   410   (1)|
|  11 |         NESTED LOOPS         |          |  2485 |   325K|   352   (1)|
|* 12 |          TABLE ACCESS FULL   | OBJ$     |  3142 |   110K|   236   (2)|
|* 13 |          TABLE ACCESS CLUSTER| TAB$     |     1 |    98 |     1   (0)|
|* 14 |           INDEX UNIQUE SCAN  | I_OBJ#   |     1 |       |     0   (0)|
|  15 |         TABLE ACCESS FULL    | SEG$     |  7092 |   311K|    58   (0)|
|  16 |       INDEX FAST FULL SCAN   | I_OBJ1   | 69116 |   539K|    54   (0)|
|  17 |     INDEX FAST FULL SCAN     | I_OBJ2   | 69116 |  2024K|   187   (0)|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  18 |    BUFFER SORT               |          |     1 |    55 |   516  (10)|
|* 19 |     FIXED TABLE FULL         | X$KSPPI  |     1 |    55 |     0   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
   4 - access("T"."BOBJ#"="CO"."OBJ#")
   5 - access("CX"."OWNER#"="CU"."USER#")
   7 - access("T"."DATAOBJ#"="CX"."OBJ#")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   8 - access("T"."TS#"="TS"."TS#")
  10 - access("T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#" AND
              "T"."TS#"="S"."TS#")
  12 - filter(("O"."OWNER#"=USERENV('SCHEMAID') AND
              BITAND("O"."FLAGS",128)=0))
  13 - filter(BITAND("T"."PROPERTY",1)=0)
  14 - access("O"."OBJ#"="T"."OBJ#")
  19 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - 'DYNAMIC_PLAN_TABLE' is old version

已选择45行。

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template