Thanks for the question, Patrick.
Asked: September 21, 2018 - 2:15 pm UTC
Last updated: September 24, 2018 - 3:40 pm UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
How can I force a long query parsing time for Oracle or how to prepare such a complicated query?
I want to simulate a situation where Oracle parses a query for 30 seconds before execution. Within 30 seconds I want to modify the synonym to see if the database will invalidate the query at the "Shared Pool Check" stage.
(
https://docs.oracle.com/database/121/TGSQL/tgsql_sqlproc.htm#TGSQL176 )
Unfortunately, all my queries are parsed in less than 2 seconds, which makes it impossible for me to make a test.
and Chris said...
I'm not aware of a way to "force" long parse times. Creating a "complicated" query that joins many tables with many candidate indexes and a high level of dynamic sampling may drag out the parse time. But I'd expect this to fall mostly in the optimization phase, rather than the shared pool check.
But... why exactly do you want to do this? What are you hoping to (dis)prove?
Is this answer out of date? If it is, please let us know via a Comment