Thanks for the suggestion I will try it! The query does return very few rows, but the table has 5102 rows in it. That's why I figured an index may improve the IO counts. Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote: A classic problem with tables used to store queues is a high-water-mark pushed very high by a temporary glitch - Queue lengthening. I suspect something of the kind, especially if your table is supposed to contain very few rows (at least, your query returns very few). Stop your system, then CREATE TABLE MYFIX AS SELECT * FROM TRUNCATE TABLE INSERT INTO SELECT *FROM MYFIX DROP TABLE MYFIX Regards, Stephane Faroult On Fri, 23 Jul 2004 07:17 , 'M.Godlewski' sent: I ran tkprof on a trace file, and saw the SQL was doing full table scans. I added an index and now the SQL is using the index, but it still seems like itis using a lot of Oracle I/O. Was wondering if anyone had ideas or HELP for index/indexes they would add tothe following SQL statements? SELECT 'wt.queue.ScheduleQueueEntry',A0.args,A0.classnamekeydomainRef, A0.idA3domainRef,TO_CHAR(A0.endExec,'dd mm yyyy hh24:mi:ss'),A0.entryNumber, A0.entryOwnerIsNull,A0.classnamekeyB5,A0.idA3B5,A0.failureCount, A0.inheritedDomain,TO_CHAR(A0.datelock,'dd mm yyyy hh24:mi:ss'), A0.classnamekeyA2lock,A0.idA3A2lock,A0.notelock,A0.classnamekeyA6,A0.idA3A6, A0.classnamekeyA5,A0.idA3A5,TO_CHAR(A0.scheduleTime,'dd mm yyyy hh24:mi:ss') ,TO_CHAR(A0.startExec,'dd mm yyyy hh24:mi:ss'),A0.statusInfoIsNull, A0.codeC5,A0.messageC5,TO_CHAR(A0.rescheduleTimeC5,'dd mm yyyy hh24:mi:ss'), A0.targetClass,A0.targetMethod,TO_CHAR(A0.createStampA2,'dd mm yyyy hh24:mi:ss'),TO_CHAR(A0.modifyStampA2,'dd mm yyyy hh24:mi:ss'),A0.idA2A2, A0.updateCountA2,TO_CHAR(A0.updateStampA2,'dd mm yyyy hh24:mi:ss') FROM ScheduleQueueEntry A0 WHERE (A0.idA3A5 = :1) AND (A0.scheduleTime <= TO_DATE(:2,:3)) AND ((A0.codeC5 = :4) OR (A0.codeC5 = :5)) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.00 0.00 0 0 0 0 Execute 29 0.00 0.00 0 0 0 0 Fetch 29 5.42 5.60 17240 50464 0 4 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 63 5.42 5.62 17240 50464 0 4 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 75 (PL626) Rows Row Source Operation ------- --------------------------------------------------- 0 TABLE ACCESS FULL SCHEDULEQUEUEENTRY --------------------------------- Do you Yahoo!? Vote for the stars of Yahoo!'s next ad campaign! ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com[1] ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx[2] put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/[3] FAQ is at //www.freelists.org/help/fom-serve/cache/1.html[4] ----------------------------------------------------------------- --- Links --- 1 modules/refer.pl?redirect=http%3A%2F%2Fwww.orafaq.com 2 javascript:parent.opencompose('oracle-l-request@xxxxxxxxxxxxx','','','') 3 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle-l%2F 4 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fhelp%2Ffom-serve%2Fcache%2F1.html ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- --------------------------------- Do you Yahoo!? Vote for the stars of Yahoo!'s next ad campaign! ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------