| 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的次数： SELECT ksqsttyp "Lock", ksqstget "Gets", ksqstwat "Waits" FROM X$KSQST WHERE KSQSTWAT>0; Lo Gets Waits
-- ---------- ----------
CF 618768 5
SQ 560909 141
ST 28493 51
TC 30 5
TX 13302940 5035
4.x$kvis 数据库的块大小及最大可用大小 SQL> select kvisval,kvisdsc from x$kvis;
KVISVAL KVISDSC
---------- ------------------------------------------
8192 size of physical block
8168 data area available in each physical block
|
