首页 > 网络 > 其他 > 正文
SQLT导入测试数据 及SQL Profile的使用
2017-03-20 09:28:05       个评论      
收藏    我要投稿

SQLT导入测试数据 及SQL Profile的使用:性能分析过程中,经常会遇到生产库出现SQL的性能问题,但是,我们没有办法在生产库上做很多动作,需要将这个SQL的对应的表结构信息,统计信息导入到测试库进行测试(没有真实的测试数据,只有统计信息)

本次测试目的,客户环境导出了Test和product环境的相同SQL的不同执行计划的SQLT报告。

TEST环境是11.2.0.1版本

Product是11.2.0.2版本

我们会将两个SQLT报告分别导入到11.2.0.1和11.2.0.2的测试环境中,然后将11.2.0.1版本,好的执行计划,作为profile导出到11.2.0.2中。让11.2.0.2也根据11.2.0.1中的SQL一样,使用相同的执行计划。

第一部分,导入testcase环境,只导入统计信息,和表的元数据。没有真实数据~~~~~~

1. 参考sqlt_s39917_readme.html(客户SQLT输出的文件中,就已经包含这个readme.html文件)

也可以参考文档How to Use SQLT (SQLTXPLAIN) to Create a Testcase Without Row Data (Doc ID 1470811.1)

ImplementSQLTTestCase(TC)
Custommode
CreatetestcaseuserandschemaobjectsconnectingasSYSDBA:
sqlplus/assysdba
STARTsqlt_s39917_metadata.sql

Purgepre-existings39917fromlocalSQLTrepositoryconnectedasSYSDBA:
STARTsqlt_s39917_purge.sql

ImportSQLTrepositoryfors39917(provideSQLTXPLAINpassword):
HOSimpsqltxplainFILE=sqlt_s39917_exp.dmpLOG=sqlt_s39917_imp.logTABLES=sqlt%IGNORE=Y

RestoreCBOschemastatisticsfortestcaseuserconnectedasSYSDBA:
STARTsqlt_s39917_restore.sql

RestoreCBOsystemstatisticsconnectedasSYSDBA:

STARTsqlt_s39917_system_stats.sql

SettheCBOenvironmentconnectingastestcaseuserTC39917(includeoptionaltestcaseusersuffix):
CONNTC39917/TC39917
STARTsqlt_s39917_set_cbo_env.sql

Executetestcase:
STARTtc.sql

 

2. 执行tc.sql

注意:每次sqlplus连接进入后,都需要执行最后两步

CONNTC39917/TC39917
STARTsqlt_s39917_set_cbo_env.sql
Executetestcase:
STARTtc.sql----需要修改q.sql中对应表的用户名为TC39917

TC.sql执行结果如下

SQL>starttc.sql
SQL>REMExecutesSQLonTCthenproducesexecutionplan.Justexecute"@tc.sql"fromsqlplus.
SQL>SETAPPIOFFSERVEROUTOFF;
SQL>@@q.sql
SQL>SELECTDistinctfc_acc.blnctr_acc,
2fc_acc.shortcut,
3fc_acc.acc_name,
4fc_subject.amount_money_sign,
5fc_acc.subject_code,
6fc_acc.corp_code,
7fc_acc.net_code
8FROMTC39917.fc_acc,TC39917.fc_user_corp,TC39917.fc_subject
9WHERE((fc_acc.corp_code=fc_user_corp.corp_code)OR
10(acc_cussentisnotNulloracc_cussent<>''))
11AND(fc_acc.subject_code=fc_subject.subject_code)
12and((fc_acc.checker_codeisNotNull))
13AND(fc_acc.acc_state<>'R')
14AND(fc_acc.cur_code=01)
15and(fc_acc.acc_state<>'D')
16AND(fc_acc.net_code=1000)
17AND(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
18(union_accisnotNullorunion_acc<>''));
norowsselected
SQL>@@plan.sql
SQL>REMDisplaysplanformostrecentlyexecutedSQL.Justexecute"@plan.sql"fromsqlplus.
SQL>SETPAGES2000LIN180;
SQL>SPOplan.log;
SQL>--SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASICROWSCOSTPREDICATE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINEDSQLSTATEMENT:
------------------------
SELECTDistinctfc_acc.blnctr_acc,fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign,fc_acc.subject_code,
fc_acc.corp_code,fc_acc.net_codeFROM
fc_acc,fc_user_corp,fc_subjectWHERE((fc_acc.corp_code=
fc_user_corp.corp_code)OR(acc_cussentisnotNullor
acc_cussent<>''))AND(fc_acc.subject_code=
fc_subject.subject_code)and((fc_acc.checker_codeisNotNull))
AND(fc_acc.acc_state<>'R')AND(fc_acc.cur_code=01)and
(fc_acc.acc_state<>'D')AND(fc_acc.net_code=1000)AND
(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
(union_accisnotNullorunion_acc<>''))
Planhashvalue:31035937
------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost(%CPU)|
------------------------------------------------------------------------------
|0|SELECTSTATEMENT|||133(100)|
|1|HASHUNIQUE||4573K|133(3)|
|2|CONCATENATION||||
|3|MERGEJOINCARTESIAN||1|42(0)|
|4|NESTEDLOOPS||||
|5|NESTEDLOOPS||1|36(0)|
|*6|TABLEACCESSFULL|FC_ACC|1|35(0)|
|*7|INDEXUNIQUESCAN|PK_FC_SUBJECT|1|0(0)|
|*8|TABLEACCESSBYINDEXROWID|FC_SUBJECT|1|1(0)|
|9|BUFFERSORT||6210|41(0)|
|10|TABLEACCESSFULL|FC_USER_CORP|6210|6(0)|
|11|MERGEJOINCARTESIAN||1|42(0)|
|12|NESTEDLOOPS||||
|13|NESTEDLOOPS||1|36(0)|
|*14|TABLEACCESSFULL|FC_ACC|1|35(0)|
|*15|INDEXUNIQUESCAN|PK_FC_SUBJECT|1|0(0)|
|*16|TABLEACCESSBYINDEXROWID|FC_SUBJECT|1|1(0)|
|17|BUFFERSORT||6210|41(0)|
|18|TABLEACCESSFULL|FC_USER_CORP|6210|6(0)|
|*19|HASHJOIN||122|47(3)|
|*20|HASHJOIN||7|41(3)|
|*21|TABLEACCESSFULL|FC_ACC|8|35(0)|
|22|TABLEACCESSFULL|FC_SUBJECT|636|5(0)|
|23|TABLEACCESSFULL|FC_USER_CORP|6210|6(0)|
------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
6-filter((TO_NUMBER("FC_ACC"."CUR_CODE")=1AND
"FC_ACC"."ACC_STATE"<>'R'AND"FC_ACC"."ACC_STATE"<>'D'AND
"ACC_CUSSENT"<>''AND"FC_ACC"."CHECKER_CODE"ISNOTNULLAND
TO_NUMBER("FC_ACC"."NET_CODE")=1000))
7-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
8-filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
14-filter(("ACC_CUSSENT"ISNOTNULLAND
TO_NUMBER("FC_ACC"."CUR_CODE")=1AND"FC_ACC"."ACC_STATE"<>'R'AND
"FC_ACC"."ACC_STATE"<>'D'AND"FC_ACC"."CHECKER_CODE"ISNOTNULLAND
TO_NUMBER("FC_ACC"."NET_CODE")=1000ANDLNNVL("ACC_CUSSENT"<>'')))
15-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
16-filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
19-access("FC_ACC"."CORP_CODE"="FC_USER_CORP"."CORP_CODE")
20-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
21-filter((TO_NUMBER("FC_ACC"."CUR_CODE")=1AND
"FC_ACC"."ACC_STATE"<>'R'AND"FC_ACC"."ACC_STATE"<>'D'AND
"FC_ACC"."CHECKER_CODE"ISNOTNULLAND
TO_NUMBER("FC_ACC"."NET_CODE")=1000ANDLNNVL("ACC_CUSSENT"ISNOT
NULL)ANDLNNVL("ACC_CUSSENT"<>'')))
76rowsselected.
SQL>SPOOFF;
SQL>

假设,上面的执行计划是我们期望的,性能优异的。我需要导出为profile,并导入到性能较差的数据库

 

~~~~~~~~第二部分,导出11.2.0.1的profile~~~~~~~~~~~~~~~~~~~~

3. 生成profile

SQL>@/home/oracle/Bright-temp/sqlt/utl/coe_xfr_sql_profile.sqlcwju3syt2mx9a31035937
SQL>SPOcoe_xfr_sql_profile.log;
SQL>SETDEFONTERMOFFECHOONFEEDOFFVEROFFHEAONLIN2000PAGES100LONG8000000LONGC800000TRIMSONTIOFFTIMIOFFSERVEROUTONSIZE1000000NUMF""SQLPSQL>;
Parameter1:
SQL_ID(required)
PLAN_HASH_VALUEAVG_ET_SECS
--------------------------
31035937.014
Parameter2:
PLAN_HASH_VALUE(required)
Valuespassedtocoe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID:"cwju3syt2mx9a"
PLAN_HASH_VALUE:"31035937"

检查生成的profile执行文件,需要修改force_match => TRUE ,并且在新的数据库执行前,还需要针对新的数据库的testcase用户名,修改其中对应的表的用户名。

vi coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql

 

~~~~~~~~第三部分,导入性能差的SQLT到11.2.0.2,并查看执行计划~~~~~~~~~~~~~~

4. 导入SQLT_product到11.2.0.2上,查看11.2.0.2上这个SQL的执行计划

SQL>SPOOFF;
SQL>STARTtc.sql
SQL>REMExecutesSQLonTCthenproducesexecutionplan.Justexecute"@tc.sql"fromsqlplus.
SQL>SETAPPIOFFSERVEROUTOFF;
SQL>@@q.sql
SQL>SELECTDistinctfc_acc.blnctr_acc,
2fc_acc.shortcut,
3fc_acc.acc_name,
4fc_subject.amount_money_sign,
5fc_acc.subject_code,
6fc_acc.corp_code,
7fc_acc.net_code
8FROMTC34646.fc_acc,TC34646.fc_user_corp,TC34646.fc_subject
9WHERE((fc_acc.corp_code=fc_user_corp.corp_code)OR
10(acc_cussentisnotNulloracc_cussent<>''))
11AND(fc_acc.subject_code=fc_subject.subject_code)
12and((fc_acc.checker_codeisNotNull))
13AND(fc_acc.acc_state<>'R')
14AND(fc_acc.cur_code=01)
15and(fc_acc.acc_state<>'D')
16AND(fc_acc.net_code=1000)
17AND(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
18(union_accisnotNullorunion_acc<>''));
norowsselected
SQL>@@plan.sql
SQL>REMDisplaysplanformostrecentlyexecutedSQL.Justexecute"@plan.sql"fromsqlplus.
SQL>SETPAGES2000LIN180;
SQL>SPOplan.log;
SQL>--SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASICROWSCOSTPREDICATE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINEDSQLSTATEMENT:
------------------------
SELECTDistinctfc_acc.blnctr_acc,fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign,fc_acc.subject_code,
fc_acc.corp_code,fc_acc.net_codeFROM
TC34646.fc_acc,TC34646.fc_user_corp,TC34646.fc_subjectWHERE
((fc_acc.corp_code=fc_user_corp.corp_code)OR(acc_cussentis
notNulloracc_cussent<>''))AND(fc_acc.subject_code=
fc_subject.subject_code)and((fc_acc.checker_codeisNotNull))
AND(fc_acc.acc_state<>'R')AND(fc_acc.cur_code=01)and
(fc_acc.acc_state<>'D')AND(fc_acc.net_code=1000)AND
(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
(union_accisnotNullorunion_acc<>''))
Planhashvalue:561502789
--------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost(%CPU)|
--------------------------------------------------------------------
|0|SELECTSTATEMENT|||278K(100)|
|1|HASHUNIQUE||7469K|278K(1)|
|*2|HASHJOIN||7469K|3321(6)|
|*3|TABLEACCESSFULL|FC_ACC|1156|38(3)|
|4|MERGEJOINCARTESIAN||4189K|3197(3)|
|5|TABLEACCESSFULL|FC_SUBJECT|636|5(0)|
|6|BUFFERSORT||6587|3192(3)|
|7|TABLEACCESSFULL|FC_USER_CORP|6587|5(0)|
--------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
filter((("FC_ACC"."CORP_CODE"="FC_USER_CORP"."CORP_CODE"OR
"ACC_CUSSENT"ISNOTNULLOR"ACC_CUSSENT"<>'')AND
("FC_ACC"."ACC_TYPE_CODE"='02'OR"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'
OR"UNION_ACC"ISNOTNULLOR"UNION_ACC"<>'')))
3-filter((TO_NUMBER("FC_ACC"."CUR_CODE")=1AND
"FC_ACC"."ACC_STATE"<>'D'AND"FC_ACC"."CHECKER_CODE"ISNOTNULLAND
"FC_ACC"."ACC_STATE"<>'R'ANDTO_NUMBER("FC_ACC"."NET_CODE")=1000))
42rowsselected.
SQL>SPOOFF;

5. 在将11.2.0.1的profile导入到11.2.0.2,然后查看profile是否生效

执行coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql前,选需要修改SQL文本为

SELECTDistinctfc_acc.blnctr_acc,
fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign,
fc_acc.subject_code,
fc_acc.corp_code,
fc_acc.net_code
FROM
TC34646.fc_acc,<<<<<<<<<<<<<<<<<<<<增加用户TC34646
TC34646.fc_user_corp,
TC34646.fc_subject

6. 执行coe脚本

sqlplus/assysdba
SQL>@/home/oracle/Bright-temp/sqlt_s39917_xecute_TEST/coe_xfr_sql_profile_cwju3syt2mx9a_31035937.sql

7. 再次执行tc.sql

SQL>CONNTC34646/TC34646
SQL>STARTsqlt_s34646_set_cbo_env.sql
SQL>STARTtc.sql
SQL>REMExecutesSQLonTCthenproducesexecutionplan.Justexecute"@tc.sql"fromsqlplus.
SQL>SETAPPIOFFSERVEROUTOFF;
SQL>@@q.sql
SQL>SELECTDistinctfc_acc.blnctr_acc,
2fc_acc.shortcut,
3fc_acc.acc_name,
4fc_subject.amount_money_sign,
5fc_acc.subject_code,
6fc_acc.corp_code,
7fc_acc.net_code
8FROMTC34646.fc_acc,TC34646.fc_user_corp,TC34646.fc_subject
9WHERE((fc_acc.corp_code=fc_user_corp.corp_code)OR
10(acc_cussentisnotNulloracc_cussent<>''))
11AND(fc_acc.subject_code=fc_subject.subject_code)
12and((fc_acc.checker_codeisNotNull))
13AND(fc_acc.acc_state<>'R')
14AND(fc_acc.cur_code=01)
15and(fc_acc.acc_state<>'D')
16AND(fc_acc.net_code=1000)
17AND(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
18(union_accisnotNullorunion_acc<>''));
norowsselected
SQL>@@plan.sql
SQL>REMDisplaysplanformostrecentlyexecutedSQL.Justexecute"@plan.sql"fromsqlplus.
SQL>SETPAGES2000LIN180;
SQL>SPOplan.log;
SQL>--SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR);
SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'BASICROWSCOSTPREDICATE'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINEDSQLSTATEMENT:
------------------------
SELECTDistinctfc_acc.blnctr_acc,fc_acc.shortcut,
fc_acc.acc_name,
fc_subject.amount_money_sign,fc_acc.subject_code,
fc_acc.corp_code,fc_acc.net_codeFROM
TC34646.fc_acc,TC34646.fc_user_corp,TC34646.fc_subjectWHERE
((fc_acc.corp_code=fc_user_corp.corp_code)OR(acc_cussentis
notNulloracc_cussent<>''))AND(fc_acc.subject_code=
fc_subject.subject_code)and((fc_acc.checker_codeisNotNull))
AND(fc_acc.acc_state<>'R')AND(fc_acc.cur_code=01)and
(fc_acc.acc_state<>'D')AND(fc_acc.net_code=1000)AND
(fc_acc.acc_type_code='02'orfc_subject.sys_subject_Code='1010'or
(union_accisnotNullorunion_acc<>''))
Planhashvalue:31035937
------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Cost(%CPU)|
------------------------------------------------------------------------------
|0|SELECTSTATEMENT|||8755(100)|
|1|HASHUNIQUE||7469K|8755(1)|
|2|CONCATENATION||||
|3|MERGEJOINCARTESIAN||226K|245(3)|
|4|NESTEDLOOPS||||
|5|NESTEDLOOPS||34|73(2)|
|*6|TABLEACCESSFULL|FC_ACC|35|38(3)|
|*7|INDEXUNIQUESCAN|PK_FC_SUBJECT|1|0(0)|
|*8|TABLEACCESSBYINDEXROWID|FC_SUBJECT|1|1(0)|
|9|BUFFERSORT||6587|244(3)|
|10|TABLEACCESSFULL|FC_USER_CORP|6587|5(0)|
|11|MERGEJOINCARTESIAN||2285|46(3)|
|12|NESTEDLOOPS||||
|13|NESTEDLOOPS||1|39(3)|
|*14|TABLEACCESSFULL|FC_ACC|1|38(3)|
|*15|INDEXUNIQUESCAN|PK_FC_SUBJECT|1|0(0)|
|*16|TABLEACCESSBYINDEXROWID|FC_SUBJECT|1|1(0)|
|17|BUFFERSORT||6587|45(3)|
|18|TABLEACCESSFULL|FC_USER_CORP|6587|7(0)|
|*19|HASHJOIN||193|51(4)|
|*20|HASHJOIN||11|43(3)|
|*21|TABLEACCESSFULL|FC_ACC|11|38(3)|
|22|TABLEACCESSFULL|FC_SUBJECT|636|5(0)|
|23|TABLEACCESSFULL|FC_USER_CORP|6587|7(0)|
------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
6-filter((TO_NUMBER("FC_ACC"."CUR_CODE")=1AND"ACC_CUSSENT"<>''
AND"FC_ACC"."ACC_STATE"<>'D'AND"FC_ACC"."CHECKER_CODE"ISNOTNULL
AND"FC_ACC"."ACC_STATE"<>'R'ANDTO_NUMBER("FC_ACC"."NET_CODE")=1000))
7-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
8-filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
14-filter(("ACC_CUSSENT"ISNOTNULLAND
TO_NUMBER("FC_ACC"."CUR_CODE")=1AND"FC_ACC"."ACC_STATE"<>'D'AND
"FC_ACC"."CHECKER_CODE"ISNOTNULLAND"FC_ACC"."ACC_STATE"<>'R'AND
TO_NUMBER("FC_ACC"."NET_CODE")=1000ANDLNNVL("ACC_CUSSENT"<>'')))
15-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
16-filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
19-access("FC_ACC"."CORP_CODE"="FC_USER_CORP"."CORP_CODE")
20-access("FC_ACC"."SUBJECT_CODE"="FC_SUBJECT"."SUBJECT_CODE")
filter(("FC_ACC"."ACC_TYPE_CODE"='02'OR
"FC_SUBJECT"."SYS_SUBJECT_CODE"='1010'OR"UNION_ACC"ISNOTNULLOR
"UNION_ACC"<>''))
21-filter((TO_NUMBER("FC_ACC"."CUR_CODE")=1AND
"FC_ACC"."ACC_STATE"<>'D'AND"FC_ACC"."CHECKER_CODE"ISNOTNULLAND
"FC_ACC"."ACC_STATE"<>'R'ANDTO_NUMBER("FC_ACC"."NET_CODE")=1000AND
LNNVL("ACC_CUSSENT"ISNOTNULL)ANDLNNVL("ACC_CUSSENT"<>'')))
74rowsselected.
SQL>SPOOFF;
SQL>

最终,可以看到,在11.2.0.2上,也按照11.2.0.1上的执行计划进行执行SQL了。

这里要强调一下SQLT真的是非常使用的SQL 调优工具。而且非常易用,很多步骤都在SQLT的HTML文件中,逐步指导。建议大家学习使用。

点击复制链接 与好友分享!回本站首页
上一篇:Azure实践系列(4)启用和配置免费的MFA
下一篇:Linux的10个最危险的命令
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做实用的IT技术学习网站