有時候你可能會用SQLPLUS spool 表的數(shù)據(jù),那么怎么加快spool速度呢?SQLPLUS中有個行預取的選項
SQLPLUS中 arraysize默認為15
SQL> show arraysize
arraysize 15
它表示從Oracle服務器端一次只傳遞15行記錄到客戶端(SQLPLUS),當然了JDBC,WEBLOGIC也有行預取,具體自己Google
-
舉個例子:
SQL> select * from test where owner='ADWU_OPTIMA_AP11';
773 rows selected.
Elapsed: 00:00:30.95
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 134K| 882 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 593 | 134K| 882 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='ADWU_OPTIMA_AP11')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2976 consistent gets
0 physical reads
0 redo size
50484 bytes sent via SQL*Net to client
597 bytes received via SQL*Net from client
53 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
773 rows processed
SQL> set arraysize 5000
SQL> select * from test where owner='ADWU_OPTIMA_AP11';
773 rows selected.
Elapsed: 00:00:16.06
Execution Plan
----------------------------------------------------------
Plan hash value: 217508114
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593 | 134K| 882 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TEST | 593 | 134K| 882 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='ADWU_OPTIMA_AP11')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2927 consistent gets
0 physical reads
0 redo size
47800 bytes sent via SQL*Net to client
241 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
773 rows processed
當設置 arraysize 之后,SQLPLUS 客戶端與數(shù)據(jù)庫Server端交互次數(shù)明顯減少,這就是為什么返回773行數(shù)據(jù)第二次比第一次快1倍了,同時也可以看到,第二次邏輯讀比第一次低了,那說明設置行預取會影響邏輯讀。 本文出自:億恩科技【mszdt.com】
服務器租用/服務器托管中國五強!虛擬主機域名注冊頂級提供商!15年品質保障!--億恩科技[ENKJ.COM]
|