彦祖的小号的博客

An Oracle Databasebackground processis defined as any process that is listed inV$PROCESSand has a non-null value in thePNAMEcolumn.Select PNAME,SPID from v$process where pname is not null;Table F-1describes Oracle Database background process...

Oracle启动时不会按pga_aggregate_target或sga_target把内存预分配，而是在需要时再从系统获取内存，一旦不需要再释放给操作系统关于SGA与PGA分配：OLTP：SGA_TARGET = (total_mem * 0.8) * 0.8PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2OLAP(DSS)：SGA_TARGET= (total_mem * 0.8) * 0.5PGA_AGGREGATE_TARGET =(t..

A process normally runs in its own private memory area. Most processes can periodically write to an associated trace file.The process execution architecture depends on the operating system：For example, on Windows an Oracle background process is a thr..

（一）Redo Byte Address(RDA)Redolog的redo entry地址用RBA(Redo Byte Address)表示，没有视图查看RDA，需要dump redo log查看。RDA由三部分组成：the log file sequence number (4 bytes) the log file block number (4 bytes) the byte offset into the block at which the redo record starts (..

Oracle Database uses undo data to do the following:Roll back anactive transactionRecover a terminated transactionProvideread consistencyPerform some logical flashback operationsOracle事务回滚闪回等操作是通过UNDO SEGMENT完成的而不是REDO LOG，更高率. 而只在恢复时使用redo lo...

如果当前隔离级别为read committed且查询不是在事务中执行, 则查询结果与开始查询SCN一致; 如果当前隔离级别为read committed且查询在事务中执行，则查询结果与事务开始SCN一致；如在当前为serializable或read only隔离级别，这时查询也是事务，所以多个查询(中间没有commite或rollback)会跟第一次查询开始SCN一致 Statement-Level Read ConsistencyOracle Database always enforcess...

Tablespace是最大的逻辑存储结构，它把HWM以下block标号，即表空间下segment共用block id; 每个segment又把其extent标号，每个segment下的extent id均从0开始。以上标号均为逻辑顺序不表示物理存储地址连续，block是最小的逻辑单位，它由多个物理block组成，物理block本身就可能不连续SET LINEISZE 200COL SEGMENT_NAMEFORMAT A20select segment_name, extent_id,...

An extent is a unit of database storage made up of logically contiguous data blocks.只是逻辑连续的blocks id，实际组成block的物理block会分散在磁盘上Extent是segment的扩展单位, 即一次扩展1个extent，extent不能跨数据文件（一）Allocation of Extents在创建segment时会分配一个initial extent(可能包含多个extent)By ..

Oracle分为Logical Storage Structures与Physical Storage Structures，逻辑存储结构包含block, extent, segment, tablespace; 物理存储结构包含数据文件，控制文件，REDO LOG, 归档文件The following figure is an entity-relationship diagram for physical and logical storage. The crow's foot notation..

Oracle通过rowid来唯一标识行，Rowid并不物理存储在数据库中，而是从存储数据的文件和块中推断出来的。Oracle Database uses arowidto uniquely identify a row.A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.Apseudocolumnbehav...

Thehigh water mark (HWM)is the point in a segment beyond which data blocks are unformatted and have never been used.HWM对于全表扫描至关重要，HWM以下过多碎片空间会占用更多物理存储，增加物理IO，消耗更多内存缓存(内存读入的块也会有大量碎片)MSSM是在freelist中没有free block时，在freelist加入一些HWM以上blocks, 并且在加入时对它们进...

Data Block也称为page, 它是Oracle IO的最小单位，不同于OS的block，它是逻辑存储结构，由多个OS block组成，查看OS block大小：$ getconf PAGESIZEAt the physical level, database data is stored in disk files made up of operating system blocks.Anoperating system blockis the minimum unit of dat...