ORA-1652: unable to extend temp segment by 64 in tablespace TEMP
以下是部分巡检记录
temp表空间共50G,被会话 483,47098占用了32G
报告中能看到改sql做的hash操作占用了大量排序表空间
根据sql_id找到改sql,这便是罪魁祸首,看起来是一个查询备份片的语句 ,它的HASH为什么会占用如此多的temp表空间呢
使用sql_monitor查看详细信息
select dbms_sqltune.report_sql_monitor(sql_id=>'dx4nqvbtu06bx',report_level=>'ALL',type=>'text') from dual
执行计划6# 显示使用了55GB的TEMP空间，CBO估算的统计信息是1行 erows列，实例arows 并非1行与实例值差异较大，因为错误的统计信息而错误的使用了多个MERGE JOIN CARTESIAN笛卡尔积的执行计划。
笛卡尔积的表关联方法一般出现必有问题
出现的3种情况：
1, 的确没有连接条件
2，在” _optimizer_cartesian_enabled”=true时，某个结果集统计信息预估行数<=1
3, or条件写错，未增加合适的括号
造成最大的问题：
1, 高CPU
2, 消耗大量的PGA以及临时表空间
执行计划这一步两个关联表X$KCCRSR,X$KSFQP都是Oracle内部固定表
SQL> select * from v$fixed_table where name='V$BACKUP_PIECE_DETAILS';
NAME OBJECT_ID TYPE TABLE_NUM
------------------------------ ---------- ----- ----------
V$BACKUP_PIECE_DETAILS 4294952626 VIEW 65537
通过查看该视图的定义可以知道X$KCCRSR,X$KSFQP表是V$RMAN_STATUS的基表,而V$BACKUP_PIECE_DETAILS又引用了V$RMAN_STATUS
怀疑fixed表统计信息出了问题,导致CBO选择了不佳的执行计划
我尝试使用RBO瞬间执行出结果
SQL> var B3 NUMBER
SQL> var B2 NUMBER
SQL> var B1 NUMBER
SQL>
SQL>
SQL>
SQL> exec :B3 := 229809
PL/SQL procedure successfully completed.
SQL> exec :B2 := 229809
PL/SQL procedure successfully completed.
SQL> exec :B1 := 1045627209
PL/SQL procedure successfully completed.
SQL>
SQL> alter session set statistics_level=all;
Session altered.
SQL> set autot off;
SQL> SELECT /*+rule*/MEDIA FROM V$BACKUP_PIECE_DETAILS WHERE SESSION_KEY=:B3 AND SESSION_RECID=:B2 AND SESSION_STAMP=:B1 AND DEVICE_TYPE = 'SBT_TAPE' AND ROWNUM = 1;
no rows selected
使用RBO则没有再出现笛卡尔积的连接情况 ,实际返回行数也变为4512行大大减少了后续步骤进行关联的代价
至此问题已经锁定在X$KCCRSR,X$KSFQP表上
SQL> select count(*) from X$KCCRSR;
COUNT(*)
----------
4512
SQL> select count(*) from X$KSFQP;
COUNT(*)
----------
26731
我发现X$KSFQP不存在统计信息,而X$KCCRSR的统计信息也相差较大
可以验证该问题的出现是因为隐藏参数在” _optimizer_cartesian_enabled”=true时，X$KSFQP结果集统计信息预估行数<=1,导致CBO选择走笛卡尔积的连接方式,以至于后续操作在超大结果集情况下选择HASH连接占满TEMP表空间
SQL> @p cartesian
NAME VALUE
---------------------------------------- ----------------------------------------
_optimizer_cartesian_enabled TRUE
得到定论后,只需要收集相关表统计信息即可,COB自然会选择合适关联方式
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'X$KSFQP', no_invalidate=>false);
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'X$KCCRSR', no_invalidate=>false);
PL/SQL procedure successfully completed.
收集统计信息后的执行计划如下,sql执行速度很快都是秒出
dbsnmp是系统用户当前用于OEM， 通过V$BACKUP_PIECE_DETAILS ora-1652在MOS中也匹配文档
Queries from V$BACKUP_PIECE_DETAILS or V$RMAN_STATUS Return Error ORA-01652 (Doc ID 2119607.1)
"ORA-1652: Unable To Extend Temp Segment By 128 In Tablespace TEMP" Due to X$ Tables (Doc ID 2575123.1)
MOS上也有相关介绍 X$表统计信息不建议随意收集,应当在必要情况下收集,收集前应进行评估
- X $ /固定表上的统计信息丢失或错误可能会导致性能下降或挂起。各种X $视图受闩锁保护，因此在大型/繁忙系统中查询可能非常昂贵。
- 最常见的问题是在DBA_EXTENTS，V $ ACCESS，V $ RMAN_BACKUP_JOB_DETAILS和V $ RMAN_STATUS的基础X $表上看到的，但是通过闩锁保护的任何固定表都可能遇到这种情况。
- 另一个常见症状是由于针对固定表的不良计划而导致的极端TEMP空间使用情况。
- RMAN，Data Guard，Streams和Grid Control通过DBA / V $视图大量使用固定表，因此通常首当其冲出现性能问题。
- 这些不是详尽的症状列表。使用X $视图并获得不良计划的任何项目可能由于缺少统计信息或统计错误而遇到不良计划。
大型固定对象上的闩锁争用非常昂贵，因此，如果没有适当的统计信息，则在发生次优计划时，性能会下降。（在某些情况下，会产生实例范围的影响，例如有效地序列化对共享池的访问）这在非常大型或非常繁忙的系统中更为普遍，因为请求锁存器的次数和/或锁的长度保持闩锁的时间，将随着通过视图查询的X $中的负载和体积而增加。
从10.1开始（除了X $表（默认情况下）），优化器在没有统计信息时使用动态采样，而不是像以前一样默认基于规则的优化。在X $表中，如果没有统计信息，则使用默认值，除非支持人员设置了特殊的下划线。如果在X $上使用默认值或动态采样，则采样时间和/或较差的计划会导致花费更长的时间，这可能会导致竞争性能问题和可能出现的“悬挂式”症状。
注意： 当缺少优化器统计信息时，动态采样不会自动用于X $表。
因此，建议收集负载下的固定对象统计信息，以便优化程序确定最佳路径。需要一定的负载，以便数据库具有尽可能多的视图的代表性卷/内容。
注意：收集统计信息时，性能可能会下降。
例如，如果在重负载下收集固定对象统计信息，则可能导致完全相同的争用问题，从而导致性能下降或挂起。
exec dbms_stats.gather_fixed_objects_stats（）;
固定表统计信息仍需要单独收集,自动收集统计信息不会收集固定表的统计信息
从12c开始，如果不存在固定对象统计信息，则可以在维护窗口期间由自动化作业收集它们。请注意，只有在所有用户对象统计信息，所有字典统计信息之后有足够的时间，然后仅在当前不存在固定对象统计信息之后，才可以执行此操作。
10. PDB和CDB固定对象统计信息分别维护。这可能确定需要同时连接到pdb和cdb进行收集。建议在不同时间运行PDB固定对象统计信息收集作业，而不是同时收集所有PDB的固定对象统计信息。