# Re: what does Grouping column cardinality mean in 10053 trace ?

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 3 Mar 2014 08:22:03 -0800 (PST)
Message-ID: <1393863723.97322.YahooMailNeo_at_web124704.mail.ne1.yahoo.com>

```Can you share the SQL text for this statement?

David Fitzjarrell
Primary author, "Oracle Exadata Survival Guide"

On Monday, March 3, 2014 8:34 AM, Obivan <djeday84_at_gmail.com> wrote:

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 - 17:22:03 CET

Original text of this message