[20260810]row cache的直方图信息.txt
--//昨天探究row cache mutex等待事件关联的对象。oracle 缺省分析方法是auto，这样会在表的一些字典上建立大量的直方图信息。
--//实际上许多字段根本不需要建立直方图，尝试建立表并且建立直方图，看看相关字段直方图占用共享内存的情况。
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> create table t as select * from all_objects;
Table created.
--//强制在每个字段建立直方图。
--//session 1:
SCOTT@book01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 254', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
SCOTT@book01p> select owner,object_name,object_id,data_object_id from dba_objects where owner='SCOTT' and object_name='T';
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------------------ ---------- --------------
SCOTT T 76760 76760
SCOTT@book01p> @ 10to16 76760
10 to 16 HEX REVERSE16
----------------- -------------------------------------------------------------------
0000000000012bd8 0xd82b0100-00000000
--//在 v$rowcache_parent视图保存的直方图信息key按照REVERSE16的格式(注意大写)+字段号作为开头，这是猜测出来的,注意intel cpu大小头
--//问题。
--//session 2:
SYS@book> alter system flush SHARED_POOL;
System altered.
SYS@book> select * from v$rowcache_parent where cache#=16 and key like 'D82B0100%';
no rows selected
--//session 1:
SCOTT@book01p> select * from t where rownum=1;
--//输出略。
--//session 2:
sys@book> column key noprint
SYS@book> select v.*,substr(v.key,1,20) c20 from v$rowcache_parent v where cache#=16 and key like 'D82B0100%';
INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID C20
------ ---------- ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------ --------------------
1049 555 0000000070A966F8 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100060000000000
1106 4365 000000006B7B1210 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100080000000000
1112 4635 000000006953C9D0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100010000000000
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1173 9218 0000000069EE2920 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100130000000000
1231 12986 0000000069EE1900 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100160000000000
1300 18387 0000000070E5C4E0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000E0000000000
1324 20028 0000000069EE0E40 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100180000000000
1336 20603 000000006953ADE0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100030000000000
1371 22650 000000006FDDDD10 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01001B0000000000
1412 26403 000000006FDDE040 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01001A0000000000
1493 31538 0000000066FCCF20 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100070000000000
1513 33114 000000006953AAB0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100050000000000
1527 34125 000000006F464940 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100040000000000
1569 36366 0000000070E5CFA0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000C0000000000
1613 38890 0000000070E5D500 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000B0000000000
1674 43124 0000000070E5CA40 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000D0000000000
1689 44468 000000006FDDE370 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100190000000000
1705 45276 0000000069EE1E60 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100150000000000
1776 49242 000000006B7F94F8 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100090000000000
1778 49250 0000000069EE23C0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100140000000000
1869 57055 0000000069EE13A0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100170000000000
1875 57402 000000006953B840 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100020000000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1889 58438 0000000070E5B200 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100110000000000
1920 60744 000000006B7F8368 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000A0000000000
1961 63080 0000000070E5B750 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100100000000000
1977 64044 0000000070E5BA80 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000F0000000000
1983 64198 0000000069EE2E80 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100120000000000
27 rows selected.
--//表T一共27个字段。
--//字段号2，3，4,5应OBJECT_NAME，SUBOBJECT_NAME，OBJECT_ID，DATA_OBJECT_ID，直方图bucket=254.
--//看看相关字段OBJECT_NAME的直方图占用共享内存的情况。
SYS@book> @ fchaz 000000006953B840
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006953B810 1 1 KQR PO 816 recr 584 00 000000006953B810 000000006953BB40
--//占用816字节。
--//再通过地址parent='000000006953B840'查询v$rowcache_subordinate。
SYS@book> select * from v$rowcache_subordinate where parent='000000006953B840';
INDX HASH ADDRESS CACHE# SUBCACHE# SUBCACHE_NAME EXISTENT PARENT CON_ID
------ ---------- ---------------- ---------- ---------- ---------------------------- -------- ------------------------------ ------
2322 57402 000000006F435CF0 16 1 dc_histogram_data_values Y 000000006953B840 3
2323 57402 000000006F436420 16 1 dc_histogram_data_values Y 000000006953B840 3
2324 57402 0000000066BD4A60 16 1 dc_histogram_data_values Y 000000006953B840 3
2325 57402 0000000066BD5190 16 1 dc_histogram_data_values Y 000000006953B840 3
2326 57402 0000000066BD58C0 16 1 dc_histogram_data_values Y 000000006953B840 3
2327 57402 0000000066BD5FF0 16 1 dc_histogram_data_values Y 000000006953B840 3
2328 57402 0000000066BD6720 16 1 dc_histogram_data_values Y 000000006953B840 3
2329 57402 000000006F407CD0 16 1 dc_histogram_data_values Y 000000006953B840 3
2330 57402 000000006F408400 16 1 dc_histogram_data_values Y 000000006953B840 3
2331 57402 000000006F408B30 16 1 dc_histogram_data_values Y 000000006953B840 3
2332 57402 000000006F409260 16 1 dc_histogram_data_values Y 000000006953B840 3
2333 57402 000000006F409990 16 1 dc_histogram_data_values Y 000000006953B840 3
2334 57402 000000006953B110 16 1 dc_histogram_data_values Y 000000006953B840 3
13 rows selected.
--//SUBCACHE_NAME=dc_histogram_data_values
SYS@book> @ fchaz 000000006F435CF0
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006F435CC0 1 1 KQR SO 1840 recr 608 00 000000006F435CC0 000000006F4363F0
SYS@book> @ fchaz 000000006F436420
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006F4363F0 1 1 KQR SO 1840 recr 608 00 000000006F4363F0 000000006F436B20
SYS@book> @ fchaz 000000006953B110
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006953B0E0 1 1 KQR SO 1840 recr 608 00 000000006953B0E0 000000006953B810
--//每个占用1840字节，1840*13 = 23920。
--//再来看看owner字段，直方图bucket=25.
SYS@book> @ fchaz 000000006953C9D0
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006953C9A0 1 1 KQR PO 816 recr 584 00 000000006953C9A0 000000006953CCD0
SYS@book> select * from v$rowcache_subordinate where parent='000000006953C9D0';
INDX HASH ADDRESS CACHE# SUBCACHE# SUBCACHE_NAME EXISTENT PARENT CON_ID
------ ---------- ---------------- ---------- ---------- ---------------------------------------------------------------- -------- ------------------------------ ------
142 4635 000000006953BB70 16 1 dc_histogram_data_values Y 000000006953C9D0 3
143 4635 000000006953C2A0 16 1 dc_histogram_data_values Y 000000006953C9D0 3
--//仅仅2条记录。
SYS@book> @ fchaz 000000006953BB70
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006953BB40 1 1 KQR SO 1840 recr 608 00 000000006953BB40 000000006953C270
SYS@book> @ fchaz 000000006953C2A0
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 000000006953C270 1 1 KQR SO 1840 recr 608 00 000000006953C270 000000006953C9A0
--//仅仅占用 2*1840 = 3680.
--//可以想象如果数据库的表建立大量直方图，数据字典占用大量的共享内存，而许多字段的直方图实际上没有必要的。oracle 9i分析
--//表缺省repeat，10g以后修改为auto，前者容易遗漏在相应字段建立直方图，后者容易在许多不需要字段建立直方图。
--//当然现在服务器内存都很大，这些直方图信息不会消耗许多共享内存。
3.继续：
--//session 1:
SCOTT@book01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE 1', cascade=>true, no_invalidate=>false)
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> column key noprint
SYS@book> select v.*,substr(v.key,1,20) c20 from v$rowcache_parent v where cache#=16 and key like 'D82B0100%';
no rows selected
SYS@book> alter system flush SHARED_POOL;
System altered.
--//session 1:
SCOTT@book01p> select * from t where rownum=1;
--//输出略。
SYS@book> select v.*,substr(v.key,1,20) c20 from v$rowcache_parent v where cache#=16 and key like 'D82B0100%';
INDX HASH ADDRESS CACHE# CACHE_NAME EXISTENT LOCK_MODE LOCK_REQUEST TXN SADDR INST_LOCK_REQUEST INST_LOCK_RELEASE IN INST_LOC INST_LOC CON_ID C20
------ ---------- ---------------- ---------- -------------------- -------- ---------- ------------ ---------------- ---------------- ----------------- ----------------- -- -------- -------- ------ --------------------
1202 555 0000000066978F70 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100060000000000
1280 4365 00000000687D6650 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100080000000000
1287 4635 0000000066979F60 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100010000000000
1364 9218 00000000687D4350 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100130000000000
1441 12986 000000006250D510 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100160000000000
1531 18387 00000000687D5330 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000E0000000000
1564 20028 000000006250CEB0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100180000000000
1580 20603 0000000066979900 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100030000000000
1623 22650 000000006250C520 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01001B0000000000
1681 26403 000000006250C850 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01001A0000000000
1778 31538 0000000066978C40 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100070000000000
1808 33114 00000000669792A0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100050000000000
1827 34125 00000000669795D0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100040000000000
1876 36366 00000000687D5990 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000C0000000000
1929 38890 00000000687D5CC0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000B0000000000
2001 43124 00000000687D5660 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000D0000000000
2022 44468 000000006250CB80 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100190000000000
2040 45276 000000006250D840 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100150000000000
2126 49242 00000000687D6320 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100090000000000
2128 49250 000000006250DB70 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100140000000000
2254 57055 000000006250D1E0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100170000000000
2259 57402 0000000066979C30 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100020000000000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2276 58438 00000000687D49A0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100110000000000
2314 60744 00000000687D5FF0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000A0000000000
2366 63080 00000000687D4CD0 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100100000000000
2387 64044 00000000687D5000 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B01000F0000000000
2392 64198 00000000687D4670 16 dc_histogram_defs Y 0 0 00 00 0 0 00 00 3 D82B0100120000000000
27 rows selected.
SYS@book> @ fchaz 0000000066979F60
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR KSMCHPTR_BEGIN KSMCHPTR_END+1
---- ---------------- ---------- ---------- -------------------------------- ---------- -------- ---------- ---------------- ---------------- -----------------
VSGA 0000000066979F30 1 1 KQR PO 816 recr 584 00 0000000066979F30 000000006697A260
--//再通过地址parent='0000000066979C30'查询v$rowcache_subordinate。
SYS@book> select * from v$rowcache_subordinate where parent='0000000066979F60';
no rows selected
SYS@book> select * from v$rowcache_subordinate where parent='00000000687D4670';
no rows selected
--//这样就没有直方图信息在数据字典里面。
4.小结：
--//可以看出仅仅在需要的字段建立直方图，可以少量节省共享池内存消耗，同时以后的分析表也可以节省时间。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3179843/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
