Een SQL tuning set is een groep van statements samen met hun executie context. Deze kunnen automatisch met de Enterprise Manager gemaakt worden of handmatig indien je de nodige privileges hebt:

CONN sys/password AS SYSDBA
GRANT ADMINISTER ANY SQL TUNING SET TO scott;

Een SQL tuning set wordt aangemaakt met de CREATE_SQLSET procedure:

CONN scott/tiger 
BEGIN
DBMS_SQLTUNE.create_sqlset (
sqlset_name => 'test_sql_tuning_set',
description => 'A test SQL tuning set.');
END;
/

Statements worden toegevoegd aan de set met de LOAD_SQLSET procedure welke een REF CURSOR van statements verkregend door gebruik te maken van een van de volgende pipelined functions:

· SELECT_WORKLOAD_REPOSITORY - Verkrijgt statements van de Automatic Workload Repository (AWR).

· SELECT_CURSOR_CACHE - Verkrijgt statements van de cursor cache.

· SELECT_SQLSET - Verkrijgt statements van een andere SQL tuning set.

En een voorbeeld van hun gebruik:

-- Laad de SQL set van de Automatic Workload Repository (AWR).
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_workload_repository (
765, -- begin_snap
766, -- end_snap
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
10) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set',
populate_cursor => l_cursor);
END;
/

-- Laad de SQL set van de cursor cache.
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
1) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set',
populate_cursor => l_cursor);
END;
/

-- creëer een nieuwe set en laad het van een bestaande.
DECLARE
l_cursor DBMS_SQLTUNE.sqlset_cursor;
BEGIN
DBMS_SQLTUNE.create_sqlset(
sqlset_name => 'test_sql_tuning_set_2',
description => 'Another test SQL tuning set.');

OPEN l_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.select_sqlset (
'test_sql_tuning_set', -- sqlset_name
NULL, -- basic_filter
NULL, -- object_filter
NULL, -- ranking_measure1
NULL, -- ranking_measure2
NULL, -- ranking_measure3
NULL, -- result_percentage
NULL) -- result_limit
) p;

DBMS_SQLTUNE.load_sqlset (
sqlset_name => 'test_sql_tuning_set_2',
populate_cursor => l_cursor);
END;
/

De inhoud van een SQL tuning set kan bekeken worden met de SELECT_SQLSET functie:

SELECT *
FROM TABLE(DBMS_SQLTUNE.select_sqlset ('test_sql_tuning_set'));

Referenties kunnen aan een set toegevoegd worden om zijn gebruik door een cliënt aan te geven met de ADD_SQLSET_REFERENCE functie. Het resultaat reference ID kan gebruikt worden om het te verwijderen met de REMOVE_SQLSET_REFERENCE procedure:

DECLARE
l_ref_id NUMBER;
BEGIN
-- Add a reference to a set.
l_ref_id := DBMS_SQLTUNE.add_sqlset_reference (
sqlset_name => 'test_sql_tuning_set',
reference => 'Used for manual tuning by SQL*Plus.');

-- Delete the reference.
DBMS_SQLTUNE.remove_sqlset_reference (
sqlset_name => 'test_sql_tuning_set',
reference_id => l_ref_id);
END;
/

De UPDATE_SQLSET procedure wordt gebruikt om bepaalde string (MODULE en ACTION) en nummer (PRIORITY en PARSING_SCHEMA_ID) attributen van specifieke statements in een set te updaten:

BEGIN
DBMS_SQLTUNE.update_sqlset (
sqlset_name => 'test_sql_tuning_set',
sql_id => '19v5guvsgcd1v',
attribute_name => 'ACTION',
attribute_value => 'INSERT');
END;
/

De inhoud van een set kan ingekort worden of helemaal verwijderd met de DELETE_SQLSET procedure:

BEGIN
-- Delete statements met minder dan 50 executies.
DBMS_SQLTUNE.delete_sqlset (
sqlset_name => 'test_sql_tuning_set',
basic_filter => 'executions < 50');

-- Verwijder alle statements.
DBMS_SQLTUNE.delete_sqlset (
sqlset_name => 'test_sql_tuning_set');
END;
/
Tuning sets kunnen verwijderd worden met de DROP_SQLSET procedure:
BEGIN
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set');
DBMS_SQLTUNE.drop_sqlset (sqlset_name => 'test_sql_tuning_set_2');
END;
/