direct path read和sql(or pl/sql)

昨天读jonathan lewis的sql-vs-plsql,本文的comment部分值得仔细看。对于SQL和PLSQL的使用场景和效率问题,一直都是一个比较有趣的话题。文中引用到Oracle论坛上的一个问题https://community.oracle.com/thread/3759404?start=0&tstart=0。JL给出了测试的结果和原因,同时也看到oracle引入了越来越多的新技术,在每一个版本上都有很有意思的优化和改变,只有熟练使用这些特性才可以让查询跑的更好更快。 在上述的文章中涉及到11g中的direct path read,Alex Fatkulintanel poder对这个特性(event)进行了深入的研究。

在sql-vs-plsql文中,JL在footnote中提到,使用PL/SQL进行更新的时候是不安全的,因为被更新的表的数据可能在PLSQL操作的过程中出现更新操作,会抛出异常。这让我想起前几天看到的never文中对plsql异常数据的捕获和性能的描述,文中的脚本很有用。

关闭direct path read和开启direct path read的比较如下。关闭这个特性之后增加了cache buffer contention和相关的latch。

SQL> select blocks FROM user_tables WHERE table_name = 'JOIN1';

    BLOCKS
----------
   1161628

Elapsed: 00:00:00.16
SQL> SELECT name,block_size,buffers FROM v$buffer_pool;

NAME                 BLOCK_SIZE    BUFFERS
-------------------- ---------- ----------
DEFAULT                    8192     468375

Elapsed: 00:00:00.08
SQL> show parameter db_cache_size

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ------------------------------------------------------------
db_cache_size                                                big integer 1G
SQL>
SQL>
SQL> select bytes/1024/1024 from user_segments where segment_name = 'JOIN1';

BYTES/1024/1024
---------------
           9088

Elapsed: 00:00:00.50
SQL> select (1161628*8)/1024 from dual;

(1161628*8)/1024
----------------
      9075.21875

Elapsed: 00:00:00.03

运行runStats_pkg包对开启direct path read和关闭之后的statistics和latch信息进行对比

set serveroutput on 
exec runStats_pkg.rs_start;
select * from join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352; 
exec runStats_pkg.rs_middle;
alter session set events '10949 trace name context forever' ;  --turn off serial direct path read
select * from join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352;
exec runStats_pkg.rs_stop;

对比的运行结果如下,我去掉了一些没有关注的信息

Name                                  Run1        Run2        Diff
LATCH.hash table column usage          286           0        -286    $$$$$$$$$
STAT...bytes sent via SQL*Net        1,514       1,811         297
LATCH.Real-time plan statistic           1         356         355
LATCH.name-service request que         119         490         371
LATCH.JS queue state obj latch          96         480         384
LATCH.row cache objects                106         562         456
LATCH.redo writing                     613         108        -505
STAT...bytes received via SQL*       1,099       1,627         528
LATCH.redo allocation                  595          36        -559
LATCH.DML lock allocation              669          73        -596
STAT...session logical reads     1,161,907   1,162,616         709
STAT...consistent gets           1,161,843   1,162,568         725
STAT...calls to kcmgcs                 226         953         727
LATCH.enqueue hash chains            2,313       1,441        -872
LATCH.SQL memory manager worka         271       1,148         877
LATCH.messages                       1,032       1,910         878
LATCH.undo global data                 999          13        -986
STAT...CPU used by this sessio         596       1,617       1,021
STAT...CPU used when call star         595       1,617       1,022
STAT...non-idle wait time              593       1,978       1,385
STAT...user I/O wait time              592       1,978       1,386
LATCH.post/wait queue                  752       2,423       1,671
LATCH.KJCT flow control latch        1,631       3,754       2,123
LATCH.ges caches resource list       1,572       3,752       2,180
STAT...DB time                       1,197       3,507       2,310    $$$$$$$$$$
LATCH.ges deadlock list              1,210       3,624       2,414
LATCH.ges timeout list               1,210       3,624       2,414
STAT...non-idle wait count           6,186       9,110       2,924
STAT...Elapsed Time                  1,199       4,929       3,730    $$$$$$$$$$
LATCH.channel operations paren       1,142       5,468       4,326
LATCH.ges group table                3,195       7,631       4,436
LATCH.ges domain table               3,183       7,631       4,448
LATCH.ges enqueue table freeli       3,184       7,641       4,457
LATCH.ges resource table freel       2,011       7,586       5,575
STAT...Number of read IOs issu       9,088           0      -9,088    $$$$$$$$$$
LATCH.cache table scan latch             0       9,088       9,088    $$$$$$$$$$
LATCH.checkpoint queue latch         3,585      13,826      10,241    $$$$$$$$$$
LATCH.ges resource hash list         6,671      17,143      10,472
LATCH.ges process parent latch       7,588      18,960      11,372
LATCH.multiblock read objects            0      18,176      18,176
STAT...session uga memory                0      65,488      65,488
LATCH.simulator hash latch             160     259,356     259,196
STAT...free buffer inspected             0     817,916     817,916     $$$$$$$$$
STAT...session pga memory          983,040      65,536    -917,504
STAT...physical reads cache pr           0   1,152,540   1,152,540    $$$$$$$$$$
STAT...consistent gets from ca         210   1,153,478   1,153,268
LATCH.gcs resource hash                 57   1,161,641   1,161,584
STAT...gc local grants                   1   1,161,628   1,161,627
STAT...free buffer requested            11   1,161,638   1,161,627
STAT...physical reads cache              0   1,161,628   1,161,628    $$$$$$$$$$
STAT...physical reads direct     1,161,628           0  -1,161,628    $$$$$$$$$$
STAT...consistent gets direct    1,161,629           0  -1,161,629    $$$$$$$$$$
STAT...consistent gets from ca         214   1,162,568   1,162,354    $$$$$$$$$$
LATCH.gcs partitioned table ha      17,092   1,227,181   1,210,089
STAT...session uga memory max    1,800,264           0  -1,800,264
LATCH.object queue header oper         199   1,863,550   1,863,351
STAT...session pga memory max    2,752,512           0  -2,752,512
LATCH.cache buffers chains           8,280   4,648,812   4,640,532    $$$$$$$$$$
STAT...Effective IO time         5,919,635           0  -5,919,635
STAT...file io wait time               109  19,780,227  19,780,118
STAT...logical read bytes from   2,277,376########################
                                                                  
Run1 latches total versus runs -- difference and pct              
Run1        Run2        Diff       Pct                            
90,032   9,318,655   9,228,623       .97%                         
                                                                  
PL/SQL procedure successfully completed. 

对会话级别的statistics信息收集可以使用jonathan lewis的my-stats
文中使用的脚本runStats_pkg来自Thomas Kyte,脚本内容和使用方法如下

grant connect,resource to blue;
grant create view to blue;
grant create view to blue;
grant select on sys.v_$statname to blue;
grant select on sys.v_$mystat to blue;
grant select on sys.v_$latch to blue;
grant select on sys.v_$timer to blue;  
   
create global temporary table run_stats
    ( runid varchar2(15),
      name varchar2(80),
      value int )
on commit preserve rows;

create or replace view stats
as select 'STAT...' || a.name name, b.value
from sys.v_$statname a, sys.v_$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from sys.v_$latch
union all
select 'STAT...Elapsed Time', hsecs from sys.v_$timer;

create or replace package runstats_pkg
as
    procedure rs_start;
    procedure rs_middle;
    procedure rs_stop( p_difference_threshold in number default 0 );
end;
/
        
create or replace package body runstats_pkg
as
    g_start number;
    g_run1 number;
g_run2 number;

procedure rs_start
    is
    begin
        delete from run_stats;

        insert into run_stats
        select 'before', stats.* from stats;
            
        g_start := dbms_utility.get_cpu_time;
end;

procedure rs_middle
    is
    begin
        g_run1 := (dbms_utility.get_cpu_time-g_start);
     
        insert into run_stats
        select 'after 1', stats.* from stats;
        g_start := dbms_utility.get_cpu_time;

end;

procedure rs_stop(p_difference_threshold in number default 0)
    is
    begin
        g_run2 := (dbms_utility.get_cpu_time-g_start);

        dbms_output.put_line
        ( 'Run1 ran in ' || g_run1 || ' cpu hsecs' );
        dbms_output.put_line
        ( 'Run2 ran in ' || g_run2 || ' cpu hsecs' );
            if ( g_run2 <> 0 )
            then
        dbms_output.put_line
        ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
          '% of the time' );
            end if;
        dbms_output.put_line( chr(9) );

        insert into run_stats
        select 'after 2', stats.* from stats;

        dbms_output.put_line
        ( rpad( 'Name', 30 ) || lpad( 'Run1', 12 ) ||
          lpad( 'Run2', 12 ) || lpad( 'Diff', 12 ) );

        for x in
        ( select rpad( a.name, 30 ) ||
                 to_char( b.value-a.value, '999,999,999' ) ||
                 to_char( c.value-b.value, '999,999,999' ) ||
                 to_char( ( (c.value-b.value)-(b.value-a.value)), '999,999,999' ) data
            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
             and abs( (c.value-b.value) - (b.value-a.value) )
                   > p_difference_threshold
           order by abs( (c.value-b.value)-(b.value-a.value))
        ) loop
            dbms_output.put_line( x.data );
        end loop;

        dbms_output.put_line( chr(9) );
        dbms_output.put_line
        ( 'Run1 latches total versus runs -- difference and pct' );
        dbms_output.put_line
        ( lpad( 'Run1', 12 ) || lpad( 'Run2', 12 ) ||
          lpad( 'Diff', 12 ) || lpad( 'Pct', 10 ) );

        for x in
        ( select to_char( run1, '999,999,999' ) ||
                 to_char( run2, '999,999,999' ) ||
                 to_char( diff, '999,999,999' ) ||
                 to_char( round( run1/decode( run2, 0, to_number(null), run2) *100,2 ), '99,999.99' ) || '%' data
            from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
                          sum( (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 a.name like 'LATCH%'
                    )
        ) loop
            dbms_output.put_line( x.data );
        end loop;
end;
end;
/
show err
usage:
set serveroutput on
exec runStats_pkg.rs_start;
statement 1
exec runStats_pkg.rs_middle;
statement 2
exec runStats_pkg.rs_stop;
此条目发表在CBO, Infrastructure分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s