[20120109]sqlplus 與set longchunksize 設定問題.txt

昨天使用dbms_sqltune.report_sql_monitor,發現僅僅顯示一段,顯示不全.

SQL> select /*+ monitor */ count(*) from dept;
  COUNT(*)
----------
         4

SQL> select dbms_sqltune.report_sql_monitor from dual;
REPORT_SQL_MONITOR
--------------------------------------------------------------------------------
SQL Monitoring Report

SQL Text
------------------------------
select /*+ monitor */

看一下dbms_sqltune.report_sql_monitor的返回型別clob,可以猜到long設定太小.

SQL> show long longc
long 80
longchunksize 80

修改glogin.sql 檔案,沒有仔細思考加入,也就是設定long=1G,longchunksize=1G

set long 10000000
set longchunksize 10000

結果災難出現,
select dbms_sqltune.report_sql_monitor from dual;
很久都沒有出來.

檢視文件:

SET LONGCHUNKSIZE
SET LONGCHUNKSIZE sets the size of the increments SQL*Plus uses to retrieve a CLOB, LONG, NCLOB or XMLType value.

Experiment with different sizes if LONGS or LOBs are being fetched.

SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.

The maximum value of n is 2,000,000,000 bytes.


測試一下,long是顯示lob的大小,而LONGCHUNKSIZE是每次提取的大小,如果設定過大,導致OS使用swap.設定如下:
set long 10000000
set longchunksize 102400

在執行一些正常了!