使用DBMS_XPLAN查看执行计划

使用DBMS_XPLAN查看执行计划

SQL> show user;

USER is "SCOTT"

SQL>

SQL> select count(*) from dba_objects;

  COUNT(*)

----------

     87091

SQL> create table t2 as select * from dba_objects;

Table created.

SQL>

SQL> create index idx_t2 on t2(OBJECT_ID);

Index created.

SQL> 

SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

SQL> alter session set optimizer_use_sql_plan_baselines=false;

Session altered.

SQL> alter session set optimizer_capture_sql_plan_baselines=true;

Session altered.

SQL> 

SQL> select OBJECT_ID,OBJECT_NAME from t2 where OBJECT_ID between 103 and 108;

 OBJECT_ID OBJECT_NAME

---------- --------------------------------------------------------------------------------------------------------------------------------

       103 MIGRATE$

       104 DEPENDENCY$

       105 ACCESS$

       106 I_DEPENDENCY1

       107 I_DEPENDENCY2

       108 I_ACCESS1

6 rows selected.

SQL>

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

User has no SELECT privilege on V$SESSION

SQL>

---

sys登陆

SQL> grant select on v_$session to scott;

Grant succeeded.

SQL> grant select on v_$sql_plan to scott;

Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

SQL> grant select on v_$sql to scott;

Grant succeeded.

SQL>

---

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

SQL_ID  43g5t8dnn4qcs, child number 1

-------------------------------------

select OBJECT_ID,OBJECT_NAME from t2 where OBJECT_ID between 103 and 108

Plan hash value: 2008370210

--------------------------------------------------------------------------------------

| Id  | Operation                                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |        |       |       |     3 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     6 |   180 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                    | IDX_T2 |     6 |       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 / T2@SEL$1

   2 - SEL$1 / T2@SEL$1

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      DB_VERSION('11.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OBJECT_ID"))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=103 AND "OBJECT_ID"<=108)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]

   2 - "T2".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]

45 rows selected.

SQL>