[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脚本查询理论应该是主备库两者的集合,而实际的情况不是.