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*
("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")))]',
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE); DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
END;
/
Date: Thu, 16 Jun 2016 11:57:33 -0500
Message-ID: <CAP79kiQ8ZzLpBCmPGGjhgDFt6TmPkO0WSqDNrHU6RYcNyK1M4w_at_mail.gmail.com>
*SQL profile*
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 inSQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); DBMS_LOB.FREETEMPORARY(sql_txt);
END;
/
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 16 2016 - 18:57:33 CEST