That literally cannot make it better if the plans are identical. :/
Its more likely the results are cached or other executions experienced some
type of wait event that slowed them down.
You can look at DBA_HIST_ACTIVE_SESS_HISTORY for the SQL_IDs and
session_id/session_serial#s and look at EVENT, WAIT_CLASS, WAIT_TIME,
SESSION_STATE, BLOCKING_SESSION_STATUS, BLOCKING_SESSION,
BLOCKING_SESSION_SERIAL#.
It could ALSO be DYNAMIC SAMPLING slowing it down - do you have stats on
the tables involved in the query and how old are they (are they stale)?
What is your optimizer_dynamic_sampling parameter set to?
Chris
On Thu, Oct 31, 2019 at 2:28 PM Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
wrote:
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 andChris Taylor <christopherdtaylor1994@xxxxxxxxx> 10/31/19 2:22 PM >>>
bad) ? If that's true , then a sql plan isn't the problem. Sounds like one
of the child cursors is working on a lot less data if its using the same
execution plan and performing much better.
However, if the one good cursor is showing a different plan, look up the
coe_xfr_sql_profile.sql from Carlos. It takes a SQL_ID and a
PLAN_HASH_VALUE and creates an sql script to force a SQL profile for that
specific plan. Then you execute the sql script that is generated to create
the profile in the db.
Chris
On Thu, Oct 31, 2019 at 2:03 PM Jeffrey Beckstrom <jbeckstrom@xxxxxxxxx>
wrote:
I have a couple of SQL statements that have multiple child cursors. Each
child cursor has the same plan hash value. The plans all show "this is an
adaptive plan (rows marked '-' are inactive)". The difference is that on
the "good" child cursor, the plan also shows "statistics feedback used for
this statement". Since all of the plans have the same plan hash value, I
can not use baselines (or can I).
Any suggestions on how to force Oracle to always use the "good" child
cursor.
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
------------------------------
This email has been scanned for spam and viruses. Click here
<https://attseg.cloud-protect.net/index01.php?mod_id=11&mod_option=logitem&mail_id=1572546192-5KRkM39NwgMG&r_address=jbeckstrom%40gcrta.org&report=1>
to report this email as spam.
