Oracle性能分析和调整.docx
《Oracle性能分析和调整.docx》由会员分享，可在线阅读，更多相关《Oracle性能分析和调整.docx（21页珍藏版）》请在冰点文库上搜索。
Oracle性能分析和调整
性能分析与调整
1．Oracle的SQL执行计划
2．Auto_trace
1）设置步骤：
SQL>connsystem/oracle
已连接。
SQL>start?
\rdbms\admin\utlxplan
表已创建。
SQL>createpublicsynonymplan_tableforplan_table;
同义词已创建。
SQL>grantallonplan_tabletopublic;
授权成功。
SQL>connsys/oracleassysdba
已连接。
SQL>start?
\sqlplus\admin\plustrce
SQL>droproleplustrace;
droproleplustrace
SQL>createroleplustrace;
角色已创建
SQL>grantselectonv_$sesstattoplustrace;
授权成功。
SQL>grantselectonv_$statnametoplustrace;
授权成功。
SQL>grantselectonv_$sessiontoplustrace;
授权成功。
SQL>grantplustracetodbawithadminoption;
授权成功。
SQL>setechooff
SQL>grantplustracetopublic;
授权成功。
SQL>connscott/tiger
已连接。
SQL>setautotraceon
SQL>selectename,salfromemp;
ENAMESAL
--------------------
SMITH800
ALLEN1600
WARD1250
JONES2975
MARTIN1250
BLAKE2850
CLARK2450
KING5000
TURNER1500
JAMES950
FORD3000
MILLER1300
已选择12行。
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'EMP'
Statistics
----------------------------------------------------------
12recursivecalls
0dbblockgets
92consistentgets
0physicalreads
0redosize
588bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
12rowsprocessed
2）设置autotrace的一些选项：
setautotraceoff：
执行计划和统计信息都不显示，这是缺省的设置。
setautortraceonexplain：
只显示执行计划。
setautotraceonstatistics：
只显示统计信息。
setautotraceon：
执行计划和统计信息都显示。
setautotracetraceonly：
类似于setautotraceon，只是不显示查询结果。
setautotracetraceonlyexplain：
只显示执行计划。
setautotracetraceonlystatistics：
只显示统计结果。
3）Howtoreadaqueryplan
4）关于statistics的解释
✓recursivecalls：
高recursivecalls的原因：
Øhardpares：
第二次执行同一语句即可使recursivecalls降低。
可以通过两次同样的查询，验证上述结论。
Øpl/sqlfunctioncalls：
SQL>createorreplacefunctiontestreturnnumber
2as
3l_cntnumber;
4begin
5selectcount（*）intol_cntfromdept;
6returnl_cnt;
7end;
8/
函数已创建。
SQL>selectename,testfromemp;
ENAMETEST
--------------------
SMITH6
ALLEN6
WARD6
JONES6
MARTIN6
BLAKE6
CLARK6
KING6
TURNER6
JAMES6
FORD6
MILLER6
已选择12行。
ExecutionPlan
--------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'EMP'
Statistics
--------------------------------------------------------
284recursivecalls
0dbblockgets
144consistentgets
6physicalreads
136redosize
579bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
9sorts（memory）
0sorts（disk）
12rowsprocessed
SQL>/
ENAMETEST
--------------------
SMITH6
ALLEN6
WARD6
JONES6
MARTIN6
BLAKE6
CLARK6
KING6
TURNER6
JAMES6
FORD6
MILLER6
已选择12行。
ExecutionPlan
--------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'EMP'
Statistics
--------------------------------------------------------
12recursivecalls
0dbblockgets
92consistentgets
0physicalreads
0redosize
579bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
12rowsprocessed
SQL>/
ENAMETEST
--------------------
SMITH6
ALLEN6
WARD6
JONES6
MARTIN6
BLAKE6
CLARK6
KING6
TURNER6
JAMES6
FORD6
MILLER6
已选择12行。
ExecutionPlan
--------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE
10TABLEACCESS（FULL）OF'EMP'
Statistics
--------------------------------------------------------
12recursivecalls
0dbblockgets
92consistentgets
0physicalreads
0redosize
579bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
12rowsprocessed
Øsideeffectfrommodification：
由于triggers、基于函数的索引引起。
Øspacerequest：
DMT表空间中的表要求空间时，会引起较多的recursivecalls，通过使用LMT，可以显著减少这种recursivecalls，而LMT中的recursivecalls主要是由于验证quota权限引起。
可以通过实验验证上面的说法（实验步骤见EffectiveOraclebyDesignpp101）。
✓dbblockgetsandconsistentgets
dbblockget是以currentmode读取的数据块数，通常是由于数据修改而引起，consistentgets是以consistentmode读取的数据块数，通常由于select操作引起。
我们关注的是这两个数量之和，即逻辑I/O的数量，逻辑I/O也代表了对缓存加上latch的数量，逻辑I/O越少，越好。
ThelesslogicalI/Owecando,thebetter。
我们可以通过设置合适的arraysize（许多方法中的一个，适用于sql*plus）来降低逻辑I/O数量，ODBC，JDBC也有类似的设置。
Arraysize：
SQL>connsystem/oracle@catalog
已连接。
SQL>grantdbatoscott;
授权成功。
SQL>connscott/tiger@catalog
已连接。
SQL>droptablet;
表已丢弃。
SQL>createtablet
2as
3select*fromall_objects;
表已创建。
SQL>selectcount（*）fromt;
COUNT（*）
----------
6219
已选择6219行。
SQL>setautotracetraceonlystatistics
SQL>select*fromt;
已选择6219行。
Statistics
------------------------------------------------------
0recursivecalls
0dbblockgets
491consistentgets
0physicalreads
0redosize
357171bytessentviaSQL*Nettoclient
5057bytesreceivedviaSQL*Netfromclient
416SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
6219rowsprocessed
SQL>showarraysize
arraysize15
SQL>setarraysize2
SQL>select*fromt;
已选择6219行。
Statistics
----------------------------------------------------
0recursivecalls
0dbblockgets
3156consistentgets
0physicalreads
0redosize
683239bytessentviaSQL*Nettoclient
34702bytesreceivedviaSQL*Netfromclient
3111SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
6219rowsprocessed
SQL>setarraysize4
SQL>/
已选择6219行。
Statistics
----------------------------------------------------
0recursivecalls
0dbblockgets
1618consistentgets
0physicalreads
0redosize
495111bytessentviaSQL*Nettoclient
17597bytesreceivedviaSQL*Netfromclient
1556SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
6219rowsprocessed
SQL>setarraysize8
SQL>/
已选择6219行。
Statistics
------------------------------------------------------
0recursivecalls
0dbblockgets
853consistentgets
0physicalreads
0redosize
401094bytessentviaSQL*Nettoclient
9050bytesreceivedviaSQL*Netfromclient
779SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
6219rowsprocessed
SQL>setarraysize16
SQL>/
已选择6219行。
Statistics
------------------------------------------------------
0recursivecalls
0dbblockgets
465consistentgets
0physicalreads
0redosize
354025bytessentviaSQL*Nettoclient
4771bytesreceivedviaSQL*Netfromclient
390SQL*Netroundtripsto/fromclient
0sorts（memory）
0sorts（disk）
6219rowsprocessed
✓phisicalreads：
指Oracle把数据从硬盘读到内存的次数，也就是读取到内存的数据块数，然后我们执行逻辑I/O从内存读取数据，因此，一般情况下，物理I/O都跟随着逻辑I/O。
phisicalreads分为两种：
readingdatainfromdatafiles：
对数据文件读取获得索引数据或者表数据，这种I/O立刻跟随着逻辑I/O。
directreadsfromtemp：
当sortarea或hasharea不能在内存中容纳sortdata或hashdata时，Oracle会把部分数据交换到temp表空间，然后再读取，这种读取会越过buffercache，不会引发逻辑I/O。
第一种物理I/O，我们不能避免，如果在第一次查询后，同样的查询还需要物理I/O,，则可能是因为databuffercache太小，在物理内存足够的情况下，可以把databuffercache增大。
对于第二种，我们可以通过设置合适的sort_area_size和hash_area_size大小，来降低phisicalreads，注意，在Oracle9i中，要先把workarea_size_policy参数设置为manual，改动sort_area_size及hash_area_size参数才会生效，8i可以直接设置sort_area_size。
SQL>connscott/tiger@catalog
已连接。
SQL>showparameterwork
NAMETYPEVALUE
------------------------------------------------------------------------
workarea_size_policystringAUTO
SQL>altersessionsetworkarea_size_policy=manual;
会话已更改。
SQL>altersessionsetsort_area_size=0;
会话已更改。
SQL>setautotracetraceonlystatistics
SQL>select*fromtorderbyobject_id;
已选择6219行。
Statistics
----------------------------------------------------------
0recursivecalls
24dbblockgets
80consistentgets
214physicalreads
0redosize
219216bytessentviaSQL*Nettoclient
767bytesreceivedviaSQL*Netfromclient
26SQL*Netroundtripsto/fromclient
0sorts（memory）
1sorts（disk）
6219rowsprocessed
SQL>/
已选择6219行。
Statistics
----------------------------------------------------------
0recursivecalls
22dbblockgets
80consistentgets
212physicalreads
0redosize
219216bytessentviaSQL*Nettoclient
767bytesreceivedviaSQL*Netfromclient
26SQL*Netroundtripsto/fromclient
0sorts（memory）
1sorts（disk）
6219rowsprocessed
SQL>altersessionsetsort_area_size=1024;
会话已更改。
SQL>select*fromtorderbyobject_id;
已选择6219行。
Statistics
----------------------------------------------------------
0recursivecalls
59dbblockgets
80consistentgets
435physicalreads
0redosize
219216bytessentviaSQL*Nettoclient
767bytesreceivedviaSQL*Netfromclient
26SQL*Netroundtripsto/fromclient
0sorts（memory）
1sorts（disk）
6219rowsprocessed
SQL>/
已选择6219行。
Statistics
----------------------------------------------------------
0recursivecalls
59dbblockgets
80consistentgets
437physicalreads
0redosize
219216bytessentviaSQL*Nettoclient
767bytesreceivedviaSQL*Netfromclient
26SQL*Netroundtripsto/fromclient
0sorts（memory）
1sorts（disk）
6219rowsprocessed
SQL>altersessionsetsort_area_size=102400;
会话已更改。
SQL>select*fromtorderbyobject_id;
已选择6219行。
Statistics
----------------------------------------------------------
0recursivecalls
19dbblockgets
80consistentgets
198physicalreads
0redosize
219216bytessentviaSQL*Nettoclient
767bytesreceivedviaSQL*Netfromclient
26SQL*Netroundtripsto/fromclient
0sorts（memory）
1sorts（disk）
6219rowsprocessed
SQL>altersessions
升级会员 