발표 통계정보 수집 관련 test
2011.03.23 20:03
안녕하세요 B조 임재규입니다.
저도 이번주에 발표인데 요즘 이슈가 너무 많아서 발표준비를 미쳐 못했습니다.
죄송합니다. 이슈사항 중에 하나 제가 test 한거 올릴께요.
노여움 푸시고 봐주세요 ~~ㅋ
글 올리고 보니까 디테일한 설명이 부족하네요 ㅠㅠ
한가할 때 좀 더 디테일하게 설명 올릴께요
<통계정보 수집>
---매일 22:00 시 gather_stats_jobs 스케줄러가 호출되어 통계정보를 수집함 ---
<통계정보 수집 대상>
-table의 insert,update,delete 의 dml 발생량이 10%이상
-DDL 작업
-index 생성
############################# test1 (dml 발생량 측정 #############################
1.emp table 생성
OWNER Segment Type Tablespace Size Extents MaxExtents Initial(MB) Next(MB)
-------------------------- --------------------------------------------------------------------
JK EMP TABLE USERS 65536 1 2147483645 0.06
OWNER OBJECT_NAME TABLESPACE_NAME TS# OBJ# DATAOBJ# OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
------ --------------------------- ----------- ----------- ----------- ------------------ ------------------------------------- -----------
JK EMP USERS 4 9926 9926 TABLE logical reads 0 801 rows selected.
JK EMP USERS 4 9926 9926 TABLE buffer busy waits 1 0
JK EMP USERS 4 9926 9926 TABLE gc buffer busy 2 0
JK EMP USERS 4 9926 9926 TABLE db block changes 3 0
JK EMP USERS 4 9926 9926 TABLE physical reads 4 0
JK EMP USERS 4 9926 9926 TABLE physical writes 5 8
JK EMP USERS 4 9926 9926 TABLE physical reads direct 6 0
JK EMP USERS 4 9926 9926 TABLE physical writes direct 7 0
JK EMP USERS 4 9926 9926 TABLE gc cr blocks received 9 0
JK EMP USERS 4 9926 9926 TABLE gc current blocks received 10 0
JK EMP USERS 4 9926 9926 TABLE ITL waits 11 0
JK EMP USERS 4 9926 9926 TABLE row lock waits 12 0
JK EMP USERS 4 9926 9926 TABLE space used 14 0
JK EMP USERS 4 9926 9926 TABLE space allocated 15 65536
JK EMP USERS 4 9926 9926 TABLE segment scans 17 0
############## data insert 및 delete #####################
insert into emp
select
EMPNO,
ENAME ,
JOB ,
MGR ,
HIREDATE ,
SAL,
COMM,
DEPTNO
from emp,
(select level from dual connect by level <=100) b ;
delete from emp where empno=7369;
############################################################
--- data insert 로 table sizw 가 65K 에서 662M으로 증가--
OWNER Segment Type Tablespace Size Extents MaxExtents Initial(MB) Next(MB)
------------------------- ----------- ----------- ----------- ----------- ----------- -----------
JK EMP TABLE USERS 662700032 150 2147483645 0.06
#############################################################
-- dml 조회 --
select u.name,
o.name,
null,
null,
m.inserts,
m.updates,
m.deletes,
m.timestamp,
decode(bitand(m.flags, 1), 1, 'YES', 'NO'),
m.drop_segments
from sys.mon_mods_all$ m,
sys.obj$ o,
sys.tab$ t,
sys.user$ u
where o.obj# = m.obj#
and u.name ='JK'
and o.obj# = t.obj#
and o.owner# = u.user#
union all
select u.name,
o.name,
o.subname,
null,
m.inserts,
m.updates,
m.deletes,
m.timestamp,
decode(bitand(m.flags, 1), 1, 'YES', 'NO'),
m.drop_segments
from sys.mon_mods_all$ m,
sys.obj$ o,
sys.user$ u
where o.owner# = u.user#
and u.name ='JK'
and o.obj# = m.obj#
and o.type#=19
union all
select u.name,
o.name,
o2.subname,
o.subname,
m.inserts,
m.updates,
m.deletes,
m.timestamp,
decode(bitand(m.flags, 1), 1, 'YES', 'NO'),
m.drop_segments
from sys.mon_mods_all$ m,
sys.obj$ o,
sys.tabsubpart$ tsp,
sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj#
and o.owner# = u.user#
and u.name ='JK'
and o.obj# = tsp.obj#
and o2.obj# = tsp.pobj# ;
-------------------
NAME NAME NULL NULL INSERTS UPDATES DELETES TIMESTAMP DEC DROP_SEGMEN
------ ------- ----- ---------------- ----------- ----------- ------------------- --- -----------
JK EMP 130130 0 1001 2011/03/21 23:10:40 NO 0
###########################################################
-- dml 변동량 TOTAL 조회 --
select
num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name,
inserts, updates, deletes, timestamp, truncated
, to_char(perc_updates, 'FM999,999,999,990.00') perc_updates
from (
select a.*
, nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates
from (
select
(select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows
, (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name
and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed
, (inserts + updates + deletes) tot_updates
, DBA_TAB_MODIFICATIONS.*
from sys.DBA_TAB_MODIFICATIONS
) a
) b
where table_owner='JK';
NUM_ROWS LAST_ANALYZED TOT_UPDATES TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU PERC_UPDATES
----------- ------------------- ----------- -------------- --------------------------------------------------------- ----------- ----------- ------------------- --- -------------------
14014 2011/03/21 22:01:36 131131 JK EMP 130130 0 1001 2011/03/21 23:10:40 NO 935.71
1 rows selected.
###############################################################
SQL> select /*+ rule */ *
from v$segment_statistics where owner='JK' and object_name='EMP';
OWNER OBJECT_NAME TABLESPACE_NAME OBJECT_TYPE STATISTIC_NAME STATISTIC# VALUE
-------------------- ------------------------------------ -------------------------- ----------- -----------
JK EMP USERS TABLE logical reads 0 1554960
JK EMP USERS TABLE buffer busy waits 1 3
JK EMP USERS TABLE gc buffer busy 2 0
JK EMP USERS TABLE db block changes 3 515456
JK EMP USERS TABLE physical reads 4 0
JK EMP USERS TABLE physical writes 5 149815
JK EMP USERS TABLE physical reads direct 6 0
JK EMP USERS TABLE physical writes direct 7 0
JK EMP USERS TABLE gc cr blocks received 9 0
JK EMP USERS TABLE gc current blocks received 10 0
JK EMP USERS TABLE ITL waits 11 0
JK EMP USERS TABLE row lock waits 12 0
JK EMP USERS TABLE space used 14 668258817
JK EMP USERS TABLE space allocated 15 662700032
JK EMP USERS TABLE segment scans 17 16
#################################################################
select table_name,inserts ,to_char(timestamp,'DD-MM-YYYY HH:MM:SS')
from user_tab_modifications where table_name='TSCOTT';
select a.*,b.fts_stmp from v$segment_statistics a,x$ksolsfts b
where a.obj#=b.fts_objd
and a.statistic#=b.fts_statid
and a.owner='JK';
################### 10% 이상 변경된 오브젝트 조회##############
22:00 시에 gather_stats_jobs 스케줄러가 실행되면서 gather_database_state 실행되면서
*_tab_modifications 의 STALE_STATS 상태가 YES 인 object에 대해 통계정보가 수집됨
dml 변경량은 smon이 자동으로 위 딕셔너리에 반영하거나 수동으로 실행할 수 있음
begin
dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
end;
/
--stale 상태의 오브젝트를 조회-
serveroutput on
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'JK', options=>'LIST STALE',objlist=>
mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
####################################################
SQL> select * from user_tab_statistics;
TABLE_NAME SUBPARTITIO OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCK AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_F NUM_FREELIS AVG_CACHED_ AVG_CACHE_H SAMPLE_SIZE LAST_ANALYZED GLO USE STATT STALE_STATS
------------------------ ------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------- --- --- ----- ---
EMP TABLE 14014 79965 0 0 0 37 0 0 14014 2011/03/21 22:01:36 YES NO YES
EMP1 TABLE 370373 4024 72 4100 0 39 0 0 17160 2011/03/22 00:18:22 NO NO NO
EMP2 TABLE NO NO
3 rows selected
#####################################################
SQL> select * from user_tab_statistics;
TABLE_NAME SUBPARTITIO OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCK AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_F NUM_FREELIS AVG_CACHED_ AVG_CACHE_H SAMPLE_SIZE LAST_ANALYZED GLO USE STATT STALE_STATS
----------------------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------- --- --- ----- ---
EMP TABLE 14014 79965 0 0 0 37 0 0 14014 2011/03/21 22:01:36 YES NO YES
EMP1 TABLE 370373 4024 72 4100 0 39 0 0 17160 2011/03/22 00:18:22 NO NO NO
EMP2 TABLE 7021 43 5 1305 0 39 0 0 7021 2011/03/22 00:36:47 NO NO YES
3 rows selected.
########## emp1 table 37만건 삭제 후
begin
dbms_stats.FLUSH_DATABASE_MONITORING_INFO();
end;
/
##############stats_stale yes 변경
SQL> select * from user_tab_statistics;
TABLE_NAME SUBPARTITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCK AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_F NUM_FREELIS AVG_CACHED_ AVG_CACHE_H SAMPLE_SIZE LAST_ANALYZED GLO USE STATT STA
------------------------ ------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------- --- --- ----- ---
EMP TABLE 14014 79965 0 0 0 37 0 0 14014 2011/03/21 22:01:36 YES NO YES
EMP1 TABLE 370373 4024 72 4100 0 39 0 0 17160 2011/03/22 00:18:22 NO NO YES
EMP2 TABLE 7021 43 5 1305 0 39 0 0 7021 2011/03/22 00:36:47 NO NO YES
//해당 오브젝트에 한번이라도 통계정보가 생성되어져야 user_tab_statistics에 기록 되어짐
//dbms_stats 나 analyze가 실행되면 STALE_STATS가 NO로 바뀜
declare
mystaleobjs dbms_stats.objecttab;
begin
-- check whether there is any stale objects
dbms_stats.gather_schema_stats(ownname=>'JK', options=>'LIST STALE',objlist=>
mystaleobjs);
for i in 1 .. mystaleobjs.count loop
dbms_output.put_line(mystaleobjs(i).objname);
end loop;
end;
/
#####################################################################
######### emp1 index 추가 및 컬럼 삭제 #############################
//dml 변동량만 측정되는거 같음..ddl 작업에 의한 상태 변화는 없음
SQL> select * from user_tab_statistics;
TABLE_NAME PARTITION_NAME PARTITION_P SUBPARTITION_NAME SUBPARTITIO OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCK AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_F NUM_FREELIS AVG_CACHED_ AVG_CACHE_H SAMPLE_SIZE LAST_ANALYZED GLO USE STATT STA
------------------------------ ------------------------------ ----------- ------------------------------ ----------- ------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ------------------- --- --- ----- ---
EMP TABLE 14014 79965 0 0 0 37 0 0 14014 2011/03/21 22:01:36 YES NO YES
EMP1 TABLE 0 4024 72 7722 0 0 0 0 0 2011/03/22 10:08:27 NO NO NO
EMP2 TABLE 7021 43 5 1305 0 39 0 0 7021 2011/03/22 00:36:47 NO NO YES
일반적으로 통계정보 수집정책은 업무에 따라 틀리지만 auto 대신 수동으로 오브젝트에 대해 돌리게
되는데 개발자나 해당 업무 DBA가 아닌 이상은 변동량이 많은 오브젝트를 판별하기 쉽지 않다
위 방법으로 리스트를 뽑고 ddl 작업에 대해서는 *_objects의 last_ddl_time 조회로 오브젝트를
뽑아낼 수 있는 방법을 생각해 볼수도 있을듯.
###user_objects 의 last_ddl_time 으로 조회
SQL> select * from user_objects;
OBJECT_NAME OBJECT_ID DATA_OBJECT OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S
-------------------------- ----------- ------------------- ------------------- ------------------- ------------------- ------- - - -
EMP 9926 9926 TABLE 2011/03/21 20:28:02 2011/03/21 20:28:02 2011-03-21:20:28:02 VALID N N N
EMP1 10005 10005 TABLE 2011/03/21 23:56:13 2011/03/22 10:24:01 2011-03-22:10:24:01 VALID N N N
EMP2 10007 10007 TABLE 2011/03/22 00:21:28 2011/03/22 00:21:28 2011-03-22:00:21:28 VALID N N N
EMP1_IND 10008 10008 INDEX 2011/03/22 10:18:34 2011/03/22 10:18:34 2011-03-22:10:18:34 VALID N N N
4 rows selected
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 3 |
4월30일 세미나 자료 - 데이터모델링 표기법
[4]  | suspace | 2011.05.04 | 46694 |
| 2 |
4월30일 세미나 자료 - Query Transformation
[1] | 멋진넘 | 2011.05.03 | 41524 |
| » | 통계정보 수집 관련 test [2] | 오라클잭 | 2011.03.23 | 52403 |
좋은자료 감사합니다.
조금 더 한가해 지시면 디테일하게 해서 통계정보 관련 세미나를 ^^