oracle效能最佳化

文章目錄

• IO忙
• CPU負載過高
• 查詢計劃分析
• 最佳化方法：
• SQLPLUS設定：
• 查詢計劃設定
• 線上效能最佳化

系統效能IO忙IO熱點檔案和SQL分析判斷熱點資料檔案

SQL> @?/rdbms/admin/awrrpt.sql

或者

column name format a40;

set lines 500;

select name, sum(s.PHYRDS), sum(PHYWRTS),sum(READTIM),sum(writetim),

sum((phyrds+phywrts))/(sum(readtim+writetim)) avgio from v$filestat s, v$datafile f

where s.file#=f.file#

group by name

order by 6;

1. 移動資料檔案,調整熱點資料檔案到空閑磁碟上

SQL> startup mount

SQL> alter database rename ‘源檔案’ to ‘目的檔案’

$ mv  ‘源檔案’ ‘目的檔案’

SQL>alter database open

 

查看各進程的物理IO情況

SELECT se.sid,

       se.serial#,

       pr.SPID,

       se.username,

       se.status,

       se.program,

       se.MODULE,

       st.event,

       st.p1text,

       st.p1,

       st.p2,

       st.p3,

       st.STATE,

       st.SECONDS_IN_WAIT,

       si.physical_reads,

       si.block_changes

  FROM v$session se,

       v$session_wait st,

       v$sess_io si,

       v$process pr

 WHERE st.sid=se.sid AND st.sid=si.sid

   AND se.PADDR=pr.ADDR AND se.sid>6

   AND st.wait_time=0 AND st.event NOT LIKE '%SQL%'

 ORDER BY physical_reads

尋找IO高的sql;

select p.spid,s.sid,s.machine,s.program,q.disk_reads,q.sql_text

from v$process p,v$session s,v$sql q

where p.addr=s.paddr and s.sql_id=q.sql_id

order by 5;

 

8799                612 cqcu-zyite-col109

perl@cqcu-zyite-col109 (TNS V1-V3)                  1683932

 

SPID                SID MACHINE

------------ ---------- ----------------------------------------------------------------

PROGRAM                                          DISK_READS

------------------------------------------------ ----------

SQL_TEXT

----------------------------------------------------------------------------------------------------

DELETE resmonicurinfo rc     WHERE EXISTS (SELECT 1 FROM rescurinfo ri           WHERE rc.probeid IN

 ('PRS00008')         AND rc.probeid = ri.probeid         AND rc.resid = ri.resid         AND rc.dat

aitemid = ri.dataitemid)

 

8733                483 cqcu-zyite-col113

perl@cqcu-zyite-col113 (TNS V1-V3)                  1998034

DELETE resmonicurinfo rc     WHERE EXISTS (SELECT 1 FROM rescurinfo ri           WHERE rc.probeid IN

 

SPID                SID MACHINE

------------ ---------- ----------------------------------------------------------------

PROGRAM                                          DISK_READS

------------------------------------------------ ----------

SQL_TEXT

----------------------------------------------------------------------------------------------------

 ('PRS00012')         AND rc.probeid = ri.probeid         AND rc.resid = ri.resid         AND rc.dat

aitemid = ri.dataitemid)

b)      delete from devicemacvlan where deviceid =:p1

c) 31448               113 cqcu-zyite-col113                                                perl@cqcu-zyite-col113 (TNS V1-V3)    11009354

select d.DeviceID,        d.PPDescr,               d.UpConSpeed,        d.DownConSpeed,        d.UpCfgSpeed,        d.DownCfgSpeed,   d.UpMaxSpeed,        d.DownMaxSpeed,        d.UpSNR,        d.DownSNR,        d.UpAttenuation,        d.DownAttenuation,        p.pptype   from DSLAMPortParaCur d, ppinfo p  where d.deviceid = p.deviceid(+) and d.ppdescr = p.ppdescr(+)

CPU負載過高

1. 尋找cpu過高的進程號；

ps -e -o pid,pcpu,user,args|sort -n +1"

1. 查看進程號對應的sql語句

select p.spid,s.status,substr(s.machine,1,10) machine,substr(s.program,1,10) program,substr(q.sql_text,1,70) sql from v$session s,v$process p,v$sqlarea q where s.paddr=p.addr and s.sql_id=q.sql_id(+)

1.  

                       

案例：資料庫CPU過高

3540 , 84.25% , ACTIVE , fj-zyite-a ,perl@fj-zy , SELECT adeviceid, p1.portdescr, c.transcircode       FROM circuit c, p 

28380 , 83.24% , ACTIVE , nmscol1 ,perl@nmsco , MERGE INTO RESMONICURINFO A          USING (SELECT RESID, RESPARA, DAT 

15713 , 83.18% , ACTIVE , nmscol2.qz ,perl@nmsco , MERGE INTO RESMONICURINFO A          USING (SELECT RESID, RESPARA, DAT 

5880 , 49.81% , ACTIVE , fj-zyite-a ,perl@fj-zy , select p.spid,s.status,substr(s.machine,1,10) machine,substr(s.program 

112 , 34.73% , INACTIVE , fj-zyite-a , , Select failureId,max(g.endLevel) from failurelist,( Select FailureUpgr 

100 , 31.54% , ACTIVE , nmscol2.fz ,sqlldr@nms , INSERT INTO RESCURINFO   (PROBEID,RESID,RESPARA,DATAITEMID,VALUE,RECOR 

21706 , 19.13% , ACTIVE , nmscol1.ly , , select distinct aa.CircuitID, aa.FluxTime, aa.InAvgVec, aa.OutAvgVec,c 

12672 , 14.34% , ACTIVE , nmscol2.fz ,perl@nmsco , select to_char(checktime,'yyyymmddhh24miss') from rcheckreschecklog    

28114 , 13.75% , ACTIVE , nmscol1.pt ,perl@nmsco , select to_char(checktime,'yyyymmddhh24miss') from rcheckreschecklog  

SQL問題診斷和最佳化查詢計劃分析

：右上優先原則

 

SQL> set autotrace traceonly;

SQL> DELETE resmonicurinfo rc     WHERE EXISTS (SELECT 1 FROM rescurinfo ri           WHERE rc.probeid IN  ('PRS00012')         AND rc.probeid = ri.probeid         AND rc.resid = ri.resid         AND   rc.dataitemid = ri.dataitemid);

 

10024 rows deleted.

 

 

Execution Plan

----------------------------------------------------------

 

| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)|

 Pstart| Pstop |

 

--------------------------------------------------------------------------------

----------------

 

|   0 | DELETE STATEMENT         |                |     1 |    31 |  1504K  (1)|

       |       |

 

|   1 |  DELETE                  | RESMONICURINFO |       |       |            |

       |       |

 

|*  2 |   FILTER                 |                |       |       |            |

       |       |

 

|   3 |    TABLE ACCESS FULL     | RESMONICURINFO | 95404 |  2888K|   750K  (1)|

       |       |

 

|*  4 |    FILTER                |                |       |       |            |

       |       |

 

|   5 |     PARTITION LIST SINGLE|                |     1 |    34 |     8   (0)|

   KEY |   KEY |

 

|*  6 |      TABLE ACCESS FULL   | RESCURINFO     |     1 |    34 |     8   (0)|

   KEY |   KEY |

 

--------------------------------------------------------------------------------

----------------

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter( EXISTS (SELECT 0 FROM "RESCURINFO" "RI" WHERE :B1='PRS00012' AND

              "RI"."DATAITEMID"=:B2 AND "RI"."RESID"=:B3))

   4 - filter(:B1='PRS00012')

   6 - filter("RI"."DATAITEMID"=:B1 AND "RI"."RESID"=:B2)

 

Note

-----

   - 'PLAN_TABLE' is old version

 

 

Statistics

----------------------------------------------------------

          1  recursive calls

     101751  db block gets

    3984004  consistent gets

     670813  physical reads

    9593840  redo size

        667  bytes sent via SQL*Net to client

        766  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      10024  rows processed

 

查看索引

select u.index_name,index_type,column_name

from user_indexes i,user_ind_columns u

where i.index_name = u.index_name and i.table_name='RESCURINFO';

查看物件類型，

是否分區表

select object_type from user_objects where object_name='RESCURINFO'；

最佳化方法：建立索引

大表建立索引,資源忙？

建立分區索引

create index ind_rescurinfo_resid on rescurinfo (resid)  tablespace indexcfg online local nologging;

建立非分區索引

create index ind_devicemacvlan_deviceid on devicemacvlan (deviceid) tablespace indexlist online  nologging;

create index ind_RESMONICURINFO_RESPARADATA on resmonicurinfo (resid,respara,dataitemid) tablespace indexlist online  nologging;

清空failurelist

線上大表建立索引選擇性參數：

parallel 4

sort_area_size

muti_block_read_count

 

表分析

Analyze table tablename

 

 

附錄：SQLPLUS設定：

define editor=vi

set serveroutput on size 1000000

set trimspool on

set long 5000

set linesize 100

set pagesize 9999

column plan_plus_exp format a80;

 

查詢計劃設定

SQL> alter user system identified by oracle  ;

使用者已更改。

 

SQL> conn system/oracle

 

SQL> @?/rdbms/admin/utlxplan.sql

SQL> CREATE public synonym plan_table for plan_table;

SQL> GRANT ALL ON PLAN_TABLE TO PUBLIC;

 

SQL> conn / as sysdba

SQL> @?/sqlplus/admin/plustrce.sql

SQL> GRANT PLUSTRACE TO PUBLIC

 

SET AUTOTRACE OFF|ON|TRACEONLY

線上效能最佳化

SQL> alter system set timed_statistics true scope=both;

SQL> alter sytem set sql_trace=true;

SQL> alter system set max_dump_file_size='10M';