What a Junior DBA will do, When he Faces “Slowness in the Database” ?

Many a times, a DBA gets a call from Client side regarding the slowness of the Database.

In these case, fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit.

There are few checks a Junior DBA can perform to detect & Troubleshoot Slowness

1. Taking user inputs:

·        Is application is slow or any particular batch processing is slow?

·        Slowness is observed through out the system or only few or one user

·        Is it happening in some particular timing ?

By collecting these information we will get an outline of what needs to be checked.

2. Identify the Problem:

Gather specific details about the reported slowness. What operations or queries are slow? Is it affecting specific applications or users?

Check if this slowness is a new issue or a recurring problem.

3. Check System Resources:

Examine the server's CPU, memory, and disk utilization.

Monitor Oracle database performance metrics such as CPU usage, memory consumption, and I/O activity.

You can check the following Areas :

·        CPU utilisation

·        Load

·        Memory utilization

ü  Run TOP command in Linux to check CPU usage.

ü  Check  any single process is holding the CPU for long time -- note that process ID.

            Press 'c' in top command, it will give you the time and process which is   consuming more CPU.

ü  Run VMSTAT, SAR, PRSTAT command to get more information on CPU, memory usage and possible blocking.

4. Review Oracle Database Logs:

Check the Oracle alert log for any error messages or warnings related to performance issues. Look for any recent changes or events that might correlate with the onset of slowness.

Checking the alert log. You can check how many log switches are happening in one hour.

If you have more than 5 archives per hour we can say you may need to increase the redo log size. Troubleshoot the errors if it is critical or related to performance.

5. Check for any lock contention

You can use the below query for this.

SQL> select count(*) from v$lock where block=1;

·        If count is greater than one, lock is there in database.

·        Check with application team and release the blocking sessions

6. Locking is not only the cause to effects the performance. Disk I/O contention is another case.

·        When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as "db file sequential read" (for index scan) or "db file scattered read" (for full table scan).When you see the event, you know that the session is waiting for I/O from the disk to complete.

·        To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works.

·        Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.

        7. Collaborate with Senior DBAs or Support:

        If needed, escalate the issue to senior DBAs or Oracle Support for further assistance.

        

        8. Consult Documentation and Resources:

        Refer to Oracle documentation, blogs, or forums to troubleshoot specific issues related to Oracle performance tuning.

Advance performance tuning is not the scope of this blog, this is for junior DBA. Hope it helps.

Expecting your comments...

Find more about Tuning Click Here