统计信息不准_导致笛卡尔积_temp表空间爆满
来源:互联网 发布:华硕fx50j优化 编辑:程序博客网 时间:2024/03/29 08:46
统计信息不准_导致笛卡尔积_temp表空间爆满
一、问题现象
前几天出现一个问题,temp表空间爆满,开发人员说平常1分钟不到就跑出的sql,现在用了30多分钟依然没有结果,最后报错temp表空间爆满
经核查,是由于sql其中的一张表的统计信息不准(是晚上10多左右,开发人员跑过程时候,在truncate中间临时表后,oracle自动收集的该表的统计信息,row_num为0了),导致的接连为笛卡尔积(merge join cartesian),关联的第二张表为BUFFER SORT 导致的temp表空间爆满
二、模拟现象发生
用sql模拟如下(部分sql语句模糊化):
1、设置 test.bbbb 表的row_num为0
exec dbms_stats.set_table_stats('TEST','BBBB',numrows=>0);
2、查看sql及执行计划
发现出现了笛卡尔积(merge join cartesian),BBBB表和DDDD表都是1万左右的数据量,笛卡尔积后大约1亿数据,而且DDDD表BUFFER SORT,这样就占用大量了temp表空间。
Select * From (Select a.Cha_aaa_Val_Id, a.Cha_aaa_Id, b.bbb_Id, b.bbb_Id_Type, c.ccc_Field_Name, b.bbb_Field_Code Value, b.bbb_Field_Name Text, Max(b.bbb_Start_Date)Start_Date, Min(b.bbb_End_Date)End_Date, b.bbb_Eparchy_Code From test.aaaa a, test.bbbb c, test.cccc b, test.dddd d, test.eeee e Where b.bbb_Id = e.Offer_Id And b.bbb_Id_Type = c.ccc_Field_Type And c.Cha_aaa_Id = e.eee_Cha_Id And (b.bbb_Attr_Code =c.ccc_Field_Name Or (b.bbb_Attr_Code <>d.bbb_Attr_Code And b.bbb_Attr_Code =d.ddd_Rsrv_Str2 And d.ddd_Rsrv_Str2 Is Not Null)) And b.bbb_Field_Code =a.aaa_Value And (Nvl(b.bbb_Field_Name,'abc') = a.Text) And a.Cha_aaa_Id = c.ccc_Cha_Id And b.bbb_Id_Type In ('H', 'A','H', 'A') And b.bbb_Id > 0 AND D.ddd_ID <> '112233' Group By b.bbb_Id, b.bbb_Id_Type, c.ccc_Field_Name, b.bbb_Field_Code, b.bbb_Field_Name, a.Cha_aaa_Val_Id, a.Cha_aaa_Id, b.bbb_Eparchy_Code)X
执行计划
Execution Plan----------------------------------------------------------| Operation | Name |row |bytes |cost|SELECT STATEMENT, GOAL = ALL_ROWS | |1 |178 |216 | HASH GROUP BY | |1 |178 |216| HASH JOIN | |1 |178 |215| NESTED LOOPS | |1 |162 |146 | NESTED LOOPS | |1 |162 |146 | MERGE JOIN CARTESIAN | |1 |141 |145 | HASH JOIN | |1 |120 | 77 | TABLE ACCESS FULL |BBBB |1 |75 | 68 | TABLE ACCESS FULL |AAAA |1938 |87210 | 9 | BUFFER SORT | |11251 |236271 |136 | TABLE ACCESS FULL |DDDD |11251 |236271 | 68 | INDEX UNIQUE SCAN |PK_CCCC | 0 |1 | | TABLE ACCESS BY INDEX ROWID |CCCC |1 |21 | 1 | TABLE ACCESS FULL |EEEE |11708 |187328 | 68
三、问题解决
1、分析一下BBBB的表信息
execute dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'BBBB',cascade=>true,DEGREE=>8,no_invalidate=>false,granularity=>'ALL',method_opt=>'for all columns size 1',estimate_percent =>dbms_stats.auto_sample_size);
2、再次执行sql半分钟就跑出来了, 执行计划如下
Plan hash value: 3301740042--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 0 | SELECT STATEMENT | | 1 | 178 | 223 (2)| 1 | HASH GROUP BY | | 1 | 178 | 223 (2)| 2 | NESTED LOOPS | | 1 | 178 | 222 (1)|* 3 | HASH JOIN | | 1 | 157 | 154 (2)|* 4 | HASH JOIN | | 1 | 141 | 86 (3)|* 5 | HASH JOIN | | 8 | 960 | 79 (3)| 6 | TABLE ACCESS FULL |AAAA | 1938 | 87210 | 9 (0)|* 7 | TABLE ACCESS FULL | BBBB | 9421 | 690K| 69 (2)|* 8 | TABLE ACCESS FULL | CCCC | 1107 | 23247 | 7 (0)| 9 | TABLE ACCESS FULL | EEEE | 11708 | 182K| 68 (0)|* 10 | TABLE ACCESS FULL | DDDD | 25 | 525 | 68 (0)--------------------------------------------------------------------------------
附1:sql tuning guide 的MERGE JOIN CARTESIAN讲解
How Cartesian Joins Work
At ahigh level, the algorithm for a Cartesian join looks as follows, where ds1 istypically the smaller data set, and ds2 is the larger data set:
FOR ds1_row IN ds1 LOOP FOR ds2_row IN ds2 LOOP output ds1_row and ds2_row END LOOPEND LOO
Example9-7 Cartesian Join
In thisexample, a user intends to perform an inner join of the employees and departmentstables, but accidentally leaves off the join condition:
SELECT e.last_name, d.department_nameFROM employees e, departments
Theexecution plan is as follows:
--------------------------------------------------------------------------------| Id| Operation | Name | Rows | Bytes |Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | |11 (100)| || 1 | MERGE JOIN CARTESIAN | | 2889 | 57780 |11 (0)| 00:00:01 || 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 324 | 2 (0)| 00:00:01 || 3 | BUFFER SORT | | 107 | 856 | 9 (0)| 00:00:01 || 4 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 856 | 0 (0)| |--------------------------------------------------------------------------------
In Step1 of the preceding plan, the CARTESIAN keyword indicates the presence of aCartesian join. The number of rows (2889) is the product of 27 and 107.
In Step3, the BUFFER SORT operation indicates that the database is copying the datablocks obtained by the scan of emp_name_ix from the SGA to the PGA. Thisstrategy avoids multiple scans of the same blocks in the database buffer cache,which would generate many logical reads and permit resource contention.
- 统计信息不准_导致笛卡尔积_temp表空间爆满
- 统计信息不准导致执行计划出错跑不出结果,优化后只要1分钟
- 分区表imdp 导入 收集统计信息 自动 采样导致执行计划card 不准
- OR导致笛卡尔积
- Oracle undo表空间爆满的解决
- 临时表空间爆满的处理办法
- Oracle undo表空间爆满的解决
- Oracle undo表空间爆满的解决
- 记一次undo表空间爆满
- Oracle undo表空间爆满的解决
- rac空间爆满问题
- standby空间爆满
- Oracle undo表空间爆满的处理方法
- Oracle undo表空间爆满的处理方法
- Oracle temp表空间爆满的处理方法
- Oracle temp表空间爆满的处理方法
- Oracle temp表空间爆满的处理方法
- MYSQL ibdata1空间爆满处理
- java并发中的原子类
- servlet的内置对象
- 自顶向下的Java虚拟机
- 基于CentOS6.5搭建ngrok内网穿透服务器
- Android必学之数据适配器BaseAdapter
- 统计信息不准_导致笛卡尔积_temp表空间爆满
- Linux--进程间通讯IPC(信号量,消息队列,共享内存)
- 1到n的加法
- 2017-2018 ACM-ICPC Nordic Collegiate Programming Contest (NCPC 2017) 部分题/平衡树/最小环/思路bfs
- 组合模式
- 用keras实验mnist数据
- hdu 1301 Jungle roads
- spring+mybatis之声明式事务管理实例
- TensorFlow学习日记13