数据库管理-第279期相同SQL在不同实例结果竟然不同（20250107）

原创胖头鱼的鱼缸 2025-01-07

数据库管理279期 20245-01-07

数据库管理-第279期相同SQL在不同实例结果竟然不同（20250107）

1 问题背景
2 前期排查
3 深入排查
4 尝试解决
总结

数据库管理-第279期相同SQL在不同实例结果竟然不同（20250107）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE Partner

10年数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家，数盟会长老会成员，OCM讲师
PolarDB开源社区、青学会MOP社区技术顾问
HaloDB外聘技术顾问
OceanBase观察团成员
IF CLUB社区联合发起人
圈内拥有“总监”称号，非著名社恐（社交恐怖分子）

公众号：胖头鱼的鱼缸
CSDN：胖头鱼的鱼缸（尹海文）
墨天轮：胖头鱼的鱼缸
ITPUB：yhw1809。
除授权转载并标明出处外，均为“非法”抄袭

演示文稿1_01.png
这是一个2024年遗留的问题，其实源自于EM中发现的一个问题。

1 问题背景

这是一个遗留的老库，4节点12.2的RAC。
我们每天都会通过EM对数据库所有PDB的表空间使用量进行巡检，针对使用率较高的表空间将和业务方进行沟通并扩容。但是最近发现一个非常奇怪的现象，即根据表空间当前使用数据量和数据文件自动增长的最大值比值得出的已用空间使用率（Available Space Used(%)）没有产生变化了，但是其余的值比如已用的分配空间占用率（Allocated Space Used (%)）、分配大小（Allocated Size (GB)）、已用空间（Space Used(GB)）、数据文件数量（Datafiles）等其他数据却又是变化的，且可以通过这些数值又可以人工算出正确的已用空间使用率结果：

而奇怪的是，进入PDB中又会发现已用用空间使用率结果是正确的。

2 前期排查

其实这个页面的后台语句，在数据库管理-第265期 Oracle数据库的空间问题检查与处理（20241125）中也介绍过：

select * from (
WITH df AS (
  SELECT
    con_id,
    tablespace_name,
    SUM(bytes) bytes,
    COUNT(*) cnt,
    DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext 
  FROM
    cdb_data_files 
  GROUP BY
    con_id,
tablespace_name),
um AS (SELECT con_id, tablespace_name, used_space ub, used_percent FROM cdb_tablespace_usage_metrics),
pdb AS (SELECT con_id, NAME FROM v$containers) SELECT
p.NAME,
d.tablespace_name,
TO_CHAR (u.used_percent, '99999990.00'),
NVL ((a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),
a.autoext,
NVL (a.bytes, 0) / 1024 / 1024 / 1024,
NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024 / 1024,
NVL (f.bytes, 0) / 1024 / 1024 / 1024,
d.STATUS,
a.cnt,
d.contents,
d.extent_management,
d.segment_space_management 
FROM
  cdb_tablespaces d,
  df a,
  um u,
  pdb p,
  (SELECT CON_ID, tablespace_name, SUM(bytes) bytes FROM cdb_free_space GROUP BY CON_ID, tablespace_name) f 
WHERE
  d.tablespace_name = a.tablespace_name (+) 
  AND d.tablespace_name = f.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND NOT d.contents = 'UNDO' 
  AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = a.con_id (+) 
  AND p.con_id = f.con_id (+) UNION ALL
  SELECT p.NAME,
  d.tablespace_name,
  TO_CHAR (u.used_percent, '99999990.00'),
  NVL ((u.ub * d.block_size) / tf.bytes * 100, 0),
  tf.autoext,
  NVL (tf.bytes, 0) / 1024 / 1024 / 1024,
  NVL (u.ub * d.block_size, 0) / 1024 / 1024 / 1024,
  (NVL (tf.bytes, 0) - NVL (u.ub * d.block_size, 0)) / 1024 / 1024 / 1024,
  d.STATUS,
  tf.cnt,
  d.contents,
  d.extent_management,
  d.segment_space_management 
FROM
  cdb_tablespaces d,
  um u,
  pdb p,
  (
    SELECT
      con_id,
      tablespace_name,
      SUM(bytes) bytes,
      COUNT(*) cnt,
      DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext 
    FROM
      cdb_temp_files 
    GROUP BY
      con_id,
  tablespace_name) tf 
WHERE
  d.tablespace_name = tf.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND d.extent_management = 'LOCAL' 
  AND d.contents = 'TEMPORARY' 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = tf.con_id (+) UNION ALL
SELECT
  p.NAME,
  d.tablespace_name,
  TO_CHAR (u.used_percent, '99999990.00'),
  NVL ((u.ub * d.block_size) / a.bytes * 100, 0),
  a.autoext,
  NVL (a.bytes, 0) / 1024 / 1024 / 1024,
  NVL (u.ub * d.block_size, 0) / 1024 / 1024 / 1024,
  (NVL (a.bytes, 0) - NVL (u.ub * d.block_size, 0)) / 1024 / 1024 / 1024,
  d.STATUS,
  a.cnt,
  d.contents,
  d.extent_management,
  d.segment_space_management 
FROM
  cdb_tablespaces d,
  df a,
  um u,
  pdb p 
WHERE
  d.tablespace_name = a.tablespace_name (+) 
  AND d.tablespace_name = u.tablespace_name (+) 
  AND d.contents = 'UNDO' 
  AND p.con_id = d.con_id 
  AND p.con_id = u.con_id (+) 
  AND p.con_id = a.con_id (+)
) order by 3;

这里在外面嵌套了一层用于排序。因为计算结果没有更新，一开始的排查方向是EM的缓存没有清理，但是对EM的各项设置进行检查后，并没有发现相关问题。随即在各个节点上执行该SQL，发现在节点1上执行结果有问题，在EM上将表空间查询操作指定到其他实例结果也是正确的。随即又开了个和数据库相关的SR。

3 深入排查

在数据库SR的指引下，收集了SQLHC的相关诊断信息，然后给了一大堆hint：

select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$07BDC5B4")
MERGE(@"SEL$4" >"SEL$3")
OUTLINE_LEAF(@"SEL$ABDE6DFF")
MERGE(@"SEL$6" >"SEL$5")
OUTLINE_LEAF(@"SEL$DFD66ADD")
MERGE(@"SEL$CF5359D5" >"SEL$7")
OUTLINE_LEAF(@"SEL$22C746FF")
OUTLINE_LEAF(@"SEL$513E9771")
OUTLINE_LEAF(@"SEL$522E92D8")
OUTLINE_LEAF(@"SEL$42DFC41A")
MERGE(@"SEL$12" >"SEL$11")
OUTLINE_LEAF(@"SEL$1F78930A")
MERGE(@"SEL$10" >"SEL$2")
OUTLINE_LEAF(@"SEL$513E9770")
OUTLINE_LEAF(@"SEL$522E92D7")
OUTLINE_LEAF(@"SEL$29F99543")
MERGE(@"SEL$16" >"SEL$15")
OUTLINE_LEAF(@"SEL$1CF66C63")
MERGE(@"SEL$14" >"SEL$13")
OUTLINE_LEAF(@"SEL$22C746FE")
MATERIALIZE(@"SEL$07BDC5B4")
OUTLINE_LEAF(@"SEL$513E976F")
MATERIALIZE(@"SEL$ABDE6DFF")
OUTLINE_LEAF(@"SEL$522E92D6")
MATERIALIZE(@"SEL$DFD66ADD")
OUTLINE_LEAF(@"SEL$DC4B4145")
MERGE(@"SEL$18" >"SEL$17")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$CF5359D5")
MERGE(@"SEL$9" >"SEL$8")
OUTLINE(@"SEL$07BDC5B4")
MERGE(@"SEL$4" >"SEL$3")
OUTLINE(@"SEL$ABDE6DFF")
MERGE(@"SEL$6" >"SEL$5")
OUTLINE(@"SEL$DFD66ADD")
MERGE(@"SEL$CF5359D5" >"SEL$7")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$15")
OUTLINE(@"SEL$16")
OUTLINE(@"SEL$13")
OUTLINE(@"SEL$14")
OUTLINE(@"SEL$17")
OUTLINE(@"SEL$18")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$9")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
PQ_CONCURRENT_UNION(@"SET$1")
NO_ACCESS(@"SEL$DC4B4145" "P"@"SEL$17")
FULL(@"SEL$DC4B4145" "K"@"SEL$18")
NO_ACCESS(@"SEL$DC4B4145" "A"@"SEL$17")
NO_ACCESS(@"SEL$DC4B4145" "U"@"SEL$17")
LEADING(@"SEL$DC4B4145" "P"@"SEL$17" "K"@"SEL$18" "A"@"SEL$17" "U"@"SEL$17")
USE_HASH(@"SEL$DC4B4145" "K"@"SEL$18")
USE_HASH(@"SEL$DC4B4145" "A"@"SEL$17")
USE_HASH(@"SEL$DC4B4145" "U"@"SEL$17")
PQ_DISTRIBUTE(@"SEL$DC4B4145" "K"@"SEL$18" HASH HASH)
PQ_DISTRIBUTE(@"SEL$DC4B4145" "A"@"SEL$17" HASH HASH)
PQ_DISTRIBUTE(@"SEL$DC4B4145" "U"@"SEL$17" HASH HASH)
NO_ACCESS(@"SEL$1CF66C63" "P"@"SEL$13")
FULL(@"SEL$1CF66C63" "K"@"SEL$14")
NO_ACCESS(@"SEL$1CF66C63" "U"@"SEL$13")
NO_ACCESS(@"SEL$1CF66C63" "TF"@"SEL$13")
LEADING(@"SEL$1CF66C63" "P"@"SEL$13" "K"@"SEL$14" "U"@"SEL$13" "TF"@"SEL$13")
USE_HASH(@"SEL$1CF66C63" "K"@"SEL$14")
USE_HASH(@"SEL$1CF66C63" "U"@"SEL$13")
USE_HASH(@"SEL$1CF66C63" "TF"@"SEL$13")
PQ_DISTRIBUTE(@"SEL$1CF66C63" "K"@"SEL$14" HASH HASH)
PQ_DISTRIBUTE(@"SEL$1CF66C63" "U"@"SEL$13" HASH HASH)
PQ_DISTRIBUTE(@"SEL$1CF66C63" "TF"@"SEL$13" HASH HASH)
PX_JOIN_FILTER(@"SEL$1CF66C63" "TF"@"SEL$13")
NO_ACCESS(@"SEL$1F78930A" "P"@"SEL$2")
FULL(@"SEL$1F78930A" "K"@"SEL$10")
NO_ACCESS(@"SEL$1F78930A" "A"@"SEL$2")
NO_ACCESS(@"SEL$1F78930A" "U"@"SEL$2")
NO_ACCESS(@"SEL$1F78930A" "F"@"SEL$2")
LEADING(@"SEL$1F78930A" "P"@"SEL$2" "K"@"SEL$10" "A"@"SEL$2" "U"@"SEL$2" "F"@"SEL$2")
USE_HASH(@"SEL$1F78930A" "K"@"SEL$10")
USE_HASH(@"SEL$1F78930A" "A"@"SEL$2")
USE_HASH(@"SEL$1F78930A" "U"@"SEL$2")
USE_HASH(@"SEL$1F78930A" "F"@"SEL$2")
PQ_DISTRIBUTE(@"SEL$1F78930A" "K"@"SEL$10" HASH HASH)
PQ_DISTRIBUTE(@"SEL$1F78930A" "A"@"SEL$2" HASH HASH)
PQ_DISTRIBUTE(@"SEL$1F78930A" "U"@"SEL$2" HASH HASH)
PQ_DISTRIBUTE(@"SEL$1F78930A" "F"@"SEL$2" HASH HASH)
PX_JOIN_FILTER(@"SEL$1F78930A" "F"@"SEL$2")
FULL(@"SEL$522E92D8" "T1"@"SEL$522E92D8")
FULL(@"SEL$513E9771" "T1"@"SEL$513E9771")
FULL(@"SEL$22C746FF" "T1"@"SEL$22C746FF")
FULL(@"SEL$42DFC41A" "K"@"SEL$12")
GBY_PUSHDOWN(@"SEL$42DFC41A")
USE_HASH_AGGREGATION(@"SEL$42DFC41A")
FULL(@"SEL$522E92D7" "T1"@"SEL$522E92D7")
FULL(@"SEL$513E9770" "T1"@"SEL$513E9770")
FULL(@"SEL$29F99543" "K"@"SEL$16")
GBY_PUSHDOWN(@"SEL$29F99543")
USE_HASH_AGGREGATION(@"SEL$29F99543")
FULL(@"SEL$522E92D6" "T1"@"SEL$522E92D6")
FULL(@"SEL$513E976F" "T1"@"SEL$513E976F")
FULL(@"SEL$22C746FE" "T1"@"SEL$22C746FE")
FULL(@"SEL$DFD66ADD" "X$CON"@"SEL$9")
FULL(@"SEL$ABDE6DFF" "K"@"SEL$6")
FULL(@"SEL$07BDC5B4" "K"@"SEL$4")
USE_HASH_AGGREGATION(@"SEL$07BDC5B4")
USE_PARTITION_WISE_GBY(@"SEL$07BDC5B4")
END_OUTLINE_DATA
*/
* from (
WITH df AS (
SELECT
...

对比不同节点的SQL的实际执行计划的Outline信息（这里不做展示），这里是通过hint的方式SQL执行将指向到正确的执行计划，输出结果也回归正常。
SR的回复则是：

实例 1 hard parse 出来的执行计划产生了错误结果，这个执行计划可能是一个不正确的执行计划。
一个错误的执行计划有可能产生错误的记录条数，也可能产生正确的结果条数，但是每个记录中的 sum / count 数据项却不正确。
这都是错误执行计划可能导致的结果。

4 尝试解决

既然执行计划有误，SQL PLAN会缓存在Shared Pool中，那么是不是可以通过清理Shared Pool的执行计划缓存来解决这一问题呢：

-- 查询语句的相关信息
SELECT sql_text, plan_hash_value, address, hash_value
FROM v$sqlarea
WHERE sql_id = '1fr0p0hnav1bq';

-- 清理执行计划缓存
-- EXEC DBMS_SHARED_POOL.PURGE('ADDRESS,HASH_VALUE', 'C');
EXEC DBMS_SHARED_POOL.PURGE('0000000A32100428,682460534', 'C');

再次查询，结果恢复正常：

目前得到的消息，这一现象仅会出现在对系统视图、元数据的复杂查询中。
将对应几条语句的执行计划缓存都清理过后，直接执行语句没问题了，但EM显示还是有点问题，相关问题还得继续处理。

总结

这是一个比较奇怪的从EM中发现的现象，目前已解决了数据库层面的问题。
老规矩，知道写了些啥。

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

胖头鱼的数据库之旅（共221篇）

胖头鱼的鱼缸-数据库相关文章

数据库管理-第279期 相同SQL在不同实例结果竟然不同（20250107）

数据库管理279期 20245-01-07

数据库管理-第279期 相同SQL在不同实例结果竟然不同（20250107）

1 问题背景

2 前期排查

3 深入排查

4 尝试解决

总结

文章被以下合辑收录

评论

数据库管理-第279期相同SQL在不同实例结果竟然不同（20250107）

数据库管理-第279期相同SQL在不同实例结果竟然不同（20250107）