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

[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方式登录查询，可能根本没有结果。