Using ILM - Storage DB Tiering

[sunilmenon82]

Hello Team,
One of our customer wanted to do a storage level tiering of there production databases using ILM in oracle.They have 17 Production databases that has to be tired .Could you guys please help me with the factors that should be taken into account from staspack report for doing this tiering . They have 3 levels of storage with different performance . so the critical and most used DB should go to the storage with highest performance .

Regards,
Sunil

[Tim...]

Hi.

My starting point would not be statspack. I would start by talking to the business. What seems like busy or slow databases may not reflect the worth of the system to the business. For example:

Let's assume I have a call center application. Looking at the stats it doesn't seem to have any major I/O issues because it just does lots of little PK lookups and looks like it could be moved to slower storage with no obvious problem. I then talk to the business and they tell me if each call is made longer by 1 second as a result of any change, the company loses £1 Million a month, should I consider moving this database? Hell no!

You have to talk to the business to understand the implications of any change you may think is possible. Once you understand the relative importance of the databases and their usage by the business, and more importantly the impact of any change, you can then start thinking about what could be done.

Another factor is you probably don't want to move whole databases to different storage. Most databases have hot objects or tablespaces along with tablespaces that have very little I/O. If you were trying to optimizer performance across the business, it would make sense to have the hot objects on the faster storage, and the cooler objects on the slower storage. This is not necessarily a whole database issue.

Looking at statspack itself, the top section of the report contains a load profile. This gives you a list of key factors as far as load is concerned. Physical Reads and Physical Writes are obviously pretty important, along with the total Redo Size.

About half way down the report you have "Tablespace IO Stats" and "File IO Stats", which indicate which tablespaces and datafiles are busy.

Remember, the snapshots in the report are important. If a system has extremely high load at one time of day, then nothing for the rest of the day, looking at a 24 hour report could be misleading. I once worked on a system that had *massive* I/O for 1 hour a day, then was virtually idle for the rest. Most DBAs would look at the I/O requirement for the day and laugh at how small it was, but if they looked at the I/O requirements for the busy hour, the would probably cry. Your reporting period is application specific. To know this you need to talk to the business.

Like I said, this information shouldn't be considered in isolation.

Cheers

Tim...

[sunilmenon82]

Thanks Tim , You have really provided some valid worth inputs which will really help me a long way

[Tim...]