在版本10.2中存在<BUG 6624968 – A QUERY AGAINST A TRANSACTIONAL TEXT INDEX CONSUMES HUGE PGA MEMORY>对于文本索引的查询可能引发PGA内存过量消耗， 如以下演示：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
SQL>
SQL> CREATE TABLE TESTTAB (COL1 NUMBER, COL2 VARCHAR2(500),
2 CONSTRAINT PK_COL1 PRIMARY KEY (COL1));
Table created.
SQL> CREATE INDEX CTXI ON TESTTAB (COL2)
2 INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('TRANSACTIONAL');
Index created.
BEGIN
FOR CNT IN 1..5000 LOOP
INSERT INTO TESTTAB VALUES
(CNT,'123456789 123456789 123456789 123456789 123456789 ');
IF MOD(CNT,500)=0 THEN COMMIT;
END IF;
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> conn maclean/oracle
Connected.
SELECT COL1 FROM TESTTAB WHERE CONTAINS(COL2,'%2') > 0;
SQL> col name for a30
SQL> set linesize 140 pagesize 1400
SQL> select ss.sid, sn.name, ss.value
2 from v$session se, v$sesstat ss,v$statname sn
3 where ss.STATISTIC#=sn.STATISTIC# and se.SID=ss.SID
4 and se.USERNAME='MACLEAN' and sn.name like '%pga%';
SID NAME VALUE
---------- ------------------------------ ----------
159 session pga memory 330403416
159 session pga memory max 331976280
BEGIN
FOR CNT IN 1..50000 LOOP
INSERT INTO TESTTAB VALUES
(CNT,'123456789 123456789 123456789 123456789 123456789 ');
IF MOD(CNT,500)=0 THEN COMMIT;
END IF;
END LOOP;
END;
/
SQL> set linesize 140 pagesize 1400
SQL> select ss.sid, sn.name, ss.value
2 from v$session se, v$sesstat ss,v$statname sn
3 where ss.STATISTIC#=sn.STATISTIC# and se.SID=ss.SID
4 and se.USERNAME='MACLEAN' and sn.name like '%pga%';
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 983993944
159 session pga memory max 985108056
SQL>
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 991071832
159 session pga memory max 992185944
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 993693272
159 session pga memory max 994807384
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1000771160
159 session pga memory max 1001885272
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1127648856
159 session pga memory max 1128762968
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1130008152
159 session pga memory max 1131122264
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1131974232
159 session pga memory max 1133088344
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1133678168
159 session pga memory max 1134792280
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1197641304
159 session pga memory max 1198755416
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1200262744
159 session pga memory max 1201376856
SQL> /
SID NAME VALUE
---------- ---------------------------------------------------------------- ----------
159 session pga memory 1695321688
159 session pga memory max 1696435800
该BUG已确认在版本10.2.0.4和11.1.0.7之前可以重复触发。 解决方法主要是达到最新的patchset
Comment