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

From: Obivan <djeday84_at_gmail.com>
Date: Tue, 04 Mar 2014 11:01:19 +0400
Message-ID: <53157A3F.1090101_at_gmail.com>



Thx for reply.
query is:
----- Current SQL Statement for this session (sql_id=5f4qrnrufsdtk) -----

/* SQL Analyze(1377,0) */ select round(p_l), count(distinct client_uk) from (select client_uk, prod, max(ovd_level) as ovd_level, sum(p_l) p_l, avg(opex) opex, sum(pl.losses) losses,
case when (prod in ('POS + POS','Низкодоходные (20-30%)','Среднедоходные (30-40%)','Акционные (<20%)','Высокодоходные( >40%)') and avg (opex)<-2225) or avg (opex) <-7500 then '3big_opex'

      when (avg (opex) >-4150 and prod in ('CASH. ЛД-100-200-300','CASH. ЛД-300-500','CASH. ЛД-50-75-100','CASH + CASH','CASH + X-sell')) or avg (opex) >-825 then '1opex_good' else '2opex_ok' end opex_level

   FROM (select L.CLIENT_UK, l.deal_loan_uk, l.deal_loan_no, case when ll.deal_limit = 0 then ll.deal_amt else ll.deal_limit end as deal_limit,

   case when deals_cnt=1 then group_prod_1 else group_all_corrected end prod

     from cmdm.loan_supertype l
     join cmdm.loans ll on l.deal_loan_uk = ll.deal_loan_uk
     join MA_USER.segment_all_1 s on s.client_uk=l.client_uk
   ) deals

   join (select contract_number,opex, p_l,losses,start_dt, end_dt_tech,report_date, case when report_date between start_dt and end_dt_tech then 1 else 0 end as open_ovd,

   case when report_date between start_dt and end_dt_tech then report_date-start_dt else 0 end as open_days_cnt,

   case when (report_date-start_dt >= 0 and report_date-start_dt < 30) or report_date-start_dt is null then 1 when report_date-start_dt >= 30 and report_date-start_dt < 60 then 2 when report_date-start_dt >= 60 and report_date-start_dt < 90 then 3 when report_date-start_dt >= 90 and report_date-start_dt < 120 then 4 else 5 end as ovd_level

               FROM (select contract_number, opex, p_l, losses, report_date, min(start_dt) start_dt, max(end_dt_tech) end_dt_tech

                     FROM (select contract_number, sum(total_pl) - 
sum(losses) - sum(oper_income) opex, sum(total_pl) p_l, sum(pl.losses) losses, max(report_date) as report_date
                         FROM U_analytic.pl_contract _at_dwh pl
                         where is_actual = 1 and upper(forecast_type) = 'F'
                         group by  contract_number) pl
                     left join (select deal_uk, deal_loan_id as 
deal_loan_no, min(start_dt) as start_dt, case when end_dt is null then trunc(sysdate) else end_dt end end_dt_tech
                               FROM (select deal_uk, start_dt, 
max(end_dt) end_dt, deal_loan_id
                                    FROM pre_dm.mart_overdue_fifo _at_dwh p
                                           where report_dt = trunc(sysdate)
                                           group by deal_uk, start_dt, 
deal_loan_id) g
                                     group by deal_uk, end_dt, 
deal_loan_id)ovd on ovd.deal_loan_no = pl.contract_number and report_date between start_dt and end_dt_tech

               group by contract_number, opex, p_l, losses, report_date ) g    ) pl on deals.deal_loan_no = pl.contract_number    group by client_uk, prod)
   where prod not in ('POS + POS','Низкодоходные (20-30%)','Среднедоходные (30-40%)','Акционные (<20%)','Высокодоходные(  >40%)')
   group by round(p_l)

On 03/03/2014 08:22 PM, David Fitzjarrell wrote:
> 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 Tue Mar 04 2014 - 08:01:19 CET

Original text of this message