| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Tuning help
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 -
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)*/
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
Explained.
Elapsed: 00:00:00.01
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
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 Received on Wed May 02 2007 - 14:07:32 CDT
 |
 |