【筆記】statspack 學習（二） sql調整

sql調整很重要的方面在於：
1，索引
2，join

優化器模式：rule,first row,all rows,choose
除rule以外，注意其他三個需要先對錶和索引analyze
rule注意索引的選擇，其他則要注意全表掃描問題。

關於索引的選擇：
1，分析讀取資料量與總資料量的比例（有序40%，無序7%），注意使用全表掃描和索引的選擇
2，注意時間欄位上的索引
3，注意字典表中的索引，注意大表內型別欄位上的索引，特別是在大量資料行讀取的時候。
4，索引型別的選擇注意

基於規則的優化器注意：
1，資料表順序，注意驅動表的選擇，即from後最後一個表（一般認為是返回資料行最少的表）
2，where條件的順序（最後一個是可以去除最多資料行的條件，以此類推）
3，注意檢查是否使用不合理的索引

基於成本的優化器注意：
1，analyze table and index
可以按owner來analyze,調整時注意利用analyze結果：
select * from dba_tables where wner = 'TEST' (主要：num_rows, blocks, empty_blocks AS empty, avg_space,

chain_cnt, avg_row_lend等)
下面生成的sql，也可以加入定時作業

SQL> set pages 999
SQL> set heading off
SQL> set echo off
SQL> set feedback off
SQL> spool ?\exp\tuning\analyze_all.sql
SQL> select 'analyze table '||owner||'.'||table_name||' compute statistics;' from dba_tables where o
wner = 'TEST';
SQL> select 'analyze index '||owner||'.'||index_name ||'compute statistics;' from dba_indexes where
owner = 'TEST';
SQL> spool off;
SQL> set echo on
SQL> set feedback on
SQL> @ ?\exp\tuning\analyze_all.sql

2，使用hint
/* + rule*/
/* + full, table =xx parallel=nn */
/* + table=xx index = xx */
/* + first_rows */
/* use_hash */
等

sql寫法調整：
開始前識別低效的sql：
1，使用系統表：
SELECT EXECUTIONS,
       DISK_READS,
       BUFFER_GETS,
       ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,
       ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
       SQL_TEXT
  FROM V$SQLAREA
 WHERE sql_text like '%&sql_text%'
   AND EXECUTIONS > 0
   AND BUFFER_GETS > 0
   AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
 ORDER BY 4 DESC;
2，使用statspack：

select * from perfstat.stats$snapshot;

drop table sqltuning_temp_table1;
create table sqltuning_temp_table1 as
select min(snap_id) min_snap
  from perfstat.stats$snapshot
 where snap_time > sysdate - &days_back;

drop table sqltuning_temp_table2;
create table sqltuning_temp_table2 as
select  executions,
       to_date(snap_time, 'dd mon hh24:mi:ss') as mydate,
       loads,
       parse_calls,
       disk_reads,
       buffer_gets,
       rows_processed,
       sorts,
       sql_text
  from perfstat.stats$sql_summary sql, perfstat.stats$snapshot sn
 where sql.snap_id > (select min_snap from sqltuning_temp_table1)
   and sql.snap_id = sn.snap_id
 order by &sortkey desc;

select * from sqltuning_temp_table2 where rownum < &num;

上面需要設定天數，sortkey，和rownum，根據需要設定。

具體：
1，避免使用子查詢，而使用Join
2，in 和 exists 寫法的選擇，not in 和 not exists的選擇
3，注意函式代替：order by，group by，distinct都是比較耗時的
   union，minus，intersect一般可以代替
4，注意連線的欄位型別，隱式的型別轉換可能不使用索引
  注意!= || not 等和其他函式 將不使用索引，可以考慮function-based index
5，重用，引數化，使用 :val 。緩衝區清理：alter system flush shared pool
   確定緩衝區中非重用sql：
 select a.sql_text
   from v$sql a,
        (select substr(sql_text, 1, &size) sqltext, count(*)
           from v$sql
          group by substr(sql_text, 1, &size)
         having count(*) > 10) b
  where substr(a.SQL_TEXT, 1, &size) = b.sqltext;
6,注意使用create as 來建立臨時表來代替一些大的子查詢，適當使用 with xx as () select
7，增加索引（待續）

 

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