Re: Tuning help

On Wed, 2 May 2007 21:07:32 +0200, "astalavista" <nobody_at_nowhere.com> wrote:

>Hi,
>
>Can you help me to tune this query
>thanks in advance ...
>
> 3 SELECT
> 4 SUM (t111852.num_calls) AS c1, SUM (t111852.num_call_itms) AS
>c2,
> 5 SUM (t111852.ly_num_calls) AS c3,
> 6 SUM (t111852.ly_num_call_itms) AS c4, t111633.accnt_rnk_wid AS
>c5,
> 7 t111633.accnt_wid AS c6, t111633.act_type_wid AS c7,
> 8 t111633.area_wid AS c8, t111633.contact_rnk_wid AS c9,
> 9 t111633.contact_wid AS c10, t111633.datasource_num_id AS c11,
> 10 t111633.etl_proc_wid AS c12, t111633.integration_id AS c13,
> 11 t111633.owner_postn_wid AS c14, t111633.period_day_wid AS c15,
> 12 t111633.postn_con_wid AS c16, t111633.priority_wid AS c17,
> 13 t111633.prod_wid AS c18, t111633.row_wid AS c19,
> 14 t111633.vis_pr_pos_id AS c20, t111633.act_priority_wid AS c21,
> 15 t111633.category_wid AS c22, t111633.owner_wid AS c23,
> 16 t111633.period_month_wid AS c24, t111633.per_netwk_rnk_wid AS
>c25,
> 17 t111633.pr_vis_org_wid AS c26, t111633.replaced_flg AS c27,
> 18 t111633.rlm_flg AS c28, t111633.status_wid AS c29
> 19 FROM wc_con_t_2_align_dh t197284
> 20 /* GRS Dim Contact Alignment
>(WC_CON_T_2_ALIGN_DH) - Top -
>2 */
> 21 ,
> 22 w_emp_org_d t57538
> 23 /* GRS Dim Organization (W_ORG_D) */
> 24 ,
> 25 v_wx_act_t_a t111633
> 26 /* GRS Fact Activity Product (WX_ACT_T_A)
>*/
> 27 ,
> 28 wx_day_d t16825
> 29 /* Dim_W_DAY_D_TimePeriod */
> 30 ,
> 31 v_wx_act_t_a t111852
> 32 /* GRS Fact Activity Product (WX_ACT_T_A) - Top Acc Align - 2 */
> 33 WHERE ( t197284.inactivated_flg = 'N'
> 34 AND t197284.lvl7anc_divn_name = 'AM'
> 35 AND t57538.row_wid = t111633.pr_vis_org_wid
> 36 AND t57538.row_wid = t111852.pr_vis_org_wid
> 37 AND t111852.contact_wid = t197284.align_wid
> 38 AND t16825.row_wid = t111633.period_day_wid
> 39 AND t16825.row_wid = t111852.period_day_wid
> 40 AND t16825.per_name_fscl_ter IS NOT NULL
> 41 )
> 42 GROUP BY t111633.accnt_rnk_wid,
> 43 t111633.accnt_wid,
> 44 t111633.act_type_wid,
> 45 t111633.contact_rnk_wid,
> 46 t111633.contact_wid,
> 47 t111633.datasource_num_id,
> 48 t111633.etl_proc_wid,
> 49 t111633.integration_id,
> 50 t111633.owner_postn_wid,
> 51 t111633.period_day_wid,
> 52 t111633.postn_con_wid,
> 53 t111633.priority_wid,
> 54 t111633.prod_wid,
> 55 t111633.row_wid,
> 56 t111633.vis_pr_pos_id,
> 57 t111633.pr_vis_org_wid,
> 58 t111633.per_netwk_rnk_wid,
> 59 t111633.status_wid,
> 60 t111633.category_wid,
> 61 t111633.owner_wid,
> 62 t111633.act_priority_wid,
> 63 t111633.rlm_flg,
> 64 t111633.area_wid,
> 65 t111633.replaced_flg,
> 66 t111633.period_month_wid
> 67 /
>
>Explained.
>
>Elapsed: 00:00:00.01
>SQL> select * from table(dbms_xplan.display);
>
>PLAN_TABLE_OUTPUT
>----------------------------------------------------------------------------------------------------
>
>----------------------------------------------------------------------------------------------------
>| Id | Operation | Name | Rows
>| Bytes |TempSpc| Cost |
>----------------------------------------------------------------------------------------------------
>| 0 | SELECT STATEMENT | |
>3572M| 539G| | 43M|
>| 1 | SORT GROUP BY | |
>3572M| 539G| 1124G| 43M|
>| 2 | NESTED LOOPS | |
>3572M| 539G| | 11572 |
>|* 3 | HASH JOIN | |
>21G| 3134G| 322M| 11571 |
>|* 4 | HASH JOIN | |
>5286K| 262M| | 2514 |
>|* 5 | VIEW | index$_join$_004 | 11457
>| 156K| | 6 |
>|* 6 | HASH JOIN | |
>5286K| 262M| | 2514 |
>
>PLAN_TABLE_OUTPUT
>----------------------------------------------------------------------------------------------------
>|* 7 | INDEX FAST FULL SCAN | WX_DAY_D_M45 | 11457
>| 156K| | 1 |
>| 8 | INDEX FAST FULL SCAN | WX_DAY_D_P1 | 11457
>| 156K| | 1 |
>|* 9 | HASH JOIN | |
>5286K| 191M| 2944K| 2088 |
>|* 10 | TABLE ACCESS BY INDEX ROWID | WC_CON_T_2_ALIGN_DH |
>120K| 1527K| | 69 |
>|* 11 | INDEX RANGE SCAN | WC_CON_T_2_ALIGN_DH_M35 |
>725K| | | 323 |
>| 12 | TABLE ACCESS BY INDEX ROWID | WX_ACT_T_A |
>4504K| 107M| | 302 |
>| 13 | BITMAP CONVERSION TO ROWIDS| |
>| | | |
>| 14 | BITMAP INDEX FULL SCAN | WX_ACT_T_A_BMX_F1 |
>| | | |
>| 15 | TABLE ACCESS BY INDEX ROWID | WX_ACT_T_A |
>4504K| 451M| | 302 |
>| 16 | BITMAP CONVERSION TO ROWIDS | |
>| | | |
>| 17 | BITMAP INDEX FULL SCAN | WX_ACT_T_A_BMX_F1 |
>| | | |
>
>PLAN_TABLE_OUTPUT
>----------------------------------------------------------------------------------------------------
>|* 18 | INDEX UNIQUE SCAN | W_EMP_ORG_D_P1 | 1
>| 5 | | |
>----------------------------------------------------------------------------------------------------
>
>Predicate Information (identified by operation id):
>---------------------------------------------------
>
> 3 - access("T16825"."ROW_WID"="WX_ACT_T_A"."PERIOD_DAY_WID")
> 4 - access("T16825"."ROW_WID"="WX_ACT_T_A"."PERIOD_DAY_WID")
> 5 - filter("T16825"."PER_NAME_FSCL_TER" IS NOT NULL)
> 6 - access("indexjoin$_alias$_009".ROWID="indexjoin$_alias$_008".ROWID)
> 7 - filter("indexjoin$_alias$_008"."PER_NAME_FSCL_TER" IS NOT NULL)
>
>PLAN_TABLE_OUTPUT
>----------------------------------------------------------------------------------------------------
> 9 - access("WX_ACT_T_A"."CONTACT_WID"="T197284"."ALIGN_WID")
> 10 - filter("T197284"."LVL7ANC_DIVN_NAME"='AM')
> 11 - access("T197284"."INACTIVATED_FLG"='N')
> 18 - access("T57538"."ROW_WID"="WX_ACT_T_A"."X_PR_VIS_ORG_WID")
> filter("T57538"."ROW_WID"="WX_ACT_T_A"."X_PR_VIS_ORG_WID")
>
>Note: cpu costing is off
>
>40 rows selected.
>
>Elapsed: 00:00:00.01
>

Do you really think this is a reasonable request? Your table names are awful and unreadable. Your aliases are awful and unreadable.
You post no information about relationships between tables, primary keys, foreign keys and indices.
And you expect this forum to do *your* work *for free*? Why don't you submit a service request?
At least *those monkeys* are *getting paid* to do *your work*

-- 
Sybrand Bakker
Senior Oracle DBA