Stefan,
The drawback to SQL Patches is that they're limited to 500 characters - which
is sufficient for many cases, or as a temporary fix, or for the cases where
you're not trying to fix the path but want (e.g.) to fix a degree of
parallelism or disable a particular optimizer feature for a specific query.
My preferred method is to hint the problem SQL to the path I want then capture
a proper baseline that connects the unhinted SQL to the plan for the hinted SQL
using one of the "load baseline from cursor cache" procedures that's documented
in the manual.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 17 June 2016 08:17
To: ORACLE-L; christopherdtaylor1994@xxxxxxxxx
Subject: Re: SQL Profile in Oracle Glossary - is this actually correct?
Hey Chris,
please use SQL patches if you want to apply a full set of hints. This is what
they are made for and they are also license free for Oracle EE:
* https://blogs.oracle.com/optimizer/entry/how_can_i_hint_a
* https://blogs.oracle.com/optimizer/entry/additional_information_on_sql_patches
There are also scripts to generate the PL/SQL call, e.g. from Kerry Osborne:
http://kerryosborne.oracle-guy.com/scripts/create_sql_patch.sql
SQL profiles are initially designed to adjust cardinality (OPT_ESTIMATE) for
execution plan steps.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Chris Taylor <christopherdtaylor1994@xxxxxxxxx> hat am 16. Juni 2016 um 18:57
geschrieben:
When a profile exists, I cannot see how the optimizer will "select better
plans", instead the optimizer selects the plan in the profile.
Am I mistaken?
