This is another SQL Tuning case, which was to tune a SQL caused high load in the database server.

Problem

We noted there was high CPU load in the database server. The database and application became slow as a consequence.

Trouble shooting

Launch SQLBooster, connect to the target database, click "Top SQL", it showed as below.

We can see there were 70+ sessions running a same SQL, and consumed 43 CPU cores. Click "OK" to check this SQL

The buffer gets is very high, and teh average CPU time is also unusal. 

Click "Var" to check the captured bind variables lists, check their performance statistics data, and choose one of them as the test case.

The click "SQL Tree" tab to parse the SQL to a SQL tree, and click "Analyze" to analyze the target SQL. We got an analyze report half hour later. 

Tips: You can go to File->Preferences to set how many times can the analyzing subqueries be timeout.

click the first highlighted subquery, we can see it has 2.3M consistent gets in 1 minute, and was cancelled due to timeout setting.

Look the query, we noted it involves anlother subquery. Then go to the SQL Tree, we can see this subquery actually was running fast and only returned 33 rows. That means the Oracle SQL optimizer might did an incorrect Query Transforming. Let add some hints to fix it.

• a NO_MERGE hint will tell the Oracle SQL Optimizer not to merge the subquery
• a LEADING hint to guarantee the join sequence of the table/view/subquery 
• a USE_NL to for the Optimizer to choose NESTED LOOP join

Then, let see what will happen.

It fetched 29 rows in 2 seconds, and only has 7566 Consistent Gets. It looks good! Let's apply these hints to the original SQL to see if it can solve the problem.

Hmm, it's not good as we expected. We have to stop the running after 1 minutes. That means there is still some problems in somewhere of this SQL. So, we reparsed the SQL with the added hints and analyze it again!

There is another subquery got timeout! Let's review this query. Check the SQL Tree, we noted the WITH inline view "o2" is in a good status and only returned 33 rows. Let's add some hints to adjust the join sequence and join method.

It returned 423976 rows in 20 seconds, got 883359 Consistent Gets. It's not bad! Let apply this change to the original SQL again.

Bingle! It fetched 23 rows in 5 seconds, and only got 41115 Consistent Gets. This is what we wanted.

After the tuned SQL was applyed to the database. The high load sessions are gone, and the application was back to normal.