Steps to Create and Transfer SQL Profile from One Oracle Database to Another Database
Hello DBAs, In my second post for SQL PROFILE, I am just going to show you how we can transfer the same SQL PROFILE we have accepted in previous post Automatic SQL Tunig and SQL Profile to another database let say 'TEST'.
So, Here I am again showing you how to create and accept SQL PROFILE that we have done already in my earlier post. Now here is the steps that you'll have to just follow.
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:
From Scott
-----------
1) create table test (n number );
2)
declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
3) create index test_idx on test(n);
4) exec dbms_stats.gather_table_stats('','TEST');
5) Here you can see I am using no_index hint so optimzer will go for full table scan, which is but obvious.
SQL> set autotrace traceonly
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
FROM SYS USER
-------------------------
6) Now, run SQLTUNING task to generate SQL PROFILE and accept the recommended profile.
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
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_2',
description => 'Task to tune a query on a specified table');
end;
/
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
7) Now, see the report for SQL PROFILE recommendation.
SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/26/2011 15:07:04
Completed at : 10/26/2011 15:07:08
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d4wgpc5g0s0vu
SQL Text : select /*+ no_index(test test_idx) */ * from test where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
9) Finally accept recommended profile.
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/
AGAIN FROM SCOTT USER
-----------------------------------------
10) This time if you will fire the same query again, optimizer will ignore no_index hint and will go for index scan because it will use SQL PROFILE.
----------------------------------------------------------------------------------------------------------------------------------
2)
declare
begin
for i in 1 .. 10000 loop
insert into test values(i);
commit;
end loop;
end;
/
3) create index test_idx on test(n);
4) exec dbms_stats.gather_table_stats('','TEST');
5) Here you can see I am using no_index hint so optimzer will go for full table scan, which is but obvious.
SQL> set autotrace traceonly
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
FROM SYS USER
-------------------------
6) Now, run SQLTUNING task to generate SQL PROFILE and accept the recommended profile.
declare
my_task_name VARCHAR2(30);
my_sqltext CLOB;
begin
my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
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_2',
description => 'Task to tune a query on a specified table');
end;
/
begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');
end;
/
7) Now, see the report for SQL PROFILE recommendation.
SQL> set long 1000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 10/26/2011 15:07:04
Completed at : 10/26/2011 15:07:08
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID : d4wgpc5g0s0vu
SQL Text : select /*+ no_index(test test_idx) */ * from test where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.95%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
9) Finally accept recommended profile.
DECLARE
my_sqlprofile_name VARCHAR2(30);
begin
my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => 'my_sql_tuning_task_2',
name => 'my_sql_profile');
end;
/
AGAIN FROM SCOTT USER
-----------------------------------------
10) This time if you will fire the same query again, optimizer will ignore no_index hint and will go for index scan because it will use SQL PROFILE.
----------------------------------------------------------------------------------------------------------------------------------
SQL> set autotrace traceonly
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
-------------------------------------------------------------------------
Plan hash value: 1416057887
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
---------------
Note
-----
- SQL profile "my_sql_profile" used for this statement
2. Creating a staging table to store the SQL Profiles
---------------------------------------------------------------
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');
PL/SQL procedure successfully completed.
Where,
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
---------------------------------------------------------
SQL> exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile',staging_schema_owner => 'SCOTT');
PL/SQL procedure successfully completed.
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
-----------------------------------------
[oracle@node1 ~]$ expdp system/oracle_4U directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE
Export: Release 11.2.0.1.0 - Production on Sun Aug 26 20:27:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."STAGE" 22.10 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/expdp/a.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 20:28:08
4b. Import into Target Database
-----------------------------------------
[oracle@node1 ~]$ impdp system/oracle_4U directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE
Import: Release 11.2.0.1.0 - Production on Sun Aug 26 20:28:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=dir dumpfile=a.dmp logfile=a.log tables=scott.STAGE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."STAGE" 22.10 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 20:29:16
5. Unpack the SQL Profiles so that new database start using imported SQL PROFILE
---------------------------------------------------------------------------------------
5a. Test before unpacking
---------------------------------
SQL> set autotrace traceonly
SQL>
SQL>
SQL>
SQL>
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 4 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
NOTE: Here you can see, optimizer is not using SQL PROFILE because still it is not unpack for use.
5b. Unpack Staging Table from sys
--------------------------------------------
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SCOTT');
PL/SQL procedure successfully completed.
6. Check the SQL Profile is enabled in Target Database
--------------------------------------------------------
SQL> set autotrace traceonly
SQL>
SQL>
SQL>
SQL>
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Note
-----
- SQL profile "my_sql_profile" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
519 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
So finally you can see this time optimizer is using SQL Profile also in TEST DATABASE.
No comments:
Post a Comment