행위

PLSQL 성능분석

DB CAFE

notifications_active 데이터베이스 전문기업 안내


1 PLSQL 성능분석[편집]

  1. 실습용 SP_SAMPLE_BATCH_PROCEDURE 프로시져 컴파일 하기 ( 프로시져 내부 3개 쿼리 존재 )
    1. 프로시저 시작과 끝에 DBMS_APPLICATION_INFO.set_module 처리 (필수)
    2. 프로시저 내부 SQL 수행 전 DBMS_APPLICATION_INFO.set_action 처리 (옵션)
  2. 프로시져 실행 전 alter session set statistics_level = all; 셋팅
  3. 프로시져 실행
    1. Script (plsqlxplann) 호출 하여, Sublime 편집기에 실행계획 출력하기




1.1 실습 하기[편집]

1.1.1 프로시져 생성[편집]

CREATE OR REPLACE PROCEDURE SP_SAMPLE_BATCH_PROCEDURE
IS
 /* -- TEST TABLE 생성하기 
CREATE TABLE TB_PLSQL_TEST
(
 COL1 VARCHAR2(100)
,COL2 VARCHAR2(100)
,COL3 VARCHAR2(100)
,COL4 VARCHAR2(100)
);
-- 권한 부여 하기 ( SYS로 접속하여 TBA_DBA(접속계정)에 권한을 주자. DBMS_LOCK.SLEEP 수행하기 위해서. )
GRANT EXECUTE ON DBMS_LOCK TO TBA_DBA;
 */

PROCID VARCHAR2(50) := 'SP_SAMPLE_BATCH_PROCEDURE';
V_CNT NUMBER;

BEGIN 
  DBMS_APPLICATION_INFO.set_module( PROCID ,'Task Topic'); -- MODULE INPUT

  BEGIN
    DBMS_APPLICATION_INFO.set_action( PROCID || '_001' ); -- ACTION INPUT1
    SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*) 
      INTO V_CNT
      FROM TB_PLSQL_TEST;
      DBMS_LOCK.sleep(10);
  EXCEPTION
    WHEN OTHERS THEN
      NULL; 
  END;

  BEGIN
    DBMS_APPLICATION_INFO.set_action( PROCID || '_002' ); -- ACTION INPUT2 
    FOR V_REF IN 1 .. 10 LOOP
    INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST
    SELECT LEVEL, LEVEL, LEVEL, LEVEL 
    FROM DUAL
    CONNECT BY LEVEL <= 1000; 
    END LOOP;
    DBMS_LOCK.sleep(10); 
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  BEGIN
    DBMS_APPLICATION_INFO.set_action( PROCID || '_003'); -- ACTION INPUT3 
    FOR V_REF IN 1 .. 10 LOOP 
      DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST
      WHERE ROWNUM <= 100; 
    END LOOP;
    DBMS_LOCK.sleep(10);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  COMMIT;​

DBMS_APPLICATION_INFO.set_module( '','' ); -- MODULE INPUT
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line ( sqlerrm );
END SP_SAMPLE_BATCH_PROCEDURE;​


1.1.2 프로시져 실행 전, 세션 통계정보 레벨 셋팅[편집]

- 아래 문장을 수행해야만, 실행 후 실행 계획을 얻을수 있다. pagesize, linesize 조정은 가독성 좋게 하는 효과가 있다. ( sql> @setting 대체 가능 )
sql> alter session set statistics_level = all; 
sql> set pagesize 2000;
sql> set linesize 2000;


1.1.3 프로시져 실행[편집]

sql> exec sp_sample_batch_procedure;

1.1.4 Script 호출 후, Sublime 편집기에 실행계획 출력하기[편집]

sql> @plsqlxplann SP_SAMPLE_BATCH_PROCEDURE;  ( @plsqlxplann 모듈명 )


1.1.5 프로시져 모든 SQL 실행계획[편집]

±¸  10:                WHERE MODULE = '&1' -- module명 바인딩
                 AND PLAN_HASH_VALUE <> 0
½Å  10:                WHERE MODULE = 'SP_SAMPLE_BATCH_PROCEDURE' -- module명 바인딩
                 AND PLAN_HASH_VALUE <> 0

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        EXECUTIONS CHILD_NUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------- ------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SQL_ID  3qtyvu1syqaaj, child number 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SELECT /*+ SP_SAMPLE_BATCH_PROCEDURE_001 */ COUNT(*) FROM TB_PLSQL_TEST                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Plan hash value: 3930280971                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
| Id  | Operation          | Name          | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   0 | SELECT STATEMENT   |               |      1 |        |    11 (100)|          |      1 |00:00:00.01 |      38 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   1 |  SORT AGGREGATE    |               |      1 |      1 |            |          |      1 |00:00:00.01 |      38 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   2 |   TABLE ACCESS FULL| TB_PLSQL_TEST |      1 |   9000 |    11   (0)| 00:00:01 |   9000 |00:00:00.01 |      38 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   1 - SEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - SEL$1 / TB_PLSQL_TEST@SEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Outline Data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  /*+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      DB_VERSION('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      ALL_ROWS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      FULL(@"SEL$1" "TB_PLSQL_TEST"@"SEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      END_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Column Projection Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   1 - (#keys=0) COUNT(*)[22]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - (rowset=1020)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   - dynamic statistics used: dynamic sampling (level=2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  2            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SQL_ID  327zu0t04qfhn, child number 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
INSERT /*+ SP_SAMPLE_BATCH_PROCEDURE_002 */ INTO TB_PLSQL_TEST SELECT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
LEVEL, LEVEL, LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Plan hash value: 1236776825                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
| Id  | Operation                     | Name          | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   0 | INSERT STATEMENT              |               |      1 |        |     2 (100)|          |      0 |00:00:00.01 |      17 |       |       |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   1 |  LOAD TABLE CONVENTIONAL      | TB_PLSQL_TEST |      1 |        |            |          |      0 |00:00:00.01 |      17 |       |       |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   2 |   CONNECT BY WITHOUT FILTERING|               |      1 |        |            |          |   1000 |00:00:00.01 |       0 |  2048 |  2048 | 2048  (0)|                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   3 |    FAST DUAL                  |               |      1 |      1 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       0 |       |       |          |                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   1 - SEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   3 - SEL$1 / DUAL@SEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Outline Data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  /*+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      DB_VERSION('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      ALL_ROWS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OUTLINE_LEAF(@"SEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OUTLINE_LEAF(@"INS$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      FULL(@"INS$1" "TB_PLSQL_TEST"@"INS$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      NO_CONNECT_BY_FILTERING(@"SEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      CONNECT_BY_COMBINE_SW(@"SEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      END_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Column Projection Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - LEVEL[4]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
SQL_ID  5sbpsm2154zsk, child number 1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
DELETE /*+ SP_SAMPLE_BATCH_PROCEDURE_003 */ TB_PLSQL_TEST WHERE ROWNUM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
<= 100                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Plan hash value: 1650093290                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
| Id  | Operation           | Name          | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   0 | DELETE STATEMENT    |               |      1 |        |    11 (100)|          |      0 |00:00:00.01 |     111 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   1 |  DELETE             | TB_PLSQL_TEST |      1 |        |            |          |      0 |00:00:00.01 |     111 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|*  2 |   COUNT STOPKEY     |               |      1 |        |            |          |    100 |00:00:00.01 |       6 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
|   3 |    TABLE ACCESS FULL| TB_PLSQL_TEST |      1 |  10000 |    11   (0)| 00:00:01 |    100 |00:00:00.01 |       6 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Query Block Name / Object Alias (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   1 - DEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   3 - DEL$1 / TB_PLSQL_TEST@DEL$1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Outline Data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  /*+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      BEGIN_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      IGNORE_OPTIM_EMBEDDED_HINTS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      DB_VERSION('12.1.0.2')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      ALL_ROWS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      OUTLINE_LEAF(@"DEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      FULL(@"DEL$1" "TB_PLSQL_TEST"@"DEL$1")                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
      END_OUTLINE_DATA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  */                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
---------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - filter(ROWNUM<=100)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Column Projection Information (identified by operation id):                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   2 - "TB_PLSQL_TEST".ROWID[ROWID,10], ROWNUM[8]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   3 - "TB_PLSQL_TEST".ROWID[ROWID,10]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
Note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
-----                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
   - dynamic statistics used: dynamic sampling (level=2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 20            1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

139 ÇàÀÌ ¼±ÅõǾú½À´Ï´Ù.


1.1.6 참고[편집]

1. Settinig.sql 내용 
  alter session set statistics_level = all;
  set pagesize 2000;
  set linesize 2000;

2. plsqlxplann.sql 내용
  SPOOL C:\app\myscript\xplan_list\plsqlxplan.txt

 SELECT B.PLAN_TABLE_OUTPUT, TA.EXECUTIONS, TA.CHILD_NUMBER
   FROM 
      (
       SELECT  T.MODULE, T.ACTION, T.LAST_ACTIVE_TIME, T.SQL_ID, T.CHILD_NUMBER, T.EXECUTIONS,T.ELAPSED_TIME, T.BUFFER_GETS, T.LAST_ACTIVE_TIME              
         FROM 
            (  
              SELECT a.*
                   , RANK() OVER( PARTITION BY ACTION ORDER BY LAST_ACTIVE_TIME DESC ) AS RANK 
                FROM V$SQL A 
               WHERE MODULE = '&1' -- module명 바인딩
                 AND PLAN_HASH_VALUE <> 0 
            ) T 
        WHERE RANK = 1  
     ) TA
     , TABLE( DBMS_XPLAN.DISPLAY_CURSOR( TA.SQL_ID, TA.CHILD_NUMBER, 'ADVANCED ALLSTATS LAST') ) B
     ;

 SPOOL OFF
 $C:\Program Files\Sublime Text 3\sublime_text.exe C:\app\myscript\xplan_list\plsqlxplan.txt