Re: Force specific plan to be used

I'll defer to the LIST but if its the same plan, you can rule out child cursors and anything related to stats. It's spinning on something else --- doing extra work for the longer execution times.

In 12.1.0.2 there was bugs with ALL_SYNONYMS as well but I don't see that in 12.2.0.1.

Can you get a 10046 trace a few times and see if you can get an execution with good perf and one with slow performance? In 12.1.0.2 this was easy as executing the query 2x in the same session would spin the 2nd one and pointed to optimizer_dynamic_features. Not sure if that would work here or not but you could try it.

Chris

On Thu, Oct 31, 2019 at 2:41 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> This is 12.2.0.1.
> >>> Chris Taylor <christopherdtaylor1994_at_gmail.com> 10/31/19 2:38 PM >>>
> OOOO USER_TABLES.
>
> Is this 12.1.0.2 ? Is OPTIMIZER_ADAPTIVE_FEATURES enabled? That's your
> problem. 99.999999999999% guaranteed if your on 12.1.0.2 with
> optimizer_adaptive_features.
>
> Run the query in a session with alter session set
> optimizer_adaptive_features=false and see if it doesn't run better.
>
> Chris
>
>
>
> On Thu, Oct 31, 2019 at 2:36 PM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org>
> wrote:
>
>> See below for more details. Same plan hash value but vastly different
>> elapsed time.
>>
>>
>> SQL_TEXT
>>
>> --------------------------------------------------------------------------------
>> SQL_PROFILE SQL_PLAN_BASELINE CHILD_NUMBER
>> ---------------------------- ---------------------------- ------------
>> PLAN_HASH_VALUE FIRST_LOAD_TIME LAST_ACTIVE_TIME EXECUTIONS end of fetch
>> --------------- ------------------- ------------------- ----------
>> ------------
>> DISK_READS BUFFER_GETS BUFF_PER_EXEC DISK_PER_EXEC BUFF_ROW ROWS_EXEC
>> ------------ ------------ ------------- ------------- ------------
>> ----------
>> HIT_RATIO SORTS_EXEC CPU_SEC CPU_SEC_PER_EXEC ELAPSED_SEC
>> ---------- ---------- ---------- ---------------- -----------
>> ELAPSED_SEC_PER_EXEC I I I I object id PROGRAM_LINE#
>> FORCE_MATCHING_SIGNATURE
>> -------------------- - - - - ---------- -------------
>> ------------------------
>> SELECT 1 x FROM user_tables u WHERE u.table_name='FERRYLINE' UNION SELECT
>> 1 x
>> FROM all_synonyms s INNER JOIN all_tables t ON
>> s.synonym_name=t.table_name AND
>> s.owner='PUBLIC' AND s.synonym_name=s.table_name WHERE
>> s.synonym_name='FERRYLINE' UNION SELECT 1 x FROM user_views WHERE
>> view_name =
>> 'FERRYLINE' UNION SELECT 1 x FROM all_synonyms s INNER JOIN all_views v ON
>> s.synonym_name = v.view_name AND s.owner = 'PUBLIC' AND s.synonym_name =
>> s.table_name WHERE s.synonym_name = 'FERRYLINE'
>> 0
>> 144319191 2019-10-31/13:01:22 10/31/2019 14:34:32 1 1
>> 3 2,497 2,497 3 2,497 1
>> 99.8798558 4 2.481887 2.481887 3.47002
>> 3.47002 N N Y Y 0 0 14299696840961403209
>>
>> SELECT 1 x FROM user_tables u WHERE u.table_name='FERRYLINE' UNION SELECT
>> 1 x
>> FROM all_synonyms s INNER JOIN all_tables t ON
>> s.synonym_name=t.table_name AND
>> s.owner='PUBLIC' AND s.synonym_name=s.table_name WHERE
>> s.synonym_name='FERRYLINE' UNION SELECT 1 x FROM user_views WHERE
>> view_name =
>> 'FERRYLINE' UNION SELECT 1 x FROM all_synonyms s INNER JOIN all_views v ON
>> s.synonym_name = v.view_name AND s.owner = 'PUBLIC' AND s.synonym_name =
>> s.table_name WHERE s.synonym_name = 'FERRYLINE'
>> 1
>> 144319191 2019-10-31/13:01:22 10/31/2019 14:20:09 7 7
>> 0 4,509 644 0 644 1
>> 100 4 3.2556 .465085714 6.323123
>> .903303286 N N N 0 0 14299696840961403209
>>
>> >>> "Jeffrey Beckstrom" <jbeckstrom_at_gcrta.org> 10/31/19 2:27 PM >>>
>> 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.
>>
>>
>
> ------------------------------
> 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=1572547145-FKseghWkBh3k&r_address=jbeckstrom%40gcrta.org&report=1>
> to report this email as spam.
>
>

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