Query Rewrite - My Story of a Performance Nightmare

A while back, I worked on a project where we shifted data from an old datamodel to a new one. There are lots of interesting aspects and technical details about this project.

However, today, I want to discuss a complicated setup of hierarchical Materialized Views and the usage of the Query Rewrite feature. My main focus is to share an interesting observation regarding Materialized Views and the Query Rewrite feature, which led to a Parsing Nightmare.

Kindly note that this is my first blog post in English, and in this project, I was one of many developers. So, some things may be described in a way that seems strange to you, especially if you are a database administrator :D
I would appreciate your feedback in the comment section, and I will update the post periodically :)

What is Query Rewrite?

This feature is designed to optimize queries using Materialized Views without changing code. You can create a Materialized View with the same query as another view or query. By enabling the Query Rewrite feature on the Materialized View, the Optimizer might select the MView instead of the chosen tables, and so on.

This allows you to design indexes specifically for the query, which should significantly improve its speed.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-query-rewrite-materialized-views.html#GUID-BA8C963B-21C7-465D-8C94-CFE65F8B7513

Because query rewrite is transparent, materialized views can be added or dropped just like indexes without invalidating the SQL in the application code.

Query Rewrite invalidates my execution plans

We execute a query on the employees filtering the department 20. It's a simple query, but it serves well for demonstration purposes.

SELECT /* TEST_QUERY_REWRITE */ *
  FROM emp
 WHERE deptno = 20;

SELECT sql_id
      ,sql_text
      ,object_status
  FROM v$sql
 WHERE sql_id = '79ybcyu95tv4k';

After checking the status of this query, we can see that everything is fine. The plan is stored in our cache and may be reused during the next execution.

So, what happens if we create a Materialized View with the same query and enable the Query Rewrite option?

-- Create Materialized View with same filter
CREATE MATERIALIZED VIEW mv_emp_with_deptno_20
ENABLE QUERY REWRITE
AS
SELECT *
  FROM emp
 WHERE deptno = 20;

-- Get the explain plan of the query we executed before
-- As the table is really small the Optimizer might decide not to 
-- use the Query Rewrite function
-- With the REWRITE hint we will force the Optimizer to use it for
-- demonstration purposes
SELECT /*+ REWRITE */ /* TEST_QUERY_REWRITE */ *
  FROM emp
 WHERE deptno = 20;

-- Check the status of the SQL via query
SELECT sql_id
      ,sql_text
      ,object_status
  FROM v$sql
 WHERE sql_id = '0m9ms9rawncn9';

Because we are discussing Materialized Views, we need to update or refresh them to get new data. Let's proceed with a full refresh:

EXEC dbms_mview.refresh('MV_EMP_WITH_DEPTNO_20', 'C');

And now, let's check our SQL again:

SELECT sql_id
      ,sql_text
      ,object_status
  FROM v$sql
 WHERE sql_id = '0m9ms9rawncn9';

The query was invalidated after we refreshed the Materialized View.

In our project, we mainly used Fast Refreshable MViews. When we refreshed them, every 15 minutes, every query that used Materialized Views via Query Rewrite got invalidated. As a result a lot of our queries hat to be parsed again. Due to the complex nature of our queries this took sometimes 30 seconds and more.. :(

Edit 29.04.2024: Why do some queries take 30 seconds and more to parse?
I don't want to dive too deep into the specific project. In conclusion, these queries used hundreds of objects e.g. via View on View on View on multiple MViews etc. So the Parse Time was somehow reasonable. (We don't have to talk about how it make sense to query that much objects etc. 😄)
Before we knew the reason why the queries had to parse that often, the Performance Tuning guys fixed the problems with attaching fix execution plans to the queries. So the parse time was reduced drastically.

Let's do some more tests

Let's see what happens if we select the Materialized View directly instead of through Query Rewrite. I would expect the query to also be invalidated after refreshing the Materialized View, right?
From my point of view it should not matter if we select the MView directly that is used by the Query Rewrite option. (Even though there is obviously a technical difference)

But as we can see the SQL is still valid.

-- Select Materialized View directly
SELECT /* TEST_QUERY_REWRITE_MV */ *
  FROM mv_emp_with_deptno_20;

-- Get information about the query
SELECT sql_id
      ,sql_text
      ,object_status
  FROM v$sql
 WHERE sql_id = '1m1d6zt1t0g9y';

-- Refresh Materialized View
EXEC dbms_mview.refresh('MV_EMP_WITH_DEPTNO_20', 'C');

In another example, I created a Materialized View without any filters on a table. This might not be applicable for practical purposes, but I was curious to see what would happen.

The Materialized View is utilized by the Optimizer and also invalidates the SQL after the refresh.

-- Create materialized View on dept table
CREATE MATERIALIZED VIEW mv_dept_all
ENABLE QUERY REWRITE
AS
SELECT *
  FROM dept;

-- Select Dept Table with any filter (with Optimizer Hint to use the MView)
SELECT /*+ REWRITE */ /* TEST_MV_DEPT */ *
  FROM dept
 WHERE loc = 'NEW YORK';

-- Check SQL status
SELECT sql_id
      ,sql_text
      ,object_status
  FROM v$sql
 WHERE sql_id = '1v9w9fy5n2pzf';

-- Refresh
EXEC dbms_mview.refresh('MV_DEPT_ALL', 'C');

-- Check SQL status again

Be cautious when you use Query Rewrite

The behavior described above caused many issues in my project as performance rapidly declined after refreshing the Fast Refreshable Materialized Views. In my project things got even worse, since we used a RAC and invalidated queries had to be parsed on multiple nodes over and over again.

It took us a couple of weeks to understand why the queries were becoming invalid.

After rebuilding all Materialized Views without the Query Rewrite Option the problems with excessive parsing disappeared.

TLDR

• Query Rewrite feature in Materialized Views can lead to SQL queries being invalidated after refreshes

• This can cause performance issues and excessive parsing

• Consider rebuilding Materialized Views without the Query Rewrite Option to avoid problems.