数据库性能优化基础步骤

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_FULLTEXT

from v$sqlareatt, v$session t1

where (tt.BUFFER_GETS>100000

or tt.DISK_READS>100000)

and tt.HASH_VALUE = t1.SQL_HASH_VALUE

and tt.ADDRESS = t1.SQL_ADDRESS

and t1.STATUS = 'ACTIVE'

orderby tt.BUFFER_GETS desc

(2)：根据客户端程序发出的SQL来定位需要跟踪的session

select s.sid sid,

s.SERIAL# "serial#",

ername,

s.machine,

s.program,

s.server,

s.LOGON_TIME

from v$session s

1.1.2 通过Oracle提供的SQL TRACE进行SQL跟踪

(1)：跟踪前设定相应参数

1．查询得到需要跟踪的session

2．打开时间开关

Show parameter timed_statistics

alter 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_dest

alter 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/TIGER

tkprof各参数含义：

' 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 for

select t.type_name,t.source_value,t.standard_value from

ODS_STD_COMP t,ODS_STD_COMP_BAK t1

where t.system_id = t1.system_id

and t.type = t1.type

and t.source_value = t1.source_value

(3)：示例演示

1．让ORALCE自动选择最优的执行计划，不人为干预

explainplansetstatement_id = 'dd'into plan_table for

select t.type_name,t.source_value,t.standard_value from

ODS_STD_COMP t,ODS_STD_COMP_BAK t1

where t.system_id = t1.system_id

and t.type = t1.type

and 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_value

from ODS_STD_COMP t, ODS_STD_COMP_BAK t1

where t.system_id = t1.system_id

and t.type = t1.type

and t.source_value = t1.source_value

或者指定表ODS_STD_COMP使用全表扫描(full(t)),得到相同执行计划3．组合索引，查询关联条件不包含先导列，则不使用索引

select t.type_name, t.source_value, t.standard_value

from ODS_STD_COMP t, ODS_STD_COMP_BAK t1

where t.type = t1.type

and 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_value

from ODS_STD_COMP t, ODS_STD_COMP_BAK t1

where t.type = t1.type

and t.source_value = t1.source_value