如何将sqlprofile导到另一个数据库

转自 http://www.dbaroad.me/archives/2011/07/move_sql_profiles.html

 

发现培训环境上某条语句与生产环境一样存在性能问题，生产环境用SQL Profile进行了固化，所以决定把生产环境的SQL Profile都导到测试环境。

生产环境导出：

SYS@DB1> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'S_SQLPROFILE',schema_name=>'PERFSTAT');
 
PL/SQL procedure successfully completed.
 
SYS@DB1> begin
  2  DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (PROFILE_NAME=>'PROF_2mdwk0babv4m4_3381668933',
  3                                    PROFILE_CATEGORY=>'DEFAULT',
  4                                    STAGING_TABLE_NAME=>'S_SQLPROFILE',
  5                                    STAGING_SCHEMA_OWNER=>'PERFSTAT');
  6
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
 
SYS@DB1> select count(*) from perfstat.S_SQLPROFILE;
 
  COUNT(*)
----------
         1
 
exp perfstat/perf1206stat@DB10G1 tables=S_SQLPROFILE file=/oradata/DB1/arch1/s_sqlprofile.dmp

培训环境导入：

imp "'/ as sys dba'" fromuser=perfstat touser=perfstat tables=S_SQLPROFILE file=/oradata/DB1/arch1/s_sqlprofile.dmp
 
应用指定语句的SQL Profile：
SQL> BEGIN
  2  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(PROFILE_NAME=>'PROF_2mdwk0babv4m4_3381668933',
  3                                     PROFILE_CATEGORY=>'DEFAULT',
  4                                     REPLACE => TRUE,
  5                                     STAGING_TABLE_NAME => 'S_SQLPROFILE',
  6                                     STAGING_SCHEMA_OWNER=>'PERFSTAT');
  7  end;
  8  /   
 
PL/SQL procedure successfully completed.
 
 
SQL> select name,category,signature,created,status,force_matching from dba_sql_profiles ; 
 
NAME                           CATEGORY    SIGNATURE CREATED         STATUS   FOR
------------------------------ ---------- ---------- --------------- -------- ---
PROF_2mdwk0babv4m4_3381668933  DEFAULT    1.4157E+19 11-JUL-11       ENABLED  YES

应用全部的SQL Profile:

SQL> BEGIN
  2  DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,
  3                                     STAGING_TABLE_NAME => 'S_SQLPROFILE',
  4                                     STAGING_SCHEMA_OWNER=>'PERFSTAT');
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> select name,category,signature,created,status,force_matching from dba_sql_profiles ; 
 
NAME                           CATEGORY    SIGNATURE CREATED         STATUS   FOR
------------------------------ ---------- ---------- --------------- -------- ---
PROF_1fxyut012wg2z_3752316588  DEFAULT    1.3069E+18 11-JUL-11       ENABLED  YES
coe_cj11wn4gb2s1p_1357173250   DEFAULT    1.7851E+18 11-JUL-11       ENABLED  YES
PROF_7fu6x9gpfkmg7_1525729015  DEFAULT    3.9492E+18 11-JUL-11       ENABLED  NO
PROF_4821mmjbznbcu_3857570453  DEFAULT    8.6484E+18 11-JUL-11       ENABLED  NO
PROF_d52xgh9k2qg2t_1288318737  DEFAULT    9.9387E+18 11-JUL-11       ENABLED  NO
PROF_3wwtb8038pm9u_2575594700  DEFAULT    1.3945E+19 11-JUL-11       ENABLED  NO
PROF_2mdwk0babv4m4_3381668933  DEFAULT    1.4157E+19 11-JUL-11       ENABLED  YES
PROF_6u0ta5dz9xrg7_49043822    DEFAULT    1.4990E+19 11-JUL-11       ENABLED  NO
 
8 rows selected.