Re: Index help

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 23 Jul 2004 13:08:38 -0600
Message-id: <41016236.9020504@sun.com>

I see several problems.

1) This statement was executed 29 times. As only 4 rows were returned, 25 of the executions returned 0 rows.
2) Factoring in the executions, each execution performed a little over 1700 logical i/os.
3) Without seeing the data/binds I'm guessing. But... the predicate on codeC5 would return 2/3 of all rows (and probably touch all 

of the blocks in the table. Considering the number of columns in this table, I'd venture that the number of rows per block is not that high. A table rebuild *might* help, but if the number of rows/block would not change much and the high water mark is reasonable, you won't gain anything.

Based on these observations, I don't see anything earth-shaking at the db level. I would be more concerned as to why 25 (87%) of the executions were unneccesary.

Regards,
Daniel Fink

M.Godlewski wrote:
> 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
> -----------------------------------------------------------------

---

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 - 14:05:54 CDT