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

Ah, very good points. You know, I didn't know what to exactly call that section - I kew it was before the outline section technically but I was like "the watchamacallit section" - which I thought was less helpful :) Thank you for giving me the right wording to use for that section.

Yeah I've got access to the outline section - the problem though is that we switch a table out from under a view (used by the query) nightly. So one table in the outline actually changes daily :/

That bit about the changing the execution plan in such a way that it removes that block alias , or changes it around, is somewhat annoying but makes sense.

Also I learned today that QB_NAME hints get overwritten by system generated names again in certain transformations which was driving me a bit bonkers!

On the brighter side, I learned a lot more about DBMS_SQLDIAG and DBMS_SQLDIAG_INTERNAL today.....

begin
dbms_sqldiag_internal.I_CREATE_PATCH(

                    SQL_ID=>'xxxxxxxxxxx',
                    HINT_TEXT=>'FULL(_at_"SEL$10" "SRCL")',
                    NAME => 'APP_NAME_PATCH1',
                    DESCRIPTION => 'SQLPatch for APP_NAME_REDACTED',
                    CATEGORY  => 'DEFAULT',
                    VALIDATE => TRUE);

(Though I know upon upgrade to 12.2 , we'll have to redo that one if we still need it as the underlying table has a new column I think called CREATOR) Chris

On Mon, Sep 13, 2021 at 2:29 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

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