First, i had to create the TEST table in target in order to compare the result post sql_profile transfer.
In the Source database
How to export the sql_profile from one instance to another
oracle@test ~/dba/scripts/REFRESH> exp tables=test indexes=no
Export: Release 11.2.0.3.0 - Production on Tue May 23 03:15:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 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.
In the target instance
oracle@test1 /tmp> imp file=expdat.dmp tables=test
Import: Release 11.2.0.3.0 - Production on Tue May 23 03:17:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.
So we have confirmed that there are no indexes in the target database
Also we have confirmed that there are no sql profiles present in the target instance
SYS@test1 AS SYSDBA> select name, created, status from dba_sql_profiles;
no rows selected
SYS@test1 AS SYSDBA>
We have confirmed that in the target instance there is no sql_profile present at this moment.
Now we will import the sql profile from source to target
We will create a staging table. This is a table which we will create using dbms_sqltune package
In the target instance, we have the sql profile.
SQLPROFILE_02.
Note:We can't create a staging table under sys schema. I have created it under system
oracle@test /tmp> sqlplus system
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 23 04:31:27 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYSTEM@sam > EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROF_STGTAB');
PL/SQL procedure successfully completed.
Copy sql profile into staging table
This step is also called Packing the SQL Profiles into the staging tables. The SQL Profiles are copied from the data dictionary into the staging table, in order to be ready for transport.
SYSTEM@sam > EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROF_STGTAB',profile_name=>'SQLPROFILE_02');
PL/SQL procedure successfully completed.
SYSTEM@sam >
Now we need to move this staging table from source database to target database
SYS@sam AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/tmp/tmp';
Directory created.
SYS@sam AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@sam AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@test /tmp> expdp system/** tables=SQL_PROF_STGTAB directory=TEST_DIR dumpfile=stage1.dmp logfile=exp.log compression=all
Export: Release 11.2.0.3.0 - Production on Tue May 23 07:11:06 2017
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=SQL_PROF_STGTAB directory=TEST_DIR dumpfile=stage1.dmp logfile=exp.log compression=all
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/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."SQL_PROF_STGTAB" 7.695 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/tmp/tmp/stage1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 07:14:58
Scp the stage.dmp file to the target database directory
now lets check the explain plan for the query which we will be using in target instance
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
This is obvious result. Now once we import the staging table(stage.dmp) into the data dictionary, the query should start using the sql profile(taken out from source database).
oracle@test1 /oracle/stage/DUMP_DIR_donotdelete/SIT> impdp system/*** tables=SQL_PROF_STGTAB dumpfile=stage1.dmp
Import: Release 11.2.0.3.0 - Production on Tue May 23 07:19:39 2017
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, 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/******** tables=SQL_PROF_STGTAB dumpfile=stage1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."SQL_PROF_STGTAB" 7.695 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 07:19:41
oracle@test1 /oracle/stage/DUMP_DIR_donotdelete/SIT>
Now we need to unpack the staging table into data dictionary. This should be done in system user or any user having proper privige
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (REPLACE => TRUE,staging_table_name => 'SQL_PROF_STGTAB');
SYSTEM@test1 > EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (REPLACE => TRUE,staging_table_name => 'SQL_PROF_STGTAB');
PL/SQL procedure successfully completed.
Now lets check the execution plan again.
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Note
-----
- SQL profile "SQLPROFILE_02" used for this statement
17 rows selected.
hmm, so index was not present. I had to create it.
SYS@test1 AS SYSDBA> create index test1 on test(w);
Index created.
SYS@test1 AS SYSDBA> select owner,index_name from dba_indexes where table_name='TEST';
OWNER INDEX_NAME
------------------------------ ------------------------------
SYS TEST1
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3981825257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
Note
-----
- SQL profile "SQLPROFILE_02" used for this statement
18 rows selected.
Happy learning.
In the Source database
How to export the sql_profile from one instance to another
oracle@test ~/dba/scripts/REFRESH> exp tables=test indexes=no
Export: Release 11.2.0.3.0 - Production on Tue May 23 03:15:10 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 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.
In the target instance
oracle@test1 /tmp> imp file=expdat.dmp tables=test
Import: Release 11.2.0.3.0 - Production on Tue May 23 03:17:50 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "TEST" 1 rows imported
Import terminated successfully without warnings.
So we have confirmed that there are no indexes in the target database
Also we have confirmed that there are no sql profiles present in the target instance
SYS@test1 AS SYSDBA> select name, created, status from dba_sql_profiles;
no rows selected
SYS@test1 AS SYSDBA>
We have confirmed that in the target instance there is no sql_profile present at this moment.
Now we will import the sql profile from source to target
We will create a staging table. This is a table which we will create using dbms_sqltune package
In the target instance, we have the sql profile.
SQLPROFILE_02.
Note:We can't create a staging table under sys schema. I have created it under system
oracle@test /tmp> sqlplus system
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 23 04:31:27 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYSTEM@sam > EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROF_STGTAB');
PL/SQL procedure successfully completed.
Copy sql profile into staging table
This step is also called Packing the SQL Profiles into the staging tables. The SQL Profiles are copied from the data dictionary into the staging table, in order to be ready for transport.
SYSTEM@sam > EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROF_STGTAB',profile_name=>'SQLPROFILE_02');
PL/SQL procedure successfully completed.
SYSTEM@sam >
Now we need to move this staging table from source database to target database
SYS@sam AS SYSDBA> CREATE OR REPLACE DIRECTORY test_dir AS '/tmp/tmp';
Directory created.
SYS@sam AS SYSDBA> GRANT READ, WRITE ON DIRECTORY test_dir TO system;
Grant succeeded.
SYS@sam AS SYSDBA> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@test /tmp> expdp system/** tables=SQL_PROF_STGTAB directory=TEST_DIR dumpfile=stage1.dmp logfile=exp.log compression=all
Export: Release 11.2.0.3.0 - Production on Tue May 23 07:11:06 2017
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, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=SQL_PROF_STGTAB directory=TEST_DIR dumpfile=stage1.dmp logfile=exp.log compression=all
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/PRE_TABLE_ACTION
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCDEPOBJ
. . exported "SYSTEM"."SQL_PROF_STGTAB" 7.695 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/tmp/tmp/stage1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 07:14:58
Scp the stage.dmp file to the target database directory
now lets check the explain plan for the query which we will be using in target instance
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Note
-----
- dynamic sampling used for this statement (level=2)
17 rows selected.
This is obvious result. Now once we import the staging table(stage.dmp) into the data dictionary, the query should start using the sql profile(taken out from source database).
oracle@test1 /oracle/stage/DUMP_DIR_donotdelete/SIT> impdp system/*** tables=SQL_PROF_STGTAB dumpfile=stage1.dmp
Import: Release 11.2.0.3.0 - Production on Tue May 23 07:19:39 2017
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, 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/******** tables=SQL_PROF_STGTAB dumpfile=stage1.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."SQL_PROF_STGTAB" 7.695 KB 1 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 07:19:41
oracle@test1 /oracle/stage/DUMP_DIR_donotdelete/SIT>
Now we need to unpack the staging table into data dictionary. This should be done in system user or any user having proper privige
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (REPLACE => TRUE,staging_table_name => 'SQL_PROF_STGTAB');
SYSTEM@test1 > EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF (REPLACE => TRUE,staging_table_name => 'SQL_PROF_STGTAB');
PL/SQL procedure successfully completed.
Now lets check the execution plan again.
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 16 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("W"='sam')
Note
-----
- SQL profile "SQLPROFILE_02" used for this statement
17 rows selected.
hmm, so index was not present. I had to create it.
SYS@test1 AS SYSDBA> create index test1 on test(w);
Index created.
SYS@test1 AS SYSDBA> select owner,index_name from dba_indexes where table_name='TEST';
OWNER INDEX_NAME
------------------------------ ------------------------------
SYS TEST1
SYS@test1 AS SYSDBA> explain plan for select * from test where w='sam';
Explained.
SYS@test1 AS SYSDBA> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3981825257
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("W"='sam')
Note
-----
- SQL profile "SQLPROFILE_02" used for this statement
18 rows selected.
Happy learning.
No comments:
Post a Comment