A DBA’s role often revolves around tuning the database. This in itself can be a very daunting task. A checklist of DBA tasks can be quite handy. James Koopmann shares some performance tuning activities that a DBA needs to know and that will often be part of a solution to remedy a database performance problem.
Ok, so you landed yourself a DBA job, maybe you’ve been promoted, or you just had the role pushed into your lap because money is tight and your company can’t hire a “real” DBA. Whatever the reason we all know, aside from creating tables, a DBA’s role often revolves around tuning the database. This in itself can be a very daunting task. Where do you start, where do you go and how do you know you are doing it right? All these questions are quite reasonable and are surely one reason why Oracle has put out a series of 2-day type guides that can help guide you through your newly found set of tasks. This article and some to follow, we will being looking at one of those guides, the 2-Day + Performance Tuning Guide, and attempting to help guide you through the process of tuning your database. Granted, this 2-Day + Performance Tuning Guide does not give an exhaustive methodology of tuning an Oracle Database but it is a good place to start.
Probably one of the more common questions from new DBAs has to be wrapped around what tasks are involved with keeping an Oracle Database running smoothly. After all you won’t always have someone pounding on your door telling you what problems they are having and it is up to you to somewhat sniff out those performance type problems. To do this, checklists or a set of DBA tasks can become quite handy. While many of the following items in the following checklist are not actual performance-tuning activities they are tasks that a DBA needs to know and they will often be part of a solution to remedy the performance problem.
- Verify that an instance is up and running – clearly this can lead to performance issues (lack of any performance) and a DBA must know how to start and stop an instance as well as check to see if it is available.
- Look for alert log entries – the alert log is often your keys to the engine, as it will report errors / problems that are being encountered. Being able to quickly find alert log messages and relate them to performance problems is paramount.
- Verify backups are running and successful and be able to perform various recoveries – backups are your lifeline to ensuring data is protected. Verifying a backup has completed successfully enables you to validate that you can recover and that a currently running or stuck backup is not causing performance problems.
- Verify disk usage – I’ve seen many databases come to a complete halt because disk storage was limited for audit, trace, alert, database, and archive log files. Make sure you know how to check this quickly. Clearing up or allocating space is even more important.
- Check for memory and CPU resource issues – lack of memory or CPU resources over time can develop and we as DBAs need to be able to not only view these resources but we need to relate them to current trends in workloads.
- Identify growth patterns within the database – this is very similar to verifying disk usage but the growth patterns within a database are sometimes different from externally on disk. Make sure you are able to grasp how various database structures grow so that you can plan, if needed, for the future.
- Be able to install or patch the Oracle software – some performance issues are rooted in the Oracle software itself. Be able to install and patch the Oracle software for the purposes of testing persistence of tuning issues across versions.
- Be able to create and upgrade Oracle databases – same issue as the Oracle software.
- Be able to extract and manage user accounts and security – finding out who is doing what, what authorizations they have, and trimming access can often reduce rambunctious users from doing too much within the database and driving up resource usage.
- Be able to manage objects like tables, indexes, view, triggers, etc. – improperly built objects such as views, indexes, triggers, procedures, etc. can have dramatic impact on query performance. Solve these problems and many performance issues will go away as well.
- Monitor object usage – determining if objects are being used, such as indexes, can shed light on a multitude of transaction type issues. All too often indexes are ill-placed or go out-dated. Keeping index structures clean, as well as other objects, makes for a tight application environment.
- Monitor various performance ratios – while these ratios have gotten bad press in the past they are still quite valid in helping find areas that could provide potential tuning opportunities. Take a look at the big picture and then dive down into the details.
- Monitor I/O contention – too many systems are developed without taking into consideration the ability of disks to keep up with workload requirements. All too often DBAs are called in to solve performance problems that end up being disk I/O contention/speed and they can do nothing about it. Understand your disk limitations and when you are approaching them.
- Compare past results from checklists – it is nearly impossible to understand if performance is good today unless you have something from the past that tells you, today’s performance is worse. Surely there are times when systems grind to a halt but these are typically, at least should be, special instances. Understand past performance trends, current application workloads, and take appropriate action if performance is an issue.
Clearly, DBAs have a lot to think about when taking on the task of tuning an Oracle Database. Getting ready is all about having a plan set that you can follow. In this article, I took a quick look at a checklist of items you should be thinking about on a regular basis and some tasks that you should know how to do since they will most definitely be called upon within some of your attempts to tune an Oracle Database. This checklist/list of items in no way covers how to solve any performance issues, that will come later as we progress in this series and as we go through the 2-Day + Performance Tuning Guide. Stick around, it is bound to be an eye-opener if you have never tuned an Oracle Database before.
