ORA性能诊断调优

# ORA性能诊断调优

1. 查看当前会话情况

---v$session视图显示实时的会话记录 select sql\_id,event,count(*) from v$session where wait\_class <>'Idle' group by sqL\_ID,EVENT; ---v$active\_session\_history视图显示历史会话记录，每1s采样一次，存放内存 select sample\_id,sample\_time,count(*) from v$active\_session\_history group by sample\_id,sample\_time having count(*)>10 order by sample\_id desc ---dba\_hist\_active\_sess\_history视图显示历史会话记录，每10s采样一次，存放物理表

1. 共享池命中率低

考虑调整 CURSOR\_SHARING 参数， 设置成SIMILIAR(check docuBment)

1. DB time

SELECT * fROM (select t1.snap\_id, T1.INSTANCE\_NUMBER, to\_char(t2.end\_interval\_time,'YYYY-MM-DD HH24:MI:SS') AS T\_SAMPLE, t1.stat\_name, t1.value - lag(t1.value, 1) over(oRder by t1.snap\_id asc) as delta from dba\_hist\_sys\_time\_model t1, dba\_hist\_snapshot t2 where t1.instance\_number= t2.instance\_number and t1.instance\_number=1 and t1.snap\_id = t2.snap\_id and t1.stat\_name = 'DB time' and t2.end\_interval\_time > sysdate - 3) WHERE DELTA>0 order by snap\_id asc

1. IOPS ,MBPS

dba\_hist\_sysstat v$sysstatt select * from (select t1.snap\_id, to\_char(t2.begin\_interval\_time, 'yyyy-mm-dd hh24:mi:ss') as t\_begin, to\_char(t2.end\_interval\_time, 'yyyy-mm-dd hh24:mi:ss') as t\_end, t1.stat\_name, t1.value - lag(t1.value, 1) over(order by t1.snap\_id asc) as delta From dba\_hist\_sysstat t1, dba\_hist\_snapshot t2 where t1.snap\_id = t2.snap\_id and t1.instance\_number=t2.instance\_number and t1.instance\_number=1 --- RAC库如果看节点2 ， 修改成2 and t1.stat\_name = 'physical reads' --'session logical reads’ and t2.begin\_interval\_time > sysdate - 10) where delta > 0 order by snap\_id asc

1. resource limit

show parameter session; show parameter processes;

1. force logging enable(建议)
2. 优化sql
	1. 根据sql\_id分析语句资源使用情况（当前VS历史）

---查看sql语句当前执行时资源使用情况 select sql\_id, child\_number, PLAN\_HASH\_VALUE, round(elapsed\_time / executions) as elapsetimeavg, round(rows\_processed / executions) as row\_avg, round(buffer\_gets / executions) as bufferavg, round(disk\_reads / executions) as diskavg, round(t.CPU\_TIME/executions) as cpuavg, executions, sql\_profile from v$sql t where t.sql\_id = 'f401pc3jtp8sg' and executions > 0; ---查看sql语句历史执行时资源使用情况 select to\_char(end\_interval\_time, 'YYYY-MM-DD HH24:MI:SS') SAMPLE\_TIME, t1.snap\_id, t1.sql\_id, t1.executions\_delta, t1.plan\_hash\_value, round(t1.elapsed\_time\_delta / t1.executions\_delta) elapsed\_time\_average, round(t1.buffer\_gets\_delta / t1.executions\_delta) buffer\_average, round(t1.rows\_processed\_delta / t1.executions\_delta) as row\_average, round(t1.disk\_reads\_delta / t1.executions\_delta) disk\_average, round(t1.cpu\_time\_delta / t1.executions\_delta) cpu\_time\_average, round(t1.clwait\_delta / t1.executions\_delta) as cl\_time\_avg, round(t1.iowait\_delta / t1.executions\_delta) as io\_time\_avg, round(t1.apwait\_delta / t1.executions\_delta) as lock\_time\_avg, round(t1.direct\_writes\_delta / t1.executions\_delta) as direct\_write\_average, t1.sql\_profile from dba\_hist\_sqlstat t1, dba\_hist\_snapshot t2 where t1.instance\_number = t2.instance\_number and t1.snap\_id = t2.snap\_id and t1.sql\_id = 'bb7qdasc1snkn' and t1.executions\_delta > 0 order by sample\_Time asc;

* 
	1. 根据sql\_id查看语句执行计划（当前VS历史）

---查看语句当前的执行计划 select * from table(dbms\_xplan.display\_cursor('f401pc3jtp8sg')); ---查看语句历史执行计划 select * from table(dbms\_xplan.display\_awr('f401pc3jtp8sg'));

* 
	1. 使用调优顾问为sql生成优化建议

set serveroutput on size 10000; declare a varchar2(100); begin a:=DBMS\_SQLTUNE.CREATE\_TUNING\_TASK(sql\_id=>'f401pc3jtp8sg'); dbms\_sqltune.execute\_tuning\_task(a); dbms\_output.put\_line('select dbms\_sqltune.report\_tuning\_task ('''||a||''') from dual;'); end; / ; select to\_char(substr(dbms\_sqltune.report\_tuning\_task ('a'),1,2000)) from dual;

1. 锁和HOLDER

select sid,serial#,username,sql\_id,event,program from v$session t where t.sid in (select sid from ( select a.sid, connect\_by\_isleaf as isleaf from v$session a start with a.blocking\_session is not null connect by nocycle a.sid = prior a.blocking\_Session ) where isleaf =1);  select * from ( select a.inst\_id,a.sid, a.sql\_id, a.event, a.program, connect\_by\_isleaf as isleaf, sys\_connect\_by\_path(sid,'<-') tree, level as tree\_level from gv$session a start with a.blocking\_session is not null connect by nocycle a.sid = prior a.blocking\_Session and a.inst\_id=prior a.BLOCKING\_INSTANCE ) where isleaf =1 order by tree\_level asc;

1. 历史上PGA的使用情况

select snap\_id,round(value/1024/1024,0) from dba\_hist\_pgastat where name='total PGA allocated' order by snap\_id；

1. 查询当前进程的PGA使用情况

SELECT p.spid, p.pid, s.sid, s.serial#, s.status, p.pga\_alloc\_mem, p.PGA\_USED\_MEM, s.username, s.osuser, s.program FROM vprocess p, vsession s WHERE s.paddr ( + ) = p.addr AND p.BACKGROUND is NULL /* Remove if need to monitor background processes */ ORDER BY p.pga\_alloc\_mem desc;