Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index help

Re: Index help

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Fri, 23 Jul 2004 08:30:25 -0700 (PDT)
Message-ID: <20040723153025.76044.qmail@web42005.mail.yahoo.com>


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
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_at_freelists.org[2] put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/[3] FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html[4]

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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Jul 23 2004 - 10:27:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US