Statistics
----------------------------------------------------------
4001 recursive calls
18 db block gets
276723 consistent gets
525087 physical reads
1480 redo size
789593219 bytes sent via SQL*Net to client
4448243 bytes received via SQL*Net from client
404343 SQL*Net roundtrips to/from client
9 sorts (memory)
1 sorts (disk)
6065116 rows processed
Thou explain plan showing 12 minutes for this query, actually it is taking close to 2 hours to complete. I am trying to find why it is taking 2 hours and couple of questing flashing in my mind.
is this because of (disk) sort?
is this because of more recursive calls / consistent gets?
As this is simple query, not much options in my hand. Could some one drop some hints to me so that I can tune this query in a better way?
Thanks,
Last edited by vnktummala; 02-22-2011 at 10:32 AM.
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
It is doing FTS in parallel. As there is no filter condition no chances of using Index.
Thanks,
If there is an index on order by column, it is used by the optimizer and the rows are fetched using the index, and hence a sort should not be required. I think that I am correct. Am I?
It is a report, but who is firing this query and does he really need all those 6 million records?
If he needs what is frequency of this report once in month.
Derive this way and then find out is it worth tuning it?
However for the time being, you can reove that PARALLEL hint or reduce 8 to some other value if you I/O wait is high? BTW how many cpu cores availble in DB.
Informatica session fires this job and yes it needs all 6 million order by. This session is to build persistent cache and it is part of daily job.
I have executed this query with parallel 4 hint and traced with 10046 evebt. As parallel_threads_per_cpu value is 2, total 8 parallel slaves assigned work.
Due to formatting issue, I have attached the tkprof output from PX & QC processes. Please have a look.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
threads P4 - P7 are doing FTS (1.5 million rows each). For these threads I have seen two interested wait events(listed below) along with idle wait events.
PX Deq Credit: need buffer
PX qref latch
but total wait time for the above two events is very less ( average 0.50 for need buffer and average 0.03 for latch).
I will trace this query again with reduced parallel servers and post my findings. Mean time any expert advise is highly appreciated.
Thanks,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.