---
title: "Discussion on \"Query Rewrite - My Story of a Performance Nightmare\" | Hashnode"
description: "Discussion on \"Query Rewrite - My Story of a Performance Nightmare\". Optimize queries with Materialized Views using Query Rewrite, but beware of potential performance nightmares and invalidated SQL queries"
url: "https://hashnode.com/posts/query-rewrite-my-story-of-a-performance-nightmare/662e404658beadbf834b8a13"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [Query Rewrite - My Story of a Performance Nightmare](https://carokruetzi.hashnode.dev/query-rewrite-my-story-of-a-performance-nightmare?utm_source=hashnode&utm_medium=feed)

Optimize queries with Materialized Views using Query Rewrite, but beware of potential performance nightmares and invalidated SQL queries

Apr 28, 2024 · 5 min read · carokruetzi.hashnode.dev  
Updated Apr 29, 2024  
Read the article

-   [#materialized-views](https://hashnode.com/tag/materialized-views)
-   [#query-rewrite](https://hashnode.com/tag/query-rewrite)
-   [#oracle](https://hashnode.com/tag/oracle)
-   [#performance-optimization](https://hashnode.com/tag/performance-optimization)

## Written by

[Caro Krützmann](https://hashnode.com/@caroKruetzi)

@caroKruetzi · Near of Hamburg, Germany

  
[View profile](https://hashnode.com/@caroKruetzi)

oracle apex, plsql, oracle database

## Responses 2

Markdown supported · ⌘↵ to post

[David Kurtz](https://hashnode.com/@DavidKurtz)

[Apr 29, 2024](https://hashnode.com/posts/query-rewrite-my-story-of-a-performance-nightmare/662e404658beadbf834b8a13/comment/662f7c30421c789ec3521ec2)

I would expect a complete refresh to invalidate cursors on those materialized views.  
I have successfully used fast refreshed materialized views in a high-parse environment (to introduce HCC on Exadata in PeopleSoft Financials). The parse is a symptom. The question is what is the cause of so much parse, and why is it such a problem for you?

[Caro Krützmann](https://hashnode.com/@caroKruetzi)

[Apr 29, 2024](https://hashnode.com/posts/query-rewrite-my-story-of-a-performance-nightmare/662e404658beadbf834b8a13/comment/662f7c30421c789ec3521ec2#reply-662fec8c0f24760be6bd4c17)

Hi David, I think the real problem was a series of bad decisions :D I also think that Query Rewrite is not bad at all. The behavior was unexpected and should be checked before implementing MViews with such an option. But I think this is applies for any technical solution.

[Philipp Salvisberg](https://hashnode.com/@phsalvisberg)

[Apr 29, 2024](https://hashnode.com/posts/query-rewrite-my-story-of-a-performance-nightmare/662e404658beadbf834b8a13/comment/662f534b509598ca67cb1d48)

Caro, nice write-up. You wrote, "Due to the complex nature of our queries this took sometimes 30 seconds and more.. :(". IMO it would be interesting to explore why parsing took so long. There are several possible reasons for that. In any case, 30 seconds is far too long. It should always be less than a second which probably would have been unnoticed by the users. 30 seconds is also not acceptable for new sessions. Therefore, fixing the parsing issue would have been an adequate solution approach.

[Caro Krützmann](https://hashnode.com/@caroKruetzi)

[Apr 29, 2024](https://hashnode.com/posts/query-rewrite-my-story-of-a-performance-nightmare/662e404658beadbf834b8a13/comment/662f534b509598ca67cb1d48#reply-662fea4c76126818cf3c35ae)

Hi Philipp, thank you very much for your response :) I've added a note for the long parse time. The queries were using many objects via Views on Views on Views with the MViews in between. So we used a lot of objects (can't remember the exact number anymore) and the parse time seemed to be reasonable. At least the Tuning guys didn't mention it specifically :D

What bothers me is the different behavior when selecting data from a query with Query Rewrite and selecting data from the Materialized View directly. I can imagine that the Optimizer is forced to get a new plan due to changes in the data. Maybe it would then decide to query the data directly from the table and not from the Materialized View 🤔
