Scenario 2: What will you do if a SQL statement preferably an Update Statement is taking a long time to execute ?

Hi guys, This is a one of the most common Oracle DBA interview questions you will most likely to expect if you are going to attend a technical interview. This question is popping in every where because this issue is affecting almost everyone and this is going to be a nightmare if the database is extremely large and has millions of records. So before you face this kinda situation, keep calm and read the below solution.

SOLUTION:

1. Analyse Table is deprecated so Use DBMS_STATS because it is faster and more efficient
Before DBMS_STATS was introduced in Oracle 8i, there were 2 main methods which were used to calculate the statistics and over all execution plan namely Rule based Optimizer (RBO) and Cost Based Optimizer (CBO). Anyway the point is RBO is deprecated as they tend to mess up the overall SQL execution plan resulting in serious performance issues. The "Analyze Table" command follows the CBO method so this is where DBMS_STATS come into the picture.
Basically, with the DBMS_STATS it is possible to view and modify optimizer statistics gathered for database objects. Below is a image from http://www.dba-oracle.com explaining the DBMS_STATS using CBO method.

image courtesy of http://www.dba-oracle.com/t_dbms_stats.htm

NOTE:
You can read the entire article at http://www.dba-oracle.com/t_dbms_stats.htm

2. Review the SQL Execution Plans

The Next step is to understand the logic behind the update statement and finding out what is causing Update Statement to lag. This can be done by the below command. 

explain plan for <your query>;
select * from table(dbms_xplan.display);

3. Use Parallelism for DML statements

It is a good thing to use parallelism for executing the DML statements as it is much faster and more efficient than single thread trying to execute a long DML statement. For example of how parallelism works:

NOTE:

You can read the original article of how parallelism works in oracle database @ http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

4. Break the SQL Statements into smaller Batches and run them via Crontab

5. Use Solid State Drives for Buffers 

Try using a Solid State Drive (SSD) for the buffers as SSD have more performance than normal RAID hard dish

6. Try to Simplify the Update Statement
You can try to simplify the the overall DML statement by using SET operators such as UNION, MINUS and INTERSECT etc... and Logical operators such as AND, OR, NOT and maybe relational operators just in case to simplify your update statment.
Try to avoid "IN, NOT IN" commands as they tend to bring in a lot of values into the picture.

That is all pretty much I could think of. If you have better suggestions, please leave it in the comment box below and also share this post if you find it useful.