Re: Force specific plan to be used

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.
>
>

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