Get SQL ID
===========
select distinct hash_value, sql_id, sql_text
from v$sql
where sql_text like ‘%pktable_cat%’
and sql_text not like ‘select distinct hash_value, sql_id%’;
Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘0j3dqg3a8aj8c’,
scope => ‘COMPREHENSIVE’,
time_limit => 3600,
task_name => ‘my_sql_tuning_task_1’,
description => ‘Tune query using sqlid’);
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_1’);
end;
/
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
–***************************
–TO GET SUMMARY INFORMATION
–***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_1’) from DUAL;
–***************************
–TO GET DETAILED INFORMATION
–***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘my_sql_tuning_task_1′,’TEXT’,’ALL’,’ALL’) FROM DUAL;
— Accept the profile <<< If you get one…
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task_1’,
name => ‘my_sql_profile’);
END;
/
###
If necessary you can drop the tuning task.
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(‘my_sql_tuning_task_1’);
end;
/
If necessary (the profile does not help),
you can drop the profile.
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (‘MY_SQL_TUNING_TASK_1’);
END;
/
===========
select distinct hash_value, sql_id, sql_text
from v$sql
where sql_text like ‘%pktable_cat%’
and sql_text not like ‘select distinct hash_value, sql_id%’;
Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘0j3dqg3a8aj8c’,
scope => ‘COMPREHENSIVE’,
time_limit => 3600,
task_name => ‘my_sql_tuning_task_1’,
description => ‘Tune query using sqlid’);
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task_1’);
end;
/
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
–***************************
–TO GET SUMMARY INFORMATION
–***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task_1’) from DUAL;
–***************************
–TO GET DETAILED INFORMATION
–***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘my_sql_tuning_task_1′,’TEXT’,’ALL’,’ALL’) FROM DUAL;
— Accept the profile <<< If you get one…
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => ‘my_sql_tuning_task_1’,
name => ‘my_sql_profile’);
END;
/
###
If necessary you can drop the tuning task.
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(‘my_sql_tuning_task_1’);
end;
/
If necessary (the profile does not help),
you can drop the profile.
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE (‘MY_SQL_TUNING_TASK_1’);
END;
/
Recommended Posts
