问题背景：

客户数据库宕机，需要排查数据库宕机原因

1> 查看alert日志

Wed Nov 13 22:00:11 2019

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc  (incident=73515):

ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []

Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc

Wed Nov 13 22:00:14 2019

Trace dumping is performing id=[cdmp_20191113220014]

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc  (incident=73516):

ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []

Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc

Wed Nov 13 22:00:15 2019

Sweep [inc][73516]: completed

Sweep [inc][73515]: completed

Sweep [inc2][73515]: completed

Trace dumping is performing id=[cdmp_20191113220018]

Wed Nov 13 22:00:21 2019

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc  (incident=72019):

ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []

Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72019/orcl_pmon_2510_i72019.trc

Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc:

ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], []

PMON (ospid: 2510): terminating the instance due to error 472

Instance terminated by PMON, pid = 2510

Thu Nov 14 08:04:36 2019

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on. 

2> 数据库遭遇了ORA-00600的错误，

继续查看 

Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc

Dump file /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1

System name: Linux

Node name: OA-DBSVR

Release: 3.10.0-123.el7.x86_64

Version: #1 SMP Mon May 5 11:16:57 EDT 2014

Machine: x86_64

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 189

Unix process pid: 26935, image: oracle@OA-DBSVR (J004)

*** 2019-11-13 22:00:11.667

*** SESSION ID:(437.12455) 2019-11-13 22:00:11.667

*** CLIENT ID:() 2019-11-13 22:00:11.667

*** SERVICE NAME:(SYS$USERS) 2019-11-13 22:00:11.667

*** MODULE NAME:(DBMS_SCHEDULER) 2019-11-13 22:00:11.667

*** ACTION NAME:(ORA$AT_SA_SPC_SY_8162) 2019-11-13 22:00:11.667

Dump continued from file: /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc

ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], []

========= Dump for incident 73515 (ORA 600 [kcbo_unlink_q_3]) ========

*** 2019-11-13 22:00:11.669

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=8szmwam7fysa3) -----

insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_alloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,  NULL, 'FALSE', :5, 'FALSE'))

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

  object      line  object

  handle    number  name

insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, space_alloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL,  NULL, 'FALSE', :5, 'FALSE'))

这条sql引发了数据库ORA-00600错误

tkprof orcl_j004_26935_i73516.trc orcl_j004_26935_i73516.txt

内容如下

[oracle@OA-DBSVR incdir_73515]$ cat orcl_j004_26935_i73515.txt 

TKPROF: Release 11.2.0.1.0 - Development on Thu Nov 14 15:28:37 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_j004_26935_i73515.trc

Sort options: default

********************************************************************************

count    = number of times OCI procedure was executed

cpu      = cpu time in seconds executing 

elapsed  = elapsed time in seconds executing

disk     = number of physical reads of buffers from disk

query    = number of buffers gotten for consistent read

current  = number of buffers gotten in current mode (usually for update)

rows     = number of rows processed by the fetch or execute call

********************************************************************************

Trace file: orcl_j004_26935_i73515.trc

Trace file compatibility: 11.1.0.7

Sort options: default

       1  session in tracefile.

       0  user  SQL statements in trace file.

       0  internal SQL statements in trace file.

       0  SQL statements in trace file.

       0  unique SQL statements in trace file.

  160288  lines in trace file.

       0  elapsed seconds in trace file.

3> 查询一下这条sql是做什么用的wri$_adv_objspace_trend_data

insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))

自动SQL调整顾问：自动标识并尝试调整高负载的SQL，任务名是“sqltuning advisor”。

也花费了大量时间这显然是不正常的。

在Metalink上存在如下一个Bug：

Bug 5376783: DBMS_SPACE.OBJECT_GROWTH_TREND CALL TAKES A LOT OF DISK READS

这个Bug在DBMS_SPACE.OBJECT_GROWTH_TREND进行空间分析时被触发，根本原因在于内部算法在执行空间检查时，耗费了大量的评估IO成本，导致了大量的IO资源使用：

在后台跟踪里可以看到这个步骤的资源消耗：

insert into wri$_adv_objspace_trend_data select timepoint,  space_usage, 

  space_alloc, quality from  table(dbms_space.object_growth_trend(:1, :2, :3, 

  :4, NULL, NULL,  NULL, 'FALSE', :5, 'FALSE'))

临时的处理办法是，暂时关闭这个自动任务：

execute dbms_scheduler.disable('sys.auto_space_advisor_job'); 

这个Bug在10.2.0.2之后的版本中被修正。记录一下供参考！

-The End-