| 오라클 통계정보 백업/복구 방법은 2가지가 있다. - DBA_TAB_STAT_HISTORY 사용한 통계정보 갱신 시 자동으로 백업 되어진 데이터를 이용한 복구 - 백업 테이블 생성 후 export/import를 이용한 복구 운영 중 통계정보 갱신으로 인한 SQL 실행계획 변경 시 과거 통계정보를 복구하여 SQL실행계획 원복 |
DBA_TAB_STAT_HISTORY 이용한 통계정보 백업/복구
• 테이블 생성 및 데이터 INSERT
SQL> set line 1000
SQL> set pages 1000
SQL> set long 9999
SQL> col owner for a30
SQL> col table_name for a30
SQL> col object_name for a30
SQL> col partition_name for a30
SQL> col SUBPARTITION_NAME for a30
SQL> col sql_text for a30
SQL> col LAST_LOAD_TIME for a30
SQL> col report for a150
SQL> alter session set NLS_DATE_FORMAT = 'yyyy/mm/dd hh24:mi:ss' ;
Session altered.
SQL> alter session set nls_timestamp_tz_format='yyyy/mm/dd hh24:mi:ss:ff' ;
Session altered.
-- 테이블 생성 및 100건 INSERT
SQL>
SQL> CREATE TABLE SK.TAB1 TABLESPACE TSDTHOON01 AS SELECT * FROM DBA_OBJECTS WHERE 1=0 ;
Table created.
SQL> CREATE INDEX SK.TAB1_IX01 ON SK.TAB1(OWNER,OBJECT_NAME) TABLESPACE TSIXHOON01 ;
Index created.
SQL> INSERT INTO SK.TAB1 SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 100 ;
100 rows created.
SQL> SELECT COUNT(*) FROM SK.TAB1 ;
COUNT(*)
----------
100
SQL> COMMIT ;
Commit complete.• 통계 정보 갱신
: no_invalidate=>false 옵션으로 통계 갱신 시 관련 SQL 커서 무효화
SQL> begin
dbms_stats.gather_table_stats(ownname => 'SK', tabname => 'TAB1', cascade =>TRUE, no_invalidate => FALSE) ;
end;
/
PL/SQL procedure successfully completed.• 통계정보 갱신 확인
: NUM_ROWS 값 100 확인
통계 정보 자동 수집 상태 확인
SQL> SELECT OWNER, TABLE_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME = 'TAB1'
UNION ALL
SELECT OWNER, INDEX_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_IND_STATISTICS
WHERE TABLE_NAME = 'TAB1'
;
OWNER OBJECT_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ------------ ---------- -------------------
SK TAB1 TABLE 100 2022/09/01 16:14:36
SK TAB1_IX01 INDEX 100 2022/09/01 16:14:36• 테스트 SQL 실행(통계 갱신 전)
: INDEX RANGE SCAN 으로 수행됨
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL ;
Session altered.
SQL>
SQL> SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL
FROM SK.TAB1
WHERE 1=1
AND OWNER = 'SYS'
AND OBJECT_NAME LIKE 'ora%'
;
VAL
----------
SQL>
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g2a0rv8nzjvur, child number 0
-------------------------------------
SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL FROM SK.TAB1 WHERE 1=1
AND OWNER = 'SYS' AND OBJECT_NAME LIKE 'ora%'
Plan hash value: 306178678
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.01 | 1 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 18 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
|* 3 | INDEX RANGE SCAN | TAB1_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'ora%')
filter("OBJECT_NAME" LIKE 'ora%')
...
57 rows selected.
SQL>
SQL> ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;
Session altered.• 데이터 적재 및 통계정보 갱신
-- 데이터 변경 발생
SQL> INSERT INTO SK.TAB1 SELECT * FROM DBA_OBJECTS ;
72916 rows created.
SQL> SELECT COUNT(*) FROM SK.TAB1 ;
COUNT(*)
----------
73016
SQL> COMMIT ;
Commit complete.
-- 통계정보갱신
SQL> begin
dbms_stats.gather_table_stats(ownname => 'SK', tabname => 'TAB1', cascade =>TRUE, no_invalidate => FALSE) ;
end;
/
PL/SQL procedure successfully completed.
-- 통계정보 갱신 확인
SQL> SELECT OWNER, TABLE_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME = 'TAB1'
UNION ALL
SELECT OWNER, INDEX_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_IND_STATISTICS
WHERE TABLE_NAME = 'TAB1'
;
OWNER OBJECT_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ------------ ---------- -------------------
SK TAB1 TABLE 73016 2022/09/01 16:15:32
SK TAB1_IX01 INDEX 73016 2022/09/01 16:15:32
SQL>• 통계정보 자동 백업 확인
-- 통계정보 자동 백업 보관기간 확인(기본 31일)
SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL>
-- 통계정보 자동 백업 내역 확인
SQL> SELECT * FROM DBA_TAB_STATS_HISTORY
WHERE 1=1
AND OWNER='SK'
AND TABLE_NAME='TAB1'
ORDER BY STATS_UPDATE_TIME DESC
;
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SK TAB1 2022/09/01 16:15:32:599686
SK TAB1 2022/09/01 16:14:36:658754• 테스트 SQL 실행(통계 갱신 후)
: IS_ROLLING_INVALID = 'Y' 로 정상 상태가 아님
: PLAN_HASH_VALUE 변경되며 child cursor 생성 됨
-- 테스트 SQL 커서 상태 확인
SQL> SELECT SUBSTR(SQL_TEXT,1,30) SQL_TEXT,SQL_ID,PLAN_HASH_VALUE,INVALIDATIONS,EXECUTIONS,LAST_LOAD_TIME,LAST_ACTIVE_TIME,IS_ROLLING_INVALID
FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT /* PSK_STAT_TEST */%'
ORDER BY SQL_ID, LAST_ACTIVE_TIME DESC
;
SQL_TEXT SQL_ID PLAN_HASH_VALUE INVALIDATIONS EXECUTIONS LAST_LOAD_TIME LAST_ACTIVE_TIME I
------------------------------ ------------- --------------- ------------- ---------- ------------------------------ ------------------- -
SELECT SUBSTR(SQL_TEXT,1,30) S 1b7svfq29wf7m 2836784050 0 1 2022-09-01/16:16:04 2022/09/01 16:16:04 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 306178678 0 1 2022-09-01/16:14:54 2022/09/01 16:14:54 Y
-- 테스트 SQL 수행
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL ;
Session altered.
SQL>
SQL> SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL
FROM SK.TAB1
WHERE 1=1
AND OWNER = 'SYS'
AND OBJECT_NAME LIKE 'ora%'
;
VAL
----------
300514965
SQL>
-- 플랜 변경됨 (INDEX RANGE SCAN -> TABLE ACCESS FULL)
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g2a0rv8nzjvur, child number 1
-------------------------------------
SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL FROM SK.TAB1 WHERE 1=1
AND OWNER = 'SYS' AND OBJECT_NAME LIKE 'ora%'
Plan hash value: 1117438016
...
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 410 (100)| | 1 |00:00:00.01 | 1451 |
| 1 | SORT AGGREGATE | | 1 | 1 | 45 | | | 1 |00:00:00.01 | 1451 |
|* 2 | TABLE ACCESS FULL| TAB1 | 1 | 7116 | 312K| 410 (1)| 00:00:01 | 6111 |00:00:00.01 | 1451 |
---------------------------------------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("OBJECT_NAME" LIKE 'ora%' AND "OWNER"='SYS'))
...
52 rows selected.
SQL> ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;
Session altered.
-- V$SQL 확인(PLAN_HASH_VALUE 변경 확인)
SQL> SELECT SUBSTR(SQL_TEXT,1,30) SQL_TEXT,SQL_ID,PLAN_HASH_VALUE,INVALIDATIONS,EXECUTIONS,LAST_LOAD_TIME,LAST_ACTIVE_TIME,IS_ROLLING_INVALID
FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT /* PSK_STAT_TEST */%'
ORDER BY SQL_ID, LAST_ACTIVE_TIME DESC
;
SQL_TEXT SQL_ID PLAN_HASH_VALUE INVALIDATIONS EXECUTIONS LAST_LOAD_TIME LAST_ACTIVE_TIME I
------------------------------ ------------- --------------- ------------- ---------- ------------------------------ ------------------- -
SELECT SUBSTR(SQL_TEXT,1,30) S 1b7svfq29wf7m 2836784050 0 2 2022-09-01/16:16:04 2022/09/01 16:16:43 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 1117438016 0 1 2022-09-01/16:16:35 2022/09/01 16:16:35 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 306178678 0 1 2022-09-01/16:14:54 2022/09/01 16:16:35 Y• 백업 통계정보 확인
SQL> SELECT * FROM DBA_TAB_STATS_HISTORY
WHERE 1=1
AND OWNER='SK'
AND TABLE_NAME='TAB1'
ORDER BY STATS_UPDATE_TIME DESC
;
OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------------------------------------
SK TAB1 2022/09/01 16:15:32:599686
SK TAB1 2022/09/01 16:14:36:658754• 현재 통계정보와 과거 통계정보 비교
: time1=>systimestamp 는 현재시간, time2=>to_timestamp('2022-09-01 16:15:32','yyyy-mm-dd hh24:mi:ss') 는 비교하고 싶은 시간
: 현재는 73016 건, 과거는 100 건
SQL> select * from table(dbms_stats.diff_table_stats_in_history(
ownname=>'SK',
tabname=>'TAB1',
time1=>systimestamp,
time2=>to_timestamp('2022-09-01 16:15:32','yyyy-mm-dd hh24:mi:ss'),
pctthreshold => 0))
;
REPORT MAXDIFFPCT
------------------------------------------------------------------------------------------------------------------------------------------------------ ----------
###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : TAB1
OWNER : SK
SOURCE A : Statistics as of 2022/09/01 16:17:22:785062
SOURCE B : Statistics as of 2022/09/01 16:15:32:000000
PCTTHRESHOLD : 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~
TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS BLOCKS ROWLEN SAMPSIZE
...............................................................................
TAB1 T A 73016 1504 132 73016
B 100 5 99 100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
CREATED A 1083 .000923361 NO 0 8 78770 787A0 73016
B 2 .5 NO 0 8 78770 78770 100
CREATED_APPID A 0 0 NO 73016 0 NULL
B 0 0 NO 100 0 0
CREATED_VSNID A 0 0 NO 73016 0 NULL
B 0 0 NO 100 0 0
DATA_OBJECT_ID A 6219 .000160797 NO 66660 2 C103 C30F4 6356
B 86 .011627906 NO 0 4 C103 C2020 100
DEFAULT_COLLATI A 1 1 NO 58600 4 55534 55534 14416
B 1 1 NO 61 7 55534 55534 39
EDITIONABLE A 2 .5 NO 47760 2 4E 59 25256
B 0 0 NO 100 0 0
EDITION_NAME A 0 0 NO 73016 0 NULL
B 0 0 NO 100 0 0
GENERATED A 2 .5 NO 0 2 4E 59 73016
B 1 1 NO 0 2 4E 4E 100
LAST_DDL_TIME A 1286 .000777604 NO 1 8 78650 787A0 73015
B 6 .166666666 NO 0 8 78770 78770 100
MODIFIED_APPID A 0 0 NO 73016 0 NULL
B 0 0 NO 100 0 0
MODIFIED_VSNID A 0 0 NO 73016 0 NULL
B 0 0 NO 100 0 0
NAMESPACE A 24 .041666666 NO 1 4 C102 C2022 73015
B 3 .333333333 NO 0 3 C102 C106 100
OBJECT_ID A 73015 .000013695 NO 1 5 C103 C30F4 73015
B 100 .01 NO 0 4 C103 C2020 100
OBJECT_NAME A 61136 .000016 YES 0 35 41425 73756 5543
B 100 .01 NO 0 10 41434 56494 100
OBJECT_TYPE A 48 .020833333 NO 0 10 434C5 584D4 73016
B 3 .333333333 NO 0 7 434C5 54414 100
ORACLE_MAINTAIN A 2 .5 NO 0 2 4E 59 73016
B 1 1 NO 0 2 59 59 100
OWNER A 32 .000006847 YES 0 5 41505 58444 73016
B 1 1 NO 0 4 53595 53595 100
SECONDARY A 2 .5 NO 0 2 4E 59 73016
B 1 1 NO 0 2 4E 4E 100
SHARING A 4 .25 NO 0 14 44415 4E4F4 73016
B 2 .5 NO 0 10 4D455 4E4F4 100
STATUS A 2 .5 NO 0 7 494E5 56414 73016
B 1 1 NO 0 6 56414 56414 100
SUBOBJECT_NAME A 382 .002617801 NO 72152 2 24565 57525 864
B 0 0 NO 100 0 0
TEMPORARY A 2 .5 NO 0 2 4E 59 73016
B 1 1 NO 0 2 4E 4E 100
TIMESTAMP A 1280 .00078125 NO 1 20 31393 32303 73015
B 2 .5 NO 0 20 32303 32303 100
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................
OBJECTNAME TYP SRC ROWS LEAFBLK DISTKEY LF/KY DB/KY CLF LVL SAMPSIZ
...............................................................................
INDEX: TAB1_IX01
................
TAB1_IX01 I A 73016 707 70722 1 1 31520 2 73016
B 100 1 100 1 1 39 0 100
###############################################################################• 통계정보 복구 및 테스트 SQL 실행
: 과거 시점으로 통계정보가 변경되면서 실행계획도 해당 시점으로 변경됨(INDEX SCAN → TABLE FULL → INDEX SCAN)
-- 과거 통계정보 복구
SQL> begin
dbms_stats.restore_table_stats(
ownname=>'SK',
tabname=>'TAB1',
as_of_timestamp=>to_date('2022-09-01 16:15:32','yyyy-mm-dd hh24:mi:ss'),
no_invalidate=>false);
end;
/
PL/SQL procedure successfully completed.
-- 통계정보 갱신되며 SQL 커서 만료됨
SQL> SELECT SUBSTR(SQL_TEXT,1,30) SQL_TEXT,SQL_ID,PLAN_HASH_VALUE,INVALIDATIONS,EXECUTIONS,LAST_LOAD_TIME,LAST_ACTIVE_TIME,IS_ROLLING_INVALID
FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT /* PSK_STAT_TEST */%'
ORDER BY SQL_ID, LAST_ACTIVE_TIME DESC
;
SQL_TEXT SQL_ID PLAN_HASH_VALUE INVALIDATIONS EXECUTIONS LAST_LOAD_TIME LAST_ACTIVE_TIME I
------------------------------ ------------- --------------- ------------- ---------- ------------------------------ ------------------- -
SELECT SUBSTR(SQL_TEXT,1,30) S 1b7svfq29wf7m 2836784050 0 3 2022-09-01/16:16:04 2022/09/01 16:17:54 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 1117438016 0 1 2022-09-01/16:16:35 2022/09/01 16:16:35 Y
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 306178678 0 1 2022-09-01/16:14:54 2022/09/01 16:16:35 Y• 테스트 SQL 실행(통계정보 복구 후)
: 조건에 해당하는 6111건에 대해 index scan 사용하여 조회
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL ;
Session altered.
SQL>
SQL> SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL
FROM SK.TAB1
WHERE 1=1
AND OWNER = 'SYS'
AND OBJECT_NAME LIKE 'ora%'
;
VAL
----------
300514965
SQL>
SQL> SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS COST LAST')) ;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g2a0rv8nzjvur, child number 2
-------------------------------------
SELECT /* PSK_STAT_TEST */SUM(OBJECT_ID) VAL FROM SK.TAB1 WHERE 1=1
AND OWNER = 'SYS' AND OBJECT_NAME LIKE 'ora%'
Plan hash value: 306178678
--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 1883 |
| 1 | SORT AGGREGATE | | 1 | 1 | 18 | | | 1 |00:00:00.01 | 1883 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 18 | 2 (0)| 00:00:01 | 6111 |00:00:00.01 | 1883 |
|* 3 | INDEX RANGE SCAN | TAB1_IX01 | 1 | 1 | | 1 (0)| 00:00:01 | 6111 |00:00:00.01 | 68 |
--------------------------------------------------------------------------------------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OWNER"='SYS' AND "OBJECT_NAME" LIKE 'ora%')
filter("OBJECT_NAME" LIKE 'ora%')
...
57 rows selected.
SQL>
SQL> ALTER SESSION SET STATISTICS_LEVEL = TYPICAL ;
Session altered.
-- V$SQL 확인(커서 신규 추가됨)
SQL> SELECT SUBSTR(SQL_TEXT,1,30) SQL_TEXT,SQL_ID,PLAN_HASH_VALUE,INVALIDATIONS,EXECUTIONS,LAST_LOAD_TIME,LAST_ACTIVE_TIME,IS_ROLLING_INVALID
FROM V$SQL
WHERE SQL_TEXT LIKE '%SELECT /* PSK_STAT_TEST */%'
ORDER BY SQL_ID, LAST_ACTIVE_TIME DESC
;
SQL_TEXT SQL_ID PLAN_HASH_VALUE INVALIDATIONS EXECUTIONS LAST_LOAD_TIME LAST_ACTIVE_TIME I
------------------------------ ------------- --------------- ------------- ---------- ------------------------------ ------------------- -
SELECT SUBSTR(SQL_TEXT,1,30) S 1b7svfq29wf7m 2836784050 0 4 2022-09-01/16:16:04 2022/09/01 16:42:23 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 306178678 0 1 2022-09-01/16:18:06 2022/09/01 16:18:06 N
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 1117438016 0 1 2022-09-01/16:16:35 2022/09/01 16:18:06 Y
SELECT /* PSK_STAT_TEST */SUM( g2a0rv8nzjvur 306178678 0 1 2022-09-01/16:14:54 2022/09/01 16:16:35 Y
SQL>
백업 테이블 이용한 통계정보 백업/복구
• 백업 테이블 이용한 통계정보 백업
SQL> SELECT COUNT(*) FROM SK.TAB1 ;
COUNT(*)
----------
100
-- 통계정보 백업 테이블 생성
SQL> EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME=>'SK',STATTAB=>'STAT_TAB',TBLSPACE=>'TSIXHOON01');
PL/SQL procedure successfully completed.
SQL> SELECT * FROM SK.STAT_TAB ;
no rows selected
-- 테이블 통계정보 조회
SQL> SELECT OWNER, TABLE_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_TAB_STATISTICS
WHERE TABLE_NAME = 'TAB1'
UNION ALL
SELECT OWNER, INDEX_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
FROM DBA_IND_STATISTICS
WHERE TABLE_NAME = 'TAB1'
;
OWNER OBJECT_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ------------ ---------- -------------------
SK TAB1 TABLE 100 2022/09/05 14:40:53
SK TAB1_IX01 INDEX 100 2022/09/05 14:40:53
-- 통계정보 백업
SQL> EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'SK',TABNAME=>'TAB1',STATOWN=>'SK',STATTAB=>'STAT_TAB',STATID=>'STAT_20220905',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT STATID,C1,C5,D1 FROM SK.STAT_TAB WHERE TYPE = 'T' ;
STATID C1 C5 D1
------------------------------ ------------------------------ ------------------------------ ------------------------------
STAT_20220905 TAB1 SK 2022/09/05 14:40:53• 현재 통계정보 삭제
SQL> exec dbms_stats.delete_table_stats(ownname => 'SK', tabname => 'TAB1') ;
PL/SQL procedure successfully completed.
-- 통계정보 삭제 확인
SQL> SELECT OWNER, TABLE_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
2 FROM DBA_TAB_STATISTICS
3 WHERE TABLE_NAME = 'TAB1'
4 UNION ALL
5 SELECT OWNER, INDEX_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
6 FROM DBA_IND_STATISTICS
7 WHERE TABLE_NAME = 'TAB1'
8 ;
OWNER OBJECT_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ------------ ---------- -------------------
SK TAB1 TABLE
SK TAB1_IX01 INDEX• 통계정보 복구
SQL> SELECT STATID,C1,C5,N1,D1 FROM SK.STAT_TAB WHERE TYPE IN ('T','I') ;
STATID C1 C5 N1 D1
------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------
STAT_20220905 TAB1 SK 100 2022/09/05 14:40:53
STAT_20220905 TAB1_IX01 SK 100 2022/09/05 14:40:53
-- 통계정보 복구
SQL> exec dbms_stats.import_table_stats(ownname=>'SK',tabname=>'TAB1',statown=>'SK',stattab=>'STAT_TAB',statid=>'STAT_20220905',cascade=>TRUE) ;
PL/SQL procedure successfully completed.
-- 통계정보 정상복구 확인
SQL> SELECT OWNER, TABLE_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
2 FROM DBA_TAB_STATISTICS
3 WHERE TABLE_NAME = 'TAB1'
4 UNION ALL
5 SELECT OWNER, INDEX_NAME OBJECT_NAME, OBJECT_TYPE, NUM_ROWS, LAST_ANALYZED
6 FROM DBA_IND_STATISTICS
7 WHERE TABLE_NAME = 'TAB1'
8 ;
OWNER OBJECT_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ------------ ---------- -------------------
SK TAB1 TABLE 100 2022/09/05 14:40:53
SK TAB1_IX01 INDEX 100 2022/09/05 14:40:53
SQL>'DBMS > ORACLE' 카테고리의 다른 글
| PMO_DEFERRED_GIDX_MAINT_JOB (0) | 2023.06.01 |
|---|---|
| NCHAR(national character set) 테스트 (0) | 2022.10.07 |
| 특정 SQL_ID shared pool 에서 flush 하기 (0) | 2022.09.05 |
| OPTIMIZER_DYNAMIC_SAMPLING (0) | 2022.07.06 |
| WITH 문 사용(임시테이블, 프로시저, 펑션) 및 동작방식 (0) | 2022.07.01 |
댓글