X$表-CSDN博客

本文深入探讨了Oracle数据库的内存管理机制，详细解释了多个关键内存结构，包括shared pool、large pool等，以及如何通过查询特定视图如x$ksmsp、x$ksmlru来监控和诊断内存分配情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

X$表

Table Name	Acronym Explained	Comments
x$bh	buffer header	James Morle says it's buffer hash
x$kcbwait	kernel cache, block wait
x$kcbfwait	kernel cache, block file wait	A commonly used query tells you more than v$waitstat does
x$kcccp	kernel cache, controlfile checkpoint progress
x$kccle	kernel cache, controlfile logfile entry	lebsz usually used to show log block size
x$kcfio	kernel cache, file I/O
x$kclfh	kernel cache, lock file header
x$kclfi	kernel cache, lock file index
x$kcluh	kernel cache, lock undo header
x$kclui	kernel cache, lock undo index
x$kdxst	kernel data, index status	used in catalog.sql to create index_stats
x$kdxhs	kernel data, index histogram	used in catalog.sql to create index_histogram
x$kghlu	kernel generic, heap LRUs
x$kgllk	kernel generic, librarycache lock	used in catblock.sql to build dba_kgllock
x$kglob	kernel generic, librarycache object
x$kglpn	kernel generic, librarycache pin	used in catblock.sql to build dba_kgllock
x$kglst	kernel generic, librarycache status
x$kqfco	kernel query, fixed table columns
x$kqfta	kernel query, fixed table
x$kqfdt	kernel query, fixed table
x$kqfp	kernel query, fixed procedure	used in catprc.sql to build disk_and_fixed_objects view
x$kqfsz	kernel query, fixed size (size of fixed objects in current version of Oracle)
x$kqfvi	kernel query, fixed view
x$kqfvt	kernel query, fixed view table (how fixed view is built on fixed tables)
x$ksled, x$kslei, x$ksles	kernel service, event definition, events for instance, events for session, respectively (not sure about "l")
x$ksmfs	kernel service, memory fixed SGA	also contains db_block_buffers and log_buffer sizes for some reason
x$ksmfsv	kernel service, memory fixed SGA vectors	detailing fixed SGA
x$ksmjs	kernel service, memory java_pool summary
x$ksmlru	kernel service, memory LRU
x$ksmls	kernel service, memory large_pool summary
x$ksmmem	kernel service, memory	entire memory map
x$ksmpp	kernel service, memory process pool	PGA
x$ksmsd	kernel service, memory SGA definition
x$ksmsp	kernel service, memory shared pool
x$ksmspr	kernel service, memory shared pool reserved
x$ksmss	kernel service, memory shared_pool summary
x$ksmup	kernel service, memory user pool	UGA
x$ksqst	kernel service, enqueue status
x$ksulv	kernel service, user locale value
x$ksulop	kernel service, user long operation
x$ksupr	kernel service, user process
x$kzsro	kernel security, system role	used in many SQL scripts in ?/rdbms/admin
x$le	lock element
x$le_stat	lock element status

关于x$table的研究，可以看以下几个视图:

v$fixed_table ( where name like 'x$%')

v$fixed_view_definition (view_definition可以看到v$视图所用的x$table)

1.x$ksmsp (shared_pool的映象)

X$KSMSP的名称含义为:
[K]ernel [S]torage [M]emory Management [S]GA Hea[P]

addr	该chunk的地址
indx	该chunk的序号
inst_id	实例号
ksmchcom	该chunk的简要说明
ksmchsiz	该chunk的大小(byte)
ksmchptr	该chunk在内存的物理地址(16进制)
ksmchcls	该chunk的类型: perm : permanent分配，不能被free free : free内存 recr : recreatable(可以被free/flush) freeable: 可以被free的，但当前在使用，不准备用来被flush

我们关注以下几个字段:

KSMCHCOM是注释字段,每个内存块被分配以后，注释会添加在该字段中.
x$ksmsp.ksmchsiz代表块大小

x$ksmsp.ksmchcls列代表类型，主要有四类，说明如下:

free
Free chunks--不包含任何对象的chunk,可以不受限制的被分配.

recr
Recreatable chunks--包含可以被临时移出内存的对象,在需要的时候，这个对象可以
被重新创建.例如,许多存储共享sql代码的内存都是可以重建的.

freeabl
Freeable chunks--包含session周期或调用的对象,随后可以被释放.这部分内存有时候
可以全部或部分提前释放.但是注意，由于某些对象是中间过程产生的，这些对象不能
临时被移出内存(因为不可重建).

perm
Permanent memory chunks--包含永久对象.通常不能独立释放.

我们可以通过查询x$ksmsp视图来考察shared pool中存在的内存片的数量
不过注意:Oracle的某些版本(如:10.1.0.2)在某些平台上(如:HP-UX PA-RISC 64-bit)查
询该视图可能导致过度的CPU耗用,这是由于bug引起的.

2.x$ksmlru

[K]ernel Layer
[S]ervice Layer
[M]emory Management
[LRU]east recently used shared pool chunks flushes
from the shared pool

addr	该chunk的地址
indx	该chunk的序号
inst_id	实例号
ksmchcom	该chunk的简要说明
ksmchsiz	该chunk的大小(byte)
ksmlrnum	从shared_pool中flush出去的数目
ksmlrhon	被load的对象的名字
ksmlrohv	被load的对象的hash值
ksmlrses	执行这个分配的session(和v$session的saddr连接)

说明：

The contents of this view are ZEROED when it is queried and so it shows the largest allocations since the view was last queried.

The view can be useful when looking for the cause of memory allocations in the shared pool. It is quite normal for Oracle to flush chunks of memory from the shared pool to make space for new allocations. However, on heavily concurrent systems the shared pool can become a point of contention if SQL is not well shared. This view can help show sessions and statements requesting the largest memory allocations. The important thing to watch for is entries which are showing large values in KSMLRNUM as this indicates the number of items flushed to find a chunk of memory of size KSMLRSIZ. Under normal operation where SQL it not fully shared this table will show entries with KSMLRNUM set to 7 or 8 (as we flush in batches). If the figure is much higher than this then normal flushing is not finding space and is having to loop through the 'flush' code to find more space.

If the entries are always from the same session then it may be useful to see what the offending session is doing (eg: running a literal SQL script)

3.x$ksqst

数据库中各种lock的统计，下面的sql可以得出每种锁wait和get的次数：

4.x$kvis

数据库的块大小及最大可用大小

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-242135/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242135/