Franck's description is correct but, as Dominic has pointed out, the SQL
Profile mechanism has been subverted and bastardised - by CoE, no less - so
that many people now push outline information (effectively the equivalent of
SQL Plan Baselines) into the SQL Profile structure. It's a practice that I
really dislike because there's really no guarantee that one day the code to use
SQL Profiles won't change and cause problems with the fakes, or maybe some
detail of SQL Plan Baselines may need to be handled differently and the
difference won't be used for the fakes.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Chris Taylor [christopherdtaylor1994@xxxxxxxxx]
Sent: 16 June 2016 18:21
To: Franck Pachot
Cc: ORACLE-L
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<mailto: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<mailto: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;
/
