Re: Index help
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 23 Jul 2004 08:40:53 -0700
Your table has 5102 rows, and a full scan does 50,000 logical reads? That's
one row for every 10 blocks. Unless your average row size is several
blocks, rebuilding the table sounds like a good idea, as Stephane suggests.
I would use "alter table ScheduleQueueEntry move;" rather than "create table
as ..." and then rebuilding any existing indexes. Even if the query didn't
use an index it would do much less I/O.
--Terry
----- Original Message -----
From: "M.Godlewski" <mcgodlewski@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, July 23, 2004 8:30 AM
Subject: Re: Index help
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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxx[2]
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/[3]
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html[4]
-----------------------------------------------------------------
--- Links ---
1 modules/refer.pl?redirect=http%3A%2F%2Fwww.orafaq.com
2 javascript:parent.opencompose('oracle-l-request@xxxxxxxxxxxxx','','','')
3
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Farchives%2Foracle
-l%2F
4
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fhelp%2Ffom-serve%
2Fcache%2F1.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
-----------------------------------------------------------------
---------------------------------
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:
