Solaris Systems Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
==== The following tutorial shows how to manually implement a sql profile. ====

1. Use the following [[sql_plans_history|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;
  /
Solaris Systems Wiki

User Tools

Site Tools

Page Tools
