ORACLE By Chao_Ping and Parrotao 1 Oracle9i, SGA 2 Oracle9i 3, 4, Oracle? Oracle??? Oracle 1 Overview Oracle, Datafile, Background process, System Global Area, Server Process User Process
System Global Area, Oracle Background process Oracle, Server Process user process, user process : SQL*PLUS SYSTEM SQL> select name from v$datafile; NAME --------------------------------------------- /u05/dbf/prod/system_01dbf /u06/dbf/prod/temp_01dbf /u04/dbf/prod/tools_01dbf /u04/dbf/prod/users_01dbf /u09/dbf/prod/rbs02_01dbf /u06/dbf/prod/applsys_indx_01dbf /u05/dbf/prod/applsys_data_01dbf /u06/dbf/prod/ar_indx_01dbf SGA SQL*PLUS SYSTEM
SQL> select * from v$sga; NAME VALUE -------------------- --------- Fixed Size 39816 Variable Size 259812784 Database Buffers 1049E+09 Redo Buffers 327680 ps ef grep ora_ # ps -ef grep ora_ grep XCLUAT oracle 29431 1 0 Sep 02? 2:02 ora_dbwr_sid oracle 29444 1 0 Sep 02? 0:03 ora_ckpt_sid oracle 29448 1 0 Sep 02? 2:42 ora_smon_sid oracle 29442 1 0 Sep 02? 3:25 ora_lgwr_sid oracle 29427 1 0 Sep 02? 0:01 ora_pmon_sid 2 Oracle a,oracle,, Oracle b i :,,,, Oracle select table_name from dict; ii
, iii iv c, d, e i initsidora $ORACLE_HOME/dbs ii Password Oracle Internal iii Oracle Log 1 alertlog,, SQL select value from v$parameter where name = 'background_dump_dest'; 2 trace SQL select value from v$parameter where name = 'user_dump_dest'; f Oracle, Oracle 3 System Global Area(SGA a SGA Oracle,, b Share_spool( i SQL
SQL,Oracle SQL (parse,, SQL,Oracle Share_spool, SQL,Oracle SQL SQL parse SQL> select count(* fromscpass ; COUNT(* ---------- 243 Elapsed: 00:00:0008 Share_pool Data buffer SQL> alter system flush SHARED_POOL; System altered Share_pool, Data buffer SQL> select count(* from scpass ; COUNT(* ---------- 243 Elapsed: 00:00:0002 SQL> select count(* from scpass ; COUNT(* ---------- 243 Elapsed: 00:00:0000 SQL SQL Parse 00:00:0002 ii Share_spool Oracle
c Database Buffer Cache(,,,, DBWR d Redo log buffer(, LGWR e Java_Pool(Java Java,Oracle 8I,Oracle Java Java Java f Large_Pool( RMAN MTS 4 ---- SCN(system change number, a SCN, SCN b, SCN,, SCN, SCN SCN, : 5 ID Name ------------------------ 1 ShangHai 2 Beijing 3 Gugangzhou 4 ShenZhen 5 HanZhou
A 12:00, 12:05 12:01 B Update, ID 2 Beijing Tianjing A Tianjing 12:00 SCN N B SCN N+1 A ID=2 SCN SCN, SCN=N 5 a Oracle 5 DBWR( LGWR( SMON( PMON( CKPT(,,, b DBWR : 1,DBWR 2 DBWR CKPT 3 DBWR LGWR DBWR, DBWR LGWR CKPT, DBWR 1 DBWR 2 3 CKPT DBWR c LGWR LGWR
, SCN=000000001000 ID ID=0801 =02 =0011,LGWR, LGWR LGWR 1 2 1/3 3 1M 4 5 DBWR SCN LGWR SCN,DBWR LGWR : 1? : : DBWR LGWR 2 1,? : : LGWR 5 DBWR, ORACLE d SMON e PMON, f CKPT,
DBWR/LGWR,,, CKPT CKPT / CKPT 1 2 immediate,transaction, normal shutdown 3 LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT,FAST_START_IO_ TARGET 4 g ARCH, Oracle ARCH,,, ARCH h RECO Oracle,, RECO,,RECO i Server Process i 1 2, ii PGA & UGA PGA = Process Global Area UGA = User Global Area j User Process, SQL 6 Oracle a System Global Area Background process b SID SID?
Oracle SID, ORACLE_SID, initsidora, SID? SID Oracle, SID (System Global Area SID c Oracle,,, (, Oracle9i RAC(real application cluster, Oracle,,, RAC 5 1 2
3 :, 4,,, 5 Oracle, OS = = (, = segment = (,,OS, extend = = 1 SQL select * from dict SQL select * from v$fixed_view_definition DBA, SQL, SQL, SQL SGA DBA OEM/Quest, 2
, 3 a,,,, commit? 12:01 B Update, ID 2, Beijing Tianjing A Tianjing, SCN SCN, : Bcommit,, Select,,?? : Oracle ORA-1555 (snapshot too old rollback segment b i ii iii 4, 1-2- 3-4- 1, 1 2, 2 3, 3 4, 4 1 1, 4 1, 5
Oracle Update 1 Update 2 Server Process, i,dbwr ii, 3
a, b c Redo log buffer 4 Commit a SCN b Redo log buffer Redo log file c Commit?,, commit Oracle Oracle LRU Oracle Oracle SGA, LRU, LRU,, LRU,,,,, LRU, DBWR LRU, LRUW (, DBWR, LRU HASH Oracle HASH, HASH, Oracle HASH 100,, 100 HASH, 1000*2000, 100 : 1, 0-999 2
3 hash f(row hash hash hash HashArray[n][m] find a unused buffer in HashArray[f(row][?] HashArray[f(row][?] = row;, hash, hash hash For (I=1;I<=2000; I ++ If (HashArray[f(row][I] == row return; Next HASH,, LATCH(Oracle Latch Lock,,Latch Oracle Lock,, Oracle, Oracle latch, DBWR, Latch,DBWR