My Oracle Support Banner

Query Explain Plan is taking long time On Parse Due to MVIEW (Doc ID 2539536.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

In Similar Env Prod and Dev Query is spending more time on Parsing on Prod. While in Dev its taking 2 seconds.

Even the Explain Plan taking long time and spending  more time on parsing

In Good env:

call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  
Parse        1      3.00       3.05          4       3279          0          
 0
Execute      1      1.12       1.13          0         22          0          
 0
Fetch        0      0.00       0.00          0          0          0          
 0
------- ------  -------- ---------- ---------- ---------- ----------  
total        2      4.12       4.19          4       3301          0          
 

In Bad Env:

call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  
Parse        1    368.82     371.44          0         14          0          
 0
Execute      1      0.04       0.04          0          0          0          
 0
Fetch        0      0.00       0.00          0          0          0          
 0
------- ------  -------- ---------- ---------- ---------- ----------  
total        2    368.87     371.49          0         14          0          

 

By Setting below

By setting  "_fix_control"='19546825:OFF' the run time reduced from 5 minutes to 30 seconds on parsing

By setting  ALTER SESSION SET query_rewrite_enabled=false; is the fastest  with runtime of 2 seconds

Changes

Compare the MViews from the Good and Bad Env and found that they were not same in Good and Bad Env.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.