Oracle Version 10.2.0.3
I have been given an arbitary query written by some one and asked to tune it. Right now it takes 40 seconds to complete. The table has around 1.8 Million Rows. The query is :
SELECT /*+ USE_HASH(i,o)*/
i.schedule_set_daily_flt_id AS previous_flight_leg,
o.schedule_set_daily_flt_id AS onward_flight_leg,
(o.dep_datetime_local - i.arr_datetime_local) * 1440 AS ground_time,
i.data_owner_id, i.truncated_path AS prev_trunc_path,
o.truncated_path AS onw_trunc_path
FROM essalv1_qa.schedule_set_daily_flt i, essalv1_qa.schedule_set_daily_flt o
WHERE i.dst_station_id = o.dep_station_id
AND i.aircraft_type_id = o.aircraft_type_id
AND ( (i.flight_no = o.flight_no
AND (i.leg_sequence + 1) = o.leg_sequence
)
OR (i.flight_no <> i.onward_flight AND i.onward_flight = o.flight_no
)
)
AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 >= 20
AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 <= 1440
AND ((o.dep_datetime_local) - (i.arr_datetime_local)) >=
NVL (i.aircraft_rotation_layover, 0)
AND ((o.dep_datetime_local) - (i.arr_datetime_local)) <
NVL (i.aircraft_rotation_layover, 0)
+ 1
AND i.schedule_set_id = 2263
AND (i.next_leg_id IS NULL OR i.prev_leg_id IS NULL)
AND i.schedule_set_id = o.schedule_set_id
AND (o.next_leg_id IS NULL OR o.prev_leg_id IS NULL)
AND (i.ess_file_import_id = 5365 OR i.truncated_path = 1)
AND (o.ess_file_import_id = 5365 OR o.truncated_path = 1);
Explain Plan :
SQL> explain plan for
2 SELECT /*+ USE_HASH(i,o)*/
3 i.schedule_set_daily_flt_id AS previous_flight_leg,
4 o.schedule_set_daily_flt_id AS onward_flight_leg,
5 (o.dep_datetime_local - i.arr_datetime_local) * 1440 AS ground_time,
6 i.data_owner_id, i.truncated_path AS prev_trunc_path,
7 o.truncated_path AS onw_trunc_path
8 FROM essalv1_qa.schedule_set_daily_flt i, essalv1_qa.schedule_set_daily_flt o
9 WHERE i.dst_station_id = o.dep_station_id
10 AND i.aircraft_type_id = o.aircraft_type_id
11 AND ( (i.flight_no = o.flight_no
12 AND (i.leg_sequence + 1) = o.leg_sequence
13 )
14 OR (i.flight_no <> i.onward_flight AND i.onward_flight = o.flight_no
15 )
16 )
17 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 >= 20
18 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 <= 1440
19 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) >=
20 NVL (i.aircraft_rotation_layover, 0)
21 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) <
22 NVL (i.aircraft_rotation_layover, 0)
23 + 1
24 AND i.schedule_set_id = 2263
25 AND (i.next_leg_id IS NULL OR i.prev_leg_id IS NULL)
26 AND i.schedule_set_id = o.schedule_set_id
27 AND (o.next_leg_id IS NULL OR o.prev_leg_id IS NULL)
28 AND (i.ess_file_import_id = 5365 OR i.truncated_path = 1)
29 AND (o.ess_file_import_id = 5365 OR o.truncated_path = 1);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3301526158
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | | 36598 (1)| 00:07:20 |
|* 1 | HASH JOIN | | 1 | 117 | 1512K| 36598 (1)| 00:07:20 |
|* 2 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1236K| | 18220 (1)| 00:03:39 |
|* 3 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1442K| | 18220 (1)| 00:03:39 |
-----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"."DST_STATION_ID"="O"."DEP_STATION_ID" AND
"I"."AIRCRAFT_TYPE_ID"="O"."AIRCRAFT_TYPE_ID" AND
"I"."SCHEDULE_SET_ID"="O"."SCHEDULE_SET_ID")
filter(("I"."FLIGHT_NO"="O"."FLIGHT_NO" AND "O"."LEG_SEQUENCE"="I"."LEG_SEQUENCE"+1
OR "I"."ONWARD_FLIGHT"="O"."FLIGHT_NO" AND "I"."FLIGHT_NO"<>"I"."ONWARD_FLIGHT") AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440>=20 AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440<=1440 AND
"O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL">=NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
AND "O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL"<NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)+
1)
2 - filter(("O"."NEXT_LEG_ID" IS NULL OR "O"."PREV_LEG_ID" IS NULL) AND
"O"."SCHEDULE_SET_ID"=2263 AND ("O"."ESS_FILE_IMPORT_ID"=5365 OR "O"."TRUNCATED_PATH"=1))
3 - filter(("I"."NEXT_LEG_ID" IS NULL OR "I"."PREV_LEG_ID" IS NULL) AND
"I"."SCHEDULE_SET_ID"=2263 AND ("I"."ESS_FILE_IMPORT_ID"=5365 OR "I"."TRUNCATED_PATH"=1))
28 rows selected.
SQL> set autotrace traceonly arraysize 100
SQL>
SQL> SELECT /*+ USE_HASH(i,o)*/
2 i.schedule_set_daily_flt_id AS previous_flight_leg,
3 o.schedule_set_daily_flt_id AS onward_flight_leg,
4 (o.dep_datetime_local - i.arr_datetime_local) * 1440 AS ground_time,
5 i.data_owner_id, i.truncated_path AS prev_trunc_path,
6 o.truncated_path AS onw_trunc_path
7 FROM essalv1_qa.schedule_set_daily_flt i, essalv1_qa.schedule_set_daily_flt o
8 WHERE i.dst_station_id = o.dep_station_id
9 AND i.aircraft_type_id = o.aircraft_type_id
10 AND ( (i.flight_no = o.flight_no
11 AND (i.leg_sequence + 1) = o.leg_sequence
12 )
13 OR (i.flight_no <> i.onward_flight AND i.onward_flight = o.flight_no
14 )
15 )
16 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 >= 20
17 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 <= 1440
18 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) >=
19 NVL (i.aircraft_rotation_layover, 0)
20 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) <
21 NVL (i.aircraft_rotation_layover, 0)
22 + 1
23 AND i.schedule_set_id = 2263
24 AND (i.next_leg_id IS NULL OR i.prev_leg_id IS NULL)
25 AND i.schedule_set_id = o.schedule_set_id
26 AND (o.next_leg_id IS NULL OR o.prev_leg_id IS NULL)
27 AND (i.ess_file_import_id = 5365 OR i.truncated_path = 1)
28 AND (o.ess_file_import_id = 5365 OR o.truncated_path = 1);
16997 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3301526158
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | | 36598 (1)| 00:07:20 |
|* 1 | HASH JOIN | | 1 | 117 | 1512K| 36598 (1)| 00:07:20 |
|* 2 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1236K| | 18220 (1)| 00:03:39 |
|* 3 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1442K| | 18220 (1)| 00:03:39 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"."DST_STATION_ID"="O"."DEP_STATION_ID" AND
"I"."AIRCRAFT_TYPE_ID"="O"."AIRCRAFT_TYPE_ID" AND
"I"."SCHEDULE_SET_ID"="O"."SCHEDULE_SET_ID")
filter(("I"."FLIGHT_NO"="O"."FLIGHT_NO" AND "O"."LEG_SEQUENCE"="I"."LEG_SEQUENCE"+1
OR "I"."ONWARD_FLIGHT"="O"."FLIGHT_NO" AND "I"."FLIGHT_NO"<>"I"."ONWARD_FLIGHT") AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440>=20 AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440<=1440 AND
"O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL">=NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)
AND "O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL"<NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)+
1)
2 - filter(("O"."NEXT_LEG_ID" IS NULL OR "O"."PREV_LEG_ID" IS NULL) AND
"O"."SCHEDULE_SET_ID"=2263 AND ("O"."ESS_FILE_IMPORT_ID"=5365 OR "O"."TRUNCATED_PATH"=1))
3 - filter(("I"."NEXT_LEG_ID" IS NULL OR "I"."PREV_LEG_ID" IS NULL) AND
"I"."SCHEDULE_SET_ID"=2263 AND ("I"."ESS_FILE_IMPORT_ID"=5365 OR "I"."TRUNCATED_PATH"=1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
165243 consistent gets
139394 physical reads
0 redo size
339178 bytes sent via SQL*Net to client
2197 bytes received via SQL*Net from client
171 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16997 rows processed
SQL>
SQL> SELECT /*+ gather_plan_statistics */
2 i.schedule_set_daily_flt_id AS previous_flight_leg,
3 o.schedule_set_daily_flt_id AS onward_flight_leg,
4 (o.dep_datetime_local - i.arr_datetime_local) * 1440 AS ground_time,
5 i.data_owner_id, i.truncated_path AS prev_trunc_path,
6 o.truncated_path AS onw_trunc_path
7 FROM essalv1_qa.schedule_set_daily_flt i, essalv1_qa.schedule_set_daily_flt o
8 WHERE i.dst_station_id = o.dep_station_id
9 AND i.aircraft_type_id = o.aircraft_type_id
10 AND ( (i.flight_no = o.flight_no
11 AND (i.leg_sequence + 1) = o.leg_sequence
12 )
13 OR (i.flight_no <> i.onward_flight AND i.onward_flight = o.flight_no
14 )
15 )
16 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 >= 20
17 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) * 1440 <= 1440
18 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) >=
19 NVL (i.aircraft_rotation_layover, 0)
20 AND ((o.dep_datetime_local) - (i.arr_datetime_local)) <
21 NVL (i.aircraft_rotation_layover, 0)
22 + 1
23 AND i.schedule_set_id = 2263
24 AND (i.next_leg_id IS NULL OR i.prev_leg_id IS NULL)
25 AND i.schedule_set_id = o.schedule_set_id
26 AND (o.next_leg_id IS NULL OR o.prev_leg_id IS NULL)
27 AND (i.ess_file_import_id = 5365 OR i.truncated_path = 1)
28 AND (o.ess_file_import_id = 5365 OR o.truncated_path = 1);
16997 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3301526158
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 117 | | 36598 (1)| 00:07:20 |
|* 1 | HASH JOIN | | 1 | 117 | 1512K| 36598 (1)| 00:07:20 |
|* 2 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1236K| | 18220 (1)| 00:03:39 |
|* 3 | TABLE ACCESS FULL| SCHEDULE_SET_DAILY_FLT | 23448 | 1442K| | 18220 (1)| 00:03:39 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("I"."DST_STATION_ID"="O"."DEP_STATION_ID" AND
"I"."AIRCRAFT_TYPE_ID"="O"."AIRCRAFT_TYPE_ID" AND
"I"."SCHEDULE_SET_ID"="O"."SCHEDULE_SET_ID")
filter(("I"."FLIGHT_NO"="O"."FLIGHT_NO" AND "O"."LEG_SEQUENCE"="I"."LEG_SEQUENCE"+1
OR "I"."ONWARD_FLIGHT"="O"."FLIGHT_NO" AND "I"."FLIGHT_NO"<>"I"."ONWARD_FLIGHT") AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440>=20 AND
("O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL")*1440<=1440 AND
"O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL">=NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)
AND "O"."DEP_DATETIME_LOCAL"-"I"."ARR_DATETIME_LOCAL"<NVL("I"."AIRCRAFT_ROTATION_LAYOVER",0)+
1)
2 - filter(("O"."NEXT_LEG_ID" IS NULL OR "O"."PREV_LEG_ID" IS NULL) AND
"O"."SCHEDULE_SET_ID"=2263 AND ("O"."ESS_FILE_IMPORT_ID"=5365 OR "O"."TRUNCATED_PATH"=1))
3 - filter(("I"."NEXT_LEG_ID" IS NULL OR "I"."PREV_LEG_ID" IS NULL) AND
"I"."SCHEDULE_SET_ID"=2263 AND ("I"."ESS_FILE_IMPORT_ID"=5365 OR "I"."TRUNCATED_PATH"=1))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
165243 consistent gets
161093 physical reads
0 redo size
339178 bytes sent via SQL*Net to client
2197 bytes received via SQL*Net from client
171 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16997 rows processed
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
8 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3713220770
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR | | | | |
----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6158 recursive calls
17 db block gets
1704 consistent gets
5 physical reads
1928 redo size
729 bytes sent via SQL*Net to client
338 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
158 sorts (memory)
0 sorts (disk)
8 rows processed
Parameters :
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL>
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
SQL>
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL>
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL>
SQL> column sname format a20
SQL> column pname format a20
SQL> column pval2 format a20
SQL> SELECT sname, pname, pval1, pval2
2 FROM SYS.aux_stats$;
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 10-09-2008 19:54
SYSSTATS_INFO DSTOP 10-09-2008 19:54
SYSSTATS_INFO FLAGS 1
SYSSTATS_MAIN CPUSPEEDNW 1304.15264
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SNAME PNAME PVAL1 PVAL2
-------------------- -------------------- ---------- --------------------
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
13 rows selected.
SQL>
I am unable to give the TKPROF as I don't have enough privileges.
I am a developer and am not an expert in Performance tuning. I could see no difference in the Explain plan even with out the USE_HASH hint. Please suggest.