数据字典(Data Dictionary)是Oracle数据库的一个重要组成部分,是元数据(MetaData)的存储地点。
Oracle通过数据字典记录管理对象及安全信息,用户和数据库管理员可以通过数据字典来获取数据库相关信息。
数据库字典包括以下内容:
所有数据库Schema的定义(TABLE、VIEW、INDEX、CLUSTER、SYNONYM、SEQUENCE、PROCEDURE、FUNCTION、PACKAGE、TRIGGER );
数据库空间分配和使用情况;
字段缺失值;
完整性约束;
Oracle用户名称、角色、权限;
审计信息;
其它数据库信息;
一般来说,数据字典是数据库的核心,是只读的,不建议对任何数据字典表进行手工更改
对于Oracle,我们所说的数据字典由4部分组成:内部RDBMS(X$)表、数据字典表、动态视图(V$)和数据字典视图。
内部RDBMS(X$)表
X$表示Oracle数据的核心部分,这些表用于跟踪内部数据信息,维持数据库正常运行。X$表的信息在官方找不到相应的文档,ORACLE的大量性嫩视图都是通过这些X$表建立出来的,由于X$表的重要性,Oracle不允许SYSDBA之外的用户直接访问。
SQL> grant select on x$ksppi to sss; grant select on x$ksppi to sss * ERROR at line 1: ORA-02030: can only select from fixed tables/views
查看报错:
[oracle@021Y-SH-BKAP ~]$ oerr ora 02030 02030, 00000, "can only select from fixed tables/views" // *Cause: An attempt is being made to perform an operation other than // a retrieval from a fixed table/view. // *Action: You may only select rows from fixed tables/views.
解释说明固定表或视图只能查询,不能进行操作。
要查看X$视图的最好办法就是通过Oracle提供的AUTOTRACE功能,当查询某些视图时,可以通过AUTOTRACE追踪到访问了哪些X$表。
AUTOTRACE的语法:
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
开启该功能进行语句查询:
SQL> set autot trace explain SQL> select * from v$parameter where rownum < 5; Execution Plan ---------------------------------------------------------- Plan hash value: 2725466811 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4414 | 1 (100)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | |* 2 | HASH JOIN | | 1 | 4414 | 1 (100)| 00:00:01 | |* 3 | FIXED TABLE FULL| X$KSPPI | 1 | 249 | 0 (0)| 00:00:01 | | 4 | FIXED TABLE FULL| X$KSPPCV | 100 | 406K| 0 (0)| 00:00:01 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<5) 2 - access("X"."INDX"="Y"."INDX") filter(TRANSLATE("KSPPINM",'_','#') NOT LIKE '#%' OR "KSPPSTDF"='FALSE' OR BITAND("KSPPSTVF",5)>0) 3 - filter("X"."INST_ID"=USERENV('INSTANCE') AND BITAND("KSPPIFLG",268435456)=0 AND TRANSLATE("KSPPINM",'_','#') NO T LIKE '##%')
可以看到查询视图V$PARAMETER时,访问了两张固定表X$KSPPI和X$KSPPCV。
KSPPI [ Kernel layer Service layer Parameter Component Parameter Info]
KSPPCV [ Kernel layer Service layer Parameter Component Current session Values]
书中介绍了一个固定表X$KVIT[ Trascation layer Performance layer Information tables Transitory Instance parameter]
SQL> col KVITTAG for a16 SQL> col KVITDSC for a60 SQL> set line 300 SQL> set pagesize 300 SQL> select inst_id,kvittag,kvitval,kvitdsc from x$kvit; INST_ID KVITTAG KVITVAL KVITDSC ---------- ------------------ ---------- ------------------------------------------------------------ 1 ksbcpu 8 number of logical CPUs in the system used by Oracle 1 ksbcpucore 8 number of physical CPU cores in the system used by Oracle 1 ksbcpusocket 2 number of physical CPU sockets in the system used by Oracle 1 ksbcpu_hwm 8 high water mark of number of CPUs used by Oracle 1 ksbcpucore_hwm 8 high water mark of number of CPU cores on system 1 ksbcpusocket_hwm 2 high water mark of number of CPU sockets on system 1 ksbcpu_actual 8 number of available CPUs in the system 1 ksbcpu_dr 1 CPU dynamic reconfiguration supported 1 kcbnbh 339227 number of buffers 1 kcbldq 25 large dirty queue if kcbclw reaches this 1 kcbfsp 40 Max percentage of LRU list foreground can scan for free 1 kcbcln 2 Initial percentage of LRU list to keep clean 1 kcbnbf 750 number buffer objects 1 kcbwst 0 Flag that indicates recovery or db suspension 1 kcteln 0 Error Log Number for thread open 1 kcvgcw 0 SGA: opcode for checkpoint cross-instance call 1 kcvgcw 0 SGA:opcode for pq checkpoint cross-instance call
触发后台进程DBWR写动作的两个条件
条件1:达到脏缓存阀值(Dirty Buffers threshold),这个阀值为多少,在X$KVIT表中体现。
1 kcbldq 25 large dirty queue if kcbclw reaches this
条件2:No free buffer,即在扫描LRU列表一定的数量块后,如果找不到足够的空闲空间,则触发DBWR
1 kcbfsp 40 Max percentage of LRU list foreground can scan for free
即是说当脏缓存达到25%或扫描40%的LRU list后发现没有free space的情况下,均触发DBWR回写数据。
而这些数值都是Oracle内部定义的,无法通过系统参数进行修改