발표 통계정보 수집 관련 test

2011.03.23 20:03

오라클잭 조회 수:20001

안녕하세요 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