sql tunning

SQL TUNINGStep1:Check current sql long running statusselect a.sid,a.serial#,a.program, b.pid,b.spid,c.sql_text,c.SQL_ID,c.status,a.statusfrom v$session a, v$process b, v$sqlarea cwhere a.paddr = b.addrand a.sql_hash_value = c.hash_valueand a.username is not null;查询应用的连接数SQL:SELECT b.MACHINE, b.PROGRAM, COUNT (*)FROM v$process a, v$session bWHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULLGROUP BY b.MACHINE, b.PROGRAMORDER BY COUNT (*) DESC;Check the history for sqlplanSELECT sql_id,plan_hash_value,SUM(elapsed_time_total)/SUM(executions_total)/1000000 avg_elapsedt_secsFROM dba_hist_sqlstat;select * from table(dbms_xplan.display_cursor('71t6mr7ht84v4'));select * from table(dbms_xplan.display_awr('71t6mr7ht84v4'));explain plan for sql;select * from table(dbms_xpaln.display_awr(‘sql_id’);select * from table(dbms_xpaln.display_cursor(‘sql_id’); select * from table(dbms_xplan.display);set autotrace onrun sqlset autotrace off方法1：explain for ; select * from table(dbms_xplan.display); 例如：SQL> explain plan for select * from t2;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| T2 | 1 | 128 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------8 rows selected.方法2：set autotrace on 执行 例如：SQL> set autotrace onSQL> select * from hr.t2;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 1513984157--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| T2 | 1 | 128 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1139 bytes sent via SQL*Net to client481 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processedSQL>注意：若是使用非DBA角色的用户下进行执行计划的查看,则要用DBA权限的用户通过执行plustrce.sql脚本来先创建角色‘PLUSTRACE’，然后将角色赋予当前用户。操作方法类似如下：SQL> show userUSER is "SYS"SQL> @?/sqlplus/admin/plustrce.sql--以下为自动输出SQL> create role plustrace;Role created.SQL>SQL> grant select on v_$sesstat to plustrace;Grant succeeded.SQL> grant select on v_$statname to plustrace;Grant succeeded.SQL> grant select on v_$mystat to plustrace;Grant succeeded.SQL> grant plustrace to dba with admin option;Grant succeeded.SQL>SQL> set echo off--以下需要手工输入该命令SQL> grant plustrace to hr;Grant succeeded.方法3：查看v$sql_plan表通过SQL语句的SQL_ID和子游标号，可以在V$SQL_PLAN表中查看到该SQL语句的执行计划。例如：执行了语句“select * from t1;',先通过v$sqlarea和v$sql找到该语句的SQL_ID和子游标号。SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%';SQL_ID SQL_TEXT------------- ----------------------------------------------------------------------------------------------------27uhu2q2xuu7r select * from t1bf45pybkumcx5 select sql_id,sql_text from v$sqlarea where sql_text like '%from t1%'SQL> select sql_id,child_number,sql_text from v$sql where sql_id='27uhu2q2xuu7r';SQL_ID CHILD_NUMBER SQL_TEXT------------- ------------ ----------------------------------------------------------------------------------------------------27uhu2q2xuu7r 0 select * from t1--通过以上两条查询语句，查得目标SQL语句的SQL_ID为 “27uhu2q2xuu7r”， 子游标号为“0”.SQL> select timestamp,operation,options,object_name,cost,id,parent_id from v$sql_plan where sql_id='27uhu2q2xuu7r' and child_number=0;TIMESTAMP OPERATION OPTIONS OBJECT_NAME COST IDPARENT_ID----------------- -------------------- ---------- ------------------------------ ---------- ---------- ----------20131221 20:49:14 SELECT STATEMENT 3 020131221 20:49:14 TABLE ACCESS FULL T1 3 1 0方法4：查到指定语句的SQL_ID和子游标号后（查找方法请见方法3），通过DBMS_XPLAN包进行查看。例如：假设目标语句的SQL_ID和子游标号同方法3.SQL> select * from table(dbms_xplan.display_cursor('27uhu2q2xuu7r',0,'TYPICAL'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID27uhu2q2xuu7r, child number 0-------------------------------------select * from t1Plan hash value: 3617692013--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 3 (100)| || 1 | TABLE ACCESS FULL| T1 | 6 | 96 | 3 (0)| 00:00:01 |PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------13 rows selected. . 会话级跟踪。SQL_TRACE的通常使用方式是仅跟踪一个会话。被跟踪的会话可以是您自己的，也可以是其它用户的会话。如果是自己的会话，只需要在SQL*PLUS中运行一下命令即可： SQL> alter session set sql_trace = true; 类似的如果取消对会话的跟踪，运行一下命令： SQL> alter session set sql_trace = false; 如果需要跟踪一个特定的会话，首先需要获取会话的SID和Serial#，这些信息可以在视图V$SESSION中获得，一旦知道了这两个参数，就可以运行一下命令： SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,true); 同样也可以使用这个过程关闭会话跟踪： SQL> execute SYS.dbms_system.set_sql_trace_in_session(13,9,false);2、跟踪文件的位置： 一旦为会话激活了SQL_TRACE，ORACLE就会在udump管理区创建跟踪文件，文件的目标位置由参数user_dump_dest来确定。每个操作都不会覆盖原来的文件，新的跟踪记录将会被追加到文件末尾。通常情况下，可以根据文件的修改时间判断目录下哪个文件是最新的文件。 SQL> show parameter user_dump_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ user_dump_dest string d:oracleadminora9iudump 也可以通过以下SQL来确定文件名： select d.value||''||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p, (select t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' and ( v.value = 0 or t.thread# = to_number(v.value) ) ) i, ( select value from sys.v$parameter where name = 'user_dump_dest' ) d ; TRACE_FILE_NAME -------------------------------------------------------------------------------- d:oracleadminora9iudumpora9i_ora_2060.trc 3、计时信息： 为了最大限度的利用跟踪文件，应该打开计时标志,通过参数TIMED_STATISTICTS=TRUE进行设置，这样可以对每个SQL语句的执行时间等进行记录，这个功能对系统性能的负担很小。 打开会话的计时信息: SQL> alter session set timed_statistics = true ; 打开数据库系统的计时信息 SQL> alter system set timed_statistics = true ; 4、TKPROF： 通过前三步的设置已经知道如何生成SQL跟踪文件了，ORACLE生成的跟踪文件阅读起来很困难（也就是易读性很差），可以看跟踪文件的一部分，执行以下SQL语句： SQL> select count(*) from sys_dept; COUNT(*) ---------- 16 执行完后，查看跟踪文件中这个语句的内容如下： PARSING IN CURSOR #1 len=31 dep=0 uid=62 oct=3 lid=62 tim=14727407741 hv=2200985491 ad='128e3820' select count(*) from sys_dept END OF STMT PARSE #1:c=0,e=16348,p=1,cr=31,cu=0,mis=1,r=0,dep=0,og=4,tim=14727407735 EXEC #1:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727407814 FETCH #1:c=0,e=15641,p=5,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=14727423807 ===================== PARSING IN CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727508742 hv=3517412409 ad='12bbcff4' begin :id := sys.dbms_transaction.local_transaction_id; end; END OF STMT PARSE #2:c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727508735 EXEC #2:c=0,e=144,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727508945 ===================== PARSING IN CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727587562 hv=3517412409 ad='12bbcff4' begin :id := sys.dbms_transaction.local_transaction_id; end; END OF STMT PARSE #2:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727587556 EXEC #2:c=0,e=97,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727587721 这样不仅阅读麻烦，并且也有很多内容难以理解。ORACLE提供了一个格式化跟踪文件的工具 - TKPROF( Transient Kernel Profiler ),通过这个工具能将SQL文件转化为分析人员容易理解的格式。 一般TKPROF工具的使用的简单方法,只用到了两个关键字:跟踪文件名和输出文件名 (TKPROF的具体请参阅其他资料): TKPROF 在命令行模式下运行(数据库在window2000下安装的) C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt 执行完后,在reprot.txt中查询刚才的语句内容如下: select count(*) from sys_dept call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.01 1 31 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.01 5 7 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 0.00 0.03 6 38 0 1 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 62 通过设置tkprof的关键字[EXPLAIN = ],也可以在跟踪文件中增加SQL语句的执行计划： C:>tkprof D:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt explain=test/test; ******************************************************************************** select count(*) from sys_dept call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.01 1 31 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.01 5 14 0 2 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 6 0.00 0.03 6 45 0 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 62 Rows Row Source Operation ------- --------------------------------------------------- 1 SORT AGGREGATE 16 TABLE ACCESS FULL SYS_DEPTSET SERVEROUTPUT ON DECLARE err_msg VARCHAR2(120); BEGIN dbms_output.enable (1000000); FOR err_num IN 10000..10999 LOOP err_msg := SQLERRM (-err_num); IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN dbms_output.put_line (err_msg); END IF; END LOOP; END; /1.select sid,serial#,username,osuser,machine from v$session;查询session的sid,serial#2.exec dbms_system.set_sql_trace_in_session(141,6,true);开始跟踪3.exec dbms_system.set_sql_trace_in_session(141,6,true) 停止跟踪4.tkprof *.trc *.txt5.查看文件6.get trace file name select d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from ( select p.spid from sys.v$mystat m,sys.v$session s,sys.v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p, ( select t.instance from sys.v$thread t,sys.v$parameter v where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i, ( select value from sys.v$parameter where name = 'user_dump_dest') d;CBO:Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划。一个查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。 对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。 在使用CBO时，需要有表和索引的统计数据(分析数据)作为基础数据，有了这些数据，CBO才能为各个执行计划计算出相对准确的代价，从而使CBO选择最佳的执行计划。所以定期的对表、索引进行分析是绝对必要的，这样才能使统计数据反映数据库中的真实情况。否则就会使CBO选择较差的执行计划，影响数据库的性能。分析操作不必做的太频繁，一般来说，每星期一次就足够了。切记如果想使用CBO，则必须定期对表和索引进行分析。a)捕获Oracle SQL语句b)产生SQL语句的执行计划；c)验证统计信息(SQL语句涉及到的表格是否做过分析)，表格信息(结果集的记录数，索引)，字段上面数据分布特点d)通过手工收集到的信息，形成自己理想的执行计划。e)如果做过分析，则重新分析相关表格或者做柱状图分析。f)如果没有做过分析，则通过尝试不同的Hint，从而获得合适的执行计划。g)当我们正常无法调优到位时，可以打开10053事件打开优化器的跟踪，看看Oracle如何选择的.如何捕获Oracle SQL语句捕获Oracle SQL语句的方法有如下几种：1．SQL TRACE或10046跟踪某个模块。2．PERFSTAT性能统计包，使用方法见附录二。3．V$SQL，V$SESSION_WAIT，V$SQL_TEXT如何查看执行计划查看SQL语句的执行计划有以下几种：1．Set autotrace on(set autotrace traceonly exp)2．Explain plan for …..@?/rdbms/admin/utlxpls.sql3．V$SQL_PLAN视图◆column operation format a16◆column "Query Plan" format a60◆column options format a15◆column object_nameformat a20◆column idformat 991. select id,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' ' 2. ||decode(id,0,'Cost = '||position) "Query Plan" 3. from (select * 4. from v$sql_plan 5. where address='&a') sql_plan 6. start with id = 0 7. connect by prior id = parent_id 4．第三方工具，如pl/sql developer,TOAD，以上介绍捕获Oracle SQL语句。5. 通过下面的sql查询执行计划是否发生变化：select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_timefrom dba_hist_sqlstat a, dba_hist_snapshot b where sql_id ='56s18gn1k19yp' and a.snap_id = b.snap_id order by instance_number, snap_id;SQL> select a.snap_id, a.sql_id, a.plan_hash_value,to_char(b.begin_interval_time,'yyyy-mm-dd hh24:mi:ss') 2 from dba_hist_sqlstat a, dba_hist_snapshot b 3 where sql_id ='56s18gn1k19yp' 4 and a.snap_id = b.snap_id 5 order by snap_id desc; www.2cto.com SNAP_ID SQL_ID PLAN_HASH_VALUE TO_CHAR(B.BEGIN_INT---------- ------------- --------------- ------------------- 30569 56s18gn1k19yp 947531627 2012-03-02 14:00:30 30558 56s18gn1k19yp 947531627 2012-03-02 03:00:12 30552 56s18gn1k19yp 947531627 2012-03-01 21:00:36 30551 56s18gn1k19yp 2658265176 2012-03-01 20:00:27 30550 56s18gn1k19yp 947531627 2012-03-01 19:00:21 30535 56s18gn1k19yp 947531627 2012-03-01 04:00:31 30527 56s18gn1k19yp 947531627 2012-02-29 20:00:37 30524 56s18gn1k19yp 947531627 2012-02-29 17:00:24 30521 56s18gn1k19yp 947531627 2012-02-29 14:00:11 30519 56s18gn1k19yp 947531627 2012-02-29 12:00:04 30518 56s18gn1k19yp 947531627 2012-02-29 11:00:59 30511 56s18gn1k19yp 947531627 2012-02-29 04:00:11 30510 56s18gn1k19yp 947531627 2012-02-29 03:00:04 30502 56s18gn1k19yp 947531627 2012-02-28 19:00:27 30501 56s18gn1k19yp 947531627 2012-02-28 18:00:22 30500 56s18gn1k19yp 947531627 2012-02-28 17:00:18 30498 56s18gn1k19yp 947531627 2012-02-28 15:00:11 30497 56s18gn1k19yp 947531627 2012-02-28 14:00:07 30491 56s18gn1k19yp 947531627 2012-02-28 08:00:25 30475 56s18gn1k19yp 947531627 2012-02-27 16:00:03 30464 56s18gn1k19yp 947531627 2012-02-27 05:00:06 30463 56s18gn1k19yp 947531627 2012-02-27 04:00:00 30461 56s18gn1k19yp 947531627 2012-02-27 02:00:51 30456 56s18gn1k19yp 947531627 2012-02-26 21:00:31 30448 56s18gn1k19yp 2658265176 2012-02-26 13:00:55 30440 56s18gn1k19yp 947531627 2012-02-26 05:00:33 30439 56s18gn1k19yp 947531627 2012-02-26 04:00:25 30438 56s18gn1k19yp 947531627 2012-02-26 03:00:17 30437 56s18gn1k19yp 947531627 2012-02-26 02:00:09 30428 56s18gn1k19yp 947531627 2012-02-25 17:00:16 30423 56s18gn1k19yp 947531627 2012-02-25 12:00:51 30418 56s18gn1k19yp 947531627 2012-02-25 07:00:05 30416 56s18gn1k19yp 947531627 2012-02-25 05:00:55 30415 56s18gn1k19yp 947531627 2012-02-25 04:00:50 30406 56s18gn1k19yp 947531627 2012-02-24 19:00:52 30398 56s18gn1k19yp 947531627 2012-02-24 11:00:12 30396 56s18gn1k19yp 947531627 2012-02-24 09:00:56 30392 56s18gn1k19yp 947531627 2012-02-24 05:00:23 30391 56s18gn1k19yp 947531627 2012-02-24 04:00:11 30388 56s18gn1k19yp 947531627 2012-02-24 01:00:46 30383 56s18gn1k19yp 947531627 2012-02-23 20:00:15 30380 56s18gn1k19yp 947531627 2012-02-23 17:00:56 30377 56s18gn1k19yp 947531627 2012-02-23 14:00:37 30377 56s18gn1k19yp 2658265176 2012-02-23 14:00:37我们注意到最近一次3月1号20点左右，执行计划发生了变化。具体查看这两种执行计划有什么区别：select sql_id,plan_hash_value,id,operation,options,object_owner,object_name,depth,cost,timestamp from DBA_HIST_SQL_PLANwhere sql_id ='56s18gn1k19yp' and plan_hash_value in (947531627,2658265176);SQL> select plan_hash_value,id,operation,options,object_name,depth,cost,timestamp 2 from DBA_HIST_SQL_PLAN www.2cto.com 3 where sql_id ='56s18gn1k19yp' 4 and plan_hash_value in (947531627,2658265176);我们从查询结果中可以看到不同：plan_hash_value = 947531627 --执行计划走索引plan_hash_value = 2658265176 --执行计划走全表扫描使用coe_xfr_sql_profile.sql可以发现两种执行计划的效率(AVG_ET_SECS)：SQL> @coe_xfr_sql_profile.sqlParameter 1: www.2cto.com SQL_ID (required)Enter value for 1: 56s18gn1k19yp PLAN_HASH_VALUE AVG_ET_SECS--------------- ----------- 947531627 .037 2658265176 24.646Parameter 2:PLAN_HASH_VALUE (required)Enter value for 2: 947531627 如何固定执行计划： 10g推荐使用sql profile来固定执行计划，coe_xfr_sql_profile.sql的本质也是调用sql profile来固定执行计划的。Alter session set events=’10053 trace name context forever ,level 1’Alter session set events=’10053 trace name context off’Show parameter dumpGet the directory name from the dump parameter name .and get the excute plan from the dump information.Desc DBA_HIST_SQL_PLANDesc DBA_HIST_SQLSTATDesc DBA_HIST_SNAPSHOT1) Gateher stats or analyze table and index for ervery week onceCheck last analyze for table and index:analyze table a compute statistics;analyze index inx_col12A compute statistics;Sample:select index_name,num_rows,last_analyzed from user_indexes where index_name=’TEST_IDX’select table_name,num_rows,last_analyzed from user_tables where table_name=’TEST’;2) 引入了一个CURSOR_SHARING参数，该参数的主要目的就是为了解决在编程过程中已大量使用的硬编码SQL问题。上面的代码实际上使用了硬编码SQL，使我们不能使用共享SQL的功能，结果是数据库效率不高。但是从上面的2个语句来看，产生的硬编码SQL只是列值不同，其它部分都是相同的，如果仅仅因为列值不同而导致这2个语句不能共享是很可惜的，为了解决这个问题，引入了CURSOR_SHARING参数，使这类问题也可以使用共享SQL，从而使这样的开发也可以利用共享SQL功能。听起来不错，ORACLE真为用户着想，使用户在不改变代码的情况下还可以利用共享SQL的功能。真的如此吗？天上不会无缘无故的掉一个馅饼的，ORACLE对该参数的使用做了说明，建议在经过实际测试后再改该参数的值(缺省情况下，该参数的值为EXACT，语句完全一致才使用共享SQL)。因为有可能该变该值后，你的硬编码SQL是可以使用共享SQL了，但数据库的性能反而会下降。 我在实际应用中已经遇到这种情况。所以建议编写需要稳定运行程序的开发人员最好还是一开始就使用绑定变量的SQL。3)常见的几种索引使用场景 1) 全表扫描（Full Table Scans, FTS） 为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件。Oracle顺序地读取分配给表的每个数据块，直到读到表的最高水线处(high water mark, HWM，标识表的最后一个数据块)。一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模式下，每个数据块只被读一次。由于HWM标识最后一块被读入的数据，而delete操作不影响HWM值，所以一个表的所有数据被delete后，其全表扫描的时间不会有改善，一般我们需要使用truncate命令来使HWM值归为0。幸运的是oracle 10G后，可以人工收缩HWM的值。 由FTS模式读入的数据被放到高速缓存的Least Recently Used (LRU)列表的尾部，这样可以使其快速交换出内存，从而不使内存重要的数据被交换出内存。 使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% -- 10%，或你想使用并行查询功能时。 使用全表扫描的例子： ~~~~~~~~~~~~~~~~~~~~~~~~ SQL> explain plan for select * from dual; Query Plan ----------------------------------------- SELECT STATEMENT [CHOOSE] Cost= TABLE ACCESS FULL DUAL2) 通过ROWID的表存取（Table Access by ROWID或rowid lookup） 行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。 为了通过ROWID存取表，Oracle 首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。 这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。使用ROWID存取的方法：SQL> explain plan for select * from dept where rowid = 'AAAAyGAADAAAAATAAF';Query Plan------------------------------------SELECT STATEMENT [CHOOSE] Cost=1TABLE ACCESS BY ROWID DEPT [ANALYZED]3）索引扫描（Index Scan或index lookup） 我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。 在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% -- 10%，使用索引扫描会效率下降很多。如下列所示：SQL> explain plan for select empno, ename from emp where empno=10;Query Plan------------------------------------SELECT STATEMENT [CHOOSE] Cost=1TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 注意TABLE ACCESS BY ROWID EMP部分，这表明这不是通过FTS存取路径访问数据，而是通过rowid lookup存取路径访问数据的。在此例中，所需要的rowid是由于在索引查找empno列的值得到的，这种方式是INDEX UNIQUE SCAN查找，后面给予介绍，EMP_I1为使用的进行索引查找的索引名字。 但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的，因为这只会在索引中读取。所以上面我在介绍基于规则的优化器时，使用了select count(id) from SWD_BILLDETAIL where cn <'6'，而没有使用select count(cn) from SWD_BILLDETAIL where cn <'6'。因为在实际情况中，只查询被索引列的值的情况极为少，所以，如果我在查询中使用count(cn)，则不具有代表性。SQL> explain plan for select empno from emp where empno=10; -- 只查询empno列值Query Plan------------------------------------SELECT STATEMENT [CHOOSE] Cost=1 INDEX UNIQUE SCAN EMP_I1 进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序SQL> explain plan for select empno, ename from empwhere empno > 7876 order by empno;Query Plan--------------------------------------------------------------------------------SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。根据索引的类型与where限制条件的不同，有4种类型的索引扫描： 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan) (1) 索引唯一扫描(index unique scan) 通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束（它保证了语句只存取单行）的话，Oracle经常实现唯一性扫描。 使用唯一性约束的例子： SQL> explain plan for select empno,ename from emp where empno=10; Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 (2) 索引范围扫描(index range scan) 使用一个索引存取多行数据，同上面一样，如果索引是组合索引，如(1)所示，而且select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句返回多行数据，虽然该语句还是使用该组合索引进行查询，可此时的存取方法称为索引范围扫描。在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between) 使用索引范围扫描的例子：SQL> explain plan for select empno,ename from empwhere empno > 7876 order by empno;Query Plan--------------------------------------------------------------------------------SELECT STATEMENT [CHOOSE] Cost=1TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。 使用index rang scan的3种情况： (a) 在唯一索引列上使用了range操作符(> < <> >= <= between) (b) 在组合索引上，只使用部分列进行查询，导致查询出多行 (c) 对非唯一索引列上进行的任何查询。 (3) 索引全扫描(index full scan) 与全表扫描对应，也有相应的全索引扫描。在某些情况下，可能进行全索引扫描而不是范围扫描，需要注意的是全索引扫描只在CBO模式下才有效。CBO根据统计数值得知进行全索引扫描比进行全表扫描更有效时，才进行全索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。全索引扫描的例子：An Index full scan will not perform single block i/o's and so it may prove to be inefficient. e.g.Index BE_IX is a concatenated index on big_emp (empno, ename)SQL> explain plan for select empno, ename from big_emp order by empno,ename;Query Plan--------------------------------------------------------------------------------SELECT STATEMENT [CHOOSE] Cost=26 INDEX FULL SCAN BE_IX [ANALYZED] (4) 索引快速扫描(index fast full scan) 扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。索引快速扫描的例子：BE_IX索引是一个多列索引：big_emp (empno,ename)SQL> explain plan for select empno,ename from big_emp;Query Plan------------------------------------------SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED]只选择多列索引的第2列：SQL> explain plan for select ename from big_emp;Query Plan------------------------------------------SELECT STATEMENT [CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED]如果用ordered 提示(此时肯定用CBO)，则以from 子句中按从左到右的顺序选择驱动表”这句话是正确的。实际上在CBO中，如果有统计数据(即对表与索引进行了分析)，则优化器会自动根据cost值决定采用哪种连接类型，并选择合适的驱动表，这与where子句中各个限制条件的位置没有任何关系。如果我们要改变优化器选择的连接类型或驱动表，则就需要使用hints了，具体hints的用法在后面会给予介绍。3) 避免子查询，子查询的消耗会非常大。4) 规避FTS，查看表的索引情况。select index_name,column_name from dba_ind_columns where table_name like 'XSTFXPS%' .create the index as the request . 为了使用索引，我们必须对原来的日期字段的条件进行一些调整。因为有个trunc()函数的存在，语句将不会使用到索引。我们只要明白trunc(ywrq00)=trunc(sysdate)事实上等同于ywrq00大于trunc(sysdate)，小于trunc(sysdate+1)减去一秒，我们就有了比较好的办法来处理 一：使用where少使用having;二：查两张以上表时，把记录少的放在右边；三：减少对表的访问次数；四：有where子查询时，子查询放在最前；五：select语句中尽量避免使用*（执行时会把*依次转换为列名）；六：尽量多的使用commit；七：Decode可以避免重复扫描相同的记录或重复连接相同的表；八：通过内部函数也可提高sql效率；九：连接多个表时，使用别名并把别名前缀于每个字段上；十：用exists代替in十一：not exists代替 not in（not in 字句将执行一个内部的排序和合并，任何情况下，not in是最低效的，子查询中全表扫描了。为了避免使用not in，可以改写成outer joins或not exists）;十二：表连接比exists更高效；十三：用exists替换distinct 例： 低： 高： select distinct dept_no, dept_name select dept_no, dept_name from dept d, emp e from dept d where d.dept_no = e.dept_no; where exists (select 1 from emp e where e.dept_no = d.dept_no);十四：使用TKPROF工具来查询sql性能状态；十五：用索引提高效率（代价是：索引需要空间，而且定期重构索引很有必要：ALTER INDEX REBUILD 20 AND EMP_CAT = 'A'; （在两个非唯一性索引前提下）此时范围索引不被使用，通过EMP_CAT索引查询出记录再与DEPT_NO条件进行比较 注意：唯一性所以做范围比较时，等级要比非唯一性索引的等式比较低；二十：强制索引失效： 如果两个或两个以上索引具有相同的等级，可以强制命令oracle优化器使用其中的一个。 那何时使用此种策略呢？如果一个索引已接近于唯一，而另一索引有很多重复的值，排序与合并反而会成为负担，此时可以屏蔽后者使其索引失效。 （失效方式：对索引列加入计算'+0'或'||""'a.使用临时表重写复杂的子查询 Oracle 使用全局临时表以及WITH操作符去解决那些复杂的SQL子查询。尤其是那些where子句中的子查询，SELECT 字句标量子查询， FROM 子句的内联视图。使用临时表实现SQL tuning(以及使用WITH的物化视图)能够使得性能得以惊人的提升。 b.使用MINUS 代替EXIST子查询 使用MINUS操作代替NOT IN 或NOT EXISTS将产生更高效的执行计划(译者按：此需要测试)。 c.使用SQL分析函数 Oracle 分析函数能够一次提取数据来做多维聚合运算(象ROLLUP,CUBE)以提高性能。 d.重写NOT EXISTS和查询作为外部连接NOT EXISTS 子查询 在一些案例中的NOT 查询(where 中一个列被定义为NULL值)，能够将其改写这个非相关子查询到IS NULL 的外部链接。如下例： select book_key from book where book_key NOT IN (select book_key from sales); 下面我们在where子句中使用了外部连接来替代原来的not exits，得到一个更高效的执行计划。 select b.book_key from book b, sales s where b.book_key = s.book_key(+) and s.book_key IS NULL; e.索引NULL值列 如果你的SQL语句频繁使用到NULL值，应当考虑基于NULL值创建索引。为使该查询最优化，可以创建一个使用基于NULL值索引函数。 (译者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);) f.避免基于索引的运算 不要基于索引列做任何运算，除非你创建了一个相应的索引函数。或者重设设计列以使得where子句列上的谓词不需要转换。 -->下面都是低效的SQL写法 where salary*5 > :myvalue where substr(ssn,7,4) = "1234" where to_char(mydate,mon) = "january" g.避免使用NOT IN 和HAVING 在合适的时候使用not exists子查询更高效。 h.避免使用LIKE谓词 在合适地时候，如果能够使用 = 运算应尽可能避免LIKE操作。 i.避免数据类型转换 如果一个where 子句列是数字型，则不要使用引号。而对一个字符索引列，总是使用引号。下面是数据类型混用的情形。 where cust_nbr = "123" where substr(ssn,7,4) = 1234 j.使用decode与case 使用decode 与case 函数能够最小化查询表的次数。 k.不要害怕全表扫描 并不是所有的OLTP系统在使用索引时是最优化的。如果你的查询返回了表中的绝大部分数据，则全表扫描性能优于索引扫描。这取决于 一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查询，以及表块和索引块在buffer cache中的数量。 l.使用别名 在参照列的地方总是使用表别名。1. ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此 FROM 子句中写在最后的表(基础表 driving table)将被最先处理。在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。 例如： 表 TAB1 16，384 条记录 表 TAB2 1 条记录 选择 TAB2作为基础表 (最好的方法) select count(*) from tab1，tab2 执行时间 0.96秒 选择 TAB2作为基础表 (不佳的方法) select count(*) from tab2，tab1 执行时间 26.09秒 2. ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理，表之间的连接必须写在其他 WHERE 条件之前， 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。 例如： (低效，执行时间 156.3秒) SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); (高效,执行时间 10.6秒) SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER'; 3. SELECT子句中避免使用'*' 当你想在 SELECT子句中列出所有的 COLUMN时，使用动态 SQL列引用 ‘*' 是一个方便的方法。不幸的是，这是一个非常低效的方法。 实际上，ORACLE 在解析的过程中，会将‘*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。 4. 使用DECODE 函数来减少处理时间, 使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。 5. 删除重复记录 最高效的删除重复记录方法 ( 因为使用了 ROWID) DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO); 6. 计算记录条数 和一般的观点相反，count(*) 比 count(1)稍快，当然如果可以通过索引检索，对索 引列的计数仍旧是最快的。 例如 COUNT(EMPNO) 7. 用WHERE子句替换HAVING子句 避免使用 HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。 如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销。 例如： 低效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' 高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != ‘SYDNEY' AND REGION != ‘PERTH' GROUP BY REGION HAVING 中的条件一般用于对一些集合函数的比较，如 COUNT() 等等。除此而外，一般的条件应该写在 WHERE 子句中。 8. 减少对表的查询 在含有子查询的 SQL语句中，要特别注意减少对表的查询。 例如： 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 9. 使用表的别名ALIAS 当在 SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个 Column上。这 样一来，就可以减少解析的时间并减少那些由 Column歧义引起的语法错误。 (Column歧义指的是由于 SQL中不同的表具有相同的 Column名，当 SQL语句中出现这个 Column时，SQL解析器无法判断这个 Column的归属) 10. 用EXISTS替代IN 在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这 种情况下， 使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率。 低效： SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB') 高效： SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB') 11. 用NOT EXISTS替代 NOT IN 在子查询中， NOT IN子句将执行一个内部的排序和合并。 无论在哪种情况下， NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用 NOT IN ，我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS. 例如： SELECT … FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A'); 为了提高效率。改写为： (方法一： 高效) SELECT …. FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A' (方法二： 最高效) SELECT …. FROM EMP E WHERE NOT EXISTS (SELECT ‘X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A'); 12. 用表连接替换EXISTS 通常来说 ，采用表连接的方式比 EXISTS 更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A'); (更高效) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A' ; 13. 用EXISTS替换DISTINCT 当提交一个包含一对多表信息(比如部门表和雇员表)的查询时，避免在 SELECT 子句 中使用 DISTINCT. 一般可以考虑用 EXIST 替换 例如: 低效： SELECT DISTINCT DEPT_NO,DEPT_N FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效： SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); 14. 避免在索引列上使用计算 WHERE 子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。 举例： 低效： SELECT … FROM DEPT WHERE SAL * 12 > 25000; 高效： SELECT … FROM DEPT WHERE SAL > 25000/12; ：这是一个非常实用的规则，请务必牢记15. 避免在索引列上使用NOT 通常，我们要避免在索引列上使用 NOT，NOT 会产生在和在索引列上使用函数相同 的影响。当ORACLE“遇到”NOT，他就会停止使用索引转而执行全表扫描。 举例： 低效： (这里，不使用索引) SELECT … FROM DEPT WHERE DEPT_CODE NOT = 0; 高效： (这里，使用了索引) SELECT … FROM DEPT WHERE DEPT_CODE > 0; 16. 用>=替代> 如果 DEPTNO 上有一个索引， 高效： SELECT * FROM EMP WHERE DEPTNO >=4 低效： SELECT * FROM EMP WHERE DEPTNO >3 两者的区别在于， 前者 DBMS将直接跳到第一个 DEPT 等于 4的记录而后者将首先 定位到 DEPTNO=3的记录并且向前扫描到第一个 DEPT 大于 3的记录。 17. 用UNION替换OR (适用于索引列) 通常情况下， 用 UNION替换 WHERE 子句中的 OR将会起到较好的效果。 对索引列使用 OR将造成全表扫描。注意， 以上规则只针对多个索引列有效。 如果有 column没有被索引， 查询效率可能会因为你没有选择 OR而降低。 在下面的例子中， LOC_ID 和 REGION上都建有索引。 高效： SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效： SELECT LOC_ID ， LOC_DESC ， REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你坚持要用 OR， 那就需要返回记录最少的索引列写在最前面。 18. 用IN来替换OR 下面的查询可以被更有效率的语句替换： 低效： SELECT… FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效： SELECT… FROM LOCATION WHERE LOC_IN IN (10，20，30); ：这是一条简单易记的规则，但是实际的执行效果还须检验，在 ORACLE8i 下，两者 的执行路径似乎是相同的。 19. 避免在索引列上使用IS NULL和IS NOT NULL 避免在索引中使用任何可以为空的列，ORACLE 将无法使用该索引。对于单列索引， 如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。 如果至少有一个列不为空，则记录存在于索引中。 举例： 如果唯一性索引建立在表的 A列和 B 列上， 并且表中存在一条记录的 A， B 值为(123，null) ， ORACLE 将不接受下一条具有相同 A，B 值(123，null)的记录(插入)。 然而如果所有的索引列都为空， ORACLE 将认为整个键值为空而空不等于空。 因此你可以插入 1000条具有相同键值的记录，当然它们都是空! 因为空值不存在于索引列中，所以 WHERE 子句中对索引列进行空值比较将使ORACLE 停用该索引。 举例： 低效：(索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效：(索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0; 20. 用UNION ALL替换 UNION( 如果有可能的话) 当 SQL语句需要 UNION两个查询结果集合时，这两个结果集合会以 UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。 如果用 UNION ALL替代 UNION， 这样排序就不是必要了。 效率就会因此得到提高。 举例： 低效： SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ‘31-DEC-95' UNION SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ‘31-DEC-95' 高效： SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ‘31-DEC-95' UNION ALL SELECT ACCT_NUM， BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ‘31-DEC-95' ：需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。 因此各位还是 要从业务需求分析使用 UNION ALL 的可行性。UNION 将对结果集合排序，这个操作会使用到 SORT_AREA_SIZE这块内存。 对于这块内存的优化也是相当重要的。 下面的 SQL可以用来查询排序的消耗量 Select substr（name，1，25） "Sort Area Name"， substr（value，1，15） "Value" from v$sysstat where name like 'sort%' 21. 优化GROUP BY 提高 GROUP BY 语句的效率， 可以通过将不需要的记录在 GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。 低效： SELECT JOB ， AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' 高效： SELECT JOB ， AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'GROUP by JOB;Checking HWMFor non-partition table，for partition/subpartition table , write it yourselfcol table format a40select a.owner || '.' || a.table_name "TABLE",a.num_rows,a.avg_row_len,b.inserts,b.deletes,a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0) total_rows,round(a.avg_row_len*(a.num_rows+nvl(b.inserts,0)-nvl(b.deletes,0))/1024/1024,4)"Calculate_Sizle(Mb)",c.bytes/1024/1024 "Segment_Size(Mb)"from dba_tables a left join all_tab_modifications bon a.owner=b.table_owner and a.table_name=b.table_name inner joindba_segments c on a.owner=c.owner and a.table_name=c.segment_name where a.table_name=upper('&table_name') and a.owner=upper('&owner'); Then compare Caculate Size(Mb) and Segment_Size(Mb) and deletes, insertsIf Segment_size is much bigger than caculate size ,deletes much bigger than insertsThen alter table move(do not use shrink , move is much faster), then rebuild indexes, and gather stats.Checking statsexec dbms_stats.flush_database_monitoring_info; ---flush stats from memory into disc select owner || '.' || table_name name , object_type,stale_stats,last_analyzed from dba_tab_statistics where owner=upper('&owner') and table_name=upper('&table_name');Then check stale_stats, if value is Yes, then gather statsGather stats:None partition tableBEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'DEPT',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all columns size repeat',degree => DBMS_STATS.AUTO_DEGREE,cascade=>TRUE);END;/For small tables , I always set estimate_percent=50/100 , for big table, I always set 20/30Partition table BEGINDBMS_STATS.GATHER_TABLE_STATS(ownname => 'ROBINSON',tabname => 'P_TEST',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'for all columns size repeat',degree => DBMS_STATS.AUTO_DEGREE,granularity => 'ALL',cascade=>TRUE);END;/Check column Cardinality and selectivity and histogramNon-partition tablecol name format a35col column_name format a25 select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity, num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name and a.owner=upper('&owner') and a.table_name=upper('&table_name') and a.column_name=upper('&column_name'); Partition tablecol name format a35 col column_name format a25 select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity, num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name and a.owner=upper('&owner') and a.table_name=upper('&table_name') and a.column_name=upper('&column_name');Subpartition tablecol name format a35 col partition_name format a20 col column_name format a20 select a.owner ||'.'|| a.table_name name,a.partition_name,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,num_nulls,density ,a.histogram,a.num_buckets from dba_part_col_statistics a, dba_tab_partitions b where a.owner=b.table_owner and a.table_name=b.table_name and a.partition_name=b.partition_name and a.owner=upper('&owner') and a.table_name=upper('&table_name') and a.column_name=upper('&column_name'); Check index on table, index typeChecking Index on which columncol index_owner format a20 col index_name format a30 col column_name format a30 select index_owner,index_name,column_name,column_position from dba_ind_columns where table_owner=upper('&table_owner') and table_name=upper('&table_name'); Check Index type On the tableselect a.owner,a.index_name,a.index_type,a.partitioned,a.status,b.status p_status,b.composite from dba_indexes a left join dba_ind_partitions b on a.owner=b.index_owner and a.index_name=b.index_name where a.owner='&owner' and a.table_name='&table_name'; Check index selectivity ,clustering factorChecking Index on which columncol owner format a15 col index_name format a20 col index_type format a15 col partition format a15col subpartition_name format a20 select a.owner,a.index_name,a.index_type,decode(partitioned,'YES',b.partition_name,'NO') partition,b.subpartition_name subpartition_name,b.num_rows,b.distinct_keys,b.num_rows/b.distinct_keys avg_row_per_key,b.distinct_keys/b.num_rows SELECTIVITY,b.clustering_factor,b.last_analyzed,b.stale_stats from dba_indexes a,dba_ind_statistics b where a.owner=b.owner and a.index_name=b.index_name and a.owner=upper('&owner') and a.index_name=upper('&index_name'); Watching parallel executionCheck Server CPU usage, I/O usageWhen to use parallel execution?If there is no way to avoid full table scan(select,update,delete) on a big table, then use parallel execution.How to set parallel execution? set degree on the table : alter table table_name parallel 8;Using parallel hint. : select /*+ parallel(a,8)*/ ………How to set parallel degree ?SQL> show parameter cpu NAME TYPE VALUE------------------------------------ --------------------------------- --------cpu_count integer 8parallel_threads_per_cpu integer 2If there is no sort operation, then you can set parallel degree to cpu_count*parallel_threads_per_cpu-1If need sort, then parallel degree=cpu_count*parallel_threads_per_cpu/2-1How to disable parallel query/dml/ddl?Alter session disable parallel query/dml/ddl;Check if index need rebuildanalyze index schema.index_name validate structure;select height, lf_rows,del_lf_rows from index_stats;If height>=4 or del_lf_rows/lf_rows>0.2 , then rebuild index.I always rebuild index when del_lf_rows/lf_rows>0.1 Watching Wait event, ASH• v$session_wait, -- not recommend• v$session -- recommend• v$active_session_history --strongly recommendUse below script to monitor wait eventselect SAMPLE_TIME,SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# from v$active_session_history ash, v$event_name enm where ash.event#=enm.event# and SESSION_ID=&sid;Note: we must join v$event_name view, or sometimes event will be null为什么要用小表/结果集较小的表作为驱动表？因为如果表/结果很大，那么返回的数据就多了，这样循环次数就会增加，会导致多次的index unique/range scan,那么效率就下去了，这个时候CBO 会考虑交换驱动表或者干脆选择哈希连接。 为什么内部表的连接列上面要有索引？因为如果内部表很大，如果连接列上面没有索引，那么会导致Oracle 对内部表的全表扫描，而且是多次(根据外部表返回记录数)，这样效率极其低下，所以如果内部表的连接列上面没有索引，CBO 会选择哈希连接。如何识别低效的 Nested loops？可以通过比较E-Rows 和A-Rows，如果两者相差太大，说明执行计划出问题了，另外你也可以查看COST,如果NESTED LOOPS 这个操作COST操作很高(占用很大部分COST)，说明执行计划也出问题了。这些通常是由于统计信息没有收集/及时更新导致的。 如何识别低效HASH JOIN？可以看HASH JOIN 那一步的COST，如果COST 的值比2 个HASH JOIN 的子操作的值加起来要高很多，那么说明HASH JOIN 不是很优的，同时你也可以看HASH JOIN 是否用了temp tablespace,如果用了表示HASH AREA不够大。另外就是查看HASH JOIN 的执行计划的时候，你要看ACCESS，这个信息表示Oracle 选用的哪些where 列作为HASH KEY。 select sid,sql_id,operation_type operation,work_area_size/1024 work_area_size_mb,expected_size/1024 e_size_mb, actual_mem_used/1024 a_size_mb,max_mem_used/1024 max_size_mb,tempseg_size/1024/1024 temp_size_mb, decode(number_passes,0,'memory',1,'1-pass','m','m-pass') pass,tablespace from v$sql_workarea_active where sid=&sid; OPTIMIZER_INDEX_CACHING 这个初始化参数代表一个百分比,取值范围在0到99之间. 缺省值是0，代表当CBO使用索引访问数据时，在内存中发现数据的比率是0%，这意味着通过索引访问数据将需要产生物理读取，代价昂贵。如果使用缺省设置，Oracle评估成本的时候，很多时候就会错误的选择全表扫描。 OPTIMIZER_INDEX_COST_ADJ 这个初始化参数代表一个百分比,取值范围在1到10000之间. 该参数表示索引扫描和全表扫描成本的表较。缺省值100表示索引扫描成本等于全表扫描。 这些参数对于CBO的执行具有重大影响，其缺省值对于数据库来说通常需要调整。 一般来说对于OPTIMIZER_INDEX_CACHING可以设置为90左右 对于大多数OLTP系统，OPTIMIZER_INDEX_COST_ADJ可以设置在10到50之间。对于数据仓库和DSS系统，可能不能简单的把OPTIMIZER_INDEX_COST_ADJ设置为50，通常我们需要反复调整取得一个合理值.