비용기반의 오라클 원리 (2009년)
히스토그램과 바인드 변수 0 0 32,079

by 구루비스터디 히스토그램 Histogram [2023.09.23]


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


PropertyDescriotion
Parameter typeString
SyntaxCURSOR_SHARING = ( SIMILAR , EXACT , FORCE )
Default valueEXACT
ModifiableALTER SESSION, ALTER SYSTEM
BasicNo


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를 사용할때도 히스토그램이 있더라도 그것을 사용하지 않는다.
"코어 오라클 데이터베이스 스터디모임" 에서 2009년에 "비용기반의 오라클 원리 " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4414

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입