从执行效率上分析为什么SYSTEM不适宜存储业务数据

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

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/xifenfei
Connected.

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

Table created.

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

Table created.

SQL>  select table_name,tablespace_name from user_tables;   

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_XIFENFEI_U                   USERS
T_XIFENFEI_S                   SYSTEM

非系统表空间测试

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_xifenfei_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

系统表空间测试

SQL> begin
  2  for i in 1..200000 loop
  3  insert into t_xifenfei_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消耗还是执行时间上,系统表空间占用都是非系统表空间两倍以上

分析原因

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/xifenfei
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_xifenfei_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.

使用oradebug hang住某个进程

在一次测试中,需要模拟在归档模式下,数据库发生多次redo切换而这些redo并未被归档的情景,一般来说这样的情况只有在归档目录满的时候会遇到.但是在日常测试中,这样的归档目录满的模拟不太现实,可以通过oradebug SUSPEND来实现该功能,让arcn进程挂起
配置log_archive_max_processes为1(可以配置多个,但是1个更加方便测试)

SQL> show parameter log_archive_max_processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     1

该配置可以在线修改,但是不重启数据库不一定完全生效(测试环境本来是4,修改为1之后,还有arc0和arc1进程)

查找arcn进程

[oracle@localhost trace]$ ps -ef|grep ora_arc
oracle    3686     1  0 21:07 ?        00:00:00 ora_arc0_test

oradebug进程(session 1)

SQL> oradebug  setospid  3686
Oracle pid: 57, Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0)
SQL> oradebug SUSPEND
Statement processed.

alert日志

Tue Apr 16 21:09:42 2013
Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) flash frozen [ command #1 ]

切换日志(session 2)

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     53
Next log sequence to archive   55
Current log sequence           55
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter system checkpoint;

System altered.

SQL> set lines 134
SQL> col member for a40
SQL>   SELECT thread#,
  2           a.sequence#,a.ARCHIVED,
  3           a.group#,
  4           TO_CHAR (first_change#, '9999999999999999') "SCN",
  5           a.status,
  6           MEMBER
  7      FROM v$log a, v$logfile b
  8     WHERE a.group# = B.GROUP#
  9  ORDER BY a.sequence# DESC;

   THREAD#  SEQUENCE# ARC     GROUP# SCN               STATUS           MEMBER
---------- ---------- --- ---------- ----------------- ---------------- ----------------------------
         1         57 NO           3            261053 CURRENT          /data/oracle/oradata/test/redo03.log
         1         56 NO           2            261046 INACTIVE         /data/oracle/oradata/test/redo02.log
         1         55 NO           1            260856 INACTIVE         /data/oracle/oradata/test/redo01.log

SQL> alter system switch logfile;--hang住

此时alert日志

Tue Apr 16 21:10:19 2013
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 2 seq# 56 mem# 0: /data/oracle/oradata/test/redo02.log
Tue Apr 16 21:10:36 2013
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log

Tue Apr 16 21:13:13 2013
ORACLE Instance test - Can not allocate log, archival required
Thread 1 cannot allocate new log, sequence 58
All online logs needed archiving
  Current log# 3 seq# 57 mem# 0: /data/oracle/oradata/test/redo03.log

oradebug RESUME(session 1)

SQL> oradebug RESUME  
Statement processed.

alert日志

Tue Apr 16 21:14:23 2013
Unix process pid: 3686, image: oracle@localhost.localdomain (ARC0) resumed
Archived Log entry 2 added for thread 1 sequence 55 ID 0x7dd4ccb7 dest 1:
Archived Log entry 3 added for thread 1 sequence 56 ID 0x7dd4ccb7 dest 1:

hang住会话继续执行(session 2)

SQL> alter system switch logfile;

System altered.

ORA-600[2037]与ORA-07445[kcbs_dump_adv_state]错误

一台win oracle 数据库,重启后发现数据库无法访问,检查发现是Bug 4899479,但是oracle未提供完整的解决方法,这里根据自己对于数据库启动过程的理解,通过屏蔽前滚和回滚,拉起来数据库
数据库版本平台信息

ORACLE:11.1.0.7
OS:WIN 2008 R2 X64

数据库启动报错

Tue Apr 16 12:36:31 2013
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Started redo scan
Completed redo scan
 28878 redo blocks read, 7353 data blocks need recovery
Started redo application at
 Thread 1: logseq 7960, block 14132
Recovery of Online Redo Log: Thread 1 Group 1 Seq 7960 Reading mem 0
  Mem# 0: D:\APP\SDWLJG-DB101\ORADATA\WLJG\REDO01.LOG
Tue Apr 16 12:36:32 2013
RECOVERY OF THREAD 1 STUCK AT BLOCK 915068 OF FILE 9
Hex dump of (file 9, block 1698691) in trace file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p001_1500.trc
Corrupt block relative dba: 0x0259eb83 (file 9, block 1698691)
Bad header found during crash/instance recovery
Data in bad block:
 type: 0 format: 0 rdba: 0x0000a206
 last change scn: 0x2359.0259eb83 seq: 0xf7 flg: 0x0b
 spare1: 0x0 spare2: 0x0 spare3: 0x601
 consistency value in tail: 0x02c10243
 check value in block header: 0x0
 block checksum disabled
Reread of rdba: 0x0259eb83 (file 9, block 1698691) found valid data
Slave exiting with ORA-1172 exception
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p001_1500.trc:
ORA-01172: recovery of thread 1 stuck at block 915068 of file 9
ORA-01151: use media recovery to recover block, restore backup if needed
Tue Apr 16 12:36:32 2013
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p003_4088.trc  (incident=187558):
ORA-00600: internal error code, arguments: [2037], [12619645], [41474], [6], [1], [247], [12619645], [0], [], [], [], []
Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\incident\incdir_187558\wljg_p003_4088_i187558.trc
ORA-07445: exception encountered: core dump [kcbs_dump_adv_state()+1352] [ACCESS_VIOLATION]
 [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x16BFD20] [UNABLE_TO_READ] []
ORA-00600: internal error code, arguments: [2037], [12619645], [41474], [6], [1], [247], [12619645], [0], [], [], [], []
Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\incident\incdir_187559\wljg_p003_4088_i187559.trc
Errors in file c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p006_1216.trc  (incident=187567):

这里提示file 9 block 915068异常,但是通过dbv检查发现file 9无任何坏块.

trace文件内容

Dump continued from file: c:\app\sdwljg-db101\diag\rdbms\wljg\wljg\trace\wljg_p003_4088.trc
ORA-00600: internal error code, arguments: [2037], [12620930], [41474], [2], [1], [247], [12619645], [0], [], [], [], []

** DBGRL Error: ARB Alert Log
** DBGRL Error: <msg time='2013-04-16T11:05:58.522+08:00' org_id='oracle' comp_id='rdbms'
 msg_id='dbgexProcessError:1097:3370026720' type='TRACE' level='16'
 host_id='SDWLSCJG-DB' host_addr='172.18.1.15'>
 <txt>Incident details in: c:\app\sdwljg-db101\diag\rdbms\wljg\wlj
========= Dump for incident 129879 (ORA 600 [2037]) ========

*** 2013-04-16 11:05:58.522
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+111        CALL???  skdstdst()+0         000000000 000000000 01CFC9B80
                                                   000000200
ksedst()+63          CALL???  ksedst1()+0          000000005 021B00600 005D30C80
                                                   000002004
dbkedDefDump()+1012  CALL???  ksedst()+0           000000000 000000000 000000000
                                                   000000000
ksedmp()+51          CALL???  dbkedDefDump()+0     000000003 000000002 021AF92C0
                                                   000405038
__PGOSF184_ksfdmp()  CALL???  ksedmp()+0           000000000 000000000 000000000
+27                                                27F00000000
dbgexPhaseII()+266   CALL???  __PGOSF184_ksfdmp()  00000000D 0082FAE50 000000000
                              +0                   000000004
dbgexProcessError()  CALL???  dbgexPhaseII()+0     021B00600 021AFCA50 000000201
+1313                                              000000000
dbgeExecuteForError  CALL???  dbgexProcessError()  021B00600 021B07590 000000001
()+55                         +0                   000000000
dbgePostErrorKGE()+  CALL???  dbgeExecuteForError  021AFCA30 021AFCA80 00000002E
1608                          ()+0                 000000005
dbkePostKGE_kgsf()+  CALL???  dbgePostErrorKGE()+  01CFC99D0 021B0E080 000000258
65                            0                    021B0E080
kgeade()+556         CALL???  dbkePostKGE_kgsf()+  000002000 000000000 000000009
                              0                    000000004
kgeriv_int()+105     CALL???  kgeade()+0           3A4F00000003 000C09482
                                                   0FFFFFFFF 000000000
kgeriv()+27          CALL???  kgeriv_int()+0       3A9A024E0 000000000 01CFC9410
                                                   000000000
kgesiv()+102         CALL???  kgeriv()+0           0000008D5 0000008C3 021AFD9A0
                                                   000AFDC73
ksesic7()+125        CALL???  kgesiv()+0           006371F20 000000007 27F912000
                                                   200000004
kcoexam()+248        CALL???  ksesic7()+0          2000007F5 000000000 000C09482
                                                   000000000
kcbtema()+2154       CALL???  kcoexam()+0          27FFC22C8 39E113470 3A940BBB8
                                                   000000000
kcrpap()+355         CALL???  kcbtema()+0          27FFC22C8 28BFC2628 000000000
                                                   021B10200
kcrpdv()+1655        CALL???  kcrpap()+0           021B101A0 000000002 000000004
                                                   000000512
kxfprdp()+1384       CALL???  kcrpdv()+0           3A7AD3098 000000000 00000000C
                                                   00757CF00
opirip()+1396        CALL???  kxfprdp()+0          00000001E 005CDB518 021AFF9E0
                                                   000000000
opidrv()+855         CALL???  opirip()+0           000000032 000000004 021AFFD30
                                                   000000000
sou2o()+52           CALL???  opidrv()+213         000000032 000000004 021AFFD30
                                                   021AFFDB0
opimai_real()+295    CALL???  sou2o()+0            000000000 7FEFD9819B5
                                                   000000000 000000000
opimai()+96          CALL???  opimai_real()+0      000000000 000000000 000000000
                                                   000000000
BackgroundThreadSta  CALL???  opimai()+0           021AFFE98 000000001 000000000
rt()+695                                           000000000
00000000775AF56D     CALL???  BackgroundThreadSta  00A26B7A0 000000000 000000000
                              rt()+0               000000000
0000000077923281     CALL???  00000000775AF560     000000000 000000000 000000000
                                                   000000000
 
--------------------- Binary Stack Dump ---------------------

查询mos发现During Startup (Open Database) Alert Log Shows ORA-600[2037] and ORA-7445[kcbs_dump_adv_state] [ID 551993.1]和我们这里展示的错误相符,引起该问题的原因主要是因为:The database may crash and fail to open due to undo/redo corruption if you are using distributed transactions.因为使用分布式事务的时候,数据库crash导致undo/redo corruption,从而使得数据库无法正常启动.

故障处理思路
因为通过数据库alert日志可以知道,数据库是在做前滚的时候并发进程失败,设置fast_start_parallel_rollback=false,禁止数据库实例恢复并发,可以恢复依然失败.因为前滚过不去,那就通过设置隐含参数禁止数据库前滚,在open数据库的过程中发现ora-600[2662]错误,推进scn,继续open数据库发现ora-600[4194],通过设置undo管理模式,屏蔽事务,屏蔽回滚段等方法,终于重新open库并重建undo,然后重建库算是完成恢复任务

ORACLE 12C TOP N SQL实现分页功能

从oracle 12c开始,oracle 也提供了类似sql server的top,mysql的limit分页功能,在本文中分别通过TOP N和传统方法来实现分页,sql实现效果是(按照id 倒序排列,取第六条到底十条)
TOP N写法

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> set autot on exp stat
SQL> SELECT id
  2  FROM t_xifenfei
  3  ORDER BY id  desc offset 5 rows FETCH next 5 ROWS ONLY;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 755690401

---------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                    |            |   192 |  7488 |     3   (0)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|            |   192 |   768 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE  WHEN (5>=0)
              THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ID") DESC
              )<=CASE  WHEN (5>=0) THEN 5 ELSE 0 END +5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

传统分页写法

SQL> select id from (select id,rownum rn from (
  2  select id  from t_xifenfei order by id desc
  3  )) where rn<11 and rn>=6;

        ID
----------
       188
       187
       186
       185
       184


Execution Plan
----------------------------------------------------------
Plan hash value: 327151993

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   192 |  4992 |     3   (0)| 00:00:01 |
|*  1 |  VIEW                 |            |   192 |  4992 |     3   (0)| 00:00:01 |
|   2 |   COUNT               |            |       |       |            |          |
|   3 |    VIEW               |            |   192 |  2496 |     3   (0)| 00:00:01 |
|   4 |     SORT ORDER BY     |            |   192 |   768 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| T_XIFENFEI |   192 |   768 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN">=6 AND "RN"<11)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        619  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

从这里可以看出来两种sql分页写法,在小数据量上效率都差不多,但是明显TOP N的写法更加简单,更加灵活.如果数据量大可能还是需要自己写分页SQL。TOP N是通过ROW_NUMBER() OVER INTERNAL_FUNCTION(“ID”)和CASE WHEN内部转换实现分页功能.