最佳化學習筆記

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