Re: Physical reads on table very high.

Is this Oracle 7.3 with 8K blocks ? All
comments below relate to 7.3

I presume, also, that you can't hint the code because it is 3rd party.

From the stats, Oracle thinks it is going to find 3,112 rows matching the query.

You last post says you have 12,000 blocks, so Oracle has probably rated your query as 3,000 physical I/Os by index, or (apparently) 831 by tablescan (which suggests that your multiblock_read_count may be 16)

As a test, trying hinting the use of index, and see what value the cost comes to.

(It also looks as if you need to rebuild the table to pack the data, but I suspect it just degrades over time - you could try setting PCTUSED to a 75% and see if this helps).

You could try creating an index to match the ORDER BY clause; I don't think it will work in this version, but if it does, it will force the table into the MRU end of the buffer, which may help.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Ethan Post wrote in message <02eace00.04b29b5e_at_usw-ex0105-037.remarq.com>...


>Ok here is execution plan:

>

>SQL> SELECT  *  FROM SVMB733.F986110  WHERE  ( JCJOBQUE

>= 'QB7331    '

>  2   AND JCJOBSTS = 'D ' AND

>  3  JCEXEHOST = 'hq-asprd                                '

>  4   AND JCFUNO = 'UBE '

>  5   AND JCPRTQ = '7100      ' );

>

>Execution Plan

>----------------------------------------------------------

>          0

>SELECT STATEMENT Optimizer=CHOOSE (Cost=831 Card=3112

>Bytes=1191896)

>

>

>          1                  0

>  TABLE ACCESS (FULL) OF 'F986110' (Cost=831 Card=3112

>Bytes=1191896)

>

>So it is obviously using full table scan but here is the index I

>created:

>

>create index jdeF986110_01 on F986110 (JCJOBQUE, JCJOBSTS,

>JCEXEHOST, JCFUNO, JCPRTQ)

>tablespace svmb733i;

>

>Same columns as in the SQL statement why doesn't the optimizer

>see that this is the best way to go???

>

>Thanks,

>Ethan

>

>

>-----------------------------------------------------------

>

>Got questions?  Get answers over the phone at Keen.com.

>Up to 100 minutes free!

>http://www.keen.com

>