[20220309]完善shp4.sql脚本.txt

[20220309]完善shp4.sql脚本.txt --//昨天看了以前写的shp4.sql脚本,发现写的不好,不能充分利用索引,重新改写一个新版本: 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 SYS@book> select * from V$INDEXED_FIXED_COLUMN where table_name='X$KGLOB' order by 2 ; TABLE_NAME INDEX_NUMBER COLUMN_NAME          COLUMN_POSITION ---------- ------------ -------------------- --------------- X$KGLOB               1 KGLNAHSH                           0 X$KGLOB               2 KGLOBT03                           0 --//修改如下： $ cat shp4x.sql column N0_6_16 format 99999999 SELECT DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')     text,        kglhdadr,        kglhdpar,        substr(kglnaobj,1,40) c40,        KGLHDLMD,        KGLHDPMD,        kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,        kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,        kglnahsh,        kglobt03,        kglobt09     FROM x$kglob  WHERE kglobt03 = '&1' or KGLNAHSH= &2; --//我以前写的查询条件是 WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;，这样测试环境没什么， --//但是到了生产系统很慢，并且很容易遭遇ora-00600错误。 2.测试： SYS@book> select sysdate from dual; SYSDATE ------------------- 2022-03-09 08:32:13 SYS@book> @ hash HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2343063137 7h35uxf5uhmm1            0      20065      1388734953  8ba84e61  2022-03-09 08:32:13    16777218 SYS@book> @ sharepool/shp4x 7h35uxf5uhmm1 0 TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address  000000007D7887D8 000000007C79E448 select sysdate from dual                          0          0          0 000000007CC7EFA0 000000007D2FCA48       4528       8088       3081     15697      15697 2343063137 7h35uxf5uhmm1          0 parent handle address 000000007C79E448 000000007C79E448 select sysdate from dual                          0          0          0 000000007BE75BE0 00                     4720          0          0      4720       4720 2343063137 7h35uxf5uhmm1      65535 SYS@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  352c9tf8hy2nw, child number 0 ------------------------------------- SELECT DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')  text, kglhdadr,        kglhdpar,        substr(kglnaobj,1,40) c40, KGLHDLMD,     KGLHDPMD,     kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,     kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglob hs6+kglobt16 N20,     kglnahsh,     kglobt03,     kglobt09   FROM x$kglob  WHERE kglobt03 = '7h35uxf5uhmm1' or KGLNAHSH= 0 Plan hash value: 4104444136 ------------------------------------------------------------------ | Id  | Operation        | Name    | E-Rows |E-Bytes| Cost (%CPU)| ------------------------------------------------------------------ |   0 | SELECT STATEMENT |         |        |       |     1 (100)| |*  1 |  FIXED TABLE FULL| X$KGLOB |      8 |  1536 |     0   (0)| ------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1 / X$KGLOB@SEL$1 Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(("KGLOBT03"='7h35uxf5uhmm1' OR "KGLNAHSH"=0)) --//虽然两个索引都存在，但是oracle选择全表扫描，在测试环境没有问题，如果生产系统共享内存很大的情况下，很慢并且容易出现ora-00600之类的错误。 --//加入提示看看。  $ cat shp4x.sql column N0_6_16 format 99999999 SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')         text,        kglhdadr,        kglhdpar,        substr(kglnaobj,1,40) c40,            KGLHDLMD,            KGLHDPMD,            kglhdivc,        kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,        kglobhs0+kglobhs6+kglobt16 N0_6_16,            kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,            kglnahsh,            kglobt03,            kglobt09   FROM x$kglob  WHERE kglobt03 = '&1' or KGLNAHSH= &2; --//注：加入use_concat无效。 SYS@book> @ sharepool/shp4x 7h35uxf5uhmm1 0 TEXT                  KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09 --------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ---------- child handle address  000000007D7887D8 000000007C79E448 select sysdate from dual                          0          0          0 000000007CC7EFA0 000000007D2FCA48       4528       8088       3081     15697      15697 2343063137 7h35uxf5uhmm1          0 parent handle address 000000007C79E448 000000007C79E448 select sysdate from dual                          0          0          0 000000007BE75BE0 00                     4720          0          0      4720       4720 2343063137 7h35uxf5uhmm1      65535 SYS@book> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID  4q21f8mg38n11, child number 0 ------------------------------------- SELECT /*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */ DECODE (kglhdadr,                kglhdpar, 'parent handle address',                'child handle address')  text,        kglhdadr,        kglhdpar, substr(kglnaobj,1,40) c40,     KGLHDLMD,     KGLHDPMD,     kglhdivc,     kglobhd0,        kglobhd6,        kglobhs0,kglobhs6,kglobt16,  kglobhs0+kglobhs6+kglobt16 N0_6_16, kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,     kglnahsh,     kglobt03,     kglobt09   FROM x$kglob  WHERE kglobt03 = '7h35uxf5uhmm1' or KGLNAHSH= 0 Plan hash value: 453496081 ---------------------------------------------------------------------------------- | Id  | Operation                | Name            | E-Rows |E-Bytes| Cost (%CPU)| ---------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |                 |        |       |     1 (100)| |   1 |  CONCATENATION           |                 |        |       |            | |*  2 |   FIXED TABLE FIXED INDEX| X$KGLOB (ind:1) |      1 |   178 |     0   (0)| |*  3 |   FIXED TABLE FIXED INDEX| X$KGLOB (ind:2) |      8 |  1424 |     0   (0)| ---------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1_1 / X$KGLOB@SEL$1    3 - SEL$1_2 / X$KGLOB@SEL$1_2 Predicate Information (identified by operation id): ---------------------------------------------------    2 - filter("KGLNAHSH"=0)    3 - filter(("KGLOBT03"='7h35uxf5uhmm1' AND LNNVL("KGLNAHSH"=0))) Note -----    - Warning: basic plan statistics not available. These are only collected when:        * hint 'gather_plan_statistics' is used for the statement or        * parameter 'statistics_level' is set to 'ALL', at session or system level 42 rows selected. --//这样的查询可以充分利用索引,对于大的共享内存执行更加,也不容易出现ora-00600之类的错误.