Oracle (TOAD) Query Progress

Question

Is there a way using Toad to either check or estimate the progress of a query? I have a query which is at it's best running for around 20 minutes. As i'm tweaking the query to try and make it faster sometimes it ends up in a total loop and will never finish. Sometimes it might take an hour. What would be really useful is some indication of just how much ground the query is actually covering to at least give me an idea what is going quicker or what is making it go slower.

Any help would be much appreciated.

I've included my query just incase someone might see something glaringly obvious that might increase the efficiency...

select * from
(select menu_optn_name, tran_nbr, seq_nbr, cntr_nbr, ch.total_qty, ptt.create_date_time,
um.user_name, ch.trlr_nbr, sum(ref_field_2) as Total from prod_trkg_tran ptt
inner join user_master um 
on um.emplye_id = ptt.user_id
left outer join carton_hdr ch  
on ch.carton_nbr = ptt.cntr_nbr
where (menu_optn_name = 'RF Split/Comb {Carton}' and ptt.cntr_nbr = '0030651942')
group by menu_optn_name, tran_nbr, seq_nbr, cntr_nbr, ch.total_qty, ptt.create_date_time,
um.user_name, ch.trlr_nbr
union
select aptt.menu_optn_name, aptt.tran_nbr, aptt.seq_nbr, aptt.cntr_nbr, ach.total_qty, 
aptt.create_date_time, um.user_name, ach.trlr_nbr, sum(aptt.ref_field_2) 
as Total from   wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
inner join user_master um 
on um.emplye_id = aptt.user_id
left outer join wm_archive.carton_hdr@awm.corp.*******.com ach  
on ach.carton_nbr = aptt.cntr_nbr
where aptt.cntr_nbr not in
(select aptt.cntr_nbr from wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
where aptt.menu_optn_name = 'RF Split/Comb {Carton}' and aptt.cntr_nbr = '0030651942')
and aptt.tran_nbr in
(select aptt.tran_nbr from wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
where aptt.menu_optn_name = 'RF Split/Comb {Carton}' and aptt.cntr_nbr = '0030651942')
group by aptt.menu_optn_name, aptt.tran_nbr, aptt.seq_nbr, aptt.cntr_nbr, ach.total_qty,
aptt.create_date_time, um.user_name, ach.trlr_nbr)
where rownum <=2;

7's

Answer 1

You want to look at the v$session_longops table e.g. http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2092.htm

Or if you have enterprise manager with the tuning and diagnostics it will show up long running queries. Otherwise split the query into lots of small parts and run separately. Find out what takes a long time and rewrite or index it. Maybe put your query here and we might make some suggestions. As above explain plan could help.

I'd highly recommend you format your query for readability e.g.

select * from
       (select menu_optn_name, 
               tran_nbr, 
               seq_nbr, 
               cntr_nbr, 
               ch.total_qty, 
               ptt.create_date_time,
               um.user_name, 
               ch.trlr_nbr, 
               sum(ref_field_2) as Total 
        from prod_trkg_tran ptt
        inner join user_master um on um.emplye_id = ptt.user_id
        left outer join carton_hdr ch on ch.carton_nbr = ptt.cntr_nbr
        where (menu_optn_name = 'RF Split/Comb {Carton}' 
        and ptt.cntr_nbr = '0030651942')
        group by menu_optn_name, 
                 tran_nbr, 
                 seq_nbr, 
                 cntr_nbr, 
                 ch.total_qty, 
                 ptt.create_date_time,
                 um.user_name, 
                 ch.trlr_nbr
        union
        select aptt.menu_optn_name, 
               aptt.tran_nbr, 
               aptt.seq_nbr, 
               aptt.cntr_nbr, 
               ach.total_qty, 
               aptt.create_date_time, 
               um.user_name, 
               ach.trlr_nbr, 
               sum(aptt.ref_field_2) as Total 
        from   wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
        inner join user_master um on um.emplye_id = aptt.user_id
        left outer join wm_archive.carton_hdr@awm.corp.*******.com ach on ach.carton_nbr = aptt.cntr_nbr
        where aptt.cntr_nbr not in (select aptt.cntr_nbr 
                                    from wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
                                    where aptt.menu_optn_name = 'RF Split/Comb {Carton}' 
                                    and aptt.cntr_nbr = '0030651942')
        and aptt.tran_nbr in (select aptt.tran_nbr 
                              from wm_archive.prod_trkg_tran@awm.corp.*******.com aptt
                              where aptt.menu_optn_name = 'RF Split/Comb {Carton}' 
                              and aptt.cntr_nbr = '0030651942')
        group by aptt.menu_optn_name, 
                 aptt.tran_nbr, 
                 aptt.seq_nbr, 
                 aptt.cntr_nbr, 
                 ach.total_qty,
                 aptt.create_date_time, 
                 um.user_name, 
                 ach.trlr_nbr)
where rownum <=2;

Have you tried running the 2 sub-selects separately and to see how long they take and each half of the union. Do you really only want the first row (you could try to add the first rows hint - SELECT /*+ FIRST_ROWS */ )? You can only tune stuff by finding the particular part that runs slowly!

Answer 2

I would say to use the Explain Plan option (Ctrl+E). It will give you an idea from the impact of your query.

Beside that, it is really hard to determine the duration of your query.