统计信息不准_导致笛卡尔积_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.



 


原创粉丝点击