Here is the trace 10046 output for Dev DB.

PARSING IN CURSOR #2 len=1585 dep=0 uid=60 oct=3 lid=60 tim=4844269580 hv=4130183277 ad='18e267a8'
select /* 10046_dev */ am.airline_name,fsp.schedule_no,fsp.fs_serial_number,min(ofs.effective_period_from) Effective_period,fsp.created_date,
(fsp.created_date+5) Date_To_Clear,pl.serial_number_document,ud.user_name,fsp.OPERATING_OR_MARKETING,fsp.airline_id
from Airline_master am, flight_schedule_procesing fsp,action_table at,
operating_flight_schedule ofs,permit_license pl,user_details ud where ofs.schedule_no = pl.schedule_no and
fsp.schedule_no=pl.schedule_no and am.airline_id = fsp.airline_id and ud.user_id = pl.atman_userid
and pl.ddgo='1' AND fsp.SEASON_CODE='NS0909' and pl.permit_or_license='L'
group by am.airline_name,fsp.schedule_no,fsp.fs_serial_number,fsp.created_date,pl.serial_number_document,
ud.user_name ,fsp.OPERATING_OR_MARKETING,fsp.airline_id
UNION select am.airline_name,fsp.schedule_no,fsp.fs_serial_number,min(ofs.effective_period_from) Effective_period,
fsp.created_date,(fsp.created_date+5) Date_To_Clear,pl.serial_number_document,ud.user_name ,
fsp.OPERATING_OR_MARKETING,fsp.airline_id from Airline_master am, flight_schedule_procesing fsp,action_table at,
MARKETING_FLIGHT_SCHEDULE ofs,permit_license pl,user_details ud
where ofs.schedule_no = pl.schedule_no and fsp.schedule_no=pl.schedule_no
and am.airline_id = fsp.airline_id and ud.user_id = pl.atman_userid and pl.ddgo='1'
AND fsp.SEASON_CODE='NS0909' and pl.permit_or_license= 'L'
group by am.airline_name,fsp.schedule_no,fsp.fs_serial_number,fsp.created_date,
pl.serial_number_document,ud.user_name ,fsp.OPERATING_OR_MARKETING,fsp.airline_id
order by Date_To_Clear desc,Effective_Period desc
END OF STMT
PARSE #2:c=843750,e=1233677,p=49,cr=1319,cu=0,mis=1,r=0,dep=0,og=1,tim=4844269569
EXEC #2:c=0,e=354,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=4844270322
WAIT #2: nam='SQL*Net message to client' ela= 8 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=4844270407
WAIT #2: nam='db file sequential read' ela= 19146 file#=4 block#=12044 blocks=1 obj#=51959 tim=4844291907
WAIT #2: nam='db file scattered read' ela= 3427 file#=4 block#=12045 blocks=16 obj#=51959 tim=4844295590
WAIT #2: nam='db file scattered read' ela= 2932 file#=4 block#=12061 blocks=16 obj#=51959 tim=4844298999
....................
.....................
(lots of similar WAIT for 'db file scattered read' lines)
....................
....................
WAIT #2: nam='db file scattered read' ela= 861 file#=4 block#=785 blocks=8 obj#=51929 tim=4848209699
WAIT #2: nam='db file scattered read' ela= 832 file#=4 block#=794 blocks=7 obj#=51929 tim=4848269278
WAIT #2: nam='db file scattered read' ela= 1096 file#=4 block#=801 blocks=8 obj#=51929 tim=4848321229
WAIT #2: nam='db file scattered read' ela= 588 file#=4 block#=810 blocks=4 obj#=51929 tim=4848380713
*** 2009-05-22 10:10:42.237
WAIT #2: nam='db file scattered read' ela= 26178 file#=4 block#=13915 blocks=16 obj#=51982 tim=5305514047
*** 2009-05-22 10:13:21.920
WAIT #2: nam='db file scattered read' ela= 25565 file#=4 block#=13931 blocks=16 obj#=51982 tim=5465586645
*** 2009-05-22 10:14:09.928
WAIT #2: nam='db file scattered read' ela= 24855 file#=4 block#=13947 blocks=14 obj#=51982 tim=5513657089
*** 2009-05-22 10:17:29.012
WAIT #2: nam='db file scattered read' ela= 15669 file#=4 block#=13963 blocks=16 obj#=51982 tim=5712986353
*** 2009-05-22 10:18:37.422
WAIT #2: nam='db file scattered read' ela= 23656 file#=4 block#=13979 blocks=16 obj#=51982 tim=5781468584
*** 2009-05-22 10:20:23.414
WAIT #2: nam='db file scattered read' ela= 40395 file#=4 block#=13995 blocks=16 obj#=51982 tim=5887508279
*** 2009-05-22 10:22:40.931
WAIT #2: nam='db file scattered read' ela= 41340 file#=4 block#=14011 blocks=14 obj#=51982 tim=6025035684
*** 2009-05-22 10:24:02.698
WAIT #2: nam='db file sequential read' ela= 21001 file#=4 block#=12619 blocks=1 obj#=51975 tim=6106848462
WAIT #2: nam='db file scattered read' ela= 792 file#=4 block#=12620 blocks=5 obj#=51975 tim=6106849532
WAIT #2: nam='db file scattered read' ela= 784 file#=4 block#=12625 blocks=8 obj#=51975 tim=6106869092
WAIT #2: nam='db file scattered read' ela= 1030 file#=4 block#=12634 blocks=7 obj#=51975 tim=6106900005
WAIT #2: nam='db file scattered read' ela= 782 file#=4 block#=12641 blocks=8 obj#=51975 tim=6106926075
WAIT #2: nam='db file scattered read' ela= 726 file#=4 block#=12650 blocks=7 obj#=51975 tim=6106955718
WAIT #2: nam='db file scattered read' ela= 796 file#=4 block#=12657 blocks=8 obj#=51975 tim=6106982712
WAIT #2: nam='db file scattered read' ela= 715 file#=4 block#=12666 blocks=7 obj#=51975 tim=6107011906
WAIT #2: nam='db file scattered read' ela= 783 file#=4 block#=12673 blocks=8 obj#=51975 tim=6107037802
WAIT #2: nam='db file scattered read' ela= 941 file#=4 block#=12682 blocks=7 obj#=51975 tim=6107067545
*** 2009-05-22 10:24:26.841
WAIT #2: nam='db file scattered read' ela= 31789 file#=4 block#=12689 blocks=8 obj#=51975 tim=6131010935
FETCH #2:c=1274828125,e=1286758667,p=1547,cr=2007,cu=0,mis=0,r=1,dep=0,og=1,tim=6131029148
WAIT #2: nam='SQL*Net message from client' ela= 34676 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131064072
WAIT #2: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131064203
FETCH #2:c=0,e=368,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=6131064529
WAIT #2: nam='SQL*Net message from client' ela= 330776 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131395398
WAIT #2: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131395574
FETCH #2:c=0,e=150,p=0,cr=0,cu=0,mis=0,r=2,dep=0,og=1,tim=6131395670
WAIT #2: nam='SQL*Net message from client' ela= 60068 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131456111
STAT #2 id=1 cnt=18 pid=0 pos=1 obj=0 op='SORT UNIQUE (cr=2007 pr=1547 pw=0 time=1286759124 us)'
STAT #2 id=2 cnt=18 pid=1 pos=1 obj=0 op='UNION-ALL (cr=2007 pr=1547 pw=0 time=1286758687 us)'
STAT #2 id=3 cnt=16 pid=2 pos=1 obj=0 op='HASH GROUP BY (cr=1501 pr=1473 pw=0 time=1262504407 us)'
STAT #2 id=4 cnt=40910398 pid=3 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=1501 pr=1473 pw=0 time=945084723 us)'
STAT #2 id=5 cnt=799 pid=4 pos=1 obj=0 op='HASH JOIN (cr=1388 pr=1365 pw=0 time=3818244 us)'
STAT #2 id=6 cnt=18 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=317 pr=296 pw=0 time=162166 us)'
STAT #2 id=7 cnt=18 pid=6 pos=1 obj=0 op='HASH JOIN (cr=297 pr=290 pw=0 time=140385 us)'
STAT #2 id=8 cnt=18 pid=7 pos=1 obj=0 op='HASH JOIN (cr=281 pr=276 pw=0 time=101273 us)'
STAT #2 id=9 cnt=349 pid=8 pos=1 obj=51959 op='TABLE ACCESS FULL FLIGHT_SCHEDULE_PROCESING (cr=189 pr=187 pw=0 time=59551 us)'
STAT #2 id=10 cnt=362 pid=8 pos=2 obj=51995 op='TABLE ACCESS FULL PERMIT_LICENSE (cr=92 pr=89 pw=0 time=23938 us)'
STAT #2 id=11 cnt=520 pid=7 pos=2 obj=52012 op='TABLE ACCESS FULL USER_DETAILS (cr=16 pr=14 pw=0 time=25311 us)'
STAT #2 id=12 cnt=18 pid=6 pos=2 obj=51934 op='TABLE ACCESS BY INDEX ROWID AIRLINE_MASTER (cr=20 pr=6 pw=0 time=21200 us)'
STAT #2 id=13 cnt=18 pid=12 pos=1 obj=51935 op='INDEX UNIQUE SCAN SYS_C005605 (cr=2 pr=1 pw=0 time=18785 us)'
STAT #2 id=14 cnt=91120 pid=5 pos=2 obj=51982 op='TABLE ACCESS FULL OPERATING_FLIGHT_SCHEDULE (cr=1071 pr=1069 pw=0 time=1756633 us)'
STAT #2 id=15 cnt=40910398 pid=4 pos=2 obj=0 op='BUFFER SORT (cr=113 pr=108 pw=0 time=369042788 us)'
STAT #2 id=16 cnt=51202 pid=15 pos=1 obj=51929 op='INDEX FAST FULL SCAN ACTION_ID_FK (cr=113 pr=108 pw=0 time=531161 us)'
STAT #2 id=17 cnt=2 pid=2 pos=2 obj=0 op='HASH GROUP BY (cr=506 pr=74 pw=0 time=24251876 us)'
STAT #2 id=18 cnt=716828 pid=17 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=506 pr=74 pw=0 time=19063303 us)'
STAT #2 id=19 cnt=14 pid=18 pos=1 obj=0 op='HASH JOIN (cr=393 pr=74 pw=0 time=365136 us)'
STAT #2 id=20 cnt=18 pid=19 pos=1 obj=0 op='NESTED LOOPS (cr=317 pr=0 pw=0 time=49047 us)'
STAT #2 id=21 cnt=18 pid=20 pos=1 obj=0 op='HASH JOIN (cr=297 pr=0 pw=0 time=46978 us)'
STAT #2 id=22 cnt=18 pid=21 pos=1 obj=0 op='HASH JOIN (cr=281 pr=0 pw=0 time=28911 us)'
STAT #2 id=23 cnt=349 pid=22 pos=1 obj=51959 op='TABLE ACCESS FULL FLIGHT_SCHEDULE_PROCESING (cr=189 pr=0 pw=0 time=6541 us)'
STAT #2 id=24 cnt=362 pid=22 pos=2 obj=51995 op='TABLE ACCESS FULL PERMIT_LICENSE (cr=92 pr=0 pw=0 time=4419 us)'
STAT #2 id=25 cnt=520 pid=21 pos=2 obj=52012 op='TABLE ACCESS FULL USER_DETAILS (cr=16 pr=0 pw=0 time=5327 us)'
STAT #2 id=26 cnt=18 pid=20 pos=2 obj=51934 op='TABLE ACCESS BY INDEX ROWID AIRLINE_MASTER (cr=20 pr=0 pw=0 time=1465 us)'
STAT #2 id=27 cnt=18 pid=26 pos=1 obj=51935 op='INDEX UNIQUE SCAN SYS_C005605 (cr=2 pr=0 pw=0 time=570 us)'
STAT #2 id=28 cnt=8297 pid=19 pos=2 obj=51975 op='TABLE ACCESS FULL MARKETING_FLIGHT_SCHEDULE (cr=76 pr=74 pw=0 time=138494 us)'
STAT #2 id=29 cnt=716828 pid=18 pos=2 obj=0 op='BUFFER SORT (cr=113 pr=0 pw=0 time=7234330 us)'
STAT #2 id=30 cnt=51202 pid=29 pos=1 obj=51929 op='INDEX FAST FULL SCAN ACTION_ID_FK (cr=113 pr=0 pw=0 time=460892 us)'
WAIT #0: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6131457258
*** 2009-05-22 10:30:36.465
WAIT #0: nam='SQL*Net message from client' ela= 369383560 driver id=1413697536 #bytes=1 p3=0 obj#=51975 tim=6500840876
XCTEND rlbk=0, rd_only=1