Oracle10GSQLTuning资料汇总编辑.pptVIP

SGA Oracle 10g 中可以选择手动或者自动的方式来管理SGA及各部分的大小，默认为自动管理（ASSM） Oracle Database 10g uses an SGA size parameter (SGA_TARGET) that includes all the memory in the SGA, including all the automatically sized components, manually sized components, and any internal allocations during startup. ASMM simplifies the configuration of the SGA by enabling you to specify a total memory amount to be used for all SGA components. The Oracle Database then periodically redistributes memory between these components according to workload requirements. 高 伟 达 软 件 技 术 有 限 公 司 Oracle 10g SQL Tuning 李小芹 目录 1、Oracle数据库结构 2、SQL调优的主要方法 搜集统计信息 执行计划的查看 确定运行速度慢的语句 使用索引 使用hints 使用物化视图 Oracle Database Architecture Two main components: The database:physical structures The instance:memory structures The size and structure of these components impact perfomance. Oracle Database Architecture The database: --The control file --The redo log files --The data files --The parameter file --The password file The instance: --SGA --PGA --background processes --server processes Oracle Database Architecture SGA Database buffer cache: Caches blocks of data retrieved from the data files Redo log buffer: Caches redo information (used for instance recovery) until it can be written to the physical redo log files stored on disk Shared pool: Caches various constructs that can be shared among users SGA Large pool: Optional area in the SGA that provides large memory allocations for Oracle backup and restore operations, I/O server processes, and session memory for the shared server Java pool: Used for all session-specific Java code and data within the Java Virtual Machine (JVM) Streams pool: Used by Oracle Streams PGA The Program Global Area (PGA) is a memory region, which contains data and control information for each server process. A server process is a process that services a client’s requests. Each server process has its own private PGA that is created when the server process is starte