Re: Force specific plan to be used

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_at_gcrta.org> 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.
> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 10/31/19 2:22 PM >>>
> So, to be clear, the all the cursors have the same plan ? (Both good and
> 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_at_gcrta.org>
> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l