[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

在执行一些正常了!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-714642/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-714642/