SQL调优工具.docx
- 文档编号:30767845
- 上传时间:2023-08-23
- 格式:DOCX
- 页数:14
- 大小:114.08KB
SQL调优工具.docx
《SQL调优工具.docx》由会员分享,可在线阅读,更多相关《SQL调优工具.docx(14页珍藏版)》请在冰豆网上搜索。
SQL调优工具
OracleSQL调优工具
技术文件编号:
版本:
V1.0
共页
(包括封面)
拟制陈文文
审核
会签
标准化
批准
2011年10月
目录
1问题3
2推荐工具3
3工具简介3
4SQL优化3
4.1执行计划3
4.1.1Pl/sqldev查看执行计划的方法:
4
4.1.2执行计划优劣4
4.1.3根据执行计划调整SQL4
4.2利用STA优化语句5
1问题
经统计,80%的数据库问题是由应用软件中的BadSQL引起的。
因此,大部分的数据库优化工作是在应用级完成。
应用级的优化即对SQL的优化。
SQL优化是一个复杂繁琐的过程,可以借助工具来简化这个工作。
2推荐工具
SQL调优的工具五花八门,但是所有的优化工具都是基于Oracle的STA工具开发的。
这里将我平时使用的工具介绍下。
(PL/SQLDEV+OracleSTA)。
其他的工具,由于在试用用的过程中,用户体验不是很好,故不做介绍。
如LECCOSQLExportProforOracle需要oracle9i的客户端等等,QuestSQLOptimizer很耗资源。
而我们平时都使用PL/SQLDEV来做开发,故推荐使用PL/SQLDEV+OracleSTA。
3工具简介
PL/SQLDeveloper是一个集成开发环境,专门面向Oracle数据库存储程序单元的开发。
如今,有越来越多的商业逻辑和应用逻辑转向了OracleServer,因此,PL/SQL编程也成了整个开发过程的一个重要组成部分。
PL/SQLDeveloper侧重于易用性、代码品质和生产力,充分发挥Oracle应用程序开发过程中的主要优势。
STA(SQLTuningAdvisor;SQL优化器)是Oracle10g中推出的帮助DBA优化工具,它的特点是简单、智能,DBA值需要调用函数就可以给出一个性能很差的语句的优化结果。
下面介绍一下它的使用。
使用STA一定要保证优化器是CBO模式下
4SQL优化
4.1执行计划
任何SQL的优化都是从执行计划开始的。
只有了解执行计划才能判断SQL的优劣,才能有的放矢,进一步优化。
4.1.1Pl/sqldev查看执行计划的方法:
A)在SQLWindow中,直接按“F5”,即可查看目的SQL的执行计划
B)打开ExplainPlanWindow,然后输入SQL,点击“执行“
4.1.2执行计划优劣
执行计划没有绝对的优劣,优劣是相对的。
衡量执行计划的优劣主要是看SQL的COST与BYTES。
COST与BYTES越小执行计划就越优。
优化SQL的目的就是降低COST和BYTES。
4.1.3根据执行计划调整SQL
如下图的执行计划
select
e.lgc_eqp_id,
e.nameeqp_name,
e.noeqp_no,
e.res_spec_ideqp_res_spec_id,
p.lgc_port_id,
p.nameport_name,
p.noport_no,
p.res_spec_idport_res_spec_id,
p.direction_idport_direction_id,
p.opr_state_idport_opr_state_id,
p.rate_idport_rate_id
fromlgc_equipmente
innerjoinlgc_portpon(e.lgc_eqp_id=p.lgc_eqp_id)
wheree.lgc_eqp_id=131
可以看出,这条SQL的总的Cost是20,而其中的对LGC_PORT的全表扫描占了19,因此如果可以对LGC_PORT扫描进行优化的话,这SQL的成本将下降。
从SQL可以看到,lgc_equipment与lgc_portp内连接,连接栏位为lgc_eqp_id
说明:
并不是说全表扫描就比索引扫描性能差。
全表扫描是扫描表的HWM下的所有数据块,而索引扫描是选扫描索引块,在索引块中取得所要的数据的ROWID,再根据ROWID扫描数据库。
因此当选择性高的话,索引扫描效能高。
反之者全表扫描效能高。
查看lgc_port这个表的信息
表LGC_PORT没有可用的索引可以使用,故走全表扫描。
对lgc_eqp_id创建一个索引测试,
建完索引后,执行计划的cost变为3,相对于前一个计划成本下降。
4.2利用STA优化语句
STA是用起来很简单(只要你会调用存储过程,都能使用这个工具),三个步骤就可以完成一次语句调优。
测试环境创建:
SQL>CREATETABLEbigtabASSELECTrownumas"id",a.*FROMdba_objectsa;
Tablecreated.
SQL>createtablesmalltabasselectrownumas"id",a.*FROMdba_tablesa;
Tablecreated.
SQL>DECLARE
nNUMBER;
BEGIN
FORnIN1..100
LOOP
INSERTINTObigtabSELECTrownumas"id",a.*FROMdba_objectsa;
COMMIT;
ENDLOOP;
END;
/
PL/SQLproceduresuccessfullycompleted.
这里创建一张大表和一张小表,并且都没有索引,下面执行一个查询:
SQL>settimingon
SQL>setautoton
SQL>selectcount(*)frombigtaba,smalltabbwherea.object_name=b.table_name;
可以看出这个语句执行性能很差:
16013consistentgets。
第一步:
创建优化任务并执行
通过调用函数CREATE_TUNING_TASK来创建优化任务,调用存储过程EXECUTE_TUNING_TASK执行该任务:
SQL>DECLARE
2 my_task_nameVARCHAR2(30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext:
='selectcount(*)frombigtaba,smalltabbwherea.object_name=b.table_name';
6 my_task_name:
=DBMS_SQLTUNE.CREATE_TUNING_TASK(
7 sql_text =>my_sqltext,
8 scope =>'COMPREHENSIVE',
9 time_limit =>60,
10 task_name =>'TERRY_TEST',
11 description=>'Tasktotuneaqueryonaspecifiedtable');
12
13 DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'TERRY_TEST');
14 END;
16 /
PL/SQLproceduresuccessfullycompleted.
在函数CREATE_TUNING_TASK,sql_text是需要优化的语句,user_name是该语句通过哪个用户执行,scope是优化范围(limited或comprehensive),time_limit优化过程的时间限制,task_name优化任务名称,description优化任务描述。
可以通过视图USER_ADVISOR_LOG和USER_ADVISOR_LOG来查看创建过的优化任务。
说明:
每次要优化SQL时,就执行这一串sql,将所要优化的SQL替换红色字部分。
如果sql中有’,那么需要用转义字符’’,如’转义后为’’。
蓝色部分就是优化任务的名字,自己定义一个容易辨认的即可。
SQL>selecttask_name,statusfromUSER_ADVISOR_LOGwheretask_name='TERRY_TEST';
第二步:
查看优化结果
通过函数可以查看优化结果。
SQL>SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('TERRY_TEST')fromDUAL;
CLOB的内容如下:
GENERALINFORMATIONSECTION
-------------------------------------------------------------------------------
TuningTaskName:
TERRY_TEST
TuningTaskOwner:
RESDEV
Scope:
COMPREHENSIVE
TimeLimit(seconds):
60
CompletionStatus:
COMPLETED
Startedat:
10/24/201113:
50:
16
Completedat:
10/24/201113:
50:
34
NumberofStatisticFindings:
2
NumberofIndexFindings:
1
-------------------------------------------------------------------------------
SchemaName:
RESDEV
SQLID:
6p64dnnsqf9pm
SQLText:
selectcount(*)frombigtaba,smalltabbwhere
a.object_name=b.table_name
-------------------------------------------------------------------------------
FINDINGSSECTION(3findings)
-------------------------------------------------------------------------------
1-StatisticsFinding
---------------------
Table"RESDEV"."SMALLTAB"wasnotanalyzed.
Recommendation
--------------
-Considercollectingoptimizerstatisticsforthistable.
executedbms_stats.gather_table_stats(ownname=>'RESDEV',tabname=>
'SMALLTAB',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FORALLCOLUMNSSIZEAUTO');
Rationale
---------
Theoptimizerrequiresup-to-datestatisticsforthetableinorderto
selectagoodexecutionplan.
2-StatisticsFinding
---------------------
Table"RESDEV"."BIGTAB"wasnotanalyzed.
Recommendation
--------------
-Considercollectingoptimizerstatisticsforthistable.
executedbms_stats.gather_table_stats(ownname=>'RESDEV',tabname=>
'BIGTAB',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'FORALLCOLUMNSSIZEAUTO');
Rationale
---------
Theoptimizerrequiresup-to-datestatisticsforthetableinorderto
selectagoodexecutionplan.
3-IndexFinding(seeexplainplanssectionbelow)
--------------------------------------------------
Theexecutionplanofthisstatementcanbeimprovedbycreatingoneormore
indices.
Recommendation(estimatedbenefit:
98.75%)
------------------------------------------
-ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesign
orcreatingtherecommendedindex.
createindexRESDEV.IDX$$_07BE0001onRESDEV.SMALLTAB("TABLE_NAME");
-ConsiderrunningtheAccessAdvisortoimprovethephysicalschemadesign
orcreatingtherecommendedindex.
createindexRESDEV.IDX$$_07BE0002onRESDEV.BIGTAB("OBJECT_NAME");
Rationale
---------
Creatingtherecommendedindicessignificantlyimprovestheexecutionplan
ofthisstatement.However,itmightbepreferabletorun"AccessAdvisor"
usingarepresentativeSQLworkloadasopposedtoasinglestatement.This
willallowtogetcomprehensiveindexrecommendationswhichtakesinto
accountindexmaintenanceoverheadandadditionalspaceconsumption.
-------------------------------------------------------------------------------
EXPLAINPLANSSECTION
-------------------------------------------------------------------------------
1-Original
-----------
Planhashvalue:
358182001
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|83|19424
(2)|00:
03:
54|
|1|SORTAGGREGATE||1|83|||
|*2|HASHJOIN||4950K|391M|19424
(2)|00:
03:
54|
|3|TABLEACCESSFULL|SMALLTAB|3119|53023|22(0)|00:
00:
01|
|4|TABLEACCESSFULL|BIGTAB|8413K|529M|19338
(1)|00:
03:
53|
--------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."OBJECT_NAME"="B"."TABLE_NAME")
2-UsingNewIndices
--------------------
Planhashvalue:
60674790
-----------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
-----------------------------------------------------------------------------------------
|0|SELECTSTATEMENT||1|83|242(27)|00:
00:
03|
|1|SORTAGGREGATE||1|83|||
|*2|HASHJOIN||4950K|391M|242(27)|00:
00:
03|
|3|INDEXFASTFULLSCAN|IDX$$_07BE0001|3119|53023|8(0)|00:
00:
01|
|4|INDEXFASTFULLSCAN|IDX$$_07BE0002|8413K|529M|171
(1)|00:
00:
03|
-----------------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("A"."OBJECT_NAME"="B"."TABLE_NAME")
-------------------------------------------------------------------------------
看一下这个优化建议报告:
第一部分是关于这次优化任务的基本信息:
如任务名称、执行时间、范围、涉及到的语句等等。
第二部分是关于这次优化任务的所找到的问题以及给出的优化建议。
前面先给出了问题描述:
可以通过建立更多的所引来提高性能;然后是建议的具体内容:
在表smalltab的字段table_name上创建索引,在表bigtab的字段object_name上创建索引;最后是相关注意事项:
此次优化虽然给出了创建索引的建议,但是最好通过SQL访问建议器(SQLAccessAdvisorSAA)结合整个数据库的工作量来深入分析,那样就能给出考虑了索引维护和空间消耗等因素的更加合理的建议。
最后,报告还给出了原有的查询计划,以及采用优化建议以后的查询计划的对比。
可以看出COST值大大下降。
第三步:
按照优化建议进行优化我们这里只是验证一下优化建议的效果。
按照建议,创建两个索引:
SQL>createindexsmalltab_idx1onsmalltab(table_name);
Indexcreated.
SQL>createindexbigtab_idx1onbigtab(object_name);
Indexcreated.
SQL>analyzetablesmalltabcomputestatistics;
Tableanalyzed.
SQL>analyzetablebigtabcomputestatistics;
Tableanalyzed.
SQL>selectcount(*)frombigtaba,smalltabbwherea.object_name=b.table_name;
可以看出,COST比优化前大大下降了,优化建议确实提高了性能。
Oracle10g让优化变得如此简单。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL 工具