Oracle10g优化鼎力推荐.docx
《Oracle10g优化鼎力推荐.docx》由会员分享,可在线阅读,更多相关《Oracle10g优化鼎力推荐.docx(17页珍藏版)》请在冰豆网上搜索。
Oracle10g优化鼎力推荐
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。
这种方法会产生大量的系统开销。
用户级跟踪:
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的性能