imp STATISTICS=y的陷阱
今天一个数据库被查出有很多literal SQL，有6000多条。都是来自imp.
SQL_TEXT COUNT(*) MODULE
———————————— ————————
DECLARE SREC DBMS_STATS.STA 6006 imp@MACHINE (TNS V1-V3)
Literal SQL的文本如下，都是在设置导入表相应的统计信息。这个表不但有很多列,而且有很多分区和子分区。列*分区，就有6000条的literal SQL.
SQL_TEXT
—————————————————————-
DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL; SREC.MAXVAL := NULL; SREC.EAVS := 0;
SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(0,0); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);
SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,’”TEST”‘,’”FLAG_5″‘,’”PART44_SUBPARTMAX”‘,
NULL,NULL,0,0,0,srec,0,6); END;
察看这些literal SQL占用内存的情况
SQL> select sum(SHARABLE_MEM)/power(1024,2) SHARABLE_MEM,sum(PERSISTENT_MEM)/power(1024,2) PERSISTENT_MEM,sum(RUNTIME_MEM)/power(1024,2)
RUNTIME_MEM from v$sql where sql_text like ‘DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := NULL;%’;SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
———— ————– ———–
105.5 6.1 1.8
三个数值似乎都不高，但从statspack里面我看到IMP那个时候shared pool free memory大概降低了200M。还好我就导入一个表。要是导入的表很多而Shared pool free memory又不多的情况下，说不定会很引起很严重的Shared pool contention。
这些literal SQL在10g中也不好manually remove,11g的话说不定可以用dbms_shared_pool.remove。
还有一个tip是order by可以在为长时间打开的cursor避免ora-1555
通常开发者可能会open一个cursor，取一些纪录，然后对这些纪录作处理,又或者作些别的事情,然后再fetch一些纪录….. 记录多的时候，这个cursor就会open很长时间，超过undo_rentention的时候，就有可能发生ORA-1555的错误。
如果修改代码很麻烦，则我们可以在这个SQL cursor上加一个order by，让Oracle先将所有数据读出，排序，将排好顺序的纪录存储在pga或者temporary tablespace。以后再fetch cursor的时候，直接从pga中读取已排序的记录，再无需做consistent read,因此避免了ora-1555.
有时候确实想不到这个good idea.
‘如果修改代码很麻烦，则我们可以在这个SQL cursor上加一个order by，让Oracle先将所有数据读出，排序’
加了order by ，这个cursor就会在open时fetch所有数椐?
还是说用bulk insert到temp table ?
Open cursor后，fetch第一行的时候，因为是排序的，所以Oracle要一致读所有的记录，在pga或者temproary tablespace中排好序，然后返回第一行给客户端。再fetch接下来的行时，直接从pga或者temproary tablespace按照顺序返回记录。
加order by的主意看起来很不错。不知道能否实验证实一下？
statistics=none is almost always recommended. The same applies to impdp (the parameter is exclude=(table_statistics,index_statistics)). It has the same problem of littering the shared pool with lots of literal SQLs. See Bug 7185178. If the damage is already done, you can simply “grant select on sys.impdp_stats to dba” to remove those SQLs (in case of impdp). BTW, dbms_shared_pool.purge (not remove) is available from 10.2.0.4 and up.