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

Hi Chris,

I would use baselines instead of SQL*Patch. With the baseline, you can completely rewrite the SQL and associate the new plan.

Regards

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

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com