Sometimes databases require down time and performance fixes as parts decline or become obsolete. Where do you start and what should you do? More importantly, what are your users' expectations? Business people generally care about two factors: how much (cost) and how soon (time). To them, down time is lost money.
Today's DBAs need to examine all of the potential performance fixes available to them. Sometimes a quick, "good enough" but technically suboptimal solution is sufficient and even preferred.
In a recent DBTA webinar, Bret Scalzo, senior product manager, DB PowerStudio, IDERA, discussed possible performance improvement options that may be overlooked in the DBA's rush to tune the database.
There are a few places to start when assessing database performance, Scalzo explained. Perform a quick check of OS log file for errors/warnings and look for hardware/driver issues (e.g. NIC or IO failures).
After taking that step, users should look for Oracle error messages here too and perform a quick check of the Oracle alert.log file.
Sometimes the complete issue and answer lies here, Scalzo said, as it often refers users to specific trace files for the full answer. Once that’s complete, download latest version of free orachk utility and run it.
Don’t always assume it’s the database that’s automatically failing, Scalzo explained. He outlined several ideas users should keep in mind including:
- The database is generally the last moving part in a complex machine
- The database is almost always downstream of most everything else
- Know your database architecture
- Know your application architecture
- Keep system statistics up to date
- Know your database object design
- Know your database batch jobs and order
When all else fails, consider counterintuitive ideas, Scalzo said. In past hardware was expensive and person hours were relatively cheap, applications often could not be down to permit hardware upgrades, and the golden rule is: never throw hardware at a problem
Today hardware is now cheap and person hours are relatively expensive, amazing new hardware technologies exist such as SSD and PCIe NVMe, hypervisors offer online dynamic scale up or out (e.g. add CPU, RAM, etc.), hypervisors offer online VM relocation to less stressed or more capable host, and cloud databases offer online dynamic scale up or out as simply a configuration check box with increased hourly cost.
An archived on-demand replay of this webinar is available here.