Re: Force specific plan to be used

(adding oracle-l back in to the thread)

I ran a diff and yeah the predicate placement was exactly the same ...

It was impossible for me to read the heavily wrapped sqlid_v_sql_output file, so I don't know what the actual CPU/response times (and execution counts) were for the different child cursors.

As Jonathan said, maybe you can inject the MONITOR or GATHER_PLAN_STATISTICS hint to the query (or even run it manually in hopes of getting the bad plan). This way we'd see where all this extra time is going then.

If you can't use these hints, you can also use my asqlmon.sql on this SQL_ID (assuming that these child cursors have been executed within your ASH time range). This won't require enabling any monitoring or extra stats collection:

   -
   https://blog.tanelpoder.com/2013/03/17/asqlmon-sql-sql-monitoring-like-execution-plan-line-level-drilldown-into-sql-response-time/

Another quick check would be to see if there is any difference in the SQL Optimizer environment stored in the child cursors. Since the V$SQL_OPTIMIZER_ENV only shows documented optimizer environment parameters, you'd need to query the underlying *x$kqlfsqce* table:

• https://github.com/tanelpoder/tpt-oracle/blob/master/sqlopt.sql

Tanel

On Fri, Nov 1, 2019 at 7:54 AM Jeffrey Beckstrom <jbeckstrom_at_gcrta.org> wrote:

> I have attached some more information.
> - sql used to extract from v$sql
> - output of sga_sqlid_v_sql
> - child 0 and child 1 explain plans from the cursor cache
>
> The only obvious difference is child 1 has statistics feedback message in
> the plan - that is all also the faster plan.
> >>> Tanel Poder <tanel_at_tanelpoder.com> 10/31/19 11:29 PM >>>
> There a number of ways to demo this, but here's one example. It doesn't
> try to mimic the OP scenario (I have different SQL statements) and the
> predicate doesn't move to a different location, but shows that *predicate
> section* difference doesn't change the plan hash value:
>
> SQL> SELECT COUNT(*) FROM t WHERE owner LIKE 'SYS' AND object_id = 5;
>
> COUNT(*)
> ----------
> 1
>
> Plan hash value: *2079104444*
>
> ----------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> ----------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> | 1 | SORT AGGREGATE | | 1 | 1 |
> |* 2 | INDEX RANGE SCAN| I | 1 | 1 |
> ----------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> * 2 - access("OWNER"='SYS' AND "OBJECT_ID"=5)*
>
>
> SQL> SELECT COUNT(*) FROM t WHERE owner LIKE 'SYS%' AND object_id = 5;
>
> COUNT(*)
> ----------
> 1
>
>
> Plan hash value: *2079104444*
>
> ----------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows |
> ----------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | |
> | 1 | SORT AGGREGATE | | 1 | 1 |
> |* 2 | INDEX RANGE SCAN| I | 1 | 1 |
> ----------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> * 2 - access("OWNER" LIKE 'SYS%' AND "OBJECT_ID"=5)*
> *filter(("OBJECT_ID"=5 AND "OWNER" LIKE 'SYS%'))*
>
>
> Same plan hash value, regardless of predicate difference. In fact the plan
> hash value doesn't include the SCHEMA name either, so as long as the object
> name part is the same, plan hash value will be the same (but this
> particular plan may refer to a different schema - if some user/app has
> changed the current_schema - as Rakesh mentioned in another thread).
>
> Tanel.
>
>
> On Thu, Oct 31, 2019 at 11:17 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:
>
>> The OP said that the plan *hash value* didn't change, but since the
>> predicate placement doesn't affect the hash value, it's worth confirming on
>> which plan lines the predicates are applied.
>>
>> You'd just make sure that the predicates in both plans (with the same
>> plan hash value) are on the same lines, "operation id"-s as highlighted
>> below:
>>
>> SELECT SUM(order_total) FROM soe.orders WHERE order_id = 5
>>
>> Plan hash value: 1761615243
>>
>> -------------------------------------------------------------------
>> | Id | Operation | Name | Starts | E-Rows |
>> -------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 1 | |
>> | 1 | SORT AGGREGATE | | 1 | 1 |
>> | 2 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 1 |
>> |* 3 | INDEX UNIQUE SCAN | ORDER_PK | 1 | 1 |
>> -------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>> * 3 - access("ORDER_ID"=5)*
>>
>>
>> Tanel.
>>
>> On Thu, Oct 31, 2019 at 10:39 PM Chris Taylor <
>> christopherdtaylor1994_at_gmail.com> wrote:
>>
>>> But wouldn't that show up in the plan? I must be missing something.
>>>
>>> How can you tell by examining the predicate section where the filtering
>>> is occurring?
>>>
>>> Chris
>>>
>>> On Thu, Oct 31, 2019, 8:35 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:
>>>
>>>> Even if the plan hash values are the same, go still ahead and compare
>>>> the predicate sections of the good vs bad child cursors.
>>>>
>>>> Predicate existence (or placement) is not part of the plan hash value -
>>>> so how early you're filtering the rows may differ.
>>>>
>>>> Tanel Poder
>>>> https://blog.tanelpoder.com/seminar
>>>>
>>>> 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
>>>>>
>>>>>

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