2 Interesting Questions regarding 12.1 dbms_sqldiag_internal.i_create_patch (not 12.2)
Date: Mon, 13 Sep 2021 13:17:10 -0400
Message-ID: <CAP79kiTka8hFh5upkSSYuRBmCA_Zm10=P9eefZvKqEGuXj3zLQ_at_mail.gmail.com>
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-lReceived on Mon Sep 13 2021 - 19:17:10 CEST