자수성가한 부자

Q : 11g NF 중 하나인 SPM 사용시에 SQL이 baseline에 등록되는 기준은 무엇일까?
    (optimizer_capture_sql_plan_baselines 파라미터를 이용한 자동 등록일 경우)

A : SQL이 두번째 실행될 때 등록됨.

테스트

shared pool flush를 한번 해준다.
alter system flush shared_pool;

파라미터를 설정
SQL> alter system set optimizer_capture_sql_plan_baselines = false;

시스템이 변경되었습니다.

SQL> alter system set optimizer_capture_sql_plan_baselines = true;

시스템이 변경되었습니다.

SQL을 실행한다.
SQL> select /* jgh_test */ * from dual;

D
-
X

SQL> select sql_text
from  dba_sql_plan_baselines
where sql_text like 'select /* jgh_te%';  2    3 

선택된 레코드가 없습니다.

SQL> select sql_text, version_count, executions, loads
from v$sqlarea
where sql_text like  'select /* jgh_te%';  2    3 

SQL_TEXT        VERSION_COUNT EXECUTIONS  LOADS
-------------------------------------------------- ------------- ---------- ----------
select /* jgh_test */ * from dual          1   1      1

같은 SQL을 두번째 실행한다.
SQL> select /* jgh_test */ * from dual;

D
-
X

baseline 뷰에 등록되었는지 확인한다. 등록되었다.

SQL> select sql_text
from  dba_sql_plan_baselines
where sql_text like 'select /* jgh_te%';  2    3 

SQL_TEXT
--------------------------------------------------
select /* jgh_test */ * from dual

실행횟수가 2인 것을 확인할 수 있다. 결론은 하드파싱이 발생횟수가 2회라서 그런 것은 아니고,
실행이 2번째라서 baseline 뷰에 등록이 되었다.

SQL> select sql_text, version_count, executions, loads
from v$sqlarea
where sql_text like  'select /* jgh_te%';  2    3 

SQL_TEXT        VERSION_COUNT EXECUTIONS  LOADS
-------------------------------------------------- ------------- ---------- ----------
select /* jgh_test */ * from dual          1   2      1

다시 파라미터를 원래대로 변경한다.
alter system set optimizer_capture_sql_plan_baselines = false;

추가 질문
버전카운트 증가시에도 등록이 되나?

SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> alter system set optimizer_capture_sql_plan_baselines = true;

시스템이 변경되었습니다.

SQL> select /* jgh_test2 */ * from dual;

D
-
X

SQL> select sql_text
from  dba_sql_plan_baselines
where sql_text like 'select /* jgh_test2%';  2    3 

선택된 레코드가 없습니다.

SQL> select sql_text, version_count, executions, loads
from v$sqlarea
where sql_text like  'select /* jgh_test2%';  2    3 

SQL_TEXT        VERSION_COUNT EXECUTIONS  LOADS
-------------------------------------------------- ------------- ---------- ----------
select /* jgh_test2 */ * from dual          1   1      1

optimizer 관련 파라미터를 변경한다.

SQL> alter system set optimizer_mode = 'FIRST_ROWS';

시스템이 변경되었습니다.

같은 SQL을 두번째 실행한다.
SQL> select /* jgh_test2 */ * from dual;

D
-
X

baseline에 등록되었다.
SQL> select sql_text
from  dba_sql_plan_baselines
where sql_text like 'select /* jgh_test2%';  2    3 

SQL_TEXT
--------------------------------------------------
select /* jgh_test2 */ * from dual

역시 실행횟수가 baseline 등록여부를 결정한다.
optimizer 관련 변수가 변경되어 version_count가 1증가하였다.

SQL> select sql_text, version_count, executions, loads
from v$sqlarea
where sql_text like  'select /* jgh_test2%';  2    3 

SQL_TEXT        VERSION_COUNT EXECUTIONS  LOADS
-------------------------------------------------- ------------- ---------- ----------
select /* jgh_test2 */ * from dual          2   2      2

파라미터를 원래대로 변경
SQL> alter system set optimizer_capture_sql_plan_baselines = false;

시스템이 변경되었습니다.

오라클의 compress 기능
 1. 특성
   - 9iR2에 소개됨.
   - table, index, mview, partition table의 각 partition 에 적용할 수 있음.
   - alter table ~ move 명령어를 사용하면 해당 오브젝트 전체에 lock이 걸림.
   - 압축이 되기 때문에 스토리지 비용절감/IO개선 효과가 있음.
   - insert, delete 에서는 성능차이가 거의 발생하지 않지만, update시에는 많이 발생함.
   - 데이터가 많이 변하고 많은 세션이 있는 oltp에서는 부적합하고,
     데이터가 변화가 거의 없고, 조회성 업무가 많은 DW 환경에서 적합.
   - 방법
     1) direct sql*loader
     2) CTAS
     3) parallel insert
     4) direct insert /*+ append */
 
   - 명령어
     1) 압축
       : alter table <테이블명> move compress;
       : alter table <테이블명> move    partition tablespace <테이블스페이스명> compress;
       : alter index <인덱스명> rebuild partition tablespace <테이블스페이스명> compress;
     2) 해제
       : alter table <테이블명> move nocompress;
       : alter index <인덱스명> rebuild nocompress
    
 2. 테스트
   - 압축율은 어떻게 될까?
     1) 같은 데이터가 많이 있을 경우
    
       select * from v$version where rownum = 1;
      
       BANNER
       ----------------------------------------------------------------
       Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

       drop table t1 purge;
       create table t1 (col1 number, col2 varchar2(100), col3 varchar2(100));
      
       insert into t1
       select level,
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
       from dual
       connect by level <= 100000;
      
       commit;
      
       create table com_t1
       as
       select * from t1 where 1=2;
      
       alter table com_t1 move compress;
        => 그냥 alter table com_t1 compress; 는 안됨.
      
       insert /*+ full append */ into com_t1
       select * from t1;
      
       commit;
      
       select segment_name, bytes
       from dba_segments
       where segment_name in ('T1','COM_T1');
      
       SEGMENT_NAME       BYTES
       ------------------------------ ----------
       T1     17825792
       COM_T1     15728640  
      
     2) 같은 데이터가 거의 없을 경우
    
   
       drop table t2 purge;
       create table t2 (col1 number, col2 varchar2(100), col3 varchar2(100));
      
       insert into t2
       select level,
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
       from dual
       connect by level <= 25000
       union all
       select level,
              'cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc',
              'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
       from dual
       connect by level <= 25000
       union all
       select level,
              'eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee',
              'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
       from dual
       connect by level <= 25000
       union all
       select level,
              'gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg',
              'ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
       from dual
       connect by level <= 25000;
      
       commit;
      
       create table com_t2
       as
       select * from t2 where 1=2;
      
       alter table com_t2 move compress;
      
       insert /*+ append */ into com_t2
       select * from t2;
      
       commit;
      
       select segment_name, bytes
       from dba_segments
       where segment_name in ('T2','COM_T2');
    
            SEGMENT_NAME       BYTES
       ------------------------------ ----------
       T2     17825792
       COM_T2     15728640

       => 똑같네.ㅠㅠ
   
      ※ 만약에 append 힌트를 안썼을 경우는?
         => 결과는 같음. 뭐니??
        insert into com_t2
        select * from t2;
   
            SEGMENT_NAME       BYTES
        ------------------------------ ----------
        T2     17825792
        COM_T2     15728640
   
      3) 테이블생성시에 compress 옵션을 주게 되면??
     
        create table init_com_t2
        compress
        as
        select * from t1;
      
        select segment_name, bytes
        from dba_segments
        where segment_name in ('T2','COM_T2','INIT_COM_T2');
      
        SEGMENT_NAME       BYTES
        ------------------------------ ----------
        INIT_COM_T2     2097152
        T2     17825792
        COM_T2     15728640
      
        => 훨씬 압축율이 좋다.
           88%, 12%이다.
  
 ※ 참고사이트
    http://ej5811.blog.me/80112840894
    http://blog.naver.com/conifer7?Redirect=Log&logNo=30093121932

여기서는 DDB(분산 데이타베이스)의 기능과 역할 및 사용 시에 발생하는 문제점들에 대해서 알아보기로 한다.　 분산 DB 환경과 관련된 init.ora 화일의 파라미터는 다음과 같은 것들이 있다.　　　　

DB_NAME　　　　　
DB_DOMAIN　　　　　
GLOBAL_NAME　　　　　
DISTRIBUTED_RECOVER_CONNECTION_HOLD_TIME　　　　　
DISTRIBUTED_TRANSACTIONS　　　　　
DISTRIBUTED_LOCK_TIMEOUT　　　　　
COMMIT_POINT_STRENGTH　　　　　
OPEN_LINKS　

　　　 분산 DB와 관련된 VIEW와 TABLE은 다음과 같다.　　　　

GLOBAL_NAME　　　　　
DBA_DB_LINKS, ALL_DB_LINKS, USER_DB_LINKS　　　　　
DBA_2PC_PENDING　　　　　
DBA_2PC_NEIGHBORS　

　 RECO:Recoverer Process　　　　

　 분산 트랜잭션의 Failure를 해결하는 Process로 RECO는 In-doubt 분산 트랜잭션을 가지고 있는 다른 DB에 자동적으로 Connect 하여 모든 In-doubt 트랜잭션을 해결한다.　　　　
　만일 RECO가 Remote Server에 Connection을 시도했는데 Remote Server가 사용 가능하지 않거나 Network가 복구 되어 있지 않을 경우 RECO는 일정한 시간 후에 다시 Connect를 시도한다.　　　　
　　　　

　 DATABASE LINK　　　　

1. Database Link에는 Private Database Link와 Public Database Link의 두 종류가 있는데 일반적으로 Public Database Link로 정의하여 사용한다.　　　　　
2. Public Database Link　　　　
　 Local 데이타베이스의 모든 사용자가 Remote 데이타베이스의 데이타를 사용 할 수 있다. 이 경우 Remote Database에 대해서 필요한 권한을 가지고 있어야 한다.　　　　　

3. Public Database Link Creation　　　　
EX ) CREATE PUBLIC DATABASE LINK sales.acme.com USING ´dbstring´;　　　　

4. Dropping Databsae Link　　　　
EX ) DROP PUBLIC DATABASE LINK sales.acme.com;　　　　
　　　　

　 Two-Phase Commit의 구성　　　　

　　 Two-Phase Commit은 Prepare Phase와 Commit Phase로 나누어 진다.　　　　

　1. Prepare Phase　　　　　
　 Global Coordinator(분산 Transaction을 일으킨 Node)가 분산 Transaction에 참석한 Node들에 대해 Commit이나 Rollback을 수행해도 되는가를 확인하는 단계이다.　　　　

　2. Commit Phase　　　　
　분산 Transaction에 참여한 Node가 Global Coordinator에게 분산 Commit해도 좋다는Response(즉 Prepare 되었음)를 받고 Transaction을 Commit한다. 어떠한 Node라도 Prepare가 안되었다는 Response가 있으면 Transaction을 Rollback한다.　　　　

　 Two-Phase Commit의 수행　　　　

　1. Prepare Phase에 대한 Response는 Prepared, Read-Only, Abort가 있다.　　　　
　　　 A. Prepared는 Data가 정상적으로 수정되었으며 Prepare되었음을 의미한다.　　　　
　　　 B. Read-Only 는 해당Node에서 Data의 수정이 없었다는 의미이다.　　　　
　　　 C. Abort는 Prepare되지 않았음을 의미한다.　　　　

　2. 모든 Node에서 Prepared가 되면 Commit Phase가 수행된다.　　　　
　　 그러나 한 Node라도 Abort되면 Commit Phase에서 전 Node에 Rollback을 수행한다.　　　　

　 Two-Phase Commit 메카니즘　　　　

　예를 들어 다음과 같은 경우를 가정해 보자　　　　

SQL> update 부서@Pusan　　　　
　　　　　　　 set 부서이름 = 기술연구소　　　　
　　　　 where 부서코드 = 40;　　　　

SQL> update 사원@Seoul　　　　
　　　　　　　 set 부서코드 = 40;　　　　
　　　　 where 사번 = 777;　　　　

SQL> commit;　　　　<-- 현재 시점에서 부서@Pusan 테이블은 변경이 됐지만, 사원　　　　
　　　　　　　　　　　@Seoul 테이블을 변경하다가 네트웍 장애로 인해 변경이 안됐을 경우.

이런 경우에 오라클은 Two-Phase commit 알고리즘에 따라 자동으로 모두 롤백시킨다. 한 트랜잭션안에 remote update나 distributed update등이 있을때, commit이나 rollback을 실행하는 시점에서 트랜잭션의 모든 변경된 내용이 동시에 commit되거나 rollback된다.　　　　　
　　　　

　　　 분산처리 문제와 해결 방법　　　　

1. Two-Phase Commit을 인터럽트하는 Failures　　　　

[ 현상 ]　　　　
ORA-02050: Transaction ID rolled back, some remote dbs may be in-doubt　　　　
ORA-02051: Transaction ID committed, some remote dbs may be in-doubt　　　　
ORA-02054: Transaction ID in-doubt　　　　

　 만일 프로그램이 사용 중 위의 에러 가운데 하나가 발생한다면 그 트랜잭션에 대한 정보가 자동적으로 저장되며 이 정보는 후에　 분산 트랜잭션에 대한 Manual Recovery 시에 사용될 수 있다.　　　　　

[ 조치 방법 ]　　　　
　 DBA는 특별한 Action을 취할 필요가 없다. RECO Process가 Session Tree의 모든 노드에서 같은 결과가 발생되도록 (즉 모두 Commit 혹은 모두 Rollback) In-doubt 트랜잭션을 자동적으로 Recovery 한다. 그러나 장기적인 Power Failure인 경우 Lock된 리소스를 Release 하기 위해 강제적으로 Commit나 Rollback을 시켜준다.　　　　　

2. 데이타의 Access를 금지시키는 Failure　　　　

[ 현상1. ]　　　　
Transaction Time-out　　　　
ORA-02049: time-out : distributed transaction waiting for lock　　　　
　　　　　
　 Local의 DML 문장이 Remote의 다른 Transaction에 의해 Lock 되어 있는 데이타를 수정 및 삭제 하려고 할 때 Time-out이 발생하고 그 명령은 Rollback 된다.　　　　　

(Scenario)　　　　
　[ in REMOTE ]　　　　
SQL>update dept set deptno=10;　　　　
　[ in LOCAL ]　　　　
SQL>update dept set deptno=10;　　　　
SQL>update dept@ORA7 set deptno=10;　　　　

ORA-02049 : time-out　　　　

[ 조치 방법 ]　　　　
　　 Local DB에는 Update가 발생하므로 항상 Rollback을 시켜야 한다. Remote DB는 변경되지 않으므로 Time-out의 결과로 다른 조치는 필요 없다. 위 상황에서 Time-Out Interval 은 DISTRIBUTED_LOCK_TIMEOUT 파라미터로 Time Out 시간을 조정할 수 있다.　　　　　

　[ 현상2. ]　　　　
Lock from In-doubt Transaction　　　　
ORA-01591 : Lock held by in-doubt distributed transaction ID　　　　

　 Local DB에 Lock을 발생시키는 DML 문장을 In-doubt 분산 트랜잭션에 의해　　　　
Lock된 Resource에 계속해서 실행할 경우 발생하는 에러　　　　

(Scenario)　　　　
(1) update dept@ORA7 set dname=´AA´;　　　　
(2) Remote DB down　　　　
(3) Commit　　　　
　　　　 ORA-02054 : transaction 2.1.207 is in-doubt transaction　　　　
(4) select * from dept;　　　　
　　　 ORA-01591 : Lock held by in-doubt distributed transaction id　2.1.207
　 Local에서 dba_2pc_pending으로부터 In-doubt 트랜잭션 정보를 확인할 수있다.

　[ 조치 방법 ]　　　　
　이 경우 SQL 문장은 즉시 Rollback 되 만일 Lock이 계속해서 걸려있으면　　　　　
DBA에게 알리도록 한다. 이런 현상은 드물게 발생하며 Network나 System　　　　　
Failure가 빠르게 복구되면 문제는 자동적으로 해결된다.　　　　

3. 두개 NODE 사이에 Connection이 만들어진 이후에　 Network Failure가 발생한 경우　　　　

(Scenario)　　　　
(1) Forms 화면에서 Data Update　　　　
(2) Commit - DB Trigger에 의해서 Server Machine에 Server process가 생기며
Network 와 Remote DB 가 정상적인 경우 Commit 된다.　　　　　
(3) Network Fail(Line 을 Disconnect)　　　　
(4) Forms 화면에서 Update　　　　
(5) Commit　　　　
(6) Forms Screen이 Holding 상태로 됨　　　　
　　　　

[ 조치 방법 ]　　　　　
　 Network이 복구되면 자동적으로 Commit 되지만 Network Failure가 장기화될 경우 강제로 Abort 시키는 조치가 필요하다. 강제로 Abort 이후 자동적으로 Rollback 되므로 더이상 조치가 필요하지 않다.　　　　　

4. Network이 Down된 상태에서 Connection을 시도하려는 경우　　　　

(Scenario)　　　　
(1) Network Down　　　　
(2) Forms 화면에서 Data 수정　　　　
(3) Commit　　　　

[조치 방법]　　　　
　 모든 Transaction이 Rollback 되므로 더 이상 조치가 필요하지 않으나 다만 Network이 Down된 상태에서 Connect를 시도하려고 하는 시간을 줄여서 곧바로 Error가 발생되도록 해야 한다.

5. Server Machine의 orasrv가 Down된 상태에서 Connect를 하려고 하는 경우　　　　　
　　　　　 : ORA-06108 error 발생　　　　

6. In-doubt 트랜잭션을 Manually Overriding　　　　
　　 ORA-01591 Message가 발생하여 User가 조치를　 요구하는 경우　　　　　
　　 In-doubt 트랜잭션이 Rollback Segment의 Extension를 막는 경우　　　　
　　 Network 및 System Failure가 장기화 될 경우　　　　

　 In-doubt 트랜잭션에 대한 정보를 얻는 방법　　　　
　　　　　　　 a.User Feedback을 기록　　　　
　　　　　　　 b.Local dba_2pc_pending View를 조회　　　　
　　　　　　　 c.Local dba_2pc_neighbors View를 조회　　　　
　　　　　　　 d.정상적으로 통신이 복구된 후에 Mixed Outcome Flag를 Check　　　　

　 Record User Feedback　　　　

ORA-01591: Lock held by in-doubt distributed transaction 1.21.17　　　　
1.21.17 : local transaction id　　　　

　 Query dba_2pc_pending　　　　
　　　　　　　 < global_db_name format >　　　　
　　　　　　　 global_database_name.hhhhhhhh.local_transaction_id　　　　
　　　　　　　 cf ) global_database_name=global coordinator db name　　　　
　　　　　　　　　　　　 hhhhhhhh=internal db id　　　　

　 Query dba_2pc_neighbors　　　　
　 In-doubt 트랜잭션이 관련되어 있는 Connection에 대한 정보를 조회한다. 각 Connection에 대한 정보는 Connection이 Inbound, Outbound 이냐에 따라 다르다.　　　　　

　　 만일 Connection이 Inbound이면 그 Node는 다른 Node 의 Server이다. 만일 Connection이 Outbound이면 그 Node는 다른 Server의 Client이다. Interface Column 은 Local Node 혹은 Subordinate Node가 Commit Point Site 인지를 알려준다.　　　　　
만일 Local Transaction id와 Global Transaction id가 일치하면 그 Node 는 Global Coordinator이다.　　　　　

　 Check for Mixed Outcome　　　　
　 트랜잭션이 Manual로 Commit 혹은 Rollback된 후에 해당 Row가 Pending 트랜잭션 테이블에 남아있다. 그 트랜잭션의 상태는 "forced commit" or "forced abort" 로 변경되어지고 Instance 사이에 Connection이 복구되면 RECO는 트랜잭션의 Global Outcome을 Check하여 정상적인 경우 Row를 삭제하지만 틀린 방법으로 트랜잭션을 Force 시키면 삭제되지 않고 Mixed Column이 "Yes" 로 변경된다.　　　　　

7. Manual Commiting In-Doubt Transaction　　　　

　　　　　　　 COMMIT FORCE ´SALES.ACME.COM.55dic563.193.29´;　　　　
　　　　　　　 COMMIT FORCE ´global_id´, 829381993 (system commit number)　　　　

8. Manually Rolling Back In-Doubt Transaction　　　　
　　　　　　　 ROLLBACK FORCE ´2.9.4´;　　　　

9. Disabling and Enabling RECO　　　　

　　　　　　　 ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;　　　　
　　　　　　　 ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;　　　　

10. Commit Point Site 결정　　　　
　　　　　　　 첫번째 Commit하는 Node　　　　
　　　　　　　 In-Doubt Data를 가질 수 없다.　　　　　
　　　　　　　 Prepared 상태로 될 수 없다.　　　　　
　　　　　　 분산 데이타베이스에 참여하는 시스템 중 가장 안정된 시스템에 가장 높은
값을 준다.　　　　



출처 : http://www.intercraft.co.kr/data/detail.php?code=1&rowid=8

1. audit trail 설정

현재 테스트하는 오라클의 버전을 확인

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

현재 audit_trail 파라미터의 값을 확인한다. 현재는 DB로 설정되어 있어
audit 정보가 테이블에 저장됨을 확인

SQL> show parameter audit_trail 

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_trail        string  DB


test 유저의 t1테이블에 select가 일어날 경우 audit을 실행하도록 설정한다.


SQL> audit select on test.t1;

그리고 sql_trace를 실행하기 위하여 test 유저에 alter session 권한을 부여한다.

SQL> grant alter session to test;

권한이 부여되었습니다.

test 유저로 접속

SQL> conn test/test

세션에 sql trace 설정.

SQL> alter session set sql_trace=true;

SQL> select * from t1;

      COL1 COL2
---------- ----------
  1 test
  2 test
..
..
..

sql trace를 disable 시켜준다.

SQL> alter session set sql_trace=false;

trace 파일이 쌓이는 user_dump_dest의 위치를 확인한다.

SQL> conn / as sysdba
SQL> show parameter user_dump_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest        string  /oracle/admin/jgh/udump

SQL> exit

user_dump_dest로 이동하여 tkprof 툴을 이용하여 트레이스 파일을 포맷팅한다.

# cd /oracle/admin/jgh/udump
# tkprof jgh_ora_15594.trc audit_on.out

결과는 아래와 같다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       93      0.24       1.37          0          0          0           0
Execute   1550      3.75       6.11         18       4620          0        1507
Fetch       43      0.14       0.40          4        346          0          42
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1686      4.14       7.88         22       4966          0        1549

Misses in library cache during parse: 9
Misses in library cache during execute: 7

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      172      0.19       0.21          0          0          0           0
Execute   3314      1.94       2.83         17        759        720         237
Fetch     3301      2.09       3.50         50       5533          0        1746
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6787      4.24       6.55         67       6292        720        1983

Misses in library cache during parse: 15
Misses in library cache during execute: 14
 
  122  user  SQL statements in session.
  179  internal SQL statements in session.
  301  SQL statements in session.


2. audit trail 설정 안되어있을 경우


audit_trail 파라미터를 none으로 설정한 후 DB 재시작

SQL> alter system set audit_trail=none scope=spfile;

SQL> startup force;

변경된 파라미터 확인

SQL> show parameter audit_trail

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
audit_trail        string  NONE

SQL> conn test/test

SQL> alter session set sql_trace=true;

세션이 변경되었습니다.

SQL> select * from t1;

      COL1 COL2
---------- ----------
  1 test
  2 test
  3 test
..
..
..

SQL> alter session set sql_trace=false;

세션이 변경되었습니다.

SQL> conn / as sysdba

SQL> show parameter user_dump_dest

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest        string  /oracle/admin/jgh/udump

SQL> exit

$ cd /oracle/admin/jgh/udump
$ tkprof jgh_ora_16172.trc audit_off.out

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       19      0.15       0.38          0          0          0           0
Execute    119      0.41       1.06         15        555          0         113
Fetch        6      0.02       0.47          4         50          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      144      0.59       1.92         19        605          0         118

Misses in library cache during parse: 9
Misses in library cache during execute: 7

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      163      0.14       0.23          0          0          0           0
Execute    398      0.38       0.50         13         69         45          19
Fetch      528      0.39       0.42         30       1048          0         338
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1089      0.92       1.16         43       1117         45         357

Misses in library cache during parse: 15
Misses in library cache during execute: 14

   48  user  SQL statements in session.
  160  internal SQL statements in session.
  208  SQL statements in session.

최종 비교

audit_trail = on일 경우  NON-RECURSIVE

SQL> select sid,event,seconds_in_wait from v$session where username='DONGHUA' and status='ACTIVE';


       SID EVENT                                    SECONDS_IN_WAIT

---------- ---------------------------------------- ---------------

        44 enq: TS - contention                                  21

SQL> select * from v$lock where request>0;


ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST

-------- -------- ---------- -- ---------- ---------- ---------- ----------

     CTIME      BLOCK

---------- ----------

3E68104C 3E681078         44 TS          7          1          0          6

        29          0




SQL> select sid from v$lock where id1=7 and id2=1;


       SID

----------

        13

        44


SQL> select program,status from v$session where sid=13;


PROGRAM                                          STATUS

------------------------------------------------ --------

oracle@vmxdb01.lab.dbaglobe.com (SMON)           ACTIVE


SQL> select sid,event,seconds_in_wait from v$session where sid=13;


       SID EVENT                                    SECONDS_IN_WAIT

---------- ---------------------------------------- ---------------

        13 smon timer                                            87

SQL> SELECT se.username username,

  2  se.SID sid, se.serial# serial#,

  3  se.status status, se.sql_hash_value,

  4  se.prev_hash_value,se.machine machine,

  5  su.TABLESPACE tablespace,su.segtype,

  6  su.CONTENTS CONTENTS

  7  FROM v$session se,

  8  v$sort_usage su

  9  WHERE se.saddr=su.session_addr;


USERNAME                              SID    SERIAL# STATUS   SQL_HASH_VALUE

------------------------------ ---------- ---------- -------- --------------

PREV_HASH_VALUE MACHINE

--------------- ----------------------------------------------------------------

TABLESPACE                      SEGTYPE   CONTENTS

------------------------------- --------- ---------

DONGHUA                                41        259 INACTIVE              0

     2640221370 WORKGROUP\ORACLE-PC

TEMP2                           LOB_DATA  TEMPORARY

SQL> alter system kill session '41,259';


System altered.

출처 : http://www.dbaglobe.com/2010/08/drop-temporary-tablespace-hang-with-enq.html

Q :

10g가 되면 dba_scheduler_jobs라는 뷰가 생겨 스케쥴링되는 job들을 확인할 수 있습니다.

그런데 제가 등록하지 않은 AUTO_SPACE_ADVISOR_JOB라는 job이 디폴트로 등록되어 있는데, 이 job은 무엇일까요?


A :

Auto Space Advisor는 Advisor의 한 종류로 10g에서 새로 추가된 Advisor이다. 이 Advisor는 Tablespace/Segment의 공간 사용 정도를 분석하여 공간을 절약하기 위해 적절한 충고/가이드를 하는 역할을 제공한다.

=> AUTO_SPACE_ADVISOR_JOB은 Auto Space Advisor가 관련 데이터를 수집하기 위한 job이라고 보면 된다.

다음은 10g에서 확인한 내용이다.

기타 :
AUTO_SPACE_ADVISOR_JOB 잡과 관련하여 10.2.0.3버전에서 잘못된 block corruption 리포팅과 거대한 trace 파일이 생성되므로
이 잡을 enable시키는 것을 권고함.

•deactivating the AUTO_SPACE_ADVISOR_JOB

SQL> execute dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

•limiting the size of trace files via max_dump_file_size

http://ora-mystery.com/2008/10/15/auto_space_advisor_job-is-generating-huge-tracefiles-and-reports-wrong-block-corruptions/


참고 : http://ukja.tistory.com/89

참조 : http://kr.forums.oracle.com/forums/thread.jspa?messageID=1692499&#1692499