Physical reads on table very high.

I have been banging my head on this one for a while and need to seek some professional advice. We have a table in our ERP system (OneWorld) called the F986110 which J.D.Edwards monitors for new batch jobs being submitted to the system. There are 5-7 sessions all submitting the same SQL pretty much all of the time. The physical read rate is out of site running between 3000-20000 physical reads per minute per session.

Here is the output of the sqlarea.sql file found on JL's website (http://www.jlcomp.demon.co.uk/sqlarea.html)

First load time: 2000-05-18/03:12:00

Buffer gets:     -75404099 ratio       -13
Disk reads:      -1.93E+09 ratio      -327
Rows delivered       16768 ratio         0
Executions         5906223
Parses               16768
Memory              178709
Sorts                    1
Invalidations         2736

SELECT * FROM SVMB733.F986110 WHERE ( JCJOBQUE = :KEY1 AND JCJOBSTS =
:KEY2 AND JCEXEHOST = :KEY3 AND JCFUNO = :KEY4 AND JCPRTQ = :KEY5 ) ORDER BY
JCJOBPTY ASC,JCACTDATE ASC,JCACTTIME ASC It is the exact same SQL for each session. If I do compute statistics on the table 4 out of 5 times the rate will drop back down to the lowpoint of about 3000 reads per minute per session. I have tried putting an index on the 5 columns in the table and no change. The only thing I have not done yet is explain plan (which I am doing in a few) but I would think that the use iof bind variables on the SQL and the index clearly on the 5 columns that Oracle would have no problem using the index.

Here is the information on the table:

37306 Rows, 12624 Blocks, 3158 Avg Space, 0 Chain Count, 1803 Avg Row Len, 6412 Average Space Free

Now for the life of me even if it where doing full table scans I can see how it is getting aged out of the buffer pool so quickly, I know that a full scan would go to the end of the LRU list but would it be marked free and aged out that quickly? The tablespace is on a seperate disk on and EMC unit and the actual disk reads are not a problem because the data is in disk cache but it just ticks me off that I can't get the rate down to a reasonable number.

Thanks,
Ethan

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Thu Jul 13 2000 - 00:00:00 CDT