Re: DB File Sequential Read Waits

Ignore the minor details about control files, log files and parsing for the moment.

In your one hour snapshot you've averaged 213 reads per second at an average wait
time of 39 milliseconds, for a total of 8 hours of wait time (do you happen to have around 8 concurrent streams of queries, or far more) whilst using 18% of a CPU. The only problem worth addressing is your I/O problem. You have to reduce the total number of read requests, and find out if there is anything (other than your own disk layout and concurrency) that is causing the device bottleneck.

Question 1 HAS to be:

    How many different physical devices are     you hitting when you query your indexes     and tables ? Make sure that you really     have the striping that you think you have -     maybe you have one disc for data, one     for indexes, one for temp ... maybe your     logical names have become physical devices !     If you have N highly-concurrent users in this     test, and it's a realistic test, I would aim to     have N devices being hit simultaneously.

Question 2:

    Given that you are hitting a very large partitioned     table:

1. Are you sure your test queries are realistic, or might you be hitting 'old' partitions very regularly that would not be hit very often in a production system.
2. Are your queries supposed to achieve partition elimination, and are they achieving it. Failure to eliminate could be driving up the work load by a factor of N where N is the number of partitions in the table
3. Are your access paths into the partitioned tables as efficient as they could be ? Are the indexes the correct ones, have they been padded with garbage, are they not precise enough. Scrape v$sql for a sample set of queries, and check how many rows are acquired from the table for each row returned to the end-user.

You appear to be doing roughly one physical read for each row fetched by rowid, so I doubt if you are going to get much benefit from playing around with the KEEP and RECYCLE buffer pools.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"rjpfal" <ralbertson_at_comcast.net> wrote in message
news:2932a99f.0312290945.6fc14a6f_at_posting.google.com...


> Anyone have suggestions on how do reduce the number of db file

> sequential reads?

> Also any suggestions on improving IO on an EMC array (concatenated

> versus striped volumes)

>

> I have a CRM application running on Oracle 8.1.6.3 Enterprise Edition.

> It is in archive log mode and is running in a Sun Solaris 2.8 machine

> with 2 dedicated CPU's.

>

> I am in the process of testing a new disk configuration on the backend

> of this server (EMC) I have the data index files assigned to

> respective file systems /u02 and /u03. The breakdown of the physical

> disk on the array is done in 8G chunks on 73G hardrives. The stripe

> size across the disk is 1M.

>

> The test I am performing is strictly a read-only set against the DB

> using most often used queries from the production database. A similar

> test was run against the production DB during off hours.

>

> I am seeing no real improvement in the total IO time between the

> production system test (concatenated volumes) and the test server

> (striped volumes) both is amount of IO and the actual times.

>

> The application is totally random on its access to the data. There are

> not really any hot blocks other than those used by a small set of

> lookup tables.

>

> For the most part index scans are being utilized. Note

> optimizer_index_cost_adj is set at 5. Also db_file_direct_io_count is

> at 16. Do these not have opposing methods by the CBO, i.e. less costly

> index versus full table scan.

>

> There is primarily 1 table that is accessed frequently and this table

> is partitioned based upon each month. IO seems to be evenly

> distributed.

>