Re: Influencing execution plan via SQL Profiles

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 18 Nov 2016 18:56:04 +0000
Message-ID: <LO1P123MB1252331F96376FDAAC2D603DA5B00_at_LO1P123MB1252.GBRP123.PROD.OUTLOOK.COM>

One option would be to use the dbms_sqldiag_internal "sql_patch" procedure described at: https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Hameed, Amir <Amir.Hameed_at_xerox.com> Sent: 18 November 2016 18:01:19
To: oracle-l_at_freelists.org
Subject: Influencing execution plan via SQL Profiles

Hi,
I am trying to figure out how to remove/nullify existing hints of SQL statement and insert different hints without modifying the code:

Original Statement:
UPDATE
    SELECT /*+ leading(aeh,xet,xte) use_hash(xet,xec,xeca) use_nl(xte)

     swap_join_inputs(xet) swap_join_inputs(xec) swap_join_inputs(xeca) */
     aeh.program_update_date -- added hint per performance change 7259699


           ,aeh.program_id

           ,aeh.request_id

           ,aeh.gl_transfer_date

           ,aeh.gl_transfer_status_code


           ,aeh.group_id

TO

Modified statement:
UPDATE
    SELECT /*+ full(xte) parallel(xte,8) */

     aeh.program_update_date -- added hint per performance change 7259699


           ,aeh.program_id

           ,aeh.request_id

           ,aeh.gl_transfer_date

           ,aeh.gl_transfer_status_code


           ,aeh.group_id


This is a packaged application and therefore, it is not possible for us to change the code at the moment.

Thanks,
Amir

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 18 2016 - 19:56:04 CET