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

  • From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
  • To: Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
  • Date: Mon, 13 Sep 2021 14:27:42 -0400

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@xxxxxxxxx>
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(@"SEL$10" "SRCL")'



În lun., 13 sept. 2021 la 20:17, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> 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@SEL$10
  44 - SEL$63682743   / SRCL@SEL$10

So how I would hint this sql using the SQL Patch?

Would it be something like:

HINT_TEXT=>'FULL(@SEL$63682743 SRCL)'

Chris




Other related posts: