Re: Oracle Init.ora Parameters

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 29 May 2004 10:53:41 -0700
Message-ID: <1085853229.974754@yasure>

Mill wrote:

> "g300" <ian_cpt_at_yahoo.com> wrote in message
> news:nI2dnRYVec063yrdRVn-jg_at_is.co.za...
>

>>Hi,
>>
>>I agree with Daniel, throwing more memory at the database does not

>
> necessary
>

>>mean it will perform any better. I am currently supporting around 10
>>databases on Windows 2000, and the largest SGA is around 1GB. We've tested
>>really big SGA's (Up to 5GB) but we did not receive any SIGNIFICANT
>>performance gain by having it set so large.
>>
>>Just from what I've seen, I have the following comments:
>>
>>- Why is your shared pool so large? I know this is something that varies
>>from environment to environment but 600MB is extremely large. Remember,

>
> this
>

>>is a cache for SQL statements so unless you have gigantic SQL statements
>>being thrown at your system, it does not have to be so large. You may
>>actually have this in reverse, in that your buffer cache is only around
>>100MB. Try and find a balance by lowering the shared pool and increasing

>
> the
>

>>buffer cache.
>>- From your top wait events, db file sequential read is tops. This

>
> normally
>

>>points to index scans. Are you running a packaged app or something

>
> developed
>

>>in-house. Again, this is dependant on your environment.
>>- Finally, you've provided a STATSPACK report that is fairly old and does
>>not span a great deal of time. I would recommend looking at more recent
>>data, and making comparisons between different periods of activity on your
>>system.
>>
>>Regards
>>
>>P.S. What is your disk configuration like? Size of DB?
>>
>>

>
>
>
> Hi
>
> Thanks for your comments, i have inherited the system from a DBA, I am due
> to go on a DBA course
> next month, which should hopefully help. We are running a packaged app
> (PL/SQL).
>
> The disk configuration is 14 disks on RAID 5, then partition to create 2
> drives, system tablespace
> mirrored copy of redo logs & control file on one drive, all other data files
> + Control file + Redo logs
> on the other drive.
>
> I am really confused as to why this setup has been used i though striping
> was best for db's ? also confused
> why it's been partitioned if you lose the array you are stuffed partition or
> no partition.
>
>
> Mill

You thought ... or think ... correctly. RAID 5 is the worst possible choice.

The chances of the single DBA course being sufficient to help you with the questions you are asking is rather small. I'd suggest getting finding a local resource. Do you have a local Oracle user's group?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat May 29 2004 - 12:53:41 CDT