SQL 메타 데이터

SQL과 실행 계획에 관련된 메타 데이터를 조회할 수 있는 뷰를 살펴보자.

DBA_HIST_SQLCOMMAND_NAME 뷰에서 SQL 명령어를 조회할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 1 SELECT command_type, command_name FROM dba_hist_sqlcommand_name;   COMMAND_TYPE COMMAND_NAME ------------ -------------- 0 1            CREATE TABLE 2            INSERT 3            SELECT 4            CREATE CLUSTER ... 194 행이 선택되었습니다.

DBA_HIST_PLAN_OPERATION_NAME 뷰에서 실행 계획 오퍼레이션을 조회할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 2 SELECT operation_id, operation_name FROM dba_hist_plan_operation_name;   OPERATION_ID OPERATION_NAME ------------ -------------------- 0 1            BITMAP KEY ITERATION 2            NESTED LOOPS 3            MERGE JOIN 4            HASH JOIN ... 149 행이 선택되었습니다.

DBA_HIST_PLAN_OPTION_NAME 뷰에서 실행 계획 옵션을 조회할 수 있다. 아쉽게도 오퍼레이션과 옵션의 연관 정보는 제공되지 않는다.

1 2 3 4 5 6 7 8 9 10 11 12

-- 3 SELECT option_id, option_name FROM dba_hist_plan_option_name;   OPTION_ID OPTION_NAME --------- ----------- 0 1         OUTER 2         ANTI 3         SEMI 4         CARTESIAN ... 249 행이 선택되었습니다.

해당 뷰는 ASH 데이터를 정규화하기 위한 용도로 사용된다.  DBA_HIST_ACTIVE_SESS_HISTORY 뷰에 해당 뷰를 사용하고 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

-- 4 EXPLAIN PLAN FOR SELECT * FROM dba_hist_active_sess_history;   ------------------------------------------------------------------- | Id  | Operation                   | Name                        | ------------------------------------------------------------------- |   0 | SELECT STATEMENT            |                             | |   1 |  TABLE ACCESS BY INDEX ROWID| WRH$_SQLCOMMAND_NAME        | -- dba_hist_sqlcommand_name |*  2 |   INDEX UNIQUE SCAN         | WRH$_SQLCOMMAND_NAME_PK     | |   3 |  TABLE ACCESS BY INDEX ROWID| WRH$_PLAN_OPERATION_NAME    | -- dba_hist_plan_operation_name |*  4 |   INDEX UNIQUE SCAN         | WRH$_PLAN_OPERATION_NAME_PK | |   5 |  TABLE ACCESS BY INDEX ROWID| WRH$_PLAN_OPTION_NAME       | -- dba_hist_plan_option_name |*  6 |   INDEX UNIQUE SCAN         | WRH$_PLAN_OPTION_NAME_PK    | |   7 |  TABLE ACCESS BY INDEX ROWID| WRH$_TOPLEVELCALL_NAME      | |*  8 |   INDEX UNIQUE SCAN         | WRH$_TOPLEVELCALL_NAME_PK   | |   9 |  FIXED TABLE FULL           | X$MODACT_LENGTH             | |  10 |  FIXED TABLE FULL           | X$MODACT_LENGTH             | |* 11 |  HASH JOIN RIGHT OUTER      |                             | |  12 |   TABLE ACCESS FULL         | WRH$_EVENT_NAME             | |  13 |   PARTITION RANGE ALL       |                             | |  14 |    TABLE ACCESS FULL        | WRH$_ACTIVE_SESSION_HISTORY | -------------------------------------------------------------------