| 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
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 <queue table>
TRUNCATE TABLE <queue table>
INSERT INTO <queue table> SELECT *FROM MYFIX
DROP TABLE MYFIX Regards,
Stephane Faroult
On Fri, 23 Jul 2004 07:17 , 'M.Godlewski' <mcgodlewski_at_yahoo.com> 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
 |
 |