FW: VMS, large database

Sorry - premature send of the last one! This note finished properly.

Mike,

> I have a large 7.4 db running under VMS... Database has

I am assuming 7.3.4 here (unless VMS has 7.4 :)

> It runs around 256 I/O per sec and 7,011 logical I/O per sec.
> Fetch vs. Scan is 5%..
> They run about 87 db waits per minute and get 176 I/O per wait...

I assume you calculated these figures off V$SYSSTAT and V$SESSION_EVENT? What does the OS say? (Long time since I used VMS, but I think you do have a SHOW DEVICE or some other SHOW command that can show iostat-like OS stats?

7011 LIOs for 256 PIO computes to almost 27:1. Do you have optimized SQL? I would look at large amounts of Nested Loops in case you are at Rule...  

> What might I look at to see if they would benefit to go from
> an 8K blocksize to 16K as they migrate from 7.4 to 8.1.7 I think it
> is... He's going to build a new database and import. If this
> would help, it would be the time to do it.

I would seriously question the need for moving from 8k to 16k until you have exhausted all other possibilities. If the problem is large amounts of LIO, it's not going to solve anything. For a database that complex, figuring out the right order of Import and the elapsed time it would take in itself would be a good weapon that could be used against the blocksize change argument.

I would look at the 'quick and dirty' set of views, i.e. V$SYSTEM_EVENT, rollup V$SESSION_WAIT, etc. and look at the Top wait events. Keep the blocksize increase for the last.

Hth,
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

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).