Solaris Systems Wiki

1. Use the following script to get the best execution plan ID for a SQL ID.

2. Obtain the sql plan hints:

select * from table(dbms_xplan.display_awr('2tgrx1vgdw0t9',format => 'ADVANCED'));

or adding also plan_hash_value value:

select * from table(dbms_xplan.display_awr('9tk35vcp7znns',plan_hash_value => '2586930822', format => 'ADVANCED'));

3. Create sql profiles for sql_id using the above hints:

DECLARE
SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '2tgrx1vgdw0t9';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
SQL_TEXT => SQL_FTEXT,
PROFILE => SQLPROF_ATTR(q'^IGNORE_OPTIM_EMBEDDED_HINTS^',
q'^OPTIMIZER_FEATURES_ENABLE('11.2.0.4')^',
q'^DB_VERSION('11.2.0.4')^',
q'^OPT_PARAM('_optimizer_max_permutations' 500)^',
q'^OPT_PARAM('_always_semi_join' 'off')^',
q'^OPT_PARAM('_partition_view_enabled' 'false')^',
q'^OPT_PARAM('_b_tree_bitmap_plans' 'false')^',
q'^OPT_PARAM('query_rewrite_enabled' 'false')^',
q'^OPT_PARAM('_new_initial_join_orders' 'false')^',
q'^OPT_PARAM('optimizer_dynamic_sampling' 1)^',
q'^OPT_PARAM('_optimizer_cost_based_transformation' 'off')^',
q'^OPT_PARAM('_optimizer_use_feedback' 'false')^',
q'^OPT_PARAM('optimizer_index_cost_adj' 1)^',
q'^OPT_PARAM('_fix_control' '12555499:0')^',
q'^ALL_ROWS^',
q'^OUTLINE_LEAF(@"SEL$1")^',
q'^INDEX_RS_ASC(@"SEL$1" "T11"@"SEL$1" ("S_LOY_MEMBER"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T13"@"SEL$1" ("S_ORG_EXT"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T7"@"SEL$1" ("S_CONTACT"."PAR_ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T3"@"SEL$1" ("S_ORG_EXT"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T20"@"SEL$1" ("S_LOY_TXN"."MEMBER_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("S_LOY_PROGRAM"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("S_LOY_PTR_ZONE"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T17"@"SEL$1" ("S_LOY_PTR_ZONE"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T10"@"SEL$1" ("S_LOY_PROMO"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T12"@"SEL$1" ("S_EMP_PER"."PAR_ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T16"@"SEL$1" ("S_LOY_ATTRDEFN"."ROW_ID"))^',  
q'^INDEX_RS_ASC(@"SEL$1" "T9"@"SEL$1" ("S_USER"."PAR_ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T15"@"SEL$1" ("S_USER"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T19"@"SEL$1" ("S_USER"."PAR_ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T5"@"SEL$1" ("S_LOY_TXN_X"."PAR_ROW_ID" "S_LOY_TXN_X"."CONFLICT_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T4"@"SEL$1" ("S_LOY_MEM_VCHR"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T6"@"SEL$1" ("S_PROD_INT"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T18"@"SEL$1" ("S_LOY_MEMBER"."ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T8"@"SEL$1" ("S_ORG_EXT"."PAR_ROW_ID"))^',
q'^INDEX_RS_ASC(@"SEL$1" "T14"@"SEL$1" ("S_PROD_INT"."ROW_ID"))^',
q'^LEADING(@"SEL$1" "T11"@"SEL$1" "T13"@"SEL$1" "T7"@"SEL$1" "T3"@"SEL$1" "T20"@"SEL$1" "T2"@"SEL$1"
      "T1"@"SEL$1" "T17"@"SEL$1" "T10"@"SEL$1" "T12"@"SEL$1" "T16"@"SEL$1" "T9"@"SEL$1" "T15"@"SEL$1" 
"T19"@"SEL$1"
      "T5"@"SEL$1" "T4"@"SEL$1" "T6"@"SEL$1" "T18"@"SEL$1" "T8"@"SEL$1" "T14"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T13"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T7"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T3"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T20"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T2"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T1"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T17"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T10"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T12"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T16"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T9"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T15"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T19"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T5"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T4"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T6"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T18"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T8"@"SEL$1")^',
q'^USE_NL(@"SEL$1" "T14"@"SEL$1")^'),
NAME => 'PROFILE_4_SQLIDDUMMY',
REPLACE => TRUE,
FORCE_MATCH => TRUE
);
END;
/