Oracle基础体系结构及性能优化

1、Oracle基础体系结构及性能优化1、基础体系结构介绍1.1 Oracle 数据库基础对象n Tablespaces and DatafileOracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace. Figure 3-1 illustrates this relationship.Figure 3-1 Datafiles and Tablespacesn Temporary Tablespaces and tempfi

2、lesWhat are Temporary Tablespaces:Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be alloc

3、ated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.What are TEMPFILES?Unlike normal data files, TEMPFILEs are not fully initi

4、alised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.TEMPFILEs are not recorded in the databases control file. This implies that one can just recreate them w

5、henever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILEs to be local instead of shared in a RAC environment.One cannot remove datafiles from

6、 a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:SQL ALTER DATABASE TEMPFILE /oradata/temp02.dbf DROP INCLUDING DATAFILES;If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary

7、Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace:SQL ALTER TABLESPACE temp ADD TEMPFILE /oradata/temp03.dbf SIZE 100M;Except for adding a tempfile, as illustrated in the above example, you cannot use the ALTER TABLESPACE statement for a locally managed tem

8、porary tablespace (operations like rename, set to read only, recover, etc. will fail).n Segments and extentsA segment is a container for objects (such as tables, indexes.). A segment consists of extents.An extent is a part of a segment. It consists of one or more contiguos db blocks. One or more ext

9、ents make a segment.如下图可以清楚的说明段、范围与块的关系。段、范围与块段（Segments）段代表一系列的范围，包括数据库对象中的数据。段存储在表空间中。表、索引等都存储在段中。范围（Extent）是表空间中分配的相邻的数据块，一个数据块组就是一个范围。 块（Blocks）是Oracle存储管理的最小逻辑单元，Oracle的块和操作系统的块是不同的。注：1、每个block 的内容不能同时被不同用户更新，OLTP系统block size 为2k 防止更新冲突。Block 冲突表现为 1）buffer cache 过高 2）buffer busy 。2、4k Block 块只

10、能用 4k data buffer ，8K Block 只能为其建 8k data buffer。3、表空间大小最好预先设置好，不建议动态扩展。1）动态扩展不易管理表空间大小。2）动态扩展表空间需更新数据库数据字典，影响数据库效率。1.2 常见Oracle硬件架构注：每个存储上一般有2个控制卡。1.3 数据仓库架构案例注：接口集群是对数据进行加载到入库集群。冷备集群是多接口加载的数据进行文本的备份，以便于数据恢复用。1.4 Oracle体系结构介绍首先介绍介两个概念SGA（System Global Area）、PGA（Process Global Area）。如图1.4-1：1.4-1 Or

11、acle 结构SGA包含data buffer cache、share pool、Redo log buffer三个部分。Data Buffer Cache：包含来自于数据文件的数据和即将写到数据文件的数据，提高访问速度，性能。体积最大。查询数据是先在Data Buffer Cache查找数据，如存在直接返回用户（Cache hit），如没有记录则从磁盘数据文件读取（Cache miss）。存储的是数据块由DB_BLOCK_SIZE参数决定。Shared Pool：共享池存储解析好的SQL语句，包括Library Cache和Data Dictionary Cache（数据 字典） Libra

12、ry Cache：存储经过解析编译以后的SQL或PL/SQL语句，大小由共享池大小决定。Data Dictionary Cache：存储数据库信息，数据库字典，提高访问数据库速度。Redo log buffer：存储对数据的修改，主要用于恢复数据（Data Buffer Cache变化的数据）。LOG数据块是连续的存储。LGWR进程负责将日志从日志缓冲区写入联机日志文件，条件如下：1) COMMIT;2) 日志缓冲区达到总数的1/3;3) 定时、固定时间会写log日志，一般3秒钟;4) Checkpoint。 Checkpoint之后DBW进程是根据SGA中的DATA BUFFER刷新数据文件

13、的。5) 1 Meg of data in buffer。Meg兆字节一般log file文件会多个文件，以保证数据能够正常恢复。V$log 视图可以看到log group的信息。如下： Column DatatypeDescriptionGROUP# NUMBER Log group numberTHREAD# NUMBERLog thread numberSEQUENCE# NUMBERLog sequence numberBYTES NUMBER Size of the log (in bytes)MEMBERS NUMBER Number of members in the log g

14、roupARCHIVED VARCHAR2(3)Archive status (YES or NO)STATUS VARCHAR2(16) Log status: UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log. CURRENT - Current redo log. This implies that the

15、 redo log is active. The redo log could be open or closed. ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE

16、statement. After the log is cleared, the status changes to UNUSED. CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header. INACTIVE - Log is no longer needed for instan

17、ce recovery. It may be in use for media recovery. It might or might not be archived.FIRST_CHANGE# NUMBER Lowest system change number (SCN) in the logFIRST_TIME DATETime of the first SCN in the logOracle 自动为每个进程分配一定大小的PGA。对于数据仓库类系统的PGA应稍微调大一些。Redo Log 与 Acrchive log 区别？1） Redo log 用于恢复 data buffer ca

18、che 数据。2） Acrchive log 用于恢复 数据库的数据。3） Redo log 中数据文件写满后会讲日志信息写到Acrchive log文件中。-归档日志打开4） Redo log 文件一般为空间小、性能高的设备，而Acrchive log文件一般为空间大，性能低的设备。DBWR进程负责将脏数据块写入磁盘。这些条件包括：1）当用户进程在LRU列表中搜索了一定数量的缓存块后，仍然没有找到可用的空闲缓存块，DBWR进程将启动，把脏缓存块写入数据文件。 2）Checkpoint发生时，DBWR进程把所有的脏缓存块从LRU列表中移入到写入列表，并写入到数据文件。 3）数据库缓存块中的写入

19、列表的长度达到一定值的时候，DBWR将写入列表中的脏缓存块写入数据文件。 4）每过3秒钟，DBWR把脏缓存块从LRU列表移到写入列表，一旦写入列表长度达到一定值，DBWR便将数据写入数据文件。 5） 当表空间offline时，DBWR把所有属于该表空间的脏缓存块从LRU列表移到写入列表，并且开始写入数据文件。 6） 当表空间开始热备时，DBWR把所有属于该表空间的脏缓存块从LRU列表移到写入列表，并且开始写入数据文件。Shared Pool由共享SQL区和数据字典区组成，参数shared_pool_size确定共享池大小，共享SQL区包括：1) SQL或PL/SQL语句的文本。2) SQL或P

20、L/SQL语句的语法分析形式。3) SQL或PL/SQL语句的执行方案。SQL执行时执行以下步骤：检查SQL语法-检查对象是否存在-检查用户权限-选择最优执行计划。执行计生成分为以下几种形式：1）hard Parse 重新生成执行计划2）Soft Parse 只对权限部分修改3）Cursor cache 直接使用共享区内的执行计划。共享池CURSOR_SHARING参数 这个参数决定什么样的SQL语句能够共享游标，有三个取值：FORCE, SIMILAR, EXACT； FORCE 两个语句的差异只在字面值，这些差异不会改变语句的含义时可共享游标； SIMILAR两个语句的差异只在字面值，这些

21、差异不会改变语句的含义和执行计划时可共享游标（这个值是在9i 中引入，如果有差异的栏位上有柱状图统计，这时执行计划会改变，不能共享游标，行为同于EXACT，如果差异栏位上没有柱状图统计，执行计划相同，这时的 行为同于FORCE）； EXACT 两个语句必须精确匹配才能共享游标，这是默认值。注：1、redo log 文件等待表现为log file sync 10 ms。Commit 等待事件。调整方案：1） Batch log file 打开 2）log file 扩大 IO。2、使用CURSOR_SHARING强制选择执行计划时，必须清楚选择那种执行计划。1.5 Oracle推荐的存储架构 R

22、aid 5 Disk Group: Stripped among 6 disks with one hot spare disk. Lun: Created based on raid 5 disk group VPath: Balance IO workload among all HBAs ASM Disk Group: Stripped among all LVs, in another word, its stripped among all Luns.注：独立磁盘冗余阵列（RAID, Redundant Array of Independent Disks）。1） RAID 0 连续

23、地分割数据并并行地读/写于多个磁盘上。2） RAID 1是一种镜像磁盘阵列，其原理就是将一块硬盘的数据以相同位置指向另一块硬盘的位置。RAID 1又称为Mirror或Mirroring。3） RAID5：分布式奇偶校验的独立磁盘结构 。这三种RAID都存在自身的缺陷，有系统本身的特点原则相应的RAID方式。2、性能优化技术分享2.1表分区技术Oracle 分区包含以下几种：Range Partition/List Partition/Hash Partition/Range + Hash or List + Hash。 Range Partition:范围分区就是对数据表中的某个值的范围进行分

24、区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。 Hash Partition:散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。 List Partition:当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。与范围分区和散列分区所不同，列表分区不支持多列分区。如果表按某列分区，则分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。l Range + Hash（复合分区）:有时候我们需要根据范围分区后

25、，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法。（注意：先一定要进行范围分区）l List + Hash（复合分区）:范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。（注意：先一定要进行范围分区）注：表分区方法的选择跟表存放的数据特点，系统特点等关系密切。2.2 Oracle 关联技术2.2.1 Nested Loop Join（嵌套循环连接）工作方式：提取驱动表

26、中符合条件的记录，与被驱动表的连接列进行关联查询符合条件的记录。在这个过程中，Oracle首先提取驱动表中符合条件的第一条记录，再与内部表的连接列进行关联查询相应的记录行。在关联查询的过程中，Oracle会持续提取驱动表中其他符合条件的记录与内部表关联查询。这两个过程是并行进行的，因此嵌套循环连接返回前几条记录的速度是非常快的。在这里需要说明的是，由于Oracle最小的IO单位为单个数据块，因此在这个过程中Oracle 会首先提取驱动表中符合条件的单个数据块中的所有行，再与内部表进行关联连接查询的，然后提取下一个数据块中的记录持续地循环连接下去。当然，如果单行记录跨越多个数据块的话，就是一次单

27、条记录进行关联查询的。适用范围：1）对相对较少的数据量进行操作，尤其是驱动表。2）针对关联条件，嵌套表有高效的访问方法（索引或内存）。2.2.2 Hash Join（哈希连接）工作方式：优化器根据统计信息，首先选择两个表中的小表，在内存中建立这张表的基于连接键的哈希表；优化器再扫描表连接中的大表，将大表中的数据与哈希表进行比较，如果有相关联的数据，则将数据添加到结果集中。适用范围：1）对适应于等值关联。2）对大数据量进行关联，驱动表的数据最好能够完全装载进内存中。2.2.3 SORT MERGE JOIN（排序合并连接）工作方式：1) 优化器判断第一个源表是否已经排序，如果已经排序，则到第3步

28、，否则到第2步。2) 第一个源表排序3) 优化器判断第二个源表是否已经排序，如果已经排序，则到第5步，否则到第4步。4) 第二个源表排序5) 已经排过序的两个源表进行合并操作，并生成最终的结果集。适用范围：1）关联数据已经是预排序的2）关联条件为非等值关联三种连接的比较如表2.2-1 表2.2-1 三种连接方式比较2.3 统计信息与执行计划2.2.1 统计信息概念统计信息主要是描述数据库中表、索引的大小、数据分布状况等的一类信息。比如：表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下、不同join 方式下各种计划的成本，最后选择出成本最小的计划。手工搜集统计信息方法如下：1、完全计算法：analyze table abc compute statistics; -比较古老2、抽样估算(抽样20%)：analyze table abc estimate