[20240929]记录测试中的错误.txt

--//注意访问X$表，千万不要以sys用户PDB方式登录查询，可能根本没有结果。
--//我个人并不喜欢pdb，可以oracle肯定强推pdb，现在版本无法安装非pdb环境的。记录测试过程：

1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 21.0.0.0.0
BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@book> select PDB_ID,PDB_NAME,CON_UID from dba_pdbs;
    PDB_ID PDB_NAME                          CON_UID
---------- ------------------------------ ----------
         3 BOOK01P                        1073777561
         2 PDB$SEED                       2763294012

SCOTT@book01p> select * from dept where deptno=20;
    DEPTNO DNAME                          LOC
---------- ------------------------------ -------------
        20 RESEARCH                       DALLAS

SCOTT@book01p> @ hash
HASH_VALUE SQL_ID        CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START      SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
  95129850 80baj2c2ur47u            0     102650      2852011669   5ab90fa  2024-09-29 15:56:25    16777216

2.测试：
--//sys用户登录pdb=book01。
SYS@book01p> @ sharepool/shp4 80baj2c2ur47u 0
HANDLE_TYPE            KGLHDADR         KGLHDPAR         C40                                        KGLHDLMD   KGLHDPMD   KGLHDIVC KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address   0000000066279C20 000000006627A380 select * from dept where deptno=20                0          0          0 000000006630D7A0 0000000066220868       8080      20224       3299     31603      31603   95129850 80baj2c2ur47u          0
parent handle address  000000006627A380 000000006627A380 select * from dept where deptno=20                0          0          0 000000006627A1C0 00                     4064          0          0      4064       4064   95129850 80baj2c2ur47u      65535

SYS@book01p> @ fchaz 000000006627A1C0
no rows selected

SYS@book01p> select count(*) from x$ksmsp ;
  COUNT(*)
----------
         0

SYS@book01p> select * from x$ksmsp ;
no rows selected
--//当时真的吓了我一跳,换一句话以pdb登录查询x$ksmsp根本没有记录。

--//sys用户登录sid=book。
SYS@book>  @ fchaz 000000006627A1C0
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- -----------------
SGA 000000006627A150          1          1 KGLDA                   512 freeabl           0 00               000000006627A150 000000006627A350

SYS@book> select count(*) from x$ksmsp ;
  COUNT(*)
----------
    146404

--//非常容易出现歧义性。
--//有点不理解的是x$kglob访问没有这个问题，而x$ksmsp不行。

--//sys用户登录pdb=book01。
SYS@book01p> select count(*) from x$kglob ;
  COUNT(*)
----------
      3350

--//sys用户登录sid=book。
SYS@book> select count(*) from x$kglob ;
COUNT(*)
----------
     11092


3.继续分析：
--//sys用户登录pdb=book01。
SYS@book01p> select count(*) from x$ksmsp ;

  COUNT(*)
----------
         0

SYS@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7x84z21dmcfsv, child number 0
-------------------------------------
select count(*) from x$ksmsp
Plan hash value: 1489174925
-------------------------------------------------------------------
| Id  | Operation         | Name    | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |        |       |     1 (100)|
|   1 |  SORT AGGREGATE   |         |      1 |    13 |            |
|*  2 |   FIXED TABLE FULL| X$KSMSP |      1 |    13 |     0   (0)|
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / "X$KSMSP"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CON_ID"=0 OR "CON_ID"=3))
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
31 rows selected.

--//sys用户登录sid=book。
SYS@book> select count(*) from x$ksmsp ;

  COUNT(*)
----------
    171056

SYS@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7x84z21dmcfsv, child number 1
-------------------------------------
select count(*) from x$ksmsp
Plan hash value: 1489174925
----------------------------------------------------------------------
| Id  | Operation         | Name    | E-Rows | Cost (%CPU)| E-Time   |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |        |    17 (100)|          |
|   1 |  SORT AGGREGATE   |         |      1 |            |          |
|   2 |   FIXED TABLE FULL| X$KSMSP |    187K|    17 (100)| 00:00:01 |
----------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / "X$KSMSP"@"SEL$1"
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

--//前者加入过滤条件filter(("CON_ID"=0 OR "CON_ID"=3))。

SELECT x$ksmsp.*,x$ksmsp.ksmchptr ksmchptr_begin,TO_CHAR(TO_NUMBER(KSMCHPTR,'XXXXXXXXXXXXXXXX') + KSMCHSIZ ,'FM0XXXXXXXXXXXXXXX') "KSMCHPTR_END+1" FROM x$ksmsp WHERE to_number(substr('000000006627A1C0', instr(lower('000000006627A1C0'), 'x')+1) ,'XXXXXXXXXXXXXXXX') between to_number(ksmchptr,'XXXXXXXXXXXXXXXX') and to_number(ksmchptr,'XXXXXXXXXXXXXXXX') + ksmchsiz - 1;
ADDR                   INDX    INST_ID     CON_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_BEGIN   KSMCHPTR_END+1    HEAP_DESC
---------------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
00007F399E35A4B0     149585          1          1          1          1 KGLDA            000000006627A150        512 freeabl           0 00               000000006627A150 000000006627A350

--//而这里记录CON_ID=1，fchaz脚本查询不到就很正常了。
SYS@book> select count(*),CON_ID from x$ksmsp group by CON_ID;
  COUNT(*)     CON_ID
---------- ----------
    188457          1
--//x$ksmsp记录的全部都是CON_ID=1，感觉这个是不是oracle的bug。

SELECT c.OBJ#
      ,c.CON_ID#
      ,c.DBID
      ,c.CON_UID
      ,c.status
      ,o.name
  FROM sys.container$ c, sys.obj$ o
 WHERE o.obj# = c.obj# AND c.status <> 4;

      OBJ#    CON_ID#       DBID    CON_UID STATUS NAME
---------- ---------- ---------- ---------- ------ ------------------------------
       267          1 1617337831          1      2 CDB$ROOT
     76661          3 1073777561 1073777561      2 BOOK01P
     76089          2 2763294012 2763294012      2 PDB$SEED
 
--//CON_ID#=1对应的是CDB$ROOT，总之以后工作中注意。
--//不要以sys用户PDB方式登录查询，可能根本没有结果。