oracle高效设计阅读心得体会_分析的成本——《Oracle高效设计》学习笔记

Oracle有两类分析：

1)硬分析：语句通过语句执行阶段的每一个步骤：从分析到优化、到行资源生成、到执行。

2)软分析：语句通过语句执行阶段的某些步骤，而不是每一个步骤。特别是跳过优化步骤(最昂贵的步骤)。

使用绑定变量减少了Oracle硬分析的次数，从而改进了效率：

create table t ( x varchar2(5) );

exec runstats_pkg.rs_start

declare

type rc is ref cursor;

l_cursor rc;

begin

for i in 1 .. 5000

loop

open l_cursor for

'select x

from t

where x = ' || to_char(i);

close l_cursor;

end loop;

end;

/

exec runstats_pkg.rs_middle

declare

type rc is ref cursor;

l_cursor rc;

begin

for i in 1 .. 5000

loop

open l_cursor for

'select x

from t

where x = :x' using i;

close l_cursor;

end loop;

end;

/

exec runstats_pkg.rs_stop(500)

/

对比结果如下：

Run1 ran in 227 hsecs

Run2 ran in 35 hsecs

run 1 ran in 648.57% of the time

NameRun1Run2Diff

LATCH.messages54946-503

STAT...buffer is not pinned co61620-596

LATCH.dml lock allocation786118-668

LATCH.cache buffers lru chain80278-724

LATCH.undo global data890155-735

LATCH.object queue header oper851100-751

STAT...session cursor cache hi1165,0024,886

STAT...parse count (hard)5,0124-5,008

STAT...enqueue releases5,0186-5,012

STAT...enqueue requests5,0196-5,013

STAT...calls to get snapshot s10,1585,011-5,147

STAT...consistent gets from ca5,77326-5,747

STAT...consistent gets5,77326 -5,747

STAT...session logical reads5,81153-5,758

LATCH.simulator hash latch7,210733-6,477

LATCH.simulator lru latch7,212726-6,486

STAT...recursive calls21,827 15,079-6,748

STAT...physical read total byt8,1920-8,192

STAT...physical read bytes8,1920-8,192

LATCH.enqueues10,593210-10,383

LATCH.enqueue hash chains11,664380-11,284

LATCH.kks stats19,29272-19,220

LATCH.library cache pin50,44122,798-27,643

LATCH.session allocation40,6726,796-33,876

LATCH.library cache lock36,2951,152-35,143

STAT...session uga memory65,4640-65,464

STAT...session pga memory65,5360-65,536

LATCH.row cache objects98,7334,228-94,505

LATCH.library cache135,51824,391-111,127

LATCH.shared pool118,7622,782-115,980

LATCH.cache buffers chains165,64734,252-131,395

STAT...session uga memory max261,96457,880-204,084

Run1 latches total versus runs -- difference and pct

Run1Run2DiffPct

708,85799,670-609,187711.20%

硬分析对串行设备闩的使用是软分析的许多倍，从而严重影响了数据库的并发性能。但不是说软分析没有成本，实际上成本也不低。

通过设置参数SESSION_CATCHED_CURSORS，允许Oracle高速缓存游标，我们可以使用所谓更软的软分析。

对上面使用绑定变量的代码进行如下测试：

alter session set session_cached_cursors=0;

exec runstats_pkg.rs_start

declare

type rc is ref cursor;

l_cursor rc;

begin

for i in 1 .. 5000

loop

open l_cursor for

'select x

from t

where x = :x' using i;

close l_cursor;

end loop;

end;

/

exec runstats_pkg.rs_middle

alter session set session_cached_cursors=100;

declare

type rc is ref cursor;

l_cursor rc;

begin

for i in 1 .. 5000

loop

open l_cursor for

'select x

from t

where x = :x' using i;

close l_cursor;

end loop;

end;

/

exec runstats_pkg.rs_stop(100)

测试结果如下：

Run1 ran in 36 hsecs

Run2 ran in 35 hsecs

run 1 ran in 102.86% of the time

NameRun1Run2Diff

STAT...redo size2,7122,844132

STAT...bytes sent via SQL*Net7941,191397

STAT...bytes received via SQL*1,3581,950592

STAT...session cursor cache hi05,0005,000

LATCH.library cache40,07020,085-19,985

LATCH.library cache lock20,02630-19,996

Run1 latches total versus runs -- difference and pct

Run1Run2DiffPct

80,41640,449-39,967198.81%

从测试结果可以看出，缓存了游标后，减少了闩的使用，但执行时间上没有什么大的改进。实际上这个例子中对游标的使用并不合理，如果使用的是静态游标，执行效果会好很多：

alter session set session_cached_cursors=100;

exec runstats_pkg.rs_start

declare

type rc is ref cursor;

l_cursor rc;

begin

for i in 1 .. 5000

loop

open l_cursor for

'select x

from t

where x = :x' using i;

close l_cursor;

end loop;

end;

/

exec runstats_pkg.rs_middle

declare

cursor c( p_input in varchar2)

is

select x

from t

where x = p_input;

begin

for i in 1 .. 5000

loop

open c(i);

close c;

end loop;

end;

/

exec runstats_pkg.rs_stop(100)

/

测试结果如下：

Run1 ran in 34 hsecs

Run2 ran in 22 hsecs

run 1 ran in 154.55% of the time

NameRun1Run2Diff

LATCH.session allocation0110110

LATCH.row cache objects66223157

STAT...redo size2,7082,912204

LATCH.shared pool2229227

STAT...recursive calls15,00210,182-4,820

STAT...opened cursors cumulati5,00515-4,990

STAT...parse count (total)5,00513-4,992

STAT...session cursor cache hi5,0032-5,001

LATCH.library cache20,05110,198-9,853

LATCH.library cache pin20,03810,102-9,936

STAT...session pga memory0196,608196,608

Run1 latches total versus runs -- difference and pct

Run1Run2DiffPct

40,37521,181-19,194190.62%

从测试结果可以看出，通过改成用静态游标，分析次数有了极大的减少，执行效率有了很大改进。