Over use of Oracle With Clause?

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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