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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.