[20250707]使用tpt dashtop.sql查询遇到的问题.txt
--//前几天帮别人优化数据库遇到的问题，实际上关于pdb还是cdb建立awr的问题，做一个记录，通过例子说明问题。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> select inst_id, con_id, dbid, con_uid, guid, name,dict_flags from x$con;
INST_ID CON_ID DBID CON_UID GUID NAME DICT_FLAGS
---------- ---------- ---------- ---------- -------------------------------- ------------------------------ ----------
1 1 1617337831 1 C8209F27C6B16005E053362EE80AE60E CDB$ROOT 0
1 2 2763294012 2763294012 1F36DBBBA2C8169BE0636538A8C04D12 PDB$SEED 3221487616
1 3 1073777561 1073777561 1F36F47256D41C08E0636538A8C03260 BOOK01P 1074003968
2.建立问题语句例子：
SYS@book01p> @ cr_s
exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--//在pdb=book01下收集awr信息。
--//产生一条问题sql语句：
SCOTT@book01p> select count(*) from emp,emp,emp,emp,emp,emp,emp,depT;
COUNT(*)
----------
421654016
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
177759897 ggtn3ns59htnt 0 26265 776537916 a986699 2025-07-07 16:20:42 16777216
SYS@book> @ ashtop sql_id sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
19 .0 100% | ggtn3ns59htnt 2025-07-07 16:20:43 2025-07-07 16:21:01 1 19 1
--//使用tpt的ashtop.sql脚本可以查询到相关语句。
--//简单说明，对方系统存在大量不良sql语句，而且没有使用绑定变量，我想先建立保存相关信息在数据库，便于事后分析。
3.产生问题：
SYS@book01p> @ cr_s
exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
--//在pdb=book01下收集awr信息。
SYS@book> @ dashtop sql_id sql_id='ggtn3ns59htnt' &day
no rows selected
--//居然查询不到，实际上主要原因在于对方执行dbms_workload_repository.create_snapshot();在pdb下。
--//并没有在cdb下建立awr报表，导致使用dashtop查询不到相关信息。
SYS@book> select SNAP_ID,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,con_id from dba_hist_snapshot where begin_interval_time between trunc(sysdate) and sysdate order by 4;
SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME CON_ID
---------- ---------- --------------- ----------------------- ----------
888 1617337831 1 2025-07-07 08:52:08.000 0
889 1617337831 1 2025-07-07 09:03:14.974 0
890 1617337831 1 2025-07-07 16:15:48.000 0
SYS@book01p> select SNAP_ID,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,con_id from dba_hist_snapshot where begin_interval_time between trunc(sysdate) and sysdate order by 4;
SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME CON_ID
---------- ---------- --------------- ----------------------- ----------
888 1617337831 1 2025-07-07 08:52:08.000 0
889 1617337831 1 2025-07-07 09:03:14.974 0
890 1617337831 1 2025-07-07 16:15:48.000 0
7 1073777561 1 2025-07-07 16:15:48.000 3
8 1073777561 1 2025-07-07 16:21:23.982 3
--//pdb下视图可以看到con_id=0的snap_id
--//使用tpt的dashtop.sql在cdb下查询自然查询不到相关信息。当我切换pdb下查询时：
SYS@book01p> @ dashtop sql_id sql_id='ggtn3ns59htnt' &day
no rows selected
--//居然查询不到!!当我检查dashtop.sql脚本发现查询条件如下：
WHERE
a.user_id = u.user_id (+)
AND a.current_obj# = o.object_id(+)
AND &2
AND a.sample_time BETWEEN &3 AND &4
--AND a.dbid = (SELECT d.dbid FROM v$database d) -- for partition pruning
--AND a.snap_id IN (SELECT sn.snap_id FROM dba_hist_snapshot sn WHERE sn.begin_interval_time >= &3 AND sn.end_interval_time <= &4) -- for partition pruning
--AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE sample_time BETWEEN &3 AND &4) -- for partition pruning
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= &3 AND sn.end_interval_time <= &4) -- for partition pruning
--//发现作者的一个小错误，以前没注意，原始脚本作者使用下划线的查询条件，视图dba_hist_snapshot没有sample_time字段，实际上
--//作者相当于写成:
AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot )
--//没有利用partition pruning减少查询量。而我使用的条件是
AND (a.dbid, a.snap_id) IN (SELECT d.dbid, sn.snap_id FROM v$database d, dba_hist_snapshot sn WHERE d.dbid = sn.dbid AND sn.begin_interval_time >= &3 AND sn.end_interval_time <= &4) -- for partition pruning
SYS@book01p> select dbid,con_dbid from v$database;
DBID CON_DBID
---------- ----------
1617337831 1073777561
--//pdb下dbid=1617337831不变，但是dba_hist_snapshot记录的dbid=1073777561(相当于con_dbid值)。
--//相当于查询dbid=1617337831，不会查询使用dbid=1073777561的信息。
--//注解以上查询条件：
SYS@book01p> @ dashtop sql_id sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
20 .0 100% ggtn3ns59htnt 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//ok,可以查询到相关信息。
--//在等整点cdb运行awr信息收集后再次查询：
SYS@book01p> @ dashtop sql_id sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ------------------- ------------------- ---------- -------- -----------
40 .0 100% ggtn3ns59htnt 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//记录时间翻倍，因为cdb上到了整点启动awr信息收集。
--//取消注解：
SYS@book01p> @ dashtop sql_id,con_id sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID CON_ID FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- ---------- ------------------- ------------------- ---------- -------- -----------
20 .0 100% ggtn3ns59htnt 3 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//这时查询使用的是snap_id=890,891的信息。
SYS@book01p> select SNAP_ID,DBID,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,end_interval_time,con_id from dba_hist_snapshot where begin_interval_time between trunc(sysdate) and sysdate order by 5;
SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME END_INTERVAL_TIME CON_ID
---------- ---------- --------------- ----------------------- ----------------------- ----------
888 1617337831 1 2025-07-07 08:52:08.000 2025-07-07 09:03:14.974 0
889 1617337831 1 2025-07-07 09:03:14.974 2025-07-07 09:40:47.990 0
890 1617337831 1 2025-07-07 16:15:48.000 2025-07-07 16:19:09.396 0
7 1073777561 1 2025-07-07 16:15:48.000 2025-07-07 16:21:23.982 3
8 1073777561 1 2025-07-07 16:21:23.982 2025-07-07 16:35:59.422 3
891 1617337831 1 2025-07-07 16:19:09.396 2025-07-07 16:58:01.023 0
6 rows selected.
4.小结：
--//总之：以后优化注意这个问题，另外dashtop还存在一个小问题，作者使用dba_users u，dba_objects，这些视图无法查询pdb下的用
--//户以及对象。
FROM dba_hist_active_sess_history a) a
, dba_users u
, (SELECT
object_id,data_object_id,owner,object_name,subobject_name,object_type
, owner||'.'||object_name obj
, owner||'.'||object_name||' ['||object_type||']' objt
FROM dba_objects) o
--//查看tpt相关网站的更新，实际上作者已经发现这个问题，建立一个cashtop.sql脚本，里面使用就是cdb_users ，cdb_objects视图。
--//只是d系列的脚本没有写罢了。
SYS@book> @ dashtop sql_id,objt,username sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID OBJT USERNAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- --------------------- -------------------- ------------------- ------------------- ---------- -------- -----------
20 .0 100% ggtn3ns59htnt 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//在cdb下查询objt，username为null。
SYS@book01p> @ dashtop sql_id,objt,username sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID OBJT USERNAME FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- -------------------- -------------------- ------------------- ------------------- ---------- -------- -----------
20 .0 100% ggtn3ns59htnt SCOTT.PK_EMP [INDEX] SCOTT 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//顺手写一个dcashtop.sql脚本，很容易实现。
$ cp dashtop.sql dcashtop.sql
--//然后修改dba_users->cdb_ussers,dba_objects->cdb_objects.
SYS@book> @ ash/dcashtop sql_id,objt sql_id='ggtn3ns59htnt' &day
Total Distinct Distinct Distinct
Seconds AAS %This SQL_ID OBJT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------- --------------------- ------------------- ------------------- ---------- -------- -----------
20 .0 100% ggtn3ns59htnt SCOTT.PK_EMP [INDEX] 2025-07-07 16:20:51 2025-07-07 16:21:01 1 2 1
--//另外就是在解决问题时遇到使用工具出问题，还是比较影响工作的心情。
--//顺便提一个小问题，必须引起注意，如果收集dg数据库的awr，使用dashtop.sql仅仅查询到主库的awr信息。
--//要想获得备库的awr信息，关于建立备考的awr参考：[20230220][20230110]生成相关备库的awr报表.txt
--//其收集awr信息保存在数据库不是原来的dbid，而是使用 select * from dba_umf_registration;视图查询到备库的NODE_ID。
--//这样dashtop.sql要适当修改，注解以上该行,不然仅仅查询到主库的awr信息。
--//如果不加过滤条件db_id=<备库_node_id>【参数3】,这样查询到的就是主库与备库的信息,主备库混在一起，要引起注意。
--//如果加过滤条件db_id=<备库_node_id>【参数3】,这样查询到的就是备库的信息。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-3090958/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
