RE: SQL Profile in Oracle Glossary - is this actually correct?
Date: Thu, 16 Jun 2016 18:43:56 +0100
Message-ID: <DUB408-EAS71D602A4769C611A227AEBA1560_at_phx.gbl>
A sql profile is just one mechanism to apply a set of hints.
Whether that set of hints is a bunch of cardinality adjustments from the sql tuning advisor, or a set of outline hints from an execution plan via coe script or any other, or any other set of hints that you happened to cobble together, doesn't matter. It's still a sql profile.
I believe the documentation merely reflects the original intention/design of the sql profile mechanism.
SQL baselines would, officially, be the preferred mechanism of trying to force a specific plan.
Sent from my Windows Phone
From: Chris Taylor<mailto:christopherdtaylor1994_at_gmail.com> Sent: 16/06/2016 18:23
To: Franck Pachot<mailto:franck_at_pachot.net> Cc: ORACLE-L<mailto:oracle-l_at_freelists.org> Subject: Re: SQL Profile in Oracle Glossary - is this actually correct?
I guess I don't understand that when the profile stores an outline with the exact order of operations for the execution to follow.
OR another way to put it, how can it be just "estimations" when the profile script shows an outline being added for the query? (And there are no OPT_ESTIMATE instructions in the profile?
Chris
On Thu, Jun 16, 2016 at 12:16 PM, Franck Pachot <franck_at_pachot.net> wrote:
> Hi,
> SQL Profile stores hints to adjust estimations, not to fix a plan. Here is
> how I query those hints:
> http://blog.dbi-services.com/oracle-sql-profiles-check-what-they-do-before-accepting-them-blindly/
> Regards
> Franck
>
> Le jeu. 16 juin 2016 à 18:58, Chris Taylor <
> christopherdtaylor1994_at_gmail.com> a écrit :
>
>> *SQL profile*
>>
>> *A set of auxiliary information built during automatic tuning of a SQL
>> statement. A SQL profile is to a SQL statement what statistics are to a
>> table. The optimizer can use SQL profiles to improve cardinality and
>> selectivity estimates, which in turn leads the optimizer to select better
>> plans.*
>>
>>
>>
>> If you export a SQL Profile, or use the coe_xfr_sql_profile.sql to
>> generate a profile script, you will see the profile actually contains a
>> PLAN, much like an outline. If you use force matching when creating the
>> profile, then any sql_id with a matching signature will also use the
>> profile, and thus the plan.
>>
>>
>> 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?
>>
>>
>>
>> Excerpt from a SQL Profile I extracted:
>> ----------------------------------------------------
>>
>> h := SYS.SQLPROF_ATTR(
>> q'[BEGIN_OUTLINE_DATA]',
>> q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
>> q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
>> q'[DB_VERSION('11.2.0.4')]',
>> q'[ALL_ROWS]',
>> q'[OUTLINE_LEAF(_at_"SEL$FF74214E")]',
>> q'[MERGE(_at_"SEL$025E71EF")]',
>> q'[OUTLINE_LEAF(_at_"SEL$E2FD130C")]',
>> q'[MERGE(_at_"SEL$58A6D7F6")]',
>> q'[OUTLINE_LEAF(_at_"INS$1")]',
>> q'[OUTLINE(_at_"SEL$3")]',
>> q'[OUTLINE(_at_"SEL$025E71EF")]',
>> q'[MERGE(_at_"SEL$9834E3F4")]',
>> q'[OUTLINE(_at_"SEL$7")]',
>> q'[OUTLINE(_at_"SEL$58A6D7F6")]',
>> q'[MERGE(_at_"SEL$1")]',
>> q'[OUTLINE(_at_"SEL$6")]',
>> q'[OUTLINE(_at_"SEL$9834E3F4")]',
>> q'[MERGE(_at_"SEL$4")]',
>> q'[OUTLINE(_at_"SEL$2")]',
>> q'[OUTLINE(_at_"SEL$1")]',
>> q'[OUTLINE(_at_"SEL$5")]',
>> q'[OUTLINE(_at_"SEL$4")]',
>> q'[FULL(_at_"INS$1" "I$_86497800_2"_at_"INS$1")]',
>> q'[NO_ACCESS(_at_"SEL$E2FD130C" "C"_at_"SEL$2")]',
>> q'[INDEX_RS_ASC(_at_"SEL$E2FD130C" "T"_at_"SEL$1"
>> ("W_AR_BALANCE_F"."INTEGRATION_ID" "W_AR_BALANCE_F"."DATASOURCE_NUM_ID"))]',
>> q'[LEADING(_at_"SEL$E2FD130C" "C"_at_"SEL$2" "T"@"SEL$1")]',
>> q'[USE_NL(_at_"SEL$E2FD130C" "T"_at_"SEL$1")]',
>> q'[BITMAP_TREE(_at_"SEL$FF74214E" "PREV"_at_"SEL$4"
>> AND(("W_MCAL_PERIOD_D"."W_CURRENT_MCAL_PERIOD_CODE")))]',
>> q'[BITMAP_TREE(_at_"SEL$FF74214E" "BAL"_at_"SEL$4"
>> AND(("W_AR_BALANCE_F"."BALANCE_DT_WID")
>> ("W_AR_BALANCE_F"."MCAL_CAL_WID")))]',
>> q'[BITMAP_TREE(_at_"SEL$FF74214E" "CURR"_at_"SEL$5"
>> AND(("W_MCAL_PERIOD_D"."W_CURRENT_MCAL_PERIOD_CODE")))]',
>> q'[LEADING(_at_"SEL$FF74214E" "PREV"_at_"SEL$4" "BAL"@"SEL$4" "CURR"@"SEL$5")]',
>> q'[USE_NL(_at_"SEL$FF74214E" "BAL"_at_"SEL$4")]',
>> q'[NLJ_BATCHING(_at_"SEL$FF74214E" "BAL"_at_"SEL$4")]',
>> q'[USE_HASH(_at_"SEL$FF74214E" "CURR"_at_"SEL$5")]',
>> q'[SWAP_JOIN_INPUTS(_at_"SEL$FF74214E" "CURR"_at_"SEL$5")]',
>> q'[END_OUTLINE_DATA]');
>> :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
>> :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
>> DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
>> sql_text => sql_txt,
>> profile => h,
>> name => 'coe_2ka7m2umpaq1t_4225425333',
>> description => 'coe 2ka7m2umpaq1t 4225425333 '||:signature||'
>> '||:signaturef||'',
>> category => 'DEFAULT',
>> validate => TRUE,
>> replace => TRUE,
>> force_match => TRUE /* TRUE:FORCE (match even when different literals in
>> SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
>> DBMS_LOB.FREETEMPORARY(sql_txt);
>> END;
>> /
>>
>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 16 2016 - 19:43:56 CEST