[20211111]奇怪的ashtop输出.txt

[20211111]奇怪的ashtop输出.txt --//看了生产系统ashtop输出，感觉很奇怪，不知道那里出了问题，简单探究看看。 1.环境: XXXX> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.ashtop输出： XXXX> @ ashtop event session_type='FOREGROUND'  trunc(sysdate)+9/24 trunc(sysdate)+10/24     Total   Seconds     AAS %This   EVENT                                    FIRST_SEEN          LAST_SEEN --------- ------- ------- ---------------------------------------- ------------------- -------------------       689      .2   62% |                                          2021-11-11 09:00:20 2021-11-11 09:59:04       197      .1   18% | direct path read                         2021-11-11 09:00:23 2021-11-11 09:59:43       113      .0   10% | cell single block physical read          2021-11-11 09:01:14 2021-11-11 09:59:26        23      .0    2% | control file sequential read             2021-11-11 09:03:02 2021-11-11 09:58:06        19      .0    2% | direct path write                        2021-11-11 09:04:23 2021-11-11 09:59:05        18      .0    2% | log file sync                            2021-11-11 09:00:02 2021-11-11 09:59:36         7      .0    1% | Disk file Mirror Read                    2021-11-11 09:03:05 2021-11-11 09:52:55         5      .0    0% | SQL*Net break/reset to client            2021-11-11 09:08:36 2021-11-11 09:51:51         5      .0    0% | SQL*Net more data to client              2021-11-11 09:01:24 2021-11-11 09:59:28         5      .0    0% | enq: PS - contention                     2021-11-11 09:03:07 2021-11-11 09:36:38         5      .0    0% | null event                               2021-11-11 09:03:16 2021-11-11 09:39:06         3      .0    0% | PX Deq: Slave Session Stats              2021-11-11 09:14:08 2021-11-11 09:59:03         3      .0    0% | gc cr block 2-way                        2021-11-11 09:03:10 2021-11-11 09:39:18         3      .0    0% | gc current block 2-way                   2021-11-11 09:16:27 2021-11-11 09:39:18         2      .0    0% | IPC send completion sync                 2021-11-11 09:24:01 2021-11-11 09:54:06         2      .0    0% | SQL*Net more data from client            2021-11-11 09:02:36 2021-11-11 09:34:49         2      .0    0% | enq: HW - contention                     2021-11-11 09:11:33 2021-11-11 09:47:37         1      .0    0% | enq: FB - contention                     2021-11-11 09:11:08 2021-11-11 09:11:08         1      .0    0% | gc cr request                            2021-11-11 09:03:10 2021-11-11 09:03:10         1      .0    0% | gc current grant busy                    2021-11-11 09:01:50 2021-11-11 09:01:50         1      .0    0% | gc current multi block request           2021-11-11 09:42:11 2021-11-11 09:42:11 21 rows selected. --//我们以前db time最少几个小时以上的，怎么突然变得这么好，问题出在那里。 XXXX> @ dashtop event session_type='FOREGROUND'  trunc(sysdate)+9/24 trunc(sysdate)+10/24                                                     Total %This  EVENT                                      Seconds FIRST_SEEN          LAST_SEEN ------ ---------------------------------------- --------- ------------------- -------------------   89%                                               42340 2021-11-11 09:00:05 2021-11-11 09:59:56    6%  cell single block physical read               2660 2021-11-11 09:00:05 2021-11-11 09:59:56    2%  direct path write temp                         830 2021-11-11 09:00:16 2021-11-11 09:59:56    1%  log file sync                                  480 2021-11-11 09:00:02 2021-11-11 09:58:14    1%  SQL*Net more data to client                    360 2021-11-11 09:00:56 2021-11-11 09:59:28    1%  direct path read                               260 2021-11-11 09:00:23 2021-11-11 09:58:27    0%  cell list of blocks physical read              170 2021-11-11 09:07:22 2021-11-11 09:59:25    0%  null event                                     110 2021-11-11 09:00:56 2021-11-11 09:59:15    0%  SQL*Net break/reset to client                   70 2021-11-11 09:17:10 2021-11-11 09:58:55    0%  control file sequential read                    40 2021-11-11 09:05:50 2021-11-11 09:50:12    0%  gc cr multi block request                       40 2021-11-11 09:12:36 2021-11-11 09:43:52    0%  gc current block 2-way                          40 2021-11-11 09:13:47 2021-11-11 09:40:40    0%  cell multiblock physical read                   30 2021-11-11 09:19:22 2021-11-11 09:59:45    0%  direct path write                               30 2021-11-11 09:17:32 2021-11-11 09:55:35    0%  SQL*Net more data from client                   20 2021-11-11 09:18:51 2021-11-11 09:41:20    0%  cursor: pin S                                   20 2021-11-11 09:41:41 2021-11-11 09:41:41    0%  Disk file Mirror Read                           10 2021-11-11 09:35:45 2021-11-11 09:35:45    0%  Disk file operations I/O                        10 2021-11-11 09:12:06 2021-11-11 09:12:06    0%  cell smart table scan                           10 2021-11-11 09:21:04 2021-11-11 09:21:04    0%  cursor: pin S wait on X                         10 2021-11-11 09:52:40 2021-11-11 09:52:40    0%  gc cr block 2-way                               10 2021-11-11 09:18:03 2021-11-11 09:18:03    0%  gc current grant busy                           10 2021-11-11 09:42:11 2021-11-11 09:42:11    0%  read by other session                           10 2021-11-11 09:42:51 2021-11-11 09:42:51 23 rows selected. --//可以发现dashtop才是比较真实的显示结果。问题出在那里呢？ --//42340/3600 = 11.76. XXXX> SELECT count(*),inst_id FROM gV$ACTIVE_SESSION_HISTORY  WHERE sample_time BETWEEN                               trunc(sysdate)+9/24 and trunc(sysdate)+10/24 group by inst_id ;   COUNT(*)    INST_ID ---------- ----------       1417          2 --//实际上9－10点的信息在其中一个实例已经不存在gV$ACTIVE_SESSION_HISTORY视图中，导致出现不对的结果。 --//当前时间： XXXX> select sysdate from dual ; SYSDATE ------------------- 2021-11-11 16:39:55 --//实际上 Active Session History buffers 信息受隐含参数_ash_size控制。 XXXX> @ pd _ash_size Show all parameters and session values from x$ksppi/x$ksppcv...       INDX I_HEX NAME                          VALUE    DESCRIPTION ---------- ----- ----------------------------- -------- ----------------------------------------------------------------------       2647   A57 _olap_dimension_corehash_size 30       OLAP Dimension In-Core Hash Table Maximum Memory Use       2731   AAB _kffmap_hash_size             1024     size of kffmap_hash table       2732   AAC _kffmop_hash_size             2048     size of kffmop_hash table       2840   B18 _ash_size                     1048618  To set the size of the in-memory Active Session History buffers --//缺省1048618 大约1M，如果产生信息太多，最早产生的信息将被清除出去。我们生产系统估计一个实例太忙，无法保存太多ASH信息。 --//出现ashtop输出的奇怪现象，以后工作注意。