Re: what does Grouping column cardinality mean in 10053 trace ?
- From: Obivan <djeday84@xxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 04 Mar 2014 11:01:19 +0400
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 @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 @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@xxxxxxxxx> 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.
Other related posts:
