The following example illustrates the process of moving a SQL Profile from 10.2.0.4.0 to 11.2.0.3.0
1. Create SQL Profile in SCOTT schema
The SQL Profile is created based on the tuning task created and the recommendations given by the tuning task:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
my_sqlprofile_name VARCHAR2(30);
BEGIN
my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where
empno=7839';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Demo Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name => 'my_sql_tuning_task');
my_sqlprofile_name :=
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task',
name => 'my_sql_profile');
END;
/
PL/SQL procedure successfully completed
set lines 130
set autotrace on
select /*+ no_index(emp pk_emp) */ *
from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- ---------
---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323
--------------------------------------------------------------------------------------
| Id
| Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 1 |
37 | 1 (0)| 00:00:01 |
|
1 | TABLE ACCESS BY INDEX ROWID|
EMP | 1 |
37 | 1 (0)| 00:00:01 |
|*
2 | INDEX UNIQUE SCAN | PK_EMP | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
Note
-----
- SQL profile "my_sql_profile" used for this statement
2. Creating a staging table to store the SQL Profiles
exec
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');
PL/SQL procedure successfully
completed.
- table_name => name of the table to store the SQL Profiles.
- schema_name => name of the schema where the table is to be created.
3. Pack the SQL Profiles into the Staging Table
exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(staging_table_name =>'STAGE',profile_name=>'my_sql_profile');
PL/SQL procedure successfully completed.
- staging_table_name => name of the table to store the SQL Profiles.
- profile_name => name of the SQL Profile to be packed.
Note: The table_name and schema_name are case-sensitive.
SQL> desc STAGE
Name
Null? Type
-----------------------------------------
-------- ----------------------------
PROFILE_NAME
VARCHAR2(30)
CATEGORY
VARCHAR2(30)
SIGNATURE
NUMBER
SQL_TEXT CLOB
DESCRIPTION
VARCHAR2(500)
TYPE
VARCHAR2(9)
STATUS
VARCHAR2(8)
BOOLEAN_FLAGS NUMBER
ATTRIBUTES
SQLPROF_ATTR
VERSION
NUMBER
SPARE1 CLOB
SPARE2
BLOB.
4. Export the Staging Table to the Target Database
Using Datapump or Export/Import transfer the table to the target database where you would like to create the same profile.
4a. Export from Source Database
my_linux_1:~> exp scott/tiger
tables=STAGE
Export: Release 10.2.0.4.0 - Production
on Sun Feb 12 17:43:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g
Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and
Real Application Testing options
Export done in US7ASCII character set
and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set
(possible charset conversion)
About to export specified tables via
Conventional Path ...
. . exporting table STAGE 1 rows exported
Export terminated successfully without
warnings.
4b. Import into Target Database
my_linux_1:~> imp scott/tiger
tables=STAGE
Import: Release 11.2.0.3.0 - Production
on Mon Feb 13 14:49:12 2012
Copyright (c) 1982, 2011, Oracle and/or
its affiliates. All rights reserved.
Connected to: Oracle Database 11g
Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Real
Application Testing options
Export file created by EXPORT:V10.02.01
via conventional path
import done in US7ASCII character set
and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252
character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "STAGE" 1 rows imported
Import terminated successfully with
warnings.
5. Unpack the SQL Profiles
5a. Test before unpacking
SQL> set lines 130
SQL> set autotrace on
SQL> select /*+ no_index(emp pk_emp)
*/ * from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- ---------
---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 2872589290
--------------------------------------------------------------------------
| Id
| Operation | Name |
Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0 | SELECT STATEMENT | |
1 | 38 | 3
(0)| 00:00:01 |
|*
1 | TABLE ACCESS FULL| EMP | 1
| 38 | 3
(0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
1 - filter("EMPNO"=7839)
5b. Unpack Staging Table
If importing to the same schema, schema
owner does not need to be specified:
SQL> EXEC
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name =>
'STAGE');
However, if importing to different
schema, the staging schema owner needs to be changed:|
SQL> EXEC
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name =>
'STAGE',staging_schema_owner => 'SQLTXPLAIN');
PL/SQL procedure successfully
6. Check the SQL Profile is enabled in Target Database
set lines 130
set autotrace on
select /*+ no_index(emp pk_emp) */ *
from emp where empno=7839;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- ---------
---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
Execution Plan
----------------------------------------------------------
Plan hash value: 4066871323
--------------------------------------------------------------------------------------
| Id
| Operation |
Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0 | SELECT STATEMENT
| | 1 |
37 | 1 (0)| 00:00:01 |
|
1 | TABLE ACCESS BY INDEX ROWID|
EMP | 1 |
37 | 1 (0)| 00:00:01 |
|*
2 | INDEX UNIQUE SCAN | PK_EMP | 1 |
| 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 - access("EMPNO"=7839)
Note
-----
- SQL profile "my_sql_profile" used for this statement
No comments:
Post a Comment