SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 218

These discovered suggestions are profiled in the database as corrections to poor performing queries. This
information helps the optimizer improve selectivity estimates, and this helps the optimizer select better plans. The
SQL profile provides the following benefits:

• Profiles prevent hard mapping of the optimizer to specific plans or subplans generated due to

hard coded hints and stored outlines.

• Profiles help fix incorrect estimates while giving the optimizer the options to select the best

plans based on the conditions posed.

No changes to the code are required when profiles are used. Oracle manages the changed plans for a query in the
data dictionary and reuses the profile based on SQL_ID , keeping the use of the SQL profile transparent to the user. To
understand this better, we take an example and discuss the process of generating an SQL profile.

Workshop—SQL Tuning Using SQL Tuning Advisor
In this workshop, we use the SQL tuning advisor option and try to optimize an SQL statement.

Step 1
The following SQL statement was executed in a production environment:

SELECT /*+ NOPARALLEL */ N_NAME, R_NAME, P_NAME, S_NAME, SUM(PS_SUPPLYCOST) FROM REGION, NATION,
SUPPLIER, PARTSUPP, PART WHERE PS_PARTKEY=P_PARTKEY AND PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY =
N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY GROUP BY N_NAME,R_NAME,P_NAME,S_NAME;
Plan hash value: 710495980
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost(%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228M| 29G| | 7336K (1)| 24:27:18 |
| 1 | HASH GROUP BY | | 228M| 29G| 31G| 7336K (1)| 24:27:18 |
|* 2 | HASH JOIN | | 228M| 29G| | 351K (1)| 01:10:19 |
| 3 | TABLE ACCESS FULL | REGION | 10 | 270 | | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 114M| 11G| | 350K (1)| 01:10:10 |
| 5 | TABLE ACCESS FULL | NATION | 50 | 1450 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 57M| 4458M| 83M| 350K (1)| 01:10:06 |
| 7 | TABLE ACCESS FULL | SUPPLIER| 2040K| 60M| | 10493 (1)| 00:02:06 |
|* 8 | HASH JOIN | | 28M| 1372M| 343M| 251K (1)| 00:50:19 |
| 9 | TABLE ACCESS FULL| PART | 7204K| 261M| | 37669 (1)| 00:07:33 |
| 10 | TABLE ACCESS FULL| PARTSUPP| 28M| 354M| | 162K (1)| 00:32:34 |

If the plan is analyzed, it should be clear that with the NOPARALLEL hint the query took over 24 minutes to
complete execution. This is really expensive considering that the number of rows in the database is not very high. Now
we try to use the SQL tuning advisor to help fix this query for better efficiency.