本文小编为大家详细介绍“如何利用sqlprofile固定执行计划并将执行计划导入到新库”，内容详细，步骤清晰，细节处理妥当，希望这篇“如何利用sqlprofile固定执行计划并将执行计划导入到新库”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。

Linux 11G R2 导入到 windows 11G R2

源库：

SQL>select*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionPL/SQLRelease11.2.0.4.0-ProductionCORE11.2.0.4.0ProductionTNSforLinux:Version11.2.0.4.0-ProductionNLSRTLVersion11.2.0.4.0-Production

目标库：

SQL>select*fromv$version;BANNER--------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionPL/SQLRelease11.2.0.4.0-ProductionCORE11.2.0.4.0ProductionTNSfor64-bitWindows:Version11.2.0.4.0-ProductionNLSRTLVersion11.2.0.4.0-Production1.1 创建表

SQL>connlei/leiConnected.SQL>createtablettasselect*fromdba_objects;Tablecreated.SQL>createindexidex_01ontt(object_id);Indexcreated.1.2 收集统计信息

SQL>execdbms_stats.gather_table_stats('LEI','TT',cascade=>true);PL/SQLproceduresuccessfullycompleted.1.3 生成执行计划

SQL>explainplanforselectobject_NAMEFROMTTWHEREobject_id=2;Exlained.SQL>select*fromtable(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Planhashvalue:2974445191--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|30|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TT|1|30|2(0)|00:00:01||*2|INDEXRANGESCAN|IDEX_01|1||1(0)|00:00:01|--------------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OBJECT_ID"=2)14rowsselected.

可以看到是走索引的。

SQL>select/*+full(tt)*/*fromttwhereobject_id=2;OWNER------------------------------OBJECT_NAME--------------------------------------------------------------------------------SUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPE-------------------------------------------------------------------------CREATEDLAST_DDL_TIMESTAMPSTATUSTGSNAMESPACE---------------------------------------------------------EDITION_NAME------------------------------SYSC_OBJ#22CLUSTEROWNER------------------------------OBJECT_NAME--------------------------------------------------------------------------------SUBOBJECT_NAMEOBJECT_IDDATA_OBJECT_IDOBJECT_TYPE-------------------------------------------------------------------------CREATEDLAST_DDL_TIMESTAMPSTATUSTGSNAMESPACE---------------------------------------------------------EDITION_NAME------------------------------24-AUG-1324-AUG-132013-08-24:11:37:35VALIDNNN51.5 查看outline

SQL>explainplanforselect/*+full(tt)*/*fromttwhereobject_id=2;Explained.SQL>select*fromtable(dbms_xplan.display(null,null,'outline'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Planhashvalue:264906180--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||1|98|344(1)|00:00:05||*1|TABLEACCESSFULL|TT|1|98|344(1)|00:00:05|--------------------------------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------/*+BEGIN_OUTLINE_DATAFULL(@"SEL$1""TT"@"SEL$1")OUTLINE_LEAF(@"SEL$1")ALL_ROWSDB_VERSION('11.2.0.4')OPTIMIZER_FEATURES_ENABLE('11.2.0.4')IGNORE_OPTIM_EMBEDDED_HINTSEND_OUTLINE_DATA*/PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("OBJECT_ID"=2)27rowsselected.1.6 生成sql profile

SQL>declarev_hintssys.sqlprof_attr;beginv_hints:=sys.sqlprof_attr('FULL(@"SEL$1""TT"@"SEL$1")');--从上面获得dbms_sqltune.import_sql_profile('select*fromttwhereobject_id=2',--sql语句v_hints,'TT_LEI_20170510',--profile名称force_match=>true);end;/8910PL/SQLproceduresuccessfullycompleted.1.7 查看profile是否生效

SQL>explainplanforselect*fromttwhereobject_id=2;Explained.SQL>select*fromtable(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Planhashvalue:264906180--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------|0|SELECTSTATEMENT||1|98|344(1)|00:00:05||*1|TABLEACCESSFULL|TT|1|98|344(1)|00:00:05|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------1-filter("OBJECT_ID"=2)Note---------SQLprofile"TT_LEI_20170510"usedforthisstatement17rowsselected.

可以看到已经生效了。

SQL>execDBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'TEST_TT_PROFILE1',schema_name=>'LEI');PL/SQLproceduresuccessfullycompleted.SQL>execDBMS_SQLTUNE.PACK_STGTAB_SQLPROF(staging_table_name=>'TEST_TT_PROFILE1',profile_name=>'TT_LEI_20170510');PL/SQLproceduresuccessfullycompleted

名称随便。

更多关于DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF的说明，请查看官方文档：
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm#CACBCEEH

[oracle@dg-p~]$expdpsystem/oracledumpfile=tt.dmpdirectory=lei_dirschemas=leiExport:Release11.2.0.4.0-ProductiononWedMay1020:09:282017Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.Connectedto:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptionsStarting"SYSTEM"."SYS_EXPORT_SCHEMA_01":system/********dumpfile=tt.dmpdirectory=lei_dirschemas=leiEstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:10.18MBProcessingobjecttypeSCHEMA_EXPORT/USERProcessingobjecttypeSCHEMA_EXPORT/SYSTEM_GRANTProcessingobjecttypeSCHEMA_EXPORT/ROLE_GRANTProcessingobjecttypeSCHEMA_EXPORT/DEFAULT_ROLEProcessingobjecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLEProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeSCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS..exported"LEI"."TT"8.366MB86269rows..exported"LEI"."TEST_TT_PROFILE1"22.02KB1rowsMastertable"SYSTEM"."SYS_EXPORT_SCHEMA_01"successfullyloaded/unloaded******************************************************************************DumpfilesetforSYSTEM.SYS_EXPORT_SCHEMA_01is:/u01/backup/tt.dmpJob"SYSTEM"."SYS_EXPORT_SCHEMA_01"successfullycompletedatWedMay1020:09:402017elapsed000:00:122.3 导入到新环境2.3.1 创建用户

SQL>createuserleiidentifiedbylei;用户已创建。SQL>grantdba,resource,connecttolei;授权成功。SQL>C:/Users/Administrator>impdpsystem/oracledumpfile=tt.dmpdirectory=lei_dirschemas=LEIImport:Release11.2.0.4.0-Productionon星期三5月1012:05:092017Copyright(c)1982,2011,Oracleand/oritsaffiliates.Allrightsreserved.连接到:OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionWiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions已成功加载/卸载了主表"SYSTEM"."SYS_IMPORT_SCHEMA_01"启动"SYSTEM"."SYS_IMPORT_SCHEMA_01":system/********dumpfile=tt.dmpdirectory=lei_dirschemas=LEI处理对象类型SCHEMA_EXPORT/USERORA-31684:对象类型USER:"LEI"已存在处理对象类型SCHEMA_EXPORT/SYSTEM_GRANT处理对象类型SCHEMA_EXPORT/ROLE_GRANT处理对象类型SCHEMA_EXPORT/DEFAULT_ROL处理对象类型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA处理对象类型SCHEMA_EXPORT/TABLE/TABLE处理对象类型SCHEMA_EXPORT/TABLE/TABLE_DATA..导入了"LEI"."TT"8.366MB86269行..导入了"LEI"."TEST_TT_PROFILE1"22.02KB1行处理对象类型SCHEMA_EXPORT/TABLE/INDEX/INDEX处理对象类型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STAISTICS处理对象类型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS作业"SYSTEM"."SYS_IMPORT_SCHEMA_01"已经完成,但是有1个错误(于星期三5月1012:05:122017elapsed000:00:03完成)2.3.2 查看新库中的执行计划

SQL>connlei/lei已连接。SQL>explainplanforselect*fromttwhereobject_id=2;已解释。SQL>select*fromtable(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Planhashvalue:2974445191----------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|98|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TT|1|98|2(0)|00:00:01||*2|INDEXRANGESCAN|IDEX_01|1||1(0)|00:00:01|---------------------------------------------------------------------------------------PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("OBJECT_ID"=2)已选择14行。

可以看到默认还是走索引。

SQL>EXECDBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace=>TRUE,staging_table_name=>'TEST_TT_PROFILE1');PL/SQL过程已成功完成。2.3.4 再次查看执行计划

SQL>explainplanforselect*fromttwhereobject_id=2;已解释。SQL>select*fromtable(dbms_xplan.display());PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Planhashvalue:264906180--------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------|0|SELECTSTATEMENT||1|98|344(1)|00:00:05||*1|TABLEACCESSFULL|TT|1|98|344(1)|00:00:05|--------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------1-filter("OBJECT_ID"=2)Note------SQLprofile"TT_LEI_20170510"usedforthisstatement已选择17行。

可以看到，执行计划已经使用profile，走了全表扫描。
到此实验结束。

读到这里，这篇“如何利用sqlprofile固定执行计划并将执行计划导入到新库”文章已经介绍完毕，想要掌握这篇文章的知识点还需要大家自己动手实践使用过才能领会，如果想了解更多相关内容的文章，欢迎关注亿速云行业资讯频道。