what does Grouping column cardinality mean in 10053 trace ?

From: Obivan <djeday84_at_gmail.com>
Date: Mon, 03 Mar 2014 19:34:06 +0400
Message-ID: <5314A0EE.8060503_at_gmail.com>

Hi guru of oracle-l =)
what does Grouping column cardinality mean in 10053 trace ? example:

Access path analysis for SEGMENT_ALL_1

---

SINGLE TABLE ACCESS PATH
   Single Table Cardinality Estimation for SEGMENT_ALL_1[S]    Table: SEGMENT_ALL_1 Alias: S
     Card: Original: 118922.000000 Rounded: 1 Computed: 0.04 Non Adjusted: 0.04

   Access Path: TableScan

     Cost:  183.24  Resp: 183.24  Degree: 0
       Cost_io: 178.00  Cost_cpu: 117615280
       Resp_io: 178.00  Resp_cpu: 117615280
   Best:: AccessPath: TableScan
          Cost: 183.24  Degree: 1  Resp: 183.24  Card: 0.04 Bytes: 0

Access path analysis for from$_subquery$_008

Grouping column cardinality [ CLIENT_UK]    580928
Grouping column cardinality [ DEALS_CNT]    1
Grouping column cardinality [GROUP_PROD]    1
Grouping column cardinality [GROUP_ALL_]    1

Background
this query run out of temp space ( 120 gb ) for 20 minutes

Worried about cartesian in step 8

---

Plan Table

---

-----------------------------------------------------+-----------------------------------+-------------------------+


| Id  | Operation                    | Name          | Rows  | Bytes | 

Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
-----------------------------------------------------+-----------------------------------+-------------------------+


| 0   | SELECT STATEMENT             |               | |       |  3244 

|           |      |      |           |

| 1   |  HASH GROUP BY               |               |     1 | 26 |  

3244 |  00:00:59 |      |      |           |


| 2   |   VIEW                       | VW_DAG_0      |     1 | 26 |  

3243 |  00:00:59 |      |      |           |


| 3   |    HASH GROUP BY             |               |     1 | 46 |  

3243 |  00:00:59 |      |      |           |


| 4   |     VIEW                     |               |     1 | 46 |  

3242 |  00:00:59 |      |      |           |


| 5   |      HASH GROUP BY           |               |     1 | 119 |  

3242 |  00:00:59 |      |      |           |


| 6   |       HASH JOIN              |               |     1 | 119 |  

3241 |  00:00:59 |      |      |           |


| 7   |        HASH JOIN             |               |     1 | 60 |  

3239 |  00:00:59 |      |      |           |


| 8   |         MERGE JOIN CARTESIAN |               | 24K |  962K |  

2119 |  00:00:39 |      |      |           |
*| 9   |          TABLE ACCESS FULL   | SEGMENT_ALL_1 |     1 |    35 


|   183 |  00:00:04 |      |      |           |*

| 10  |          BUFFER SORT         |               |  647K | 3237K |  

1936 |  00:00:35 |      |      |           |


| 11  |           TABLE ACCESS FULL  | LOANS         |  647K | 3237K |  

1936 |  00:00:35 |      |      |           |


| 12  |         TABLE ACCESS FULL    | LOAN_SUPERTYPE|  647K | 13M |  

1117 |  00:00:21 |      |      |           |


| 13  |        VIEW                  |               |   100 | 5900 

|     2 |  00:00:01 |      |      |           |

| 14  |         REMOTE               |               | |       |       

|           |DWH   | R->S |           |

-----------------------------------------------------+-----------------------------------+-------------------------+

Predicate Information:

---

6 - access("L"."DEAL_LOAN_NO"="PL"."CONTRACT_NUMBER") 7 - access("S"."CLIENT_UK"="L"."CLIENT_UK" AND "L"."DEAL_LOAN_UK"="LL"."DEAL_LOAN_UK")
9 - filter((CASE "S"."DEALS_CNT" WHEN 1 THEN "S"."GROUP_PROD_1" ELSE "S"."GROUP_ALL_CORRECTED" END <>'POS + POS' AND CASE "S"."DEALS_CNT" WHEN 1 THEN "S"."GROUP_PROD_1" ELSE "S"."GROUP_ALL_CORRECTED" END <>'????????????? (20-30%)' AND CASE "S"."DEALS_CNT" WHEN 1 THEN "S"."GROUP_PROD_1" ELSE "S"."GROUP_ALL_CORRECTED" END <>'?????????????? (30-40%)' AND CASE "S"."DEALS_CNT" WHEN 1 THEN "S"."GROUP_PROD_1" ELSE

"S"."GROUP_ALL_CORRECTED" END <>'????????? (<20%)' AND CASE 
"S"."DEALS_CNT" WHEN 1 THEN "S"."GROUP_PROD_1" ELSE 
"S"."GROUP_ALL_CORRECTED" END <>'??????????????( >40%)'))

  ASH says that this is because of hash join on step 7 http://paste.org.ru/?iqugwx

I gues that this is because of step 8 where after cartesian join result was much more than 24k.. any help wellcome ! or this
ps: sorry for my english.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 03 2014 - 16:34:06 CET