sql最佳化一般步驟

Michael_DD發表於2015-01-15
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';

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29500582/viewspace-1402838/,如需轉載,請註明出處,否則將追究法律責任。

相關文章