오라클 19c 통계정보 export 관련 궁금증 테스트

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 19.27.0.0

 

방법 : 오라클 19c 통계정보 export 관련 궁금증 테스트

본문에서는 오라클 19c에서 통계정보 export 관련하여 그냥 궁금한 부분들을 테스트 해봄

 

 

테스트
1. 통계 저장용 테이블이 이미 존재하는 상태에서 한번더 명령 수행시 어떻게 될지?
2. 통계정보를 이미 ALL_TABLE_STATS 테이블에 export 한다음 한번 더 export 수행시 어떻게 될지?
3. 2번 상황에서 statid를 주고 동일 테이블에 export 수행시 어떻게 될지?

4. 3번 상황에서 동일 테이블에 export 수행시 어떻게 될지?

 

 

테스트
1. 통계 저장용 테이블이 이미 존재하는 상태에서 한번더 명령 수행시 어떻게 될지?
통계 저장용 테이블이 이미 존재하는 상태에서 한번더 명령 수행

1 2 3 4 5 6 7 8 9 10 11 12

SQL> exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'ALL_TABLE_STATS');   PL/SQL procedure successfully completed.   SQL> exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'ALL_TABLE_STATS'); BEGIN dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'ALL_TABLE_STATS'); END;   * ERROR at line 1: ORA-20002: Unable to create table ALL_TABLE_STATS: already exists ORA-06512: at "SYS.DBMS_STATS", line 20822 ORA-06512: at line 1

already exists라고 에러가 발생함

 

 

2. 통계정보를 이미 ALL_TABLE_STATS 테이블에 export 한다음 한번 더 export 수행시 어떻게 될지?
통계정보 export

1 2 3

SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');   PL/SQL procedure successfully completed.

 

 

all_table_stats 테이블 count 확인

1 2 3 4 5

SQL> select count(*) from system.all_table_stats;     COUNT(*) ----------    1529493

 

 

한번더 통계정보 export

1 2 3

SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');   PL/SQL procedure successfully completed.

잘 수행됨

 

 

export 수행중 all_table_stats 테이블 count 확인

1 2 3 4 5 6

SQL> select count(*) from system.all_table_stats;     COUNT(*) ----------     781471 (계속증가중)

 

 

export 완료 후 all_table_stats 테이블 count 확인

1 2 3 4 5

SQL> select count(*) from system.all_table_stats;     COUNT(*) ----------    1529493

그대로 약 150만건임, truncate 후 insert 된것으로 보임

 

 

3. 2번 상황에서 statid를 주고 동일 테이블에 export 수행시 어떻게 될지?
statid 주고 동일 테이블에 통계정보 export

1 2 3

SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS', statid => 'TEST_STAT_ID');   PL/SQL procedure successfully completed.

잘 수행됨

 

 

all_table_stats 테이블 count 확인
참고로 현재 통계정보 export시 statid를 지정하지 않은경우 null로 들어가고 statid를 지정하면 지정한 값으로 들어감

1 2 3 4 5 6 7 8 9 10 11

SQL>  set lines 200 pages 1000 col statid for a15 select nvl(statid, 'NULL') statid, count(*)  from system.all_table_stats group by statid;   STATID            COUNT(*) --------------- ---------- NULL               1529493 TEST_STAT_ID       1529493

지정한 statid로 된 통계정보가 추가로 150만건이 저장되어 약 300만건이 존재함

 

 

4. 3번 상황에서 동일 테이블에 export 수행시 어떻게 될지?

기존 row수 확인

SQL> 
set lines 200 pages 1000
col statid for a15
select nvl(statid, 'NULL') statid, count(*) 
from system.all_table_stats
group by statid;
 
STATID            COUNT(*)
--------------- ----------
NULL               1529493
TEST_STAT_ID       1529493

 

 

statid 안주고 (statid null로) export 수행

SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
(실행중)

 

 

export 수행중 oratop으로 plan 확인

Enter sql_id: 96zsyz4bpbqj7
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  96zsyz4bpbqj7, child number 0
--------------------------------------
delete /*+ dynamic_sampling(0) index(t) */ from
"SYSTEM"."ALL_TABLE_STATS" t where (statid is null and :statid is
null)  and (type = :typeid or type = :typeid2  or type = :typeid3
or type = :typeid4 ) and (:ownname is null)  and (:objname is
null)  and (:colname is null)  and (:partname is null) and
(:partname is null)  and (c5 != 'SYS' or c1 not like 'X$%')
 
Plan hash value: 226988053
 
---------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | DELETE STATEMENT   |                 |       |       |     1 (100)|
|   1 |  DELETE            | ALL_TABLE_STATS |       |       |            |
|*  2 |   FILTER           |                 |       |       |            |
|*  3 |    INDEX RANGE SCAN| ALL_TABLE_STATS |  1529K|   166M|     0   (0)|
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter((:PARTNAME IS NULL AND :PARTNAME IS NULL AND :COLNAME IS
              NULL AND :OBJNAME IS NULL AND :OWNNAME IS NULL AND :STATID IS NULL))
   3 - access("STATID" IS NULL)
       filter((("C5"<>'SYS' OR "C1" NOT LIKE 'X$%') AND
              INTERNAL_FUNCTION("TYPE")))

delete를 수행함, 이후 insert 됨

 

 

결론 :
1. 통계 저장용 테이블이 이미 존재하는 상태에서 한번더 명령 수행시 어떻게 될지?
=> already exists라고 나오면서 중복 생성은 되지 않음
2. 통계정보를 이미 ALL_TABLE_STATS 테이블에 export 한다음 한번 더 export 수행시 어떻게 될지?
=> 기존 데이터를 truncate 시키고 새로 삽입함, 150만건이 300만건으로 늘지 않음
3. 2번 상황에서 statid를 주고 동일 테이블에 export 수행시 어떻게 될지?
=> 같은 통계 테이블에 statid만 다르게 해서 값이 들어감

4. 3번 상황에서 동일 테이블에 export 수행시 어떻게 될지?

=> statid가 2개이기 때문에 truncate 대신 export 대상 statid를 delete 한뒤 export(insert) 수행됨

 

 

참조 : 

오라클 19c 통계정보 대량 이관시 병렬 처리 ( https://positivemh.tistory.com/1267 )
오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )