Hi all,
I'm trying to tune the performance of the following flashback query:
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = ALL';
FOR test IN (SELECT /*+ MONITOR GATHER_PLAN_STATISTICS*/
id_alojamento
FROM swiadm.coverage_alojamento versions BETWEEN scn minvalue AND maxvalue
WHERE id_alojamento = 7237545)
LOOP
NULL;
END LOOP;
FOR x IN (SELECT plan_table_output
FROM TABLE(dbms_xplan.display_cursor(NULL,
NULL,
'ADVANCED')))
LOOP
dbms_output.put_line(x.plan_table_output);
END LOOP;
ROLLBACK;
END;
Output:
SQL_ID 0bh8uj1r5aqkv, child number 3
-------------------------------------
SELECT /*+ MONITOR GATHER_PLAN_STATISTICS*/ ID_ALOJAMENTO FROM
SWIADM.COVERAGE_ALOJAMENTO VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ID_ALOJAMENTO = 7237545
Plan hash value: 3946272050
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 62249 (100)| | | |
| 1 | VIEW | | 2 | 26 | | 62249 (1)| 00:00:03 | | |
| 2 | UNION-ALL | | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SYS_FBA_HIST_7132953 | 1 | 15 | | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | IDX_SYS_FBA_HIST_7132953_1 | 1 | | | 3 (0)| 00:00:01 | | |
| 5 | MERGE JOIN OUTER | | 1 | 57 | | 62244 (1)| 00:00:03 | | |
| 6 | SORT JOIN | | 1 | 18 | | 5 (20)| 00:00:01 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | COVERAGE_ALOJAMENTO | 1 | 18 | | 4 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | PK_COVERAGE_ALOJAMENTO | 1 | | | 3 (0)| 00:00:01 | | |
|* 9 | SORT JOIN | | 4426K| 164M| 440M| 62239 (1)| 00:00:03 | | |
|* 10 | TABLE ACCESS FULL | SYS_FBA_TCRV_7132953 | 4426K| 164M| | 17811 (1)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$38A72F44 / from$_subquery$_002@SEL$38A72F44
2 - SET$38A72F44
3 - SEL$38A72F45 / SYS_FBA_HIST_7132953@SEL$38A72F45
4 - SEL$38A72F45 / SYS_FBA_HIST_7132953@SEL$38A72F45
5 - SEL$53AC7191
7 - SEL$53AC7191 / T@SEL$38A72F46
8 - SEL$53AC7191 / T@SEL$38A72F46
10 - SEL$53AC7191 / SYS_FBA_TCRV_7132953@SEL$38A72F47
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$38A72F45")
OUTLINE_LEAF(@"SEL$53AC7191")
MERGE(@"SEL$38A72F47")
OUTLINE_LEAF(@"SET$38A72F44")
OUTLINE_LEAF(@"SEL$FDDBC965")
MERGE(@"SEL$38A72F44")
OUTLINE(@"SEL$38A72F46")
OUTLINE(@"SEL$38A72F47")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$38A72F44")
NO_ACCESS(@"SEL$FDDBC965" "from$_subquery$_002"@"SEL$38A72F44")
INDEX_RS_ASC(@"SEL$53AC7191" "T"@"SEL$38A72F46" ("COVERAGE_ALOJAMENTO"."ID_ALOJAMENTO" "COVERAGE_ALOJAMENTO"."TECHNOLOGY_CODE"))
FULL(@"SEL$53AC7191" "SYS_FBA_TCRV_7132953"@"SEL$38A72F47")
LEADING(@"SEL$53AC7191" "T"@"SEL$38A72F46" "SYS_FBA_TCRV_7132953"@"SEL$38A72F47")
USE_MERGE(@"SEL$53AC7191" "SYS_FBA_TCRV_7132953"@"SEL$38A72F47")
INDEX_RS_ASC(@"SEL$38A72F45" "SYS_FBA_HIST_7132953"@"SEL$38A72F45" ("SYS_FBA_HIST_7132953"."ID_ALOJAMENTO"
"SYS_FBA_HIST_7132953"."TECHNOLOGY_CODE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$38A72F45" "SYS_FBA_HIST_7132953"@"SEL$38A72F45")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("ENDSCN">11982583055495 AND "ENDSCN"<=11986449356221))
4 - access("ID_ALOJAMENTO"=7237545)
7 - filter(("VERSIONS_ENDSCN" IS NULL OR "VERSIONS_ENDSCN">11982583055495))
8 - access("T"."ID_ALOJAMENTO"=7237545)
9 - access("RID"=ROWIDTOCHAR("T".ROWID))
filter("RID"=ROWIDTOCHAR("T".ROWID))
10 - filter((("ENDSCN" IS NULL OR "ENDSCN">11986449356221) AND ("STARTSCN"<11986449356221 OR "STARTSCN" IS NULL)))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID_ALOJAMENTO"[NUMBER,22]
2 - STRDEF[22]
3 - "ID_ALOJAMENTO"[NUMBER,22]
4 - "SYS_FBA_HIST_7132953".ROWID[ROWID,10], "ID_ALOJAMENTO"[NUMBER,22]
5 - "T"."ID_ALOJAMENTO"[NUMBER,22]
6 - (#keys=1) ROWIDTOCHAR("T".ROWID)[18], "T"."ID_ALOJAMENTO"[NUMBER,22]
7 - "T".ROWID[ROWID,10], "T"."ID_ALOJAMENTO"[NUMBER,22], SYSDEF[22]
8 - "T".ROWID[ROWID,10], "T"."ID_ALOJAMENTO"[NUMBER,22], SYSDEF[28]
9 - (#keys=1) "RID"[VARCHAR2,4000]
10 - "RID"[VARCHAR2,4000]
Since table SYS_FBA_TCRV_7132953 has an index on RID, I'm not understanding why it's not being used.
During my tests the only thing that worked was setting "_optimizer_ignore_hints" = TRUE:
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET "_optimizer_ignore_hints" = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET STATISTICS_LEVEL = ALL';
FOR test IN (SELECT /*+ MONITOR GATHER_PLAN_STATISTICS*/
id_alojamento
FROM swiadm.coverage_alojamento versions BETWEEN scn minvalue AND maxvalue
WHERE id_alojamento = 7237545)
LOOP
NULL;
END LOOP;
FOR x IN (SELECT plan_table_output
FROM TABLE(dbms_xplan.display_cursor(NULL,
NULL,
'ADVANCED')))
LOOP
dbms_output.put_line(x.plan_table_output);
END LOOP;
ROLLBACK;
END;
Output:
SQL_ID 0bh8uj1r5aqkv, child number 1
-------------------------------------
SELECT /*+ MONITOR GATHER_PLAN_STATISTICS*/ ID_ALOJAMENTO FROM
SWIADM.COVERAGE_ALOJAMENTO VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ID_ALOJAMENTO = 7237545
Plan hash value: 2319111417
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| | | |
| 1 | VIEW | | 2 | 26 | 12 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
|* 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SYS_FBA_HIST_7132953 | 1 | 15 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | IDX_SYS_FBA_HIST_7132953_1 | 1 | | 3 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS OUTER | | 1 | 57 | 7 (0)| 00:00:01 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | COVERAGE_ALOJAMENTO | 1 | 18 | 4 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | PK_COVERAGE_ALOJAMENTO | 1 | | 3 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED | SYS_FBA_TCRV_7132953 | 1 | 39 | 3 (0)| 00:00:01 | | |
|* 9 | INDEX RANGE SCAN | SYS_FBA_TCRV_IDX1_7132953 | 1 | | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$D7C7ECD6 / from$_subquery$_002@SEL$D7C7ECD6
2 - SET$D7C7ECD6
3 - SEL$D7C7ECD7 / SYS_FBA_HIST_7132953@SEL$D7C7ECD7
4 - SEL$D7C7ECD7 / SYS_FBA_HIST_7132953@SEL$D7C7ECD7
5 - SEL$811A7CE8
6 - SEL$811A7CE8 / T@SEL$D7C7ECD8
7 - SEL$811A7CE8 / T@SEL$D7C7ECD8
8 - SEL$811A7CE8 / SYS_FBA_TCRV_7132953@SEL$D7C7ECD9
9 - SEL$811A7CE8 / SYS_FBA_TCRV_7132953@SEL$D7C7ECD9
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
OPT_PARAM('_optimizer_cost_based_transformation' 'off')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$D7C7ECD7")
OUTLINE_LEAF(@"SEL$811A7CE8")
MERGE(@"SEL$D7C7ECD9")
OUTLINE_LEAF(@"SET$D7C7ECD6")
OUTLINE_LEAF(@"SEL$F33BB9D7")
MERGE(@"SEL$D7C7ECD6")
OUTLINE(@"SEL$D7C7ECD8")
OUTLINE(@"SEL$D7C7ECD9")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$D7C7ECD6")
NO_ACCESS(@"SEL$F33BB9D7" "from$_subquery$_002"@"SEL$D7C7ECD6")
INDEX_RS_ASC(@"SEL$811A7CE8" "T"@"SEL$D7C7ECD8" ("COVERAGE_ALOJAMENTO"."ID_ALOJAMENTO" "COVERAGE_ALOJAMENTO"."TECHNOLOGY_CODE"))
INDEX_RS_ASC(@"SEL$811A7CE8" "SYS_FBA_TCRV_7132953"@"SEL$D7C7ECD9" ("SYS_FBA_TCRV_7132953"."RID"
"SYS_FBA_TCRV_7132953"."STARTSCN"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$811A7CE8" "SYS_FBA_TCRV_7132953"@"SEL$D7C7ECD9")
LEADING(@"SEL$811A7CE8" "T"@"SEL$D7C7ECD8" "SYS_FBA_TCRV_7132953"@"SEL$D7C7ECD9")
USE_NL(@"SEL$811A7CE8" "SYS_FBA_TCRV_7132953"@"SEL$D7C7ECD9")
INDEX_RS_ASC(@"SEL$D7C7ECD7" "SYS_FBA_HIST_7132953"@"SEL$D7C7ECD7" ("SYS_FBA_HIST_7132953"."ID_ALOJAMENTO"
"SYS_FBA_HIST_7132953"."TECHNOLOGY_CODE"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$D7C7ECD7" "SYS_FBA_HIST_7132953"@"SEL$D7C7ECD7")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("ENDSCN">11982583055495 AND "ENDSCN"<=11986448780821))
4 - access("ID_ALOJAMENTO"=7237545)
6 - filter(("VERSIONS_ENDSCN" IS NULL OR "VERSIONS_ENDSCN">11982583055495))
7 - access("T"."ID_ALOJAMENTO"=7237545)
8 - filter((("ENDSCN" IS NULL OR "ENDSCN">11986448780821) AND ("STARTSCN"<11986448780821 OR "STARTSCN" IS NULL)))
9 - access("RID"=ROWIDTOCHAR("T".ROWID))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "ID_ALOJAMENTO"[NUMBER,22]
2 - STRDEF[22]
3 - "ID_ALOJAMENTO"[NUMBER,22]
4 - "SYS_FBA_HIST_7132953".ROWID[ROWID,10], "ID_ALOJAMENTO"[NUMBER,22]
5 - "T".ROWID[ROWID,10], "T"."ID_ALOJAMENTO"[NUMBER,22], SYSDEF[22], "SYS_FBA_TCRV_7132953".ROWID[ROWID,10],
"RID"[VARCHAR2,4000], "STARTSCN"[NUMBER,22], "ENDSCN"[NUMBER,22]
6 - "T".ROWID[ROWID,10], "T"."ID_ALOJAMENTO"[NUMBER,22], SYSDEF[22]
7 - "T".ROWID[ROWID,10], "T"."ID_ALOJAMENTO"[NUMBER,22], SYSDEF[28]
9 - "SYS_FBA_TCRV_7132953".ROWID[ROWID,10], "STARTSCN"[NUMBER,22]
Can someone explain me this behavior?
Also, is there any hint I can use on the query to achieve the same result without setting this undocumented one.
Thanks in advance,
Manuel
Message was edited by: Manuel Vidigal