Query optimizer some
times incorrect estimates about an statement due to lack of information ,due
to this poor execution plan.
Users can also manually add the hits to the application code
to guide the optimizer to make the correct plan.
Automatic SQL tuning
creates a profile for SQL statement , it is called as “ SQL PROFILE”
SQL profile address the problem by
collecting the additional information
while creating the SQLPROFILE.
The output of this type of analysis is a recommendation to
accept the SQL Profile. An SQL Profile, once accepted, is stored persistently
in the data dictionary. An SQL Profile is specific to a particular query. If
accepted, the optimizer under normal
mode uses the information in the SQL
Profile in conjunction with regular database statistics when generating an
execution plan.
DECLARE
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqlprofile_name :=
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name =>
'my_sql_tuning_task',
name => 'my_sql_profile');
END;
my_sql_tuning_task is the name of the SQL tuning task.
You can view information about a SQL Profile in the
DBA_SQL_PROFILES view.
Altering a SQL Profile:-
Alter the STATUS, NAME, DESCRIPTION, and CATEGORY
attributes of an existing SQL Profile
with the ALTER_SQL_PROFILE procedure.
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'my_sql_profile',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
/
In this example, my_sql_profile is the name of the SQL
Profile that you want to alter. The status attribute is changed to disabled
which means the SQL Profile will not be used during SQL compilation.
Dropping a SQL Profile :-
A SQL Profile can be dropped using the DROP_SQL_PROFILE
procedure.
begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name
=> 'my_sql_profile');
end;
there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql
which can be used for usage of SQL Tuning Advisor from the command line.
Create a SQL tuning
task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT * ' ||
'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text =>
my_sqltext,
user_name => 'SH',
scope =>
'COMPREHENSIVE',
time_limit => 60,
task_name =>
'TEST_sql_tuning_task',
description =>
'Task to tune a query on a specified PRODUCT');
END;
No of Available plan and
its sqlplan
select * from
table(dbms_xplan.display_awr('dzw8bvzhzwgnn'));
To check the sanp ID where wrong plan ID picked.
SELECT snap_id,SQL_PROFILE,OPTIMIZER_COST,ROWS_PROCESSED_TOTAL,ELAPSED_TIME_DELTA
FROM dba_hist_sqlstat WHERE sql_id = '&Sql_id' AND plan_hash_value = '
&PLAN_ID' ORDER BY 1
TO check List of plan
ID’s for given SQLID.
SET PAUSE ON
SET PAGESIZE 60
SET LINESIZE 300
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
(
SELECT /*+
NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
FROM
dba_hist_snapshot ss
WHERE
ss.begin_id in (&sql_id)
TO check the no.of
available plans and recommendations for given SQLID and it will create the
tunning task, later we can accept the tuning task.
@?/rdbms/admin/sqltrpt.sql
execute dbms_sqltune.accept_sql_profile(task_name =>
'TASK_70456',task_owner => 'SYS', replace => TRUE);
TO create the Tuning
task manually for Given SQLID:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id
:= DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dzw8bvzhzwgnn',
scope =>
DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name
=> 'dzw8bvzhzwgnn_tuning_task',
description => 'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
TO create the Tuning
task manually for Given snaps which is the best plan in between snaps:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id :=
DBMS_SQLTUNE.create_tuning_task (
sql_id => 'dzw8bvzhzwgnn',
begin_snap
=> 58827,
end_snap
=> 59118,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dzw8bvzhzwgnn_tuning_task_AWR',
description =>
'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: '
|| l_sql_tune_task_id);
END;
/
TO create the Tuning
task manually for Given SQL TEXT :
DECLARE
l_sql_tune_task_id VARCHAR2(100);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT statement copy here';
l_sql_tune_task_id
:= DBMS_SQLTUNE.create_tuning_task (
sql_text => my_sqltext,
scope
=> DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'dzw8bvzhzwgnn',
description =>
'Tuning task for statement dzw8bvzhzwgnn.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: '
|| l_sql_tune_task_id);
END;
/
TO drop Tuning task:
SQL> begin DBMS_SQLTUNE.drop_tuning_task (task_name =>
'TASK_70455');
2 end;
3 /
TO accept Tuning
Task:
execute dbms_sqltune.accept_sql_profile(task_name =>
'dzw8bvzhzwgnn_tuning_task');
TO create the SQL
base Line for a TASK:
execute dbms_sqltune.create_sql_plan_baseline(task_name
=> 'TASK_70467', owner_name => 'SYS', plan_hash_value => 1765760088);
TO check currently running sql ID which
plan ID using:
select
PLAN_HASH_VALUE from v$sql where
sql_id='dzw8bvzhzwgnn';
select
TIMESTAMP,PLAN_HASH_VALUE,time from
V$SQL_PLAN where SQL_ID='dzw8bvzhzwgnn';
select sql_text, plan_name, enabled, accepted from
dba_sql_plan_baselines;
Good Article
ReplyDelete