Oracle Events
Flush Buffer Cache
Oracle 10 g 에서는 다음 명령을 이용해서 Buffer Cache를 Flush한다.
- alter system flush buffer_cache;
또는 특정 Tablespace를 Offline후 Online하면 해당 Tablespace에 속하는 Buffer들이 Flush된다.
10053 Event (CBO Enable optimizer trace)
CBO의 Optimization 과정을 Trace 파일에 출력한다. Syntax는 다음과 같다.
- alter session set events '10053 trace name context forever, level 1';
10104 Event (dump hash join statistics to trace file)
Hash Join 수행 과정을 Trace 파일에 출력 상세한 정보를 조회한다.
Hash Join을 Troubleshooting할 경우에 주로 사용된다.
Syntax는 다음과 같다.
- alter session set events '10104 trace name context forever, level 1';
10195 Event (Disable generation of predicates from CHECK constraints)
CHECK Constraint에 의한 불필요한 fiterting 적용이 발생하는 것을 방지하는 역할을 한다.
Syntax는 다음과 같다.
- alter session set events '10195 trace name context off';
- alter session set events '10195 trace name context forever, level 1';
10195 Event의 사용 예는 다음과 같다.
-- Object 생성 create table t_const1(id number, name varchar2(10), reg_date date); create index t_const1_idx on t_const1(name); -- CHECK Constraint 추가 alter table t_const1 add constraint c1 check(name = trim(name)); -- Plan 확인 explain plan for select count(*) from t_const1 where name = '0987654321'; PLAN_TABLE_OUTPUT Plan hash value: 225932663 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| T_CONST1_IDX | 1 | 7 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"='0987654321') filter(TRIM("NAME")='0987654321') <-- 불필요한 Filtering 추가 alter session set events '10195 trace name context forever, level 1'; PLAN_TABLE_OUTPUT Plan hash value: 225932663 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| T_CONST1_IDX | 1 | 7 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"='0987654321') <-- 불필요한 Filtring 제거됨
Check Constraint에 의한 Filtering 추가는 일반적으로 성능에 유리한 경우가 많다.
Filtering에 의해 데이터가 걸러지면 그만큼 일량이 줄어들 가능성이 있기 때문이다.
특정 쿼리 조건에서는 이러한 Filtering이 오히려 부작용이 있을 수 있으면 그런 특별한 경우에는 10195 Event를 이용해서 Check Constraint에 의한 Filtering 조건 추가를 방지할 수 있다.
10200 Event(consistent read buffer status)
Oracle의 Logical Reads를 Trace하는 기능을 제공한다.
Logical Reads의 작동 메커니즘이나 버그에 의한 Logical Reads 이상 현상을 추적할 때 많이 사용된다.
Syntax는 다음과 같다.
- alter session set events '10200 trace name context forever, level 1';
- alter session set events '10200 trace name context off';
Logical Reads의 Trace 결과는 다음과 같다.
SQL> alter session set events '10200 trace name context forever, level 1';
SQL> select count(*) from t;
SQL> alter session set events '10200 trace name context off';
Consistent read started for block 0 : 00400a72 env: (scn: 0x07df.1aadbf59 xid: 0x0004.009.0000054f uba: 0x008002de.04ac.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x07df.1aad99e4 0sch: scn: 0x0000.00000000) CR exa ret 2 on: 03C44148 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read finished for block 0 : 400a72 Consistent read started for block 0 : 00400a75 env: (scn: 0x07df.1aadbf59 xid: 0x0004.009.0000054f uba: 0x008002de.04ac.05 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x07df.1aad99e4 0sch: scn: 0x0000.00000000) CR exa ret 2 on: 03C44148 scn: 0xffff.ffffffff xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0xffff.ffffffff sfl: 0 Consistent read finished for block 0 : 400a75
10626 Event(specify timeout for online index rebuild to wait for DML)
Online Rebuild 작업이 선행 Transaction에 의해 Blocking된 경우 대기 시간(Timeout)의 최대치를 설정한다.
Change the behaviour of an online index rebuild such that it will timeout, rather than wait indefinitely for existing DML to complete. The event level is the number of seconds the online index rebuild should wait. Values outside the legal range of 2 to 300 seconds will be adjusted automatically.
10629 Event(force online index build to backoff and retry DML lock upgrade)
Online Rebuild시 DML Lock(TM Lock) Blocking을 줄이기 위해 TM Lock을 SS->S로 Upgrade하는 방식을 제어한다. Oracle이 제공하는 설명은 다음과 같다.
Change the behaviour of an online index rebuild such that it will backoff and retry a failed DML lock upgrade. The event level is the number of retries the online index rebuild should wait. Level 1 means backoff and retry indefinitely. Any other value less than 32 will be adjusted automatically to be 32.
즉, Online Rebuild를 수행하는 Session이 TM Lock을 S(4)로 획득하지 못하면, 대기 상태에 빠지는 방법으로 TM Lock에 의한 Blocking을 회피한다. 사용법은 다음과 같다.
- alter session set events '10629 trace name context forever, level 1';
- alter session set events '10629 trace name context forever, level 32';
Online Rebuild 작업이 중간에 종료되는 경우 Index의 상태가 정리(Clean)되지 않아서 이후에 Index에 대한 DDL이 수행되지 않을 수 있다. 이 경우 ORA-8014 Error가 발생한다.
Oracle 10'g'부터는 dbms_repair.online_index_clean 함수를 이용해서 Online Rebuild에 실패한 Index의 상태를 정리할 수 있다.
10704 Event(Print out information about what enqueues are being obtained)
Enqueue 획득과 해제에 대한 Debugging 정보를 출력한다.
특정 Operation이 어떤 Enqueue를 필요로 하며, 얼마나 오래 점유하는지 알 수 있다.
Enqueue Blocking 현상을 Troubleshooting할 때 사용할 수 있다.
Syntax는 다음과 같다.
- alter session set events '10704 trace name context forever, level 10';
- alter session set events '10704 trace name context off';
Event 레벨은 다음과 같다.
- 1-4: print out basic info for ksqlrl, ksqcmi
- 5-9: also print out stuff in callbacks: ksqlac, ksqlop
- 10+: also print out time for each line
간단한 DML에 대한 Trace 결과는 다음과 같다.
CU, FB, HW, TM, TX Lock을 점유하고 해제하는 일련의 작업을 Debugging할 수 있다.
alter session set events '10704 trace name context forever, level 10';
insert into t1 values(1); alter session set events '10704 trace name context off';