How to pin a desired Plan for a query in Oracle database using SQL Profile

Overview

Starting since 10g people started preferring SQL Profile to pin the plan which seems to be more efficient than the traditional tool - STORED Outline

SQL Profile is more efficient, simple to use, more integrated to AWR.

Personally I feel SQL Profile is more convenient to use and my preferred tool to use as it is very quick and effective.

Methods to use

Method 1 - Below script uses SQL Profile to pin a plan to sqlid provided the plan is either available in memory or in AWR

PROMPT ============================== SQL Profile Inputs ==============================
set serveroutput on
ACCEPT dst_sql_id       PROMPT "Enter Destination SQL_ID: "
ACCEPT src_sql_id       PROMPT "Enter Source SQL_ID: "
ACCEPT plan_hash_value  PROMPT "Enter Source PLAN_HASH_VALUE: "
ACCEPT force_match      PROMPT "Enter FORCE_MATCH option (TRUE/FALSE), with TRUE equivalent to CURSOR_SHARING of FORCE: "
PROMPT ================================================================================
DECLARE
   c_prof_name_prefix  CONSTANT  VARCHAR2(5)  := 'SQLT';
   c_category          CONSTANT  VARCHAR2(10)  := 'DEFAULT';
   c_validate          CONSTANT  BOOLEAN       := TRUE;
   c_replace           CONSTANT  BOOLEAN       := TRUE;
   l_dst_sql_id           VARCHAR2(20)      := '&&dst_sql_id';
   l_src_sql_id           VARCHAR2(20)      := '&&src_sql_id';
   l_plan_hash_value      NUMBER            := &&plan_hash_value;
   l_plan_hash_value_chk  NUMBER;
   l_force_match_str      VARCHAR2(10)      := '&&force_match';
   l_profile_hints        SYS.SQLPROF_ATTR;
   l_dst_sql_text         CLOB;
   l_src_sql_text         CLOB;
   l_signature            NUMBER;
   l_force_match          BOOLEAN;
   l_profile_name         VARCHAR2(30)      := c_prof_name_prefix || '_' || l_dst_sql_id || '_' || l_plan_hash_value;
   l_profile_desc         VARCHAR2(50);
   err_dst_sql_id           EXCEPTION;
   err_src_sql_id           EXCEPTION;
   err_src_plan_hash_value  EXCEPTION;
   CURSOR sc_dst_sql_text IS
   SELECT sql_text
     FROM (SELECT sql_fulltext  sql_text
             FROM gv$sql
            WHERE sql_id = l_dst_sql_id
            UNION ALL
           SELECT sql_text
             FROM dba_hist_sqltext
            WHERE sql_id = l_dst_sql_id
          )
    WHERE ROWNUM = 1;
   CURSOR sc_src_sql_text IS
   SELECT sql_text
     FROM (SELECT sql_fulltext  sql_text
             FROM gv$sql
            WHERE sql_id = l_src_sql_id
            UNION ALL
           SELECT sql_text
             FROM dba_hist_sqltext
            WHERE sql_id = l_src_sql_id
          )
    WHERE ROWNUM = 1;
   CURSOR sc_src_plan_hash_value IS
   SELECT plan_hash_value
     FROM gv$sql_plan
    WHERE sql_id = l_src_sql_id
      AND plan_hash_value = l_plan_hash_value
      AND other_xml IS NOT NULL
    UNION
   SELECT plan_hash_value
     FROM dba_hist_sql_plan
    WHERE sql_id = l_src_sql_id
      AND plan_hash_value = l_plan_hash_value
      AND other_xml IS NOT NULL;
   CURSOR sc_profile_attr IS
   SELECT category
        , type
        , created
        , last_modified
        , status
        , force_matching
        , signature
        , description
     FROM dba_sql_profiles
    WHERE name = l_profile_name;
   CURSOR sc_prof_hints IS
   SELECT SUBSTR(attr_val, 1, 120) hint
     FROM sys.sqlprof$attr
    WHERE signature = l_signature
    ORDER BY category
           , attr#;
BEGIN
   OPEN sc_dst_sql_text;
   FETCH sc_dst_sql_text INTO l_dst_sql_text;
   CLOSE sc_dst_sql_text;
   IF l_dst_sql_text IS NULL THEN
      RAISE err_dst_sql_id;
   END IF;
   OPEN sc_src_sql_text;
   FETCH sc_src_sql_text INTO l_src_sql_text;
   CLOSE sc_src_sql_text;
   IF l_src_sql_text IS NULL THEN
      RAISE err_src_sql_id;
   END IF;
   OPEN sc_src_plan_hash_value;
   FETCH sc_src_plan_hash_value INTO l_plan_hash_value_chk;
   CLOSE sc_src_plan_hash_value;
   IF l_plan_hash_value_chk IS NULL THEN
      RAISE err_src_plan_hash_value;
   END IF;
   SELECT EXTRACTVALUE(VALUE(sub1), '/hint')  AS outline_hints
     BULK COLLECT
     INTO l_profile_hints
     FROM XMLTABLE('/*/outline_data/hint'
                   PASSING (SELECT xmltype(other_xml)  AS xmlval
                              FROM (SELECT other_xml
                                      FROM gv$sql_plan
                                     WHERE sql_id = l_src_sql_id
                                       AND plan_hash_value = l_plan_hash_value
                                       AND other_xml IS NOT NULL
                                     UNION ALL
                                    SELECT other_xml
                                      FROM dba_hist_sql_plan
                                     WHERE sql_id = l_src_sql_id
                                       AND plan_hash_value = l_plan_hash_value
                                       AND other_xml IS NOT NULL
                                   )
                             WHERE ROWNUM = 1
                           )
                  ) sub1;
   IF UPPER(l_force_match_str) = 'TRUE' THEN
      l_force_match := TRUE;
   ELSE
      l_force_match := FALSE;
   END IF;
   l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_dst_sql_text);
   l_profile_desc := l_profile_name || '_' || l_signature;
   DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text     => l_dst_sql_text
                                 , profile      => l_profile_hints
                                 , name         => l_profile_name
                                 , description  => l_profile_desc
                                 , category     => c_category
                                 , validate     => c_validate
                                 , replace      => c_replace
                                 , force_match  => l_force_match
                                  );
   DBMS_OUTPUT.PUT_LINE('SQL Profile ' || l_profile_name || ' successfully created');
   DBMS_OUTPUT.PUT_LINE('................................................................................');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Destination SQL Text');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE(l_dst_sql_text);
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Source SQL Text');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE(l_src_sql_text);
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Attributes');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   FOR x IN sc_profile_attr LOOP
      DBMS_OUTPUT.PUT_LINE('Category:        ' || x.category);
      DBMS_OUTPUT.PUT_LINE('Type:            ' || x.type);
      DBMS_OUTPUT.PUT_LINE('Created:         ' || x.created);
      DBMS_OUTPUT.PUT_LINE('Last Modified:   ' || x.last_modified);
      DBMS_OUTPUT.PUT_LINE('Status:          ' || x.status);
      DBMS_OUTPUT.PUT_LINE('Force Matching:  ' || x.force_matching);
      DBMS_OUTPUT.PUT_LINE('Signature:       ' || x.signature);
      DBMS_OUTPUT.PUT_LINE('Description:     ' || x.description);
   END LOOP; 
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   DBMS_OUTPUT.PUT_LINE('Hints');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------------------');
   FOR x IN sc_prof_hints LOOP
      DBMS_OUTPUT.PUT_LINE(x.hint);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('================================================================================');
EXCEPTION
   WHEN err_dst_sql_id THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Destination SQL_ID "' || l_dst_sql_id || '" not found.');
   WHEN err_src_sql_id THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Source SQL_ID "' || l_src_sql_id || '" not found.');
   WHEN err_src_plan_hash_value THEN
      DBMS_OUTPUT.PUT_LINE('Error:  Source PLAN_HASH_VALUE "' || l_plan_hash_value || '" not found.');
   WHEN OTHERS THEN
      RAISE;
END;
/


Method 2 - There is also a backdoor method to create a SQL profile if the plan is not available in either place, which can be done using SQLT, which again uses IMPORT_SQL_PROFILE with manually created SQLPROF_ATTR (i.e. SQL Profile Attribute)

Below example need to properly converted according to your scenario.

SET DEF ^ ECHO ON TERM ON LIN 2000 TRIMS ON SERVEROUT ON SIZE 1000000 TIM OFF SQLP SQL>;
SPO SQLT_2wskdsyt6w2ty_p4150230984.log;
SET ECHO OFF;
PRO
PRO ... Generating manual custom SQL Profile for SQL text provided
PRO
SET TERM OFF HEA ON TIM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
  sql_txt VARCHAR2(32767);
  h       SYS.SQLPROF_ATTR;
BEGIN
  sql_txt := q'[SELECT  /*YANTRA*/    YFS_ORDER_HEADER.ORDER_HEADER_KEY  FROM YFS_ORDER_HEADER YFS_ORDER_HEADER          WHERE PAYMENT_STATUS IN ( :"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SYS_B_03",:"SYS_B_04",:"SYS_B_05",:"SYS_B_06",:"SYS_B_07",:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11")  AND AUTHORIZATION_EXPIRATION_DATE <= :"SYS_B_12"  AND ENTERPRISE_KEY = :"SYS_B_13"  AND DOCUMENT_TYPE = :"SYS_B_14"  AND NOT EXISTS ( SELECT :"SYS_B_15" FROM YFS_ORDER_HOLD_TYPE WHERE YFS_ORDER_HOLD_TYPE.ORDER_HEADER_KEY = YFS_ORDER_HEADER.ORDER_HEADER_KEY AND HOLD_TYPE IN ( :"SYS_B_16",:"SYS_B_17" ) AND STATUS < :"SYS_B_18") ]';
  h := SYS.SQLPROF_ATTR(
      q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
      q'[OPTIMIZER_FEATURES_ENABLE('10.2.0.4')]',
      q'[OPT_PARAM('optimizer_dynamic_sampling' 1)]',
      q'[OPT_PARAM('optimizer_index_cost_adj' 50)]',
      q'[OUTLINE_LEAF(@"SEL$5DA710D3")]',
      q'[UNNEST(@"SEL$2")]',
      q'[OUTLINE(@"SEL$1")]',
      q'[OUTLINE(@"SEL$2")]',
      q'[INDEX(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HEADER%22@%22SEL$1" ("YFS_ORDER_HEADER"."ENTERPRISE_KEY" "YFS_ORDER_HEADER"."DOCUMENT_TYPE" "YFS_ORDER_HEADER"."PAYMENT_STATUS" "YFS_ORDER_HEADER"."AUTHORIZATION_EXPIRATION_DATE" "YFS_ORDER_HEADER"."ORDER_HEADER_KEY"))]',
      q'[INDEX_RS_ASC(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2" ("YFS_ORDER_HOLD_TYPE"."ORDER_HEADER_KEY" "YFS_ORDER_HOLD_TYPE"."ORDER_LINE_KEY" "YFS_ORDER_HOLD_TYPE"."HOLD_TYPE"))]',
      q'[LEADING(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HEADER%22@%22SEL$1" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]',
      q'[USE_NL(@"SEL$5DA710D3" mailto:%22YFS_ORDER_HOLD_TYPE%22@%22SEL$2%22)]'
  );
  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
    sql_text    => sql_txt,
    profile     => h,
    name        => 'SQLT_2wskdsyt6w2ty_p4150230984',
    description => 'SQLT 2wskdsyt6w2ty p4150230984'||:signature,
    category    => 'DEFAULT',
    validate    => TRUE,
    replace     => TRUE,
    force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
 

Key points to consider for Oracle RAC/Cluster in Linux

1. Ensure Max. Open Files is set to 65k for Oracle Cluster.
Reference
Oracle Bug # 8429716
Oracle Metalink Note id # 858279.1

You may see timeout errors like below in the crsd.log file.
Important : Oracle Metalink note id # 732086.1 & Bug 6196746 may misguide you.

2010-12-07 03:14:54.776: [ CRSEVT][1494178112] CAAMonitorHandler :: 0:Action Script /u01/crs/oracle/product/crs/bin/racgwrap(check) timed out for

ora.dlp34db.vip! (timeout=60)
2010-12-07 03:14:54.776: [ CRSAPP][1494178112] CheckResource error for ora.dlp34db.vip error code = -2
2010-12-07 03:14:54.846: [ CRSEVT][1492076864] CAAMonitorHandler :: 0:Could not join /u01/crs/oracle/product/crs/bin/racgwrap(check)
category: 1234, operation: scls_process_join, loc: childcrash, OS error: 0, other: Abnormal termination of the child


2. Increase DIAGWAIT. This avoids failure in dumping log information prior to Node Eviction by giving sufficient time.
Eventually it helps to troubleshoot the root cause of Node Eviction
Reference
Oracle Metalink Note id # 559365.1

crsctl set css diagwait 13 -force

3. Ensure HUGEPAGE is set appropriately.
Oracle Metalink Note Reference
Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration [ID 401749.1]
HugePages on 64-bit Linux [ID 361468.1]
HugePages on Linux: What It Is... and What It Is Not... [ID 361323.1]

4. Additional settings to fetch server level core information to diagnose the Server crash due to Oracle Cluster
a. Enable CSSD core by following Oracle Metalink Note 605449.1
b. Configure kdump by following Oracle Metalink Note 452067.1

SQLID level Tracing Feature in 11g

Now since Oracle 11g onwards you can trace all sessions by filtering a particular SQL ID or
set of SQL IDs

For example

alter session/system set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] wait=true | false, bind=true | false, planstat=never | first_execution | all_executionss|level 12';

alter session/system set events 'sql_trace [sql:g3yc1js3g2689|7ujay4u33g337] off';

Trace will be generated upon running that particular SQL ID

Keywords : SQL ID, sqlid trace

Solution for long running query against DBA_EXTENTS to find the corrupted segment

Problem

You are running a query against DBA_EXTENTS to find the segment information (most probably you would be using
this to find a CORRUPTED segment/object) for a particular FILE_ID and BLOCK_ID.

Query might look something like this.

select owner,segment_name,segment_type
from dba_extents
where file_id =
and between block_id and (block_id+(blocks-1));

This query might run longer and sometimes you may lose your patience waiting for it.

Solution

• Dump the block and take the object id using the below command

alter system dump datafile block ;
Eg.alter system dump datafile 1811 block 20000;

FYI, this command generate a trace file in the user_dump_dest directory

• Grep the object number from the trace file.

Locate the trace file and grep the object id which will be presented in HEX value.

grep "seg/obj" op01csdb01_ora_13143.trc

seg/obj: 0xe024a2 csc: 0x878.a5583a53 itc: 50 flg: E typ: 1 - DATA

• Find the object information from DBA_OBJECTS

select owner,object_id,data_object_id,object_name,subobject_name

from dba_objects

where (object_id=to_number('E024A2', 'xxxxxx')

or

data_object_id=to_number('E024A2', 'xxxxxx')

);

Note : the count of 'x' should be equal to the number of characters in the source.

Eg. E024A2 - is 6 character string, hence i used 'xxxxxx'

Keywords : query; dba_extents; long; slow; corrupt ; segment; object

How to create duplicate database from a primary when the FULL backup happens out of standby

Scenario

1. You want to create a DUPLICATE database to perform PITR out of Primary PRODUCTION database. Duplicate/auxiliary instance is created in a different host.

2. FULL/DYNAMIC backup of primary production database goes out of STANDBY

Assumption

1. Backup of Production databae controlfile exists

Facts

In this scenario, I used the following command to perform PITR recovery in RETEK database.
1. Production database
a. op01rtdb01/2
b. primnode1/primnode2
2. Standby database
a. Op99rtdb01
b. standbynode1
c. Policy : rdc_oradb_qa
d. Netbackup master server : nbmstrnode1

Note : FULL database backup is performed out of op99rtdb01

3. Duplicate/Auxiliary database
a. op01drdb01
b. auxnode1

Commands

PFA the general commands for this sort PITR. This document would also cover the directory creation and other steps.
Otherwise, the key steps are

1. Login to auxnode1 as “oracle” unix user

2. . oraenv (type op01drdb01)

3. Startup nomount

rman

connect target SYS/@op01rtdb01 Note : I am connecting only to primary production database, though the FULL backup happens out of OP99RTDB01.

connect catalog rman10g/@op01emdb02

CONNECT AUXILIARY /

run
{
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t2 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t3 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
allocate auxiliary channel t4 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=standbynode1,NB_ORA_CLASS=rdc_oradb_qa,NB_ORA_SERV=nbmstrnode1)';
set until time "to_date('2009-10-09 02:00:00','YYYY-MM-DD hh24:mi:ss')";
DUPLICATE TARGET DATABASE TO OP01DRDB
db_file_name_convert=('/u01/app/oracle/admin/oralink/op01rtdb/','/u01/app/oracle/admin/oralink/op01drdb/')
skip tablespace ARIDAT03,RTKIDX06,RTKIDXEX01,RTKIDXEX03,OUTLN01,RTKIDX02,RTKDAT02,ARIDAT02,RTKIDX08,RTKDATEX03,RTKDAT03,RTKIDX04,RTKIDX01,RTKDAT04,PMIDAT01,TOOLS01,ARIIDX02,
ARIIDX03,AUDDAT01,ARIDAT01,RTKDAT01,RTKDAT06,RTKIDX03,RTKIDXEX02,RTKIDX07,ARIIDX01,RTKIDX05,RTKDAT07,USERS01,RTKDAT05,RTKDATEX02,RTKDAT08
LOGFILE
GROUP 1 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0101a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0101b.rdo') SIZE 511M,
GROUP 2 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0201a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0201b.rdo') SIZE 511M,
GROUP 3 ('/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0301a.rdo','/u01/app/oracle/admin/oralink/op01drdb/op01drdb01_0301b.rdo') SIZE 511M REUSE;
}

Note : To get the value of the parameter marked in RED, you need to go to op99rtdb01/standbynode1:/usr/openv/netbackup/logs/user_ops/dbext/logs
Grep for rdc_oradb_qa and CAT any of the file.

standbynode1:[op99rtdb01]:/usr/openv/netbackup/logs/user_ops/dbext/logs>more 709.0.1264176793
Backup started Fri Jan 22 10:13:13 2010


10:14:11 INF - Backup id = standbynode1_1264176842
10:14:11 INF - Backup time = 1264176842
10:14:11 INF - Policy name = rdc_oradb_qa
10:14:11 INF - Snapshot = 0
10:14:12 INF - Frozen image = 0
10:14:12 INF - Backup copy = 0
10:14:12 INF - Master server = nbmstrnode1

Key words : create ; auxiliary ; duplicate ; standby ; dataguard ; full ; backup ;

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