• [20190415]11g下那些latch是共享的.txt


    [20190415]11g下那些latch是共享的.txt

    http://andreynikolaev.wordpress.com/2010/11/23/shared-latches-by-oracle-version/

    --//oracle并没有文档准确说明那些latch是支持共享,作者的链接通过使用orderbug手工调用kslgetsl()函数(10g)或者kslgetsl_w()函
    --//数(11g),确定那些latch支持共享模式.我仅仅重复测试看看.

    1.环境:
    SYS@book> @ ver1
    PORT_STRING                    VERSION        BANNER
    ------------------------------ -------------- --------------------------------------------------------------------------------
    x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

    2.建立测试脚本:
    --//drop table shared_latches purge;
    create table shared_latches(
      version varchar2(50),   /* Oracle version */
      latch# number,          /* latch# */
      name   varchar2(200),   /* ltach name */
      shared varchar2(1)      /* if 'Y' then latch is shared  */
    );
    --//建立表shared_latches.

    spool list_shared_latches.sh
    select 'sqlplus /nolog @is_latch_shared 0x'||addr||' '||latch#||' "'||translate(name,'''',' ')||'"'
       from v$latch_parent;
    spool off
    --//获得latch list.仅仅测试latch parent就ok了.适当编辑整理脚本.

    $ cat is_latch_shared.sql
    /*
         This file is part of demos for "Contemporary Latch Internals" seminar v.24.08.2010
         Copyright:   (c) Andrey S. Nikolaev (Andrey.Nikolaev@rdtex.ru) RDTEX
         http://AndreyNikolaev.wordpress.com
         for 32bit Oracle 10g and above
    */
    connect / as sysdba
    set verify off
    WHENEVER SQLERROR EXIT;
    set pagesize 0

    alter session set max_dump_file_size=0;
    /*
       before 11g shared latch get function was named kslgetsl()
       in 11g - kslgetsl_w()
    */
    col shared_latch_function new_value shared_latch_function
    var db_v varchar2(100);
    var db_comp varchar2(100);
    begin dbms_utility.db_version(:db_v,:db_comp); end;
    /
    select decode(sign(replace(:db_v,'.','')-110000),1,'kslgetsl_w', 'kslgetsl') shared_latch_function from dual;
    --//11g use kslgetsl_w,other use kslgetsl.

    oradebug setmypid
    /* try to get latch as shared in S mode */
    oradebug call &shared_latch_function &1 1 2 3 8
    /* if ORA-00600: [545] was raised in previous statement then latch was exclusive.  The error terminates the script */
    /* free the latch */
    oradebug call kslfre &1
    /* if we are here, the latch was shared */
    insert into shared_latches(version,latch#,name,shared) select version,latch#,name,'Y' from v$latch,v$instance where latch#=&2;
    commit;
    exit

    --//说明:作者利用oradebug调用报错,后面的插入语句不会执行,来完整获得那些shared latch的列表.

    3.执行:
    $ . list_shared_latches.sh
    --//略.

    4.结果:
    VERSION        LATCH# NAME                                     S
    ---------- ---------- ---------------------------------------- -
    11.2.0.4.0          6 test shared non-parent l0                Y
    11.2.0.4.0         14 session idle bit                         Y
    11.2.0.4.0         15 client/application info                  Y
    11.2.0.4.0         21 ksim group membership cache              Y
    11.2.0.4.0         24 parameter table management               Y
    11.2.0.4.0         49 resmgr group change latch                Y
    11.2.0.4.0         50 channel handle pool latch                Y
    11.2.0.4.0         51 channel operations parent latch          Y
    11.2.0.4.0         52 message pool operations parent latch     Y
    11.2.0.4.0         75 sixteenth spare latch - S non-parent     Y
    11.2.0.4.0         76 seventeenth spare latch - S non-parent   Y
    11.2.0.4.0         77 eighteenth spare latch - S non-parent    Y
    11.2.0.4.0         78 nineteenth spare latch - S non-parent    Y
    11.2.0.4.0         79 twentieth spare latch - S non-parent     Y
    11.2.0.4.0         80 twenty-first spare latch - S par         Y
    11.2.0.4.0         81 twenty-second spare latch - S par        Y
    11.2.0.4.0         82 twenty-third spare latch - S par         Y
    11.2.0.4.0         83 twenty-fourth spare latch - S par        Y
    11.2.0.4.0         84 twenty-fifth spare latch - S par         Y
    11.2.0.4.0         92 SGA mapping latch                        Y
    11.2.0.4.0         93 active service list                      Y
    11.2.0.4.0        102 storage server table manipulation latch  Y
    11.2.0.4.0        144 name-service namespace bucket            Y
    11.2.0.4.0        149 gcs remastering latch                    Y
    11.2.0.4.0        150 gcs partitioned table hash               Y
    11.2.0.4.0        151 gcs pcm hashed value bucket hash         Y
    11.2.0.4.0        155 recovery domain hash list                Y
    11.2.0.4.0        160 gcr global ctx                           Y
    11.2.0.4.0        164 Memory Management Latch                  Y
    11.2.0.4.0        171 kcbtsemkid latch                         Y
    11.2.0.4.0        173 buffer pool                              Y
    11.2.0.4.0        177 cache buffers chains                     Y
    11.2.0.4.0        183 tablespace key chain                     Y
    11.2.0.4.0        189 object queue header operation            Y
    11.2.0.4.0        215 gc element                               Y
    11.2.0.4.0        275 MinActiveScn Latch                       Y
    11.2.0.4.0        280 In memory undo latch                     Y
    11.2.0.4.0        281 KTF sga latch                            Y
    11.2.0.4.0        283 Change Notification Hash table latch     Y
    11.2.0.4.0        286 change notification client cache latch   Y
    11.2.0.4.0        289 lob segment hash table latch             Y
    11.2.0.4.0        290 lob segment query latch                  Y
    11.2.0.4.0        291 lob segment dispenser latch              Y
    11.2.0.4.0        296 space background SGA latch               Y
    11.2.0.4.0        301 kssmov protection latch                  Y
    11.2.0.4.0        305 domain validation update latch           Y
    11.2.0.4.0        306 kdlx hb parent latch                     Y
    11.2.0.4.0        308 Dedup Write Append Table Latch           Y
    11.2.0.4.0        317 global KZLD latch for mem in SGA         Y
    11.2.0.4.0        318 Read Only Database Account Status        Y
    11.2.0.4.0        320 Policy Refresh Latch                     Y
    11.2.0.4.0        321 Policy Hash Table Latch                  Y
    11.2.0.4.0        322 OLS label cache                          Y
    11.2.0.4.0        323 instance information                     Y
    11.2.0.4.0        324 policy information                       Y
    11.2.0.4.0        325 global ctx hash table latch              Y
    11.2.0.4.0        326 Role grants to users                     Y
    11.2.0.4.0        327 Role graph                               Y
    11.2.0.4.0        328 Security Class Hashtable                 Y
    11.2.0.4.0        331 third Audit Vault latch                  Y
    11.2.0.4.0        332 fourth Audit Vault latch                 Y
    11.2.0.4.0        344 resmgr:active threads                    Y
    11.2.0.4.0        349 resmgr:plan CPU method                   Y
    11.2.0.4.0        355 Shared B-Tree                            Y
    11.2.0.4.0        356 Memory Queue                             Y
    11.2.0.4.0        357 Memory Queue Subscriber                  Y
    11.2.0.4.0        369 ODM-NFS:Global file structure            Y
    11.2.0.4.0        373 SGA heap creation lock                   Y
    11.2.0.4.0        375 SGA pool creation lock                   Y
    11.2.0.4.0        378 SGA blob lock                            Y
    11.2.0.4.0        380 kgb latch                                Y
    11.2.0.4.0        382 SGA table lock                           Y
    11.2.0.4.0        383 Event Group Locks                        Y
    11.2.0.4.0        385 Sage HT Latch                            Y
    11.2.0.4.0        409 JOX SGA heap latch                       Y
    11.2.0.4.0        410 JOX JIT latch                            Y
    11.2.0.4.0        425 hash table dml freq tracking latch       Y
    11.2.0.4.0        426 hash table column usage latch            Y
    11.2.0.4.0        433 compile environment latch                Y
    11.2.0.4.0        436 Result Cache: RC Latch                   Y
    11.2.0.4.0        464 KWQP Prop Status                         Y
    11.2.0.4.0        465 KWQS pqueue ctx latch                    Y
    11.2.0.4.0        466 KWQS pqsubs latch                        Y
    11.2.0.4.0        467 AQ Propagation Scheduling Proc Table     Y
    11.2.0.4.0        468 AQ Propagation Scheduling System Load    Y
    11.2.0.4.0        471 rules engine rule set statistics         Y
    11.2.0.4.0        472 rules engine rule statistics             Y
    11.2.0.4.0        477 kwqbsn:qsga                              Y
    11.2.0.4.0        479 bufq statistics                          Y
    11.2.0.4.0        481 queue sender's info. latch               Y
    11.2.0.4.0        482 bq:time manger info latch                Y
    11.2.0.4.0        486 KWQMN job cache list latch               Y
    11.2.0.4.0        487 KWQMN to-be-Stopped Buffer list Latch    Y
    11.2.0.4.0        504 XDB NFS Stateful SGA Latch               Y
    11.2.0.4.0        505 qmne Export Table Latch                  Y
    11.2.0.4.0        507 XDB Byte Lock SGA Latch                  Y
    11.2.0.4.0        508 XDB Mcache SGA Latch                     Y
    11.2.0.4.0        565 WCR: sync                                Y
    11.2.0.4.0        566 WCR: processes HT                        Y
    11.2.0.4.0        578 JS Sh mem access                         Y
    11.2.0.4.0        579 PL/SQL warning settings                  Y
    101 rows selected.

    --//我导入作者的测试结果在链接https://andreynikolaev.wordpress.com/上可以找到.
    --//https://andreynikolaev.wordpress.com/summary-tables/shared_latches-ctl/,作者没有做11.2.0.4测试.
    --//11.2.0.3结果如下:
    SYS@book> select count(*) from  scott.shared_latches where version='11.2.0.3.0';
      COUNT(*)
    ----------
            93

  • 相关阅读:
    python中if __name__ == '__main__': 的解析
    python项目练习地址
    HTTP Response Splitting攻击探究 <转>
    常用操作系统扫描工具介绍
    app兼容性测试的几种方案
    svn自动备份并上传到ftp
    有关交易的性能测试点
    修改文件测试的测试点
    新增文件测试的测试点
    添加附件测试的测试点
  • 原文地址:https://www.cnblogs.com/lfree/p/10710674.html
Copyright © 2020-2023  润新知