[20250514]21c使用dbms_metadata.get_ddl参看临时表定义问题(整理).txt
[20250514]21c使用dbms_metadata.get_ddl参看临时表定义问题(整理).txt
--//前段时间在自己的虚拟机器测试遇到的问题，发现在21c下使用dbms_metadata.get_ddl参看临时表定义，会报ora-04031错误，当时
--//没有做的笔记很乱，重新整理记录如下：
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.
2.问题：
--//重启数据库执行如下：
SCOTT@book01p> drop table gtt purge ;
Table dropped.
SCOTT@book01p> create global temporary table gtt ( x int, y int ) on commit preserve rows;
Table created.
SCOTT@book01p> @ tpt/ddl dept
PL/SQL procedure successfully completed.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-----------------------------------------------------------------------
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
--//查看普通表正常。
SYS@book> @ sgastatx %
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 360710144 344
shared pool (Total): 360710144 344
-- Allocations matching "%":
SUBPOOL NAME SUM(BYTES) MB
------------------------------ ------------------------------ ---------- ----------
shared pool (1): SQLA 36589544 34.89
free memory 27367432 26.1
KGLH0 23382328 22.3
ksunfy_sess_meta 1 16160256 15.41
SO private sga 11893720 11.34
row cache mutex 9897624 9.44
ASH buffers 8388608 8
private strands 7490560 7.14
KQR X PO 7036496 6.71
KGLS 6650296 6.34
....
SCOTT@book01p> @ tpt/ddl gtt
PL/SQL procedure successfully completed.
ERROR:
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","ub1[]: qkexrXformVal")
ORA-06512: at "SYS.DBMS_METADATA", line 6781
ORA-06512: at "SYS.DBMS_METADATA", line 2989
ORA-06512: at "SYS.DBMS_METADATA", line 3616
ORA-06512: at "SYS.DBMS_METADATA", line 5058
ORA-06512: at "SYS.DBMS_METADATA", line 5386
ORA-06512: at "SYS.DBMS_METADATA", line 6752
ORA-06512: at "SYS.DBMS_METADATA", line 9815
ORA-06512: at line 1
no rows selected
SYS@book> @ sgastatx %
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 360738592 344.03
shared pool (Total): 360738592 344.03
-- Allocations matching "%":
SUBPOOL NAME SUM(BYTES) MB
------------------------------ ------------------------------ ---------- ----------
shared pool (1): free memory 57535160 54.87
XDBSC 17503520 16.69
ksunfy_sess_meta 1 16160256 15.41
SQLA 14519560 13.85
SO private sga 11893720 11.34
KGLH0 11654768 11.11
row cache mutex 9897624 9.44
ASH buffers 8388608 8
private strands 7490560 7.14
row cache hash 6598416 6.29
KGLSG 6309848 6.02
KGLS 6156488 5.87
--//可以发现执行ddl.sql脚本查看临时表定义，XDBSC组件占用很高的内存，实际占用也不大，但是我的测试环境sga
--//太小了，导致ora-04031出现问题。
SYS@book> @ ashtop event 1=1 &1min
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
62 1.0 60% | 2025-05-09 11:35:15 2025-05-09 11:36:10 3 43 44
25 .4 24% | SGA: allocation forcing component growth 2025-05-09 11:35:45 2025-05-09 11:36:03 1 15 15
9 .2 9% | row cache lock 2025-05-09 11:35:52 2025-05-09 11:36:03 1 9 9
2 .0 2% | db file async I/O submit 2025-05-09 11:36:07 2025-05-09 11:36:10 1 2 2
2 .0 2% | latch: shared pool 2025-05-09 11:35:49 2025-05-09 11:35:50 1 2 2
1 .0 1% | ADR block file write 2025-05-09 11:35:56 2025-05-09 11:35:56 1 1 1
1 .0 1% | kksfbc child completion 2025-05-09 11:36:07 2025-05-09 11:36:07 1 1 1
1 .0 1% | log buffer space 2025-05-09 11:36:03 2025-05-09 11:36:03 1 1 1
1 .0 1% | rdbms ipc message 2025-05-09 11:35:59 2025-05-09 11:35:59 1 1 1
9 rows selected.
--//sga的shared_pool_size增加导致整个数据库临时挂起，而且一旦出现ora-04031错误，基本经常报这个错误。
--//在cdb层面执行也是类似的情况出现：
SYS@book> @ tpt/ddl plan_table$
PL/SQL procedure successfully completed.
ERROR:
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","select o.owner#,o.name,o.nam...","SQLA","tmp")
ORA-06512: at "SYS.DBMS_METADATA", line 6781
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","kobjn : kkdcchs")
ORA-06512: at "SYS.DBMS_METADATA", line 2989
ORA-06512: at "SYS.DBMS_METADATA", line 3616
ORA-06512: at "SYS.DBMS_METADATA", line 5058
ORA-06512: at "SYS.DBMS_METADATA", line 5386
ORA-06512: at "SYS.DBMS_METADATA", line 6752
ORA-06512: at "SYS.DBMS_METADATA", line 9815
ORA-06512: at line 1
no rows selected
--//我重启数据库后执行@ tpt/ddl dept后，再次执行：
SYS@book> @ sgastatx XDBSC
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 360710144 344
shared pool (Total): 360710144 344
-- Allocations matching "XDBSC":
no rows selected
--//没有该组件出现。
--//另外我在11g做相同的测试：
SYS@book> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 11.2.0.4.0
BANNER : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL procedure successfully completed.
SYS@book> @ sgastatx XDBSC
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (0 - Unused): 25165824 24
shared pool (1): 171966464 164
shared pool (Total): 197132288 188
-- Allocations matching "XDBSC":
no rows selected
--//无论如何都没有该XDBSC组件出现。
3.分析：
--//猜测一下xdb 表示XML db的信息，sc表示Secure Connection。为什么出现这个问题不是很清楚。
--//仔细看报错,应该执行该语句时报错：
ORA-04031: unable to allocate 40 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^b183d1c6","kobjn : kkdcchs")
--//似乎也验证是XML的某一些东西。SQLA^b183d1c6中^后出现的16进制表示hash_value.
--//0xb183d1c6 = 2978206150
SYS@book> @ sharepool/shp4 '' 2978206150
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 00000000663FEDD0 0000000067472DE8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 2 00 00 0 0 3606 3606 3606 2978206150 7nfwwwfss7nf6 0
parent handle address 0000000067472DE8 0000000067472DE8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 2 00000000674767B8 00 4064 0 0 4064 4064 2978206150 7nfwwwfss7nf6 65535
--//子游标没有堆0，堆6信息，根本没有分配内存空间。
SYS@book> @ sql_id 7nfwwwfss7nf6
-- SQL_ID = 7nfwwwfss7nf6 come from x$kglob
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM
FROM SYS.KU$_FHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192) = 8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128) != 0
AND KU$.SCHEMA_OBJ.NAME = :NAME1
AND KU$.SCHEMA_OBJ.OWNER_NAME = :SCHEMA2;
--//注：做了格式化处理。
SYS@book> select sql_text from v$sql where sql_id='7nfwwwfss7nf6';
no rows selected
SYS@book> @ bind_cap 7nfwwwfss7nf6 ''
no rows selected
--//仅仅在x$kglob中看到，似乎在分析该语句阶段就报错了，根本无法执行。
--//删除后面的赋值手工执行看看。
SYS@book> SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0;
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
*
ERROR at line 1:
ORA-04031: unable to allocate 104 bytes of shared memory ("shared pool","SELECT /*+all_rows*/ SYS_XML...","SQLA^c0cc1c5e","kgmtyp : kgmgchd")
--//手工执行如下通过：
SYS@book> SELECT /*+all_rows*/ KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0;
--//输出略。这样问题就集中在SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7'))上，不熟悉这些东西。
--//要么就是这个组件消耗很大的共享池内存，测试环境配置太低了，下午继续测试看看。
4.继续：
--//下午继续，我的测试环境设置shared_pool_size，db_cache_size的大小，也就是扩展余地很小，这也是该参数的最小大小。
--//尝试取消db_cache_size设置看看。
SYS@book> show parameter sga_target
PARAMETER_NAME TYPE VALUE
-------------- ----------- ------
sga_target big integer 768M
SYS@book> show parameter shared_pool_size
PARAMETER_NAME TYPE VALUE
---------------- ----------- -----
shared_pool_size big integer 344M
SYS@book> show parameter db_cache_size
PARAMETER_NAME TYPE VALUE
-------------- ----------- ------
db_cache_size big integer 404M
SYS@book> alter system reset db_cache_size;
System altered.
--//重启数据库。
SCOTT@book01p> @ tpt/ddl gtt
PL/SQL procedure successfully completed.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
----------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE "SCOTT"."GTT"
( "X" NUMBER(*,0),
"Y" NUMBER(*,0)
) ON COMMIT PRESERVE ROWS ;
--//OK,没有问题。
SYS@book> @ sgastatx XDBSC
-- All allocations:
SUBPOOL BYTES MB
------------------------------ ---------- ----------
shared pool (1): 381681664 364
shared pool (Total): 381681664 364
-- Allocations matching "XDBSC":
SUBPOOL NAME SUM(BYTES) MB
---------------- ----- ---------- ----------
shared pool (1): XDBSC 17446584 16.64
SYS@book> @ hidez ^__shared_pool_size$
SYS@book> @ pr
==============================
NUM : 293
N_HEX : 125
CON_ID : 0
NAME : __shared_pool_size
DESCRIPTION : Actual size in bytes of shared pool
DEFAULT_VALUE : FALSE
SESSION_VALUE : 381681664
SYSTEM_VALUE : 381681664
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : IMMEDIATE
PL/SQL procedure successfully completed.
SYS@book> @ calcx 377487360/1024/1024
DEC HEX
---------- --------------------
360.000000 0000000000000168
--//增加16M比前面的344M。
--//这样修改设置如下：
--//设置 sga_target 768+16 = 784M，shared_pool_size = 360M
SYS@book> alter system set sga_target=800M scope=spfile;
System altered.
SYS@book> alter system set shared_pool_size=360M scope=spfile;
System altered.
SYS@book> alter system set db_cache_size=404M scope=spfile;
System altered.
--//404+360 = 764M,剩下800-764 = 36m。
SYS@book> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--//开启数据库马上基本不再出现前面的问题，虽然很慢。
SYS@book> @ sharepool/shp4 '' b183d1c6
HANDLE_TYPE KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
---------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
child handle address 0000000068659BD8 000000006868F1A8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 0 000000006866CBC8 000000006861E638 52608 48961560 3606 49017774 49017774 2978206150 7nfwwwfss7nf6 0
parent handle address 000000006868F1A8 000000006868F1A8 SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU 0 0 0 0000000068643268 00 4064 0 0 4064 4064 2978206150 7nfwwwfss7nf6 65535
--//堆6占用很大 48961560/1024/1024 = 46.70M.并且再次查看马上释放)。
SYS@book> @ ksmsp 000000006861E638 ''
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1 HEAP_DESC
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- ---------------- ----------------- -------------------------
....
SGA 00000000786BFDA8 1 4 SQLA^b183d1c6 4176 freeabl 0 000000006861E638 00000000786BFDA8 00000000786C0DF8 KSMCHPAR=000000006861E638
SGA 00000000786BED58 1 4 SQLA^b183d1c6 4176 freeabl 0 000000006861E638 00000000786BED58 00000000786BFDA8 KSMCHPAR=000000006861E638
SGA 00000000786BDD58 1 4 SQLA^b183d1c6 4096 freeabl 0 000000006861E638 00000000786BDD58 00000000786BED58 KSMCHPAR=000000006861E638
SGA 000000006861DDE0 1 1 KGLH0^b183d1c6 4096 recr 4095 0000000068643268 000000006861DDE0 000000006861EDE0
12009 rows selected.
--//怪不得这么容易报ora-4031错误，分析执行计划就占用许多时间，占用太多的chunk。
--//实际上即使以上设置，使用一段时间再次执行还是很容易出现ora-4031错误。可以看出21c不能设置shared_pool_size太小，建立的
--//chunk比11g多许多，缺省_column_tracking_level=53，也导致问题加剧。总而言之，还是测试环境sga_target设置太小，适当做一
--//些调整，注意由于采用hugepages，适当修改内核参数vm.nr_hugepages满足需要。
--//最后设置调整如下：
SYS@book> alter system set sga_target=1028M scope=spfile ;
System altered.
--//注：原来设置1024M报错，要求设置1028.只能修改设置1028M，原因是大于1024时Granule Size 从4M变成16M。
--//db_cache_size=404M，实际上设置的是416M。
SYS@book> alter system set large_pool_size=64M scope=spfile ;
System altered.
SYS@book> alter system set memoptimize_pool_size=64M scope=spfile ;
System altered.
SYS@book> alter system set db_cache_size=404M scope=spfile ;
System altered.
SYS@book> alter system set shared_pool_size=480M scope=spfile ;
System altered.
SYS@book> show parameter db_cache_size
PARAMETER_NAME TYPE VALUE
-------------- ----------- -----
db_cache_size big integer 416M
SYS@book> @ hidez ^sga_target$|^shared_pool_size$|^db_cache_size$|^memoptimize_pool_size$|^large_pool_size$
NUM N_HEX CON_ID NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---- ----- ---------- --------------------- ----------------------------------------------------------- ------------- ------------- ------------ ----- ---------
294 126 0 shared_pool_size size in bytes of shared pool FALSE 503316480 503316480 FALSE IMMEDIATE
294 126 2 shared_pool_size size in bytes of shared pool FALSE 503316480 0 FALSE IMMEDIATE
294 126 3 shared_pool_size size in bytes of shared pool FALSE 503316480 0 FALSE IMMEDIATE
296 128 0 large_pool_size size in bytes of large pool FALSE 67108864 67108864 FALSE IMMEDIATE
1797 705 0 sga_target Target size of SGA FALSE 1090519040 1090519040 FALSE IMMEDIATE
1797 705 2 sga_target Target size of SGA FALSE 1090519040 0 FALSE IMMEDIATE
1797 705 3 sga_target Target size of SGA FALSE 1090519040 0 FALSE IMMEDIATE
1876 754 0 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 436207616 FALSE IMMEDIATE
1876 754 2 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 0 FALSE IMMEDIATE
1876 754 3 db_cache_size Size of DEFAULT buffer pool for standard block size buffers FALSE 436207616 0 FALSE IMMEDIATE
1885 75D 0 memoptimize_pool_size Size of cache for imoltp buffers FALSE 67108864 67108864 FALSE IMMEDIATE
11 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3084240/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
