| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index help
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_at_roughsea.com> 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
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 75 (PL626)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL SCHEDULEQUEUEENTRY
 |
 |