Oracle SQL statement: a WITH clause seems taking a lot of memory in database

Hi Experts:

    I am trying to optimize a SQL statement like this:
<select statement> UNION ALL <select statement> UNION ALL ...... UNION ALL <select statement>

    I changed it to:
WITH temp AS (select statement)
(<some select statements using temp "UNION ALL together">
UNION ALL <some select statement not using temp "UNION ALL" together>)

and then the Oracle database returns ORA-4030: out of process memory when trying to allocate XXX bytes (kxs-heap-c, temporary memory).

Anybody know why? and what can I do for that if I still want to simplify the SQL statement?

Thank you!
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

slightwv (䄆 Netminder)Connect With a Mentor Commented:
You are likely hitting a bug.  There should be a core file generated (check the alert log for the location and name).

You'll need to open an SR with Oracle to figure out the exact reason.

I really don't see how a WITH statement will simplify/optimize the original.  If you could explain a little more about what you are thinking maybe we can work around the bug.
Can you post the full query?

huangs3Author Commented:
Hi Slightwv and sdstuber:

    Sorry I cannot post the original SQL statement but I can explain the idea.

    The basic Idea is to put the repeated code in the WITH clause to make it more readable and avoid repeated computation.

    The original query is like:
<select statement 1> UNION ALL ... UNION ALL <select statement 30>
where each single sub statement only return several records or none. On another hand, each single sub statement queries large tables with up to millions of records corresponding to them. This original SQL statement returns in 20 seconds when running in TOAD.

    Some sub select statements are sharing identical code generating tempeorary data. For example, <select statement 1> roughly looks like:
 SELECT <some fields>,
              SELECT FROM (<shared select statement>)  WHERE <filter>) AS "DESCRIPTION",
   FROM tableY, tableX                   
WHERE <condition to join tableX and tableY>
    AND <some filters>
where the <shared select statement is shared with other "UNION ALL" clauses and I am putting it into WITH clause and then caused memory problem.

    Then I made an experimental change which is more obivious. I changed the SQL query to this:
WITH x AS (select '1' as v from dual -- this is a dummy)
<a dummy select statement using x>
<select statement 1> UNION ALL ... UNION ALL <select statement 30>
and then the memory problem shows up after 6 minutes. If I delete things after <select statement 10>, then it will return but takes a lot more than 20 seconds.

From the experiment result, it seems to me that Oracle is trying to buffer all the tables when WITH clause is being used, even though only a small amount of data is returned.

Any idea? Does it look like an Oracle design bug?
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

The subfactoring clause can be "inlined" or "materialized" at the optimizer's choice.

what does the plan look like for the query?  if you see lots of SYS_xxxxxx tables in the plan, then your clause is being materialized.
huangs3Author Commented:
Hi sdstuber:

    I selected the SQL query and click "Explain Plan" button, but it gives "ORA-02404:specified plan table not found" error, so I don't think I have privilege to see the plan.

    How is "inlined" and "materialized" different? How can I specify them?
    Thank you.
huangs3Author Commented:
I did some test:
1. if there are a lot of components in the "UNION ALL" statement, the error shows up.
2. if there are only a few components in the "UNION ALL" statement, then the error doesn't show up

So I think the data in the UNION ALL statement is bufferred if I let it follow the WITH clause. To resolve the issue I just keep the logic simple (not using WITH clause).

huangs3Author Commented:
I agree this may be a bug, and I just worked around.
All Courses

From novice to tech pro — start learning today.