Oracle10g性能调整与优化

Oracle10g性能调整与优化
2011年04月04日
　　Oracle10g性能调整与优化
　　主要内容：收集性能数据、优化SQL语句和应用程序、调整共享池(Shared Pool)的性能、调整缓冲区高速缓存(Buffer Cache)的性能、调整重做有关的性能、共享(多线程)服务器（MTS）、调整磁盘I/O的性能、调整闩(latch)和锁定(lock)、调整操作系统。
　　第一章            收集性能数据
　　1.   报警(Alert)日志文件
　　报警(Alert)日志文件在c:\oracle\product\10.2.0\admin\erp\bdump目录下：文件名为alert_erp.log。
　　注意报警(Alert)日志文件中的错误信息：
　　ORA-01652：在临时表空间中没有足够的空闲空间来进行排序操作。
　　ORA-01653：在表空间中没有足够的空闲空间来存放表。
　　ORA-01650：在回退段所在的表空间中没有足够的空闲空间来使回退段增长。
　　ORA-01631：表所占用的空间超过允许的最大值。
　　Checkpoint Not Complete。
　　Snapshot too old。
　　后台进程跟踪文件：也在c:\oracle\product\10.2.0\admin\erp\bdump目录下。文件名类似于erp_lgwr_2548.trc、erp_arc0_2620.trc、erp_dbw0_3012.trc。
　　用户跟踪文件：
　　udump目录下：erp_ora_788.trc文件。其中788为该会话所对应的Server Process的编号，可以通过V$process查得。
　　设置用户跟踪
　　实例级跟踪：init.ora参数SQL_TRACE=TRUE。这种方法会产生大量的系统开销。
　　用户级跟踪：
　　SQl>alter session set sql_trace=true;
　　SQl>alter session set sql_trace=false;
　　DBA跟踪：
　　SQL>exec sys.dbms_system.set_sql_trace_in_session(10,87,tru e);
　　SQL>exec sys.dbms_system.set_sql_trace_in_session(10,87,fal se);
　　10为会话编号，87为会话序列号。
　　限制用户跟踪文件的大小：init.ora参数 MAX_DUMP_FILE_SIZE。
　　2.   性能优化视图
　　v$sysstat：数据库启动以来的统计数据。
　　v$system_event：系统中所有会话发生过的等待事件。
　　v$sesstat：所有当前会话的统计数据。
　　v$session: 所有当前的会话。
　　V$session_event：已经发生过的等待事件。
　　V$session_wait：正在发生的等待事件。
　　V$sgastat：SGA内存的统计数据。
　　V$waitstat：对自由列表的争用。
　　3.   收集性能数据的程序：STATSPACK。
　　STATSPACK工具的使用
　　创建一个单独的表空间存放性能数据。大小300M左右。
　　注意：STATSPACK收集的是默认数据库的数据。
　　用管理员SYS用户登录。
　　SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\spcreate .sql
　　用PERFSTAT用户登录。
　　SQL>execute STATSPACK.SNAP;
　　至少要有两个快照。
　　用PERFSTAT用户登录，生成报告文件：
　　SQL> @c:\oracle\product\10.2.0\db_1\rdbms\admin\spreport .sql
　　4.   图形性能工具（WEB方式）
　　第二章            优化SQL语句和应用程序 1.   测量SQL语句的性能
　　Tkprof(Trace Kernel Profile)工具的使用
　　功能：测量SQL语句的性能。
　　c:\> tkprof   c:\oracle\product\10.2.0\admin\ERP\udump\erp_ora_1436.trc c:\bao.txt sys=no   sys=no的含义：不包含递归SQL语句(即访问数据字典的隐含语句)
　　SQL语句的处理要经过三个阶段：Parse、Execute、Fetch。
　　需要优化的SQL语句：
　　占用过多的CPU时间。
　　Parse、Execute、Fetch阶段的时间太长。
　　从磁盘读太多的数据块，而从内存中读很少的数据块。
　　访问许多数据块，但只返回几条数据。
　　Top SQL 的使用(WEB方式)
　　Top SQL用来代替Tkprof。
　　Top SQL可以找出哪些SQL语句的性能差，需要优化。
　　Top SQL 中的数据来源于V$SQL。
　　Top Sessions的使用(WEB方式)
　　Top Sessions可以找出哪些会话占用较多的资源。
　　2.   SQL语句的解释计划(EXPLAIN PLAN)
　　通过解释计划，可以找出SQL语句性能低的原因。
　　用SQL ScratchPad来生成SQL语句的解释计划：
　　用命令来生成SQL语句的解释计划：
　　先检查sys用户下是否有plan_table表(9i中已经有了这个表)，如果没有，执行
　　SQL〉@C:\oracle\product\10.2.0\db_1\rdbms\admin\utlxpla n.sql脚本。
　　SQL>explain plan for SELECT e.empno, e.ename, d.deptno, d.loc
　　FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno;
　　注意要commit。
　　查询执行计划：
　　SQL>select lpad('   ',4*(level-2)) || operation || '   ' || options || '   ' || object_name "EXECUTION_PLAN" from plan_table start with id =0 connect by prior id = parent_id;
　　3.   STATSPACK报告中的SQL语句性能
　　SQL ordered by Gets(按Gets排序的SQL语句)
　　SQL ordered by Reads(按Reads排序的SQL语句)
　　SQL ordered by Executions(按Executions排序的SQL语句)
　　SQL ordered by Parse Calls(按Parse Calls排序的SQL语句
　　4.   Oracle优化方式
　　优化方式：基于rule和cost.
　　基于rule时的优化等级：根据语法和表结构优化
　　1         Single row by rowid  
　　2         Single row by cluster join  
　　3         Single row by hash cluster key with unique or primary key  
　　4         Single row by unique or primary key  
　　5         Cluster join  
　　6         Hash cluster key  
　　7         Indexed cluster key  
　　8         Composite key  
　　9         Single-column indexes  
　　10       Bounded range search on indexed columns  
　　11       Unbounded range search on indexed columns  
　　12       Sort-merge join
　　13       MAX or MIN of indexed column
　　14       ORDER BY on indexed columns
　　15       Full table scan
　　以 SELECT empno    FROM emp   WHERE ename = 'CHUNG'     AND sal > 2000;语句为例说明访问路径。分析应在哪个字段上创建索引？
　　缺点：小表的全表扫描比索引效率高，索引字段值的差异性小。
　　基于cost时的优化根据表和索引的统计信息优化，优先采用。
　　根据表和索引的统计信息包括：
　　每个表或索引的大小。
　　每个表或索引所包括的数据行数。
　　每个表或索引所使用的数据块数量。
　　每个表行的字节数。
　　索引字段值的差异性(基数)。
　　5.   统计信息的创建
　　SQL> ANALYZE TABLE employee COMPUTE STATISTICS;
　　SQL> ANALYZE INDEX employee_last_name_idx COMPUTE STATISTICS;
　　查询统计信息，可用图形界面或DBA_TABLES。
　　SQL> ANALYZE TABLE employee DELETE STATISTICS;
　　如果表或索引的数据量很大时，可以使用样本来创建统计信息：
　　SQL> ANALYZE TABLE employee ESTIMATE STATISTICS;
　　默认的样本大小为1064行。
　　SQL> ANALYZE TABLE employee ESTIMATE STATISTICS 
　　SAMPLE 500 ROWS;
　　SQL> ANALYZE TABLE employee ESTIMATE STATISTICS 
　　SAMPLE 35 PERCENT;
　　创建字段的统计信息：
　　SQL> ANALYZE TABLE employee ESTIMATE STATISTICS 
　　FOR COLUMNS employee_id   SIZE 200;
　　SIZE的默认值是75。可以是1到254。
　　字段上的数据假设是正态分布。
　　直方图：SQL> ANALYZE TABLE finaid COMPUTE STATISTICS FOR COLUMN award SIZE 100; 
　　用图形界面创建统计信息。
　　优化提示：SQL> SELECT /*+ FIRST_ROWS   */   * FROM hr.employees;
　　其它优化提示有：RULE、FULL SALES(访问SALES表)、 INDEX SALES_ID_PK、PARALLEL。
　　6.   设置优化模式
　　init.ora参数OPTIMIZER_MODE：
　　CHOOSE、RULE、FIRST_ROWS(提高响应时间)、ALL_ROWS(提高吞吐量)。
　　7.   索引
　　B-树索引: 适合建在重复值少的字段。
　　索引的统计信息，索引B-树的高度(建议 ANALYZE INDEX employee_last_name_idx VALIDATE STRUCTURE;
　　SQL> SELECT (DEL_LF_ROWS_LEN/ LF_ROWS_LEN) * 100 "Wasted Space" FROM index_stats WHERE NAME= "EMPLOYEE_LAST_NAME_IDX" ;
　　建议：索引的空闲空间( alter index scott.pk_dept rebuild online;
　　SQL> alter index scott.pk_dept coalesce;
　　压缩B树索引：适合于索引字段重复值多的情况
　　SQL>ALTER INDEX employee_last_name_idx   REBUILD COMPRESS; 
　　位图(bitmap)索引：适合建在于重复值多的字段。
　　位图索引不适合于建在频繁进行insert、update和delete的表上。这些操作的性能代价太高。位图索引适合于数据仓库和DSS。
　　优化位图索引的init.ora参数：SORT_AREA_SIZE、PGA_AGGREGATE_TARGET。
　　淘汰的init.ora参数：CREATE_BITMAP_AREA_SIZE、BITMAP_MERGE_AREA_SIZE。 函数索引
　　必须要把init.ora参数QUERY_REWRITE_ENABLE设成TRUE，才能创建函数索引。
　　SQL>SELECT last_name,first_name FROM employees 
　　WHERE   UPPER(first_name)='SMITH';
　　SQL> CREATE INDEX hr.employee_first_name_upper_idx 
　　ON hr.employees(UPPER(first_name));
　　SQL> SELECT * FROM sales where (price * units) > 10000;
　　SQL> CREATE INDEX sales_total_sale_idx 
　　ON sales (price * units) TABLESPACE INDX;
　　反键索引：适用于序列字段。
　　反键索引只适用于=和!=查询。使用Between、>、内存、I/O、锁定、OS。
　　第三章            调整共享池(Shared Pool)的性能
　　1.   监视共享池的性能
　　共享池由library cache和dictionary cache组成。采用LRU(Least Recently Used)算法管理。library cache用于缓存执行的SQL语句和PL/SQL程序。dictionary cache用于缓存数据字典。
　　共享池有关的数据字典：V$SQL、V$SQLAREA、V$SQLTEXT、V$SQL_PLAN。(V$session)。
　　测量library cache的命中率：V$librarycache 
　　SQL>select namespace,gethitratio,pinhitratio,reloads,invalida tions
　　from v$librarycache where namespace in ('SQL AREA','TABLE/PROCEDURE','BODY','TRIGGER');
　　SQL AREA部分的gethitratio 、pinhitratio要 > 90%。
　　GETS(语法分析)。
　　PINS(执行)。
　　RELOADS(SQL语句需要重新语法分析)、
　　INVALIDATIONS(SQL语句所引用的表结构发生变化，或视图重新编译)。
　　select SUM(reloads)/SUM(pins) "Reload Ratio" from V$librarycache;
　　重新装载率Reload Ratio要 内存、数据库例程、SQL)来监视library cache。
　　SGA内存的经验公式。
　　SGA= 55% 物理内存
　　Shared Pool = 45% SGA
　　Buffer cache = 45% SGA
　　Redo Log Cache = 10% SGA
　　测量dictionary cache的命中率：V$rowcache 
　　select 1- (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" from V$rowcache;
　　"Data Dictionary Hit Ratio"的值要 > 85%。
　　使用STATSPACK来监视dictionary cache。
　　使用REPORT.TXT来监视dictionary cache。
　　2.   提高共享池性能的方法
　　加大共享池的大小：init.ora参数shared_pool_size(动态参数)。注意参数sga_max_size。
　　为大型PL/SQL程序设置保留内存：防止其它SQL语句从内存中移走。
　　init.ora参数SHARED_POOL_RESERVED_SIZE(建议值：10% shared_pool_size)。
　　销定(Pin)程序：DBMS_SHARED_POOL.KEEP('deposit')。
　　鼓励代码重用：在SQL语句中使用变量。
　　例如：SELECT * FROM EMP WHERE ename = 'Smith';
　　SELECT * FROM EMP WHERE ename= 'John';
　　改写为：
　　v_ename = 'Smith';
　　Select * from emp where ename =v_ename;
　　v_ename = 'John';
　　Select * from emp where ename =v_ename;
　　调整共享池有关的init.ora参数
　　OPEN_CURSORS：建议值500。
　　CURSOR_SPACE_FOR_TIME：建议值TRUE。
　　SESSION_CACHED_CURSORS：建议值TRUE。
　　CURSOR_SHARING：默认值为EXACT。建议设成SIMILAR或FORCE。
　　第四章            调整缓冲区高速缓存(Buffer Cache)的性能
　　1.   Buffer Cache的工作原理
　　Buffer Cache由数据块组成。
　　LRU列表：MRU  ………………. LRU。(全表扫描FTS放在LRU端。)
　　缓冲区块的状态：Free、Pinned、Clean、Dirty。
　　Dirty List或Write List(写列表)。
　　数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。
　　2.   测量Buffer Cache的性能
　　测量Buffer Cache的命中率：
　　SQL> select 1-((physical.value   direct.value   lobs.value)/logical.value) "Buffer Cache Hit Ratio" from V$SYSSTAT physical, V$SYSSTAT direct,V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = 'physical reads'
　　And direct.name = 'physical reads direct' and 
　　lobs.name = 'physical reads direct (lob)' 
　　And logical.name = 'session logical reads';
　　"Buffer Cache Hit Ratio"的值要 > 90%。
　　使用STATSPACK来监视Buffer Cache。
　　使用REPORT.TXT来监视Buffer cache。
　　非命中率指标：Free Buffer Inspected。(V$sysstat)
　　Free Buffer Waits、Buffer Busy Waits。(V$system_event)
　　3.   提高缓冲区高速缓存性能的方法 加大Buffer Cache的大小：init.ora参数DB_CACHE_SIZE(动态参数)。
　　使用Buffer Cache Advisory功能决定Buffer Cache的大小：
　　首先将init.ora参数DB_CACHE_ADVICE设成ON，然后查询V$DB_CACHE_ADVICE。
　　使用多个缓冲区池：
　　Keep Pool: DB_KEEP_CACHE_SIZE
　　Recycle Pool:DB_RECYCLE_CACHE_SIZE
　　Default Pool: DB_CACHE_SIZE
　　在内存中缓存表: 表的CACHE选项，对优化小表的全表扫描。
　　正确创建索引。
　　4.   调整Large Pool和JAVA POOL
　　Large Pool用于共享服务器、RMAN、并行查询、DBWR的从属进程。
　　Large Pool的大小通过init.ora参数Large_pool_size设置。默认为8M。
　　从V$sgastat中监视free memory的值：
　　SQL>SELECT name,bytes FROM V$sgastat WHERE pool = 'large pool';
　　JAVA_POOL池的默认大小为32M。对于大型Java应用程序，JAVA_POOL池的大小应大于50M。
　　init.ora参数java_pool_size
　　从V$sgastat中监视free memory的值。
　　SQL>SELECT name,bytes FROM V$sgastat WHERE pool = 'java pool';
　　第五章            调整重做有关的性能
　　Oracle重做有关的组件包括：Redo Log Buffer、Online Redo Log、LGWR、Archive Log、Checkpoint、Arch0。
　　1.   监视Redo Log Buffer的性能
　　Redo Log Buffer不采用LRU(Least Recently Used)算法管理。
　　当下列事件发生时，Redo Log Buffer的内容存盘：
　　Commit时、每3秒、空间使用1/3、达到1M、检查点。
　　如果写入Redo Log Buffer的速度超过LGWR存盘的速度，就会因等待而降低性能。
　　监视Redo Log Buffer的重试率( alter table emp allocate extent ;
　　事务处理数量
　　初始值：在分配给该对象的每个数据块内分配给事务处理条目的初始数量。可以输入1或2（对于簇和索引）到255之间的值。
　　最大值：可同时更新分配给对象的数据块的并行事务处理的最大数量。可以输入1到255之间的值。 自由表
　　列表：表、簇或索引的每个自由表组的自由表数量。可以输入1或大于1的值。默认值为1。
　　组：表、簇或索引的自由表组的数量。可以输入1或大于1的值。默认值为1。
　　缓冲池。
　　行转移(更新行时超过块的可用空间)和行链接(行的大小超过块的大小)的概念。
　　使用V$sysstat来监视行转移和行链接：table fetch continued row。
　　SQL>analyze table emp compute statistics;
　　使用DBA_TABLES来查询统计信息。
　　SQL> alter table emp deallocate unused;
　　SQL> alter table scott.emp move tablespace users;
　　表的高水位标志High Water Mark(HWM)。
　　1M       10M       100M
　　4.   调整排序IO
　　哪些SQL语句需要排序操作:order by、group by、select distinct、union、
　　intersect、minus、analyze、create index、联接。
　　V$sysstat。内存排序和磁盘排序(临时表空间中)。
　　监视排序性能(内存排序比例>95%)。
　　使用init.ora参数SORT_AREA_SIZE(512K)、SORT_AREA_RETAINED_SIZE、
　　pga_aggregate_target、WORKAREA_SIZE_POLOCY。
　　如何避免排序:SQL语法、正确索引、创建索引、ANALYZE。
　　v$sort_segment、v$sort_usage。
　　5.   优化回退段
　　一个回退段的区间可以分配给多个事务，回退段的一个数据块只能分配给一个事务。
　　测量回退段事务表的争用
　　select * from V$system_event where event like '%undo%';
　　回退段事务表的等待时间应接近于0。
　　select * from V$waitstat;
　　V$rollstat  
　　回退段事务表访问的成功率应>95%。
　　回退段区间争用
　　V$waitstat、V$sysstat。
　　回退段事务环绕(Wrap)：一个事务占用的回退段从一个区间扩展到另一个区间。
　　回退段的动态区间分配
　　V$system_event。
　　使用V$rollstat来监视回退段的使用情况。
　　使用Performance Manager(后台进程)来测量回退段。
　　提高回退段的性能
　　Oracle10g中的撤消表空间。
　　建议：每四个事务使用一个回退段，最多不超过20个回退段。
　　会退段的区间大小512k，最小区间数20。
　　明确分配回退段给事务。
　　SQL> set transaction use rollback segment rbs01;
　　最小化回退段活动:EXPORT、IMPORT、SQL* Loader时加commit=y参数。
　　Oracle10g中的撤消表空间。
　　第七章            共享(多线程)服务器（MTS）
　　1.   共享服务器(用户数>200)
　　SP1     SP2       SP3                 SP10
　　Dispatcher1      2    ……. UP1     UP2        UP3 ……………….. UP100 dispatchers、max_dispatchers(5)、shared_server(1)、
　　max_shared_server(20)。Sessions(170)、circuits(170)。mts开始的参数已被淘汰。
　　共享服务器的联接不能关闭和启动数据库。
　　客户端的联接方式。(SERVER=DEDICATED)
　　SQL>alter system set MTS_SERVERS=5;
　　测量共享服务器的性能
　　V$shared_server、V$queue   是否需要生成更多的Shared Server进程。
　　V$dispatcher   是否需要增加更多的 Dispatcher进程。
　　Net8的高级配置
　　多路复用：此功能允许通过单个传输协议连接以集中方式多路传送多个客户网络会话。
　　连接共享(连结池)：(释放物理连接，保持逻辑连接)。
　　入网连接超时（以秒记）
　　超时（以秒记），用于入网网络连接。如果指定超时的数值为0，则使用缺省值（10 秒)。
　　第八章            调整闩(latch)和锁定(lock)
　　1.   调整闩(latch)
　　闩可以作为内存性能的另一个指标。
　　1.闩:等待闩和立即闩(V$lacth。共239个)。
　　数据库中是否存在闩争用V$system_event("latch free")。
　　几个重要的闩：shared pool、library cache、cache buffers lru chain、
　　cache buffers chains、redo allocation、redo copy。
　　select * from V$latch where misses!=0;
　　2.自由列表：V$system_event("buffer busy waits")。
　　V$waitstat。
　　测量哪些段存在自由列表争用：dba_segments、V$session_wait。
　　alter table scott.emp storage (freelists 5);
　　自动段空间管理的表空间。
　　2.   调整锁定
　　DML锁(TM)和DDL锁(TX)
　　事务。
　　其它。
　　自动。
　　1.update t1      2. drop table t1
　　2.update t1      2. select t1
　　3.update t1      2. update t1
　　锁定模式：
　　RX：对表UPDATE、INSERT、DELETE时获得。
　　RS：对表SELECT … FOR UPDATE时获得。
　　S: LOCK TABLE EMP IN SHARE MODE; 可以是多个用户获得。
　　SRX: LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE; 只能是一个用户获得。V$LOCK;
　　外键约束时的锁定。死锁。
　　第九章            调整操作系统
　　1.   调整操作系统
　　观察内存和CPU利用率(<90%)。
　　2.   使用Resource Manager
　　资源使用者组。一个用户可以是多个资源使用者组的成员，但一次只有一个组是活动的。
　　确定用户的CPU利用率。(v$sesstat和v$sysstat)
　　资源计划:由资源计划指令组成。一次只能有一个资源计划是活动的(V$rsrc_plan)。
　　alter system set resource_manager_plan=system_plan;
　　select username,RESOURCE_CONSUMER_GROUP from V$session;
　　子计划。
　　资源计划调度。