oracle性能优化
系统性能
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;
- 移动数据文件,调整热点数据文件到空闲磁盘上
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负载过高
- 查找cpu过高的进程号；
ps -e -o pid,pcpu,user,args|sort -n +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(+)
案例：数据库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';
