by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

15. ADAPTIVE CURSOR




목차

I. Overview

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

III. Adaptive Cursor

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

참고 사이트

문서에 대하여

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 10월 31, 2011

    장태길 says:

    Adaptive Cursor 의 Base 가 되는 Histogram 은 10g 이상 기본 모드( 통계정보 자동 수집을 그대로 둔 상태로, 기...
    • Adaptive Cursor 의 Base 가 되는 Histogram 은
      10g 이상 기본 모드( 통계정보 자동 수집을 그대로 둔 상태로, 기본 모드로 통계정보 수집 환경 - Method_OPT 파라미터가 Default ) 면
      자동으로 오라클 판단에 따라 Histogram 수집여부가 결정되고, 수집됨

    수집 여부 체크는 아래와 같이 체크 가능합니다.

    SELECT COUNT
    FROM DBA_TAB_HISTOGRAMS
    WHERE OWNER ='SCOTT' ;

    SELECT COUNT(DISTINCT(TABLE_NAME))
    FROM DBA_TAB_HISTOGRAMS
    WHERE OWNER ='SCOTT' ;

    SELECT COUNT(DISTINCT(TABLE_NAME))
    FROM DBA_TABLES
    WHERE OWNER ='SCOTT' ;