Automatische SQL Tuning in Oracle Database 10g - SQL Tuning Advisor
Om de SQL tuning advisor API te kunnen gebruiken moet een user het ADVISOR privilege krijgen:

 

CONN sys/password AS SYSDBA
GRANT ADVISOR TO scott;

Wanneer je de SQL tuning advisor wilt gebruiken moet je eerst een nieuwe tuning task aanmaken met de CREATE_TUNING_TASK functie. Het statement wat je wilt analyseren kan verkregen worden uit de Automatic Workload Repository (AWR), de cursor cache en SQL tuning set of handmatig opgegeven worden:

SET SERVEROUTPUT ON

-- Tuning task gecreëerd voor een specifiek a statement uit de AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 764,
end_snap => 938,
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_AWR_tuning_task',
description => 'Tuning task voor statement 19v5guvsgcd1v in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task gecreëerd voor een specifiek a statement uit de cursor cache.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '19v5guvsgcd1v',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => '19v5guvsgcd1v_tuning_task',
description => 'Tuning task voorstatement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task gecreëerd uit een SQL tuning set.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sqlset_name => 'test_sql_tuning_set',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'sqlset_tuning_task',
description => 'Tuning task for an SQL tuning set.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

-- Tuning task gecreëerd voor een handmatig gespecificeerd statement.
DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT e.*, d.* ' ||
'FROM emp e JOIN dept d ON e.deptno = d.deptno ' ||
'WHERE NVL(empno, ''0'') = :empno';

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'scott',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task',
description => 'Tuning task voor een EMP naar DEPT join query.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Als de TASK_NAME parameter wordt gespecificeerd zal deze waarde te zien zijn als de SQL tune task identifier. Als deze niet gegeven wordt dan zal een door het systeem gegenereerde naam "TASK_1478" te zien zijn. Als de SCOPE parameter wordt gezet naar scope_limited zal de SQL profiling analyse ontbreken. De TIME_LIMIT parameter geeft simpelweg de tijd aan dat de optimizer kan besteden om aanbevelingen te geven. De volgende voorbeelden zullen refereren aan de laatste tuning set omdat het geen externe afhankelijkheden heeft anders dan het SCOTT schema. De NVL uit het SQL statement is daarin gezet om een reactie van de optimizer te forceren. Als extra aanmoediging kunnen we nog de statistieken van een van de tabellen verwijderen.

EXEC DBMS_STATS.delete_table_stats('SCOTT','EMP'); 

Met het definiëren van de tuning task zal de volgende stap die uitgevoerd wordt, gebruik maken van de EXECUTE_TUNING_TASK procedure:


EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'emp_dept_tuning_task');

Gedurende de executie fase kan je de task pauzeren en herstarten,afbreken of hem resetten om hem weer opnieuw
te kunnen executen:

-- Onderbreken en herstarten van een tuning task.
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => 'emp_dept_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => 'emp_dept_tuning_task');

-- Een tuning task afbereken.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => 'emp_dept_tuning_task');

-- Een tuning task resetten om hem opnieuw te kunnen laten executen.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => 'emp_dept_tuning_task');

De status van de tuning task kan bekeken worden met de DBA_ADVISOR_LOG view:

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';

TASK_NAME STATUS
------------------------------ -----------
emp_dept_tuning_task COMPLETED

1 row selected.


ALs de tuning task eenmaal succesvol heeft gelopen kunnen de aanbevelingen bekeken worden met
de REPORT_TUNING_TASK function:

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SET PAGESIZE 24
SELECT DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task') AS recommendations FROM dual;
-- In dit geval zal de output eruit zien als volgt: RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : emp_dept_tuning_task
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 05/06/2004 09:29:13
Completed at : 05/06/2004 09:29:15

-------------------------------------------------------------------------------
SQL ID : 0wrmfv2yvswx1
SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE NVL(empno, '0') = :empno

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
Table "SCOTT"."EMP" and its indices were not analyzed.

Recommendation
--------------
Consider collecting optimizer statistics for this table and its indices.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)

Rationale
---------
The optimizer requires up-to-date statistics for the table and its indices
in order to select a good execution plan.

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan
contains an expression on indexed column "EMPNO". This expression prevents
the optimizer from selecting indices on table "SCOTT"."EMP".

Recommendation
--------------
Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.

Rationale
---------
The optimizer is unable to use an index if the predicate is an inequality
condition or if there is an expression or an implicit data type conversion
on the indexed column.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1863486531

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

-------------------------------------------------------------------------------


1 row selected.

Als de tuning sessie eenmaal over is, kan de tuning task verwijderd worden met
de DROP_TUNING_TASK procedure:

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');
DBMS_SQLTUNE.drop_tuning_task (task_name => '19v5guvsgcd1v_tuning_task');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqlset_tuning_task');
DBMS_SQLTUNE.drop_tuning_task (task_name => 'emp_dept_tuning_task');
END;
/
 

Advertentie

>

Poll

Voorkeur
 

Wie is er aanwezig

We hebben 81 gasten online