Oracle and what RAID??? performace??? 5

[hyperdaz]

I have a dev box running 9i n win2k...
all i/o interfaces seem low.. when doing some major queries

whats the standard when creating from scratch oracle9i databases...

At the moment I have Raid5 with default install of oracle....

(I am being told that Raid5 is slow and -never used for databases-).... this cant be true???

also whats best for checking performace

thanks

 

[alexhu]

RAID 5 has a write penalty (writes to the parity disk as well as the 'main' disk) and so tends not to be used for high I/O active systems.

'Stripe and Mirror Everything' (SAME) tends to be the usual method of configuration

Alex

 

[jimbopalmer]

My Oracle users are on a large financial application, in our scenario most access is read and all writes are the result of keyboarding. In this scenario, a relatively small non-volitle disk cache on the RAID array pays handsome dividends. no one keys more than one meg of text before they hit commit, so writes are always at the speed of the cache, not at the speed of the disk. (if your Oracle apps involved large bulk writes from outside sources a small cache would not be nearly as helpful)

for the maximium in reliability (raid 1+0) one would miror each disk and then stripe the disks for speed (with hot spares) but that will use over 50% of the disk space for redundancy ( My RAID manager then allows reads from either mirror, so twice as many reads can be in progress as with raw disks)

RAID 5 will not be that fast on reads or writes, and offers no protection from cable or controller failures, just drive failure, but loses much less disk space to redundancy. If you find you HAVE way more disk space than you need (because you needed a certain throughput and you needed more read/write heads to get that additional throughput) then by all means consider RAID 1 + 0 also called RAID 10 Look for RAID arrays with at least a meg of non-volitle cache (64 meg is common) if most of your writes are via user input.


I tried to remain child-like, all I acheived was childish.

 

[hyperdaz]

thanks jimbopalmer, thats helpful..

I have only four 36gig scsi harddrives on this devbox... its hpdl380 g3 with the standard intergrated raid controller... (I dont know how much memory this contains)

Its interested because when starting up the machine... with the oracle db switched off the server is responsive.. but as soon as the bd is started its writting about 3mb per second to the harddrives and this seems to be going on forever..

its been suggested that oracle is in some recovery mode but I dont know enough about oracle bd...
also have changed the redo logfiles to around 500mb each..

I have three tables and one of them could hold around 20 to 30 million records..

 

[sem]

I may assume that this is due to inappropriate TEMPORARY tablespace specified

Regards, Dima

http://seminihin.narod.ru

 

[hyperdaz]

Sem how do I increase this then as looking through the gui I see temp tablespace at 5GB...

 

[sem]

Is your temporary tablespace temporary indeed and not is just called TEMP? Such amount of sorting in most cases is implied by extremely inefficient execution plans that in turn may be caused by the lack of actual statistics. How about memory allocated/available for that instance?

Regards, Dima

http://seminihin.narod.ru

 

[hyperdaz]

One thing ppl keep asking me (out side this forum is why i am running Oracle on Windows OS) ...

-have no answer - thats what customer wants...

is this un-normal????


sem - u lost me... (soz)

 

[sem]

hyperdaz, I didn't understand your last message, but one of my Oracle instances (used for development) is on NT 4.0 and its uptime is about 3 years. Its performance is also quite good for that hardware (PII 300, 256M RAM).

Regards, Dima

http://seminihin.narod.ru

 

[dbtoo2001]

What are you using to determine that the slowness is related to disk i/o? Craig Shallahamer has some excellent free tools at

http://www.orapub.com

(you need to be sys user when you install them.) Compare the production initialization parameters with the dev instance. How different are they?

 

[Guest_imported]

No, it is not a bad idea to run Oracle9 on Windows. I've been supporting Oracle on Windows for the past eight years and find it cost-effective and responsive. But, that's usually been with 10-12 disk drives and a mix of RAID1, RAID5 and non-RAID. RAID5 is fine for your data and system tablespaces. You definitely *DO NOT* want the temp tablespace(s) or the Windows swap file on RAID5! Also, your rollback (or undo) tablespace will perform much better on RAID1 than on RAID5.

In most Oracle systems, the two tablespaces with the most disk writes are: temp and rollback/undo. Since RAID5 has a big penalty for write operations, that's why those parts of the database should be kept off RAID5 (plus the temp tablespace does not need the protection of RAID, rollback/undo is different - that should be protected with at least RAID1).

With only four disks in your case, you have a serious handicap. It will be difficult to configure Oracle with that disk configuration for both good performance and redundancy.

 

[hyperdaz]

sem...
Sorry its just comments from other people that get caught up in this dev project at work.. 99% are unix people and the first comment is why windows...

dbtoo2001
Determined by taskmanager... oracle.exe was / is writting at 3MB per second...

markgeer..
Thanks thats really useful... but leads me to the question of...
I have *painfully two semi spare hdds* they are no not big just 18.2GIG each and with reducing resources if need be i could use both...

Windows swap file I can not do anything about...

"temp tablespace(s) and rollback (or undo) tablespace will perform much better on RAID1 "

How could would I move this onto this spare drive...??
at worst how about moving these to a USB hdd..
--- hides its only Dev work ---

thanks for all your replys as you can guess I have been drawn into these problems even though my knowledge on oracle is limited... very limited.. - could never claim to be a bda or much else - thanks

 

[sem]

I think you should investigate the reason of this huge IO. Have you analyzed your data?

Regards, Dima

http://seminihin.narod.ru

 

[Guest_imported]

Step 1: Tune the Oracle SGA! Make sure that Oracle is using the RAM efficiently. It should be using about half of the physical RAM for the SGA. If this is not optimized, Oracle will be causing lots of unnecessary disk I/O. Make sure that the SGA is not too big though, or Windows will start using its swap file, and you'll get unnecessary disk I/O from that.

Step 2: If you can get more hard drives for that server, do that. The size doesn't matter, its mainly the fact that you are making additional drive heads available that is the issue.

Step 3: To move an Oracle data file (like the temp tablespace) you need to shut Oracle down, copy the file manually to the new disk location, then do a "startup mount", then do: "alter database rename file '[old location\name]' to '[new location\name]'" then open the database.

Step 4: If you get a new drive or two, and it is RAID1 or non-RAID, move the Windows swapfile there. Don't say you can't do anything about it. If someone else is responsible for the Windows install on this machine and they don't want to move the swap file (or don't know how to) they certainly do not know how to optimize Windows for Oracle.

Is this machine optimized for background processes? Windows can be optimized different ways but a Windows machine used as an Oracle server should certainly *NOT* be optimized for forground processes.