sql优化步骤

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