Is my new Oracle DBA talking sense
When we setup FC LUNs for our MSSQL boxes we rarely have to present them with more than about 8 distinct LUNs of various kinds (Quorum, MSDTC, TempDB, Data, Logs, Backup and a few others).
We have a new Oracle DBA, and he's given me a list of LUNs he wants for his first new server - there's 38 of them! and this is for a really basic DB box, with only a single DB. They're all fairly small (100GB) LUNs and they clearly bolt together using ASM in an LVM-type way.
Is the the best way to do this, I'm really no Oracle expert but it seems overcomplex to me, what's your thoughts and experiences on this matter?
Solution 1:
I am an Oracle DBA. Your new DBA is acting like alot of Oracle DBAs and over engineering.
NO oracle does NOT need 38 LUNs. I have spread data files out on large numbers of lun's but these are on systems that are VERY active and VERY large. LUNs don't necessary map to new RAID groups right? So having files on seperate luns don't necessary spread anything out anyway (I am not an expert at this).
All this kind of file striping will do is make far more work for the DBA. This increases his importance to the team. Alot of Oracle DBAs try to make themselves seem more important and over engineer things ALL THE TIME.
Seperating data out to differnet raid groups/luns is not oracle specific. Its based on usage. In order to properly spread out files your DBA would need to understand the application to know what is being accessed alot (btw, seperating indexes from data does NOT improve performance since access is serial... ). Does he know the application? Has he looked at the database to see what objects are being accessed alot? What needs to be spread out? What does bulk writes and reads and needs to be isolated.
This sounds like a small/medium size database. What is the activity level? He probably doesn't know.
Generally on smaller databases you don't need to do much at the file system level to improve performance. 95% is SQL and do the developers run too many sql statements in loops.
edit (years later!):
I have been spending some time talking to SAN engineers and have improved my knowledge of SANs and LUNs somewhat since posting this. First off a LUN is 'logical'. It does not necessary map to separate RAID groups, disks, etc... That is setup by the SAN engineer and will not be visible to the DBA. There is alot more to separating out IO in a SAN that most people realize.
I am working on a very large systems that has a very high activity level. We have hundreds of LUNs, RAID Groups, etc... we spread files all over the place. We work with the SAN engineers to configure LUNs to make sure they are spread to different parts of the SAN. We really have NO visibility into how the LUNs are mapped from the OS level. A new file system does not mean we have data mapped to a new location on the SAN.
As far as the HP paper about striping ASM. This is totally meaningless when working with a SAN. The striping,mirroring, RAID, etc... is all done under the surface. You will not see it at the application or database level. Configuring Oracle ASM for 'striping' is agian meaningless in a SAN, because you will just be striping across logical volumes that could be using a RAID 5 configuration (vast majority due to control costs. SANs are multi-million dollar investments). You will just see file systems. Those do not necessarily map to different disks or different locations in the SAN.
IBM apparently has a new feature that lets the SAN decide where to write to disks based on activity. My point here is that people who optimize SANs are specialists. You need to work with them. A DBA or an application developer will not have visibility to see whether anything is being spread out.
From what I have seen most shops do not have very good SAN engineers. It tends to be a job for junior level people. Most of the good ones tend to be consultants. So alot of the time you are just using the default setup by the manufacturer. To reiterate adding more LUNs probably won't spread any data out, unless you have a SAN engineer configure it for you under the surface. On top of that, you can have 1 LUN and have it spread out for you. Unless you have a good SAN engineer, all this stuff is meaningless. It is obvious to me that the DBA in question does not know enough about SANs to even know he doesn't know anything.
99.9% of the time standard configurations are just fine. Unless you have a specific IO bottleneck this is unnecessary. If you do, then you need to work with the SA and SAN engineer to determine what the problem is. Alot of the time it has NOTHING to do with the layout of the SAN. Again, DBAs and developers won't have access to see what is going on underneath let alone the knowledge to figure this out. SANs are very complex.
Solution 2:
You can try to whack him over the head with THIS, describing the SAME (Stripe And Mirror Everything) approach, which is refreshingly simple.
Solution 3:
I don't have a direct answer because we use MSSQL and mySQL. But whenever my DBA asks for something that just sounds crazy ... like that. I require him to document why each piece is required. This serves two purposes one a lot of times they suddenly change their mind to something more sane and two allows me to see their thought process so i can apply some systems logic to what they want and present a alternative that isn't so out of whack. SO in this case I would ask for a document justifying the need for each of the 38 LUNs
Solution 4:
There are studies that show that striping in ASM and at the hardware level can be an advantage for performance. HP-Oracle whitepaper These performance gains are mostly seen in high concurrency situations which it doesn't sound like you are expecting. But might be what your DBA is used to.