Sunday, August 26, 2012


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.
----------------------------------------------------------------------------------------------------------------------------------

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. :)

Thanks,

2 comments:

  1. Thanks Ashish !!
    Very good info & nice explanation for SQL Profile.

    Thanks,
    Siva

    ReplyDelete