搜索
Oracle10g性能调整与优化主要内容：收集性能数据、优化SQL语句和应用程序、调整共享池(SharedPool)的性能、调整缓冲区高速缓存(BufferCache)的性能、调整重做有关的性能、共享(多线程)服务器（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：表所占用的空间超过允许的最大值。CheckpointNotComplete。Snapshottooold。后台进程跟踪文件：也在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为该会话所对应的ServerProcess的编号，可以通过V$process查得。设置用户跟踪实例级跟踪：init.ora参数SQL_TRACE=TRUE。这种方法会产生大量的系统开销。用户级跟踪：SQlaltersessionsetsql_trace=true;SQlaltersessionsetsql_trace=false;DBA跟踪：SQLexecsys.dbms_system.set_sql_trace_in_session(10,87,true);SQLexecsys.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：所有当前会话的统计数据。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用户登录。SQLexecuteSTATSPACK.SNAP;至少要有两个快照。用PERFSTAT用户登录，生成报告文件：SQL@c:\oracle\product\10.2.0\db_1\rdbms\admin\spreport.sql4.图形性能工具（WEB方式）第二章优化SQL语句和应用程序1.测量SQL语句的性能Tkprof(TraceKernelProfile)工具的使用功能：测量SQL语句的性能。c:\tkprofc:\oracle\product\10.2.0\admin\ERP\udump\erp_ora_1436.trcc:\bao.txtsys=nosys=no的含义：不包含递归SQL语句(即访问数据字典的隐含语句)SQL语句的处理要经过三个阶段：Parse、Execute、Fetch。需要优化的SQL语句：占用过多的CPU时间。Parse、Execute、Fetch阶段的时间太长。从磁盘读太多的数据块，而从内存中读很少的数据块。访问许多数据块，但只返回几条数据。TopSQL的使用(WEB方式)TopSQL用来代替Tkprof。TopSQL可以找出哪些SQL语句的性能差，需要优化。TopSQL中的数据来源于V$SQL。TopSessions的使用(WEB方式)TopSessions可以找出哪些会话占用较多的资源。2.SQL语句的解释计划(EXPLAINPLAN)通过解释计划，可以找出SQL语句性能低的原因。用SQLScratchPad来生成SQL语句的解释计划：用命令来生成SQL语句的解释计划：先检查sys用户下是否有plan_table表(9i中已经有了这个表)，如果没有，执行C:\oracle\product\10.2.0\db_1\rdbms\admin\utlxplan.sql脚本。SQLexplainplanforSELECTe.empno,e.ename,d.deptno,d.locFROMscott.empe,scott.deptdWHEREe.deptno=d.deptno;注意要commit。查询执行计划：SQLselectlpad('',4*(level-2))||operation||''||options||''||object_nameEXECUTION_PLANfromplan_tablestartwithid=0connectbypriorid=parent_id;3.STATSPACK报告中的SQL语句性能SQLorderedbyGets(按Gets排序的SQL语句)SQLorderedbyReads(按Reads排序的SQL语句)SQLorderedbyExecutions(按Executions排序的SQL语句)SQLorderedbyParseCalls(按ParseCalls排序的SQL语句4.Oracle优化方式优化方式：基于rule和cost.基于rule时的优化等级：根据语法和表结构优化1Singlerowbyrowid2Singlerowbyclusterjoin3Singlerowbyhashclusterkeywithuniqueorprimarykey4Singlerowbyuniqueorprimarykey5Clusterjoin6Hashclusterkey7Indexedclusterkey8Compositekey9Single-columnindexes10Boundedrangesearchonindexedcolumns11Unboundedrangesearchonindexedcolumns12Sort-mergejoin13MAXorMINofindexedcolumn14ORDERBYonindexedcolumns15Fulltablescan以SELECTempnoFROMempWHEREename='CHUNG'ANDsal2000;语句为例说明访问路径。分析应在哪个字段上创建索引？缺点：小表的全表扫描比索引效率高，索引字段值的差异性小。基于cost时的优化根据表和索引的统计信息优化，优先采用。根据表和索引的统计信息包括：每个表或索引的大小。每个表或索引所包括的数据行数。每个表或索引所使用的数据块数量。每个表行的字节数。索引字段值的差异性(基数)。5.统计信息的创建SQLANALYZETABLEemployeeCOMPUTESTATISTICS;SQLANALYZEINDEXemployee_last_name_idxCOMPUTESTATISTICS;查询统计信息，可用图形界面或DBA_TABLES。SQLANALYZETABLEemployeeDELETESTATISTICS;如果表或索引的数据量很大时，可以使用样本来创建统计信息：SQLANALYZETABLEemployeeESTIMATESTATISTICS;默认的样本大小为1064行。SQLANALYZETABLEemployeeESTIMATESTATISTICSSAMPLE500ROWS;SQLANALYZETABLEemployeeESTIMATESTATISTICSSAMPLE35PERCENT;创建字段的统计信息：SQLANALYZETABLEemployeeESTIMATESTATISTICSFORCOLUMNSemployee_idSIZE200;SIZE的默认值是75。可以是1到254。字段上的数据假设是正态分布。直方图：SQLANALYZETABLEfinaidCOMPUTESTATISTICSFORCOLUMNawardSIZE100;用图形界面创建统计信息。优化提示：SQLSELECT/*+FIRST_ROWS*/*FROMhr.employees;其它优化提示有：RULE、FULLSALES(访问SALES表)、INDEXSALES_ID_PK、PARALLEL。6.设置优化模式init.ora参数OPTIMIZER_MODE：CHOOSE、RULE、FIRST_ROWS(提高响应时间)、ALL_ROWS(提高吞吐量)。7.索引B-树索引:适合建在重复值少的字段。索引的统计信息，索引B-树的高度(建议4)。SQLANALYZEINDEXemployee_last_name_idxVALIDATESTRUCTURE;SQLSELECT(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100“WastedSpace”FROMindex_statsWHERENAME=“EMPLOYEE_LAST_NAME_IDX”;建议：索引的空闲空间(20)。重组索引：SQLalterindexscott.pk_deptrebuildonline;SQLalterindexscott.pk_deptcoalesce;压缩B树索引：适合于索引字段重复值多的情况SQLALTERINDEXemployee_last_name_idxREBUILDCOMPRESS;位图(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，才能创建函数索引。SQLSELECTlast_name,first_nameFROMemployeesWHEREUPPER(first_name)=’SMITH’;SQLCREATEINDEXhr.employee_first_name_upper_idxONhr.employees(UPPER(first_name));SQLSELECT*FROMsaleswhere(price*units)10000;SQLCREATEINDEXsales_total_sale_idxONsales(price*units)TABLESPACEINDX;反键索引：适用于序列字段。反键索引只适用于=和!=查询。使用Between、、查询不会使用反键索引优化。8.优化应用程序索引组织表(IOT)分区表簇:索引簇和散列簇。9.使用SQLAnalyze(对9i适用)例子：SELECTdepartment_name,department_idFROMhr.departmentsWHEREdepartment_idNOTIN(SELECTdepartment_idFROMhr.emplo