Database Concepts and Internals by Ashish

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

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

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> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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> select /*+ no_index(test test_idx) */ * from test where n=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2882402178

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

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

Database Concepts and Internals by Ashish

Sunday, August 26, 2012

2 comments: