为什么不要把用户表存储到SYSTEM表空间
平时只知道不要把非系统用户的表存放到系统表空间,至于为什么,并没有去研究,直到看到kamus(张乐奕)和老熊(熊军)发起的邮件才知道,原来系统对SYSTEM表空间的自动维护会占用CPU资源,如果将普通用户的表存放到系统表空间,效率会下降,下面是熊军的测试案例,我拿到我的数据库(11.2.0.2.0 for linux 64bit)测试过程和结果如下:
1.创建两张测试表,一张存放到USERS表空间,一张存放到SYSTEM表空间
SQL> create table t_users(id number); Table created. SQL> create table t_system(id number); Table created. SQL> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ---------- --------------- T_USERS USERS T_SYSTEM SYSTEM
2.查看当前CPU消耗
SQL> select STATISTIC#,NAME from v$statname where name='CPU used by this session'; STATISTIC# NAME ---------- ------------------------ 16 CPU used by this session SQL> select * from v$mystat where STATISTIC#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 1608
3.向T_USERS表插入数据,查看CPU消耗
SQL> set timing on SQL> begin 2 for i in 1..100000 loop 3 insert into t_users values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.83 SQL> select * from v$mystat where STATISTIC#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 1988
4.向T_SYSTEM表插入数据,查看CPU消耗
SQL> begin 2 for i in 1..100000 loop 3 insert into t_system values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:11.41 SQL> select * from v$mystat where STATISTIC#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 3127
可见,对存放到非系统表空间的表做同样的INSERT操作,存放在USERS表空间的表只需要执行3秒83和1988-1608=380的CPU,而存放到SYSTEM表空间的表却要执行11秒41和3127-1988=1139的CPU, 为什么会这样?请看下面两个参数。
SQL> show parameter db_block_check NAME TYPE VALUE ----------------- ----------- ------------ db_block_checking string FALSE db_block_checksum string TYPICAL
db_block_checking参数对block进行逻辑校验,数据库发生update,insert等涉及数据块发生改变的操作后,db_block_checking参数验证每个数据块内部或相关几个块逻辑的完整性,该参数有六个值:OFF,LOW,MEDIUM,FULL,TRUE,FALSE,这些参数含义如下:
LOW:基本的数据块header checks
MEDIUM:数据块内部的全面检测,(不包含index organized table blocks)
FULL:对数据库中所有的块进行全面检测(包括index block)
OFF: 禁用DB_BLOCK_CHECKING功能(SYSTEM tablespace除外,详见下文的隐含参数)
true,false是为了向后兼容保留的,true=FULL,false=OFF)
db_block_checksum参数对数据块进行物理校验,在block写入磁盘和读出磁盘时,oracle会对每个block生成一个校验码,并和block header中校验码进行比对,校验数据块的一致性.此外该参数也对log block执行同样的检测,此外11G中对log block的检测有所细化,该参数有五个值:OFF,FULL,TYPICAL,TRUE,FALSE,这些参数含义如下:
OFF:禁用DB_BLOCK_CHECKSUM功能(SYSTEM tablespace除外,详见下文隐含参数)
TYPICAL:数据块读出和写入时对数据块进行物理校验(默认值)
FULL: 除了TYPEICAL的功能外,还可以对内存中每个BLOCK发生变化(insert,update)的前后进行校验,但这个功能并不能代替DB_BLOCK_CHECKING参数的功能.
true,false是为了向后兼容保留的,true=FULL,false=OFF)
无论db_block_checking和db_block_checksum这两个参数的值为何值,SYSTEM 表空间都会执行block checks.不受上述参数影响,跟隐含参数_db_always_check_system_ts有关,该参数默认为TRUE。
SQL> select * from (SELECT i.ksppinm NAME, i.ksppity TYPE, v.ksppstvl VALUE, v.ksppstdf isdefault FROM x$ksppi i, x$ksppcv v WHERE i.indx = v.indx AND i.ksppinm LIKE '/_%%' ESCAPE '/') where name like '%db_alw%'; NAME TYPE VALUE ISDEFAULT ------------------------------ ---------- -------------------- --------- _db_always_check_system_ts 1 TRUE TRUE
下面通过简单的测试,来验证下_db_always_check_system_ts隐含参数多SYSTEM表空间block checks的影响:
SQL> alter system set "_db_always_check_system_ts"=false; System altered. SQL> select * from v$mystat where statistic#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 34 SQL> begin 2 for i in 1..100000 loop 3 insert into t_users values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.75 SQL> select * from v$mystat where statistic#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 374 Elapsed: 00:00:00.01 SQL> begin 2 for i in 1..100000 loop 3 insert into t_system values(i); 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:03.78 SQL> select * from v$mystat where statistic#=16; SID STATISTIC# VALUE ---------- ---------- ---------- 12 16 749
可见,禁用_db_always_check_system_ts隐含参数后,两个SQL执行时间和CPU时间消耗几乎一样了,T_USERS表执行了3秒75,CPU消耗374-34=340,T_SYSTEM表执行了3秒78,CPU时间消耗了749-374=375,把隐含参数_db_always_check_system_ts设置为FALSE,可以减少SYSTEM表空间的block checks,当然为了SYSTEM表空间数据安全,不建议将这个隐含参数值设置为FALSE,因此,最好不要将用户表和索引放到SYSTEM空间中。
另外在启用一起特定的功能后,SYSTEM表空间中一些表和索引会增长很快。比如启用了审计并且将审计日志存储到数据库中,则AUD$和FGA_LOG$会迅速增长;如果使用高级复制,DEF$_AQCALL表会增加很快,并且如果要复制的数据量比较大,则这个表上的DML是非常多的,在这样的情况会下,会消耗更多的CPU和引起性能降低。如果使用了审计和高级复制,建议将AUD$、FGA_LOG$、DEF$_AQCALL迁移到其他表空间,一方面避免产生大量数据使得SYSTEM表空间过大,另一方面则是避免出现本文提到的性能问题。
Very useful blog. Keep up the good work.
2012-08-01 21:49I’m typically to blogging and i actually recognize your content. The article has really peaks my interest. I’m going to bookmark your site and hold checking for brand spanking new information.
2012-08-01 22:59