I. Overview

1. Oracle에서 SQL 내부 수행 순서

2. Parse

오라클은 SQL 수행 시 'Query Transformation', 'Estimator & Optimization', 'Execution Plan Generation' 3 단계를 통해 Parsing 단계를 거치며, 이후 'Execute'를 하고 'Fetch'롤 통해 데이터를 사용자에게 반환함
이 때, Parsing은 소프트웨어의 코드컴파일과 유사한 개념. 즉, C++로 작성된 코드를 운영 체제에서 바로 실행할 수 없으며, 컴파일 과정을 통해 실행 파일로 변환하는 것과 같은 내용임
그만큼 Parsing 단계는 많은 고 비용을 치루는 작업이므로, 가급적 이런 고비용 작업을 안하고 기존에 만들어놓은 Parsing 작업을 재활용하는 것이 좋은데, 이처럼 미리 Parsing된 작업을 재활용하는 것을 'Sort Parsing'이라 하고, 최초 Parsing 하는 것을 'Hard Parsing'이라고 함
오라클이 이처럼 저비용의 'Sort Parsing'을 하려면 SQL 구문이 이전 'Hard Parsing' 구문과 같아야 하나, 만약 하나라도 틀릴 경우 이를 다시 'Hard Parsing'함

3. 테스트

1) Hard Parsing 테스트


-- 1. 오라클 버전 확인
SELECT *
FROM   V$VERSION
;

BANNER                                                                
----------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit      
PL/SQL Release 10.2.0.3.0 - Production                                

-- 2. 테스트 데이터 생성
-- 2-1) 테이블 생성
DROP TABLE USER.CUSTOMER_XSOFT;

CREATE TABLE CUSTOMER_XSOFT (STATE_CODE      VARCHAR2(1000),
                             TIMES_PURCHASED NUMBER);


-- 2-2) 인덱스 생성
CREATE INDEX USER.CUSTOMER_XSOFT_N1 ON USER.CUSTOMER_XSOFT (STATE_CODE);

-- 2-3) 데이터 생성
INSERT INTO CUSTOMER_XSOFT
SELECT CASE WHEN LEVEL <= 994901 THEN 'NY' ELSE 'CT' END STATE_CODE,
       DECODE(MOD(LEVEL, 6), 0, 6, MOD(LEVEL, 6)) TIMES_PURCHASED
FROM   DUAL
CONNECT BY LEVEL <= 1000000
;

COMMIT;

-- 2-4) 통계정보 생성
exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'CUSTOMER_XSOFT', DEGREE => 8);

-- 2-5) 히스토그램 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER_XSOFT', METHOD_OPT=>'for columns STATE_CODE size skewonly'); 

-- 2-6) 통계정보 확인
-- 2-6-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'CUSTOMER_XSOFT'
;

TABLE_NAME        ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN
----------------- ------------ ---------- ---------- -----------
CUSTOMER_XSOFT    24-OCT-11       1006720       1520           6
;

-- 2-6-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'CUSTOMER_XSOFT'
ORDER  BY INDEX_NAME
;

INDEX_NAME        ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
----------------- ------------ ---------- ----------- -------------
CUSTOMER_XSOFT_N1 24-OCT-11       1000000        2385             2
;

-- 2-6-3) columns
SELECT TABLE_NAME,
       COLUMN_NAME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY,
       LOW_VALUE,
       HIGH_VALUE,
       HISTOGRAM
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'CUSTOMER_XSOFT'
ORDER  BY COLUMN_NAME
;

TABLE_NAME        COLUMN_NAME        NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE   HIGH_VALUE  HISTOGRAM 
----------------- ------------------ ------------ ---------- ---------- ----------- ----------- ----------
CUSTOMER_XSOFT    STATE_CODE                    2          0 4.9666E-07 4354        4E59        FREQUENCY 
CUSTOMER_XSOFT    TIMES_PURCHASED               3          0 .333333333 C102        C104        NONE      
;

-- 3. 하드파싱 테스트
-- 3-1) 트레이스 수행
alter session set sql_trace=true;

SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = 'CT'
AND    TIMES_PURCHASED > 3
;

SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = 'NY'
AND    TIMES_PURCHASED > 3
;

-- 3-2) 트레이스 결과
SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = 'CT'
AND    TIMES_PURCHASED > 3
;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         28          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         28          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=28 pr=0 pw=0 time=8005 us)
   2549   TABLE ACCESS BY INDEX ROWID CUSTOMER_XSOFT (cr=28 pr=0 pw=0 time=10415 us)
   5099    INDEX RANGE SCAN CUSTOMER_XSOFT_N1 (cr=19 pr=0 pw=0 time=5229 us)(object id 68168394)
;

SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = 'NY'
AND    TIMES_PURCHASED > 3
;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.27       0.27          0       1532          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.27       0.27          0       1532          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=1532 pr=0 pw=0 time=274897 us)
 497450   TABLE ACCESS FULL CUSTOMER_XSOFT (cr=1532 pr=0 pw=0 time=168 us)

보는 것처럼, STATE_CODE 값을 서로 다르게 수행할 경우 Trace에서 SQL을 2번 수행한 것을 확인할 수 있음

2) Soft Parsing 테스트


-- 4. 소프트파싱 테스트
-- 4-1) 트레이스 수행
alter session set sql_trace=true;

DECLARE
    v_num NUMBER;
BEGIN
    FOR rec_val IN (SELECT DECODE(MOD(LEVEL, 2), 1, 'CT', 'NY') STATE_CODE
                    FROM DUAL 
                    CONNECT BY LEVEL <= 2) LOOP
                    
        SELECT COUNT(1)
        INTO   v_num
        FROM   CUSTOMER_XSOFT
        WHERE  STATE_CODE       = rec_val.STATE_CODE
        AND    TIMES_PURCHASED > 3;
        
        DBMS_OUTPUT.PUT_LINE('STATE_CODE : ' || rec_val.STATE_CODE || ', ' || 'COUNT : ' || v_num);        

    END LOOP;   
END;
/

STATE_CODE : CT, COUNT : 2549              
STATE_CODE : NY, COUNT : 497450            

PL/SQL procedure successfully completed.
;

-- 4-2) 트레이스 결과
SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE = :B1
AND    TIMES_PURCHASED > 3
;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.01       0.00          0          0          0           0
Fetch        2      0.38       0.38          0       3064          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.39       0.38          0       3064          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  SORT AGGREGATE (cr=3064 pr=0 pw=0 time=380638 us)
 499999   TABLE ACCESS FULL CUSTOMER_XSOFT (cr=3064 pr=0 pw=0 time=105617 us)

여기서는 CURSOR LOOPING 방식으로 STATE_CODE 값에 변수처리를 하여 같은 SQL로 인식하게 한 결과, 같은 SQL을 2번 수행한 것이 확인 가능함

3) CURSOR_SHARING 테스트


-- 5. CURSOR_SHARING 테스트
-- 5-1) 트레이스 수행
alter session set sql_trace=true;

DECLARE
    v_num NUMBER;
    v_sql VARCHAR2(1000);
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING = FORCE';

    FOR rec_val IN (SELECT DECODE(MOD(LEVEL, 2), 1, 'CT', 'NY') STATE_CODE
                    FROM DUAL 
                    CONNECT BY LEVEL <= 2) LOOP

        v_sql := NULL;
        
        v_sql := 'SELECT COUNT(1)
                  FROM   CUSTOMER_XSOFT
                  WHERE  TIMES_PURCHASED > 3
                  AND    STATE_CODE       = ' || '''' || rec_val.STATE_CODE || '''';
                  
        EXECUTE IMMEDIATE v_sql ;
        --DBMS_OUTPUT.PUT_LINE(v_sql);        
        --DBMS_OUTPUT.PUT_LINE('STATE_CODE : ' || rec_val.STATE_CODE || ', ' || 'COUNT : ' || v_num);        

    END LOOP;   
    
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING = EXACT';    
END;
/

-- 5-2) 트레이스 결과
SELECT COUNT(:"SYS_B_0")
FROM   CUSTOMER_XSOFT
WHERE  TIMES_PURCHASED > :"SYS_B_1"
AND    STATE_CODE = :"SYS_B_2"
;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2      0.01       0.00          0          0          0           0
Fetch        2      0.38       0.38          0       3064          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.39       0.38          0       3064          0           2

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      2  SORT AGGREGATE (cr=3064 pr=0 pw=0 time=380638 us)
 499999   TABLE ACCESS FULL CUSTOMER_XSOFT (cr=3064 pr=0 pw=0 time=105617 us)

이 테스트는 CURSOR_SHARING을 사용하여 모든 상수값을 바인드 변수로 변경하도록 강제화 하는 방법이며, 이 경우 변수는 시스템에 의해 자동 생성된 'SYS_B_0' 이름을 사용함

II. 바인드 변수 활용과 관련한 문제

1. 바인드 변수의 한계

STATE_CODE 값이 'NY' 일 경우, 전체의 99% 분포도를 가지기 때문에 'TABLE FULL SCAN'을 하고 'CT'는 1%이므로 'INDEX RANGE SCAN'을 하는 것이 좋음
하지만 바인드 변수일 경우, 실제 값을 모르기 때문에 통계정보 값 중 STATE_CODE 컬럼의 DISTINCT_KEY 값인 2와 NUMBER_OF_ROWS 값인 1,006,720 값을 곱한 추정치로 계산하므로 'NY', 'CT' 모두 'TABLE FULL SCAN'을 함


-- 6. 바인드 변수 사용 시 실행계획 문제점
-- 6-1) STATE_CODE = 'CT' 실행계획
EXPLAIN PLAN FOR
SELECT *
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE = 'CT'
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |  5195 | 31170 |    27   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMER_XSOFT |  5195 | 31170 |    27   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN_N1       | CUSTOMER_XSOFT |  5207 |       |    19   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
                                                                                              
Predicate Information (identified by operation id):                                           
---------------------------------------------------                                           
                                                                                              
   2 - access("STATE_CODE"='CT')                                                              


-- 6-2) STATE_CODE = 'NY' 실행계획
EXPLAIN PLAN FOR
SELECT *
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE = 'NY'
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   992K|  5815K|   436   (6)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| CUSTOMER_XSOFT |   992K|  5815K|   436   (6)| 00:00:06 |
------------------------------------------------------------------------------------
                                                                                    
Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 
                                                                                    
   1 - filter("STATE_CODE"='NY')                                                    


-- 6-3) STATE_CODE = :B1 실행계획
EXPLAIN PLAN FOR
SELECT *
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE = :B1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   498K|  2923K|   435   (6)| 00:00:06 |
|*  1 |  TABLE ACCESS FULL| CUSTOMER_XSOFT |   498K|  2923K|   435   (6)| 00:00:06 |
------------------------------------------------------------------------------------
                                                                                    
Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 
                                                                                    
   1 - filter("STATE_CODE"=:B1)

2. Bind Peeking 소개

이 단점을 보완하기 위해 Oracle 9i부터 같은 실행계획일 경우 최초에 수행된 SQL의 바인드 값을 가지고 그 이후의 실행계획을 결정하는 기능을 추가하였는데, 이 것을 'Bind Peeking' 이라고 함
하지만 이 방법 또한 최초의 값이 무엇이었느냐에 따라 'A' 또는 'B'로 고정을 시킬 수 밖에 없으므로 실행계획에 불리하게 작용하여 대부분 Site에서는 사용하지 않음


-- 7. 바인드피킹 'CT' 드라이빙 테스트
-- 7-1) _optim_peek_user_binds 파라미터 확인
SELECT A.KSPPINM  NAME,
       B.KSPPSTVL VALUE,
       B.KSPPSTDF DEF_YN,
       A.KSPPDESC DESCRIPTION
FROM   X$KSPPI  A,
       X$KSPPSV B
WHERE  A.INDX = B.INDX
AND    LOWER(A.KSPPINM) LIKE '%' || TRIM(LOWER('_optim_peek_user_binds')) || '%'
ORDER  BY 1
;

NAME                   VALUE  DEF_YN   DESCRIPTION                     
---------------------- ------ -------- --------------------------------
_optim_peek_user_binds FALSE  FALSE    enable peeking of user binds    
;

-- 7-2) 세션 레벨에서 TRUE로 변경
ALTER SESSION SET "_optim_peek_user_binds" = TRUE;

-- 7-3) 'CT' 드라이빙
SELECT COUNT(*)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       =  'CT'
AND    TIMES_PURCHASED  >= 3
;

-- 7-4) 실행계획 확인
EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       =  :B1
AND    TIMES_PURCHASED  >= 3
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

----------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |                |     1 |     6 |    27   (0)| 00:00:01 | 
|   1 |  SORT AGGREGATE              |                |     1 |     6 |            |          | 
|*  2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMER_XSOFT |  3983 | 23898 |    27   (0)| 00:00:01 | 
|*  3 |    INDEX RANGE SCAN_N1       | CUSTOMER_XSOFT |  5207 |       |    19   (0)| 00:00:01 | 
----------------------------------------------------------------------------------------------- 
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   2 - filter("TIMES_PURCHASED">=3)                                                             
   3 - access("STATE_CODE"=:B1)                                                                
;

-- 8. 바인드피킹 'NY' 드라이빙 테스트
-- 8-1) 세션 레벨에서 TRUE로 변경
ALTER SESSION SET "_optim_peek_user_binds" = TRUE;

-- 8-2) 'CT' 드라이빙
SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = 'NY'
AND    TIMES_PURCHASED > 3
;

-- 8-3) 실행계획 확인
EXPLAIN PLAN FOR
SELECT COUNT(1)
FROM   CUSTOMER_XSOFT
WHERE  STATE_CODE       = :B1
AND    TIMES_PURCHASED > 3
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-------------------------------------------------------------------------------------    
| Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     |    
-------------------------------------------------------------------------------------    
|   0 | SELECT STATEMENT   |                |     1 |     6 |   438   (6)| 00:00:06 |    
|   1 |  SORT AGGREGATE    |                |     1 |     6 |            |          |    
|*  2 |   TABLE ACCESS FULL| CUSTOMER_XSOFT |   382K|  2240K|   438   (6)| 00:00:06 |    
-------------------------------------------------------------------------------------    
                                                                                         
Predicate Information (identified by operation id):                                      
---------------------------------------------------                                      
                                                                                         
   2 - filter("STATE_CODE"=:B1 AND "TIMES_PURCHASED">=3)

3. Adaptive Cursor

Oracle 11g에서는 'Bind Peeking'의 단점을 보완하여 바인드 변수값에 따라 실행계획을 여러 개로 관리할 수 있도록 하는 새로운 기능을 제공함

III. Adaptive Cursor

Oracle 11g 에서는 쿼리가 실행될 때마다 캐시에 저장된 실행 계획을 무작정 실행하는 대신, 바인드 변수의 값이 변경되었을 때 실행 계획을 재작성해야 하는지의 여부를 판단하는 과정이 추가됨

파라미터 정보

Syntax	_OPTIMIZER_ADAPTIVE_CURSOR_SHARING
설정방법	Parameter File ALTER SYSTEM SET "_OPTIMIZER_ADAPTIVE_CURSOR_SHARING" = TRUE ALTER SESSION SET "_OPTIMIZER_ADAPTIVE_CURSOR_SHARING" = TRUE

1. Adaptive Cursor 테스트

1) Adaptive Cursor 테스트 스크립트


-- 1. 오라클 버전 확인
SELECT *
FROM   V$VERSION
;

BANNER                                                                        
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  
PL/SQL Release 11.2.0.2.0 - Production                                        

-- 2. CREATE OBJECTS
DROP TABLE XSOFT_T;
CREATE TABLE XSOFT_T(ID INT, NAME CHAR(10));
CREATE INDEX XSOFT_T_N1 ON XSOFT_T(ID);

-- 3. GENERATE SKEWED DATA 
INSERT INTO XSOFT_T SELECT 1, 'NAME' FROM ALL_OBJECTS WHERE ROWNUM <= 100000;
INSERT INTO XSOFT_T VALUES(99, 'NAME');

COMMIT;
 
-- 4. GATHER STATISTICS WITH HISTOGRAM
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'XSOFT_T', ESTIMATE_PERCENT=>100, METHOD_OPT=>'FOR COLUMNS SIZE 2 ID', CASCADE=>TRUE);

-- 5. CHECK HISTOGRAM
SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME = 'XSOFT_T';

OWNER    TABLE_NAME  COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE   
-------- ----------- ------------ --------------- -------------- ------------------------
USER     XSOFT_T     ID                     49979              1                         
USER     XSOFT_T     ID                     49980             99                         
;

-- 6. CHECK STATISTICS
-- 6-1) table
SELECT TABLE_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       BLOCKS,
       AVG_ROW_LEN
FROM   USER_TABLES
WHERE  TABLE_NAME = 'XSOFT_T'
;

TABLE_NAME           ANALYZE_TIME   NUM_ROWS     BLOCKS AVG_ROW_LEN                                                                                                                                                                                                       
-------------------- ------------ ---------- ---------- -----------                                                                                                                                                                                                       
XSOFT_T              25-OCT-11         49980        244          14   
;
 

-- 6-2) indexes
SELECT INDEX_NAME,
       LAST_ANALYZED ANALYZE_TIME,
       NUM_ROWS,
       LEAF_BLOCKS,
       DISTINCT_KEYS
FROM   USER_INDEXES
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY INDEX_NAME
;

INDEX_NAME           ANALYZE_TIME   NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS                                                                                                                                                            
-------------------- ------------ ---------- ----------- -------------                                                                                                                                                            
XSOFT_T_N1           25-OCT-11         49980         144             2   
;
 
-- 6-3) columns
SELECT TABLE_NAME,
       COLUMN_NAME,
       NUM_DISTINCT,
       NUM_NULLS,
       DENSITY,
       LOW_VALUE,
       HIGH_VALUE,
       HISTOGRAM
FROM   USER_TAB_COLUMNS
WHERE  TABLE_NAME = 'XSOFT_T'
ORDER  BY COLUMN_NAME
;


TABLE_NAME COLUMN_NAME NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE  HIGH_VALUE  HISTOGRAM                        
---------- ----------- ------------ ---------- ---------- ---------- ----------- ------------                     
XSOFT_T    ID                     2          0 .000010004 C102       C164        FREQUENCY                        
XSOFT_T    NAME                                                                  NONE  
;
 
-- 7. Bind query
alter system flush shared_pool;

var id number;

-- 8. ID == 1
-- 각 단계마다 아래 쿼리 결과 확인
SELECT SQL_ID, SQL_TEXT,IS_BIND_SENSITIVE,IS_BIND_AWARE
FROM V$SQL WHERE SQL_TEXT LIKE 'SELECT COUNT(NAME) FROM XSOFT_T%';

no rows selected

EXEC :ID := 1;

SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID;

-- 9. ID == 99
EXEC :ID := 99;

SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID;
SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID;

-- 10. ID == 1 AGAIN
EXEC :ID := 1;

SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID;

-- 11. CHECK Adaptive Cursor
SELECT IS_BIND_SENSITIVE,
       IS_BIND_AWARE,
       SQL_ID,
       CHILD_NUMBER
FROM   V$SQL
WHERE  SQL_ID = '4rs9w4k0j0khu'
;

IS IS SQL_ID                     CHILD_NUMBER
-- -- -------------------------- ------------
Y  N  4rs9w4k0j0khu                         0
Y  Y  4rs9w4k0j0khu                         1
Y  Y  4rs9w4k0j0khu                         2

-- 12. 실행계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('4rs9w4k0j0khu', NULL, 'ALLSTATS COST LAST'));

PLAN_TABLE_OUTPUT                                                                               
------------------------------------------------------------------------------------------------
SQL_ID  4rs9w4k0j0khu, child number 0                                                           
-------------------------------------                                                           
SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID                                                  
                                                                                                
Plan hash value: 978140271                                                                      
                                                                                                
------------------------------------------------------------                                    
| Id  | Operation          | Name    | E-Rows | Cost (%CPU)|                                    
------------------------------------------------------------                                    
|   0 | SELECT STATEMENT   |         |        |    69 (100)|                                    
|   1 |  SORT AGGREGATE    |         |      1 |            |                                    
|*  2 |   TABLE ACCESS FULL| XSOFT_T |  49979 |    69   (2)|                                    
------------------------------------------------------------                                    
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   2 - filter("ID"=:ID)                                                                         
                                                                                                
Note                                                                                            
-----                                                                                           
   - Warning: basic plan statistics not available. These are only collected when:               
       * hint 'gather_plan_statistics' is used for the statement or                             
       * parameter 'statistics_level' is set to 'ALL', at session or system level               
                                                                                                
SQL_ID  4rs9w4k0j0khu, child number 1                                                           
-------------------------------------                                                           
SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID                                                  
                                                                                                
Plan hash value: 191552431                                                                      
                                                                                                
-------------------------------------------------------------------------                       
| Id  | Operation                    | Name       | E-Rows | Cost (%CPU)|                       
-------------------------------------------------------------------------                       
|   0 | SELECT STATEMENT             |            |        |     2 (100)|                       
|   1 |  SORT AGGREGATE              |            |      1 |            |                       
|   2 |   TABLE ACCESS BY INDEX ROWID| XSOFT_T    |      1 |     2   (0)|                       
|*  3 |    INDEX RANGE SCAN          | XSOFT_T_N1 |      1 |     1   (0)|                       
-------------------------------------------------------------------------                       
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   3 - access("ID"=:ID)                                                                         
                                                                                                
Note                                                                                            
-----                                                                                           
   - Warning: basic plan statistics not available. These are only collected when:               
       * hint 'gather_plan_statistics' is used for the statement or                             
       * parameter 'statistics_level' is set to 'ALL', at session or system level               
                                                                                                
SQL_ID  4rs9w4k0j0khu, child number 2                                                           
-------------------------------------                                                           
SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID                                                  
                                                                                                
Plan hash value: 978140271                                                                      
                                                                                                
------------------------------------------------------------                                    
| Id  | Operation          | Name    | E-Rows | Cost (%CPU)|                                    
------------------------------------------------------------                                    
|   0 | SELECT STATEMENT   |         |        |    69 (100)|                                    
|   1 |  SORT AGGREGATE    |         |      1 |            |                                    
|*  2 |   TABLE ACCESS FULL| XSOFT_T |  49979 |    69   (2)|                                    
------------------------------------------------------------                                    
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   2 - filter("ID"=:ID)                                                                         
                                                                                                
Note                                                                                            
-----                                                                                           
   - Warning: basic plan statistics not available. These are only collected when:               
       * hint 'gather_plan_statistics' is used for the statement or                             
       * parameter 'statistics_level' is set to 'ALL', at session or system level               
                                                                                                
SQL_ID  4rs9w4k0j0khu, child number 3                                                           
-------------------------------------                                                           
SELECT COUNT(NAME) FROM XSOFT_T WHERE ID = :ID                                                  
                                                                                                
Plan hash value: 978140271                                                                      
                                                                                                
------------------------------------------------------------                                    
| Id  | Operation          | Name    | E-Rows | Cost (%CPU)|                                    
------------------------------------------------------------                                    
|   0 | SELECT STATEMENT   |         |        |    69 (100)|                                    
|   1 |  SORT AGGREGATE    |         |      1 |            |                                    
|*  2 |   TABLE ACCESS FULL| XSOFT_T |  24990 |    69   (2)|                                    
------------------------------------------------------------                                    
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   2 - filter("ID"=:ID)                                                                         
                                                                                                
Note                                                                                            
-----                                                                                           
   - Warning: basic plan statistics not available. These are only collected when:               
       * hint 'gather_plan_statistics' is used for the statement or                             
       * parameter 'statistics_level' is set to 'ALL', at session or system level               
                                                                                                

101 rows selected.

2) Adaptive CURSOR 설명

오라클은 커서의 실행 과정을 일정 기간 감시하면서 값이 어떻게 달라지는지를 확인하는데, 다른 값에 의해 실행 계획이 달라질 수 있는 경우, 커서는 "Bind-Sensitive"로 마킹되고 IS_BIND_SENSITIVE 컬럼의 값이 "Y"로 변경
오라클은 몇 차례 실행이 반복된 뒤, 커서와 값에 대한 더 많은 정보를 얻은 데이터베이스는 바인드 변수의 값에 따라 실행 계획이 변경되어야 하는지의 여부를 결정함
요약하자면, Bind-Sensitive 커서는 실행 계획이 변경될 수 있는 후보로 선정된 커서를 의미하며, Bind-Aware 커서는 실행 계획이 변경되도록 설정된 커서를 의미함

Note

☞ 출처 : 엑셈위키(http://wiki.ex-em.com/index.php/OPTIMIZER_ADAPTIVE_CURSOR_SHARING)

Adaptive Cursor Sharing 기능을 사용할 지의 여부를 지정한다. 기본값은 True로, 오라클은 항상 Adaptive Cursor Sharing 기능을 사용함
Adaptive Cursor Sharing이란 말 그대로 상황에 맞게 유연하게 Cursor를 Share하겠다는 의미이다. 이 개념을 구현하기 위해 Oracle은 Bind Sensitive Cursor, Bind Aware Cursor라는 새로운 개념을 도입함
Bind Sensitive Cursor란, 말 그대로 Bind 값에 민감한 Cursor라는 의미이다. 즉, Bind 값이 바뀌면 그것을 민감하게 처리하겠다는 의미이며 1) Equal 조건에서는 조건절에 쓰인 컬럼에 Histogram이 있고 2) Range 조건인 경우 Oracle은 이것을 Bind Senstive Cursor라고 부름
Bind Aware Cursor란, Bind Sensitive Cursor에 입력된 Bind 값에 따라 실행 계획이 분화된 Cursor를 의미하는데, Bind Aware Cursor가 생성되었다는 것은 Bind 변수의 값에 따라 Oracle이 적절한 Child Cursor를 생성했다는 것을 의미함
이에 맞게 실행 계획을 분화(새로운 Child Cursor 생성)시킨다. 따라서 Bind Peeking에 의한 부작용이 사실상 없어지게 되지만, 조건절에 쓰인 컬럼에 Histogram이 있고, Histogram의 분포도에 따라 실행 계획에 큰 차이가 있을 수 있다고 판단된다는 조건이 중요함
즉, 적절한 Histogram 없이는 의미가 없음

3) V$SQL 11g New Feature

Note

CNT	10g V$SQL	11g V$SQL	DATA TYPE	DESCRIPTION
1	SQL_TEXT	SQL_TEXT	VARCHAR2(1000)	First thousand characters of the SQL text for the current cursor
2	SQL_FULLTEXT	SQL_FULLTEXT	CLOB	Full text for the SQL statement exposed as a?CLOB?column. The full text of a SQL statement can be retrieved using this column instead of joining with the?V$SQL_TEXT?dynamic performance view.
3	SQL_ID	SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
4	SHARABLE_MEM	SHARABLE_MEM	NUMBER	Amount of shared memory used by the child cursor (in bytes)
5	PERSISTENT_MEM	PERSISTENT_MEM	NUMBER	Fixed amount of memory used for the lifetime of the child cursor (in bytes)
6	RUNTIME_MEM	RUNTIME_MEM	NUMBER	Fixed amount of memory required during the execution of the child cursor
7	SORTS	SORTS	NUMBER	Number of sorts that were done for the child cursor
8	LOADED_VERSIONS	LOADED_VERSIONS	NUMBER	Indicates whether the context heap is loaded (1) or not (0)
9	OPEN_VERSIONS	OPEN_VERSIONS	NUMBER	Indicates whether the child cursor is locked (1) or not (0)
10	USERS_OPENING	USERS_OPENING	NUMBER	Number of users executing the statement
11	FETCHES	FETCHES	NUMBER	Number of fetches associated with the SQL statement
12	EXECUTIONS	EXECUTIONS	NUMBER	Number of executions that took place on this object since it was brought into the library cache
13	PX_SERVERS_EXECUTIONS	PX_SERVERS_EXECUTIONS	NUMBER	Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
14	END_OF_FETCH_COUNT	END_OF_FETCH_COUNT	NUMBER	Number of times this cursor was fully executed since the cursor was brought into the library cache. The value of this statistic is not incremented when the cursor is partially executed, either because it failed during the execution or because only the first few rows produced by this cursor are fetched before the cursor is closed or re-executed. By definition, the value of the?END_OF_FETCH_COUNT?column should be less or equal to the value of the?EXECUTIONS?column.
15	USERS_EXECUTING	USERS_EXECUTING	NUMBER	Number of users executing the statement
16	LOADS	LOADS	NUMBER	Number of times the object was either loaded or reloaded
17	FIRST_LOAD_TIME	FIRST_LOAD_TIME	VARCHAR2(76)	Timestamp of the parent creation time
18	INVALIDATIONS	INVALIDATIONS	NUMBER	Number of times this child cursor has been invalidated
19	PARSE_CALLS	PARSE_CALLS	NUMBER	Number of parse calls for this child cursor
20	DISK_READS	DISK_READS	NUMBER	Number of disk reads for this child cursor
21	DIRECT_WRITES	DIRECT_WRITES	NUMBER	Number of direct writes for this child cursor
22	BUFFER_GETS	BUFFER_GETS	NUMBER	Number of buffer gets for this child cursor
23	APPLICATION_WAIT_TIME	APPLICATION_WAIT_TIME	NUMBER	Application wait time (in microseconds)
24	CONCURRENCY_WAIT_TIME	CONCURRENCY_WAIT_TIME	NUMBER	Concurrency wait time (in microseconds)
25	CLUSTER_WAIT_TIME	CLUSTER_WAIT_TIME	NUMBER	Cluster wait time (in microseconds)
26	USER_IO_WAIT_TIME	USER_IO_WAIT_TIME	NUMBER	User I/O Wait Time (in microseconds)
27	PLSQL_EXEC_TIME	PLSQL_EXEC_TIME	NUMBER	PL/SQL execution time (in microseconds)
28	JAVA_EXEC_TIME	JAVA_EXEC_TIME	NUMBER	Java execution time (in microseconds)
29	ROWS_PROCESSED	ROWS_PROCESSED	NUMBER	Total number of rows the parsed SQL statement returns
30	COMMAND_TYPE	COMMAND_TYPE	NUMBER	Oracle command type definition
31	OPTIMIZER_MODE	OPTIMIZER_MODE	VARCHAR2(10)	Mode under which the SQL statement is executed
32	OPTIMIZER_COST	OPTIMIZER_COST	NUMBER	Cost of this query given by the optimizer
33	OPTIMIZER_ENV	OPTIMIZER_ENV	RAW(2000)	Optimizer environment
34	OPTIMIZER_ENV_HASH_VALUE	OPTIMIZER_ENV_HASH_VALUE	NUMBER	Hash value for the optimizer environment
35	PARSING_USER_ID	PARSING_USER_ID	NUMBER	User ID of the user who originally built this child cursor
36	PARSING_SCHEMA_ID	PARSING_SCHEMA_ID	NUMBER	Schema ID that was used to originally build this child cursor
37	PARSING_SCHEMA_NAME	PARSING_SCHEMA_NAME	VARCHAR2(30)	Schema name that was used to originally build this child cursor
38	KEPT_VERSIONS	KEPT_VERSIONS	NUMBER	Indicates whether this child cursor has been marked to be kept pinned in the cache using the?DBMS_SHARED_POOL?package
39	ADDRESS	ADDRESS	RAW(8)	Address of the handle to the parent for this cursor
40	TYPE_CHK_HEAP	TYPE_CHK_HEAP	RAW(8)	Descriptor of the type check heap for this child cursor
41	HASH_VALUE	HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
42	OLD_HASH_VALUE	OLD_HASH_VALUE	NUMBER	Old SQL hash value
43	PLAN_HASH_VALUE	PLAN_HASH_VALUE	NUMBER	Numerical representation of the SQL plan for this cursor. Comparing one?PLAN_HASH_VALUE?to another easily identifies whether or not two plans are the same (rather than comparing the two plans line by line).
44	CHILD_NUMBER	CHILD_NUMBER	NUMBER	Number of this child cursor
45	SERVICE	SERVICE	VARCHAR2(64)	Service name
46	SERVICE_HASH	SERVICE_HASH	NUMBER	Hash value for the name listed in?SERVICE
47	MODULE	MODULE	VARCHAR2(64)	Contains the name of the module that was executing at the time that the SQL statement was first parsed, which is set by calling?DBMS_APPLICATION_INFO.SET_MODULE
48	MODULE_HASH	MODULE_HASH	NUMBER	Hash value of the module listed in the?MODULE?column
49	ACTION	ACTION	VARCHAR2(64)	Contains the name of the action that was executing at the time that the SQL statement was first parsed, which is set by calling?DBMS_APPLICATION_INFO.SET_ACTION
50	ACTION_HASH	ACTION_HASH	NUMBER	Hash value of the action listed in the?ACTION?column
51	SERIALIZABLE_ABORTS	SERIALIZABLE_ABORTS	NUMBER	Number of times the transaction fails to serialize, producing?ORA-08177?errors, per cursor
52	OUTLINE_CATEGORY	OUTLINE_CATEGORY	VARCHAR2(64)	If an outline was applied during construction of the cursor, then this column displays the category of that outline. Otherwise the column is left blank.
53	CPU_TIME	CPU_TIME	NUMBER	CPU time (in microseconds) used by this cursor for parsing, executing, and fetching
54	ELAPSED_TIME	ELAPSED_TIME	NUMBER	Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching
55	OUTLINE_SID	OUTLINE_SID	NUMBER	Outline session identifier
56	CHILD_ADDRESS	CHILD_ADDRESS	RAW(8)	Address of the child cursor
57	SQLTYPE	SQLTYPE	NUMBER	Denotes the version of the SQL language used for this statement
58	REMOTE	REMOTE	VARCHAR2(1)	Indicates whether the cursor is remote mapped (Y) or not (N)
59	OBJECT_STATUS	OBJECT_STATUS	VARCHAR2(19)	Status of the cursor: VALID - Valid, authorized without errors VALID_AUTH_ERROR - Valid, authorized with authorization errors VALID_COMPILE_ERROR - Valid, authorized with compilation errors VALID_UNAUTH - Valid, unauthorized INVALID_UNAUTH - Invalid, unauthorized INVALID - Invalid, unauthorized but keep the timestamp
60	LITERAL_HASH_VALUE	LITERAL_HASH_VALUE	NUMBER	Hash value of the literals which are replaced with system-generated bind variables and are to be matched, whenCURSOR_SHARING?is used. This is not the hash value for the SQL statement. If?CURSOR_SHARING?is not used, then the value is 0.
61	LAST_LOAD_TIME	LAST_LOAD_TIME	VARCHAR2(76)	Time at which the query plan (heap 6) was loaded into the library cache
62	IS_OBSOLETE	IS_OBSOLETE	VARCHAR2(1)	Indicates whether the cursor has become obsolete (Y) or not (N). This can happen if the number of child cursors is too large.
63		IS_BIND_SENSITIVE	VARCHAR2(1)
64		IS_BIND_AWARE	VARCHAR2(1)
65		IS_SHAREABLE	VARCHAR2(1)
66	CHILD_LATCH	CHILD_LATCH	NUMBER	Child latch number that is protecting the cursor
67	SQL_PROFILE	SQL_PROFILE	VARCHAR2(64)	SQL profile
68		SQL_PATCH	VARCHAR2(30)
69		SQL_PLAN_BASELINE	VARCHAR2(30)
70	PROGRAM_ID	PROGRAM_ID	NUMBER	Program identifier
71	PROGRAM_LINE#	PROGRAM_LINE#	NUMBER	Program line number
72	EXACT_MATCHING_SIGNATURE	EXACT_MATCHING_SIGNATURE	NUMBER	Signature calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings.
73	FORCE_MATCHING_SIGNATURE	FORCE_MATCHING_SIGNATURE	NUMBER	The signature used when the?CURSOR_SHARING?parameter is set to?FORCE
74	LAST_ACTIVE_TIME	LAST_ACTIVE_TIME	DATE	TIme at which the query plan was last active
75	BIND_DATA	BIND_DATA	RAW(2000)	Bind data
76		TYPECHECK_MEM	NUMBER
77		IO_CELL_OFFLOAD_ELIGIBLE_BYTES	NUMBER
78		IO_INTERCONNECT_BYTES	NUMBER
79		PHYSICAL_READ_REQUESTS	NUMBER
80		PHYSICAL_READ_BYTES	NUMBER
81		PHYSICAL_WRITE_REQUESTS	NUMBER
82		PHYSICAL_WRITE_BYTES	NUMBER
83		OPTIMIZED_PHY_READ_REQUESTS	NUMBER
84		LOCKED_TOTAL	NUMBER
85		PINNED_TOTAL	NUMBER
86		IO_CELL_UNCOMPRESSED_BYTES	NUMBER
87		IO_CELL_OFFLOAD_RETURNED_BYTES	NUMBER

2. V$SQL_CS_HISTOGRAM

11g에서 새로 추가된 V$SQL_CS_HISTOGRAM 뷰는 SQL 구문이 몇 차례 실행되었는지에 대한 정보를 표시함
아래에서 확인할 수 있듯, 정보는 각각의 자식 커서(child cursor)에 대해 3개의 버킷(bucket)으로 분류되어 표시됨

1) V$SQL_CS_HISTOGRAM 확인


SELECT *
FROM   V$SQL_CS_HISTOGRAM
WHERE  SQL_ID = '4rs9w4k0j0khu'
ORDER  BY CHILD_NUMBER,
          BUCKET_ID
;

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
---------------- ---------- ------------- ------------ ---------- ----------
0700000067AA2600 2165328410 4rs9w4k0j0khu            0          0          1
0700000067AA2600 2165328410 4rs9w4k0j0khu            0          1          1
0700000067AA2600 2165328410 4rs9w4k0j0khu            0          2          0
0700000067AA2600 2165328410 4rs9w4k0j0khu            1          0          1
0700000067AA2600 2165328410 4rs9w4k0j0khu            1          1          0
0700000067AA2600 2165328410 4rs9w4k0j0khu            1          2          0
0700000067AA2600 2165328410 4rs9w4k0j0khu            2          0          0
0700000067AA2600 2165328410 4rs9w4k0j0khu            2          1          1
0700000067AA2600 2165328410 4rs9w4k0j0khu            2          2          0

2) V$SQL_CS_HISTOGRAM 설명

Note

☞ V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing.
☞ This information is used to decide whether to enable extended cursor sharing for a query.
☞ It is stored in a histogram, whose buckets contents are exposed by this view

Column	Datatype	Description
ADDRESS	RAW(4)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
CHILD_NUMBER	NUMBER	Number of the child cursor being monitored
BUCKET_ID	NUMBER	Bucket number of the monitoring histogram
COUNT	NUMBER	Value in this bucket of the histogram

3. V$SQL_CS_SELECTIVITY

어댑티브 커서 공유 기능을 이용하여 바인드 변수의 값을 기준으로 실행 계획을 선택하려면, 데이터베이스가 이에 관련된 정보를 어딘가에 저장하고 있어야 함
이는 새로 추가된 V$SQL_CS_SELECTIVITY 뷰에서 확인할 수 있으며, 바인드 변수에 전달되는 값들의 선택성(selectivity)에 대한 정보를 보여 줌

1) V$SQL_CS_HISTOGRAM 확인


SELECT *
FROM   V$SQL_CS_SELECTIVITY
WHERE  SQL_ID = '4rs9w4k0j0khu'
;

ADDRESS          HASH_VALUE SQL_ID         CHILD_NUMBER PREDICATE  RANGE_ID LOW      HIGH          
---------------- ---------- -------------- ------------ ---------- -------- -------- --------      
0700000067AA2600 2165328410 4rs9w4k0j0khu             2 =ID               0 0.899982 1.099978      
0700000067AA2600 2165328410 4rs9w4k0j0khu             1 =ID               0 0.000018 0.000022

새로운 뷰를 통해 매우 많은 정보를 얻을 수 있음
PREDICATE 컬럼은 사용자에 의해 적용된 다양한 조건절(WHERE 절)을 보여 주며, LOW, HIGH 값은 전달된 최소, 최대값을 의미함

2) V$SQL_CS_HISTOGRAM 설명

Note

☞ V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing.
☞ This information is used to decide whether to enable extended cursor sharing for a query.
☞ It is stored in a histogram, whose buckets contents are exposed by this view.

Column	Datatype	Description
ADDRESS	RAW(4)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
CHILD_NUMBER	NUMBER	Number of the child cursor being monitored
BUCKET_ID	NUMBER	Bucket number of the monitoring histogram
COUNT	NUMBER	Value in this bucket of the histogram

4. V$SQL_CS_STATISTICS

V$SQL_CS_STATISTICS는 Bind-Aware 또는 Bind-Sensitive로 마킹된 커서의 활동 내역을 보여줌

1) V$SQL_CS_STATISTICS 확인


SELECT CHILD_NUMBER,
       BIND_SET_HASH_VALUE,
       PEEKED,
       EXECUTIONS,
       ROWS_PROCESSED,
       BUFFER_GETS,
       CPU_TIME
FROM   V$SQL_CS_STATISTICS
WHERE  SQL_ID = '4rs9w4k0j0khu'
;

CHILD_NUMBER BIND_SET_HASH_VALUE PE EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME   
------------ ------------------- -- ---------- -------------- ----------- ----------   
           2          2342552567 Y           1          49980         190          0   
           1          1937997561 Y           1              3           3          0   
           0          2342552567 Y           1          49980         193          0

이 뷰를 통해 실행 과정에서 데이터베이스에 기록된 성능 통계를 확인할 수 있음
EXECUTIONS 컬럼은 서로 다른 바인드 변수의 값에 대해 쿼리가 얼마나 자주 수행되었는지에 대한 정보를 보여주는데, ("P"로표시되는) PEEKED 컬럼은 옵티마이저가 적절한 실행 계획을 얻기 위해 바인드 변수에 대한 엿보기(peek)를 수행했음을 의미함

2) V$SQL_CS_STATISTICS 설명

Note

☞ V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component of adaptive cursor sharing.
☞ A sample of the executions is monitored. This view exposes which executions were sampled, and what the statistics were for those executions.
☞ The statistics are cumulative for each distinct set of bind values.

Column	Datatype	Description
ADDRESS	RAW(4)	Address of the handle to the parent for this cursor
HASH_VALUE	NUMBER	Hash value of the parent statement in the library cache
SQL_ID	VARCHAR2(13)	SQL identifier of the parent cursor in the library cache
CHILD_NUMBER	NUMBER	Number of the child cursor being monitored
BIND_SET_HASH_VALUE	NUMBER	Hash of the values of the binds
PEEKED	VARCHAR2(1)	Indicates if this is the bind set used to build the cursor (Y) or not (N)
EXECUTIONS	NUMBER	Number of times this bind set has been executed and monitored
ROWS_PROCESSED	NUMBER	Cumulative number of rows processed by all row sources in the plan over all monitored executions with this bind set
BUFFER_GETS	NUMBER	Cumulative number of buffer gets over all monitored executions with this bind set
CPU_TIME	NUMBER	Cumulative CPU time (in microseconds) used by this cursor for monitored executions with this bind set

참고 사이트

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2011년 10월 29일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.

15. ADAPTIVE CURSOR (by xsoft) [2011.10.24]

목차

I. Overview

1. Oracle에서 SQL 내부 수행 순서

2. Parse

3. 테스트

1) Hard Parsing 테스트

2) Soft Parsing 테스트

3) CURSOR_SHARING 테스트

II. 바인드 변수 활용과 관련한 문제

1. 바인드 변수의 한계

2. Bind Peeking 소개

3. Adaptive Cursor

III. Adaptive Cursor

1. Adaptive Cursor 테스트

1) Adaptive Cursor 테스트 스크립트

2) Adaptive CURSOR 설명

3) V$SQL 11g New Feature

2. V$SQL_CS_HISTOGRAM

1) V$SQL_CS_HISTOGRAM 확인

2) V$SQL_CS_HISTOGRAM 설명

3. V$SQL_CS_SELECTIVITY

1) V$SQL_CS_HISTOGRAM 확인

2) V$SQL_CS_HISTOGRAM 설명

4. V$SQL_CS_STATISTICS

1) V$SQL_CS_STATISTICS 확인

2) V$SQL_CS_STATISTICS 설명

문서에 대하여