个人总结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';