How to run SQL Tuning Advisor in OEM 13c

Introduction

In every organization, Database performance is crucial for maintaining smooth operations. The application team will always be happy if their queries take very little time for execution and they are getting results quickly. Unfortunately, this is not possible every time for them and they will straight away come to the DBA team if they are facing any query slowness issues.

Sometimes DBA can easily identify the issue, but what if they need more recommendations which help resolve the long-running queries? Here SQL tuning advisor comes into the picture which shares the best possible recommendation that helps the DBA to resolve the issue.

In this article, we will explore How to Run Sql Tuning Advisor in OEM 13c to enhance query performance effectively. It would be best if you had OEM 13c installed on your server for that first you have to download OEM 13c as per your requirement like in Linux, Windows, etc.

How to Run SQL Tuning Advisor in OEM 13c

Step 1) Login to the OEM console through the SYSMAN user.

Step 2) You will get the below screen after you log in to the console.

Step 3) Go to the Targets tab and click on All Targets.

Step 4) Once you click on All Target a new page will appear, you need to click on Database Instance to find out the database name where you need to connect and check out the Problematic SQL_ID.

Step 5) You can check the database running status under target status whereas on the right side, you will get the target database name which you can connect by just clicking on it.

Step 6) You will get the below prompt after clicking on the Database Instance and we need to use the tabs which are marked as 2 to get all the details of the specific database.

Step 7) Click on the below tab to find out the SQL_ID details.

Performance –> Performance Hub –> SQL Monitoring

Step 8) You need to put the credentials to the database. Please try to use SYSTEM or SYS user during login.

Step 9) Now you need to check for the problematic SQL_ID which has been provided by the application team

In my case, I am looking for this csypcc6h8ynqu

Note: – Once we get the SQL_ID then we need to click on the Tune SQL tab to get the recommendations.

Step 10) You will get the below option Total Time Limit which you can change accordingly as needed, by default it will be set as 30 minutes. Once all set click on Submit button.

Step 11) You can see from the below output SQL Tuning Advisor task is in progress. So you can with till it completed.

This is the way of How to Run SQL Tuning Advisor in OEM 13c

Step 12) Now we got some recommendations from Oracle end that we need to gather the statistics for the Index because the optimizer requires up-to-date statistics for the index to select a good execution plan.

Step 13) You can click on the Implement tab so that Optimizer will get the up-to-date statistics to select a good execution plan that boosts the performance.

Note: – Before doing any implementation get the approval from the application first and then do it.

Execution Plan Details Output

So, this concludes our practical of How to Run SQL Tuning Advisor in OEM 13c

How to Run SQL Tuning Advisor Manually

We have seen in the above steps How to run SQL Tuning Advisor in OEM 13c and now we can see the steps of how to Run SQL Tuning Advisor Manually with the help of the commands.

I will take the same SQL_ID (csypcc6h8ynqu) which I have taken in the above steps.

Step 1) First we will check if there is a plan flip for the SQL_ID.

Command

alter session set nls_date_format='mm/dd/yyyy hh24:mi';
SELECT
a.sql_id,
TO_CHAR (begin_interval_time, 'mm/dd/yyyy hh24:mi'),
TO_CHAR (end_interval_time, 'mm/dd/yyyy hh24:mi'),
plan_hash_value,
executions_delta,
rows_processed_delta,
ROUND (elapsed_time_delta / 1000000) / executions_delta
"Elapsed_per_exec_sec"
FROM dba_hist_sqlstat a, dba_hist_snapshot b
WHERE sql_id IN ('&SQL_ID')
AND a.snap_id = b.snap_id
AND executions_delta > 0
ORDER BY 2;

Output

Step 2) There is no plan flip for the SQL_ID, If there was a plan flip then we need to identify and pin the good plan. Now here we need to check the stats of the Table if they are gathered properly.

Command of Table Statistics

SET LINES 200 PAGES 200
COL OWNER FOR A30
COL TABLE_NAME FOR A30
select table_name,owner, stale_stats, last_analyzed from dba_tab_statistics where TABLE_NAME='&TABLE_NAME';

Output of Table Statistics

Note: – Table stats are not in STALE state. So we are good here.

Command of Index Statistics

SET LINES 200 PAGES 200
COL OWNER FOR A30
COL TABLE_NAME FOR A30
COL INDEX_NAME FOR A30
select table_name,owner,index_name, stale_stats, last_analyzed from dba_ind_statistics where TABLE_NAME='&TABLE_NAME';

Output of Index Statistics

Note:- We can see from the above output STATS has not gathered for the INDEX_NAME.

Step 3) Now we can run the SQL TUNING ADVISOR manually as well to check more recommendations.

Note:- Before that you need to check if you have an Active license to use this feature for that you can simply use the below command.

It should be DIAGNOSTIC+TUNING then only you can use this feature.

Command

show parameter control_management_pack_access

Output

Step 4) Now first we need to Declare the SQL tuning task.

Command

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&SQL_ID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => '&TASK_NAME',
description => 'Tuning task for statement &SQL_ID');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Output

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '&SQL_ID',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => '&TASK_NAME',
description => 'Tuning task for statement &SQL_ID');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
11 12 /
Enter value for sql_id: csypcc6h8ynqu —————> Enter the value of the SQL_ID
old 5: sql_id => '&SQL_ID',
new 5: sql_id => 'csypcc6h8ynqu',
Enter value for task_name: csypcc6h8ynqu_tuning_task —-> Enter the value of Task Name
old 8: task_name => '&TASK_NAME',
new 8: task_name => 'csypcc6h8ynqu_tuning_task',
Enter value for sql_id: csypcc6h8ynqu
old 9: description => 'Tuning task for statement &SQL_ID'); —-> Enter the value of SQL_ID for statement
new 9: description => 'Tuning task for statement csypcc6h8ynqu');

PL/SQL procedure successfully completed.

Step 5) Now we need to Execute the SQL tuning task.

Command

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&TASK_NAME');

Output

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&TASK_NAME');
Enter value for task_name: csypcc6h8ynqu_tuning_task —-> Enter the value of Task Name

PL/SQL procedure successfully completed.

Step 6) Now we will check the recommendation of the SQL tuning task created.

Command

SET LONG 9999999
SET PAGESIZE 1000
SET LINESIZE 300
SET longchunksize 20000
col RECOMMENDATIONS for a200
SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;

Output

Note: – We can see from the above output that we are getting the same recommendation that we are getting from OEM 13c that the optimizer requires up-to-date statistics for the index to select a good execution plan.

Step 7) Now after approval from the application team you can run the below command to gather the INDEX stats and then ask the application team again to re-run their query.

Command

execute dbms_stats.gather_index_stats(ownname => 'JBULLET', indname =>'SYS_C007400', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Output

SQL> execute dbms_stats.gather_index_stats(ownname => 'JBULLET', indname =>'SYS_C007400', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

Step 8) Now again we need to check the INDEX Statistics

Output

We can see from the output INDEX Stats have been gathered now.

Note: – You will get different recommendations as well apart from this one like there is a better execution plan, Table statistics, Creation of Index because of Full table scan, and many more. So you need to share these details with the application team and upon their approval only proceed with the steps provided by Oracle.

Conclusion

We have seen both methods step by step today like How to run SQL Tuning Advisor in OEM 13c and How to run SQL Tuning Advisor Manually.

Please note that using any items or information from this website is completely voluntary and at your own risk. They are provided purely for educational reasons. Although we have tested them internally, we cannot guarantee that they will perform as intended for you. Before applying, it is highly advised that you conduct a test in your non-production environment.