Re: Index help
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 23 Jul 2004 13:08:38 -0600
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
