Oracle优化.docx

Oracle优化.docx

《Oracle优化.docx》由会员分享，可在线阅读，更多相关《Oracle优化.docx（21页珍藏版）》请在冰豆网上搜索。

Oracle优化

Oracle10g性能调整与优化

第一章收集性能数据

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。

这种方法会产生大量的系统开销。

用户级跟踪：

SQl>altersessionsetsql_trace=true;

SQl>altersessionsetsql_trace=false;

DBA跟踪：

SQL>execsys.dbms_system.set_sql_trace_in_session（10,87,true）;

SQL>execsys.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用户登录。

SQL>executeSTATSPACK.SNAP;

至少要有两个快照。

用PERFSTAT用户登录，生成报告文件：

SQL>@c:

\oracle\product\10.2.0\db_1\rdbms\admin\spreport.sql

4.图形性能工具（WEB方式）

第二章优化SQL语句和应用程序

1.测量SQL语句的性能

Tkprof（TraceKernelProfile）工具的使用

功能：

测量SQL语句的性能。

c:

\>tkprof

c:

\oracle\product\10.2.0\admin\ERP\udump\erp_ora_1436.trcc:

\bao.txtsys=no

sys=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脚本。

SQL>explainplanforSELECTe.empno,e.ename,d.deptno,d.loc

FROMscott.empe,scott.deptdWHEREe.deptno=d.deptno;

注意要commit。

查询执行计划：

SQL>selectlpad（'',4*（level-2））||operation||''||options||''||object_name"EXECUTION_PLAN"fromplan_tablestartwithid=0connectbypriorid=parent_id;

3.STATSPACK报告中的SQL语句性能

SQLorderedbyGets（按Gets排序的SQL语句）

SQLorderedbyReads（按Reads排序的SQL语句）

SQLorderedbyExecutions（按Executions排序的SQL语句）

SQLorderedbyParseCalls（按ParseCalls排序的SQL语句

4.Oracle优化方式

优化方式：

基于rule和cost.

基于rule时的优化等级：

根据语法和表结构优化

1Singlerowbyrowid

2Singlerowbyclusterjoin

3Singlerowbyhashclusterkeywithuniqueorprimarykey

4Singlerowbyuniqueorprimarykey

5Clusterjoin

6Hashclusterkey

7Indexedclusterkey

8Compositekey

9Single-columnindexes

10Boundedrangesearchonindexedcolumns

11Unboundedrangesearchonindexedcolumns

12Sort-mergejoin

13MAXorMINofindexedcolumn

14ORDERBYonindexedcolumns

15Fulltablescan

以SELECTempnoFROMempWHEREename='CHUNG'ANDsal>2000;语句为例说明访问路径。

分析应在哪个字段上创建索引？

缺点：

小表的全表扫描比索引效率高，索引字段值的差异性小。

基于cost时的优化根据表和索引的统计信息优化，优先采用。

根据表和索引的统计信息包括：

每个表或索引的大小。

每个表或索引所包括的数据行数。

每个表或索引所使用的数据块数量。

每个表行的字节数。

索引字段值的差异性（基数）。

5.统计信息的创建

SQL>ANALYZETABLEemployeeCOMPUTESTATISTICS;

SQL>ANALYZEINDEXemployee_last_name_idxCOMPUTESTATISTICS;

查询统计信息，可用图形界面或DBA_TABLES。

SQL>ANALYZETABLEemployeeDELETESTATISTICS;

如果表或索引的数据量很大时，可以使用样本来创建统计信息：

SQL>ANALYZETABLEemployeeESTIMATESTATISTICS;

默认的样本大小为1064行。

SQL>ANALYZETABLEemployeeESTIMATESTATISTICS

SAMPLE500ROWS;

SQL>ANALYZETABLEemployeeESTIMATESTATISTICS

SAMPLE35PERCENT;

创建字段的统计信息：

SQL>ANALYZETABLEemployeeESTIMATESTATISTICS

FORCOLUMNSemployee_idSIZE200;

SIZE的默认值是75。

可以是1到254。

字段上的数据假设是正态分布。

直方图：

SQL>ANALYZETABLEfinaidCOMPUTESTATISTICSFORCOLUMNawardSIZE100;

用图形界面创建统计信息。

优化提示：

SQL>SELECT/*+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）。

SQL>ANALYZEINDEXemployee_last_name_idxVALIDATESTRUCTURE;

SQL>SELECT（DEL_LF_ROWS_LEN/LF_ROWS_LEN）*100“WastedSpace”FROMindex_statsWHERENAME=“EMPLOYEE_LAST_NAME_IDX”;

建议：

索引的空闲空间（<20）。

重组索引：

SQL>alterindexscott.pk_deptrebuildonline;

SQL>alterindexscott.pk_deptcoalesce;

压缩B树索引：

适合于索引字段重复值多的情况

SQL>ALTERINDEXemployee_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，才能创建函数索引。

SQL>SELECTlast_name,first_nameFROMemployees

WHEREUPPER（first_name）=’SMITH’;

SQL>CREATEINDEXhr.employee_first_name_upper_idx

ONhr.employees（UPPER（first_name））;

SQL>SELECT*FROMsaleswhere（price*units）>10000;

SQL>CREATEINDEXsales_total_sale_idx

ONsales（price*units）TABLESPACEINDX;

反键索引：

适用于序列字段。

反键索引只适用于=和!

=查询。

使用Between、>、<查询不会使用反键索引优化。

8.优化应用程序

索引组织表（IOT）

分区表

簇:

索引簇和散列簇。

9.使用SQLAnalyze（对9i适用）

例子：

SELECTdepartment_name,department_id

FROMhr.departmentsWHEREdepartment_idNOTIN（SELECTdepartment_idFROMhr.employees）

使用IndexTuningWizard。

10.并行查询

并行查询可以优化：

表扫描、join语句、sort、notin、groupby、selectdistinct、unionandunionall、aggregation、PL/SQLfunctions、orderby、createtableasselect、createindex、rebuildindex、insert...select、enableconstraint、cube、rollup。

用在多CPU和磁盘阵列。

表和索引的并行度。

ALTERTABLEempPARALLEL4;

ALTERINDEXiempPARALLEL;

SELECT/*+PARALLEL（emp,4）*/COUNT（*）FROMemp;

调整开发系统和生产系统。

应用程序、SQL、内存、I/O、锁定、OS。

第三章调整共享池（SharedPool）的性能

1.监视共享池的性能

共享池由librarycache和dictionarycache组成。

采用LRU（LeastRecentlyUsed）算法管理。

librarycache用于缓存执行的SQL语句和PL/SQL程序。

dictionarycache用于缓存数据字典。

共享池有关的数据字典：

V$SQL、V$SQLAREA、V$SQLTEXT、V$SQL_PLAN。

（V$session）。

测量librarycache的命中率：

V$librarycache

SQL>selectnamespace,gethitratio,pinhitratio,reloads,invalidations

fromv$librarycachewherenamespacein（‘SQLAREA’,’TABLE/PROCEDURE’,’BODY’,’TRIGGER’）;

SQLAREA部分的gethitratio、pinhitratio要>90%。

GETS（语法分析）。

PINS（执行）。

RELOADS（SQL语句需要重新语法分析）、

INVALIDATIONS（SQL语句所引用的表结构发生变化，或视图重新编译）。

selectSUM（reloads）/SUM（pins）“ReloadRatio”fromV$librarycache;

重新装载率ReloadRatio要<1%。

使用STATSPACK来监视librarycache。

使用REPORT.TXT来监视librarycache。

使用PerformanceManager（内存、数据库例程、SQL）来监视librarycache。

SGA内存的经验公式。

SGA=55%物理内存

SharedPool=45%SGA

Buffercache=45%SGA

RedoLogCache=10%SGA

测量dictionarycache的命中率：

V$rowcache

select1-（SUM（getmisses）/SUM（gets））“DataDictionaryHitRatio”fromV$rowcache;

“DataDictionaryHitRatio”的值要>85%。

使用STATSPACK来监视dictionarycache。

使用REPORT.TXT来监视dictionarycache。

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*FROMEMPWHEREename=‘Smith’;

SELECT*FROMEMPWHEREename=‘John’;

改写为：

v_ename=‘Smith’;

Select*fromempwhereename=v_ename;

v_ename=‘John’;

Select*fromempwhereename=v_ename;

调整共享池有关的init.ora参数

OPEN_CURSORS：

建议值500。

CURSOR_SPACE_FOR_TIME：

建议值TRUE。

SESSION_CACHED_CURSORS：

建议值TRUE。

CURSOR_SHARING：

默认值为EXACT。

建议设成SIMILAR或FORCE。

第四章调整缓冲区高速缓存（BufferCache）的性能

1.BufferCache的工作原理

BufferCache由数据块组成。

LRU列表：

MRU……………….LRU。

（全表扫描FTS放在LRU端。

）

缓冲区块的状态：

Free、Pinned、Clean、Dirty。

DirtyList或WriteList（写列表）。

数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。

2.测量BufferCache的性能

测量BufferCache的命中率：

SQL>select1-（（physical.value–direct.value–lobs.value）/logical.value）“BufferCacheHitRatio”fromV$SYSSTATphysical,V$SYSSTATdirect,V$SYSSTATlobs,V$SYSSTATlogicalwherephysical.name=‘physicalreads’

Anddirect.name=‘physicalreadsdirect’and

lobs.name=‘physicalreadsdirect（lob）’

Andlogical.name=‘sessionlogicalreads’;

“BufferCacheHitRatio”的值要>90%。

使用STATSPACK来监视BufferCache。

使用REPORT.TXT来监视Buffercache。

非命中率指标：

FreeBufferInspected。

（V$sysstat）

FreeBufferWaits、BufferBusyWaits。

（V$system_event）

3.提高缓冲区高速缓存性能的方法

加大BufferCache的大小：

init.ora参数DB_CACHE_SIZE（动态参数）。

使用BufferCacheAdvisory功能决定BufferCache的大小：

首先将init.ora参数DB_CACHE_ADVICE设成ON，然后查询V$DB_CACHE_ADVICE。

使用多个缓冲区池：

KeepPool:

DB_KEEP_CACHE_SIZE

RecyclePool:

DB_RECYCLE_CACHE_SIZE

DefaultPool:

DB_CACHE_SIZE

在内存中缓存表:

表的CACHE选项，对优化小表的全表扫描。

正确创建索引。

4.调整LargePool和JAVAPOOL

LargePool用于共享服务器、RMAN、并行查询、DBWR的从属进程。

LargePool的大小通过init.ora参数Large_pool_size设置。

默认为8M。

从V$sgastat中监视freememory的值：

SQL>SELECTname,bytesFROMV$sgastatWHEREpool=‘largepool’;

JAVA_POOL池的默认大小为32M。

对于大型Java应用程序，JAVA_POOL池的大小应大于50M。

init.ora参数java_pool_size

从V$sgastat中监视freememory的值。

SQL>SELECTname,bytesFROMV$sgastatWHEREpool=‘javapool’;

第五章调整重做有关的性能

Oracle重做有关的组件包括：

RedoLogBuffer、OnlineRedoLog、LGWR、ArchiveLog、Checkpoint、Arch0。

1.监视RedoLogBuffer的性能

RedoLogBuffer不采用LRU（LeastRecentlyUsed）算法管理。

当下列事件发生时，RedoLogBuffer的内容存盘：

Commit时、每3秒、空间使用1/3、达到1M、检查点。

如果写入RedoLogBuffer的速度超过LGWR存盘的速度，就会因等待而降低性能。

监视RedoLogBuffer的重试率（<1%）。

Selectretries.value/entries.value“RedoLogBufferRetryRatio”

FromV$sysstatretries,V$sysstatentries

Whereretries.name=‘redobufferallocationretries’

Andentries.name=‘redoentries’;

“RedoLogBufferRetryRatio”的值要<1%。

Selectname,valuefromV$sysstatwherename=’redologspacerequests’;

如果该值大，需要增加RedoLogBuffer。

2.提高RedoLogBuffer的性能

增加RedoLogBuffer的大小：

init.ora参数log_buffer。

减小重做日志的生成量。

（如果设置表的NOLOGGING属性，下列操作不记录在OnlineRedoLog中：

用SQL*Loader的直接路径加载。