www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   회원:0명 / 손님:0명
 

 

Technote

자료 및 관리팁

  • DBA Notes
  • Q & A

    북마크
  • Asktom
       (Oracle 의 대표 Forum)
  • Technical Bulltin(KR)
       (기술지원게시판)
  • Dbazine

  •  

     


     transaction monitoring
    park1q  2009-04-10 10:41:23, 조회 : 3,442, 추천 : 887


    SELECT /*+ ordered no_merge(r) use_hash(t r s p) */
           s.username,       -- 0
           s.sid,            -- 1
           s.serial#,        -- 2
           p.spid,           -- 3
           s.machine,        -- 4
           t.status,         -- 5
           t.recursive,      -- 6
           r.name,           -- 7
           s.module,         -- 8
           s.program,        -- 9
           trunc((sysdate - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 86400) duration, -- 10
           t.used_ublk,      -- 11
           t.used_urec,      -- 12
           t.log_io,         -- 13
           t.phy_io          -- 14
      FROM v$transaction t, v$rollname r, v$session s, v$process p
     WHERE s.saddr = t.ses_addr
       and t.xidusn = r.usn
       and s.paddr = p.addr
     ORDER BY 1 ASC


    ################# 인덱스가 없는 상황에서 INSERT 수행

    insert into t001 select * from NOLOGGING_T;

    CPU used by this session 426
    physical reads 5634
    physical writes 0
    rollback changes - undo records applied 58
    rollbacks only - consistent read gets 57
    session logical reads 70092
    sorts (disk) 0
    sorts (memory) 60
    sorts (rows) 5
    table fetch by rowid 125
    table scan blocks gotten 5632
    table scan rows gotten 946162
    table scans (long tables) 1
    table scans (short tables) 0
    undo change vector size 2547468
    undo segment header was pinned 0
    Used block : 300

    ################# 인덱스 생성후에 INSERT 실시
    CREATE INDEX T001_IX01 ON t001 (EMPNO, ENAME, JOB, MGR) ;
    insert into t001 select * from NOLOGGING_T;

    CPU used by this session 3609
    physical reads 6039
    physical writes 0
    rollback changes - undo records applied 140
    rollbacks only - consistent read gets 60
    session logical reads 2920429
    sorts (disk) 0
    sorts (memory) 74
    sorts (rows) 0
    table fetch by rowid 288
    table scan blocks gotten 5632
    table scan rows gotten 946162
    table scans (long tables) 1
    table scans (short tables) 0
    undo change vector size 145144748
    undo segment header was pinned 0
    Used block : 12084


      추천하기 프린트   목록보기

    Copyright 1999-2018 Zeroboard

     

     
     
    [Today:4 / Total:157069]    Design by p@rk1q