[11/27] V$SQL_PLAN 조회

orcl@SYS> select e.last_name, d.department_name

  2  from hr.employees e, hr.departments d

  3  where e.department_id = d.department_id;

orcl@SYS> select sql_id, sql_text from v$sql

  2  where sql_text like '%select e.last_name,%';

SQL_ID

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

SQL_TEXT

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

5kj0jn002wzxu

select sql_id, sql_text from v$sql where sql_text like '%select e.last_name,%'

5ct951bdw2cy2

select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id = d.department_id

Elapsed: 00:00:00.07

orcl@SYS> select plan_table_output

  2  from table(dbms_xplan.display_cursor('5ct951bdw2cy2'));

PLAN_TABLE_OUTPUT

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

SQL_ID  5ct951bdw2cy2, child number 0

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

select e.last_name, d.department_name from hr.employees e,

hr.departments d where e.department_id = d.department_id

Plan hash value: 1473400139

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

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

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

|   0 | SELECT STATEMENT             |                   |       |       |     6 (100)|        |

|   1 |  MERGE JOIN                  |                   |   106 |  2226 |     6  (34)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |    27 |   432 |     2   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |    27 |       |     1   (0)| 00:00:01 |

|*  4 |   SORT JOIN                  |                   |   107 |  1177 |     4  (50)| 00:00:01 |

|   5 |    VIEW                      | index$_join$_001  |   107 |  1177 |     3  (34)| 00:00:01 |

|*  6 |     HASH JOIN                |                   |       |       |            |        |

|   7 |      INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |   107 |  1177 |     1   (0)| 00:00:01 |

|   8 |      INDEX FAST FULL SCAN    | EMP_NAME_IX       |   107 |  1177 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

   6 - access(ROWID=ROWID)

28 rows selected.