꿈꾸는 개발자, DBA 커뮤니티 구루비

III. 히스토그램과 바인드 변수

히스토그램이 생성된 컬럼에 바인드 변수로 조건이 들어갈 경우 USER_TAB_HISTOGRAMS의 값을 이용하지 못하고 NDV값을 이용한다.
하지만 바인드 값을 사용하였다 하더라도 히스토그램이 영향을 아주 못주는 것은 아니다.
즉, 바인드 변수 Peeking으로 인해 값을 인지할 수 있고 또한 히스토그램을 생성하게 되면 NDV 값이 영향을 받아 변경되므로 히스토그램과 바인드 변수가 아예 상관이 없는 것은 아니다.

1. 바인드 변수 Peeking

바인드 변수 Peeking이란 최초의 cursor에 사용된 바인드 변수의 상수값을 인지하고 그 이후 같은 cursor가 들어올 경우 처음에 인지한 바인드 변수의 상수값을 가지고 해석을 하는것을 말한다.
하지만 히스토그램이 생성된 컬럼에 바인드 변수가 사용되었고 분포도가 낮은 값이 들어올 경우로 계산을 했다가 분포도가 높은 값이 그 이후에 들어온다면 정확한 값을 인식할 수 없다.
테스트 내용 : 하지만 바인드 Peeking을 활성화해도 별 차이 없음 .;


-- 0. Oracle Version
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
CORE    10.2.0.3.0      Production
NLSRTL Version 10.2.0.3.0 - Production
;

-- 1. 샘플 데이터 생성
DROP TABLE XSOFT_T;

CREATE TABLE XSOFT_T AS
SELECT DECODE(LENGTH(LEVEL),  1, 1,
                              2, 2,
                              3, 3) FLAG
FROM   DUAL
CONNECT BY LEVEL <= 999
;

-- 2. 통계정보 생성
-- 히스토그램 없이 통계정보 생성
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'XSOFT_T',
                                  METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY');
END;
/

-- 3. bind peeking 활성화
-- 1) 바인드 변수 선언
VAR B1 NUMBER;
EXEC :B1 := 3;

-- 2) STATISTICS_LEVEL 활성화
ALTER SESSION SET STATISTICS_LEVEL = ALL ;

-- 3) 분포도가 낮은 값 3으로 먼저 Peeking
SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
       *
FROM   XSOFT_T
WHERE  FLAG = :B1
;

-- 4) V$SQL에서 SQL_ID 확인
SELECT SUBSTR(SQL_TEXT, 1, 30),
       SQL_ID,
       CHILD_NUMBER
FROM   V$SQL
WHERE  UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
ORDER  BY FIRST_LOAD_TIME DESC
;


SUBSTR(SQL_TEXT,1,30)          SQL_ID        CHILD_NUMBER
------------------------------ ------------- ------------
SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc            0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            1
SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs            0
SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1            0
SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            0
SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            1
SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r            0
EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78            0
select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc            0
select substrb(dump(val,16,0,3 4agz0p1vt39fw            0
 select /*+ no_parallel(t) no_ 2gckc3xgdabky            0
BEGIN     DBMS_STATS.GATHER_TA 4qt4pb05pwh74            0
SELECT  topology   FROM  SDO_T 997gattrj5nag            0
*/

-- 5) Runtime Plan 확인
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| XSOFT_T |      1 |    333 |   999 |     3   (0)| 00:00:01 |    900 |00:00:00.01 |      64 |
-----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / XSOFT_T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "XSOFT_T"@"SEL$1")
      END_OUTLINE_DATA
  */
;

-- 6) 분포도가 높은 값 1 사용
VAR B1 NUMBER;
EXEC :B1 := 1;

SELECT /*+ OPT_PARAM('_OPTIM_PEEK_USER_BINDS' 'TRUE') */
       *
FROM   XSOFT_T
WHERE  FLAG = :B1
;

-- 7) V$SQL에서 SQL_ID 확인
SELECT SUBSTR(SQL_TEXT, 1, 30),
       SQL_ID,
       CHILD_NUMBER
FROM   V$SQL
WHERE  UPPER(SQL_TEXT) LIKE '%XSOFT_T%'
ORDER  BY FIRST_LOAD_TIME DESC
;

SUBSTR(SQL_TEXT,1,30)          SQL_ID        CHILD_NUMBER
------------------------------ ------------- ------------
SELECT SUBSTR(SQL_TEXT, 1, 30) 56bqkx2bgmvfc            0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            0
SELECT SUBSTR(SQL_TEXT, 1, 30) 46dmyzrsppa3a            1
SELECT SUBSTR(SQL_TEXT, 1, 20) b3qmfm55u7trs            0
SELECT SUBSTR(SQL_TEXT, 1, 20) atu0kk85um7v1            0
SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            0
SELECT SQL_TEXT,        SQL_FU 5hx6usvks1r75            1
SELECT /*+ OPT_PARAM('_OPTIM_P 35x0sx3dxtt5r            0
EXPLAIN PLAN FOR SELECT /*+ OP bfa0u88cz3n78            0
select /*+ no_parallel(t) no_p 8zw3fzkk0gvwc            0
select substrb(dump(val,16,0,3 4agz0p1vt39fw            0
 select /*+ no_parallel(t) no_ 2gckc3xgdabky            0
BEGIN     DBMS_STATS.GATHER_TA 4qt4pb05pwh74            0
SELECT  topology   FROM  SDO_T 997gattrj5nag            0
*/

-- 8) Runtime Plan 확인
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('35x0sx3dxtt5r',NULL,'ADVANCED ALLSTATS COST LAST'));

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| XSOFT_T |      1 |    333 |   999 |     3   (0)| 00:00:01 |      9 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / XSOFT_T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "XSOFT_T"@"SEL$1")
      END_OUTLINE_DATA
  */

-- 9) STATISTICS_LEVEL 비활성화
ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;

2. 커서 공유

오라클은 literal로 인한 Harde Parse 문제를 해결하기 위해 cursor를 공유할 수 있도록 cursor_sharing 파라미터를 제공한다.

cursor_sharing

(reference site : http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams.htm#REFRN10025)

Property	Descriotion
Parameter type	String
Syntax	CURSOR_SHARING = ( SIMILAR , EXACT , FORCE )
Default value	EXACT
Modifiable	ALTER SESSION, ALTER SYSTEM
Basic	No

FORCE

SQL내의 리터럴 상수를 시스템이 생성한 바인드 변수로 변경한다.
그리고 이렇게 생성된 공유 가능한 커서가 있는지 확인한다.
대부분, 오라클은 리터럴 값을 :SYS_B_0과 같은 이름의 바인드 변수로 변경한다.
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR

오라클은 먼저 리터럴 상수를 바인드 변수로 변경하고 바인드 변수를 Peeking할지 결정하기 때문에 별도로 최적화하는 것이 좋다고 생각한 문장에 대해서 single parse call을 최적화 시킬 수 있음
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT

같은 문장에 대해서만 cursor share를 함.
Only allows statements with identical text to share the same cursor.

즉, 히스토그램을 생성하고 나서 cursor_sharing 파라미터를 force 또는 simalar로 설정할 경우 리터럴값을 바인드 변경하기 때문에 기존의 실행계획에 영향을 줄 수 있다.
그러므로 이 파라미터를 사용하고자 한다면, 반드시 필요한 것 이상의 히스토그램을 생성하지 말아야 하며, 그렇지 않을 경우 해결할 수 없는 더 많은 성능 문제가 발생한다.

3. 오라클은 언제 히스토그램을 무시하는가?

1) 히스토그램과 조인

오라클이 히스토그램을 활용할때는 확실이 상수일경우에만 인식을 하고 조인으로 인해 바인드변수로 인식될때는 사용을 하지 못한다.

이 SQL에서 T1.N1, T2.N1 컬럼에 히스토그램이 있더라도 실제로 값이 들어오는 곳은 히스토그램이 없는 T1.N2이기 때문에 N1 값은 바인드 변수로 인식되어 히스토그램을 사용하지 못한다.



SELECT T1.V1,
       T2.V1
FROM   T1,
       T2
WHERE  T1.N2 = 99
AND    T1.N1 = T2.N1
;

2) 히스토그램과 분산 쿼리

오라클은 DB-Link를 사용할때도 히스토그램이 있더라도 그것을 사용하지 않는다.