Тествано на 11.2.0.2
SQL tunning advisor е страшна далавера. Е, изисква допълнителен лиценз за tunning pack, но върши работа, която за обикновен човек е практически невъзможна.
По-честият вариант е когато нещо се скапе в production и трябва спешно да се оправи. Ех, колко пъти ни е спасявал SQL tunning advisor в такива ситуации… Хем намира решение (без да ти се налага да разбираш какво, аджеба, прави тази заявка); хем прави профил – така не се налага промяна в кода на самото приложение. Аз лично не съм се и опитвал ръчно да правя профил, супер пипкава работа ми изглежда. Правил съм ръчно outline и това беше пипкаво.
Има, обаче, и един друг сценарий. Представете си един горд developer, който е сътворил заявка с 97 реда execution plan. И моли за съдействие при оптимизацията. Това нещо е извън възможностите за възприемане. А SQL tunning advisor минава за минути и в повечето случаи наистина дава добри идеи. Как да го пуснеш, обаче?
През Grid/Cloud control е лесно – цъкаш, цъкаш и става. Но какво парим, ако го няма Could control закачен към базата? Или ако просто искаме да видим някой съвет за едно query, което ни мъчи?
Всъщност става много лесно:
1. Създаване на таск:
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. Изпълнение. Това може да отнеме доста време, в зависимост от сложността на заявката и зададения лимит:
exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test2');
3. Да видим резултата. Това е целта, всъщност
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")
(Да, наистина го пусках това за заявка с 97 стъпки в плана и 95 филтъра. Естествено тук съм отрязал както заявката, така и плановете и предикатите )
4. Може и да приложим предложения план. Аз в случая исках само да видя съвета, но пък за да се измери реалната промяна, трябва да се тества наистина с пускане на заявката
execute dbms_sqltune.accept_sql_profile(task_name => 'tune_test2', task_owner => 'HR', replace => TRUE);
Пастърмицата се получи. Сега ще споделя рецептата, докато не съм я забравил. Че в момента сушилника е зает с нещо друго, с което ще се похваля по-късно.
След това притиснах парчетата между две големи тави, с възможно най-голямата тежест, която успях да закрепя отгоре. Там стояха 24 часа, леко наклонени, да се отцедят и да придобият форма. След това ги овалях в следната богоугодна смес:
