sql优化步骤
1、通过top sql找出有问题的sql语句
select * from (select * from v$sqlstats order by disk_reads desc) where rownum<=10;
还可以对elapsed_time,avg_hard_parse_time等排序
2、指定某个jdbc的连接找出有问题的sql语句
我们jdbc连接到，一般情况下是web界面连接查询。
select sid, serial#, username, sql_id, prev_sql_id, logon_time
from v$session
where program = ‘jdbc thin client’
and username = upper(‘jscnbi’)
order by logon_time desc;
一般情况下如果是连接池配置的，这个连接时间应该是相同的，如果这样，我们可以根据sql_id
和v$sql结合判断当前有问题的sql，这个时候最好加上一个sql_id is not null。
select a.sql_text, a.sql_fulltext, b.sid, b.event
from v$sql a, v$session b
where a.sql_id = b.sql_id
and b.program = ‘jdbc thin client’
and b.sql_id is not null
order by b.logon_time desc;
找出具体的sql以后，我们就可以看sql的执行计划了。
3、查看执行计划的时候，我们要特别全表扫描部分的表、扫描数据量特别大的表，看是否统计信息存储问题。
1）看是否创建索引
select * from all_ind_columns where table_name=upper(‘表名称’);
2）查看表中的统计信息
select owner,table_name,num_rows,to_char(last_analyzed,’yyyy-mm-dd hh24:mi:ss’) from all_tables where table_name=upper(‘product’);
这里特别要注意num_rows，last_analyzed这两个字段。如果last_analyzed时间太旧了也会造成性能问题。
如果太旧我要收集统计信息了
begin
sys.dbms_stats.gather_table_stats (
ownname => ‘jscnbi’
,tabname => ‘product’
,estimate_percent => 0
,method_opt => ‘for all indexed columns size 1 ‘
,degree => 4
,cascade => true
,no_invalidate => false);
end;
/
或者这样收集
analyze table jscnbi.product estimate statistics sample 33 percent;
4、查看sql的执行计划
4.1 查看sga中关于sql执行计划的信息
select * from table(dbms_xplan.display_cursor(sql_id));
eg：
select * from table(dbms_xplan.display_cursor(‘cbj0d7thtn00q’));
4.2 做10046事件和sql_trace
1）标识本session
sql> alter session set tracefile_identifier=’jscntest’;
2）sql tracle
sql> alter session set sql_trace=true;
sql> 执行sql
sql> alter session set sql_trace=false;
进入/udump目录，格式化trc文件
tkprof jscn_ora_20448_jscntest.trc jscntest.txt sys=no
3）10046
level 1： 等同于sql_trace 的功能
level 4： 在level 1的基础上增加收集绑定变量的信息
level 8： 在level 1 的基础上增加等待事件的信息
level 12：等同于level 4+level 8, 即同时收集绑定变量信息和等待事件信息。
sql> alter session set events ‘10046 trace name context forever,level 4’;
sql> 执行sql
sql> alter session set events ‘10046 trace name context off’;