Database and Application Tuning Approach:
Application Description
What is the nature of the application? What does it do?
Is the application a third-party product or home grown?
How many users does the application support?
Does the application allow AD-HOC reporting?
What is the batch window like?
Are users allowed to schedule AD-HOC batch work in the system?
What is the data retention policy? And do actively purge the old history?
Identifying the Problem
What is the nature of the problem?
Is the entire application slow
Just a group of transactions or specific transactions
Is there a particular time worse than other? If this is a store application what is the peak processing time during the week/day/time?
Is your application solution heavily customized and interfaced w/ other applications? If so are the other applications tuned?
Are all users experiencing problems locally and at the store locations?
What is the user’s perception of the performance issue?
What impact is the performance problem having on the users and your stores?
Which performance objectives are not being met?
Can the problems be recreated in your test or pqa environments?
Establishing a Timeline
Identifying when the performance issue began to occur is critical to the investigation process. What are the infrastructure and or application changes made to the environment?
When did the problem start occurring?
Is the problem intermittently or is performance poor all of the time?
Does the problem occur during specific calendar days (i.e. Monday, Friday) and/or time of day?
Identifying Changes
If you are able to measure using database performance monitoring tools can we go back prior to the changes. What database tools do you have to monitor performance? If you have Oracle the GRID tuning pack can be very useful to ID problems actively and historically. Quest Software also provides Spotlight and SQL Analyzer these two are great tools to actively tune an Oracle environment. And GRID is very complementary w/ the history playback option.
Did anything change from a business perspective (functionality, user growth and or additional data/history)?
Did anything change from a technical perspective (software upgrades/patches, new application code changes or new code implemented
Hardware changes if any…Are Workloads causing the issue?
Are these databases used by other applications?
Is the application sharing hardware and memory w/ other applications
Is batch running when the problems occur?
Are there any system and other maintenance jobs running when the problems occur (i.e. statistics, data refreshes, import/exports jobs, etc.)?
Is there an increase in the number of online users during the times when performance is an issue? Are more people using the system at that time?
Application/Database Technology
What is the architecture like?
Application server configurations (number of app servers, web servers, load balancers and hardware configuration of each including CPUs, memory disk subsystems)
Identify the database architecture (hardware, operating system, database):
Is the application RAC Cluster for highly availability and/or single instance?
If Oracle are you using ASM for your databases storage management
Hardware vendor, model and configuration (CPUs, memory)
Disk subsystem (local, SAN, NAS, storage array vendor)
What is the LUN and DISK configuration presented to the servers? A proper disk configuration paired with Oracle ASM usually preforms very well.
Operating system version and patches
Database version and patches