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@xxxxxxxxx>
Sent: 16/06/2016 18:23
To: Franck Pachot<mailto:franck@xxxxxxxxxx>
Cc: ORACLE-L<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx> 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@xxxxxxxxx> 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(@"SEL$FF74214E")]',
q'[MERGE(@"SEL$025E71EF")]',
q'[OUTLINE_LEAF(@"SEL$E2FD130C")]',
q'[MERGE(@"SEL$58A6D7F6")]',
q'[OUTLINE_LEAF(@"INS$1")]',
q'[OUTLINE(@"SEL$3")]',
q'[OUTLINE(@"SEL$025E71EF")]',
q'[MERGE(@"SEL$9834E3F4")]',
q'[OUTLINE(@"SEL$7")]',
q'[OUTLINE(@"SEL$58A6D7F6")]',
q'[MERGE(@"SEL$1")]',
q'[OUTLINE(@"SEL$6")]',
q'[OUTLINE(@"SEL$9834E3F4")]',
q'[MERGE(@"SEL$4")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$1")]',
q'[OUTLINE(@"SEL$5")]',
q'[OUTLINE(@"SEL$4")]',
q'[FULL(@"INS$1" "I$_86497800_2"@"INS$1")]',
q'[NO_ACCESS(@"SEL$E2FD130C" "C"@"SEL$2")]',
q'[INDEX_RS_ASC(@"SEL$E2FD130C" "T"@"SEL$1"
("W_AR_BALANCE_F"."INTEGRATION_ID" "W_AR_BALANCE_F"."DATASOURCE_NUM_ID"))]',
q'[LEADING(@"SEL$E2FD130C" "C"@"SEL$2" "T"@"SEL$1")]',
q'[USE_NL(@"SEL$E2FD130C" "T"@"SEL$1")]',
q'[BITMAP_TREE(@"SEL$FF74214E" "PREV"@"SEL$4"
AND(("W_MCAL_PERIOD_D"."W_CURRENT_MCAL_PERIOD_CODE")))]',
q'[BITMAP_TREE(@"SEL$FF74214E" "BAL"@"SEL$4"
AND(("W_AR_BALANCE_F"."BALANCE_DT_WID")
("W_AR_BALANCE_F"."MCAL_CAL_WID")))]',
q'[BITMAP_TREE(@"SEL$FF74214E" "CURR"@"SEL$5"
AND(("W_MCAL_PERIOD_D"."W_CURRENT_MCAL_PERIOD_CODE")))]',
q'[LEADING(@"SEL$FF74214E" "PREV"@"SEL$4" "BAL"@"SEL$4" "CURR"@"SEL$5")]',
q'[USE_NL(@"SEL$FF74214E" "BAL"@"SEL$4")]',
q'[NLJ_BATCHING(@"SEL$FF74214E" "BAL"@"SEL$4")]',
q'[USE_HASH(@"SEL$FF74214E" "CURR"@"SEL$5")]',
q'[SWAP_JOIN_INPUTS(@"SEL$FF74214E" "CURR"@"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;
/
