[20230511]最佳化的困惑17.txt

[20230511]最佳化的困惑17.txt

1.環境:
SYS@192.168.100.237:1521/orcldg> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.
--//注:伺服器是一臺dataguard資料庫,平時也作為查詢..

2.問題提出:
SYS@192.168.100.237:1521/orcldg> @ dashtop sql_id 1=1 &day
    Total
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
    16680      .2   42%                 2023-05-10 11:36:59 2023-05-11 10:58:46
     2190      .0    6%   2mhzg00unsbzf 2023-05-11 02:46:04 2023-05-11 05:19:44
      680      .0    2%   c3jafyjuwt13b 2023-05-10 12:08:03 2023-05-11 10:55:16
      480      .0    1%   g1v8dts358gq5 2023-05-10 11:36:09 2023-05-11 10:32:43
      430      .0    1%   8jjtg8t9aa79y 2023-05-11 02:03:59 2023-05-11 02:11:00
      390      .0    1%   6wcywxctbbvs8 2023-05-10 12:13:13 2023-05-11 10:31:13
      390      .0    1%   8ss7js42xzp05 2023-05-10 12:03:12 2023-05-11 10:50:25
      380      .0    1%   fnxvn2huxfy5y 2023-05-10 12:13:23 2023-05-11 10:31:23
      370      .0    1%   89u3urxj9zs1x 2023-05-10 11:42:09 2023-05-11 09:28:35
      360      .0    1%   f454ryjfx6syf 2023-05-10 12:08:23 2023-05-11 10:50:35
      320      .0    1%   7yjk3vwp2nrcu 2023-05-10 12:14:03 2023-05-11 10:46:25
      290      .0    1%   g9vqrbp03nwub 2023-05-10 12:44:37 2023-05-11 10:52:35
      270      .0    1%   d7sa2ga44303r 2023-05-10 12:33:36 2023-05-11 09:59:59
      240      .0    1%   cyqv7gknyf7bh 2023-05-10 12:09:23 2023-05-11 01:38:05
      230      .0    1%   37rzz2v2r6k04 2023-05-10 12:03:42 2023-05-11 10:53:05
      220      .0    1%   2v09t9vyy6zk6 2023-05-10 15:43:19 2023-05-11 10:10:30
      220      .0    1%   8zuqq4bunvca8 2023-05-10 11:42:49 2023-05-11 10:33:23
      210      .0    1%   19x1189chq3xd 2023-05-11 08:54:31 2023-05-11 08:58:11
      210      .0    1%   5kpx8dr6jnv3n 2023-05-11 02:00:08 2023-05-11 02:03:29
      210      .0    1%   gxak7guzxkwbh 2023-05-10 11:39:19 2023-05-11 10:42:44
      200      .0    1%   5ub6g7qwaf35x 2023-05-10 12:33:06 2023-05-11 09:50:18
      190      .0    0%   7m6szm4t720j0 2023-05-10 12:23:14 2023-05-11 09:40:26
      190      .0    0%   czkw1ncpthxy4 2023-05-10 12:43:07 2023-05-11 10:00:19
      180      .0    0%   6sbq34x7ckff7 2023-05-10 11:38:09 2023-05-11 09:00:21
      180      .0    0%   ck5qb9zs2n34g 2023-05-10 12:43:17 2023-05-11 07:15:29
      180      .0    0%   d946h5sr5gt69 2023-05-10 12:33:16 2023-05-11 08:45:30
      170      .0    0%   1g11ms1r6bnuj 2023-05-10 12:48:07 2023-05-11 10:05:20
      170      .0    0%   4qz6aykj6gq6v 2023-05-10 11:52:41 2023-05-11 09:58:39
      170      .0    0%   f6d0fpgm1w2sw 2023-05-10 14:53:13 2023-05-11 10:55:26
      160      .0    0%   18q3m92yk5zg5 2023-05-10 11:48:20 2023-05-11 10:30:33
30 rows selected.

SYS@192.168.100.237:1521/orcldg> @ ashtop sql_id 1=1 &day
    Total                                                                         Distinct Distinct
  Seconds     AAS %This   SQL_ID        FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ------------- ------------------- ------------------- ---------- --------
    22209      .3   28% |               2023-05-10 18:46:14 2023-05-11 11:33:44          1    10198
     9306      .1   12% | 89z75b1k6ybha 2023-05-11 01:10:16 2023-05-11 02:03:14         28     3113
     8969      .1   11% | 623b841u978k2 2023-05-10 18:45:06 2023-05-11 11:33:40       5118     7614
     6741      .1    8% | 32wfnhxgdwhmb 2023-05-11 09:41:25 2023-05-11 11:33:45          1     6741
     3409      .0    4% | fts36ptf9v21f 2023-05-11 00:55:50 2023-05-11 02:22:58          6     2691
     2418      .0    3% | 0v8xrbbc6kspb 2023-05-11 01:48:46 2023-05-11 02:27:07         11     1599
     2200      .0    3% | 74ad6g7z32d34 2023-05-11 08:18:11 2023-05-11 08:38:25          6     1130
     1675      .0    2% | fkb5hw5gtjch5 2023-05-11 01:03:28 2023-05-11 01:25:42          3     1335
     1492      .0    2% | 4hcufhqk7t0bt 2023-05-11 02:08:38 2023-05-11 02:33:29          1     1492
     1394      .0    2% | 103v2qw9bww7r 2023-05-11 02:10:16 2023-05-11 02:33:29          1     1394
     1381      .0    2% | c0a3w46s43y4s 2023-05-11 01:02:42 2023-05-11 01:25:42          2     1381
     1336      .0    2% | f7fhyxvguqwkq 2023-05-11 08:44:28 2023-05-11 11:04:05         36     1336
     1230      .0    2% | 6dvabspkh8v4y 2023-05-11 01:07:36 2023-05-11 01:25:44          2     1089
     1124      .0    1% | awpvjua7yu27u 2023-05-11 02:41:58 2023-05-11 03:00:41          1     1124
      711      .0    1% | 9ksmgr3sw20vu 2023-05-11 09:13:10 2023-05-11 09:55:12          4      471
      661      .0    1% | fw0gd9umv3drg 2023-05-11 01:40:16 2023-05-11 01:58:55          6      575
      586      .0    1% | 97xbt2bkaa8k7 2023-05-11 01:55:16 2023-05-11 02:09:45          3      586
      518      .0    1% | 1vf8juqdzy9cf 2023-05-11 02:39:06 2023-05-11 02:50:30          3      518
      450      .0    1% | 7pxmqak90p4z4 2023-05-10 18:54:56 2023-05-10 18:59:53          2      298
      435      .0    1% | d8ab18f07mp74 2023-05-10 20:15:12 2023-05-10 20:30:28         33      435
      403      .0    1% | amydp82bghus3 2023-05-11 01:19:01 2023-05-11 01:25:43          1      403
      399      .0    0% | 0tdjgq5qm1f07 2023-05-10 20:04:08 2023-05-10 20:12:25         31      399
      357      .0    0% | 326vvjn33xgz2 2023-05-11 08:20:10 2023-05-11 08:25:01          2      292
      346      .0    0% | 46utvzq1y75m0 2023-05-11 08:46:31 2023-05-11 10:56:15          9      346
      330      .0    0% | 37v58uha7ytpx 2023-05-11 08:42:40 2023-05-11 10:52:16          9      330
      325      .0    0% | cn7up9znk4yz4 2023-05-10 18:48:11 2023-05-11 11:33:04        114      322
      323      .0    0% | 4323z0ubrbms4 2023-05-11 08:48:37 2023-05-11 10:58:15          9      323
      302      .0    0% | 3paub2j4b05ss 2023-05-11 01:05:09 2023-05-11 01:10:10          1      302
      297      .0    0% | 73r1zz1g66d0n 2023-05-11 02:40:10 2023-05-11 02:43:53          2      224
      265      .0    0% | 0h17fpc351rp0 2023-05-10 18:46:01 2023-05-11 11:33:37        261      261
30 rows selected.
--//上下對比,發現差異很大,兩者根本沒有交集,實際上我執行上述命令是在dg伺服器,使用dashtop的查詢來源是
--//dba_hist_active_sess_history檢視,而ashtop的查詢來源是gv$active_session_history.
--//換一句話講dashtop看到的結果是主庫的相關資訊.而ashtop看到的結果是備庫的相關資訊.

--//我已經做了相關設定,支援在主庫上看備庫的awr報表,連結 [20230220][20230110]生成相關備庫的awr報表.
--//這樣使用dashtop指令碼查詢理論講應該是主備庫兩者的集合,不應該出現沒有交集的情況.

SYS@192.168.100.235:1521/orcl> select dbid,count(*) from dba_hist_active_sess_history group by dbid;
      DBID   COUNT(*)
---------- ----------
1585360079     250535

SYS@192.168.100.235:1521/orcl> select count(*) from dba_hist_active_sess_history where sql_id='89z75b1k6ybha';
  COUNT(*)
----------
         0

--//從以上情況看視乎備庫的資訊確實沒有寫入dba_hist_active_sess_history檢視對應的基表AWR_CDB_ACTIVE_SESS_HISTORY.
--//另外實際上查詢dba_hist_wr_control可以發現如下:

SYS@192.168.100.235:1521/orcl> SELECT * FROM dba_hist_wr_control
  2  @ pr
==============================
DBID                          : 1585360079
SNAP_INTERVAL                 : +00000 01:00:00.0
RETENTION                     : +00060 00:00:00.0
TOPNSQL                       : DEFAULT
CON_ID                        : 0
SRC_DBID                      : 1585360079
SRC_DBNAME                    : orcl
==============================
DBID                          : 18526484
SNAP_INTERVAL                 : +00000 01:00:00.0
RETENTION                     : +00008 00:00:00.0
TOPNSQL                       : DEFAULT
CON_ID                        : 0
SRC_DBID                      : 1585360079
SRC_DBNAME                    : standby_site

PL/SQL procedure successfully completed.
--//有主備庫的相關資訊,兩者SRC_DBID是一樣的,而DBID不同.

SYS@192.168.100.235:1521/orcl> select distinct dbid from DBA_HIST_SQLBIND ;
      DBID
----------
1585360079
  18526484

--//這些主要提醒自己注意,我開始以為使用dashtop指令碼查詢理論應該是主備庫兩者的集合,而實際的情況不是.