반응형
운영 중에 일어나 해결한 것을 정리하였습니다.
바인드변수만 다르게 해서 동일한 SQLID로 수행되는 쿼리가 특정 바인드변수가 들어오면 끝나지 않고 오래걸리는 현상이 발생하였습니다.
확인해보니 오래걸릴 때에는 실행계획이 달라졌고, SQLID는 동일했었습니다.
해결으로는 해당 SQLID에 대해서는 무조건 같은 실행계획을 타게 profile을 이용하여 고정시켜줬고, 오래걸리는 쿼리도 빠르게 끝나게 되었습니다.
처리 후에 SQLID가 같은데 실행계획이 다르게 나오는 경우는 어떤경우인지 테스트를 통해 정리해보도록 하겠습니다.
시나리오 참고 : https://aprakash.wordpress.com/2011/02/07/same-sql_id-with-different-execution-plans/
Cursor_sharing 과 optimizer의 설정이 어떻게 되어있는지 확인해봅니다.
DB 버전은 12.2.0.1입니다.
Cursor_sharing 은 EXACT(default)로 설정되어 있습니다. (EXACT/FORCE)
- EXACT는 SQL문장이 반드시 동일해야 Soft Parsing을 합니다.
- FORCE는 SQL문장 중 where절에 바인드변수가 아닌 literal한 상수값이 들어가더라도, 공유하여Soft Parsing할 수 있습니다.
참고 :
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_free_transformation_heap boolean TRUE
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 12.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_inmemory_aware boolean TRUE
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
1.테이블 생성 및 데이터 삽입
CREATE TABLE T (
C1 NUMBER,
C2 NUMBER,
C3 VARCHAR2(100));
INSERT INTO
T
SELECT
*
FROM
(SELECT
ROWNUM C1,
DECODE(MOD(ROWNUM,100),99,99,1) C2,
RPAD('A',100,'A') C3
FROM
DUAL
CONNECT BY
LEVEL <= 1000000
);
commit;
select c2,count(1) from t group by c2;
1 990000
99 10000
CREATE INDEX INDX_T_C2 ON T(C2);
데이터 확인결과 c2는 2개의 컬럼으로 이루어져 있고, 백만건 중 1%인 만건만 99이고 나머지 99%는 1인 값을 구성하고 있습니다.
2.METHOD_OPT=>'FOR ALL COLUMNS SIZE 1' 실행계획 생성
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'DEV',TABNAME=>'T',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
딕셔너리 뷰를 통해 수집된 통계정보의 정보 확인
SQL> select column_name, density, histogram from user_tab_col_statistics where table_name='T' and column_name='C2';
COLUMN_NAME DENSITY HISTOGRAM
------------------ --------- ---------------
C2 0.5 NONE
SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='T' and column_name ='C2';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
C2 0 1
C2 1 99
3.바인드 변수 사용하여 SELECT 수행
1) C2 = 1
VARIABLE N1 NUMBER
EXEC :N1:=1
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
.....
5887 1
5888 1
990000 rows selected.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 4080856482
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 990K|00:00:01.97 | 148K|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 500K| 990K|00:00:01.97 | 148K|
|* 2 | INDEX RANGE SCAN | INDX_T_C2 | 1 | 500K| 990K|00:00:01.41 | 67807 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=:N1)
2)C2=2
VARIABLE N1 NUMBER
EXEC :N1:=2
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
no rows selected
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 4080856482
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 500K| 0 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | INDX_T_C2 | 1 | 500K| 0 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=:N1)
20 rows selected.
3)C2=99
VARIABLE N1 NUMBER
EXEC :N1:=99
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
10000 rows selected.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
DEV@dbarac1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 4080856482
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:00.04 | 10689 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 500K| 10000 |00:00:00.04 | 10689 |
|* 2 | INDEX RANGE SCAN | INDX_T_C2 | 1 | 500K| 10000 |00:00:00.01 | 689 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=:N1)
20 rows selected.
1,2, 99 3개의 조건 다 같은 실행계획으로 수행되는것을 확인할 수 있습니다.
v$sql 뷰에서 sql_id를 조회해본 결과, 마지막 C2=99인 경우에는 CHILD_NUMBER가 1로 나머지 쿼리가 0인것과 차이를 내면서 수행된 것을 확인할 수 있습니다.
SQL> select
sql_id
,plan_hash_value
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '8m53magwg6495';
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------- --------------- ------------ ---------- ----------- ----------- ----------------- -------------
8m53magwg6495 4080856482 0 2 3 148615 Y N
8m53magwg6495 4080856482 1 1 0 10689 Y Y
2 rows selected.
3.METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254' 로 수집 후 체크
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'DEV',TABNAME=>'T',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254');
##buffer와 shared pool 초기화
alter system flush buffer_cache;
alter system flush shared_pool;
딕셔너리 뷰 체크
SQL> select column_name, density, histogram from user_tab_col_statistics where table_name='T' and column_name='C2';
COLUMN_NAME DENSITY HISTOGRAM
---------------- --------- ---------------
C2 0.0000005 FREQUENCY
SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where table_name='T' and column_name ='C2';
COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
C2 990000 1
C2 1000000 99
1) C2=1
VARIABLE N1 NUMBER
EXEC :N1:=1
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
990000 rows selected.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
DEV@dbarac1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 2153619298
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 990K|00:00:00.57 | 137K| 72580 |
|* 1 | TABLE ACCESS FULL| T | 1 | 990K| 990K|00:00:00.57 | 137K| 72580 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=:N1)
19 rows selected.
2) C2=2
VARIABLE N1 NUMBER
EXEC :N1:=2
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
no rows selected
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
DEV@dbarac1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 2153619298
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.85 | 72584 | 72573 |
|* 1 | TABLE ACCESS FULL| T | 1 | 990K| 0 |00:00:00.85 | 72584 | 72573 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C2"=:N1)
19 rows selected.
3)C2=99
VARIABLE N1 NUMBER
EXEC :N1:=99
SELECT /*+ GATHER_PLAN_STATISTICS */
C1,
C2
FROM
T
WHERE
C2 = :N1;
10000 rows selected.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
DEV@dbarac1 SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8m53magwg6495, child number 1
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ C1, C2 FROM T
WHERE C2 = :N1
Plan hash value: 4080856482
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10000 |00:00:02.37 | 10689 | 10023 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 10000 | 10000 |00:00:02.37 | 10689 | 10023 |
|* 2 | INDEX RANGE SCAN | INDX_T_C2 | 1 | 10000 | 10000 |00:00:00.01 | 689 | 23 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C2"=:N1)
20 rows selected.
C2=1,2는 동일한 실행계획을 보이지만 C2=99일때는 SQLID는 동일하지만 child_number가 바뀌며 다른 실행계획으로 보이게 됩니다.
SQL> select
sql_id
,plan_hash_value
, child_number
, executions
, parse_calls
, buffer_gets
, is_bind_sensitive
, is_bind_aware
from
v$sql
where
sql_id = '8m53magwg6495';
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER EXECUTIONS PARSE_CALLS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE
------------- --------------- ------------ ---------- ----------- ----------- ----------------- -------------
8m53magwg6495 2153619298 0 2 3 210228 Y N
8m53magwg6495 4080856482 1 1 0 10689 Y Y
2 rows selected.
실행계획을 수집하는 것에 있어서 차이가 생겼는데 실행계획의 차이까지 발생하였습니다.
실행계획 수집의 METHOD_OPT 에 따라 이런 결과가 발생한 것인데 이것은 무엇인지 정리해보겠습니다. 히스토그램을 수집한다라는 의미인데, METHOD_OPT 의 default 값은 FOR ALL COLUMNS SIZE AUTO 입니다.
아니면 아래 형식으로 지정해서 사용할 수도 있습니다.
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]- ALL COLUMNS : 해당 테이블의 모든 컬럼에 대해서 히스토그램 수집
- ALL INDEXED COLUMNS : 인덱스가 있는 모든 컬럼에 대해서만 히스토그램 수집
- ALL HIDDEN COLUMNS : Virtual Column(Function Based로 설정된 컬럼.
컬럼에 대해서만 히스토그램 수집. 일반적으로 수집하는 옵션은 아니고 virtual 컬럼만 수집할 때 사용.
ex)
ANNUAL_INCOME NUMBER GENERATED ALWAYS AS (PAY * 12) VIRTUAL
출처: https://fordeveloper2.tistory.com/9520 [오라클, 자바, 닷넷등 개발강좌 팁(http://ojc.asia):티스토리] )
FOR COLUMNS [column_clause] [size_clause]
size_caluse 부분에는 size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY} 의 값 중 하나를 넣을 수 있습니다.
- integer : 버킷의 수를 나타내며. [1,2048] 사이의 값을 넣을 수 있습니다.
- REPEAT : 히스토그램을 이미 가지고 있는 컬럼에 대해서만 다시 수집합니다.
- AUTO : 데이터 작업량 및 분포도를 보고 오라클이 자동으로 수집합니다. 실행계획에 Predicate로 사용된 적이 있는 컬럼만 수집.
- SKEWONLY : 데이터 분포도를 보고 오라클이 수집을 합니다. Predicate로 사용된 적이 없더라도 수집합니다.
개별 컬럼별로 버킷의 수를 설정할 수 있습니다. 1은 수집하지 않겠다는 뜻이며 최대값인 2048까지 넣어서 버킷을 생성할 수 있습니다. 예제에서는 이전 버전의 최대값을 254를 사용하였습니다. 이 버킷의 뜻은 각 컬럼의 구별값 즉, distinct value
의 개수마다 몇개의 bucket을 사용할지를 정하는 것입니다. 1개는 모든 값이 1개의 버킷에 들어가게 된다는 뜻이므로 수집할 필요가 없다는 것으로 이해되네요.
다시 위의 통계정보 수집 후 딕셔너리 뷰 조회를 보면, 다른것이 보일 것입니다.
##FOR ALL COLUMNS SIZE 1
select column_name, density, histogram from user_tab_col_statistics where table_name='T' and column_name='C2';
COLUMN_NAME DENSITY HISTOGRAM
------------------ --------- ---------------
C2 0.5 NONE
##FOR ALL INDEXED COLUMNS SIZE 254
select column_name, density, histogram from user_tab_col_statistics where table_name='T' and column_name='C2';
COLUMN_NAME DENSITY HISTOGRAM
---------------- --------- ---------------
C2 0.0000005 FREQUENCY
여기서 DENSITY는 밀도를 나타냅니다. 위에 0.5인 경우에는 bucket을 1로 했는데 distinct value는 2니까 상대적으로 높은 0.5, 아래 0.0000005는 버킷의 수가 254개이므로 상대적으로 밀도가 낮은것을 표시하고 있습니다.
그리고 위의 user_tab_col_statistics의 HISTOGRAM에 대해서는 4가지 값이 존재합니다.
[FREQUENCY.HEIGHT BALANCED,HYBRID,TOP-FREQUENCY]
통계용어인데
- FREQUENCY 도수 분포 (distinct value의 수 = 버킷 수). 각 버킷은 각 값의 빈도수를 저장
- HEIGHT BALANCED 높이균형분포 ( distinct value 의 수 > 버킷 수). 각 버킷의 높이가 동일 (전통방식)
- HYBRID 도수분포와 높이균형분포의 특성을 결합. 상황에 따라 두개 중 더나은 historgram을 선택
- TOP-FREQUENCY 도수 분포 중 통계적으로 유의미하지 않은 값들을 무시한 도수분포의 종류
histogram에 대한 내용은 다른 글로 다시 정리를 해야될 것 같습니다. 조만간 업데이트 하겠습니다.
참고 :
https://pangsun.co.kr/entry/Histogram-%EC%9D%B4%EB%9E%80
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#i1036461
https://blogs.oracle.com/optimizer/post/how-does-the-method-opt-parameter-work
http://www.gurubee.net/wiki/pages/29065610
반응형
'Oracle > 튜닝' 카테고리의 다른 글
| 히스토그램(Histogram) (4) | 2024.01.04 |
|---|---|
| 플랜 고정해주는 sql profile disable/drop (1) | 2023.12.18 |
| 공간관리와 Direct Path Load (0) | 2023.05.10 |
| 자동으로 튜닝을 권고해주는 SQL_TUNE ADVISOR (0) | 2023.03.10 |
| [DBMS_SQLTUNE] Profile로 SQL변경없이 실행계획 변경해보기 (0) | 2023.01.20 |
댓글