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

sql最佳化一般步驟

相關文章