前言
前期写过一篇oracle临时表空间不足的问题的分析，也讲了一些思路，这次碰到的问题跟之前略有不通，记录一下。
分析
1、根据报警的时间，查询v$ash视图，确认占用temp比较多的sql：
set line 200 pages 1000
col sample_time for a30
col sql_id for a30
select inst_id,sample_time,sql_id,sql_plan_hash_value,sql_exec_id,sum(TEMP_SPACE_ALLOCATED)/1024/1024/1024 GB
from gv$active_session_history
where
sample_time>to_date('20230214 17:40','yyyymmdd hh24:mi')
and
sample_time<to_date('20230214 18:05','yyyymmdd hh24:mi')
group by inst_id,sample_time,sql_id,sql_plan_hash_value,sql_exec_id
having sum(TEMP_SPACE_ALLOCATED)/1024/1024/1024>1
order by sample_time; 1 14-FEB-23 05.47.15.803 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.16.803 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.17.813 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.18.813 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.19.813 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.20.813 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.21.813 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.22.823 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.23.823 PM av2gjq1ntku7k 505231007 16777217 15.7988281
1 14-FEB-23 05.47.24.823 PM av2gjq1ntku7k 505231007 16777217 15.7988281
2、查看问题sql的执行计划
select * from table(dbms_xplan.display_cursor('av2gjq1ntku7k',0,'ADVANCED'))
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7771 (100)| |
| 1 | COUNT | | | | | |
| 2 | VIEW | | 38 | 77596 | 7771 (1)| 00:01:34 |
| 3 | HASH GROUP BY | | 38 | 1216 | 7771 (1)| 00:01:34 |
| 4 | VIEW | | 38 | 1216 | 7770 (1)| 00:01:34 |
| 5 | UNION-ALL | | | | | |
|* 6 | HASH JOIN | | 8 | 808 | 1229 (1)| 00:00:15 |
|* 7 | HASH JOIN | | 8 | 728 | 1024 (1)| 00:00:13 |
|* 8 | TABLE ACCESS FULL | ODS_ODTSML_EDS_SUBCONTRACT | 4731 | 170K| 205 (1)| 00:00:03 |
| 9 | MERGE JOIN CARTESIAN| | 17146 | 904K| 820 (1)| 00:00:10 |
|* 10 | TABLE ACCESS FULL | ODS_ODTSML_EDS_PBTRSCONTRACTGP | 1 | 36 | 204 (0)| 00:00:03 |
| 11 | BUFFER SORT | | 69690 | 1225K| 615 (1)| 00:00:08 |
|* 12 | TABLE ACCESS FULL | ODS_ODTSML_EDS_UNDERLYING | 69690 | 1225K| 615 (1)| 00:00:08 |
| 13 | TABLE ACCESS FULL | ODS_ODTSML_EDS_TRADEACCOUNT | 19573 | 191K| 204 (0)| 00:00:03 |
|* 14 | HASH JOIN | | 30 | 3270 | 6541 (1)| 00:01:19 |
|* 15 | HASH JOIN | | 30 | 2970 | 6337 (1)| 00:01:17 |
| 16 | MERGE JOIN CARTESIAN| | 17146 | 904K| 820 (1)| 00:00:10 |
|* 17 | TABLE ACCESS FULL | ODS_ODTSML_EDS_PBTRSCONTRACTGP | 1 | 36 | 204 (0)| 00:00:03 |
| 18 | BUFFER SORT | | 69690 | 1225K| 615 (1)| 00:00:08 |
|* 19 | TABLE ACCESS FULL | ODS_ODTSML_EDS_UNDERLYING | 69690 | 1225K| 615 (1)| 00:00:08 |
|* 20 | TABLE ACCESS FULL | ODS_ODTSML_SBL_EDS_SUBCONTRACT | 166K| 7327K| 5517 (1)| 00:01:07 |
| 21 | TABLE ACCESS FULL | ODS_ODTSML_EDS_TRADEACCOUNT | 19573 | 191K| 204 (0)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------
3、猛然看到了MERGE JOIN CARTESIAN，大概率是由于笛卡尔积产生了大结果集，然后进行hash join，导致占用了大量的temp表空间。
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("B"."TRADEACCOUNT_E000026"="D"."TRADEACCOUNTID")
7 - access("B"."SLUNDERLYINGID_E010001"="C"."UNDERLYINGID" AND
"A"."PBTRSCONTRACTGROUP"="B"."PBTRSCONTRACTGROUP_E020024")
8 - filter(("B"."PBTRSCONTRACTGROUP_E020024" IS NOT NULL AND "B"."SBLTYPE"='TRS'))
10 - filter(("A"."P_DT"='20230213' AND "A"."CONTRACTTYPE"='TRS' AND "A"."PBTYPE2"<>'IC'))
12 - filter(("C"."EXCHID"='0' OR "C"."EXCHID"='1' OR "C"."EXCHID"='H'))
14 - access("B"."TRADEACCOUNT_E000026"="D"."TRADEACCOUNTID")
15 - access("B"."SLUNDERLYINGID_E010001"="C"."UNDERLYINGID" AND
"A"."PBTRSCONTRACTGROUP"="B"."PBTRSCONTRACTGROUP_E020024")
17 - filter(("A"."P_DT"='20230213' AND "A"."CONTRACTTYPE"='TRS' AND "A"."PBTYPE2"<>'IC'))
19 - filter(("C"."EXCHID"='0' OR "C"."EXCHID"='1' OR "C"."EXCHID"='H'))
20 - filter("B"."SBLTYPE"='TRS') 结合执行计划中的predicate information信息，执行计划第10步的三个过滤条件，按照优化器评估，这三个过滤条件过滤后，返回的行数只有1行。
("A"."P_DT"='20230213' AND "A"."CONTRACTTYPE"='TRS' AND "A"."PBTYPE2"<>'IC')以上信息是2月14号查询的。从第四步开始都是2月15号做的查询。
4、确认数据情况，根据过滤条件，查询2月14号的数据为4521条，2月13号的数据为0条。
SQL> select count(*) from DIPPER_API.ODS_ODTSML_EDS_PBTRSCONTRACTGP WHERE 2 PBTYPE2<>'IC' AND CONTRACTTYPE='TRS' AND P_DT = '20230214'; COUNT(*) ---------- 4521SQL> select count(*) from DIPPER_API.ODS_ODTSML_EDS_PBTRSCONTRACTGP WHERE 2 PBTYPE2<>'IC' AND CONTRACTTYPE='TRS' AND P_DT = '20230213'; COUNT(*) ---------- 0
5、根据以上过滤条件查看数据库评估情况，
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4619 | 2891K| 204 (0)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| ODS_ODTSML_EDS_PBTRSCONTRACTGP | 4619 | 2891K| 204 (0)| 00:00:03 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CONTRACTTYPE"='TRS' AND "PBTYPE2"<>'IC' AND "P_DT"='20230213')
13 rows selected.
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 641 | 204 (0)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| ODS_ODTSML_EDS_PBTRSCONTRACTGP | 1 | 641 | 204 (0)| 00:00:03 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("P_DT"='20230214' AND "CONTRACTTYPE"='TRS' AND "PBTYPE2"<>'IC')
13 rows selected.
6、确认P_DT字段的统计信息
SQL> select table_name,column_name,num_distinct,UTL_RAW.CAST_TO_VARCHAR2(low_value),UTL_RAW.CAST_TO_VARCHAR2(high_value) from dba_tab_col_statistics where TABLE_NAME='ODS_ODTSML_EDS_PBTRSCONTRACTGP' and column_name='P_DT';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ODS_ODTSML_EDS_PBTRSCONTRACTGP P_DT 1
20230213
20230213 ==================>确认统计信息最大值是20230213 ，表里实际值是20230214. 综上，进行推测，执行数据库的查询是在2月14号，表里P_DT当时统计信息最大值是20230212，表里实际值是20230213，从历史查询看，虽然每天晚上22点都会收集统计信息，但是该值的更新可能是在22点以后，当收集统计信息时，表的值还是N-1天的，收集完以后，被批量改成了N天的的。在第N+1天进行查询时，带入N的值，但是统计信息时N-1的值，出现了谓词越界，由谓词越界导致了评估值太小，产生了笛卡尔积。
"-------------------------"
"Table analyze history"
OWNER OBJECT_NAME ANALYZED ROWCNT BLKCNT
------------ ------------------------ ------------------ ----------- ---------
DIPPER_API ODS_ODTSML_EDS_PBTRSCONT 20230210 22:00:15 6652 748
RACTGP
DIPPER_API ODS_ODTSML_EDS_PBTRSCONT 20230211 06:00:07 6655 748
RACTGP
DIPPER_API ODS_ODTSML_EDS_PBTRSCONT 20230212 06:00:09 6655 748
RACTGP
DIPPER_API ODS_ODTSML_EDS_PBTRSCONT 20230213 22:00:15 6655 748
RACTGP「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
