Query Explain Plan is taking long time On Parse Due to MVIEW
(Doc ID 2539536.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation 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 |