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

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