Oracle arraysize 和 fetch size 引數與效能優化說明

From:http://blog.csdn.net/tianlesoftware/article/details/6579913

一. 引數說明

1.1 arraysize 引數

Oracle sqlplus 有很多設定，這個在我之前的blog有說明：

Oracle sqlplus 常用命令總結

http://blog.csdn.net/tianlesoftware/archive/2009/12/19/5040984.aspx

昨天和owind 討論問題的時候，他強調了這個引數，通過一些測試，確實與效能這塊有很大影響。

Arraysize specifies how many rows SQL*Plus will fetch in a call. The number n can be between 1 and 5000.

arraysize定義了一次返回到客戶端的行數，當掃描了arraysize 行後，停止掃描，返回資料，然後繼續掃描。

這個過程就是統計資訊中的SQL*Net roundtrips to/from client。因為arraysize 預設是15行，那麼就有一個問題，因為我們一個block 中的記錄數一般都會超過15行，所以如果按照15行掃描一次，那麼每次掃描要多掃描一個資料塊，一個資料塊也可能就會重複掃描多次。

重複的掃描會增加consistent gets 和 physical reads。增加physical reads，這個很好理解，掃描的越多，物理的可能性就越大。

consistent gets，這個是從undo裡讀的數量，Oracle 為了保證資料的一致性，當一個查詢很長，在查詢之後，資料塊被修改，還未提交，再次查詢時候，Oracle根據Undo 來構建CR塊，這個CR塊，可以理解成資料塊在之前某個時間的狀態。這樣通過查詢出來的資料就是一致的。

那麼如果重複掃描的塊越多，需要構建的CR塊就會越多，這樣讀Undo 的機會就會越多，consistent gets 就會越多。

如果資料每次傳到客戶端有中斷，那麼這些資料會重新掃描，這樣也就增加邏輯讀，所以調整arraysize可以減少傳的次數，減少邏輯讀。

關於CR 參考我的Blog：

CR (consistent read) blocks create 說明

http://blog.csdn.net/tianlesoftware/archive/2011/06/07/6529401.aspx

所以通過上面的說明，arraysize 引數如果過低，會影響如physical reads，consistent gets 還有SQL*Net roundtrips to/from client次數。

永久儲存arraysize 引數：

可以該引數儲存到glogin.sql 或者login.sql 檔案裡，這樣可以永久生效，不必每次都去set 指定。

-- 檢視預設值

SYS@anqing2(rac2)> show arraysize

arraysize 15

--手工修改arraysize

SYS@anqing2(rac2)> set arraysize 100

SYS@anqing2(rac2)> show arraysize

arraysize 100

--修改glogin.sql

[oracle@rac2 admin]$ pwd

/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin

[oracle@rac2 admin]$ ls

glogin.sql help iplus libisqlplus.def libsqlplus.def plustrce.sql pupbld.sql

在glogin.sql裡新增：

set arraysize 5000

--重新登陸，查詢

SYS@anqing2(rac2)> show arraysize

arraysize 5000

1.2 fetch size 引數

arraysize 和 fetch size 引數都是客戶段的一個引數，需要在客戶段來設定，arraysize 是在sqlplus 中設定的，如果我們通過程式去連資料庫，那麼這個引數就是Fetch size。它的作用和arraysize 一樣。 Fetch size 預設是10，一般改成50就ok了，太大會消耗記憶體。

The JDBC fetch size gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. For large queries that return a large number of objects you can configure the row fetch size used in the query to improve performance by reducing the number database hits required to satisfy the selection criteria.Most JDBC drivers (including Oracle) default to a fetch size of 10, so if you are reading 1000 objects, increasing the fetch size to 256 can significantly reduce the time required to fetch the query's results. The optimal fetch size is not always obvious. Usually, a fetch size of one half or one quarter of the total expected result size is optimal. Note that if you are unsure of the result set size, incorrectly setting a fetch size too large or too small can decrease performance.

In this example application, I print out the default fetch size and then increase it to 50 using the setFetchSize(int) method of a Statement object. When you execute the query, the JDBC driver retrieves the first 50 rows from the database (or all rows if less than 50 rows satisfy the selection criteria). As you iterate over the first 50 rows, each time you call rset.next(), the JDBC driver returns a row from local memory – it does not need to retrieve the row from the database. When you try to access the fifty first row (assuming there are more than 50 rows that satisfy the selection criteria), the JDBC driver again goes to the database and retrieves another 50 rows. In this way, 100 rows are returned with only two database hits.

Alternatively, you can use the method setMaxRows() to set the limit for the maximum number of rows that any ResultSet can contain. If you specify a value of zero, then the hint is ignored: the JDBC driver returns one row at a time. The default value is zero.

如下連線是一個Jdbc 中配置Fetch size的示例。

http://www.idevelopment.info/data/Programming/java/jdbc/FetchSize.java

二. 相關測試

每個block 中row的條數和row的大小也有關係，row 內容越多，那麼block 中的row就會少。

每個block裡有多少條記錄，可以通過rowid 來判斷。

關於Oracle rowid說明，參考我的Blog

Oracle Rowid 介紹

http://blog.csdn.net/tianlesoftware/archive/2009/12/16/5020718.aspx

rowid 格式如下：OOOOOOFFFBBBBBBRRR, 其中：

（1）OOOOOO

The data object number identifies the segment (data object AAAPec in Example 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.

（2）FFF

The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

（3）BBBBBB

The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.

（4）RRR

The row number identifies the row in the block (row AAA in Example 12-1).

DAVE@anqing2(rac2)> create table dave as select * from sys.ta where rownum<10000;

Table created.

-- 檢視table 佔用blocks 數量

DAVE@anqing2(rac2)> select owner,extents,segment_name,blocks from dba_segments where segment_name='DAVE' and owner='DAVE';

OWNER EXTENTS SEGMENT_NAME BLOCKS

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

DAVE 3 DAVE 24

從這個資料算一個，1000行資料24個資料塊。平均下來每個資料塊裡有417條記錄. 但事情情況可能不是這樣.

--表結構很簡單

DAVE@anqing2(rac2)> desc dave;

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(10)

-- 檢視rowid格式

DAVE@anqing2(rac2)> select rowid from dave where rownum=1;

ROWID

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

AAANXzAAHAAAAAMAAA

--檢視每個資料塊中有多少記錄：

/* Formatted on 2011/7/1 14:59:56 (QP5 v5.163.1008.3004) */

SELECT prerid, COUNT (rid) rid

FROM (SELECT SUBSTR (ROWID, 1, 15) prerid, ROWID rid FROM dave)

GROUP BY prerid;

DAVE@anqing2(rac2)> select prerid,count(rid) rid from (select substr(rowid,1,15) prerid,rowid rid from dave) group by prerid;

PRERID RID

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

AAANXzAAHAAAAAa 517

AAANXzAAHAAAAAf 517

AAANXzAAHAAAAAP 517

AAANXzAAHAAAAAU 517

AAANXzAAHAAAAAW 517

AAANXzAAHAAAAAX 517

AAANXzAAHAAAAAM 524

AAANXzAAHAAAAAO 517

AAANXzAAHAAAAAQ 517

AAANXzAAHAAAAAS 517

AAANXzAAHAAAAAY 517

AAANXzAAHAAAAAR 517

AAANXzAAHAAAAAg 169

AAANXzAAHAAAAAN 517

AAANXzAAHAAAAAT 517

AAANXzAAHAAAAAV 517

AAANXzAAHAAAAAb 517

AAANXzAAHAAAAAe 517

AAANXzAAHAAAAAc 517

AAANXzAAHAAAAAd 517

20 rows selected.

-- 這裡只有20行，即實際只使用了20個資料塊，每個資料塊的記錄如上查詢結果，因為表的記錄很簡單，所以每個塊中的記錄很多。

但是之前我們查詢表佔用了24個資料塊，那麼通過以下查詢，可以理解為什麼是24個blocks：

DAVE@anqing2(rac2)> select extent_id,block_id,blocks from dba_extents where owner='DAVE' and segment_name='DAVE';

EXTENT_ID BLOCK_ID BLOCKS

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

0 9 8

1 17 8

2 25 8

因為這裡分配了3個extents，每個extent 由8個blocks組成。

如果按照預設的情況，arraysize 為15，那麼每個塊要查詢的次數是：517/15 = 35次。那麼這個就會帶來更多的consistents gets 和 physical read。我們驗證一下。

DAVE@anqing2(rac2)> set autot traceonly stat

DAVE@anqing2(rac2)> select * from dave where rownum<518;

-- 因為一個資料塊中有517條記錄，所以這裡只查詢一個資料塊的次數。

517 rows selected.

Statistics

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

7 recursive calls

0 db block gets

87 consistent gets

0 physical reads

0 redo size

9354 bytes sent via SQL*Net to client

774 bytes received via SQL*Net from client

36 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

517 rows processed

--注意這裡的SQL*Net roundtrips to/from client，在之前，我們估計是按照arraysize 的預設值，讀完這個資料塊需要roundtrips 35次，這裡實際用了36次。

我們設定下arraysize，在查詢：

DAVE@anqing2(rac2)> set arraysize 10000

SP2-0267: arraysize option 10000 out of range (1 through 5000)

--arraysize 最大5000

DAVE@anqing2(rac2)> set arraysize 5000

DAVE@anqing2(rac2)> select * from dave where rownum<518;

517 rows selected.

Statistics

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

0 recursive calls

0 db block gets

5 consistent gets

0 physical reads

0 redo size

5036 bytes sent via SQL*Net to client

400 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

517 rows processed

比較：

consistent gets ：從87 變成了5.

SQL*Net roundtrips to/from client ：從36 變成了2

如果資料量越大，那麼這種優化的效能提升就越明顯。

[jdbc drive fetch size example:

// -----------------------------------------------------------------------------
// ConnectionOptions.java
// -----------------------------------------------------------------------------

/*
 * =============================================================================
 * Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved.
 * 
 * All source code and material located at the Internet address of
 * http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and
 * is protected under copyright laws of the United States. This source code may
 * not be hosted on any other site without my express, prior, written
 * permission. Application to host any of the material elsewhere can be made by
 * contacting me at jhunter@idevelopment.info.
 *
 * I have made every effort and taken great care in making sure that the source
 * code and other content included on my web site is technically accurate, but I
 * disclaim any and all responsibility for any loss, damage or destruction of
 * data or any other property which may arise from relying on it. I will in no
 * case be liable for any monetary damages arising from such loss, damage or
 * destruction.
 * 
 * As with any code, ensure to test this code in a development environment 
 * before attempting to run it in production.
 * =============================================================================
 */
 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * -----------------------------------------------------------------------------
 * The JDBC fetch size gives the JDBC driver a hint as to the number of rows 
 * that should be fetched from the database when more rows are needed. For 
 * large queries that return a large number of objects you can configure the 
 * row fetch size used in the query to improve performance by reducing the 
 * number database hits required to satisfy the selection criteria. Most 
 * JDBC drivers (including Oracle) default to a fetch size of 10, so if you are 
 * reading 1000 objects, increasing the fetch size to 256 can significantly 
 * reduce the time required to fetch the query's results. The optimal fetch 
 * size is not always obvious. Usually, a fetch size of one half or one quarter 
 * of the total expected result size is optimal. Note that if you are unsure of 
 * the result set size, incorrectly setting a fetch size too large or too small
 * can decrease performance.
 *
 * In this example application, I print out the default fetch size and then 
 * increase it to 50 using the setFetchSize(int) method of a Statement object.
 * When you execute the query, the JDBC driver retrieves the first 50 rows from 
 * the database (or all rows if less than 50 rows satisfy the selection 
 * criteria). As you iterate over the first 50 rows, each time you call 
 * rset.next(), the JDBC driver returns a row from local memory ? it does not 
 * need to retrieve the row from the database. When you try to access the fifty 
 * first row (assuming there are more than 50 rows that satisfy the selection 
 * criteria), the JDBC driver again goes to the database and retrieves another 
 * 50 rows. In this way, 100 rows are returned with only two database hits.
 * 
 * Alternatively, you can use the method setMaxRows() to set the limit for
 * the maximum number of rows that any ResultSet can contain.
 *
 * If you specify a value of zero, then the hint is ignored: the JDBC driver 
 * returns one row at a time. The default value is zero.
 * -----------------------------------------------------------------------------
 * @version 1.0
 * @author  Jeffrey M. Hunter  (jhunter@idevelopment.info)
 * @author  http://www.idevelopment.info
 * -----------------------------------------------------------------------------
 */

public class FetchSize {

    static final String driver_class  = "oracle.jdbc.driver.OracleDriver";
    static final String connectionURL = "jdbc:oracle:thin:@linux1:1521:orcl1";
    static final String userID        = "scott";
    static final String userPassword  = "tiger";
                                        
    public FetchSize() {
    }

    public void runTest() {

        Connection  con = null;
        Statement   stmt = null;
        ResultSet   rset = null;
        String      query_string = "SELECT * FROM tables WHERE rownum < 200 ORDER BY owner, table_name";
        int         newFetchSize = 50;

        try {

            System.out.println("+-------------------------------+");
            System.out.println("| SETUP CONNECTION              |");
            System.out.println("+-------------------------------+");

            System.out.println("Loading JDBC Driver  -> " + driver_class);
            Class.forName (driver_class).newInstance();

            System.out.println("Connecting to        -> " + connectionURL);
            con = DriverManager.getConnection(connectionURL, userID, userPassword);
            System.out.println("Connected as         -> " + userID);

            System.out.println("Turning Off AutoCommit...");
            con.setAutoCommit(false);

            /*
            **  EXECUTE GENERIC QUERY
            */
            System.out.println("+-------------------------------+");
            System.out.println("| EXECUTE GENERIC QUERY         |");
            System.out.println("+-------------------------------+");

            System.out.println("Executing Generic (SYSDATE) Query...");

            System.out.println("Creating Statement...");
            stmt = con.createStatement ();
            
            System.out.println("Get Default Fetch Size:" + stmt.getFetchSize());
            System.out.println("Manually Set Default Fetch Size to " + newFetchSize);
            
            stmt.setFetchSize(newFetchSize);
            System.out.println("Get New Fetch Size:" + stmt.getFetchSize());

            System.out.println("Opening ResultsSet...");
            rset = stmt.executeQuery (query_string);

            while (rset.next ()) {
                System.out.println("  RESULTS            -> " + rset.getString (2));
            }

            System.out.println("Closing ResultSet...");
            rset.close();

            System.out.println("Closing Statement...");
            stmt.close();

        }  catch (SQLException e) {

            e.printStackTrace();

            if (con != null) {
                try {
                    con.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }

        } catch (Exception e) {

            e.printStackTrace();
        
        } finally {

            if (con != null) {
          
                try {

                    System.out.println("+-------------------------------+");
                    System.out.println("| CLOSE DOWN ALL CONNECTIONS    |");
                    System.out.println("+-------------------------------+");

                    System.out.println("Closing down all connections...");
                    con.close();
                
                } catch (SQLException e) {
                
                    e.printStackTrace();
                    
                }
            }
          
        } // FINALLY
    }

    public static void main(String[] args) {
        FetchSize fetchSize = new FetchSize();
        fetchSize.runTest();
    }
}

]

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27042095/viewspace-1171241/，如需轉載，請註明出處，否則將追究法律責任。

Oracle arraysize 和 fetch size 引數 與 效能優化 說明

相關文章

Oracle arraysize 和 fetch size 引數與效能優化說明