Re: SQL Profile in Oracle Glossary - is this actually correct?

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-l