[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之類的錯誤.