I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each other to preform a complex task.

It was brought to my attention today that overuse of the with clause could have negative side effects on the Oracle server's resources.

Can anyone explain why over use of the oracle with clause may cause a server to crash? Or point me to some articles where I can research appropriate use cases? I started using the With clause heavily to add structure to my code and make it easier to understand, I hope with some informative responses here I can continue to use it efficiently.


  • 2
    Be careful with your interpretation. Negative side effects on resources does not necessarily equate to cause a server to crash. Aug 16, 2012 at 12:21
  • To be more specific the individual who told me this was a OCP certified SQL developer with a masters in computer science. He works at a large hospital in southern California. He said that their main reporting server had crashed from the overuse of with clause by report developers eating up server resources. Which spawned this question. He noted that the hospital DBAs had since restricted when the report developers use the with clause.
    – Cimplicity
    Aug 16, 2012 at 14:41
  • 2
    Did you ask if they tried the same query moving the WITH clauses inline as sub-queries to prove that the WITH syntax was causing the issue? If they could prove a problem was from the clause then they should ask Oracle to create a bug for it. If the problem is more along the lines of an extremely high cost query, then it still shouldn't crash, but it is understandable that it would have an abnormally high resource drain. Aug 16, 2012 at 15:04
  • 1
    I agree with Rob Farley (+1). Restricting the WITH was probably beneficial because it caused the developers to think more about the problem as a whole and therefore eliminate the need for some sub-queries. Aug 16, 2012 at 15:05
  • As a note, the biggest performance gotchas I have seen due to optimization fences in PostgreSQL involved nested groups of joins with no subqueries. I saw one query drop from taking 3 hours to taking 7 seconds after factoring some of those out. Inline views/CTE's are one example of something that can cause this but not the only one. Sep 6, 2012 at 1:16

5 Answers 5


Remember that the WITH clause is used to name sub-queries, and sub-query logic can be abused somewhat. The overhead for WITH shouldn't end up being much more than using a regular sub-query, but still don't try to have a ridiculous amount of nesting going on. So long as you have a good picture of how deep you're going with things, you should be fine.


To start with let's note that I don't know all of the internals of Oracle's implementation but I do know WITH statements pretty well.

One thing to note is that WITH statements return result sets are intended to remain stable through the course of a query. In other words, logically they create sort of synthetic relations you can reference elsewhere in your query. I understand Oracle tries to fold these in as subqueries but there may be cases where this doesn't work right. In those cases, optimization of execution of the code will not be possible across the WITH statement boundary. There are other cases I have seen break optimization of execution on PostgreSQL for valid mathematical reasons and would assume Oracle is the same there so WITH statements are not unique and the problem always has to do with stability of results within a portion of the query.

For this reason even though Oracle is better than PostgreSQL at planning WITHs, I would expect there to be problems as well.

This shouldn't cause the server to crash but it may mean greater memory usage, more CPU usage, and overall less efficient query plans. So keep in mind that WITH statements can cause performance problems. Keep them to where they make sense. Oracle has a bunch of other ways of encoding your data transformations as well but without actual concrete examples, it is hard to know what to recommend.


Oracle call's the WITH clause "sub-query factoring". Its main use is to improve the performance of queries which use the same sub-query more than once.

We can also use it to make our code easier to understand but over-simplification can cause poor performance. It would be easier to understand your scenario if you posted some code, but you seem to be describing a procedural approach. This smacks of RBAR and is likely to perform slower than a set-based approach.

Employing lots of WITH clauses should not bring a database server to its knees. However it will probably result in unnecessary data retrieval and additional CPU churn. This will put more strain on the database than the equivalent logic implemented with decent JOINs.

  • Agreed. It's important to use queries to define what information you want returned, not how you want it returned. Sep 3, 2012 at 8:29

Not sure that WITH statement can couse perfomance issues, but for sure it can couse validation isssues.

We had case (in Oracle) when compilation of package that reads view with 'with option' fails with 'ERROR at line 1: ORA-00600: internal error code, arguments: [kqludp2], [], [], [], [], [], [], []' error.

It compiles ok after the view is recreated, and when view is finaly re-written to be without 'with option' package started to behave ok. So I personaly look to avoid 'with option', until I understand what was the issue here.

  • Did you file a bug report with Oracle? What did they say about this?
    – Mat
    May 22, 2013 at 15:29
  • no, not yet..I will write here when/if I find explanation
    – catalina
    May 22, 2013 at 23:16

With clause should generally be used if the resultant dataset is below 999 rows. Based on non-documented factors, Oracle decides whether it wants to store the data set in memory or in a oracle managed GTT. There is an undocumented way to force ORacle to store the result set in a Oracle defined GTT. The result set created if stored in runtime memory can become a resource hog and penalize resources in PGA AND UGA. I have seen situations where PGA and UGA memory usage of a session has increased 400% with a With() executed and expected on reserve increased by 1800%. If the result set is stored in a GTT the resource requirement would not go so high. My 2c

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.