We have noticed few queries in production database which doing full table access in two big tables holding more than 1.5 million rows. This was causing heavy CPU utilization, USER I/O waiting.
Actually, the database server does not have good storage equipment, all data files stored in local hard disks in RAID 5. There are few e-business applications using this database in 24x7.
We spent our time to find out alternate queries which can use existing indexes on these tables by analyzing SQL Execution Plans.
Finally, pass those NEW set of SQL coding to Application team for reviewing and follow their tests in QA System.
For them, this is unnecessary work, also not taking risks to amend their application code which is almost one decade old. After few months, there was no news from them. Whether, they are still doing the code review or shelved it.
The database server was still hitting high CPU usage and wait for USER I/O. The possible way to jump for this issue is, by creating new indexes and find out existing SQL coding to avoid full table access in these tables and start using the new indexes.
Consider the followings before creating indexes in Production database
1. Gather Tablespace size where tables and its existing indexes were stored
2. Gather size of Tables and its indexes
3. Estimate the size requirement for new indexes
4. Come up with syntax and do a dry run in testing environment, find out estimated time of execution (this could be varied in production server)
5. Remember to use "NOLOGGING" option in the syntax in order to avoid unnecessary logging in redo logs otherwise index creation could be taken longer duration to complete
6. Very Important option is "ONLINE" which helps to execute DML statements on these tables and not get application disturbed
Well, after introducing new indexes, the CPU usage/USER I/O wait reduced drastically.
0 comments:
Post a Comment