问题背景：
客户数据库宕机，需要排查数据库宕机原因
1> 查看alert日志
1 Wed Nov 13 22:00:11 2019 2 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc (incident=73515): 3 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], [] 4 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc 5 Wed Nov 13 22:00:14 2019 6 Trace dumping is performing id=[cdmp_20191113220014] 7 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc (incident=73516): 8 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], [] 9 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], [] 10 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc 11 Wed Nov 13 22:00:15 2019 12 Sweep [inc][73516]: completed 13 Sweep [inc][73515]: completed 14 Sweep [inc2][73515]: completed 15 Trace dumping is performing id=[cdmp_20191113220018] 16 Wed Nov 13 22:00:21 2019 17 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc (incident=72019): 18 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], [] 19 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72019/orcl_pmon_2510_i72019.trc 20 Errors in file /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_pmon_2510.trc: 21 ORA-00600: internal error code, arguments: [1100], [0x3710B87E0], [0x3710B87E0], [], [], [], [], [], [], [], [], [] 22 PMON (ospid: 2510): terminating the instance due to error 472 23 Instance terminated by PMON, pid = 2510 24 Thu Nov 14 08:04:36 2019 25 Starting ORACLE instance (normal) 26 LICENSE_MAX_SESSION = 0 27 LICENSE_SESSIONS_WARNING = 0 28 Picked latch-free SCN scheme 3 29 Autotune of undo retention is turned on.
2> 数据库遭遇了ORA-00600的错误，
继续查看
1 Incident details in: /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73516/orcl_j004_26935_i73516.trc 2 3 Dump file /oracle/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_73515/orcl_j004_26935_i73515.trc 4 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 5 With the Partitioning, OLAP, Data Mining and Real Application Testing options 6 ORACLE_HOME = /oracle/app/oracle/product/11.2.0/db_1 7 System name: Linux 8 Node name: OA-DBSVR 9 Release: 3.10.0-123.el7.x86_64 10 Version: #1 SMP Mon May 5 11:16:57 EDT 2014 11 Machine: x86_64 12 Instance name: orcl 13 Redo thread mounted by this instance: 1 14 Oracle process number: 189 15 Unix process pid: 26935, image: oracle@OA-DBSVR (J004) 16 17 18 *** 2019-11-13 22:00:11.667 19 *** SESSION ID:(437.12455) 2019-11-13 22:00:11.667 20 *** CLIENT ID:() 2019-11-13 22:00:11.667 21 *** SERVICE NAME:(SYS$USERS) 2019-11-13 22:00:11.667 22 *** MODULE NAME:(DBMS_SCHEDULER) 2019-11-13 22:00:11.667 23 *** ACTION NAME:(ORA$AT_SA_SPC_SY_8162) 2019-11-13 22:00:11.667 24 25 Dump continued from file: /oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j004_26935.trc 26 ORA-00600: internal error code, arguments: [kcbo_unlink_q_3], [0x3710B87B0], [1], [0], [1], [0], [0], [0], [], [], [], [] 27 28 ========= Dump for incident 73515 (ORA 600 [kcbo_unlink_q_3]) ======== 29 30 *** 2019-11-13 22:00:11.669 31 dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0) 32 ----- Current SQL Statement for this session (sql_id=8szmwam7fysa3) ----- 33 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')) 34 ----- PL/SQL Stack ----- 35 ----- PL/SQL Call Stack ----- 36 object line object 37 handle number name 38 39 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')) 40 这条sql引发了数据库ORA-00600错误 41 42 tkprof orcl_j004_26935_i73516.trc orcl_j004_26935_i73516.txt 43 内容如下 44 [oracle@OA-DBSVR incdir_73515]$ cat orcl_j004_26935_i73515.txt 45 46 TKPROF: Release 11.2.0.1.0 - Development on Thu Nov 14 15:28:37 2019 47 48 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 49 50 Trace file: orcl_j004_26935_i73515.trc 51 Sort options: default 52 53 ******************************************************************************** 54 count = number of times OCI procedure was executed 55 cpu = cpu time in seconds executing 56 elapsed = elapsed time in seconds executing 57 disk = number of physical reads of buffers from disk 58 query = number of buffers gotten for consistent read 59 current = number of buffers gotten in current mode (usually for update) 60 rows = number of rows processed by the fetch or execute call 61 ******************************************************************************** 62 Trace file: orcl_j004_26935_i73515.trc 63 Trace file compatibility: 11.1.0.7 64 Sort options: default 65 66 1 session in tracefile. 67 0 user SQL statements in trace file. 68 0 internal SQL statements in trace file. 69 0 SQL statements in trace file. 70 0 unique SQL statements in trace file. 71 160288 lines in trace file. 72 0 elapsed seconds in trace file.
3> 查询一下这条sql是做什么用的wri$_adv_objspace_trend_data
1 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资源使用：
在后台跟踪里可以看到这个步骤的资源消耗：
1 insert into wri$_adv_objspace_trend_data select timepoint, space_usage, 2 space_alloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, 3 :4, NULL, NULL, NULL, 'FALSE', :5, 'FALSE'))
临时的处理办法是，暂时关闭这个自动任务：
1 execute dbms_scheduler.disable('sys.auto_space_advisor_job');
这个Bug在10.2.0.2之后的版本中被修正。记录一下供参考！
-The End-