如何在不改sql情况下优化数据库

如何在无法修改应用时进行数据库优化联系方式姓名我的数据库怎么啦不动SQL,如何优化SQL审核：防范于未然我的数据库怎么啦真实案例某汽车经销商，使用X蝶财务系统由于数据量和用户数增加，月结无法进行软件提供商无法提供SQL优化CPU100%,系统数度崩溃我的数据库怎么啦系统刚上线，一切很美好一两年后，负荷逐渐增加数据量的积累用户数增多功能点使用增多频繁出现性能问题我的数据库怎么啦优化SQL是最好的方法，但是使用封装好的商业软件没法修改SQL外包开发验收周期已经结束开发商已经离场自行开发开发人员不愿修改代码没人承担变更风险担子全都抛给运维组或者DBA组不动SQL,如何优化SQL没法修改如何优化？硬件优化硬件层面CPURAC内存增加cache存储LSI闪存zData方案闪存优化LSI闪存，数十倍加速I/O性能节点1节点2Oracle

11g

RacSAN存储节点1Oracle

11g优化前优化后台账系统查询OGG复制进行入库生产系统生产系统台账系统查询

在原架构中，其中一个节点用于从生产系统上进行抽数，入库到SAN存储中，另外一个节点用于做台账系统的查询，但存储存在IO瓶颈。

在新架构中，使用LSI的高速存储卡作为本地存储。用于存放数据文件等相关的数据库文件，以及OGG的队列文件。OGG复制进行入库闪存优化LSI闪存，数十倍加速I/O性能相同时间段1个小时内的AWR数据指标优化前优化后优化提升率日志量7,627,29517,957,028235.40%逻辑读71,058236,944333.50%修改块30,24297,636322.80%SQL执行4,2345,235123.60%单块读指标优化前优化后优化提升率等待次数5,948,19815,212,847255.76%等待时间340,63831,9181000.00%平均等待5722850.00%响应时间86.8727.29318.50%IO优化Zdata存储优化单纯LSI闪存只适用于单机环境，单点故障扩展不易zData超融合数据库存储解决方案解决单点故障解决存储扩展性解决计算节点扩展性ApplicationServerApplicationServer10GbEnetworkMDMPSDSSDCSDCSDSMDMSSDSAvailabilityCompleteProtectionfordataandnetwork2datacopy“mesh”mirroringScale-OutParallelarchitectureIncreasingstoragecapacityandperformanceAutomaticrebalanceandrebuild40/10GbESwitchesApplicationServersScaleIOServersECSServer-1LSINytroECSServer-2LSINytroECSServer-nLSINytroZdata存储优化Zdata存储优化Orion/FIO存储压测结果XX移动业务数据对比IO提升34倍，执行时间降低100倍项目8k纯读IOPS8k20%写IOPS8k读延迟1M读每秒吞吐量5节点zdata30W26W0.15ms7G等同于：1000+传统硬盘1000+传统硬盘30倍40个传统硬盘项目执行时间IO等待时间IO等待次数IO平均等待时间Zdata环境80s62,574ms429k0.145ms用户环境8800s5,407,795ms1085k4.98ms数据库优化数据库参数调整：内存参数，优化器参数...表结构调整：索引,并行度,分区SQL执行计划调整：SQLProfile,SPM其它：

Cache表统计信息查询重写数据归档不动SQL,如何优化数据库内存参数SGABufferCacheSharedpoolPGA谨慎使用自动SGA调整在parsecall很高的环境下，sharedpool抖动可能发生灾难性性能Latch:librarycachecursor:pinS数据库优化优化器参数Optimizer_modeCBO/RBO统计信息为CBO服务Optimizer_features_enabled新版本优化特性总开关，升级中常用设置该参数必须经过严格测试其它小的优化参数：

运行10046traceAltersessionsetevents‘10046tracenamecontextforever,level1’;

查看trace，找到“PARAMETERSUSEDBYTHEOPTIMIZER”数据库优化索引在X蝶财务软件中为用户建立超过100个新索引适当的索引对提高系统性能有非常大帮助适当建立函数索引数据库优化索引--案例分享很简单的SQLselect*fromt1,t2wheret1.fidlike'AB%'andt2.fid=t1.item_id;T1的FID和T2的FID都是主键。TABLE_NAMEINDEX_NAMECOLUMN_NAME------------------------------------------------------------T1PK_T1FIDT2PK_T2FID数据库优化索引--案例分享执行计划走了一个全表扫描|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|88|271(1)|00:00:04||*1|HASHJOIN||1|88|271(1)|00:00:04||2|TABLEACCESSBYINDEXROWID|T1|1|52|2(0)|00:00:01||*3|INDEXRANGESCAN|PK_T1|1||2(0)|00:00:01||4|TABLEACCESSFULL|T2|89551|3148K|268(1)|00:00:04|---------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T1"."ITEM_ID"=SYS_OP_C2C("T2"."FID"))3-access("T1"."FID"LIKE'AB%')filter("T1"."FID"LIKE'AB%')Note------dynamicsamplingusedforthisstatement(level=2)数据库优化索引--案例分享执行计划走了一个全表扫描NameNull?Type-----------------------------------------------------------------------------------------------------FIDNOTNULLVARCHAR2(32)ITEM_IDNVARCHAR2(32)SQL>desct2NameNull?Type------------------------------------------------------------------------------------------------------FIDNOTNULLVARCHAR2(32)OBJECT_NAMEVARCHAR2(32)...access("T1"."ITEM_ID"=SYS_OP_C2C("T2"."FID"))数据库优化索引--案例分享改表结构最理想，但是如今只能创建函数索引---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|122|4(0)|00:00:01||1|NESTEDLOOPS||1|122|4(0)|00:00:01||2|TABLEACCESSBYINDEXROWID|T1|1|52|2(0)|00:00:01||*3|INDEXRANGESCAN|PK_T1|1||2(0)|00:00:01||4|TABLEACCESSBYINDEXROWID|T2|1|70|2(0)|00:00:01||*5|INDEXUNIQUESCAN|PK2_T2|1||1(0)|00:00:01|---------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------3-access("T1"."FID"LIKE'AB%')filter("T1"."FID"LIKE'AB%')5-access("T1"."ITEM_ID"=SYS_OP_C2C("FID"))数据库优化SQLProfileSQLprofile在Oracle10g引入通过为特定的SQL文本指定优化器的一些信息，从而引导优化器生成更为合理的SQL执行计划。达到不修改SQL文本就可以改变并指定执行计划的目的。通常和SQLTuningAdvisor一起用也可以手工设置数据库优化SQLProfile--SQLTuningAdvisor数据库优化SQLProfile--SQLTuningAdvisor数据库优化SQLProfile--SQLTuningAdvisor数据库优化SQLProfile--SQLTuningAdvisor数据库优化SQLProfile--SQLTuningAdvisor数据库优化SQLProfile--SQLTuningAdvisor接受自动生成的SQLProfile.

execute

dbms_sqltune.accept_sql_profile(task_name

=>

:tuning_task,replace

=>

TRUE,force_match=>true);

过程已成功完成。

数据库优化SQLProfile--SQLTuningAdvisor下次执行SQL,就会使用正确额执行计划数据库优化SQLProfile–手动设置以刚才SQL为例，我们通过使用hint生成需要的执行计划。select/*+ordereduse_nl(t1,t2)index(t2T2_IDX)*/t1.*,t2.ownerfromt1,t2wheret1.object_namelike'%T2%'andt1.object_id=t2.object_id;

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

Plan

hash

value:

3787413387

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

|

Id

|

Operation

|

Name

|

Rows

|

Bytes

|

Cost

(%CPU)|

Time

|

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

|

0

|

SELECT

STATEMENT

|

|

29

|

1160

|

117

(3)|

00:00:02

|

|

1

|

TABLE

ACCESS

BY

INDEX

ROWID|

T2

|

1

|

11

|

2

(0)|

00:00:01

|

|

2

|

NESTED

LOOPS

|

|

29

|

1160

|

117

(3)|

00:00:02

|

|*

3

|

TABLE

ACCESS

FULL

|

T1

|

29

|

841

|

59

(6)|

00:00:01

|

|*

4

|

INDEX

RANGE

SCAN

|

T2_IDX

|

1

|

|

1

(0)|

00:00:01

|

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

Predicate

Information

(identified

by

operation

id):

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

3

-

filter("T1"."OBJECT_NAME"

LIKE

'%T2%')

4

-

access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

数据库优化SQLProfile–手动设置找到对应的SQL_id和plan_hash_value。

数据库优化SQLProfile–手动设置

从v$sql_plan中的other_xml中提取出profile用到的属性，并使用dbms_sqltune.import_sql_profile导入declarev_sqlprofsqlprof_attr;beginselectsubstr(extractvalue(value(d),'/hint'),0,500)asoutline_hintsbulkcollectintov_sqlproffromxmltable('/*/outline_data/hint'passing(selectxmltype(other_xml)asxmlvalfromv$sql_planwheresql_id='&sqlid'andplan_hash_value=&new_planandother_xmlisnotnull))d;sys.dbms_sqltune.import_sql_profile(sql_text=>v_sqltext,profile=>v_sqlprof,name=>'PROFIL_ENMO_'||new_sqlid,replace=>true,force_match=>true);end;/数据库优化SQL计划管理SQL计划管理在Oracle11g引入通过为特定的SQL指定已知SQL执行计划，强制优化器选择已经指定的SQL执行计划。从而达到不修改SQL文本即可修改执行计划的目的。可以指定多个可用执行计划供优化器选择。可以和SQLTuningAdvisor一起用可以自动收集运行库中SQL作为已知执行计划。也可以手工设置。SQL计划管理的选择是执行计划是否在SQL基准线内直接执行SQL是否直接执行SQL执行SQL基准线里的执行计划将执行计划加入待验证列表否产生执行计划SQL提交是否存在SQL计划管理库数据库优化SQL计划管理–从autotuning中导入和SQLProfile类似，先通过autotuning生成一个满意的plan，再导进去DECLAREmy_planspls_integer;BEGINmy_plans:=DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name=>'tset1‘basic_filter=>'sql_id=“&sqlid”andplan_hash_value=&plan');END;/数据库优化SQL计划管理–手动设置和SQLProfile类似，先通过hint生成一个满意的plan,再导进去declareSPM_sql_textclob;cntnumberdefault0;v_sql_idvarchar2(100):='&oldsqlid';beginselectsql_fulltextintoSPM_sql_text fromv$sqltextwheresql_id=v_sql_id;cnt:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(v_sql_id,&new_plan_hash,sql_text=>SPM_sql_text);end;/数据库优化数据库优化优化影响面考虑优化方式影响面谨慎程度数据库内存参数整个主机★★数据库优化参数整个数据库★★★★★索引表相关SQL★★★并行度表相关SQL★★★★Keep表表相关SQL以及数据库内存使用★★SQLProfile指定的SQL以及含有指定SQL片段的SQL★★SQLPlanBaseline指定的SQL★★统计信息整个数据库★★★★查询重写特定的一些SQL★★★★★SQL审核：防范于未然对于外包或者自主开发的软件，应当在开发过程中严加把关，防范于未然在能修改SQL的时候进行优化，事半功倍。高效的应用程序需要：不断进步的开发团队、技术专家的SQL质量把关以及线上系统SQL代码的持续改进这三个维度的螺旋推进。产品简介：专业SQL审核服务规范及培训线上SQL优化SQL审核及评分SQL开发规范及培训为开发项目提供开发规范基本SQL写法表与索引规范高效SQL写法SQL性能陷阱为开发人员提供相应培训Oracle开发基础SQL基础高效SQL上线前SQL审核上线前SQL审核由于开发人员的技能差异，很多SQL隐患可能在开发环节被埋入系统；SQL审核可以通过专业的工具和人员服务，帮助用户守住上线关卡；提前发现及预防因SQL编写不合理而带来的性能隐患300%SQL审计通过优化SQL语句直接提升系统性能和用户感受

SQL执行效率大幅提升应用系统效率大幅提升系统资源充分利用全面审计SQL的结构、编写标准、执行计划、性能等内容，降低低效SQL造成的压力和提升系统开发质量SQL中子查询使用的是否合理表连接方式及顺序是否合理执行计划是否有效SQL编写是否符合统一标准索引设计不合理，缺乏战略索引设计(以表单位)。

以表为单位，搜集SQL的AccessPath。通过对其进行综合性的分析，设计出最佳索引。实现以最少的索引个数满足最多读取要求的目标。历史模块SQL新上线模块SQL元数据抽取SQL审计工