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>
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-lReceived on Mon Mar 03 2014 - 17:22:03 CET