|
CONN / AS SYSDBA
GRANT SELECT ON v_$session TO scott;
GRANT SELECT ON v_$sql TO scott;
GRANT SELECT ON v_$sql_plan TO scott;
GRANT SELECT ON v_$sql_plan_statistics_all TO scott;
CONN scott/tiger
SELECT *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'SMITH';
SET LINESIZE 130
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ADVANCED'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID gu62pbk51ubc3, child number 0
-------------------------------------
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno AND e.ename
= 'SMITH'
Plan hash value: 3625962092
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E@SEL$1
4 - SEL$1 / D@SEL$1
5 - SEL$1 / D@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "E"@"SEL$1")
INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
USE_NL(@"SEL$1" "D"@"SEL$1")
NLJ_BATCHING(@"SEL$1" "D"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("E"."ENAME"='SMITH')
4 - access("E"."DEPTNO"="D"."DEPTNO")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D"."DEPTNO"[NUMBER,22],
"D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22], "D".ROWID[ROWID,10],
"D"."DEPTNO"[NUMBER,22]
3 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],
"E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],
"E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]
4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]
5 - "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]
67 rows selected.
SQL>
|
|