当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

为什么不要把用户表存储到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表空间过大,另一方面则是避免出现本文提到的性能问题。

本文固定链接: https://www.dbdream.com.cn/2012/07/%e4%b8%ba%e4%bb%80%e4%b9%88%e4%b8%8d%e8%a6%81%e6%8a%8a%e7%94%a8%e6%88%b7%e8%a1%a8%e5%ad%98%e5%82%a8%e5%88%b0system%e8%a1%a8%e7%a9%ba%e9%97%b4/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年07月25日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 为什么不要把用户表存储到SYSTEM表空间 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

为什么不要把用户表存储到SYSTEM表空间:目前有2 条留言

  1. Very useful blog. Keep up the good work.

    2012-08-01 21:49 [回复]
  2. I’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 [回复]

发表评论

快捷键:Ctrl+Enter