oracle oracle internal DBA oracle document oracle concepts oracle document oracle DBWR update t set object_id = '0' where object_id = '12344'; 1 row updated. commit; Commit complete. 0 12344 12344 0 10%
12344 0 transaction rollbacks/( transaction rollbacks + user commits) select name,value from v$sysstat where name in ('user commits','transaction rollbacks'); NAME VALUE ---------------------------------------------------------------- ---------- user commits 12532 transaction rollbacks 21 delete update, insert rowid delete, update insert rowid consistent reads oracle Oracle SCN SCN, SCN SCN T COMMIT SCN T COMMIT SCN T COMMIT SCN SYS select count(*) from x$bh where state = 3; COUNT(*) ---------- 15
SGA SGA oracle8 x$bh v$rollstat x$bh x$bh class USN n, class 11+2n 12+2n select usn from v$rollstat; USN ---------- 0 1 2 3 4 5 6 7 9 9 rows selected. 8 9 select class,count(*) from x$bh where class > 10 group by class; CLASS COUNT(*) ---------- ---------- 11 1 12 2 13 1 14 1 15 1 16 1 17 1 18 982 19 1 20 1 21 1 CLASS COUNT(*) ---------- ---------- 22 1
23 1 24 1 25 1 26 1 29 1 30 1 18 rows selected. 8 class 27 28 class=18(usn=3) delete from t; 25374 rows deleted. commit; Commit complete. select SEGMENT_ID,SEGMENT_NAME from dba_rollback_segs; SEGMENT_ID SEGMENT_NAME ---------- ------------------------------ 0 SYSTEM 1 RBS0 2 RBS1 3 RBS2 4 RBS3 5 RBS4 6 RBS5 7 RBS6 9 RBS12 9 rows selected.
set transaction use rollback segment rbs6; Transaction set. insert into t select * from all_objects; 25649 rows created. commit; Commit complete. DBA create drop truncate 2 extent block extent 1 2 3 4 5 extent 1 block extent 5 extent 1 block Extent select usn,wraps from v$rollstat; USN WRAPS ---------- ---------- 0 0 1 15 2 15 3 15 4 15 5 12 6 15
7 17 9 12 9 rows selected. extent 3 extent 2 extent 2 extent 3 extent 4,5,1 extent 3 extent extent extent 2-1 extent extent 2-1 2-1 extent extent 3 2-1 extent 3 SQLPLUS 1 ( v$rollstat dba_rollback_segs usn =5 rbs4) select a.usn,b.segment_name from v$rollstat a,dba_rollback_segs b 2 where a.usn = b.segment_id; USN SEGMENT_NAME ---------- ------------------------------ 0 SYSTEM 1 RBS0 2 RBS1 3 RBS2 4 RBS3 5 RBS4 6 RBS5 7 RBS6 9 RBS12 9 rows selected. select usn,rssize "rollback segment size" from v$rollstat where usn = 5; USN rollback segment size ---------- --------------------- 5 4186112 set transaction use rollback segment rbs4;
Transaction set. update t_small set object_id = 1; 100 rows updated. SQLPLUS 2 begin for i in 1..1000 loop set transaction use rollback segment rbs4; update t set object_id = i where rownum < 101; commit; end loop; end; select usn,rssize "rollback segment size" from v$rollstat where usn = 5; USN rollback segment size ---------- --------------------- 5 55042048 session select usn,rssize "rollback segment size" from v$rollstat where usn= 4; USN rollback segment size ---------- --------------------- 4 4186112 begin 2 for i in 1..1000 loop 3 set transaction use rollback segment rbs3; 4 update t set object_id = i where rownum < 101; 5 commit; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. select usn,rssize "rollback segment size" from v$rollstat where usn= 4; USN rollback segment size ---------- ---------------------
4 4186112, 4M select usn,rssize "rollback segment size" from v$rollstat where usn= 5; USN rollback segment size ---------- --------------------- 5 4186112 extent n, extent n+1 (extent n+1 ) optimal optimal extent n+2 extent n+2 extent n+2 extent n+3, Optimal optimal 4M (shrinks v$rollstat oracle document) select USN,OPTSIZE,SHRINKS from v$rollstat; USN OPTSIZE SHRINKS ---------- ---------- ---------- 0 0 1 4194304 0 2 4194304 0 3 4194304 0 4 4194304 0 5 4194304 10 6 4194304 0 7 4194304 0 9 0 9 rows selected. SYSTEM oracle truncate table drop table truncate table or drop table
DDL (Deferred Rollback Segment) OFFLINE(exeample: alter tablespace users offline) client client ONLINE DBA 9i max extents 100 200 1 2 3 transactions_per_rollback_segment transactions transactions 2 max_rollback_segments rollback_segments PUBLIC PRIVATE ( ) OPS/RAC PUBLIC INSTANCE rollback_segments min(ceil(transactions/transactions_per_rollback_segment), max_rollback_segments) PUBLIC ORA-01555 (
) M G 4M 10M 50M/100M 1G 50M offline, online offline optimal 50M optimal (offline) 9i UNDO TABLESPACE oracle9i UNDO TABLESPACE show parameters undo NAME TYPE VALUE ------------------------------------ ------- ------------- undo_management string AUTO undo_retention integer 10800 undo_suppress_errors Boolean FALSE undo_tablespace string UNDOTBS1 undo_management AUTO MANUAL 8i undo_tablespace INSTANCE undo_retention ORA-01555 9i flashback undo_retention undo_suppress_errors true false UNDO UNDO UNDO
undo_retention drop UNDO UNDO alter system set undo_tablespace = undotbs1; System altered. UNDO pfile spfile UNDO UNDO trace 9i UNDO undo_management MANUAL ( ) UNDO LOB EXP 5G BUG ORACLE9.2. UNDO ASSM undo_retention BUG DBA metalink BUG ORA-01555 COMMIT SCN T ORA-01555 (block cleanout) oracle (block cleanout) (delay block cleanout) SCN T SCN SCN T COMMIT SCN 1
2 3 exp consistent = y exp 4 optimal script v$rollstat desc v$rollstat Name Null? Type ----------------------------------------- -------- ------------------- USN EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS VARCHAR2(15) CUREXT CURBLK view SHRINKS optimal waits http://tahiti.oracle.com oracle documents view oracle view select * from v$waitstat;
CLASS COUNT TIME ------------------ ---------- ---------- data block 341 0 sort block 0 0 save undo block 0 0 segment header 0 0 save undo header 0 0 free list 0 0 extent map 0 0 bitmap block 0 0 bitmap index block 0 0 unused 0 0 system undo header 0 0 system undo block 0 0 undo header 4 0 undo block 81 0 14 rows selected. view script script session ( oracle9.2.0 dba_blockers session session dba_waiters session ) select username, 2 v$lock.sid, 3 trunc(id1/power(2,16)) rbs, 4 bitand(id1,to_number('ffff','xxxx'))+0 slot, 5 id2 seq, 6 lmode, 7 request 8 from v$lock,v$session 9 where v$lock.type='tx' 10 and v$lock.sid = v$session.sid 11 and v$session.username = user; USERNAME SID RBS SLOT SEQ LMODE REQUEST ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- RAINY 8 7 45 300 0 6
RAINY 13 7 45 300 6 0 LMODE=6 LMODE=0 session LMODE=6 session 6 $ORACLE_HOME\RDBMS\ADMIN utllockt.sql script script session select b.sid, 2 a.xidusn, 3 a.used_ublk 4 from v$transaction a,v$session b 5 where a.addr = b.taddr; SID XIDUSN USED_UBLK ---------- ---------- ---------- 8 1 3 ORACLE9.2.0, HOLDING_SESSION WAITING_SESSION SID v$session desc dba_blockers Name Null? Type ----------------------- -------- ---------------- HOLDING_SESSION desc dba_waiters Name Null? Type ----------------------- -------- ---------------- WAITING_SESSION HOLDING_SESSION LOCK_TYPE VARCHAR2(26) MODE_HELD VARCHAR2(40) MODE_REQUESTED VARCHAR2(40) LOCK_ID1 LOCK_ID2 v$session SQL_ADDRESS SQL_HASH_VALUE v$sqlarea session sql v$lock dba_objects v$locked_object oracle view
DBA ORACLE www.itpub.net (biti_rainy) oracle internal performance tuning SQL oracle