# 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

Original text of this message