Explain Plan:
Create PLUSTRACE role:
SQL> show user
USER is "SYS"
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant plustrace to hr;
Grant succeeded.
SQL> show user
USER is "HR"
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
144 Peter Vargas
150 Peter Tucker
152 Peter Hall
3 rows selected.
SQL> explain plan for
select employee_id,first_name,last_name from employees where first_name ='Peter';
Explained.
SQL> @?/rdbms/admin/utlxpls.sql
SQL> Rem
SQL> Rem $Header: utlxpls.sql 26-feb-2002.19:49:37 bdagevil Exp $
SQL>-------
SQL> Rem
SQL> Rem Use the display table function from the dbms_xplan package to display the last
SQL> Rem explain plan. Force serial option for backward compatibility
SQL> Rem
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3375806929
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 3 (34)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 3 | 57 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX FAST FULL SCAN| EMP_NAME_IX | 3 | 57 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 3 | 57 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("FIRST_NAME"='Peter')
17 rows selected.
Auto trace options:
SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Explain plan with Trace & Statistics:
SQL> set autot on
select employee_id,first_name,last_name from employees where first_name ='Peter';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
144 Peter Vargas
150 Peter Tucker
152 Peter Hall
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 3 (34)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 3 | 57 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX FAST FULL SCAN| EMP_NAME_IX | 3 | 57 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 3 | 57 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("FIRST_NAME"='Peter')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
768 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
Only explain Plan:
SQL> set autot on exp
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
144 Peter Vargas
150 Peter Tucker
152 Peter Hall
Execution Plan
----------------------------------------------------------
Plan hash value: 3375806929
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 57 | 3 (34)| 00:00:01 |
| 1 | VIEW | index$_join$_001 | 3 | 57 | 3 (34)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX FAST FULL SCAN| EMP_NAME_IX | 3 | 57 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 3 | 57 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(ROWID=ROWID)
3 - filter("FIRST_NAME"='Peter')
Only Statistics:
SQL> set autot on stat
SQL> select employee_id,first_name,last_name from employees where first_name ='Peter';
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
144 Peter Vargas
150 Peter Tucker
152 Peter Hall
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
768 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
No comments:
Post a Comment