RE: SQL Profile in Oracle Glossary - is this actually correct?
Date: Fri, 17 Jun 2016 07:54:44 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D9B7C_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Stefan Koehler [contact_at_soocs.de]
Sent: 17 June 2016 08:17
To: ORACLE-L; christopherdtaylor1994_at_gmail.com
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_at_gmail.com> 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?​
-- http://www.freelists.org/webpage/oracle-l †Ûiÿü0ÁúÞzX¬¶Ê+ƒü n– {ú+iÉ^Received on Fri Jun 17 2016 - 09:54:44 CEST