性能分析与调整1.Oracle的SQL执行计划2Auto_trace1 设置步骤 SQL conn system/oracle 已连接。SQL start rdbmsadminutlxplan 表已创建。SQL create public synonym plan_table for plan_table; 同义词已创建。SQL grant all on plan_table to public; 授权成功。SQL conn sys/oracle as sysdba 已连接。SQL start sqlplusadminplustrceSQL drop role plustrace; drop role plustraceSQL create role plustrace; 角色已创建SQL grant select on v_sesstat to plustrace; 授权成功。SQL grant select on v_statname to plustrace; 授权成功。SQL grant select on v_session to plustrace; 授权成功。SQL grant plustrace to dba with admin option; 授权成功。SQL set echo offSQL grant plustrace to public; 授权成功。SQL conn scott/tiger 已连接。SQL set autotrace on SQL select ename,sal from emp;ENAMESALSMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450KING5000TURNER1500JAMES950FORD3000MILLER1300已选择 12 行cution Plan0SELECT STATEMENT OptimizerCHOOSE1 0 TABLE ACCESS FULL OF EMPStatistics12 recursive calls0 db block gets92 consistent gets0 physical reads0 redo size588 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts memory0 sorts disk12 rows processed2 设置 autotrace 的一些选项set autotrace off 执行计划和统计信息都不显示，这是缺省的设置。set autortrace on explain 只显示执行计划。set autotrace on statistics 只显示统计信息。set autotrace on 执行计划和统计信息都显示。set autotrace trace only 类似于 set autotrace on 只是不显示查询结果。set autotrace trace only explain 只显示执行计划。set autotrace traceonly statistics 只显示统计结果。3 How to read a query plan4 关于statistics的解释recursive calls高 recursive calls的原因hard pares第二次执行同一语句即可使 recursive calls降低。可以通过两次同样的查询，验证上述结论。pl/sql function callsSQL create or replace function test return number2 as3 l_cnt number;4 begin5 select count* into l_cnt from dept;6 return l_cnt;7 end;8/ 函数已创建。SQL select ename,test from emp;TEST666666666666ENAMESMITH ALLEN WARD JONES MARTIN BLAKE CLARK KING TURNER JAMES FORD MILLER已选择 12 行cution Plan0 SELECT STATEMENT OptimizerCHOOSE1 0 TABLE ACCESS FULL OF EMPStatistics284 recursive calls0 db block gets144 consistent gets6 physical reads136 redo size579 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client9 sorts memory0 sorts diskTEST66666666666612 rows processedSQL /ENAMESMITH ALLEN WARD JONES MARTIN BLAKE CLARK KING TURNER JAMES FORD MILLER已选择 12 行cution Plan0 SELECT STATEMENT OptimizerCHOOSE1 0TABLE ACCESS FULL OF EMPStatistics12 recursive calls0 db block gets92 consistent gets0 physical reads0 redo size579 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts memory0 sorts disk12 rows processedSQL /ENAMETESTSMITH6ALLEN6WARD6JONES6MARTIN6BLAKE6CLARK6KING6TURNER6JAMES6FORD6MILLER6已选择 12 行。cution Plan0 SELECT STATEMENT OptimizerCHOOSE1 0TABLE ACCESS FULL OF EMPStatistics12 recursive calls0 db block gets92 consistent gets0 physical reads0 redo size579 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts memory0 sorts disk12 rows processedside effect from modification由于 triggers、基于函数的索引引起。
space requestDMT 表空间中的表要求空间时，会引起较多的 recursive calls,通过使用LMT ,可以显著减少这种recursive calls,而LMT中的recursive calls主要是由于验证quota权限引起。可 以通过实验验证上面的说法 实验步骤见 Effective Oracle by Design pp101。db block gets and consistent getsdb block get是以current mode读取的数据块数，通常是由于数据修 改而引起，consistent gets是以consistent mode读取的数据块数，通 常由于select操作引起。我们关注的是这两个数量之和，即逻辑 I/O 的数量，逻辑I/O也代表了对缓存加上latch的数量，逻辑I/O越少，越好。The less logical I/O we can do,the bette。我们可以通过设置合 适的arraysize许多方法中的一个，适用于 sql*plus来降低逻辑I/O 数量，ODBC，JDBC 也有类似的设置。Array sizeSQL conn system/oraclecatalog已连接。SQL grant dba to scott;授权成功。SQL conn scott/tigercatalog已连接。SQL drop table t;表已丢弃。SQL create table t2 as3 select * from all_objects;表已创建。SQL select count* from t;COUNT*6219已选择 6219 行。SQL set autotrace traceonly statisticsSQL select * from t;已选择 6219 行。Statistics0 recursive calls0 db block gets491 consistent gets0 physical reads0 redo size357171 bytes sent via SQL*Net to client5057bytes received via SQL*Net from client416SQL*Net roundtrips to/from client0sorts memory0sorts disk6219rows processedSQL show arraysize arraysize 15SQL set arraysize 2SQL select *from t;已选择 6219 行。Statistics0recursive calls0db block gets3156consistent gets0physical reads0redo size683239bytes sent via SQL*Net to client34702bytes received via SQL*Net from client3111SQL*Net roundtrips to/from client0sorts memory0sorts disk6219rows processedSQL set arraysize 4SQL /已选择 6219 行。Statistics0recursive calls0db block gets1618consistent gets0physical reads0redo size495111bytes sent via SQL*Net to client17597bytes received via SQL*Net from client1556SQL*Net roundtrips to/from client0 sorts memory0 sorts disk 6219 rows processed SQL set arraysize 8 SQL /已选择 6219 行。Statistics0 recursive calls0 db block gets853 consistent gets0 physical reads0 redo size401094 bytes sent via SQL*Net to client9050 bytes received via SQL*Net from client779SQL*Net roundtrips to/from client0sorts memory0sorts disk6219rows processedSQL set arraysize 16SQL /已选择 6219 行。Statistics0 recursive calls0 db block gets465 consistent gets0 physical reads0 redo size354025 bytes sent via SQL*Net to client4771 bytes received via SQL*Net from client390 SQL*Net roundtrips to/from client0 sorts memory0 sorts disk6219 rows processedphisical reads指Oracle把数据从硬盘读到内存的次数，也就是读 取到内存的数据块数，然后我们执行逻辑 I/O 从内存读取数据，因此，一般情况下，物理 I/O 都跟随着逻辑 I/O。phisical reads分为两种reading data in from datafiles对数据文件读取获得索引数据或者表数 据，这种 I/O 立刻跟随着逻辑 I/O。direct reads from temp 当 sort area或 hash area不能在内存中容纟内 sort data或hash data时，Oracle会把部分数据交换到temp表空间，然后 再读取，这种读取会越过 buffer cache,不会引发逻辑I/O。第一种物理I/O,我们不能避免，如果在第一次查询后，同样的查询 还需要物理I/O,，则可能是因为data buffer cache太小，在物理内存 足够的情况下，可以把 data buffer cache增大。对于第二种，我们可以通过设置合适的sort_area_size和 hash_area_size大小，来降低 phisical reads,注意，在 Oracle9i 中，要先把 workarea_size_policy参数设置为 manual，改动 sort_area_size 及hash_area_size参数才会生效，8i可以直接设置sort_area_size SQL conn scott/tigercatalog已连接。SQL show parameter workNAME TYPE VALUE workarea_size_policystring AUTOSQL alter session set workarea_size_policymanual;会话已更改。SQL alter session set sort_area_size0;会话已更改。SQL set autotrace traceonly statisticsSQL select * from t order by object_id;已选择 6219行。Statistics02480214 0 219216767recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client26 SQL*Net roundtrips to/from client0 sorts memory1 sorts disk6219 rows processedSQL /已选择 6219 行。Statistics0recursive calls22db block gets80consistent gets212physical reads0redo size219216bytes sent via SQL*Net to client767bytes received via SQL*Net from client26SQL*Net roundtrips to/from client0sorts memory1sorts disk6219rows processedSQL alter session set sort_area_size1024; 会话已更改。SQL select * from t order by object_id; 已选择 6219 行。Statistics0recursive calls59db block gets80consistent gets435physical reads0redo size219216bytes sent via SQL*Net to client767bytes received via SQL*Net from client26SQL*Net roundtrips to/from client0sorts memory1 sorts disk 6219 rows processedSQL /已选择 6219 行。Statistics0recursive calls59db block gets80consistent gets437physical reads0redo size219216bytes sent via SQL*Net to client767bytes received via SQL*Net from client26SQL*Net roundtrips to/from client0sorts memory1sorts disk6219rows processedSQL alter session set sort_area_size102400; 会话已更改。SQL select * from t order by object_id; 已选择 6219 行。Statistics0recursive calls19db block gets80consistent gets198physical reads0redo size219216bytes sent via SQL*Net to client767bytes received via SQL*Net from client26SQL*Net roundtrips to/from client0sorts memory1sorts disk6219rows processedSQL alter session set sort_area_size10240000;会话已更改。SQL select * from t order by object_id; 已选择 6219 行。Statistics0 recursive calls0 db block gets80 consistent gets0 physical reads0 redo size219216 bytes sent via SQL*Net to client767 bytes received via SQL*Net from client26 SQL*Net roundtrips to/from client1 sorts memory0 sorts disk6219 rows processedSQL alter session set sort_area_size0; 会话已更改。SQL select * from t order by object_id; 已选择 6219 行。Statistics0recursive calls22db block gets80consistent gets212physical reads0redo size219216bytes sent via SQL*Net to client767bytes received via SQL*Net from client26SQL*Net roundtrips to/from client0sorts memory1 sorts disk6219 rows processedredo size主要在bulk insert操作CTAS及insertselect时需要调整，一般的 merge insert、delete、update语句我们不能控制其 redo大小。在归 档模式下，要把表设置为 nologging,insert数据时，加上 即pend提 示。alter table test_redo nologging;insert /* append */ into test_redo select * from all_objects;非归档模式，不需要把表设置为nologging，只要加上append提示，也会降低redo size的大小。如果表建有索引，则要把索引停用，否则依然有大量的redoalter index idx_test unusable;alter session set skip_unusable_indstrue;alter index idx_test rebuild nologging;sql*net statisticssorts and rows processed3SQL_TRACE 的使用方法1 设置步骤SQL conn system/oracleSQL alter system set sql_tracetrue scopespfile;SQL alter system set timed_statisticstrue;SQL conn sys/oracle as sysdbaSQL startup forceSQL grant dba to scoott;SQL conn scott/tigerSQL select * from dept;SQL select a.spid from vprocess a,vsession b2 where a.addrb.paddr3 and b.audsiduserenvsessionid4 /SPID2756Ctkprof eoracleadminoemrepudumpoemrep_ora_2756.trc creport.txt2 报告内容TKPROF Release 9.2.0.1.0 - Production on 星期二 2 月 17 211216 2004Copyright c 1982,2002,Oracle Corporation.All rights reserved.Trace file eoracleadminoemrepudumpoemrep_ora_2756.trc Sort options default********************************************************************************count number of times OCI procedure was cuted cpu cpu time in seconds cuting elapsed elapsed time in seconds cuting disk number of physical reads of buffers from diskquery number of buffers gotten for consistent read current number of buffers gotten in current mode usually for update rows number of rows processed by the fetch or cute call********************************************************************************select * from deptcallcountcpuelapseddiskquerycurrentrowsParse10.000.000000cute10.000.000000Fetch20.000.000404total40.000.000404Misses in library cache during parse 0Optimizer goal CHOOSEParsing user id 57Rows Row Source Operation4 TABLE ACCESS FULL DEPT3）报告中各个项目的含义count查询执行的次数，正常情况下，一个SQL语句parse一次，cute 一次或多于一次。Cpu语句执行花费的cpu时间，以1/1000秒为单位。Elapsed执行这条语句一共花费的时间，包括 cpu时间和等待时间，女口 果elapsed相对cpu很大的话，说明此语句在等待某些事件，具体事件类 型可以通过文件底部的内容看出。如果 cpu超过elapsed是因为计时精 确度的不同所引起的误差，实际是不可能存在这种情况的。Disk phisical reads（gets。）Qurey consistent reads（gets。）Current current reads。Rows在select操作中，出现在fetch栏目，在修改操作中，出现在cute 栏目中。4）报告中的执行计划是真正的执行结果，与autotrace的区别是，它包含每个步骤流出的 rows 数量。5）当设置set eve nt时，在报告的最后将出现 wait eve ntsalter session set events10046 trace name context forever,level 12