SQL Profiles are more preferred than OUTLINE as it is simple to use and manage.
You may find my below steps useful to start with SQL Profile. I used this to tune a single query to generate & apply SQL Profile to improve the execution plan.
Note : Here I am trying to generate SQL Profile for one single SQL statement. If you want to generate SQL Profile for multiple SQL statements then you need to create a SQL Tuning Set and then run this.
a. Create a new STS
b. Load the STS
c. Select the STS to review the contents
d. Update the STS if necessary
e. Create a tuning task with the STS as input
Overall Logical Steps to create a SQL Profile through SQL Tuning Advisor
1) You use the CREATE_TUNING_TASK Functions to create a tuning task for tuning a single statement or a group of SQL statements.
2) The EXECUTE_TUNING_TASK Procedure executes a previously created tuning task.
3) The REPORT_TUNING_TASK Function displays the results of a tuning task.
4) You use the SCRIPT_TUNING_TASK Function to create a SQL*PLUS script which can then be executed to implement a set of Advisor recommendations
1. Creating a SQL Tuning Task
Method 1
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' 'FROM employees e, locations l, departments d ' 'WHERE e.department_id = d.department_id AND ' 'l.location_id = d.location_id AND ' 'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 1800,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
Method 2
DECLARE
my_task_name VARCHAR2(30);
my_sqlid varchar2(30);
my_planhashvalue number;
BEGIN
my_sqlid := 'a59zbsyutxfg1';
my_planhashvalue := 343073668;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => my_sqlid,
plan_hash_value => my_planhashvalue,
scope => 'COMPREHENSIVE',
task_name => 'my_sql_tuning_task',
time_limit => 1800,
description => 'Task to tune a query');
END;
Method 3
DECLARE
my_task_name VARCHAR2(30);
my_beginsnap number;
my_endsnap number;
my_sqlid varchar2(30);
my_planhashvalue number;
BEGIN
my_sqlid := 'ffpyzcn9809s9';
my_planhashvalue := 2377894994;
my_beginsnap := 20134;
my_endsnap := 20136;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap => my_beginsnap,
end_snap => my_endsnap,
sql_id => my_sqlid,
plan_hash_value => my_planhashvalue,
scope => 'COMPREHENSIVE',
task_name => 'my_sql_tuning_task',
time_limit => 1800,
description => 'Task to tune a query');
END;
2. Executing a SQL Tuning Task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
3. Checking the Status of a SQL Tuning Task
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
4. Checking the Progress of the SQL Tuning Advisor
SELECT sofar, totalwork
FROM V$ADVISOR_PROGRESS
WHERE task_name = 'my_sql_tuning_task';
5. Displaying the Results of a SQL Tuning Task
SET LONG 9999999
SET LONGCHUNKSIZE 99999999
SET LINESIZE 200
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;
6. Accept SQL Profile
The above report might advise SQL Profile if found anything.It can then be implemented using.
execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task', replace => TRUE);
6. How to manage(enable/disable) a SQL PROFILE
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =>'SYS_SQLPROF_014845b914845b98',
attribute_name => 'STATUS',
value => 'DISABLED');
END;
BEGIN DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name =>'SYS_SQLPROF_01487a4c1487a4cb',
attribute_name => 'STATUS',
value => 'ENABLED');
END;
Keywords : Oracle; SQL profile ;sqlprofile ; tune; query
Thursday, 13 August 2009
Saturday, 1 August 2009
Quick Commands to copy DATAFILE or ARCHIVELOG file from ASM to unix filesystem
In Oracle 10g,
RMAN Solution
I. To Copy Datafile from ASM to filesystem
run{
allocate channel c1 type disk;
copy datafile '+DATA01/datafile/dat01.577.687959027' to /tmp/dat01.577.687959027';
}
II. To Copy archive log file from ASM to filesystem
backup as copy archivelog from sequence 23676 until sequence 23680 format '/tmp/archivelog/informatica%h_%e_resetlogs.arc';
PLSQL Solution using DBMS_FILE_TRANSFER
Create a directory object to point the source
create or replace directory SDIR as '+DATA01/datafile/';
Note : In my example, the database datafile which i wanted to copy from ASM to filesystem was residing in '+DATA01/datafile/'
Create a directory object to point the destination
create or replace directory DDIR as '/tmp';
Copy the file
BEGIN
dbms_file_transfer.copy_file(source_directory_object =>'SDIR',
source_file_name => 'dat01.577.687959027',
destination_directory_object => 'DDIR',
destination_file_name => 'dat01.dbf');
END;
In Oracle 11g,
Apart from the above compatible commands, ASMCMD command line utility has been enhanced with "cp" copy command.
ASMCMD[+]>cp +DATA01/datafile/dat01.577.687959027 /tmp/dat01.dbf
Keywords : copy; file; asm; filesystem; datafile; archive; archivelog
RMAN Solution
I. To Copy Datafile from ASM to filesystem
run{
allocate channel c1 type disk;
copy datafile '+DATA01/datafile/dat01.577.687959027' to /tmp/dat01.577.687959027';
}
II. To Copy archive log file from ASM to filesystem
backup as copy archivelog from sequence 23676 until sequence 23680 format '/tmp/archivelog/informatica%h_%e_resetlogs.arc';
PLSQL Solution using DBMS_FILE_TRANSFER
Create a directory object to point the source
create or replace directory SDIR as '+DATA01/datafile/';
Note : In my example, the database datafile which i wanted to copy from ASM to filesystem was residing in '+DATA01/datafile/'
Create a directory object to point the destination
create or replace directory DDIR as '/tmp';
Copy the file
BEGIN
dbms_file_transfer.copy_file(source_directory_object =>'SDIR',
source_file_name => 'dat01.577.687959027',
destination_directory_object => 'DDIR',
destination_file_name => 'dat01.dbf');
END;
In Oracle 11g,
Apart from the above compatible commands, ASMCMD command line utility has been enhanced with "cp" copy command.
ASMCMD[+]>cp +DATA01/datafile/dat01.577.687959027 /tmp/dat01.dbf
Keywords : copy; file; asm; filesystem; datafile; archive; archivelog
Subscribe to:
Posts (Atom)