1 / 11

V$SQL_PLAN

V$SQL_PLAN. Introduced in Oracle 9.0.1 Shows actual execution plan in memory Enhanced in Oracle 9.2 to include Access Predicates (Joins) Filter Predicates Related views include V$SQL_PLAN_WORKAREA V$SQL_PLAN_STATISTICS V$SQL_PLAN_STATISTICS_ALL.

peers
Download Presentation

V$SQL_PLAN

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. V$SQL_PLAN • Introduced in Oracle 9.0.1 • Shows actual execution plan in memory • Enhanced in Oracle 9.2 to include • Access Predicates (Joins) • Filter Predicates • Related views include • V$SQL_PLAN_WORKAREA • V$SQL_PLAN_STATISTICS • V$SQL_PLAN_STATISTICS_ALL

  2. BYTES NUMBEROTHER_TAG VARCHAR(35)PARTITION_START VARCHAR2(5)PARTITION_STOP VARCHAR2(5)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(20)CPU_COST NUMBERIO_COST NUMBERTEMP_SPACE NUMBERACCESS_PREDICATES VARCHAR2(4000)FILTER_PREDICATES VARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBERQBLOCK_NAME VARCHAR2(31)REMARKS VARCHAR2(4000)BYTES NUMBEROTHER_TAG VARCHAR(35)PARTITION_START VARCHAR2(5)PARTITION_STOP VARCHAR2(5)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(20)CPU_COST NUMBERIO_COST NUMBERTEMP_SPACE NUMBERACCESS_PREDICATES VARCHAR2(4000)FILTER_PREDICATES VARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBERQBLOCK_NAME VARCHAR2(31)REMARKS VARCHAR2(4000) V$SQL_PLAN ADDRESS RAW(4)HASH_VALUE NUMBERCHILD_NUMBER NUMBEROPERATION VARCHAR2(30)OPTIONS VARCHAR2(30)OBJECT_NODE VARCHAR2(10)OBJECT# NUMBEROBJECT_OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(64)OPTIMIZER VARCHAR2(20)ID NUMBERPARENT_ID NUMBERDEPTH NUMBERPOSITION NUMBERCOST NUMBERCARDINALITY NUMBER

  3. Optimizer Environment Variables • In Oracle 10.1 and above, optimizer environment variables are externalized at : • instance level - V$SYS_OPTIMIZER_ENV • session level - V$SES_OPTIMIZER_ENV • statement level - V$SQL_OPTIMIZER_ENV • Use the values in these views when determining why execution plans differ

  4. Optimizer Environment Variables • Optimizer Environment Variable values reported by the dynamic performance views include:

  5. DBMS_XPLAN • Introduced in Oracle 9.2 • Formats PLAN_TABLE contents generated by EXPLAIN PLAN SELECT * FROM TABLE (dbms_xplan.display); • DISPLAY function parameters include • TABLE_NAME – name of plan table • STATEMENT_ID – statement ID in plan table • FORMAT – as below

  6. DBMS_XPLAN • For example explain a query EXPLAIN PLAN FOR SET STATEMENT_ID = 'STATEMENT1' FORSELECT t1.c2, t2.c2FROM t1, t2WHERE t1.c1 = t2.c1AND t1.c2 = 10; • The plan table can be queried using SELECT * FROM TABLE ( dbms_xplan.display ('PLAN_TABLE','STATEMENT1'));

  7. DBMS_XPLAN • Example output with predicates Predicate Information (identified by operation id): 1 - access("T1"."C1"="T2"."C1")2 - filter("T1"."C2"=10)

  8. DBMS_XPLAN • Parallel execution queries are automatically formatted e.g. EXPLAIN PLAN FOR SELECT /*+ ORDERED PARALLEL (t1 2) USE_MERGE (t1 t2) */ t1.c2, t2.c2FROM t1, t2WHERE t1.c1 = t2.c1AND t1.c2 = 10; • The plan table can be queried using SELECT * FROM TABLE (dbms_xplan.display);

  9. DBMS_XPLAN • Example output for parallel execution Predicate Information (identified by operation id): 3 - filter("T1"."C2"=10)4 - access("T1"."C1"="T2"."C1") filter("T1"."C1"="T2"."C1")

  10. DBMS_XPLAN • Partition pruning information can also be included e.g. for a range partitioned table CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 CHAR(50))PARTITION BY RANGE (c1)( PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (300), PARTITION p4 VALUES LESS THAN (400)); EXPLAIN PLAN FOR SELECT c2 FROM t1 WHERE c1 >= 150 AND c1 < 250; SELECT * FROM TABLE (dbms_xplan.display);

  11. DBMS_XPLAN • Example output for partition pruning Predicate Information (identified by operation id): 2 - filter("T1"."C1">=150 AND "T1"."C1"<250)

More Related