Optimize OBIEE queries

Oracle Business Intelligence Enterprise Edition (OBIEE):
This is a business intelligence and analytics Oracle product that delivers dashboards, ad hoc queries, mobile analytic, notifications and alerts, enterprise and financial reporting.

The performance issue:

Some users start complaining about performance issues when they run dashboards used by Global HR users for Headcount verification.

These specific Headcount verification dashboards are executing queries hundreds of times per minutes and the goal is to spend very little time executing a given query—the queries should be small and fast. So the goal here is not increase the parse time (increase dynamic sampling will give the optimizer more time to hard parse the query). First thought, SQL Profiles makes more sense in this situation.

So I started creating SQL profiles for given queries, every sql profile improve the performance of every sql by 99%. But each time a different SQL Text / SQL ID querying the same tables, joining the same tables but the selected column orders is different and run so after creating several SQL Profiles for each different query, a new and different SQL Text / SQL ID is created by the dashboards.
A different tuning approach should be considered as too many and different SQL Text / SQL ID are executed on the same tables, but only selected fields are slightly different.
Here comes the idea to analyze the SQL Profiles, see what are the similar hints used for those queries;

1- First, identify the sql profile names;

select * from dba_sql_profiles order by created desc

2- Query the tables where the hints belonging to those SQL profiles are stored.

    SELECT extractValue(value(h), '.') AS hint  
      FROM sys.sqlobj$data od,  
           sys.sqlobj$ so,  
           table(xmlsequence(extract(xmltype(od.comp_data),  
                                     '/outline_data/hint'))) h  
     WHERE so.name in ('SYS_SQLPROF_014db970c3360003',  
                       'SYS_SQLPROF_014db915db320002',  
                       'SYS_SQLPROF_014db8fe7f1a0001',  
                       'SYS_SQLPROF_014db8fdb16c0000')  
       AND so.signature = od.signature  
       AND so.category = od.category  
       AND so.obj_type = od.obj_type  
       AND so.plan_id = od.plan_id;

PARALLEL  
OPT_ESTIMATE(@"SEL$2", TABLE, "T535616"@"SEL$2", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$2", JOIN, ("T535616"@"SEL$2", "T270424"@"SEL$2"), SCALE_ROWS=7.408188854)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T270424"@"SEL$1", SCALE_ROWS=2.290482666)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.193661407e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1"), SCALE_ROWS=3.300193673)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T592116"@"SEL$1", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.869319818e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.397368914e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.21434751e+12)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.703349689e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.41412714e+12)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.834422)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=5693.303256)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.711542)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
INDEX_STATS("BIAPPS"."W_INT_ORG_DH", "W_INT_ORG_DH_M13", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M18", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M15", scale, blocks=8 index_rows=0)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M5", scale, blocks=1 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M22", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M9", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M27", scale, blocks=64 index_rows=25)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M29", scale, blocks=56 index_rows=1)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M10", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M5", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M34", scale, blocks=56 index_rows=1)  
OPTIMIZER_FEATURES_ENABLE(default)  
PARALLEL  
PARALLEL  
OPT_ESTIMATE(@"SEL$2", TABLE, "T535616"@"SEL$2", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$2", JOIN, ("T535616"@"SEL$2", "T270424"@"SEL$2"), SCALE_ROWS=7.408188854)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T270424"@"SEL$1", SCALE_ROWS=2.290482666)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.193661407e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1"), SCALE_ROWS=3.300193673)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T592116"@"SEL$1", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.869319818e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.397368914e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.21434751e+12)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.703349689e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.41412714e+12)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.834422)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=5693.303256)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.711542)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
INDEX_STATS("BIAPPS"."W_INT_ORG_DH", "W_INT_ORG_DH_M13", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M18", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M15", scale, blocks=8 index_rows=0)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M5", scale, blocks=1 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M22", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M9", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M27", scale, blocks=64 index_rows=25)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M29", scale, blocks=56 index_rows=1)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M10", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M5", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M34", scale, blocks=56 index_rows=1)  
FIRST_ROWS  
OPTIMIZER_FEATURES_ENABLE(default)  
PARALLEL  
OPT_ESTIMATE(@"SEL$2", TABLE, "T535616"@"SEL$2", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$2", JOIN, ("T535616"@"SEL$2", "T270424"@"SEL$2"), SCALE_ROWS=7.408188854)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T270424"@"SEL$1", SCALE_ROWS=2.290482666)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_M1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.193661407e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1"), SCALE_ROWS=3.300193673)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=247613.6306)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T32660"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_EMPLOYEE_D_P1", SCALE_ROWS=26269077.6)  
OPT_ESTIMATE(@"SEL$1", TABLE, "T592116"@"SEL$1", SCALE_ROWS=20.04008952)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.869319818e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.397368914e+15)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=4.21434751e+12)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.703349689e+14)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.595320987e+16)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=3.41412714e+12)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.834422)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=5693.303256)  
OPT_ESTIMATE(@"SEL$1", NLJ_INDEX_FILTER, "T264928"@"SEL$1", ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T535616"@"SEL$1", "T312323"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), "W_JOB_D_P1", SCALE_ROWS=1311.711542)  
OPT_ESTIMATE(@"SEL$1", JOIN, ("T592116"@"SEL$1", "T270424"@"SEL$1", "T264890"@"SEL$1", "T32660"@"SEL$1", "T264928"@"SEL$1", "T535616"@"SEL$1", "T334016"@"SEL$1", "T312323"@"SEL$1", "T510692"@"SEL$1", "T623952"@"SEL$1", "T623980"@"SEL$1"), SCALE_ROWS=1.173030138e+14)  
INDEX_STATS("BIAPPS"."W_INT_ORG_DH", "W_INT_ORG_DH_M13", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M18", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M15", scale, blocks=8 index_rows=0)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M5", scale, blocks=1 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M22", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M9", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M27", scale, blocks=64 index_rows=25)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M29", scale, blocks=56 index_rows=1)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M10", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M5", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M34", scale, blocks=56 index_rows=1)  
OPTIMIZER_FEATURES_ENABLE(default)

For the above results, I highlight the index statistics;

INDEX_STATS("BIAPPS"."W_INT_ORG_DH", "W_INT_ORG_DH_M13", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M18", scale, blocks=8 index_rows=1)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M15", scale, blocks=8 index_rows=0)  
INDEX_STATS("BIAPPS"."W_INT_ORG_D", "W_INT_ORG_D_M5", scale, blocks=1 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M22", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M9", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M27", scale, blocks=64 index_rows=25)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M29", scale, blocks=56 index_rows=1)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M10", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M5", scale, blocks=56 index_rows=0)  
INDEX_STATS("BIAPPS"."W_EMPLOYEE_D", "W_EMPLOYEE_D_M34", scale, blocks=56 index_rows=1)

So we might have the index statistics for the above columns not updated.
Let’s confirm that by running SQL Tuning Advisor on a given SQL;

1- First create the tuning task:

    DECLARE  
         l_sql_tune_task_id  VARCHAR2(100);  
       BEGIN  
         l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (  
                                 sql_id      => 'a5jjhj5afqctp',  
                                 scope       => DBMS_SQLTUNE.scope_comprehensive,  
                                 time_limit  => 36000,  
                                 task_name   => 'a5jjhj5afqctp_tuning_task_1',  
                                description => 'Tuning task for statement a5jjhj5afqctp.');  
        DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);  
     END;   
    /

2- Run the tuning task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'a5jjhj5afqctp_tuning_task_1');

3- After execution we check the recommendations:

SET LINESIZE 200  
SET LONG 1000000 PAGESIZE 0;  
SELECT DBMS_SQLTUNE.report_tuning_task('a5jjhj5afqctp_tuning_task_1') AS recommendations FROM dual;

From the report, I extracted only the part of recommendations about index statistics because of the long report;

1- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M22" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M22', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
2- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M9" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M9', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
3- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M27" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M27', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
4- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M29" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M29', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
5- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M10" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M10', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
6- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M5" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M5', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
7- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_EMPLOYEE_D_M34" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_EMPLOYEE_D_M34', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.  
  
8- Statistics Finding  
---------------------  
  Optimizer statistics for index "BIAPPS"."W_INT_ORG_DH_M13" are stale.  
  
  Recommendation  
  --------------  
  - Consider collecting optimizer statistics for this index.  
    execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>  
            'W_INT_ORG_DH_M13', estimate_percent =>  
            DBMS_STATS.AUTO_SAMPLE_SIZE);  
  
  Rationale  
  ---------  
    The optimizer requires up-to-date statistics for the index in order to  
    select a good execution plan.

Here we see that SQL Tuning Advisor report confirm the findings from the sql profile hints;
We update the index statistics so the Oracle Optimizer can decide a good execution plan:

execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>'W_INT_ORG_DH_M13', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>'W_EMPLOYEE_D_M22', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>'W_EMPLOYEE_D_M9', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname =>'W_EMPLOYEE_D_M27', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname => 'W_EMPLOYEE_D_M29', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname => 'W_EMPLOYEE_D_M10', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);  
 execute dbms_stats.gather_index_stats(ownname => 'BIAPPS', indname => 'W_EMPLOYEE_D_M5', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

I have the user confirm that SQL queries are running faster after updating index statistics.

Hope it Helps!
Wissem

Menus

Article

Newsletter