回滚段探究

Size: px

Start display at page:

Download "回滚段探究"

颤株郑
7 years ago
Views:

1 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 %

2 transaction rollbacks/( transaction rollbacks + user commits) select name,value from v$sysstat where name in ('user commits','transaction rollbacks'); NAME VALUE user commits 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(*)

user commits 12532 transaction rollbacks 21 delete update, insert rowid delete, update insert rowid consistent reads oracle

3 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 rows selected. 8 9 select class,count(*) from x$bh where class > 10 group by class; CLASS COUNT(*) CLASS COUNT(*)

8 9 select class,count(*) from x$bh where class > 10 group by class; CLASS COUNT(*)

4 rows selected. 8 class class=18(usn=3) delete from t; rows deleted. commit; Commit complete. select SEGMENT_ID,SEGMENT_NAME from dba_rollback_segs; SEGMENT_ID SEGMENT_NAME SYSTEM 1 RBS0 2 RBS1 3 RBS2 4 RBS3 5 RBS4 6 RBS5 7 RBS6 9 RBS12 9 rows selected.

5 set transaction use rollback segment rbs6; Transaction set. insert into t select * from all_objects; rows created. commit; Commit complete. DBA create drop truncate 2 extent block extent extent 1 block extent 5 extent 1 block Extent select usn,wraps from v$rollstat; USN WRAPS

DBA create drop truncate 2 extent block extent 1 2 3 4 5 extent 1 block extent 5

6 rows selected. extent 3 extent 2 extent 2 extent 3 extent 4,5,1 extent 3 extent extent extent 2-1 extent extent extent extent 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 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 set transaction use rollback segment rbs4;

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.

7 Transaction set. update t_small set object_id = 1; 100 rows updated. SQLPLUS 2 begin for i in 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 session select usn,rssize "rollback segment size" from v$rollstat where usn= 4; USN rollback segment size begin 2 for i in 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

USN rollback segment size ---------- --------------------- 5 55042048 session select usn,rssize "rollback segment size" from v$rollstat where usn= 4; USN rollback segment size ----------

8 , 4M select usn,rssize "rollback segment size" from v$rollstat where usn= 5; USN rollback segment size 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 rows selected. SYSTEM oracle truncate table drop table truncate table or drop table

oracle document) select USN,OPTSIZE,SHRINKS from v$rollstat; USN OPTSIZE SHRINKS ---------- ---------- ---------- 0 0 1 4194304 0 2 4194304 0 3

9 DDL (Deferred Rollback Segment) OFFLINE(exeample: alter tablespace users offline) client client ONLINE DBA 9i max extents 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 (

2 max_rollback_segments rollback_segments PUBLIC PRIVATE ( ) OPS/RAC PUBLIC INSTANCE

10 ) 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 undo_suppress_errors Boolean FALSE undo_tablespace string UNDOTBS1 undo_management AUTO MANUAL 8i undo_tablespace INSTANCE undo_retention ORA i flashback undo_retention undo_suppress_errors true false UNDO UNDO UNDO

string AUTO undo_retention integer 10800 undo_suppress_errors Boolean FALSE undo_tablespace string UNDOTBS1 undo_management

11 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 COMMIT SCN T ORA (block cleanout) oracle (block cleanout) (delay block cleanout) SCN T SCN SCN T COMMIT SCN 1

12 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 oracle documents view oracle view select * from v$waitstat;

WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS

13 CLASS COUNT TIME data block 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 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

0 dba_blockers session session dba_waiters session ) select username, 2 v$lock.

14 RAINY 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 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

0, HOLDING_SESSION WAITING_SESSION SID v$session desc dba_blockers Name Null? Type ----------------------- -------- ---------------- HOLDING_SESSION desc dba_waiters Name Null?

18 DBA ORACLE (biti_rainy) oracle internal performance tuning SQL oracle

Oracle 4

Oracle 4 01 04 Oracle 07 Oracle Oracle Instance Oracle Instance Oracle Instance Oracle Database Oracle Database Instance Parameter File Pfile Instance Instance Instance Instance Oracle Instance System