For those of you who read my previous blog from last week, or read the recent White Paper entitled “Simplifying Oracle Database Administration” written by Mike Ault, Bert Scalzo and myself, you’ll have seen from the survey carried out by James F Koopmann that about 20% of a typical DBAs time is spent fire fighting and responding to user related problems. Some production DBAs I have spoken to put it even higher.

So, here is a process which, I’m sure many of you follow; essentially detect (or discover) that a problem exists, diagnose the root cause of the problem to understand what needs to be done and finally resolve the problem using appropriate methods, or the classic “top down” approach.

I’m going to briefly walk you through the minimum steps necessary using Toad’s DBA Module to achieve this simply, rapidly and effectively.

1. Detect

If your telephone is already ringing off the hook when you get to the office, it is essential that you have a systematic, repeatable approach to determining the root cause of a stoppage or performance slow-down which any of your team can follow.

1.1 Monitor the OS to see if something is running external to Oracle
                 (batch jobs, etc) – it is essential that this is ruled out first before
                  looking at the database.

Here’s an example of the Unix Monitor showing activity on a Linux operating system while executing an industry standard TPC-C Benchmark (see Figure 1)

Figure 1 – Monitoring the Linux operating system

The graph on the right shows both IO devices have heavy activity and the process list shows both the Oracle DBWR (database writer) and LGWR (log writer) processes are top resource consumers. Both CPU Usage and Process Queue are quite reasonable – and thus not an issue.

Toad’s Unix Monitor works for Linux, Solaris, AIX, HP-UX and Tru64.

1.2. Monitor the Oracle instances on the server to determine which is
                   the one with the problem

Toad’s Database Browser lets you report on multiple instances to assess which one has the problem. (see Figure 2)

Figure 2 – Monitoring multiple Oracle instances

Focus on one instance and:

1.3. Check the server, database and TNS Listener are running (see
                   Figure 3) 

  Figure 3 – Monitoring the Oracle Server, Listener and Database

Toad pings each server, TNS pings the selected databases and checks to see if the TNS Listener process is running and provides simple up/down indicators.

1.4. Monitor that instance for problems (see Figure 4)

Figure 4 – Monitoring key Oracle Database metrics 

            1.5. Check Alert Logs – Toad has an FTP interface if your alert logs are 
                   stored on a UNIX or Linux server.
            1.6. Verify Rollback Segments are online – Toad’s Schema Browser will
                   give you a quick indication of this.
            1.7. Check ADDM and AWR for instance performance problems –
                   check-out Toad’s Reports menu for a list of these and other
                   reports.
            1.8. Examine Top Sessions for resource usage (see Figure 5)

Figure 5 – Determining Top User Sessions by CPU consumption

1.9. Monitor specific sessions (Long operations, SQL Statement
                   execution, etc) – Toad’s Session Browser will relate the SID
                   to a user to show full session data including the SQL statement
                   the user is executing.     
            1.10. Monitor Tablespace usage for fragmentation, etc (see Figure
                     6)

Figure 6 – Monitoring Tablespace consumptio

2. Diagnose

Having determined where the issue is, you now need to quickly and effectively diagnose the exact cause of the problem and get user(s) up and running again.

In this screenshot of Toad for Oracle’s Database Probe window (see Figure 7)), you get real-time display of instance activity with alert markers indicating potential problems. Clicking on a marker displays a description of the problem – in this case excessive Redo Log Buffer size.

Figure 7 – Diagnosing an instance configuration problem

Another example might be slow database performance due to a user executing a very inefficient SQL statement (exposed by looking at top sessions by CPU for example). Having a view of all Oracle sessions with the ability to obtain information about each session (in this case the SQL statement the user is executing) enables a rapid way to resolve a problem.

Where the statement has already completed execution, you will need to look in the SGA or access the Oracle Trace files. (Database – Monitor – SGA Trace/Optimization or Database – Diagnose – TKProf)

In all cases, access to Toad’s SQL Optimizer directly from the SQL statement is essential in order to simplify the process of improving execution time. (see Figure 8)

Figure 8 – Diagnosing a users SQL execution problem

3. Resolve

Having diagnosed the root cause of the problem, whether it be database configuration, tablespace or rollback segment problems, or SQL execution performance, it is important to resolve the problem quickly and effectively.

Integration points in Toad for Oracle help enormously here (as you see in the screenshot above where SQL Optimizer can be launched directly from the diagnosis window.

In the case of a resolution which requires an improvement to the execution time for a SQL statement, this in itself may be time consuming and problematic.

Quest’s SQL Optimizer is a semi-automatic SQL and PL/SQL optimization tool which can be configured according to the business process being used. It is self-levelling in terms of the skill level of the person using it but, at all times, keeps you informed what it will do the database. (see Figure 9)

It is also batch oriented giving DBAs the freedom to let the tool optimize SQL according to rules they set without being involved, and also the manner in which the solution is deployed back into the application.

Figure 9 – Resolving a users SQL execution performance problem

Another essential characteristic which you will see in Toad’s DBA Module is workflow navigation. Tasks are presented in a clear, logical way – following a functional paradigm which makes sense and is easily repeatable without having to make notes !

Here is a sample of some of the menu features offered by Toad showing how they are offered in the functional paradigms of Administer, Compare, Create, Diagnose, etc (see Figure 10)

The order in which the menu items are presented can also be customized to suit a particular team – indeed, items could be removed or even an entirely new menu sub-system created to suit the workflow and range of tasks required.

Figure 10 : Administration Task presentation is clear and function based