转 sql profile 绑定 litera and move profile to another db l for spa

转载

mob604756f23a7e 2018-08-19 17:48:00

文章标签 运维帮 sql sed ide oracle 文章分类 代码人生

SQL TYPE 1:

for bind value sql ,

first create a good plan with literal and with good profile.

then use sqlT to replace the profile‘’s good plan to bind value sql_id.

then double checking .

var 1 varchar2(32);

var 2 VARCHAR2(32);

var 3 VARCHAR2(32);

var 4 VARCHAR2(32);

var 5 VARCHAR2(32);

exec :1:='313585000990';

exec :2:='313585000990';

exec :3:='121.001.01';

exec :4:='L01';

select a,b,c,d,e from a where (a = :1 and b = :2 and c = :3 and d = :4) and ((packsndflg is NULL) or (packsndflg = ''))

SQL TYPE 2:

for literal value sql ,first create a good plan literal with profile with force_match => TRUE

另外如果自动调优，仍然无法找到一条好的执行计划，需要考虑到将10G 执行计划迁移到11G .具体步骤见下文。

his document provides information on the following topics:

To create SQL Profiles for queries using literals where different literal values are passed for every execution.
Use this SQL Profile for every execution of this SQL whatever the literal is changed to.

Default behavior: By default, if a SQL Profile is created for a sql containing literal values, then the profile would be generated solely for the query with whatever literals were used while running the tuning advisor. If the same SQL was executed with only a change in literals, then that profile would not be used for this query since the changing literal would make the SQL deemed to be a new query. Since profiles are identified by the SQL that they are recorded against, the profile would not be used for this "new" SQL.

SOLUTION

Default Behavior

Firstly, lets look at the default behavior of a SQL Profile.

Example:

Create a table, populate it with some values, add an index and gather statistics:

SQL> create table test (n number );

Table created.

declare

begin

for i in 1 .. 10000

loop

insert into test values(i);

commit;

end loop;

end;

PL/SQL procedure successfully completed.

create index test_idx on test(n);

Index created.

analyze table test estimate statistics (OR use dbms_stats)

Table analyzed.

Run a test query against it with a literal predicate (n=1):

select /*+ no_index(test test_idx) */ * from test where n=1

Execution Plan

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

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=13)

1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=6 Card=1 Bytes=13)

Now, use SQL Tuning Advisor to create a profile for the query:

SQL> DECLARE 2 my_task_name VARCHAR2(30); 3 my_sqltext CLOB; 4 BEGIN 5 my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1'; 6 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( 7 sql_text=> my_sqltext, 8 user_name => 'SCOTT', 9 scope => 'COMPREHENSIVE', 10 time_limit => 60, 11 task_name => 'my_sql_tuning_task_2', 12 description => 'Task to tune a query on a specified table'); 13 END; 14 /  PL/SQL procedure successfully completed.  SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_2');  PL/SQL procedure successfully completed.  SQL> set long 2000 SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_2') from DUAL;  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: 84.03%) ------------------------------------------ - Consider accepting the recommended SQL profile. execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);

If we accept the profile:

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE);

PL/SQL procedure successfully completed.

Then we now have a profile for that SQL statement.

If we execute the sql with various literals, the SQL Profile will only be used for the query with the specific literals used when it was created:

SQL> select /*+ no_index(test test_idx) */ * from test where n=1;  N ----------          1  Execution Plan ---------------------------------------------------------- Plan hash value: 2882402178 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| TEST_IDX | 1 | 3 | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------  Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("N"=1)  Note ----- - SQL profile "SYS_SQLPROF_014af9c017890000" used for this statement

In this example the profile is used, but if we change the literal to a different value, then the profile would not be used and a different plan may be chosen (as in this case).

SQL> select /*+ no_index(test test_idx) */ * from test where n=2;  N ----------          2  Execution Plan ---------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation          | Name | Rows | Bytes | Cost (%CPU) | Time     | -------------------------------------------------------------------------- |  0 | SELECT STATEMENT   |      |    1 |     3 |       6 (0) | 00:00:01 | |* 1 |  TABLE ACCESS FULL | TEST |    1 |     3 |       6 (0) | 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("N"=2)

As you can see, there is now no message about a profile being used for this statement.

Using the Force_Match parameter of DBMS_SQLTUNE.ACCEPT_SQL_PROFILE

By default DBMS_SQLTUNE.ACCEPT_SQL_PROFILE executes with the force_match parameter set to false. When set to true, this is analogous to the matching algorithm used by the FORCE option of the cursor_sharing parameter in that it forces literals in the statement to be converted to binds and then the statement can be shared when different literals are supplied. It also causes SQL profiles to target all SQL statements which have the same text after normalizing all literal values into bind variables. (Note that if a combination of literal values and bind values is used in a SQL statement, no bind transformation occurs.)

If we recreate the SQL profile with this option, it will be used whatever literals are supplied , since it has internally replaced the literals with binds.

What follows is an example illustrating this behavior:

SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', replace => TRUE, force_match=>true);

PL/SQL procedure successfully completed.

Now even if the literals are changed, the SQL profile gets used.

SQL> select /*+ no_index(test test_idx) */ * from test where n=10;  N ----------         10  Execution Plan ---------------------------------------------------------- Plan hash value: 2882402178 ----------------------------------------------------------------------------- | Id | Operation         | Name     | Rows | Bytes | Cost (%CPU) | Time     | ----------------------------------------------------------------------------- |  0 | SELECT STATEMENT  |          |    1 |     3 |       1 (0) | 00:00:01 | |* 1 |  INDEX RANGE SCAN | TEST_IDX |    1 |     3 |       1 (0) | 00:00:01 | -----------------------------------------------------------------------------  Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("N"=10)  Note ----- - SQL profile "SYS_SQLPROF_014af9c167e84001" used for this statement

Now that force_match is set to true the SQL Profile is used.

2. You can verify that the new execution plan is being used as followed:
  
  select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='2qknbzqt0aoxb';
3. The profile can be disabled or dropped as follows using the the SQL_PROFILE name returned from the query above:
  
  To disable the profile:
  
  EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');
  To drop the profile:
  
  EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

SOLUTION

What is a SQL Profile?

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

<br class="Apple-interchange-newline"><div></div> 409/5000 SQL Profile是存储在数据字典中的信息集合，它使查询优化器能够为SQL语句创建最佳执行计划.SQL配置文件包含对自动SQL调整期间发现的较差优化程序估计的更正。此信息可以改进优化器基数和选择性估计，从而导致优化器选择更好的计划。

Managing SQL Profiles

For information on SQL Profiles see:

Document 271196.1 Automatic SQL Tuning - SQL Profiles

Steps to Create and Transfer Profile from One Database to Another

The following example illustrates the process of moving a SQL Profile from 10.2 onwards.

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:

DECLARE  my_task_name VARCHAR2(30); my_sqltext CLOB;  my_sqlprofile_name VARCHAR2(30);   BEGIN    my_sqltext := 'select /*+ no_index(emp pk_emp) */ * from emp where empno=7839';    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',          description => 'Demo Task to tune a query');     DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task');   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (task_name =>'my_sql_tuning_task',          name => 'my_sql_profile');  END;  /  PL/SQL procedure successfully completed.

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

NOTE: Even though no_index hint included, the plan uses an index as determined by the SQL profile. The Note section provides plan information that indicates that "my_sql_profile" is used.

2. Creating a staging table to store the SQL Profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE',schema_name=>'SCOTT');  PL/SQL procedure successfully completed.

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

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',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 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

my_linux_1:~> exp scott/tiger tables=STAGE  Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012  Copyright (c) 1982, 2007, Oracle.  All rights reserved.  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)  About to export specified tables via Conventional Path ... . . exporting table                          STAGE          1 rows exported Export terminated successfully without warnings.

4b. Import into Target Database

my_linux_1:~> imp scott/tiger tables=STAGE  Import: Release 11.2.0.3.0 - Production on Mon Feb 13 14:49:12 2012  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 and Real Application Testing options  Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses WE8MSWIN1252 character set (possible charset conversion) . importing SCOTT's objects into SCOTT . importing SCOTT's objects into SCOTT . . importing table                        "STAGE"          1 rows imported Import terminated successfully with warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130 SQL> set autotrace 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: 2872589290  -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------     1 - filter("EMPNO"=7839)

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

5b. Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified: SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE');  However, if importing to different schema, the staging schema owner needs to be changed:| SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SQLTXPLAIN');  PL/SQL procedure successfully completed.

6. Check the SQL Profile is enabled in Target Database

set lines 130
set autotrace on

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

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

#######drop

SET SERVEROUTPUT ON

DECLARE

l_sql_tune_task_id VARCHAR2(20);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (

task_name => 'test_tuning_task',

name => 'test_profile');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

BEGIN

DBMS_SQLTUNE.alter_sql_profile (

name => 'test_profile',

attribute_name => 'STATUS',

value => 'DISABLED');

END;

BEGIN

DBMS_SQLTUNE.drop_sql_profile (

name => 'test_profile',

ignore => TRUE);

END;

###########sample 0

Steps to Create and Transfer Profile from One Database to Another

set linesize 450 pagesize 0

select 'exec DBMS_SQLTUNE.DROP_SQL_PROFILE (name=>'''||name||''',ignore =>TRUE);' from dba_sql_profiles

1. Creating a staging table to store the SQL Profiles

--exec DBMS_SQLTUNE.DROP_STGTAB_SQLPROF(table_name=>'STAGE');

conn dbmgr/dbmgr

--exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE_AFA',schema_name=>'AFA');

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'STAGE_AFA',schema_name=>'DBMGR');

PL/SQL procedure successfully completed.

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

--exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'my_sql_profile');

--select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE'',profile_name=>'''||name||''');' from dba_sql_profiles

select 'exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>''STAGE_AFA'',profile_name=>'''||name||''');' from dba_sql_profiles

--conn afa/afa

conn dbmgr/dbmgr

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9bc2580006');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608434ca0012');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656042dc15000a');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165606254d8000f');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9181320004');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656081bfb10010');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_016560870ead0013');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605930f6000b');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655f9701d10005');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa4a7670007');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656083393e0011');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605bf9c7000c');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165608ae11f0014');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655faa9ab30009');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01656060f311000e');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_01655fa7fc680008');

exec DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name =>'STAGE',profile_name=>'SYS_SQLPROF_0165605fc25d000d');

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.

conn afa/afa

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

--my_linux_1:~> exp scott/tiger tables=STAGE

Export: Release 10.2.0.4.0 - Production on Sun Feb 12 17:43:21 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table STAGE 1 rows exported

Export terminated successfully without warnings.

--exp "'/ as sysdba'" tables=AFA.STAGE

exp dbmgr/dbmgr tables=STAGE_AFA

racle@localhost admin]$ exp "'/ as sysdba'" tables=AFA.STAGE

Export: Release 11.2.0.4.0 - Production on Wed Aug 22 17:14:16 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Current user changed to AFA

. . exporting table STAGE 19 rows exported

Export terminated successfully without warnings.

D:\tmp\spa\result_1_1\tunning\expdat.dmp

4b. Import into Target Database

--imp "'/ as sysdba'" tables=STAGE fromuser=afa touser=afa

imp dbmgr/dbmgr tables=STAGE_AFA fromuser=DBMGR touser=DBMGR

Import: Release 11.2.0.4.0 - Production on Thu Aug 23 01:31:06 2018

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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 US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

export client uses ZHS16GBK character set (possible charset conversion)

. importing AFA's objects into AFA

. . importing table "STAGE" 19 rows imported

Import terminated successfully without warnings.

5. Unpack the SQL Profiles

5a. Test before unpacking

SQL> set lines 130

SQL> set autotrace 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: 2872589290

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

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

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("EMPNO"=7839)

Note: The NO_INDEX hint has been honoured and a FULL table scan has been done on EMP

error:

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

ERROR at line 1:

ORA-38171: Insufficient privileges for SQL management object operation

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.DBMS_SMB", line 83

ORA-06512: at "SYS.DBMS_SQLTUNE", line 7657

ORA-06512: at "SYS.DBMS_SQLTUNE", line 6349

ORA-06512: at line 1

fix:

grant execute on DBMS_SQLTUNE to afa;

grant administer SQL MANAGEMENT OBJCET to afa;

5b. Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:

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

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

However, if importing to different schema, the staging schema owner needs to be changed:|

---SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'STAGE',staging_schema_owner => 'SQLTXPLAIN');

PL/SQL procedure successfully completed.

6. Check the SQL Profile is enabled in Target Database

set lines 130

set autotrace on

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

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

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

| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 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

############sample 1