Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Questions about performance tuning rules of thumb

Re: Questions about performance tuning rules of thumb

From: Steve Phelan <stevep_at_XXnospamXX.toneline.demon.co.uk>
Date: 1998/03/19
Message-ID: <890303695.2379.0.nnrp-08.c2de712e@news.demon.co.uk>#1/1

x wrote in message <6el18b$qqg$1_at_news.metrobbs.com>...
>
>We are looking into optimizing i/o performance for a data warehouse running
>Oracle 7.3.3 on HP/UX 10.3. Are these rules of thumb reasonable for
>warehouses?
>
>1) The more memory the better. Oracle loves memory. Is there a
>relationship with lots of memory improving certain types of read
 performance
>for system tables? Should the SGA be set as large as possible? I hear
>this commonly in the AIX world.

Yes, the more the better. But watch your OS and Oracle, as you may be limited to 4Gig unless you can go 64 bit with both.

System tables don't usually require that much memory (i.e., as little as 10Meg of shared pool may be OK as long as you don't use lots of PL/SQL). Sure, you'll have to scale the shared pool up, but that depends on the nature of your database and it's application.

It's the database block buffers that require the very, very large amounts of memory.

>
>2) Raid 1/0 for system, rollback, temp and high write tables. Raid 5 OK
>for mostly read tables. This sounds reasonable for a warehouse. Comments?
>

Yes. Reasonable. But test to make sure all us OK. Maybe do some comparative tests as well (i.e., raw, mirror instead of RAID 5).

>3) Oracle parallel does not like striping. Is this true? Parallel
>_server_ only? Are there some cases where mirroring should be used w/out
>striping?

Depending on how it's implemented, it will be completely transparent to Oracle. I've not heard of *hardware* striping having a problem with PQO or PSO.
>
>4) Consider solid state disk for system/temp/rollback. Is it worth it?
>SSA? EMC?
Depends how much of a 'managed, flexible' system you want and how much you have to spend. Talk to the vendors and try and get a feel of what may be suitable for you.

>
>What are some other things to look at... CPU performance is fine but I/O is
>a problem. The drives are configured like "rule" #2 using 80GB Nike drives
>but the SGA is only 500MB on a 1GB box.

Look at caching, but be careful with internal backup power supplies (this is how the EMC stuff works). Again, look at you SGA and sort space in memory usage.

>
>Would increasing SGA help i/o performance for certain operations?
>What would 2GB RAM give you if the db runs reasonably well in 1GB but i/o
>could be improved?
>Can you have too much memory - that does nothing for performance?

Yes. No. Yes. Yes. No. :-)

You'll really have to monitor and tune your own system based on it's usage. But yes, you can spend too much on performance that you will never need - not just memory, but everything.

>
>Thanks.. reply email as well if you can.
>
>
>
>xenophon_at_usa.net
>XenoTech
>
>
Received on Thu Mar 19 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US