#1) 테이블 통계
analyze table employees compute statistics for table;
#2) 컬럼 통계 (단, 함부러 수행 하지 않는다.)
analyze table employees compute statistics for all columns size 254;
analyze table employees compute statistics for columns job_id size 20, department_id size 10;
#3) 인덱스 통계
analyze index emp_emp_id_pk compute statistics;
#) 통계 수집 -> 라이브러리 캐시 -> 실행계획(hard parsing) 생성-> latch 잡기 -> 겹합 발생 -> 대기발생
#1) 개별_index
exec dbms_stats.gather_index_stats('HR', 'emp_emp_id_pk')
#2) table
1) cascade => true : 테이블 통계 수집하면서 관련 있는 인덱스 통계 수집도 함께 수행한다.
2) cascade => false: 테이블 통계 수집하면서 관련 있는 인덱스 통계 수집은 하지 않는다.
exec dbms_stats.gather_table_stats('HR', 'EMP', method_opt=>'for columns size 20 job_id', cascade => true);
#) no_invalidate 옵션
exec dbms_stats.gather_table_stats('HR', 'EMP', method_opt=>'for columns size 20 job_id', cascade => true, no_invalidate => true);
no_invalidate: 라이브러리 캐시에 캐싱된 커서를 무효화 할지 결정
no_invalidate => true : 연관된 커서(LCO) 를 무효화 하지 않겠다(10g)
no_invalidate => false : 연관된 커서(LCO) 를 무효화 한다(9i), 경합 발생량 높아짐.
no_invalidate => dbms_stats.auto_invalidate : 정해진 시간동안 조금씩 무효화(10g)
_optimizer_invalidation_period = 18000(초) (히든 파라미터)
<sys sess>
desc col$ Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OBJ# NOT NULL NUMBER
COL# NOT NULL NUMBER
SEGCOL# NOT NULL NUMBER
SEGCOLLENGTH NOT NULL NUMBER
OFFSET NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
LENGTH NOT NULL NUMBER
FIXEDSTORAGE NOT NULL NUMBER
PRECISION# NUMBER
SCALE NUMBER
NULL$ NOT NULL NUMBER
DEFLENGTH NUMBER
DEFAULT$ LONG
INTCOL# NOT NULL NUMBER
PROPERTY NOT NULL NUMBER
CHARSETID NUMBER
CHARSETFORM NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
col$
OBJ#, COL#
join
col_usage$
OBJ#, INTCOL#
desc col_usage$Name Null? Type
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
OBJ# NUMBER
INTCOL# NUMBER
EQUALITY_PREDS NUMBER
EQUIJOIN_PREDS NUMBER
NONEQUIJOIN_PREDS NUMBER
RANGE_PREDS NUMBER
LIKE_PREDS NUMBER
NULL_PREDS NUMBER
TIMESTAMP DATE
■ optimizer
- 사용자가 요청한 SQL문을 가장 효율적이고 빠르고 수행할 수 있는 최저 비용의 처리 경로를 선택해주는 엔진
1. Rule Based Optimization(RBO)
...
2. Cost Based Optimization(CBO)
- oracle 7 부터 지원. 통계정보를 통해서 생성된다.
- cost 를 기반으로 SQL 최적화 수행.
- 실제 SQL을 수행할 때 소요될 비용을 미리 예측하고 그 값을 기준으로 실행 계획을 설정.
- object 통계 정보
#1) table의 통계
select * from dba_tables;
#2) column의 통계
select * from dba_tab_columns;
desc sys.col$
#3) index의 통계
select * from dba_indexes;
- system 통계 정보
cup 속도, disk I/O
■ SYSTEM 통계
- 9i 버전 부터 하드웨어 및 애플리케이션 특성에 맞는 시스템 통계를 수집하고 활용
- i/o, cpu 성능 같은 하드웨어 특성
1) cpu 속도
2) 평균적인 single block i/o 속도, multiblock i/o 속도
3) 평균적인 multiblock i/o 갯수
4) i/o 서브 시스템의 최대 처리량(throughput)
5) 병렬 slave 의 평균적 처리량(throughput)
<sys sess>
select * from sys.aux_stats$;SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ----------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 08-25-2013 05:42
SYSSTATS_INFO DSTOP 08-25-2013 05:42
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2734
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
□ noworkload 시스템 통계(10g)
: 명시적으로 시스템 통계 수집을 하지 않더라도 CPU 비용 모델을 사용할 수 있도록 하기 위해서 오라클이 내부적으로 시스템 통계 설정
CPUSPEEDNW: CPU 속도(백만/초)
IOSEEKTIM: 데이터를 읽으려고 디스크 헤드를 옮기는데 걸리는 시간, 보편적(5 ~ 15ms)
IOTFRSPEED : OS 프로세스, i/o 서브 시스템 으로부터 데이터를 읽는 속도(byte/ms)
□ workload 시스템 통계(9i)
: 실제 애플리케이션에서 발생하는 부하를 측정한 값. 가장 pick한 시간에 체크하는게 좋다
SREADTIM: 평균적 single block i/o 속도(ms=1/1000초)
MREADTIM: 평균적 multi block i/o 속도(ms=1/1000초)
CPUSPEED: 단일 CPU가 초당 수행할 수 있는 오퍼레이션 수(백만/초)
MBRC: multi block i/o 발생시 평균적으로 읽은 블록수
MAXTHR: i/o 서브 시스템의 초당 최대 처리량(byte/초)
SLAVETHR: 병렬 slave의 평균적인 초당 처리량(byte/초)
#) noworkload 시스템 통계 수집
<sys sess>
execute dbms_stats.gather_system_stats(gathering_mode => 'NOWORKLOAD')
select * from sys.aux_stats$;SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ----------- ------------------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 02-23-2024 14:28 -- 변경
SYSSTATS_INFO DSTOP 02-23-2024 14:28 -- 변경
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 2873
SYSSTATS_MAIN IOSEEKTIM 12
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
#) workload 시스템 통계 수집
#) interval: 분단위로 설정 | 1분동안 수집할거야!
execute dbms_stats.gather_system_stats(gathering_mode => 'interval', interval=>1)select * from sys.aux_stats$;SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ----------- ------------------------------
SYSSTATS_INFO STATUS AUTOGATHERING
SYSSTATS_INFO DSTART 02-23-2024 14:30
SYSSTATS_INFO DSTOP 02-23-2024 14:31
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 2873
SYSSTATS_MAIN IOSEEKTIM 12
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SYSSTATS_TEMP SBLKRDS 197322
SYSSTATS_TEMP SBLKRDTIM 419907
SYSSTATS_TEMP MBLKRDS 7337
SYSSTATS_TEMP MBLKRDTIM 67815
SYSSTATS_TEMP CPUCYCLES 2628444
SYSSTATS_TEMP CPUTIM 916064
SYSSTATS_TEMP JOB 521
SYSSTATS_TEMP CACHE_JOB 522
SYSSTATS_TEMP MBRTOTAL 270614
#) 멈추기
execute dbms_stats.gather_system_stats(gathering_mode => 'start')
execute dbms_stats.gather_system_stats(gathering_mode => 'stop')
※ 테스트(개발) 데이터베이스 시스템 통계 정보를 운영 시스템 통계 정보로 운영
=> 맞춰주는게 제일 좋다! 개발 DB = 운영 DB
begin
dbms_stats.set_system_stats('SREADTIM', 1.2);
dbms_stats.set_system_stats('MREADTIM', 1.3);
dbms_stats.set_system_stats('MBRC', 16);
dbms_stats.set_system_stats('CPUSPEED', 700);
dbms_stats.set_system_stats('MAXTHR', 40580544);
dbms_stats.set_system_stats('SLAVETHR', 32224);
end;
/
#) optimizer_mode 확인
<sys sess>
show parameter optimizer_modeNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
■ optimizer_mode
=> ☆통계정보 중요☆
1. choose : 통계 정보 => 있으면 all_rows, 없으면 rule (9i 기본값)
2. rule : 통계 정보와 상관없이 RBO 사용
/* CBO 나오고 부터 나온 것 3가지 */
3. all_rows : 전체 처리율의 최적화(10g 기본값), 보편적으로 설정 => 환경: 분석업무, batch성 업무, 의사소통(dss) 업무 => 선호: full table scan 유도
4. first_rows : 최조 응답속도 최적화 => 환경: oltp성 업무 => 선호: index scan 유도
5. first_rows_n(1,10,100,1000) : 처음 결과가 나올때까지의 시간을 줄이기 위해 최적화 => 환경: oltp성 업무 => 선호: index scan 유도
#) optimizer_mode 설정 변경
<sys sess>
-- 함부로 system set 하지 말것!
alter system set optimizer_mode = first_rows;
#) 파라미터에서 설정, session level에서 진행
alter session set optimizer_mode = first_rows_10;
■ optimizer 에게 영향을 주는 요소
- 오라클 버전
- 옵티마이저 관련 파라미터: all_rows, first_rows
alter system set optimizer_mode = first_rows;
alter system set optimizer_mode = all_rows;- 통계정보
- 옵티마이저 힌트
- SQL문, 연산자
- 인덱스 설계
- 제약조건: primary key, foreign key
SQL문
parser 처리
Quert Transformer -> Estimator -> Plan Generator
1. Quert Transformer : SQL 을 최적화 하기 쉬운 형태로 변환
2. Estimator
- selectivity : 전체 대상 행 중에 특정한 조건에 의해 선택될 것으로 예상되는 row의 수(비율: E-Rows) (1/num_distinct)
- cardinality : 특정 access 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수 (총 로우의 수 * 선택도 = num_rows/num_distinct)
- cost
1) 특정 명령문을 실행하는데 필요한 표준화된 i/o에 대한 옵티마이저의 최적 '예측비용'
2) _optimizer_cost_model(히든파라미터) = {io|cpu|choose}
- i/o 비용(8i) : full scan 으로 진행
- cpu 비용(10g)
- choose 비용: 시스템 통계가 있으면 cpu비용, 없으면 i/o 비용(9i)
3. Plan Generator : 후보군이 될만한 실행계획들을 생성(그 중 저렴한 걸로 리턴)
■ histogram
- 조건절에 자주 사용되는 컬럼들 중에 값의 분포도가 균일하지 않는 컬럼에 히스토그램을 생성한다.
- 버킷의 최대 갯수: 254개
- 히스토그램의 유형
1) 도수분포 히스토그램(Frequency): 값의 수 = 버킷 갯수
2) 높이 균형 히스토그램(Height-Balanced): 값의 수 > 버킷 갯수 | 범위 스캔
- method_opt=>'for all columns size 254' : 모든 컬럼에 히스토그램 생성. 단, 주의해서 사용할 것.
- method_opt=>'for columns size 20 job_id: 하나의 컬럼에 히스토그램 생성.
- method_opt=>'for columns job_id size 20, department_id size 12' : 여러 컬럼에 히스토그램을 생성.
- method_opt=>'for columns size 20 job_id, department_id' : 동일한 버킷 갯수로 여러 컬럼에 히스토그램을 생성.
- 버킷갯수 설정
1) size [integer | repeat| auto | skewonly] : 옵션
2) integer: 1~254
3) repeat: 히스토그램이 기존에 생성돼 있던 컬럼에만 히스토그램 수집
ex) method_opt=>'for all columns size repeat';
4) auto : 컬럼이 조건절에 사용되는 비중이 높은 컬럼(sys.col_usage$, col$)을 찾아 히스토그램 생성. 데이터 분포를 기준으로 자동 결정
ex) method_opt=>'for all columns size auto';
5) skewonly: 데이터 분포를 분석해 균일하지 않은 컬럼에 대해서 히스토그램 생성.
■ 자동 통계 수집 (10g)
select client_name, status from dba_autotask_client;CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection: 무료 ENABLED
(한번도 gather하지 않은 table)
auto space advisor:비용지불(라이센스 구매용) ENABLED
(재구성하는 table 찾아서 권고 해준다 -> 로우마이그레이션 현상 이루어졌을 때의 table, full table scan)
sql tuning advisor:비용지불(라이센스 구매용) ENABLED
(oracle이 직접 sql tunuing 해주기)
#) 설정
select client_name, status, consumer_group, window_group from dba_autotask_client;CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
----------------------------------- -------- ------------------------------ ------------------------------
★ auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS ★
=> 어느 일정 시간에 작업을 운영하고 있는지 체크
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
#) 요일별로 확인 가능
select * from dba_scheduler_wingroup_members where window_group_name = 'ORA$AT_WGRP_OS';WINDOW_GROUP_NAME WINDOW_NAME
------------------------------ ------------------------------
ORA$AT_WGRP_OS MONDAY_WINDOW
ORA$AT_WGRP_OS TUESDAY_WINDOW
ORA$AT_WGRP_OS WEDNESDAY_WINDOW
ORA$AT_WGRP_OS THURSDAY_WINDOW
ORA$AT_WGRP_OS FRIDAY_WINDOW
ORA$AT_WGRP_OS SATURDAY_WINDOW
ORA$AT_WGRP_OS SUNDAY_WINDOW
#) 작업량 확인
select window_name, resource_plan, repeat_interval, duration from dba_scheduler_windows;WINDOW_NAME RESOURCE_PLAN REPEAT_INTERVAL DURATION
------------------------- ----------------------------------- ------------------------------------------------------------ -------------------------
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00
ysecond=0
WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
9 rows selected.
#) 다음 작업 시간까지 확인 가능
select window_name, window_next_time, autotask_status, optimizer_stats, segment_advisor, sql_tune_advisor
from dba_autotask_window_clients;
WINDOW_NAME WINDOW_NEXT_TIME AUTOTASK OPTIMIZE SEGMENT_ SQL_TUNE
------------------------- --------------------------------------------------------------------------- -------- -------- -------- --------
MONDAY_WINDOW 26-FEB-24 10.00.00.000000 PM EST5EDT ENABLED ENABLED ENABLED ENABLED
TUESDAY_WINDOW 27-FEB-24 10.00.00.000000 PM EST5EDT ENABLED ENABLED ENABLED ENABLED
WEDNESDAY_WINDOW 28-FEB-24 10.00.00.000000 PM EST5EDT ENABLED ENABLED ENABLED ENABLED
THURSDAY_WINDOW 22-FEB-24 10.00.00.000000 PM EST5EDT ENABLED ENABLED ENABLED ENABLED
FRIDAY_WINDOW 23-FEB-24 10.00.00.000000 PM EST5EDT ENABLED ENABLED ENABLED ENABLED
SATURDAY_WINDOW 24-FEB-24 06.00.00.000000 AM EST5EDT ENABLED ENABLED ENABLED ENABLED
SUNDAY_WINDOW 25-FEB-24 06.00.00.000000 AM EST5EDT ENABLED ENABLED ENABLED ENABLED
=> if) batch 프로그램이 돌아가면 cpu가 많이 돌아가고 i/o도 많이 돌아가니 사용되지 않는 시간대로 설정한다.
#) 설정
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0');
#) 특정 시간 설정
execute dbms_scheduler.set_attribute(name => 'MONDAY_WINDOW', attribute => 'duartion', value => numtodsinterval(5, 'hour')); --day, hour, minute, second
#) 히스토리 확인
select client_name, job_status, job_start_time, job_duration from dba_autotask_job_history;
# 자동 통계 수집 비활성화
begin
dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
#) 비활성화 확인
select client_name, status, consumer_group, window_group from dba_autotask_client;
CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
----------------------------------- -------- ------------------------------ ------------------------------
auto optimizer stats collection DISABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
# 자동 통계 수집 활성화
begin
dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
end;
/
#) 활성화 되었는지 확인
select client_name, status, consumer_group, window_group from dba_autotask_client;CLIENT_NAME STATUS CONSUMER_GROUP WINDOW_GROUP
----------------------------------- -------- ------------------------------ ------------------------------
auto optimizer stats collection ENABLED ORA$AUTOTASK_STATS_GROUP ORA$AT_WGRP_OS
auto space advisor ENABLED ORA$AUTOTASK_SPACE_GROUP ORA$AT_WGRP_SA
sql tuning advisor ENABLED ORA$AUTOTASK_SQL_GROUP ORA$AT_WGRP_SQ
#) 통계 수집 레벨 확인
show parameter statistics_levelNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL (기본값)
#) 기본값 변경
alter system set statistics_level = typical;
alter system set statistics_level = all;
#) 옵션
statistics_level = [ basic | typical | all ]
- mmon의 역할 : 통계수집을 하는 프로세스
| 옵션 | 해석 |
| statistics_level = basic | 통계 수집 하지 않겠다. |
| statistics_level = typical | 기본적인 일부 통계 수집만 하겠다. |
| statistics_level = all | 가능한 모든 통계 수집을 하겠다. |
#) optimizer_dynamic_sampling 확인
show parameter optimizer_dynamic_samplingNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2 (1 ~ 10, 기본값을 설정하는 것이 제일 best!)
<sys sess>
drop table hr.emp_new purge;
create table hr.emp_new nologging as select employee_id, last_name, salary from hr.employees;explain plan for select * from hr.emp_new where employee_id = 100;select num_rows, blocks, avg_row_len, last_analyzed from dba_tables where table_name = 'EMP_NEW'; NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
---------- ---------- ----------- ---------
explain plan for select * from hr.emp_new where employee_id = 100;
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_NEW | 1 | 40 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
#) 통계 수집
exec dbms_stats.gather_table_stats(ownname =>'hr', tabname => 'emp_new', degree => 2)
select num_rows, blocks, avg_row_len, last_analyzed from dba_tables where table_name = 'EMP_NEW'; NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANAL
---------- ---------- ----------- ---------
107 4 14 23-FEB-24
#) 변경된 정보(들) 확인. 즉, 모니터링 확인.
select * from dba_tab_modifications where table_name = 'EMP_NEW';=> no rows selected
#) emp_new 에 insert 문 실행
insert into hr.emp_new(employee_id, last_name, salary) values(300, 'oracle', 1000);
insert into hr.emp_new(employee_id, last_name, salary) values(301, 'itwill', 2000);
commit;
#) insert문 이후 변경된 정보(들) 확인.: smon이 모니터링 함.
select * from dba_tab_modifications where table_name = 'EMP_NEW';=> no rows selected
#) 모니터링 진행시킴.
해석) smon 일해!
exec dbms_stats.flush_database_monitoring_info
select * from dba_tab_modifications where table_name = 'EMP_NEW' and table_owner = 'HR';TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----------- ----------- --------------- ------------------ ------- ------- ------- --------- --- -------------
HR EMP_NEW 2 0 0 23-FEB-24 NO 0
#) 위의 쿼리문을 실행했을 경우, 출력이 'no rows selected'으로 나왔을 때
select inserts, updates, deletes, truncated, drop_segments,
to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp
from dba_tab_modifications
where table_name = 'EMP_NEW' and table_owner = 'HR'; INSERTS UPDATES DELETES TRU DROP_SEGMENTS TIMESTAMP
---------- ---------- ---------- --- ------------- -------------------
2 0 0 NO 0 2024-02-23 16:21:18
#) update문 실행
update hr.emp_new set salary = salary *1.1 where salary > 10000;
commit;
#) smon 모니터링
exec dbms_stats.flush_database_monitoring_infoselect * from dba_tab_modifications where table_name = 'EMP_NEW' and table_owner = 'HR';TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----------- ----------- --------------- ------------------ ------- ------- ------- --------- --- -------------
HR EMP_NEW 2 15 0 23-FEB-24 NO 0
#) delete문 실행
delete from hr.emp_new where employee_id in (100, 101, 102, 103, 104);
commit;
exec dbms_stats.flush_database_monitoring_infoselect * from dba_tab_modifications where table_name = 'EMP_NEW' and table_owner = 'HR';TABLE_OWNER TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
----------- ----------- --------------- ------------------ ------- ------- ------- --------- --- -------------
HR EMP_NEW 2 15 5 23-FEB-24 NO 0select num_rows, last_analyzed, stale_stats from dba_tab_statistics where table_name = 'EMP_NEW'; NUM_ROWS LAST_ANAL STA
---------- --------- ---
107 23-FEB-24 YESstale_stats = YES : 마지막 통계 수집 이후의 10% 이상의 변화율이 관측된 테이블을 의미한다.
#) 모니터링
exec dbms_stats.gather_table_stats(ownname =>'hr', tabname => 'emp_new', degree => 2)
select inserts, updates, deletes, truncated, drop_segments,
to_char(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp
from dba_tab_modifications
where table_name = 'EMP_NEW' and table_owner = 'HR';=> no rows selected
select num_rows, last_analyzed, stale_stats
from dba_tab_statistics
where table_name = 'EMP_NEW'; NUM_ROWS LAST_ANAL STA
---------- --------- ---
104 23-FEB-24 NO
# stale 상태로 빠지는 변화율의 값을 바꾸는 기능
exec dbms_stats.set_table_prefs('hr', 'emp_new', 'STALE_PERCENT', '40')select * from dba_tab_stat_prefs;OWNER TABLE_NAME PREFERENCE_NAME
------------------------------ ------------------------------ ------------------------------
PREFERENCE_VALUE
--------------------------------------------------------------------------------------------
HR EMP_NEW STALE_PERCENT
40
# 통계 수집을 못하게 락을 거는 방법
exec dbms_stats.lock_table_stats('hr', 'emp_new')
#) lock 이 걸려 있는지 확인
select stattype_locked from dba_tab_statistics where owner = 'HR' and table_name = 'EMP_NEW';STATT
-----
ALL=> ALL: 락이 걸려있다.
#) 오류발생
exec dbms_stats.gather_table_stats(ownname =>'hr', tabname => 'emp_new', degree => 2)BEGIN dbms_stats.gather_table_stats(ownname =>'hr', tabname => 'emp_new', degree => 2); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332
ORA-06512: at line 1=> 해당 table에 lock이 걸려 있어서 통계 수집을 할 수 없다.
# 통계 수집의 lock 해지 방법
exec dbms_stats.unlock_table_stats('hr', 'emp_new')
#) lock 이 걸려 있는지 확인.
select stattype_locked from dba_tab_statistics where owner = 'HR' and table_name = 'EMP_NEW';STATT
-----
#) 실행 성공
exec dbms_stats.gather_table_stats(ownname =>'hr', tabname => 'emp_new', degree => 2)PL/SQL procedure successfully completed.'Data Base > SQL 튜닝' 카테고리의 다른 글
| Range partition, Hash partition, List partition, Composite partition (0) | 2024.02.26 |
|---|---|
| PARTITION, 수동 파티셔닝(manual partitioning) (1) | 2024.02.26 |
| join 조건 pushdown, BLOOM FILTER, 조건절 pushdown (0) | 2024.02.26 |
| SEMI JOIN, ANTI JOIN (0) | 2024.02.23 |
| Query Transformation, 서브쿼리 처리 방식(in, exists) (0) | 2024.02.23 |