Oracle-L: RE: T3's and forcedirectio questions

From: kkennedy <kkennedy_at_firstpoint.com>
Date: Mon, 22 Jul 2002 09:38:30 -0800
Message-ID: <F001.0049E08D.20020722093830@fatcity.com>

For setting the db_multiblock_read_count, I suggest the Steve Adams script multiblock_read_test.sql which, on my system, yielded 16 with a block size of 8K.

For the optimizer settings, I used the Tim Gorman recommendations from http://www.evdbt.com/SearchIntelligenceCBO.doc which, on my system, suggested values optimizer_index_caching=90
optimizer_index_cost_adj=80

Note that on my system the index cost adj based on the average wait analysis came up higher than Tim's suggested range of 0-50. In any case, these settings produced a noticeable performance boost (thanks Tim and Steve).

Kevin Kennedy
First Point Energy Corporation

-----Original Message-----
Sent: Friday, July 19, 2002 7:13 AM
To: Multiple recipients of list ORACLE-L

Hi All,

I am attempting to improve performance in a shop that uses alot of T3 Storedge arrays to hold the oracle datafiles. I have already moved the redo logs onto to JBOD and tweaked ac couple other settings to make everyone happy but I still have some questions.

SUN best practises says set db_multiblock_read_count to a value that with the db block size equals 1M. This forces many foolish full table scans. I have fixed this by knocking the parameter down so the db does reads of 64kK but does anyone know if I can set the optimizer_index_* parameters (and to what values) so that I can set db_multiblock_read_count to match the arrays logical I/O setting?

2 Both Oracle and SUN recommend mounting the arrays with the forcedirectio option and then setting the oracle _filesystemio_options to setall. This causes a 50% performance hit measured by timing representative jobs. Does anyone know why? Even Steve Adams recommends these settings yet the numbers don't lie.

A grabbag of other stuff since I can't post to the list from work.

Thanks Kirti, for the answer to my listener question a couple of weeks ago

Rachel, I have been running Linux/Oracle in production since 8.1.6. I hate to be a paranoid command line hack again but the distribution is irrelevant, Either understand linux, ie compile your own kernels and understand the system libraries, or get a sysadmin who does. Trying to keep up to certified combinations is hard enough for commercial UNIXes,
for linux it's easier just to roll your own. So what if Oracle doesn't formally support it. This list will ;)

40% higher throughput than the same box running Oracle under Windblows, 30% less administrative work.

Upgrading the OCP. No-one cares what version of the OCP you have. I took mine at 7.3.4 and since then all anyone has asked is do you have it. The only
real value I found is it is helpful when applying for a TN visa to work in
the US.

my $0.013, otherwise known as $CAN 0.02

Dave

--

Dave Morgan
dvmrgn_at_telusplanet.net
403 399 2442
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Dave Morgan
INET: dvmrgn_at_telusplanet.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: kkennedy
INET: kkennedy_at_firstpoint.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------