This post is also available in: Bulgarian
This is tested on 11.2.0.2
SQL is a great piece of software. Well, it needs an additional license, but can handle problems that are practically impossible for a human being.
The most often case is when something goes terribly wring on production and has to be fixed urgently. SQL tunning advisor have saved us so many times in such situations… It usually finds some very good solution (without the need for the DBA to understand what this bloody SQL statement does at all); and makes an SQL profile for us, so we do not need to patch the application code.
There is, however, one more scenario. Imagine a brave developer, creating a mighty statement with 97 lines in the execution plan. He have thought for many hours or days on the problem. Then he comes for assistance with the optimization of this statement. I cannot even understand what this statement does without loosing some hours! But SQL tunning advisor completes in minutes and usually gives some quite good tips. But how can you run it?
It easy to do it through the Grid/Cloud control – some clicks and this is it. But what is the database is not monitored by OEM CC (this is a DEV db)?
In fact, it is not that hard:
1. Create a task:
DECLARE
v_task VARCHAR2(30);
v_sql CLOB;
BEGIN
v_sql := 'SELECT ... FROM ... WHERE ...';
v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => v_sql,
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 3600, -- seconds
task_name => 'tune_test2',
description => 'Tune statement used for the new XYZ functionality.');
END;
/
2. Execute the task. This may take some time, depending on the complexity of the statement and the limit given in the former step:
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');
3. Now let’s see the result
set long 90000 longchunksize 90000
set linesize 232 pagesize 9999
select dbms_sqltune.report_tuning_task('tune_test2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNE_TEST2')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tune_test2
Tuning Task Owner : HR
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status : COMPLETED
Started at : 10/05/2012 12:07:10
Completed at : 10/05/2012 12:12:13
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : ba6g55fakh01v
SQL Text : SELECT ...
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 76.48%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2',
task_owner => 'HR', replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2801951464
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1302 | 30711 (4)| 00:06:09 |
...
| 97 | TABLE ACCESS FULL | MY_TAB | 2 | 10 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
...
95 - access("E"."TEST_ID"="T"."TEST_ID")
2- Original With Adjusted Cost
------------------------------
-------------------------------------------------------------------------------
Error: cannot fetch explain plan for object: 1
-------------------------------------------------------------------------------
3- Using SQL Profile
--------------------
Plan hash value: 3439552619
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 1302 | 7223 (2)| 00:01:27 |
....
| 97 | TABLE ACCESS FULL | MY_TAB | 2 | 10 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
....
95 - access("E"."TEST_ID"="T"."TEST_ID")
(Yes, I really had to do this for a statement with 97 steps in the execution plan and 95 filters. Of course, I have removed the statement, plan and predicates here)
4. Now we can easily apply the plan. In my case I just wanted to see the advise. But you can never be 100% sure unless you run the whole statement. And even then 🙂
execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2', task_owner => 'HR', replace => TRUE);
Sorry, the comment form is closed at this time.