09.26.06

现场数据库一次调优过程

Posted in Oracle优化 at 6:31 pm by David.Guo

今天在现场,开发的兄弟反映有一个sql语句跑的特别慢.

ok,把sql拿过来看看,

语句如下:

SELECT DISTINCT B.OBJECTID, B.OBJECTNAME, B.CLDBH, B.ZDLJDZ, B.CLDXH,
    decode(B.CLDXZ, ‘40’, ‘总加组’, ‘测量点’) AS CLDXZ, D.MC AS SJLX,
    decode(B.CLDXZ, ‘40’, ‘2’, ‘1’) AS XXDBS, D.DM AS BZSJLX,
    to_char(A.SJSJ, ‘yyyymmdd’) AS SJSJ, C.DJLX, B.ZDGYH AS GYH
FROM RW_LDSJ A, V_MISSPOINT_CLD B, XT_SJRWDZB C, XT_BZDMB D
WHERE B.CLDBH = A.CLDBH
AND B.ZDGYH = C.GYH
AND A.RWH = C.RWH
AND C.SBLX = decode(B.SBSX, ‘01’, ‘10’, ‘02’, ‘10’, ‘03’, ‘20’, ‘04’, ‘30’, ‘05’, ‘30’, ‘10’)
AND C.BZSJLX = D.DM(+) AND D.DMLB = ‘BZSJLX’
AND A.SJSJ >= to_date(‘2006-9-25’,’yyyy-mm-dd’)
AND A.SJSJ < to_date(‘2006-9-25’,’yyyy-mm-dd’) + 1
AND C.SBLX = ‘20’
AND C.GYH = ‘100’
AND B.DXLX = 10
AND C.BZSJLX IN (21,22,23,24,25)
ORDER BY B.ZDLJDZ, D.DM

首先来分析使用到的表吧,表RW_LDSJ中有记录760万,v_misspoint_cldb是一个视图,xt_sjrwdzb数据量39条,xt_bzdmb有数据3600行.

rw_ldsj上有主键在字段cldbh,rwh,sjsj,gyh上,有另外一个索引在字段rwh,sjsj上.

首先简化这个sql,明显的,B.ZDGYH=C.GYH,而下面有C.GYH=’100’,那就是B.ZDGYH=’100’,其次,明显的C.SBLX=’20’,而上面的decode函数中,也过多了吗,另外,C.BZSJLX IN(21,22,23,24,25)这个用一个>=和< 不是很好吗,我是很反对随便用in的.

修改后的代码如下:

SELECT distinct B.OBJECTID, B.OBJECTNAME, B.CLDBH, B.ZDLJDZ, B.CLDXH,
    decode(B.CLDXZ, ‘40’, ‘总加组’, ‘测量点’) AS CLDXZ, D.MC AS SJLX,
    decode(B.CLDXZ, ‘40’, ‘2’, ‘1’) AS XXDBS, D.DM AS BZSJLX,
    to_char(A.SJSJ, ‘yyyymmdd’) AS SJSJ, C.DJLX, B.ZDGYH AS GYH
FROM RW_LDSJ A, V_MISSPOINT_CLD B, XT_SJRWDZB C, XT_BZDMB D
WHERE B.CLDBH = A.CLDBH
AND B.ZDGYH = ‘100’
AND A.RWH = C.RWH
AND b.sbsx=’03’
AND C.BZSJLX = D.DM(+)
AND D.DMLB = ‘BZSJLX’
AND A.SJSJ >= to_date(‘2006-9-25’,’yyyy-mm-dd’)
AND A.SJSJ < to_date(‘2006-9-25’,’yyyy-mm-dd’) + 1
AND C.SBLX = ‘20’
AND C.GYH = ‘100’
AND B.DXLX = 10
AND C.BZSJLX >=21
AND c.BZSJLX<=25
ORDER BY B.ZDLJDZ, D.DM

看起来似乎要简单一点.

执行一把看看,需要260s,这个时候,系统居然既然除了jobq slave wait等待事件.

看执行计划,表rw_ldsj这个表确实走的索引,但是走的是非主键的.这就不对吧,如果走主键,事实上,rw_ldsj这个表中的所有主键字段都出现在where中了,应该要走主键才对.

这个时候,作一个强制使用pk索引的hint,看看,快多了吗,才3s就搞定了.提高了接近90倍.

这个时候想到后面的定时分析数据库中所有表的job,去看看,每一个月才一次,晕呀,这个不是要7天一次的吗(rw_ldsj中,每7天产生数据800万以上,然后把一周以前的删除,也就是insert前先删除过期数据),如果一个月才分析,显然会导致统计数据不准确

那么我分析下这个表,取样选择1,很快就分析完成,去掉hint,看执行计划,使用了pk,ok,应用上看看,果然快了,爽.

总结总结:如果要分析oracle的性能问题,一定要让sql语句在你的控制下使用你预先想到的执行计划,否则就该看看是为啥了.当然了,使用cbo,必须经常分析,特别是对于数据改动频繁的表,否则,你会得到你极其不希望得到的结果.

Permalink Comments

12.02.05

enqueue 等待事件探究

Posted in Oracle优化, Brotherxiao's at 12:29 pm by bachelor

Enqueues are another type of locking mechanism used in Oracle.
An enqueue is a more sophisticated mechanism which permits several concurrent
processes to have varying degree of sharing of “known” resources. Any object
which can be concurrently used, can be protected with enqueues. A good example
is of locks on tables. We allow varying levels of sharing on tables e.g.
two processes can lock a table in share mode or in share update mode etc.
One difference is that the enqueue is obtained using an OS specific
locking mechanism. An enqueue allows the user to store a value in the lock,
i.e the mode in which we are requesting it. The OS lock manager keeps track
of the resources locked. If a process cannot be granted the lock because it
is incompatible with the mode requested and the lock is requested with wait,
the OS puts the requesting process on a wait queue which is serviced in FIFO.
Another difference between latches and enqueues is that
in latches there is no ordered queue of waiters like in enqueues. Latch
waiters may either use timers to wakeup and retry or spin (only in
multiprocessors). Since all waiters are concurrently retrying (depending on
the scheduler), anyone might get the latch and conceivably the first one to
try might be the last one to get.
x$ksqst(8i)