Over use of Oracle With Clause?

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.

Thanks!

-
Be careful with your interpretation. Negative side effects on resources does not necessarily equate to cause a server to crash. –  Leigh Riffel Aug 16 '12 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 '12 at 14:41
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. –  Leigh Riffel Aug 16 '12 at 15:04
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. –  Leigh Riffel Aug 16 '12 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. –  Chris Travers Sep 6 '12 at 1:16

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.

-

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. –  Chris Travers Sep 3 '12 at 8:29

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.

-

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 '13 at 15:29
no, not yet..I will write here when/if I find explanation –  catalina May 22 '13 at 23:16