oracle11g - oracle 11g execution plan behaviour -
i have simple query performing filtering , join rowid.
select * booking.booking_grid bg, booking.booking_states bs bg.hotel=128 , bs.rowid =bg.bookingstate; when explain plan get:
plan_table_output ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ plan hash value: 1597031677 -------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes |tempspc| cost (%cpu)| time | -------------------------------------------------------------------------------------------------- | 0 | select statement | | 6137k| 1041m| | 1763k (1)| 05:48:27 | |* 1 | hash join | | 6137k| 1041m| 538m| 1763k (1)| 05:48:27 | |* 2 | index unique scan| booking_gridpk | 6137k| 468m| | 547k (1)| 01:48:05 | |* 3 | index range scan| booking_grid_index5 | 6137k| | | 90388 (1)| 00:17:52 | | 4 | table access full| booking_states | 158m| 14g| | 365k (2)| 01:12:14 | -------------------------------------------------------------------------------------------------- predicate information (identified operation id): --------------------------------------------------- 1 - access("bs".rowid="bg"."bookingstate") 2 - access("bg"."hotel"=128) 3 - access("bg"."hotel"=128) index booking_grid are:
booking booking_gridpk unique valid iot - top n no no hotel, day, bookingstate booking booking_grid_uniq unique valid normal n no no hotel, day, booking, validitystart booking booking_grid_index5 nonunique valid normal n no no hotel, bookingstatus, isdayuse, day booking booking_grid_index7 nonunique valid normal n no no hotel, booking, validitystart booking booking_grid_index10 nonunique valid normal n no no hotel, isdayuse, bookingstatus, day index booking_states are:
booking booking_states_pk unique valid normal n no no hotel, booking, validitystart booking booking_states_index2 nonunique valid normal n no no hotel, yieldroomtype, bookedroomtype, roomtype booking booking_states_booking nonunique valid normal n no no hotel, booking, bookingstatus booking booking_nosegment_index nonunique valid function-based normal n no enabled no sys_nc00034$ to_number(decode(to_char("marketsegment"),null,decode("book",0,null,to_char(decode("isdayuse",'n',decode("isshared",'n',decode("bookingstatus",'b',"hotel"*10000+least("departure","validityend"),'i',"hotel"*10000+least("departure","validityend"),'w',"hotel"*10000+least("departure","validityend")))))))) booking booking_norate_code_index nonunique valid function-based normal n no enabled no sys_nc00033$ to_number(decode(to_char("ratecode"),null,decode("book",0,null,to_char(decode("isdayuse",'n',decode("isshared",'n',decode("bookingstatus",'b',"hotel"*10000+least("departure","validityend"),'i',"hotel"*10000+least("departure","validityend"),'w',"hotel"*10000+least("departure","validityend")))))))) booking booking_nobookingtype_index nonunique valid function-based normal n no enabled no sys_nc00032$ to_number(decode(to_char("bookingtype"),null,decode("book",0,null,to_char(decode("isdayuse",'n',decode("isshared",'n',decode("bookingstatus",'b',"hotel"*10000+least("departure","validityend")))))))) booking booking_states_booking_type nonunique valid normal n no no hotel, bookingtype, isdayuse, bookingstatus booking booking_states_cancel_index nonunique valid function-based normal n no enabled no sys_nc00035$, sys_nc00036$ decode("bookingstatus",'c',"hotel",'c',"hotel") booking booking_states_cancel_index nonunique valid function-based normal n no enabled no sys_nc00035$, sys_nc00036$ decode("bookingstatus",'c',"cancelreason",'c',"cancelreason") i don't understand 2 things:
- why oracle decide faster join before filtering on hotel=128?
- why using hash join when use "table access user rowid"
the weird thing when run same exact request hotel=201, fine:
plan_table_output ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ plan hash value: 4251203092 --------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | --------------------------------------------------------------------------------------------------- | 0 | select statement | | 591k| 100m| 643k (1)| 02:07:12 | | 1 | nested loops | | 591k| 100m| 643k (1)| 02:07:12 | |* 2 | index unique scan | booking_gridpk | 591k| 45m| 52686 (1)| 00:10:25 | |* 3 | index range scan | booking_grid_index5 | 591k| | 8707 (1)| 00:01:44 | | 4 | table access user rowid| booking_states | 1 | 98 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- predicate information (identified operation id): --------------------------------------------------- 2 - access("bg"."hotel"=201) 3 - access("bg"."hotel"=201) any idea what's going on there?
thank you,
renaud
the reason different execution paths because oracle thinks there 6 million rows hotel=128 591,000 hotel=201. in case of bigger intermediate set, oracle chose hash join on nested loops.
what don't this:
and bs.rowid =bg.bookingstate; you're storing oracle-format rowids in column called bookingstate???
ok, given confirmation bookingstate contain oracle rowids, here's why i'd you're getting hash join instead of nested loop join:
first, when oracle reads row, doesn't read row @ time, reads blocks @ time. nested loop join table access user rowid lookup, it's going find row in booking_grid , go read block has row in booking_states rowid. catch is, if later on there's row rowid in block it's read before, it's going re-read block (sure, may cached) other row. kind of "open block, row, close box....then later on open same box again, row, close box, move on next box"
on other hand, hash join is: - sorting rows in smaller set (in case rows in booking_grid hotel=128), put them in memory - full table scan booking_states - , here's kicker - using multiblock reads. reads many blocks @ time , processes of rows in block without needing re-read later. it's "open box, process of rows in box, close box."
(for more details on above check out http://docs.oracle.com/cd/b28359_01/server.111/b28274/optimops.htm, in particular following sections:
- 11.5.1 full table scans
- 11.5.3.1 assessing i/o blocks, not rows
- 11.6.3 nested loop joins
- 11.6.4 hash joins )
by way, it's bit curious it's doing "access("bg"."hotel"=128)" step twice using 2 indexes -how booking_gridpk , booking_grid_index5 indexes defined? you're asking columns both tables, plan never touches booking_grid table.)
Comments
Post a Comment