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

50-50 Chance and I'd knew I'd choose wrong lol

Thanks,

Chris

On Mon, Sep 13, 2021 at 2:14 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello,
>
> 1. Yes, You can find an example here:
> https://blog.dbi-services.com/sql-patch-another-way-to-change-the-plan-without-changing-the-code-1/
>
> 2. HINT_TEXT=>'FULL(_at_"SEL$10" "SRCL")'
>
>
>
> În lun., 13 sept. 2021 la 20:17, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> a scris:
>
>> 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