ba6.us - Dave's Database Related Stuff

Following 457531.1 Transporting the SQL Profile ====================

i) Creating a staging table to store the SQL Profiles

SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'TEST',schema_name=>'SCOTT'); 

PL/SQL procedure successfully completed. 

table_name => name of the table to store the SQL Profiles. schema_name => name of the user where the table is to be created.

ii) Packing the SQL Profiles to the staging table

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'TEST',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 test 
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 

SQL> select count(*) from test; 

COUNT(*) 
---------- 
1 

SQL> set autot 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: 2949544139 

-------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 | 
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 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 

iii) Now export the table using Datapump or Export/Import.

[oracle@localhost oracle]$ exp scott/tiger tables=test 

Export: Release 10.2.0.3.0 - Production on Fri Sep 7 01:18:06 2007 

Copyright (c) 1982, 2005, Oracle. All rights reserved. 


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production 
With the Partitioning and Data Mining options 
Export done in US7ASCII character set and AL16UTF16 NCHAR character set 

About to export specified tables via Conventional Path ... 
. . exporting table TEST 1 rows exported 
Export terminated successfully without warnings. 

iv) Execute the query in another database without SQL Profiles

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: 3956160932 

-------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 4 | 348 | 3 (0)| 00:00:01 | 
|* 1 | TABLE ACCESS FULL| EMP | 4 | 348 | 3 (0)| 00:00:01 | 
-------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 

1 - filter("EMPNO"=7839) 

Note: The SQL Profiles are not there.

v) Importing the SQL Profiles to the database

[oracle@localhost oracle]$ imp scott/tiger file=expdat.dmp full=y 

Import: Release 10.2.0.3.0 - Production on Fri Sep 7 01:24:15 2007 

Copyright (c) 1982, 2005, Oracle. All rights reserved. 


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production 
With the Partitioning and Data Mining options 

Export file created by EXPORT:V10.02.01 via conventional path 
import done in US7ASCII character set and AL16UTF16 NCHAR character set 
. importing SCOTT's objects into SCOTT 
. importing SCOTT's objects into SCOTT 
. . importing table "TEST" 1 rows imported 
Import terminated successfully without warnings. 

vi) Now Unpack the SQL Profiles

SQL> conn scott/tiger /* connect to scott schema where the staging table is imported and then unpack */

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'TEST');

PL/SQL procedure successfully completed. 

vii) Checking the SQL Profiles

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: 2949544139 

-------------------------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
-------------------------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 | 
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 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 

Now the SQL Profile is transported to another database.