sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle表空间 数据存放system表空间影响数据库性能

时间:2016-07-02 22:04   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 分享一篇,关于Oracle数据库system表空间研究,不能将用户数据存放在system表空间的原因

为什么不建议客户把业务数据存放到SYSTEM表空间中,一直想通过试验的数据来说明问题,今天见老熊的邮件和同事的blog来说明把业务数据存放在SYSTEM表空间中效率的影响

1.查询Oracle数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建测试环境

SQL> conn chf/oracleplus
Connected.

SQL> create table t_oracleplus_u(id number) tablespace users;

Table created.

SQL> create table t_oracleplus_s(id number) tablespace system;

Table created.

SQL>  select table_name,tablespace_name from user_tables;   

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_oracleplus_U                   USERS
T_oracleplus_S                   SYSTEM

2.非系统表空间测试

SQL> select STATISTIC#,NAME from v$statname where name='CPU used by this session';

STATISTIC# NAME
---------- ----------------------------------------------------------------
        17 CPU used by this session

SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17         33

SQL> set timing on
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_oracleplus_u values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.97
SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17        629

Elapsed: 00:00:00.00

测试结果显示,非系统表空间中的表插入200000条记录,使用时间为5.97秒;使用CPU为629-33=596

3.系统表空间测试

SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_oracleplus_s values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.00
SQL> select * from v$mystat where STATISTIC#=17;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       189         17       2019

Elapsed: 00:00:00.00

测试结果显示,对系统表空间中的表插入200000条记录,使用时间为14秒;使用CPU为2019-629=1390,基本上可以看出来无论是CPU消耗还是执行时间上,系统表空间占用都是非系统表空间两倍以上

4.影响Oracle性能分析原因

SQL> conn / as sysdba
Connected.

SQL> select * from (SELECT i.ksppinm NAME, i.ksppity TYPE, v.ksppstvl VALUE, 
  2  v.ksppstdf isdefault FROM x$ksppi i, x$ksppcv v WHERE i.indx = v.indx AND 
  3  i.ksppinm LIKE '/_%%' ESCAPE '/') where name like '%db_alw%';

NAME                                 TYPE VALUE           ISDEFAULT
------------------------------ ---------- --------------- ---------
_db_always_check_system_ts              1 TRUE            TRUE

SQL> alter system set "_db_always_check_system_ts"=false;

System altered.

SQL> conn chf/oracleplus
Connected.

SQL>  select * from v$mystat where STATISTIC#=17;   

       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17          1

Elapsed: 00:00:00.01
SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_oracleplus_s values(i);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.03

SQL> select * from v$mystat where STATISTIC#=17;   

       SID STATISTIC#      VALUE
---------- ---------- ----------
       127         17        582

通过这里可以发现,修改_db_always_check_system_ts=false之后,system表空间的操作基本上和非系统表空间所差无几(执行时间6.01秒,占用CPU 581=582-1)
在数据库默认情况下db_block_checking和db_block_checksum的值无论如何设置都不能对于SYSTEM表空间生效,也就是说SYSTEM表空间在没有修改_db_always_check_system_ts=false之前,对所有的块操作都要进行db_block_checking和db_block_checksum验证,从而使得数据块的操作效率较非SYSTEM表空间低下。对于一些插入较为频繁的aud$、FGA_LOG$、DEF$_AQCALL等表建议迁移到其他表空间

备注说明
DB_BLOCK_CHECKING

DB_BLOCK_CHECKING specifies whether or not Oracle performs block checking for database blocks.

Values:

OFF or FALSE

No block checking is performed for blocks in user tablespaces. However,
semantic block checking for SYSTEM tablespace blocks is always turned on.

LOW

Basic block header checks are performed after block contents change in memory 
(for example, after UPDATE or INSERT statements, on-disk reads, or 
inter-instance block transfers in Oracle RAC).

MEDIUM

All LOW checks and full semantic checks are performed for all objects except indexes 
(whose contents can be reconstructed by a drop+rebuild on encountering a corruption).

FULL or TRUE

All LOW and MEDIUM checks and full semantic checks are performed for all objects.

Oracle checks a block by going through the data in the block, making sure it is logically 
self-consistent. Block checking can often prevent memory and data corruption. Block checking 
typically causes 1% to 10% overhead,depending on workload and the parameter value. 
The more updates or inserts in a workload, the more expensive it is to turn on block checking.
You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable.

For backward compatibility, the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

DB_BLOCK_CHECKSUM

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum 
(a number calculated from all the bytes stored in the block) and store it in the cache header 
of every data block when writing it to disk. Checksums are verified when a block is read - 
only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. 
In FULL mode, Oracle also verifies the checksum before a change application from update/delete 
statements and recomputes it after the change is applied. In addition, Oracle gives every log block
a checksum before writing it to the current log.

Starting with Oracle Database 11g, most of the log block checksum is done by the generating foreground 
processes, while the LGWR performs the rest of the work, for better CPU and cache efficiency. 
Prior to Oracle Database 11g, the LGWR solely performed the log block checksum.

If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM tablespace, 
but not for user tablespaces. In addition, no log checksum is performed when this parameter is set to OFF.

Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. 
If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk.
Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes
4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL.

For backward compatibility the use of TRUE (implying TYPICAL) and FALSE (implying OFF) values is preserved.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle表空间 数据存放system表空间影响数据库性能

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/685.html

Oracle研究中心

关键词:

Oracle表空间

数据存放system表空间对数据库的影响

Oracle system表空间不能存储用户数据