Oracle优化.docx

1、Oracle优化Oracle10g性能调整与优化第一章 收集性能数据1. 报警(Alert)日志文件报警(Alert)日志文件在c:oracleproduct10.2.0adminerpbdump目录下：文件名为alert_erp.log。注意报警(Alert)日志文件中的错误信息：ORA-01652：在临时表空间中没有足够的空闲空间来进行排序操作。ORA-01653：在表空间中没有足够的空闲空间来存放表。ORA-01650：在回退段所在的表空间中没有足够的空闲空间来使回退段增长。ORA-01631：表所占用的空间超过允许的最大值。Checkpoint Not Complete。Snapsho

2、t too old。后台进程跟踪文件：也在c:oracleproduct10.2.0adminerpbdump目录下。文件名类似于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。这种方法会产生大量的系统开销。用户级跟踪：SQlalter session set sql_trace=true;SQlalt

3、er session set sql_trace=false;DBA跟踪：SQLexec sys.dbms_system.set_sql_trace_in_session(10,87,true);SQLexec sys.dbms_system.set_sql_trace_in_session(10,87,false);10为会话编号，87为会话序列号。限制用户跟踪文件的大小：init.ora参数 MAX_DUMP_FILE_SIZE。2. 性能优化视图v$sysstat：数据库启动以来的统计数据。v$system_event：系统中所有会话发生过的等待事件。v$sesstat：所有当前会话的统

4、计数据。v$session: 所有当前的会话。V$session_event：已经发生过的等待事件。V$session_wait：正在发生的等待事件。V$sgastat：SGA内存的统计数据。 V$waitstat：对自由列表的争用。3. 收集性能数据的程序：STATSPACK。STATSPACK工具的使用创建一个单独的表空间存放性能数据。大小300M左右。注意：STATSPACK收集的是默认数据库的数据。用管理员SYS用户登录。SQL c:oracleproduct10.2.0db_1rdbmsadminspcreate.sql用PERFSTAT用户登录。SQLexecute STATSPA

5、CK.SNAP;至少要有两个快照。用PERFSTAT用户登录，生成报告文件：SQL c:oracleproduct10.2.0db_1rdbmsadminspreport.sql4. 图形性能工具（WEB方式）第二章 优化SQL语句和应用程序1. 测量SQL语句的性能Tkprof(Trace Kernel Profile)工具的使用功能：测量SQL语句的性能。c: tkprof c:oracleproduct10.2.0adminERPudumperp_ora_1436.trc c:bao.txt sys=no sys=no的含义：不包含递归SQL语句(即访问数据字典的隐含语句)SQL语句的处

6、理要经过三个阶段：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语句性能低

7、的原因。用SQL ScratchPad来生成SQL语句的解释计划：用命令来生成SQL语句的解释计划：先检查sys用户下是否有plan_table表(9i中已经有了这个表)，如果没有，执行C:oracleproduct10.2.0db_1rdbmsadminutlxplan.sql脚本。SQLexplain plan for SELECT e.empno, e.ename, d.deptno, d.locFROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno;注意要commit。查询执行计划：SQLselect lpad( ,4*(lev

8、el-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 C

9、alls排序的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-colum

10、n indexes 10 Bounded range search on indexed columns 11 Unbounded range search on indexed columns 12 Sort-merge join13 MAX or MIN of indexed column14 ORDER BY on indexed columns15 Full table scan以 SELECT empno FROM emp WHERE ename = CHUNG AND sal 2000;语句为例说明访问路径 。分析应在哪个字段上创建索引？缺点：小表的全表扫描比索引效率高，索引字段值

11、的差异性小。基于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;如

12、果表或索引的数据量很大时，可以使用样本来创建统计信息：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 SIZ

13、E 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

14、(提高响应时间)、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

15、alter index scott.pk_dept coalesce;压缩B树索引：适合于索引字段重复值多的情况SQLALTER 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、BITM

16、AP_MERGE_AREA_SIZE。函数索引必须要把init.ora参数QUERY_REWRITE_ENABLE设成TRUE，才能创建函数索引。SQLSELECT 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 IND

17、EX sales_total_sale_idx ON sales (price * units) TABLESPACE INDX;反键索引：适用于序列字段。反键索引只适用于=和!=查询。使用Between、select namespace,gethitratio,pinhitratio,reloads,invalidationsfrom v$librarycache where namespace in (SQL AREA,TABLE/PROCEDURE,BODY,TRIGGER);SQL AREA部分的gethitratio 、pinhitratio要 90%。GETS(语法分析)。PINS

18、(执行)。RELOADS(SQL语句需要重新语法分析)、INVALIDATIONS(SQL语句所引用的表结构发生变化，或视图重新编译)。select SUM(reloads)/SUM(pins) “Reload Ratio” from V$librarycache;重新装载率Reload Ratio要 85%。使用STATSPACK来监视dictionary cache。使用REPORT.TXT来监视dictionary cache。2. 提高共享池性能的方法加大共享池的大小：init.ora参数shared_pool_size(动态参数)。注意参数sga_max_size。为大型PL/SQL

19、程序设置保留内存：防止其它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 = Joh

20、n;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、Pin

21、ned、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.nam

22、e = physical readsAnd 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

23、 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_SIZERecycle Pool:DB_RECYCLE_CACHE_SIZEDefault Pool: DB_CACHE_SIZE在内存中缓存表: 表的CACH

24、E选项，对优化小表的全表扫描。 正确创建索引。4. 调整Large Pool和JAVA POOLLarge Pool用于共享服务器、RMAN、并行查询、DBWR的从属进程。Large Pool的大小通过init.ora参数Large_pool_size设置。默认为8M。从V$sgastat中监视free memory的值：SQLSELECT name,bytes FROM V$sgastat WHERE pool = large pool; JAVA_POOL池的默认大小为32M。对于大型Java应用程序，JAVA_POOL池的大小应大于50M。init.ora参数java_pool_size

25、从V$sgastat中监视free memory的值。SQLSELECT 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

26、、达到1M、检查点。如果写入Redo Log Buffer的速度超过LGWR存盘的速度，就会因等待而降低性能。监视Redo Log Buffer的重试率(1%)。Select retries.value/entries.value “Redo Log Buffer Retry Ratio”From V$sysstat retries, V$sysstat entriesWhere retries.name = redo buffer allocation retriesAnd entries.name = redo entries;“Redo Log Buffer Retry Ratio”的值要 1%。Select name,value from V$sysstat where name=redo log space requests;如果该值大，需要增加Redo Log Buffer。2. 提高Redo Log Buffer的性能增加Redo Log Buffer的大小：init.ora参数log_buffer。减小重做日志的生成量。(如果设置表的NOLOGGING属性，下列操作不记录在Online Redo Log中：用SQL* Loader的直接路径加载。