SQL优化基础

一、oracle优化器简介
1、优化器的优化方式
  A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。

  B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。
 
 我们要明了,不一定走索引就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(full table scan)是最好的。

2、优化器的优化模式(Optermizer Mode)

优化模式包括Rule,Choose,First rows,All rows这四种方式,也就是我们以上所提及的。如下我解释一下：

Rule:不用多说,即走基于规则的方式。

Choolse:这是我们应观注的。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式，所以在吉林市医保项目中，当执行analyze table hygeia.mt_biz_fin delete statistics 后，性能得到提高。oracle10g的优化器，默认为CBO,OPTIMIZER_MODE默认值为ALL_ROWS。

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS

3、如何设定选用哪种优化模式

a、Instance级别

我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、 OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。

B、Sessions级别

通过SQL> ALTER SESSION SET OPTIMIZER_MODE=;来设定。

SQL> alter session set optimizer_mode=choose;

Session altered.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
optimizer_mode                       string      CHOOSE

C、语句级别

这些需要用到Hint,比如:
SQL> SELECT /*+ RULE */ a.userid,
2 b.name,
3 b.depart_name
4 FROM tf_f_yhda a,
5 tf_f_depart b
6 WHERE a.userid=b.userid;

 二、获取执行计划
1、dbms_xplan.display_cursor获得执行计划
SQL> explain plan for select AKC190 from kc21 where akb020 ='1103';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
---------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |   271 |  5149 |     2   (0)|
|   1 |  INDEX FAST FULL SCAN| PK_KC21 |   271 |  5149 |     2   (0)|
---------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version

11 rows selected.

2、也可以在sqlplus下使用set autotrace on/traceonly等语句，除了或者执行计划以外，还可以查看到执行统计信息，同时也可以在trace文件中查找相关的执行计划（结合使用tkprof，后续详述）。
SQL>SET AUTOTRACE ON；
*autotrace功能只能在SQL*PLUS里使用

    其他一些使用方法：
2.1、在SQLPLUS中得到语句总的执行时间
SQL> set timing on;

2.2、只显示执行计划--（会同时执行语句得到结果）
SQL>set autotrace on explain

    14 rows selected.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP' 
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       2598  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed
通过以上分析,可以得出实际的执行步骤是:
1.       TABLE ACCESS (FULL) OF 'EMP' 
2.       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3.       TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4.       NESTED LOOPS (JOINING 1 AND 3)

3、第三方查看，当然除了以上两种方法，还可以通过第三方工具查看，如TOAD、PL\SQL等

三、如何读懂执行计划

   针对执行计划，只要遵循一条原则就OK了，最里最上或者（最右最上），即先看最靠右的计划，同样位置的先看上面的，基本上就这样。

四、读懂统计信息
   
    db block gets      从buffer cache中读取的block的数量

consistent gets    从buffer cache中读取的undo数据的block的数量

physical reads      从磁盘读取的block的数量

redo size          天生的redo的巨细

sorts (memory)      在内存推行 的排序量

sorts (disk)        在磁盘上推行 的排序量

· Recursive Calls. Number of recursive calls generated at both the user and system level.
Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call.
In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls.

· DB Block Gets. Number of times a CURRENT block was requested.

Current mode blocks are retrieved as they exist right now, not in a consistent read fashion.
Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time.
During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.
(DB Block Gets:请求的数据块在buffer能满足的个数)

· Consistent Gets. Number of times a consistent read was requested for a block.
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block.
This is the mode you read blocks in with a SELECT, for example.
Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.
(Consistent Gets:数据请求总数在回滚段Buffer中)

· Physical Reads. Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache. (Physical Reads:实例启动后，从磁盘读到Buffer Cache数据块数量)

· Sorts (disk). Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13726712/viewspace-684234/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13726712/viewspace-684234/