|
|
| Re: Query optimization Plan [message #1765 is a reply to message #1764] |
Tue, 28 May 2002 04:45  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
YOU can get it in 2 ways.
for both first create the plan_table or run $ORACLE_HOMERDBMSADMINUTLXPLAN.SQL which will create the plan_table
CREATE TABLE plan_table
(
statement_id VARCHAR2(30),
timestamp DATE,
remarks VARCHAR2(80),
operation VARCHAR2(30),
options VARCHAR2(30),
object_node VARCHAR2(128),
object_owner VARCHAR2(30),
object_name VARCHAR2(30),
object_instance NUMBER,
object_type VARCHAR2(30),
optimizer VARCHAR2(255),
search_columns NUMBER,
id NUMBER,
parent_id NUMBER,
position NUMBER,
other LONG
)
method1:
*******
SQL> set autotrace on;
SQL> select * from emp where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPNO_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
316 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
method2:
********
SQL> explain plan set statement_id='sample'
2 for select * from emp where empno=7788;
Explained.
SQL> COL operation FORMAT A30
SQL> COL options FORMAT A15
SQL> COL object_name FORMAT A20
SQL> ed
Wrote file afiedt.buf
1 select operation, options, object_name
2 from plan_table
3 where statement_id = 'sample'
4 start with id = 0
5* connect by prior id=parent_id and prior statement_id = statement_id
SQL> /
OPERATION OPTIONS OBJECT_NAME
------------------------------ --------------- --------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID EMP
INDEX RANGE SCAN EMPNO_INDEX
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
