This is another SQL Tuning case, which was to tune a SQL caused high load in the database server.
We noted there was high CPU load in the database server. The database and application became slow as a consequence.
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.
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.