Re: 2 Interesting Questions regarding 12.1 dbms_sqldiag_internal.i_create_patch (not 12.2)

Chris,
1) as per the article referenced by Laurentiu Oprea

2) That's not the outline information, it's the Query Block / Object Alias information (format =>'alias'). If you can find the outline information you should be able to find the INDEX() - or index_rs_asc() hint for that access and modify it to a suitable FULL() hint. (I assume this isn't the 2nd table of a nested loop - or you might also need to find the USE_NL() and change it to a use_hash() hint etc...)

If you can't get at the outline, there's a good change that the hint you need is: FULL(_at_SEL$63682743 SRCL_at_SEL$10)

Bear in mind that if this makes the optimizer think the strategy it too expensive it may "obey" your hint by doing the calculation and then choose a complete different execution path that doesn't produce that query block, thus making the hint unusable.

Regards
Jonathan Lewis

On Mon, 13 Sept 2021 at 18:17, Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> Background: I'm looking over Maria Colgan's posts here:
>
> https://blogs.oracle.com/optimizer/post/using-sql-patch-to-add-hints-to-a-packaged-application
>
> https://blogs.oracle.com/optimizer/post/additional-information-on-sql-patches
>
> I have 2 questions:
> 1.) Can I assign "sql_text=>" to v_sql where I load v_sql from
> dba_hist_sqltext for a sql_id?
> I assume the answer should be yes?
>
> 2.) The patch I need to apply is in a terrible SUBSELECT thats part of a
> CTE.
>
> In the execution plan it looks like this:
>
> |* 43 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED |
> SPECIAL_REVERSED_CLAIM_LOG | 1 | 29 | 3 (0)| 00:00:01 |
> ROWID | ROWID |
> |* 44 | INDEX RANGE SCAN |
> SPECIAL_REVERSED_CLAIM_LOG_IDX | 1 | | 2 (0)| 00:00:01 |
> |
>
> Which is this in the outline information:
> 43 - SEL$63682743 / SRCL_at_SEL$10
> 44 - SEL$63682743 / SRCL_at_SEL$10
>
> So how I would hint this sql using the SQL Patch?
>
> Would it be something like:
>
> HINT_TEXT=>'FULL(_at_SEL$63682743 SRCL)'
>
> Chris
>
>
>
>

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