SQL Profiling - An Example
Posted at Wednesday, June 20, 2007
Often times I find myself in situations where poor SQL is submitted to the database on behalf of 3rd-party applications that generate code on the fly. One of the many tools that I find useful in responding to this is SQL Profiling. I have also found SQL Profiling to be effective in tuning SQL statements created by applications that have transitioned from rule-based to cost-based optimization without the requisite performance testing. Below are the sequence of steps that I use to determine and accept whether a SQL Profile is a viable option worth exploring for a particular sql statement:
First Create the SQL Tuning Task:
set serverout on;
DECLARE
stti VARCHAR2(100); /* Sql Tuning Identifier */
BEGIN
stti := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '[sql id here]',
plan_hash_value => [hash value here],
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
time_limit => DBMS_SQLTUNE.TIME_LIMIT_DEFAULT,
task_name => '[task name here]',
description => '[description here]');
DBMS_OUTPUT.PUT_LINE('SQL Tuning Identifier:'||stti);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception: '||sqlerrm);
END;
/
Execute the previously created task:
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'[task name here]');
END;
/
Examine the results of the Tuning Task:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '[task name here]') FROM DUAL;
Implement (accept) the profile (if applicable):
BEGIN
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name =>'[task name here]', replace => TRUE);
END;
/
If you need to drop the profile:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name =>'[name from dba_sql_profiles]');
END;
/
Labels: Tuning
_507.png)
Subscribe
