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

Saturday 17 September 2016

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. 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

3. 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.

4. Check the resource utilization:

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.

5. Check the alert log:

Check 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.

6. Server side checks

Check for the Memory, Paging, I/O utilisation from server side.

Paging and memory can be checked by ‘top’ command and ‘iostat’ will do the I/O statistics.

Contact the concern team for any abnormality if you see in this.

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