Explain Plan und DBMS_XPLAN
(für Oracle 10gR2)
Installation
sqlplus / as sysdba
drop table plan_table;
@?/rdbms/admin/utlxplan
create public synonym plan_table for plan_table;
grant all on plan_table to public;
Weitere Informationen zum Explain Plan (http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i26143) bzw. bei Tom Kyte (http://asktom.oracle.com/tkyte/article1/autotrace.html)
Plan Table per DBMS_XPLAN.DISPLAY anzeigen
1) explain plan Kommando für einen SELECT Befehl ausführen:
EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename='benoit';
2) Anzeigen per DBMS_XPLAN.DISPLAY Tabellenfunktion:
SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
Ergebnis-Bsp:
Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 6 (34)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 57 | 6 (34)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 1 | 37 | 3 (34)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (34)| 00:00:01 |
---------------------------------------------------------------------------
Quelle: http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm
Kommentare
Kommentar veröffentlichen