I have an issue with a process that I'm running on a batch of ~60,000 records. The process runs a series of dynamic sql statements that use bind variables.
In QA, our temp tablespace is around 40 GB. While running the process, the temp tablespace starts growing faster and faster and eventually runs out of space and kills my process.
I want to figure out what is causing the tablespace to grow so large so fast.
For example, when I started running the process temp tablespace usage was at 11 MB. Within 10 minutes, it was at 1.7 GB.
Here is a sample of the query I was running to monitor tablespace usage:
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
and a.tablespace_name like '%TEMP%'
GROUP by A.tablespace_name, D.mb_total
/
12:38:26 SQL> /
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 39936 8032 31904
12:48:49 SQL> /
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 39936 9490 30446
12:55:55 SQL> /
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 39936 11478 28458
13:05:51 SQL> /
TABLESPACE MB_TOTAL MB_USED MB_FREE
------------------------------- ---------- ---------- ----------
TEMP 39936 14790 25146
The initial red flag that we got is that Oracle was creating a large number of child cursors that are taking up a lot of of the temp tablespace:
select child_number from v$sql where sql_id = :1 order by child_number
and although I ran a trace specifically for the "1652" error, it gives you the query upon which the tablespace ran out of space, not necessarily the query that cause the problem in the first place.
I also ran a full trace on the process, but my question is: where do I begin looking to try to identify what is causing the tablespace usage to grow so fast?
My Oracle version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production