Oracle10g优化鼎力推荐Word文档格式.docx
《Oracle10g优化鼎力推荐Word文档格式.docx》由会员分享,可在线阅读,更多相关《Oracle10g优化鼎力推荐Word文档格式.docx(17页珍藏版)》请在冰豆网上搜索。
execsys.dbms_system.set_sql_trace_in_session(10,87,true);
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用户登录。
@c:
\oracle\product\10.2.0\db_1\rdbms\admin\spcreate.sql
用PERFSTAT用户登录。
executeSTATSPACK.SNAP;
至少要有两个快照。
用PERFSTAT用户登录,生成报告文件:
\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脚本。
explainplanforSELECTe.empno,e.ename,d.deptno,d.loc
FROMscott.empe,scott.deptdWHEREe.deptno=d.deptno;
注意要commit。
查询执行计划:
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.统计信息的创建
ANALYZETABLEemployeeCOMPUTESTATISTICS;
ANALYZEINDEXemployee_last_name_idxCOMPUTESTATISTICS;
查询统计信息,可用图形界面或DBA_TABLES。
ANALYZETABLEemployeeDELETESTATISTICS;
如果表或索引的数据量很大时,可以使用样本来创建统计信息:
ANALYZETABLEemployeeESTIMATESTATISTICS;
默认的样本大小为1064行。
ANALYZETABLEemployeeESTIMATESTATISTICS
SAMPLE500ROWS;
SAMPLE35PERCENT;
创建字段的统计信息:
FORCOLUMNSemployee_idSIZE200;
SIZE的默认值是75。
可以是1到254。
字段上的数据假设是正态分布。
直方图:
ANALYZETABLEfinaidCOMPUTESTATISTICSFORCOLUMNawardSIZE100;
用图形界面创建统计信息。
优化提示:
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)。
ANALYZEINDEXemployee_last_name_idxVALIDATESTRUCTURE;
SELECT(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100“WastedSpace”FROMindex_statsWHERENAME=“EMPLOYEE_LAST_NAME_IDX”;
建议:
索引的空闲空间(<
20)。
重组索引:
alterindexscott.pk_deptrebuildonline;
alterindexscott.pk_deptcoalesce;
压缩B树索引:
适合于索引字段重复值多的情况
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,才能创建函数索引。
SELECTlast_name,first_nameFROMemployees
WHEREUPPER(first_name)=’SMITH’;
CREATEINDEXhr.employee_first_name_upper_idx
ONhr.employees(UPPER(first_name));
SELECT*FROMsaleswhere(price*units)>
10000;
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
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’;
调整共享池有关的init.ora参数
OPEN_CURSORS:
建议值500。
CURSOR_SPACE_FOR_TIME:
建议值TRUE。
SESSION_CACHED_CURSORS:
CURSOR_SHARING:
默认值为EXACT。
建议设成SIMILAR或FORCE。
第四章调整缓冲区高速缓存(BufferCache)的性能
1.BufferCache的工作原理
BufferCache由数据块组成。
LRU列表:
MRU……………….LRU。
(全表扫描FTS放在LRU端。
)
缓冲区块的状态:
Free、Pinned、Clean、Dirty。
DirtyList或WriteList(写列表)。
数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。
2.测量BufferCache的性能
测量BufferCache的命中率:
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”的值要>
使用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的值:
SELECTname,bytesFROMV$sgastatWHEREpool=‘largepool’;
JAVA_POOL池的默认大小为32M。
对于大型Java应用程序,JAVA_POOL池的大小应大于50M。
init.ora参数java_pool_size
从V$sgastat中监视freememory的值。
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”的值要<
Selectname,valuefromV$sysstatwherename=’redologspacerequests’;
如果该值大,需要增加RedoLogBuffer。
2.提高RedoLogBuffer的性能