Tune your Query using Oracle SQL Profile

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

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

Unit size in DBA_HIST_TBSPC_SPACE_USAGE

Question : What is the unit of the below columns in DBA_HIST_TBSPC_SPACE_USAGE?
a. tablespace_size
b. tablespace_maxsize
c. tablespace_usedsize

Answer : They are reprensted in no. of blocks. In other words to get the actual size from this view you need to multiply the values with the DBA_TABLESPACES.BLOCK_SIZE

I used this simple query ( you can tune this query if you see it running slow) to get the tablespace growth report on weekly basis.

select /*+ parallel(c, 5) */
to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name,
round(max(a.tablespace_size*d.block_size)/1024/1024) tblsize_in_MB,
round(max(a.tablespace_maxsize*d.block_size)/1024/1024) totalsize_in_MB,
round(max(a.tablespace_usedsize*d.block_size)/1024/1024) usedsize_in_MB
from DBA_HIST_TBSPC_SPACE_USAGE a, v$tablespace b,
DBA_HIST_SNAPSHOT c, dba_tablespaces d
where a.tablespace_id=b.ts#
and a.snap_id=c.snap_id
and b.name=d.tablespace_name
and b.name in ('USERS')
group by to_char(c.BEGIN_INTERVAL_TIME,'MON'),
to_char(c.BEGIN_INTERVAL_TIME,'WW'),
b.name
order by 3,2,1;


Keywords : DBA_HIST_TBSPC_SPACE_USAGE, size, unit

STORED OUTLINE in Oracle 10g

Scope of this document

1. This applies to Oracle 10g
2. How to use STORED Outline to fix a performance issue?
3. Ho to change the plan in the outline after generating it?
4. Some Pros & Cons of STORED OUTLINE
   

This document also highlight some of the key facts that an Oracle DBA should be aware of while using STORED OUTLINE.

Facts

1. When the desired execution plan is occuring for a SQL statement which is not identical in SQL text (whitespacing and case differences ignored) to the original SQL statement, then a Stored Outline for that SQL is not usable as the signature of the Stored Outline does not match
2. In other words, STORED OUTLINE are driven by the SIGNATURE of an inidivual SQL statement.
3. To create private stored outline do not use the procedure DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES in Oracle10g and Oracle11g
   
4. DBMS_OUTLN.CREATE_OUTLINE procedure is hard parsing and using the optimizer environment of the session executing the procedure instead of using the one from the library cache. This is a BUG. Hence the stored outline created out of this procedure might differ from the original plan.
   
5. Unlike the traditional way, creating the stored outline in the lower environment and do export/import to get the outline to production, Oracle 10g has got the new way of creating it directly using the SQL ID, SQL_HASH_VALUE, SQL_CHILD_NUMBEr from the memory.
   
6. STORED OUTLINES doesnot work when you try it as SYS user.
   
7. Important : Though outlines are used and you follow all the steps correctly, NOT ALWAYS the execution plan is imposed as expected/desired.
   
8. When swapping the outline (to replace the BAD plan with a GOOD plan) ensure the HINTCOUNT column is taken care.
   
9. Generally you may have read CURSOR_SHARING = FORCE disables the use of stored outlines. Actually the interpretation should be the other way.
   i.e.
   STORED OUTLINE creation also captures the essential optimizer paramaters which is also assessed before overwriting a plan.
   If there is a mismatch then STORED OUTLINE will not be used for a SQL statement.
   For Eg.
   If OL1 is created with CURSOR_SHARING=FORCE, then a session with CURSOR_SHARING=EXACT/SIMILAR would not be able to use the outline OL1.
   In this scenario STORED OUTLINE is disabled.
   
10. PRIVATE outlines are automatically created in SYSTEM.OL$ table and this is a local temporary table. Hence the records vanishes the moment you exit the session
    
11. To use stored outline for your SQL statements a. alter session set use_stored_outlines=true; b. Ensure CURSOR_SHARING is NOT set to FORCE
    

Scenario

I have a single query which is using a BAD plan in the production database.You have the SQL_ID, SQL_HASH_VALUE & SQL_CHILD_NUMBER of this query which is using the BAD plan.

On the other side you either tune it by adding HINTS or have another version of this same query which is runinng fine in the same production database. What do I mean by another version of this same query?

For example, I ran the same SQL statement from another SQL prompt after substituting the BIND variables with the literalsand confirmed the execution plan was GOOD.

Important Note : This GOOD one was having different SQL_ID & SQL_HASH_VALUE

Goal of this article,

a. From the same database, create the OUTLINE out of the SQL statement which is having the GOOD plan

b. Make the original query adopt the above outline
Remember, all these steps are done in the same production database and out of the buffer cache.

Assumptions:

a. connect to a schema with CREATE ANY OUTLINE privilege

b. Perform these tasks as any schema other than SYS

c. Using DEFAULT category

d. The schema should have privilege to query v$sql & v$session

e. HINTCOUNT is same for both GOOD & BAD plan

Overall Steps

a) Generate the outline, name it OL1, for the BAD plan using the seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.

b) Generate an another outline, name it OL2, for the GOOD plan using seperate SQL_HASH_VALUE & SQL_CHILD_NUMBER.
Eg.
GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

Method 1

c) Update/Edit the outline OL2 by replacing with the values of OL1, so that both OL1 & OL2 should look alike.

Method 2

c) Rename the outline OL2 to OL1 and viceversa.

Method 3

c) Create a new private outline "PRIVOL2" out of OL2

d) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Method 4

c) Create a new private outline "PRIVOL1" out of OL1

d) Create a new private outline "PRIVOL2" out of OL2

e) Edit the private outline

f) resynchronize the private Stored Outline

g) Replace the original outline OL2 with this edited private outline "PRIVOL2"

Detailed Steps

create table emp (empno number, ename varchar2(10)) tablespace users01;

create index empidx on emp(empno,ename) tablespace users01;

begin
for i in 1..200 loop
insert into emp values (i,'blake');
end loop;
commit;
end;
/

begin
for i in 1..200 loop
insert into emp values (i,'scott');
end loop;
commit;
end;
/

execute DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT', tabname => 'EMP', estimate_percent => 100, cascade => TRUE);

Open two seperate sqlplus session as SCOTT user
In Session 1 : select distinct sid from v$mystat; ----- Lets assume the SID # 514

In Session 1 : select * from emp;

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

In session 1 : select * from emp where ename='scott';

In session 2 :

select sid,serial#,sql_id,sql_child_number,sql_hash_value,prev_sql_id,prev_hash_value,prev_child_number from v$session where sid=514;

From the above exercise we got SQL ID, SQL HASH VALUE and CHILD NUMBER of both GOOD & BAD sql statement.

GOOD : select * from emp; ===> OL1

BAD : select * from emp where ename='scott'; ===> OL2

SQL_ID SQL_HASH_VALUE SQL_PLAN_HASH_VALUE SQL_CHILD_NUMBER SQL_TEXT============== ============== =================== ================ =====================================a2dk8bdn0ujx7 1745700775 2872589290 0 select * from emp;7h38thzz3wug9 4265503209 818394873 0 select * from emp where ename='badri'
select * from TABLE(dbms_xplan.display_cursor('a2dk8bdn0ujx7',0));
Plan hash value: 2872589290
-------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time -------------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 TABLE ACCESS FULL EMP 60 540 2 (0) 00:00:01

select * from TABLE(dbms_xplan.display_cursor('7h38thzz3wug9',0));
Plan hash value: 818394873
--------------------------------------------------------------------------- Id Operation Name Rows Bytes Cost (%CPU) Time --------------------------------------------------------------------------- 0 SELECT STATEMENT 1 (100) * 1 INDEX FULL SCAN EMPIDX 20 180 1 (0) 00:00:01

Goal : To make the BAD sql statement to use FULL TABLE SCAN instead of INDEX FULL SCAN.
Steps Common to ALL methods

1) Create outline using the SQL HASH VALUE & CHILD NUMBER

alter session set create_stored_outlines=true;

exec dbms_outln.CREATE_OUTLINE(1745700775,0);

exec dbms_outln.CREATE_OUTLINE(4265503209,0);

alter session set create_stored_outlines=false;

2) Query them to confirm the presence
col ol_name form a30

col sql_text form a40

col category form a10

col hint_text form a75

set lines 200
select ol_name,hintcount,HASH_VALUE,CATEGORY,HASH_VALUE2,SIGNATURE,sql_text from outln.ol$;

select ol_name,hint#,hint_text from outln.ol$hints order by 1,2;

3) Rename them for easy use

alter outline SYS_OUTLINE_09061700365898670 rename to OL1;

commit;

alter outline SYS_OUTLINE_09061700365899172 rename to OL2;

commit;

Method 1

UPDATE outln.ol$hints a SET a.hint_text=(select b.hint_text from outln.ol$hints b where b.ol_name='OL1' and a.hint#=b.hint#) where a.ol_name='OL2'; and a.hint#=1;

commit;

Method 2

UPDATE outln.ol$hints SET ol_name = decode(ol_name, 'OL1','OL2','OL2','OL1') WHERE ol_name in ('OL1','OL2');

commit;

Method 3

create private outline PRIVOL2 from OL2;

commit;

update system.ol$hints set hint_text='FULL(@"SEL$1" http://www.blogger.com/'mailto: where ol_name='PRIVOL1' and hint#=6;

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

create or replace outline OL2 from private PRIVOL2;

Method 4

create private outline PRIVOL1 from OL1;

commit;

create private outline PRIVOL2 from OL2;

commit;

UPDATE system.ol$hints SET ol_name = decode(ol_name, 'PRIVOL1','PRIVOL2','PRIVOL2','PRIVOL1') WHERE ol_name in ('PRIVOL1','PRIVOL2');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL1');

commit;

execute dbms_outln_edit.refresh_private_outline('PRIVOL2');

commit;

create or replace outline OL2 from private PRIVOL2;

Keywords:

stored; outline; create;memory ; cache; edit;swap;dbms_outln.CREATE_OUTLINE