从no parse到hard parse的差距

July 25th, 2007 | Categories: Boring | Tags:
Leave a comment | Trackback

单独一个Session，测试其在No parse,soft soft parse, soft parse和hard parse对latch相关资源的消耗。

使用的测试代码来自asktom,Oracle版本9i


create table run_stats ( runid varchar2(15), name varchar2(80), value int );

create or replace view stats
as select ‘STAT…’ || a.name name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic#
union all
select ‘LATCH.’ || name, gets from v$latch;

declare
l_start number;
l_cnt number;
v_date date;

begin
insert into run_stats select ‘before’, stats.* from stats;
execute immediate ‘alter session set session_cached_cursors=0′;
l_start := dbms_utility.get_time;
for i in 1 .. 1000
loop
execute immediate ’select sysdate from dual’ into v_date;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs’ );

execute immediate ‘alter session set session_cached_cursors=0′;
insert into run_stats select ‘after 1′, stats.* from stats;
l_start := dbms_utility.get_time;
for i in 1 .. 1000
loop
execute immediate ’select count(*) from dual where sysdate=sysdate+’||i into l_cnt;
end loop;
dbms_output.put_line( (dbms_utility.get_time-l_start) || ‘ hsecs’ );
insert into run_stats select ‘after 2′, stats.* from stats;
commit;
end;
/

select a.name, b.value-a.value run1, c.value-b.value run2,
( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = ‘before’
and b.runid = ‘after 1′
and c.runid = ‘after 2′
and (c.value-a.value) > 0
order by abs( (c.value-b.value)-(b.value-a.value))
/

只需要相应更改循环内的SQL语句和参数session_cached_cursors的设置，来改变分析的方式:no parse,soft soft parse,soft parse,hard parse.


单独线程测试的结果

No Parse VS Soft Soft Parse:

NAME NO_PARSE SOFT_SOFT_PARSE DIFF
——————————————- ———- ———-
STAT…parse count (hard) 1 1 0
STAT…parse time elapsed 1 2 1
STAT…session cursor cache count 1 0 -1
STAT…parse time cpu 1 2 1
STAT…execute count 1006 1005 -1
STAT…opened cursors current 2 0 -2
LATCH.row cache enqueue latch 30 44 14
LATCH.row cache objects 30 54 24
STAT…opened cursors cumulative 7 1005 998
STAT…parse count (total) 7 1005 998
STAT…session cursor cache hits 3 1002 999
LATCH.shared pool 1121 2137 1016
LATCH.library cache pin allocation 73 2083 2010
LATCH.library cache pin 2091 4107 2016
LATCH.library cache 2263 4289 2026

Soft Soft Parse VS Soft Parse:

NAME soft_parse SOFT_SOFT_PARSE DIFF
—————————————– ———- ———-
STAT…parse time cpu 3 2 -1
STAT…parse time elapsed 4 2 -2
STAT…parse count (total) 1007 1005 -2
STAT…parse count (hard) 3 1 -2
STAT…opened cursors cumulative 1007 1005 -2
STAT…execute count 1007 1005 -2
LATCH.row cache enqueue latch 30 44 14
LATCH.row cache objects 30 54 24
STAT…session cursor cache hits 3 1002 999
LATCH.library cache pin 6099 4105 -1994
LATCH.library cache pin allocation 4080 2078 -2002
LATCH.shared pool 4153 2129 -2024
STAT…session uga memory max 688 4896 4208
LATCH.library cache 9285 4279 -5006

Soft Parse VS Hard Parse:

NAME soft_parse HARD_PARSE DIFF
—————————————— ———- ———-
STAT…parse count (total) 1007 1005 -2
STAT…opened cursors cumulative 1007 1005 -2
STAT…execute count 1007 1005 -2
LATCH.library cache load lock 0 12 12
STAT…parse time cpu 4 60 56
STAT…parse time elapsed 4 61 57
STAT…recursive cpu usage 19 82 63
STAT…parse count (hard) 1 1001 1000
LATCH.library cache pin allocation 4084 10154 6070
LATCH.child cursor hash table 8 7071 7063
LATCH.library cache pin 6103 13267 7164
LATCH.row cache enqueue latch 30 8088 8058
LATCH.row cache objects 30 8105 8075
LATCH.shared pool 4137 30154 26017
LATCH.library cache 9290 36745 27455

不用说，no parse有最好的性能；在写Oracle数据库应用的时候，要尽量少分析，多执行。

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. xiaolong78x
    July 27th, 2007 at 16:07
    Quote | #1

    什么情況下， 才会NO PARSE呢？
    我的记忆是， soft parse 和 soft soft parse的区别在于:
    soft parse: parse information 在library cache 中。不再PGA中
    soft soft parse: parse information 在 library cache中， 同时也存在在PGA中。

    那么什么情况下用NO PARSE呢？

  2. yumianfeilong
    July 27th, 2007 at 19:49
    Quote | #2

    oracle9.2.0.5 sqlplus中运行如下pl/sql，将只会有一个parse,N次之执行

    declare
    v_date date;
    begin
    for i in 1..N loop
    select sysdate into v_date from dual;
    end loop;
    end;
    /

    使用session_cached_cursor的soft soft parse也会触发parse call, pase count也会增加。

    每种语言，如java,cgi,都有实现no parse的方法。过几天我submit一个java的例子，现在没有安装jdk环境

  3. xiaolong78x
    July 28th, 2007 at 11:03
    Quote | #3

    ok , thank you reply!

  4. xiaolong78x
    August 1st, 2007 at 10:35
    Quote | #4

    期待你的NO PARSE的例子

  5. 木匠
    August 10th, 2007 at 02:19
    Quote | #5

    在程序里面parse 一次, (设置不同变量) 执行多次, 就是 NO PARSE 的例子.

    或者说近cursor cache 起来, 就能够 NO PARSE 啦, 这点有待商讨确认.

    PL/SQL 缺省就支持 SQL Cursor caching,
    同一个 Session 仔细相同的SQL, no parse.

    OCI JDBC connection pool 支持 cache cursor.

  6. 木匠
    August 10th, 2007 at 02:21
    Quote | #6

    同一个 Session “仔细”相同的SQL, no parse.

    仔细 => 执行, execute.

    –
    feilong兄弟, 能不能给更正一下. :)

  7. yumianfeilong
    August 10th, 2007 at 13:17
    Quote | #7

    对于application来说，比如java的代码，不确定是否设置了connection.setcachecount(N)，就能够保证no parse,还没有jre环境测试。

    在web application中，猜测可以这么设计：
    1)有connection pool,每个connection都通过preparedstatment打开一些SQL游标，且该preparedstatement在每次调用后并不关闭。
    2)web session执行SQL的时候，通过某种映射关系可以得到要执行SQL对应的preparedstatment handle
    3)然后setvalue,execute；且执行完毕后并不close游标

    需要比较复杂的封装和应用设计

  8. xiaolong78x
    August 11th, 2007 at 19:33
    Quote | #8

    感謝二位回復。

    二位的回復， 我不知道feilong大俠是不是同意木匠的觀點。

    以上是我做的一個例子，

    SQL> alter system flush shared_pool;

    System altered.

    SQL>
    SQL>
    SQL> select a.name name, b.value
    from 2 v$statname a, v$mystat b
    w 3 here a.statistic# = b.statistic# and
    a. 4 name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 22
    parse time elapsed 43
    parse count (total) 132
    parse count (hard) 38
    parse count (failures) 8

    SQL> declare
    i_f 2 irst number;
    i_ 3 second number;
    i 4 integer;
    beg 5 in
    6 i :=0;
    sel 7 ect sum(r_type) into i_first from test_bitmap
    whe 8 re r_type = ‘1′;
    9
    i: 10 = i 1;
    sele 11 ct sum(r_type) into i_first from test_bitmap
    wher 12 e r_type = ‘2′;
    e 13 nd;
    14 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    from 2 v$statname a, v$mystat b
    wh 3 ere a.statistic# = b.statistic# and
    a.n 4 ame like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 70
    parse time elapsed 102
    parse count (total) 197
    parse count (hard) 60
    parse count (failures) 8

    – 第一次執行， 存在硬分析
    SQL> declare
    i_fi 2 rst number;
    i_s 3 econd number;
    4 i integer;
    b 5 egin
    i 6 :=0;
    sel 7 ect sum(r_type) into i_first from test_bitmap
    whe 8 re r_type = ‘1′;

    9 i 10 := i 1;
    sel 11 ect sum(r_type) into i_first from test_bitmap
    whe 12 re r_type = ‘2′;
    en 13 d;
    14 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    2 from v$statname a, v$mystat b
    w 3 here a.statistic# = b.statistic# and
    a.n 4 ame like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 70
    parse time elapsed 102
    parse count (total) 201
    parse count (hard) 60
    parse count (failures) 8

    –第二次執行， 有4個sofe parse(包括查詢parse信息的一個），剩下三個parse, 我們很好理解。 一個procedure, 二個sql.繼續分析。。。

    SQL> declare
    i 2 _first number;
    i 3 _second number;
    i 4 integer;
    be 5 gin
    i 6 :=0;
    sel 7 ect sum(r_type) into i_first from test_bitmap
    whe 8 re r_type = to_char(i);

    9 i: 10 = i 1;
    s 11 elect sum(r_type) into i_first from test_bitmap
    12 where r_type = to_char(i);
    end 13 ;
    14 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    from v 2 $statname a, v$mystat b
    whe 3 re a.statistic# = b.statistic# and
    a.name 4 like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 72
    parse time elapsed 104
    parse count (total) 205
    parse count (hard) 62
    parse count (failures) 8

    —-改用了變量， 我想這就是木匠說的可以使用no parse的情況。
    第一次存在存在硬分析， 可以理解
    SQL> declare
    i 2 _first number;
    i_ 3 second number;
    i i 4 nteger;
    be 5 gin
    i : 6 =0;
    se 7 lect sum(r_type) into i_first from test_bitmap
    w 8 here r_type = to_char(i);
    9
    i: 10 = i 1;
    sele 11 ct sum(r_type) into i_first from test_bitmap
    wher 12 e r_type = to_char(i);
    end 13 ;
    14 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    fr 2 om v$statname a, v$mystat b
    wh 3 ere a.statistic# = b.statistic# and
    a 4 .name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 72
    parse time elapsed 104
    parse count (total) 209
    parse count (hard) 62
    parse count (failures) 8

    –這里還是四次parse。 所以說木匠的觀點有問題。
    –當然也有可能我理解有問題。

    SQL>
    SQL> show parameter session_cached_cursors

    NAME TYPE
    ———————————— ———————-
    VALUE
    ——————————
    session_cached_cursors integer
    10

  9. 木匠
    August 15th, 2007 at 05:55
    Quote | #9

    Java方面, 正确的名称是: Statement Caching
    文档里面明确指出了, parse one time, execute many time (no parse).

    (Oracle 10g 以后) JDBC 3.0 defines a statement-caching interface.

    Statement caching can:

    Prevent the overhead of repeated cursor creation
    Prevent repeated statement parsing and creation
    Reuse data structures in the client

    The following code specifies a cache size of ten statements:

    ((OracleConnection)conn).setStatementCacheSize(10);

    请参考 第22章
    Oracle® Database JDBC Developer’s Guide and Reference
    10g Release 2 (10.2)
    Part Number B14355-02

  10. 木匠
    August 15th, 2007 at 06:24
    Quote | #10

    To: xiaolong78x,

    想 “飞龙” 一样, 使用变量, 搞一个循环就好了,
    declare
    v_date date;
    begin
    for i in 1..N loop
    select sysdate into v_date from dual;
    UPDATE emp SET ename = ename WHERE empno = i;
    end loop;
    end;
    /

    一点建议, 多使用PL/SQL package, 匿名块anonymous PL/SQL block 可能不支持一些特性.

  11. xiaolong78x
    August 15th, 2007 at 18:29
    Quote | #11

    做了下試驗， 確實存在NO PARSE的狀況。 但并非向木匠所說“
    同一个 Session 執行相同的SQL, no parse.” 而是在package一個循環里，執行相同的SQL, 第一次循環需要parse， 剩下的循環NO PARSE。
    如下：

    SQL> declare
    2 v_date date;
    3 begin
    4 for i in 1..10 loop
    5 select sysdate into v_date from dual;
    6 UPDATE emp SET ename = ename WHERE empno = i;
    7 end loop;
    8 end;
    9 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    2 from v$statname a, v$mystat b
    3 where a.statistic# = b.statistic# and
    4 a.name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 99
    parse time elapsed 234
    parse count (total) 898
    parse count (hard) 214
    parse count (failures) 0

    SQL> declare
    2 v_date date;
    3 begin
    4 for i in 1..10 loop
    5 select sysdate into v_date from dual;
    6 UPDATE emp SET ename = ename WHERE empno = i;
    7 end loop;
    8 end;
    9 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    2 from v$statname a, v$mystat b
    3 where a.statistic# = b.statistic# and
    4 a.name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 99
    parse time elapsed 234
    parse count (total) 902
    parse count (hard) 214
    parse count (failures) 0

    SQL> declare
    2 v_date date;
    3 begin
    4 for i in 1..10 loop
    5 select sysdate into v_date from dual;
    6 UPDATE emp SET ename = ename WHERE empno = i;
    7 select sysdate into v_date from dual;
    8 end loop;
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    2 from v$statname a, v$mystat b
    3 where a.statistic# = b.statistic# and
    4 a.name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 101
    parse time elapsed 236
    parse count (total) 907
    parse count (hard) 215
    parse count (failures) 0

    SQL> declare
    2 v_date date;
    3 begin
    4 for i in 1..10 loop
    5 select sysdate into v_date from dual;
    6 UPDATE emp SET ename = ename WHERE empno = i;
    7 select sysdate into v_date from dual;
    8 end loop;
    9 end;
    10 /

    PL/SQL procedure successfully completed.

    SQL> select a.name name, b.value
    2 from v$statname a, v$mystat b
    3 where a.statistic# = b.statistic# and
    4 a.name like ‘%parse%’;

    NAME VALUE
    —————————————- ———-
    parse time cpu 101
    parse time elapsed 236
    parse count (total) 912
    parse count (hard) 215
    parse count (failures) 0

    JAVA使用的情況應該也一樣嗎？ 有待確認！

  12. 木匠
    August 18th, 2007 at 06:12
    Quote | #12

    OK.

    1) create a PL/SQL package

    CREATE OR REPLACE PACKAGE scott.my_pkg AS

    PROCEDURE call_proc(p_empno NUMBER);

    END;
    /

    CREATE OR REPLACE PACKAGE BODY scott.my_pkg AS

    PROCEDURE call_proc(p_empno NUMBER)
    is
    begin

    update emp set sal = sal 1
    where empno = p_empno;

    update emp set sal = sal - 1
    where empno = p_empno;

    commit;

    end;

    END;
    /

    2) Call the store procedure

    begin
    my_pkg.call_proc(15);
    my_pkg.call_proc(16);
    my_pkg.call_proc(17);
    my_pkg.call_proc(18);
    my_pkg.call_proc(19);
    end;
    /

    3) in another session, check the parse count

    select a.sid, b.name, a.value
    from v$sesstat a, v$statname b
    where a.statistic# = b.statistic#
    and b.name like ‘%parse count%’
    and a.sid = 178;

    结论, parse count (total)增加了 2,
    但是, 没有一个是 SQL parse, 那是 PL/SQL 或者 其它的.
    – 等以后找到了说明, 再做补充.

    (SQL 被调用了10次 (2 X 5), 而parse count (total)只增加了 2 )