I am running same query through siebel apps on production and test and both have same amount of data. On the prod it is taking much longer than on test. Also on production the timing is changing considerable based on the time of the day I run the query. During the busiest time it takes almost twice as much then during off hours. I want to pin point what is the bottle neck. I put a trace on both session and it show most time is during the Fetch.

Can someone tell me what could be the bottel neck from the reading and how should I go about digging more.

call Count cpu elapsed disk query curr rows
----- ----- --- ------- ------ ----- ---- ----
Parse 10 0.04 0.02 0 0 0 0
Execute 6 0.00 0.00 0 0 0 0
Fetch 35 11.87 250.73 56808 447290 18 35
------- ---- ---- ------- ------ ------- ---- ---
total 51 11.91 250.75 56808 447290 18 35

======================================================

call count cpu elapsed disk query curr rows
------ ----- ----- ------- ----- ----- ---- ----------
Parse 4 0.03 0.03 0 0 0 0
Execute 2 0.00 0.01 0 0 0 0
Fetch 14 2.46 8.21 24801 25372 8 14
------- ---- ----- ------- ----- ----- --- ----------
total 20 2.49 8.25 24801 25372 8 14