메뉴 건너뛰기

Korea Oracle User Group

Admin

수집된 통계가 바로 반영되지 않고 원할 때 반영하도록 하는 방식

참고 URL : https://sqlmaria.com/2023/03/21/how-to-use-dbms_stats-diff_table_stats-functions/

 

테이블에 통계 수집시 간간히 원하지 않는 성능 저하로 인해 곤욕을 겪는 경우가 있다.

대부분의 사이트에서 통계에 대한 영향도 파악이 되어있어 통계 관리에 대한 전략을 세워뒀을 테고

문제를 유발하지 않도록 관리하고 있을 것이다.

 

또 오라클의 SPM(Sql Plan Management) 기능을 사용하여 문장레벨의 플랜을 관리하고 있다면 

문제가 되지 않을 수도 있다.

 

하지만 그렇지 않은 경우 어떻게 안전하게 통계를 수집하고 반영할 수 있을지 고민이 될 수 있다.

 

위 참고 URL 방식을 통해 통계 수집시 Pending Stat으로 수집을 하고

DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING를 이용하여 기존 통계와 Pending 통계의 차이를 확인 후 

세션 레벨에서 Pending Stat을 사용하게 하여 테스트 후

Pending Stat으로 영향도 파악하여 반영하도록 할 수도 있다.

 

아래는 이러한 내용을 테스트한 내용입니다. 참고하시기 바랍니다.

 

1. 테스트 테이블 생성 및 기타 환경 구성

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
16:13:11 SQL> CREATE TABLE DB_MON.EMPLOYEES
16:13:49   2  AS 
16:13:49   3  SELECT * FROM HR.EMPLOYEES;
 
테이블이 생성되었습니다.
 
경   과: 00:00:00.24
16:13:49 SQL> CREATE UNIQUE INDEX DB_MON.EMP_EMAIL_UK ON DB_MON.EMPLOYEES(EMAIL);
 
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.02
16:13:56 SQL> 16:13:56 SQL> CREATE UNIQUE INDEX DB_MON.EMP_EMP_ID_PK ON DB_MON.EMPLOYEES(EMPLOYEE_ID);
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.01
16:13:56 SQL> CREATE INDEX DB_MON.EMP_DEPARTMENT_IX ON DB_MON.EMPLOYEES(DEPARTMENT_ID);
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.01
16:13:56 SQL> CREATE INDEX DB_MON.EMP_JOB_IX ON DB_MON.EMPLOYEES(JOB_ID);
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.01
16:13:56 SQL> CREATE INDEX DB_MON.EMP_MANAGER_IX ON DB_MON.EMPLOYEES(MANAGER_ID);
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.01
16:13:57 SQL> CREATE INDEX DB_MON.EMP_NAME_IX ON DB_MON.EMPLOYEES(LAST_NAME, FIRST_NAME);
 
인덱스가 생성되었습니다.
 
경   과: 00:00:00.01

 

위와 같이 테스트용 EMPLOYEES를 생성하고 인덱스를 생성한다.

 

2. 통계 정보 백업

현재 통계 정보의 내용을 백업해 둔다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
16:14:08 SQL> BEGIN
16:14:14   2      DBMS_STATS.CREATE_STAT_TABLE(OWNNAME => 'DB_MON',STATTAB => 'BACKUP_STAT');
16:14:14   3  END;
16:14:14   4  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:00.05
16:14:14 SQL> BEGIN
16:14:18   2      DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME => 'DB_MON'
16:14:18   3              , TABNAME => 'EMPLOYEES'
16:14:18   4              , STATOWN => 'DB_MON'
16:14:18   5              , STATTAB => 'BACKUP_STAT'
16:14:18   6              , CASCADE => TRUE);     
16:14:18   7  END;
16:14:18   8  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:03.80

 

3. 현재 통계 정보와 Pending 통계 정보를 확인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
16:14:26 SQL> col column_name for a20
16:14:41 SQL> SELECT COLUMN_NAME, HISTOGRAM
16:14:43   2  FROM DBA_TAB_COL_STATISTICS
16:14:43   3  WHERE TABLE_NAME = 'EMPLOYEES' 
16:14:43   4  AND OWNER = 'DB_MON';
 
COLUMN_NAME          HISTOGRAM
-------------------- ---------------------------------------------
EMPLOYEE_ID          NONE
FIRST_NAME           NONE
LAST_NAME            NONE
EMAIL                NONE
PHONE_NUMBER         NONE
HIRE_DATE            NONE
JOB_ID               NONE
SALARY               NONE
COMMISSION_PCT       NONE
MANAGER_ID           NONE
DEPARTMENT_ID        NONE
 
11 행이 선택되었습니다.
 
경   과: 00:00:00.37
16:17:41 SQL> SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED
16:17:47   2  FROM DBA_TAB_PENDING_STATS
16:17:47   3  WHERE OWNER = 'DB_MON'
16:17:47   4  AND TABLE_NAME = 'EMPLOYEES';
 
선택된 레코드가 없습니다.
 
경   과: 00:00:00.10

 

위와 같이 현재 Pending Stat은 존재하지 않는다.

 

4. 플랜 변경 점검용 SQL의 현재 플랜 확인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
16:17:48 SQL> EXPLAIN PLAN
16:17:54   2  SET STATEMENT_ID='STAT_TEST_BE' FOR
16:17:54   3  SELECT *
16:17:54   4  FROM DB_MON.EMPLOYEES
16:17:54   5  WHERE EMPLOYEE_ID <= 200;
 
해석되었습니다.
 
경   과: 00:00:00.02
16:17:55 SQL> COL PLAN_TABLE_OUTPUT FOR A120
16:18:02 SQL> SELECT * 
16:18:02   2  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','STAT_TEST_BE','TYPICAL',NULL));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   102 |  7038 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   102 |  7038 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
 
   1 - filter("EMPLOYEE_ID"<=200)
 
13 행이 선택되었습니다.
 
경   과: 00:00:00.15

 

위 SQL 문장은 현재 플랜상으로는 TABLE ACCESS FULL 로 테이블의 데이터를 조회한다.

이 내용을 기억해 두시기 바랍니다.

 

5. 수집된 통계의 Publish 방식에 대한 설정 변경

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
16:18:02 SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES'FROM DUAL;
 
DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES')
-----------------------------------------------------------------------------------------
TRUE
 
경   과: 00:00:00.00
16:18:12 SQL> BEGIN
16:18:25   2      DBMS_STATS.SET_TABLE_PREFS('DB_MON','EMPLOYEES','PUBLISH','FALSE');
16:18:26   3  END;
16:18:26   4  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:00.01
16:18:26 SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES'FROM DUAL;
 
DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES')
-----------------------------------------------------------------------------------------
FALSE
 
경   과: 00:00:00.00

 

위와 같이 테이블 레벨에서 수집된 통계에 대해서 Publish 방식을 변경하여 Pending Stat으로 저장되도록 설정할 수 있다.

 

6. 통계 수집 후 Pending Stat 확인

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
16:18:30 SQL> BEGIN
16:18:37   2      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DB_MON'
16:18:37   3              , TABNAME => 'EMPLOYEES'
16:18:37   4              , METHOD_OPT => 'FOR ALL COLUMNS SIZE 256');
16:18:37   5  END;
16:18:37   6  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:00.72
16:18:38 SQL> SELECT COLUMN_NAME, HISTOGRAM
16:18:43   2  FROM DBA_TAB_COL_STATISTICS
16:18:43   3  WHERE TABLE_NAME = 'EMPLOYEES' 
16:18:43   4  AND OWNER = 'DB_MON';
 
COLUMN_NAME          HISTOGRAM
-------------------- ---------------------------------------------
EMPLOYEE_ID          NONE
FIRST_NAME           NONE
LAST_NAME            NONE
EMAIL                NONE
PHONE_NUMBER         NONE
HIRE_DATE            NONE
JOB_ID               NONE
SALARY               NONE
COMMISSION_PCT       NONE
MANAGER_ID           NONE
DEPARTMENT_ID        NONE
 
11 행이 선택되었습니다.
 
경   과: 00:00:00.00
16:18:48 SQL> COL TABLE_NAME FOR A20
16:19:13 SQL> SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED
16:19:13   2  FROM DBA_TAB_PENDING_STATS
16:19:13   3  WHERE OWNER = 'DB_MON'
16:19:13   4  AND TABLE_NAME = 'EMPLOYEES';
 
TABLE_NAME           SAMPLE_SIZE LAST_ANA
-------------------- ----------- --------
EMPLOYEES                    107 23/04/03
 
경   과: 00:00:00.00

 

수집된 통계 정보가 현재 테이블에는 반영이 되어 있지 않고 Pending Stat 정보에 저장되어 있는 것을 확인할 수 있다.

 

7. 수집된 통계와 현재 통계의 차이점에 대한 리포트 확인

 

DIFF_TABLE_STATS_IN_PENDING 함수를 이용하여 수집된 통계와 현재 통계의 차이점을 확인하는 리포트를 조회할 수 있다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
16:19:13 SQL> SET LONG 99999 LONGCHUNKSIZE 99999
16:19:24 SQL> COL REPORT FOR A120
16:19:51 SQL> SELECT REPORT 
16:19:55   2  FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('DB_MON','EMPLOYEES',SYSTIMESTAMP,0));
 
REPORT
------------------------------------------------------------------------------------------
###############################################################################
 
STATISTICS DIFFERENCE REPORT FOR:
.................................
 
TABLE         : EMPLOYEES
OWNER         : DB_MON
SOURCE A      : Statistics as of 23/04/03 16:19:55.028831 +09:00
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
REPORT
------------------------------------------------------------------------------------------
 
 
NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
COLUMN STATISTICS DIFFERENCE:
.............................
 
COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................
 
 
REPORT
------------------------------------------------------------------------------------------
COMMISSION_PCT  A   7       .142857142 NO   72      2    C00B  C029  35
                B   7       .014285714 YES  72      2    C00B  C029  35
DEPARTMENT_ID   A   11      .090909090 NO   1       3    C10B  C2020 106
                B   11      .004716981 YES  1       3    C10B  C2020 106
EMAIL           A   107     .009345794 NO   0       8    41424 57544 107
                B   107     .004672897 YES  0       8    41424 57544 107
EMPLOYEE_ID     A   107     .009345794 NO   0       4    C202  C2030 107
                B   107     .004672897 YES  0       4    C202  C2030 107
FIRST_NAME      A   91      .010989010 NO   0       7    41646 57696 107
                B   91      .004672897 YES  0       7    41646 57696 107
HIRE_DATE       A   98      .010204081 NO   0       8    78650 786C0 107
 
REPORT
------------------------------------------------------------------------------------------
                B   98      .004672897 YES  0       8    78650 786C0 107
JOB_ID          A   19      .052631578 NO   0       9    41435 53545 107
                B   19      .004672897 YES  0       9    41435 53545 107
LAST_NAME       A   102     .009803921 NO   0       8    41626 5A6C6 107
                B   102     .004672897 YES  0       8    41626 5A6C6 107
MANAGER_ID      A   18      .055555555 NO   1       4    C202  C2030 106
                B   18      .004716981 YES  1       4    C202  C2030 106
PHONE_NUMBER    A   107     .009345794 NO   0       15   30313 36353 107
                B   107     .004672897 YES  0       15   30313 36353 107
SALARY          A   58      .017241379 NO   0       4    C216  C3032 107
                B   58      .004672897 YES  0       4    C216  C3032 107
 
REPORT
------------------------------------------------------------------------------------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
 
경   과: 00:00:00.05

 

이전 통계와 크게 차이를 보이지는 않지만 컬럼 히스토그램의 존재 유무와 density 정보가 다른 것을 확인할 수 있다.

 

8. 샘플 SQL 플랜 정보 비교

 

현재 상태에서 Pending Stat이 정말 플랜에 영향을 미치지 않는지 확인해 본다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
16:19:55 SQL> EXPLAIN PLAN
16:20:08   2  SET STATEMENT_ID='STAT_TEST_AF' FOR
16:20:08   3  SELECT *
16:20:08   4  FROM DB_MON.EMPLOYEES
16:20:08   5  WHERE EMPLOYEE_ID <= 200;
 
해석되었습니다.
 
경   과: 00:00:00.01
16:20:10 SQL> COL PLAN_TABLE_OUTPUT FOR A120
16:20:15 SQL> SELECT * 
16:20:15   2  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','STAT_TEST_AF','TYPICAL',NULL));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1445457117
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   102 |  7038 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   102 |  7038 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
 
   1 - filter("EMPLOYEE_ID"<=200)
 
13 행이 선택되었습니다.

 

Pending Stat의 영향을 받지 않아 원래의 플랜이 그대로 사용되는 것을 확인할 수 있다.

 

이제 세션 레벨에서 Pending Stat을 사용하도록 설정을 변경하여 수집된 Pending Stat을 사용할시 

샘플 SQL 문장이 어떤 플랜을 사용하는지 확인해 본다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
16:20:35 SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=TRUE;
 
세션이 변경되었습니다.
 
경   과: 00:00:00.00
16:24:13 SQL> EXPLAIN PLAN
16:24:28   2  SET STATEMENT_ID='STAT_TEST_PENDING_USE' FOR
16:24:28   3  SELECT *
16:24:28   4  FROM DB_MON.EMPLOYEES
16:24:28   5  WHERE EMPLOYEE_ID <= 200;
 
해석되었습니다.
 
경   과: 00:00:00.02
16:24:28 SQL> COL PLAN_TABLE_OUTPUT FOR A120
16:24:31 SQL> SELECT * 
16:24:32   2  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','STAT_TEST_PENDING_USE','TYPICAL',NULL));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 1781021061
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |   101 |  6969 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   101 |  6969 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_EMP_ID_PK |   101 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
 
   2 - access("EMPLOYEE_ID"<=200)
 
14 행이 선택되었습니다.
 
경   과: 00:00:00.14

 

위와 같이 INDEX RANGE SCAN 방식으로 테이블에 접근하는 방식이 변경된 것을 확인할 수 있다.

즉, 통계를 Pending 시키지 않고 그냥 수집했다면 SQL 문장이 변경된 플랜을 사용하게 되어 예상치 않았던 상황을 마주할 수도 있다.

 

그래서 위와 같이 통계 수집을 Pending 방식으로 수집 후 테스트 세션에서 서비스를 점검하여 영향도 파악을 수행하고

반영을 할지 여부를 결정할 수 있다.

 

위의 내용들을 참고로 결정을 했다면 Pending Stat을 반영할지 버릴지 결정을 해야할 것이다.

 

Case1. Pending Stat을 테이블에 반영

세션 레벨에서 Pending Stat을 사용하게 했던 설정을 원복하고 Pending Stat을 Publish 시킨다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
15:28:51 SQL> ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS=FALSE;
 
세션이 변경되었습니다.
 
경   과: 00:00:00.00
15:29:22 SQL> BEGIN
15:29:39   2      DBMS_STATS.PUBLISH_PENDING_STATS('DB_MON','EMPLOYEES');
15:29:39   3  END;
15:29:39   4  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:04.28

 

이제 반영된 내용을 확인하고 반영된 상태에서 샘플 SQL이 어떤 플랜을 사용하는지 확인해 보자

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
15:30:56 SQL> SELECT COLUMN_NAME, HISTOGRAM
15:31:13   2  FROM DBA_TAB_COL_STATISTICS
15:31:13   3  WHERE TABLE_NAME = 'EMPLOYEES' 
15:31:13   4  AND OWNER = 'DB_MON';
 
COLUMN_NAME          HISTOGRAM
-------------------- -------------------------
EMPLOYEE_ID          FREQUENCY
FIRST_NAME           FREQUENCY
LAST_NAME            FREQUENCY
EMAIL                FREQUENCY
PHONE_NUMBER         FREQUENCY
HIRE_DATE            FREQUENCY
JOB_ID               FREQUENCY
SALARY               FREQUENCY
COMMISSION_PCT       FREQUENCY
MANAGER_ID           FREQUENCY
DEPARTMENT_ID        FREQUENCY
 
11 행이 선택되었습니다.
 
경   과: 00:00:00.40
15:31:14 SQL> SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED
15:31:27   2  FROM DBA_TAB_PENDING_STATS
15:31:28   3  WHERE OWNER = 'DB_MON'
15:31:28   4  AND TABLE_NAME = 'EMPLOYEES';
 
선택된 레코드가 없습니다.
 
경   과: 00:00:00.00
15:32:37 SQL> EXPLAIN PLAN
15:32:45   2  SET STATEMENT_ID='STAT_TEST_PENDING_PUBLISH' FOR
15:32:45   3  SELECT *
15:32:45   4  FROM DB_MON.EMPLOYEES
15:32:45   5  WHERE EMPLOYEE_ID <= 200;
 
해석되었습니다.
 
경   과: 00:00:00.01
15:32:46 SQL> COL PLAN_TABLE_OUTPUT FOR A120
15:32:54 SQL> SELECT * 
15:32:54   2  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','STAT_TEST_PENDING_PUBLISH','TYPICAL',NULL));
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 1781021061
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |   101 |  6969 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   101 |  6969 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_EMP_ID_PK |   101 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
 
 
   2 - access("EMPLOYEE_ID"<=200)
 
14 행이 선택되었습니다.
 
경   과: 00:00:00.04

 

Pending Stat을 테이블에 반영 후 SQL 문장이 변경된 플랜을 사용하는 것을 확인할 수 있다.

 

이제 테이블에 수집된 통계가 바로 반영되지 않게 설정해 놓은 사항을 원복해 보자

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
15:41:16 SQL> BEGIN
15:51:12   2      DBMS_STATS.SET_TABLE_PREFS('DB_MON','EMPLOYEES','PUBLISH','TRUE');
15:51:12   3  END;
15:51:12   4  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:00.31
15:51:12 SQL> SELECT DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES'FROM DUAL;
 
DBMS_STATS.GET_PREFS('PUBLISH','DB_MON','EMPLOYEES')
-----------------------------------------------------------------------------
TRUE
 
경   과: 00:00:00.01

 

 

Case2. Pending된 통계가 적절치 않아 반영하지 않고 삭제하는 경우

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
15:40:56 SQL> SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED
15:40:58   2  FROM DBA_TAB_PENDING_STATS
15:40:58   3  WHERE OWNER = 'DB_MON'
15:40:58   4  AND TABLE_NAME = 'EMPLOYEES';
 
TABLE_NAME           SAMPLE_SIZE LAST_ANA
-------------------- ----------- --------
EMPLOYEES                    107 23/04/04
 
경   과: 00:00:00.00
15:40:59 SQL> BEGIN
15:41:08   2      DBMS_STATS.DELETE_PENDING_STATS('DB_MON','EMPLOYEES');
15:41:08   3  END;
15:41:08   4  /
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
경   과: 00:00:00.06
15:41:09 SQL> SELECT TABLE_NAME, SAMPLE_SIZE, LAST_ANALYZED
15:41:15   2  FROM DBA_TAB_PENDING_STATS
15:41:15   3  WHERE OWNER = 'DB_MON'
15:41:15   4  AND TABLE_NAME = 'EMPLOYEES';
 
선택된 레코드가 없습니다.
 
경   과: 00:00:00.00

 

 

위와 같이 통계 수집시 DBMS_STATS 패키지에 있는 몇개의 펑션과 프로시저를 사용해

수집된 통계가 바로 Publish 되지 않게 하여 영향도 파악을 할 수 있도록 Pending 시키고

Pending 시킨 통계를 사용해 적합성 판단해 반영되게 하는 방법에 대해서 확인해 봤다.

 

참고 URL의 내용이 통계 정보 수집시 활용해 볼만한 내용이라서 테스트 케이스를 만들어 내용을 확인해 보았다.

번호 제목 글쓴이 날짜 조회 수
34 Windows 접속 에러 ORA-12638 [1] file Talros 2024.02.14 9
33 Python 을 이용해 파일 업로드 해 보기 [1] file Talros 2023.04.19 405
» 수집된 통계가 바로 반영되지 않고 원할 때 반영하도록 하는 방식 명품관 2023.04.04 167
31 ORA-28014: Cannot Drop Administrative Users 에러 현상 [1] 명품관 2023.03.27 247
30 ORA-3136 inbound connection time out & JDBC Io exception: Connection reset [1] Talros 2023.02.24 460
29 default 값을 포함한 컬럼 추가시 오라클 버전별 개선 사항 [1] 명품관 2023.01.17 353
28 mbr(multi block read) 과 sbr(single block read) 사이에 과연 어떤 방식을 선택해야 할까요? [1] 명품관 2022.09.16 248
27 Schema password 재 사용 불가능 하게 하기 [1] Talros 2022.01.26 1083
26 Pga 메모리 설정관련 내용 [1] 우뽕 2021.03.05 1657
25 DB option Enable / Disable 정리 방법 - Mos 참고 file 우뽕 2021.01.31 1152
24 RECO 프로세스 트레이스 발생 하면서 ORA-02019 에러 발생시 명품관 2021.01.12 875
23 OS 터미널 접속 없이 오라클 접속을 통해 OS CPU 사용율 모니터링 하기 [3] file 명품관 2020.12.22 736
22 SQL Plan Management(SPM) - 3 Manual Plan Capture 명품관 2020.06.01 563
21 SQL Plan Management(SPM) - 2 DBMS_SPM.CONFIG로 필터링 file 명품관 2020.03.26 502
20 SQL Plan Management(SPM) - 1 파라미터의 기능 확인 명품관 2020.03.19 803
19 [12cR2 이상] 오브젝트 이름 30자이상 사용 가능 우뽕 2020.01.22 1042
18 Alert log 에서 갑자기 패치 정보가 나타나는 현상 Talros 2019.09.23 1538
17 External Table 기능을 사용해 파일 읽기 명품관 2017.04.05 930
16 DBMS_SCHEDULER을 통해 OS 레벨(EXTERNAL)의 shell 수행하기 명품관 2017.04.05 5732
15 오라클 패치 정보를 조회할 수 있는 뷰 DBA_REGISTRY_SQLPATCH 명품관 2017.03.02 5331
위로