当前位置:文档之家› 数据库性能优化基础步骤

数据库性能优化基础步骤

1性能优化基本步骤1.1定位跟踪耗费资源较多的SQL语句步骤1.1.1 通过SQL查询(1): 查询出最耗费资源的SQL语句select t1.SID,t1.SERIAL#,tt.HASH_VALUE,tt.ADDRESS,tt.BUFFER_GETS, --读内存次数tt.DISK_READS, --磁盘物理读次数tt.EXECUTIONS, --语句的执行次数tt.BUFFER_GETS / tt.EXECUTIONS, --平均读内存次数tt.SQL_FULLTEXTfrom v$sqlareatt, v$session t1where (tt.BUFFER_GETS>100000or tt.DISK_READS>100000)and tt.HASH_VALUE = t1.SQL_HASH_VALUEand tt.ADDRESS = t1.SQL_ADDRESSand t1.STATUS = 'ACTIVE'orderby tt.BUFFER_GETS desc(2):根据客户端程序发出的SQL来定位需要跟踪的sessionselect s.sid sid,s.SERIAL# "serial#",ername,s.machine,s.program,s.server,s.LOGON_TIMEfrom v$session s1.1.2 通过Oracle提供的SQL TRACE进行SQL跟踪(1):跟踪前设定相应参数1.查询得到需要跟踪的session2.打开时间开关Show parameter timed_statisticsalter session set timed_statistics=true;execsys.dbms_system.set_bool_param_in_session(sid => 8,serial# => 3,parnam => 'timed_statistics',bval => true);3.设置跟踪文件存放位置Show parameter user_dump_destalter system set user_dump_dest='c:\temp';(2):启动跟踪功能并让系统运行一段时间alter session set sql_trace=true;execsys.dbms_system.set_sql_trace_in_session(8, 3, true);(3):关闭跟踪功能alter session set sql_trace=false;execsys.dbms_system.set_sql_trace_in_session(8, 3, false);(4):格式化跟踪数据文件,并分析跟踪结果文件tkprof dsdb2_ora_18468.trc dsdb2_trace.txt EXPLAIN=SCOTT/TIGERtkprof各参数含义:' traced_file ' 指定输入文件,即oracle产生的trace文件'formatted_file'指定输出文件,即我们想得到的易于理解的格式化文件'EXPLAIN' 利用哪个用户对trace文件中的sql进行分析得到该sql语句的执行计划1.2查看分析执行计划1.2.1查看执行计划(1):Sqlplus中可按F5查看执行计划(2):使用执行计划表进行查看使用语句将SQL语句的执行计划装入plan_table表,然后进行分析查看explainplansetstatement_id = 'dd'into plan_table forselect t.type_name,t.source_value,t.standard_value fromODS_STD_COMP t,ODS_STD_COMP_BAK t1where t.system_id = t1.system_idand t.type = t1.typeand t.source_value = t1.source_value(3):示例演示1.让ORALCE自动选择最优的执行计划,不人为干预explainplansetstatement_id = 'dd'into plan_table forselect t.type_name,t.source_value,t.standard_value fromODS_STD_COMP t,ODS_STD_COMP_BAK t1where t.system_id = t1.system_idand t.type = t1.typeand t.source_value = t1.source_value选择对表ODS_STD_COMP进行索引范围扫描2.不走索引,使用hints干预select/*+ NO_INDEX(t,IND_ODS_STD_COMP01)*/t.type_name, t.source_value, t.standard_valuefrom ODS_STD_COMP t, ODS_STD_COMP_BAK t1where t.system_id = t1.system_idand t.type = t1.typeand t.source_value = t1.source_value或者指定表ODS_STD_COMP使用全表扫描(full(t)),得到相同执行计划3.组合索引,查询关联条件不包含先导列,则不使用索引select t.type_name, t.source_value, t.standard_valuefrom ODS_STD_COMP t, ODS_STD_COMP_BAK t1where t.type = t1.typeand t.source_value = t1.source_value使用hints干预,指定对表ODS_STD_COMP进行索引扫描select/*+ index(t,IND_ODS_STD_COMP01)*/t.type_name, t.source_value, t.standard_valuefrom ODS_STD_COMP t, ODS_STD_COMP_BAK t1where t.type = t1.typeand t.source_value = t1.source_value4.走索引,使用嵌套连接,并指定特定的连接顺序select/*+ use_NL(t,t1) INDEX(t,IND_ODS_STD_COMP01)*/t.type_name, t.source_value, t.standard_valuefrom ODS_STD_COMP t, ODS_STD_COMP_BAK t1where t.system_id = t1.system_idand t.type = t1.typeand t.source_value = t1.source_value(4): 访问数据的存取方法1. 全表扫描(Full Table Scans, FTS)取出的数据量较多,使用多块读效率高2. 索引扫描唯一索引扫描索引范围扫描索引全扫描索引快速全扫描/*+INDEX_FFS(t,IND_ODS_STD_COMP01)*/3. By ROWID根据ROWID直接定位到目标数据上(ROWID指定了数据在数据文件,数据快以及行的位置)重点注意如下几点:1.组合索引中,若查询条件不包含“引导列”,不走索引2.索引快速全表扫描与index full scan很类似,但是一个显著的区别就是它不对查询出的数据进行排序3.走索引查询出来的结果都是经过排序了的(除了FFS)4.在索引中,除了存储索引值外,还存储了此值对应的ROWID 索引扫描的过程:(1): 扫描索引得到对应的rowid值(一般存于内存中)(2): 通过INDEX_ROWID读出具体的值,在这边每步都是单独的I/O所以当对大表进行索引扫描时(大表数据不可能全在内存中,取出数据大于总量的5%)会产生大量的I/O,效率低下1.3 SQL语句优化1.3.1 使用hints来干预执行计划(1):改变数据存取方法对全表扫描,ROWID方式,索引扫描数据存取方式的最优选择(2):改变表的连接方式合并连接(USE_MERGE):1.非等值连接嵌套连接(USE_NL):1.driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时2.可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间哈希连接(USE_HASH):1.一般来说,其效率应该好于其它2种连接2.在2个较大的row source之间连接时会取得相对较好的效率3.只能用于等值连接中1.3.2 常用hints解析-优化方式和目标:如RULE、CHOOSE、FIRST_ROWS、ALL_ROWS-访问路径:如INDEX、FULL、CLUSTER、INDEX_FFS、NO_INDEX、ROWID(TABLE)等-查询转换:如MERGE、USE_CONCAT、NO_EXPAND等-连接顺序:如LEADING、ORDERED、STAR等-连接操作:如USE_NL、USE_HASH、USE_MERGE等-并行执行:如PARALLE、NOPARALLEL、PARALLEL_INDEX等-其他类型:如APPEND、UNNEST、CACHE、NOLOGGING等1.3.3收集统计信息(1):表统计信息的收集ANALYZE TABLE FDM.F_NIN_INWARD_REMITTANCEcompute statistics;dbms_stats.gather_table_stats(ownname => 'FDM',tabname => 'F_NIN_INWARD_REMITTANCE',estimate_percent =>1,degree =>8,cascade => true);参数含义:estimate_percent:采样百分比degree:并行度cascade:是否收集索引信息1.4 作业性能优化分析要点1.4.1 分析要点(1):查看创建的表分区是否合适(2):新增索引update的数据量占目标表数据量小比重,考虑给目标表以关联条件字段创建索引(3):分析是否有冗余逻辑,对冗余逻辑进行合并处理以减少insert,update步骤。

相关主题