根據工作總結的18條Oracle性能調優經驗

1.      查看具體語句的執行計劃及消耗的內存/CPU量

當前兩張表中各有數據1508875條

原始語句為：

SELECT/*+rule*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ, ZP.ZP

FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHEREJB.RYID = ZP.RYID

AND ( JB.RYID>= 1 AND JB.RYID < 10001)

采用以下方式可以獲得該語句的執行計劃：

執行計劃

----------------------------------------------------------

Plan hashvalue: 2567408823

 

--------------------------------------------

| Id  | Operation           | Name         |

--------------------------------------------

|   0 | SELECT STATEMENT    |              |

|   1 | MERGE JOIN         |              |

|   2 |  SORT JOIN         |              |

|   3 |   TABLE ACCESS FULL| HZCZRK_ZPXXB |

|*  4 |  SORT JOIN         |              |

|*  5 |   TABLE ACCESS FULL| HZCZRK_JBXXB |

--------------------------------------------

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   4 -access("JB"."RYID"="ZP"."RYID")

       filter("JB"."RYID"="ZP"."RYID")

   5 -filter("JB"."RYID"<10001 AND"JB"."RYID">=1)

 

Note

-----

   - rule based optimizer used (consider usingcbo)

 

 

統計信息

----------------------------------------------------------

          2 recursive calls

          1 db block gets

        544 consistent gets

          0 physical reads

        176 redo size

      11999 bytes sent via SQL*Net to client

        811 bytes received via SQL*Net from client

          7 SQL*Net roundtrips to/from client

          2 sorts (memory)

          0 sorts (disk)

         79  rows processed

如果不設置總行數，則執行計劃是：

執行計劃

----------------------------------------------------------

Plan hashvalue: 2567408823

 

--------------------------------------------

| Id  | Operation           | Name         |

--------------------------------------------

|   0 | SELECT STATEMENT    |              |

|   1 | MERGE JOIN         |              |

|   2 |  SORT JOIN         |              |

|   3 |   TABLE ACCESS FULL| HZCZRK_ZPXXB |

|*  4 |  SORT JOIN         |              |

|   5 |   TABLE ACCESS FULL| HZCZRK_JBXXB |

--------------------------------------------

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   4 - access("JB"."RYID"="ZP"."RYID")

      filter("JB"."RYID"="ZP"."RYID")

 

Note

-----

   - rule based optimizer used (consider usingcbo)

 

 

統計信息

----------------------------------------------------------

          2 recursive calls

          1 db block gets

       1103 consistent gets

          0 physical reads

        176 redo size

      23915 bytes sent via SQL*Net to client

        866 bytes received via SQL*Net from client

         12 SQL*Net roundtrips to/from client

          2 sorts (memory)

          0  sorts(disk)

        164  rows processed

這里我們主要需要關注兩個點：consistent gets代表內存消耗，physical reads代表磁盤I/O消耗，單位都是數據塊(DB_BLOCK_SIZE)。

從上面的執行計劃可以看出表之間的連接關系基本上就是采用排序合并連接技術，所以下面對連接技術做出一些說明

適合于大批量數據處理的連接技術只有兩種：

1．    排序合并連接(Sort/Merge)技術

兩個表先按連接字段進行排序，再將兩個表的排序結果進行順序匹配，將合并結果返回給客戶。

2．    哈希連接(HASH)技術

A hashjoin is executed as follows:

Bothtables are split into as many partitions as required, using a full table scan.

For eachpartition pair, a hash table is built in memory on the smallest partition.

The otherpartition is used to probe the hash table.

兩種技術都適合于大表與大表的查詢，而且通常情況下，HASH優于Merge，更優于嵌套循環(Nested_Loop)連接技術，尤其是當HASH與Oracle并行處理技術相結合的情況下，將極大地提高系統的整體吞吐量。

 

2.      對兩張表做一個分析

由于從Oracle10G開始已經不采用基于規則優先的算法，而是交由RBO來進行，所以需要在優化前對表進行分析

execute dbms_stats.gather_table_stats(ownname =>'SYSTEM',tabname => 'HZCZRK_JBXXB' ,estimate_percent => null ,method_opt=> 'for all indexed columns' ,cascade => true);

分析結果可以在這里看：

selecta.owner,a.segment_name,a.segment_type,a.tablespace_name,round(a.bytes/1024/1024/1024,2)tablesize,b.last_analyzed

from dba_segments a,dba_tables b

wherea.segment_name=b.table_name；

看起來表一切正常。

 

3.      看V$Session_longops，超過6秒的SQL

select * from V$Session_longopsorder BY last_update_time DESC；

只要是涉及兩張表的全表查詢都會超過6秒。

 

4.      去掉Hint基于規則優先/*rule/看幾次語句執行后的執行計劃及消耗的內存/CPU量

SELECT JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ, ZP.ZP

FROM HZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHERE JB.RYID = ZP.RYID;

執行計劃是：

執行計劃

----------------------------------------------------------

Plan hashvalue: 993606438

 

--------------------------------------------------------------------------------

 

---

 

| Id  | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time

  |

 

--------------------------------------------------------------------------------

 

---

 

|   0 | SELECT STATEMENT   |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|*  1 | HASH JOIN         |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|   2 |  TABLE ACCESS FULL| HZCZRK_JBXXB |    1 |    41 |     2  (0)| 00:00:0

 

1 |

 

|   3 |  TABLE ACCESS FULL| HZCZRK_ZPXXB | 61692 |   481K| 48725  (1)| 00:09:4

 

5 |

 

--------------------------------------------------------------------------------

 

---

 

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   1 -access("JB"."RYID"="ZP"."RYID")

 

 

統計信息

----------------------------------------------------------

          8 recursive calls

          0 db block gets

          5 consistent gets

          0 physical reads

          0 redo size

        830 bytes sent via SQL*Net to client

        756 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          0  rows processed

連接關系由Merge join和Sort join轉變為Hash Join。

 

5.      嘗試在RYID、身份證號碼、姓名這些字段建復合索引，然后看執行計劃及消耗的內存/CPU量

CREATE INDEX inx_ryid_HZCZRK_JBXXB onHZCZRK_JBXXB(RYID,XM,CSRQ);

CREATE INDEX inx_ryid_HZCZRK_ZPXXB onHZCZRK_ZPXXB(RYID,ZPID);

之后執行計劃為：

執行計劃

----------------------------------------------------------

Plan hashvalue: 993606438

 

--------------------------------------------------------------------------------

 

---

 

| Id  | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time

  |

 

--------------------------------------------------------------------------------

 

---

 

|   0 | SELECT STATEMENT   |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|*  1 | HASH JOIN         |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|   2 |  TABLE ACCESS FULL| HZCZRK_JBXXB |    1 |    41 |     2  (0)| 00:00:0

 

1 |

 

|   3 |  TABLE ACCESS FULL| HZCZRK_ZPXXB | 61692 |   481K| 48725  (1)| 00:09:4

 

5 |

 

--------------------------------------------------------------------------------

 

---

 

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   1 -access("JB"."RYID"="ZP"."RYID")

 

 

統計信息

----------------------------------------------------------

          0 recursive calls

          0 db block gets

          3 consistent gets

          0 physical reads

          0 redo size

        830 bytes sent via SQL*Net to client

        756 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

0        rows processed

    可以看到內存消耗量更加小了。

 

6.      嘗試復合的Global Partition索引，然后看執行計劃及消耗的內存/CPU量

以出生年月來進行范圍的劃分，在表HZCZRK_JBXXB上創建索引CSRQ_IX_RANGE

CREATE INDEXCSRQ_IX_RANGE ON HZCZRK_JBXXB(CSRQ)

GLOBALPARTITION BY RANGE(CSRQ)

(

PARTITIONP_19021912 VALUES LESS THAN (19130101),

PARTITIONP_19131923 VALUES LESS THAN (19240101),

PARTITIONP_19241934 VALUES LESS THAN (19350101),

PARTITIONP_19351945 VALUES LESS THAN (19460101),

PARTITIONP_19561966 VALUES LESS THAN (19670101),

PARTITIONP_19671977 VALUES LESS THAN (19780101),

PARTITIONP_19781988 VALUES LESS THAN (19890101),

PARTITIONP_19891999 VALUES LESS THAN (20000101),

PARTITIONP_GREATER_THAN_20120101 VALUES LESS THAN (MAXVALUE)

);

創建索引后采用MapReduce方式對出生日期按照年的方式導出數據，讀的速度加快很多。

 

7.      確保兩張表的所有字段，特別是索引字段為同一數據類型

這個經過檢查可以確認對應字段全部一致。

  

8.      嘗試晴空內存空間(Shared Pool)，然后看執行計劃及消耗的內存/CPU量

alter system flush shared_pool;

alter system flush buffer_cache;

 

9.      避免重復語句解析(Parse)

這里Java代碼中執行語句中RYID是采用參數傳入的方式，所以避免了重復語句解析的狀況出現。

  

10.  增加PGA參數看看效果

監控PGA的視圖：v$sql_workarea_active、v$sql_workarea、v$sesstat、v$process、v$sysstat、v$sql_workarea_histogram等。
使用下面的視圖查看Oracle建議的評估設置:
Select pga_target_for_estimate/1024/1024 ||'M' "Estimate PGA Target"
       ,estd_pga_cache_hit_percentage "CacheHit(%)"
       ,estd_extra_bytes_rw/1024/1024 ||'M'"Extra Read/Write"
       ,estd_overalloc_count "Over alloccount"
From v$pga_target_advice
選出的4個列中，Over alloc count指示Oracle SQL工作區內存分配的三種情況：optimal完全可以在內存中完成操作；onepass需要進行一次磁盤交換；multipass需要進行多次磁盤交換。第四列的值就是需要進行磁盤交換的數量。
PGA_AGGREGATE_TARGET的值最好選擇: Over alloc count為0、Cache Hit(%)盡可能高、Extra Read/Write盡可能低的Estimate PGA Target值。

l  PGA_AGGREGATE_TARGET參數用以在PGA自動管理模式下，設置所有用戶會話對PGA內存的使用限制，也就是說，PGA在自動管理模式下，能夠妥善地利用PGA的內存空間。而對于需要大量內存空間來進行運算的SQL語句來說，Oracle能夠優先分配足夠的空間，并盡量將其作業保持在Optimal Size類型的工作區域中以增加效率，PGA_AGGREGATE_TARGET的大小范圍是10 MB至4 GB。

聯機事務處理系統(On-LineTransaction Processing System，OLTP)公式為：PGA_ AGGREGATE_TARGET = (物理內存大小* 80%) * 20%。

 

而決策支持系統(DecisionSupport Systems，DSS)的公式為：PGA_AGGREGATE_ TARGET = (物理內存大小* 80%) * 50%。

ALTER SYSTEM SET pga_aggregate_target=1024M;

l  設置數據高速緩沖區大小

ALTER SYSTEM SET db_cache_size=1024;

l  設置共享池大小

ALTER SYSTEM SET shared_pool_size=1024;

l  設置Java池大小

ALTER SYSTEM SET java_pool_size=1024;

l  設置大型池大小

ALTER SYSTEM SET large_pool_size=1024;

l  設置SGA內存總和

      ALTER SYSTEM SET sga_target=1024;

 

11.  查看Redo_Log文件塊

重做日志文件塊過多也會造成系統性能下降，可以通過以下語句查找重做日志文件，

select * fromv$logfile order by GROUP#;

如果需要清除重做日志文件組，可以使用以下語句：

alter databaseclear logfile group 2;該語句會清除文件組2的日志文件內容

 

12.  分區表技術介紹

請根據項目的實際情況選擇采用哪種分區技術實踐

l  范圍(Range)分區，按一張表指定的一個字段值或多個字段值的范圍進行分區，一般適用于按時間周期進行數據的存儲，缺點是字段記錄值變動太多的話將導致記錄在分區之間移動太頻繁，影響性能。

l  哈希(Hash)分區，是指Oracle通過一個內部的Hash散列算法，以分區字段值為輸入，進行散列運算，返回一個分區值，最后自動將記錄插入到該分區。最大的特點是記錄被Oracle均勻分布到各分區。適合于靜態數據。

l  列表(list)分區，通過對分區字段的散列值進行分區，或者說以枚舉方式進行分區。該分區是不排序的，而且分區之間沒有關聯關系，另外，只支持單個字段。與范圍分區的差別在列表分區是按記錄的離散值進行分區的，適用場景和優缺點完全一致。

l  間隔(Interval)分區，通過該分區技術，用戶可指定時間間隔，例如假設按月進行分區，則Oracle在每個新月到來時，自動創建該月的分區，免去了DBA此方面的管理工作。

l  基于虛擬列(VirtualColumn-Based)的分區技術，支持在字段函數基礎上進行分區，例如to_char,substr這些函數。

l  引用(Reference)分區技術，支持通過外鍵直接管理子表的分區。

l  系統(system)分區，允許應用程序控制寫入記錄到哪個分區。

 

13.  對索引進行碎片分析，如果需要則整理

l  查表的索引及狀態

select *from USER_INDEXES where table_name = 'HZCZRK_JBXXB'

l  2. 開啟Oracle索引監控

ALTERINDEX SYS_C0010867 MONITORING USAGE

l  3. 執行sql語句

l  4. 關閉Oracle索引監控

ALTERINDEX SYS_C0010867 NOMONITORING USAGE

l  5. 查看索引是否有被使用

SELECT *FROM V$OBJECT_USAGE;

l  6. 索引碎片分析

ANALYZEINDEX SYS_C0010867 VALIDATE STRUCTURE ONLINE;

SELECTNAME,DEL_LF_ROWS_LEN,LF_ROWS_LEN,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 FROMINDEX_STATS;

如果碎片率超過20%，則需要整理

l  7. 碎片整理

ALTERINDEX SYS_C0010867 REBUILD;

l  8．碎片壓縮

ALTER INDEX SYS_C0010867 COALESCE;

 

14.  在線重新定義表技術

DBMS_REDEFINITION支持在幾乎不中斷業務的情況下，通過創建一個中間表，并通過內部機制，保證原表與中間表的數據同步，最后通過一個切換操作，完成表結構的在線重新定義，即非分區表向分區表的轉換，或者已分區表向另一種分區表的轉換等。

 

15.  嘗試并行執行語句/*+Parallel*/

l  SELECT /*+PARALLER()*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ,ZP.ZP

FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHEREJB.RYID = ZP.RYI;

Oracle在執行該語句時，將根據該表的并行度(DOP)定義或者根據語句的并行處理HINT自動從并行處理緩沖池中分配相應數量的并行過程進行并行操作，并行處理協調器(Parallel Execution Coordinator)自動將各并行子進程處理的結果合并返回給客戶，并釋放并行子進程返回到緩沖池中。

執行計劃如下：

----------------------------------------------------------

Plan hashvalue: 993606438

 

--------------------------------------------------------------------------------

 

---

 

| Id  | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time

  |

 

--------------------------------------------------------------------------------

 

---

 

|   0 | SELECT STATEMENT   |              | 4714 |   478K|  3050  (1)| 00:00:3

 

7 |

 

|*  1 | HASH JOIN         |              | 4714 |   478K|  3050  (1)| 00:00:3

 

7 |

 

|   2 |  TABLE ACCESS FULL| HZCZRK_JBXXB | 4713 |   405K|    30  (0)| 00:00:0

 

1 |

 

|   3 |  TABLE ACCESS FULL| HZCZRK_ZPXXB | 17999 |   281K| 3019   (1)| 00:00:3

 

7 |

 

--------------------------------------------------------------------------------

 

---

 

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   1 -access("JB"."RYID"="ZP"."RYID")

 

Note

-----

   - dynamic sampling used for this statement(level=2)

 

 

統計信息

----------------------------------------------------------

         58 recursive calls

          1 db block gets

      25467 consistent gets

          0 physical reads

        240 redo size

     557645 bytes sent via SQL*Net to client

       3627 bytes received via SQL*Net from client

        263 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

       3924  rows processed

l  在對分區表進行處理時，Oracle會自動為每個分區分配一個進程，從而達到并行處理的目的。不僅分區之間可以并行處理，而且Oracle會自動根據資源使用情況和數據分布情況，自動在分區內部進行并行處理，因此可進一步提高大批量數據處理的吞吐量。

        SELECT/*+PARALLER_INDEX(oi,ghoi_ix,8)*/ JB.RYID, JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ,ZP.ZP

FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHERE JB.RYID = ZP.RYID;

語句的執行計劃如下：

----------------------------------------------------------

Plan hashvalue: 993606438

 

--------------------------------------------------------------------------------

 

-----------

 

| Id  | Operation          | Name         | Rows | Bytes |TempSpc| Cost (%CPU)|

 

 Time    |

 

--------------------------------------------------------------------------------

 

-----------

 

|   0 | SELECT STATEMENT   |              |   458K|   45M|       | 38437   (1)|

 

 00:07:42 |

 

|*  1 | HASH JOIN         |              |   458K|   45M|    43M| 38437   (1)|

 

 00:07:42 |

 

|   2 |  TABLE ACCESS FULL| HZCZRK_JBXXB |  458K|    38M|       |  323   (5)|

 

 00:00:04 |

 

|   3 |  TABLE ACCESS FULL| HZCZRK_ZPXXB | 2439K|    37M|       | 32674  (1)|

 

 00:06:33 |

 

--------------------------------------------------------------------------------

 

-----------

 

 

PredicateInformation (identified by operation id):

---------------------------------------------------

 

   1 - access("JB"."RYID"="ZP"."RYID")

 

Note

-----

   - dynamic sampling used for this statement(level=2)

 

 

統計信息

----------------------------------------------------------

         52 recursive calls

          3 db block gets

     265779 consistent gets

     224230  physical reads

        528 redo size

    5738732 bytes sent via SQL*Net to client

      30236 bytes received via SQL*Net from client

       2682 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

      40200  rows processed

 

16.  表空間遷移技術(Transportable Tablespace)

Transportable Tablespace技術是所有Oracle ETL工具中速度最快的！

 

17.  嘗試物化表視圖技術與語句重寫技術

物化視圖是一個實體，保存了從視圖中產生的數據，尤其是匯總數據，需要消耗一定的硬盤資源。物化視圖將進行統計運算、多表連接和其他復雜計算的 SQL語句的結果，直接生成到定義的物化視圖中。通過物化視圖的各種數據刷新機制(COMPLETE、FAST、FORCE等)，以及手工或自動等方式，來保持基表與物化視圖數據的一致性。語句重寫技術能自動將原有SQL統計運算導向到相應的物化視圖，一方面大大提高了統計運算速度，另一方面又保證了對應用的透明性。在物化視圖上可以創建相應的索引。

以下語句建立物化視圖：

CREATEMATERIALIZED VIEW mview_ry_summary

TABLESPACEHZCZRK_DATA

PARALLEL(DEGREE1)

BUILD IMMEDIATE

REFRESH COMPLETEON DEMAND

ENABLE QUERYREWRITE

AS

SELECT JB.RYID,JB.GMSFHM,JB.XM, JB.XB, JB.CSRQ,"TO_LOB"(ZP.ZP)

FROMHZCZRK_JBXXB JB,HZCZRK_ZPXXB ZP

WHERE JB.RYID =ZP.RYID

  

18.  嘗試不合并視圖/*+no_merge*/，然后看執行計劃及消耗的內存/CPU量

如果在查詢中用到多個視圖，而組成這些視圖的SQL語句都是優化好了的，單獨訪問任何一個視圖，性能都沒有問題。如果此時不加NO_MERGE，則 ORACLE會自動將若干個視圖拆散，重新構造執行計劃。而事實證時，重新構造的執行計劃往往會比較糟糕，于是，這種情況下就可以利用 NO_MERGE（按字面理解就是不把若干個視圖的查詢條件進行合并），避免ORACLE將視圖的查詢拆散。

加入后執行計劃為：

----------------------------------------------------------

Plan hash value: 993606438

 

--------------------------------------------------------------------------------

 

---

 

| Id | Operation          | Name         | Rows | Bytes | Cost (%CPU)| Time

 |

 

--------------------------------------------------------------------------------

 

---

 

|  0 | SELECT STATEMENT   |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|* 1 |  HASH JOIN         |              |     1 |   49 | 48728   (1)| 00:09:4

 

5 |

 

|  2 |   TABLE ACCESS FULL|HZCZRK_JBXXB |     1 |    41 |    2   (0)| 00:00:0

 

1 |

 

|  3 |   TABLE ACCESS FULL|HZCZRK_ZPXXB | 61692 |   481K| 48725   (1)| 00:09:4

 

5 |

 

--------------------------------------------------------------------------------

 

---

 

 

Predicate Information (identified byoperation id):

---------------------------------------------------

 

  1 -access("JB"."RYID"="ZP"."RYID")

 

 

統計信息

----------------------------------------------------------

          8 recursive calls

          0 db block gets

          5 consistent gets

          0 physical reads

          0 redo size

        830 bytes sent via SQL*Net to client

        756 bytes received via SQL*Net from client

          2 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

          0 rows processed

 

工作當中總結出來通過上述18項的若干項優化后確實可以提高Oracle的性能，但是設置各參數的值需要根據服務器實際情況來做調整。