SQL Profile in Oracle Glossary - is this actually correct?

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Thu, 16 Jun 2016 11:57:33 -0500
Message-ID: <CAP79kiQ8ZzLpBCmPGGjhgDFt6TmPkO0WSqDNrHU6RYcNyK1M4w_at_mail.gmail.com>

*​​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

Received on Thu Jun 16 2016 - 18:57:33 CEST