plsql - Oracle Scalar function in select vs table valued function join -

i have have query performing poorly. 1 aspect of query use of cross join on table-valued function, in mimicking tsql behaviors of using cross apply on function avoid using scalar function call. bad behavior in oracle?

the main issue i'm running oracle tuning advisor not parse query i'm unable research index optimizations yet. wouldn't post code suspect query more table optimization may causing issue.

the statistics se table table volumn more 4,000,000 records. can recommend removal of blatant bad oracle behaviors? or if looks tool index tuning advisory? oracle enterprise manager won't parse query provide recommendations.

additional performance information captured trace , formatted through tkprof

parse: count(1) | cpu(0.04) | elapsed(0.04) | disk(0) | query(852) | current(0) | rows(0)

execute: count(1) | cpu(0.00) | elapsed(0.00) | disk(0) | query(0) | current(0) | rows(0)

fetch: count(1) | cpu(9.64) | elapsed(14.50) | disk(34578) | query(35610) | current(4) | rows(4)

misses in library cache during parse: 1 optimizer mode: all_rows parsing user id: 1165

rows row source operation

---

  4  hash join outer (cr=38069 pr=34578 pw=0 time=19208475 us)   2   collection iterator pickler fetch report_interval_sequence_udf (cr=97 pr=0 >                                                                    pw=0 time=13766 us)   4   view  (cr=37972 pr=34578 pw=0 time=19194353 us)   4    hash group (cr=37972 pr=34578 pw=0 time=19194329 us)   

60650 filter (cr=37972 pr=34578 pw=0 time=19673947 us)
60650 nested loops (cr=37972 pr=34578 pw=0 time=19431329 us)
60650 hash join (cr=37941 pr=34578 pw=0 time=5294908 us) 4 collection iterator pickler fetch report_machineinfo_gett_udf (cr=2331 pr=0 pw=0 time=212033 us)
60650 table access full els_statistic_entry (cr=35610 pr=34578 pw=0 time=4416705 us)
60650 collection iterator pickler fetch report_interval_gett_udf (cr=31 pr=0 > pw=0 time=13372794 us)

select          timeinterval,          stats.during,          stats.name,          stats.cnt             table (get_interval_sequence_udf(                                          to_timestamp ('07/15/2011','mm/dd/yyyy')                                         ,to_timestamp ('07/20/2011','mm/dd/yyyy')                                         ,2)) dtrange     left join     (          select               i.during               , mi.name               , sum (cast (value_numeric int)) cnt                         statistics se          join table (get_context_info_udf ()) mi                on (se.context_id = mi.context_id)          cross join table (interval_gett (se.entrydate, 2))                        statistictypeid = hextoraw ('6cf933b091ae46fea7f56be96308190f')                , entrydate < to_timestamp ('07/20/2011','mm/dd/yyyy')                , entrydate > to_timestamp ('07/15/2011', 'mm/dd/yyyy')          group              i.during              , mi.name     ) stats on dtrange.timeinterval = stats.timeinterval   following reference in aforementioned query.   create or replace function interval_gett(datestamp in timestamp,  timeinterval in int)  return treportintervallist vresult treportintervallist; begin      select treportinterval(                             case timeinterval                              when 1 to_char(datestamp, 'yyyy-mm-dd hh24')                              when 2 to_char(datestamp, 'yyyy-mm-dd')                             when 3 to_char(datestamp, 'yyyy-ww')                             end                            )       bulk collect vresult                                             dual rownum = 1;       return vresult; end;    create or replace function get_interval_sequence_udf(       starttime in timestamp,       endtime in timestamp,       inputinterval in int)       return t_interval_list_table    intervallist t_interval_list_table := t_interval_list_table();     begin      select           case inputinterval          when 1 (t_interval(report_interval_get_udf((starttime + ((rownum-1) * 1/24)), inputinterval))) --hour          when 2 (t_interval(report_interval_get_udf((starttime + (rownum-1)), inputinterval))) --day          when 3 (t_interval(report_interval_get_udf((starttime + ((rownum-1)*7)), inputinterval))) --week             end            bulk collect intervallist           dual connect level <= (case inputinterval                                            when 1 cast(ceil(((trunc(endtime, 'hh') - trunc(starttime, 'hh')) * 24)) int)                                           when 2 cast(trunc(endtime, 'dd') - trunc(starttime, 'dd') int)                                           when 3 cast(ceil(((trunc(endtime, 'dd') - trunc(starttime, 'dd')) )/7) int)                                        end);       return intervallist;       end get_interval_sequence_udf;   create or replace function      get_context_info_udf     return ttrfrmengmachineinfolist vresult ttrfrmengmachineinfolist;     begin             select ttrfrmengmachineinfo(ch.context_id, mac.name)         bulk collect vresult                        ch           inner join             b cxm  on ch.contx_machine_id = cxm.contx_machine_id            inner join             c mac on cxm.machine_id = mac.machine_id            inner join             d ic  on mac.machine_id = ic.machine_id                        ic.onfigurable_entity_id =  hextoraw(format_guid_udf('11111111-fae9-47a1-91a9-60a53e9660fe'))             , mac.is_deleted = 'n'             , ic.is_deleted = 'n';          return vresult;       end; 

you can investigate time being spent , execution plan oracle chooses following advice in this otn thread

regards,
rob.