I think I must have hit "reply" instead of "reply" all on the last message.
The two rows you've sent have four columns labelle I I I I - I'm guessing that
the last two are "is_bind_aware" and "is_bind_sensitive", and the first child
has both of these as Y - which makes you wonder how a statement with only
literals can be bind_aware.
The query is against user_tables, etc. So one thing to check is whether the
parsing_user_id, parsing_schema_id are the same in both cases.
I think the dictionary views you query also have scalar subqueries and
functions in the select list that could do different amounts of work for
different users, and there are plenty of calls to sys_context() in them that
could result in different amounts of work for the same plan.
If the queries run a few dozen times an hour you might inject a /*+ monitor */
hint into them as an SQL_Patch if you're licensed to use the diagnostic and
performance packs - this might let you see what's different between different
rates of execution and where the different plans do their work.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
Sent: 31 October 2019 18:27
To: Chris Taylor
Cc: oracle-l-freelist
Subject: Re: Force specific plan to be used
As stated, the parent and child cursor have the same plan_hash value. The SQL
uses literals and they are the same. In the good child, it states "statistics
feedback used for this statement" - that makes it better.
So, to be clear, the all the cursors have the same plan ? (Both good and bad) ?Chris Taylor <christopherdtaylor1994@xxxxxxxxx> 10/31/19 2:22 PM >>>
