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/，如需轉載，請註明出處，否則將追究法律責任。