Index help

From: M.Godlewski <mcgodlewski_at_yahoo.com>
Date: Fri, 23 Jul 2004 07:17:32 -0700 (PDT)
Message-ID: <20040723141732.71051.qmail@web42007.mail.yahoo.com>

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 it is using a lot of Oracle I/O.

Was wondering if anyone had ideas or HELP for index/indexes they would add to the 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

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 - 09:14:26 CDT