Home » RDBMS Server » Performance Tuning » Explain Plan from statsreport (Oracle DB, 10.2.0.4.0 , UNIX)
Explain Plan from statsreport [message #436644] |
Thu, 24 December 2009 20:44 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
smita_giri
Messages: 2 Registered: December 2009
|
Junior Member |
|
|
Hi All,
I am new to performance tuning.We are having serious performance issues in batch processes on production DB.
We ran the statspack and got some poorly performing SQLs.I am not quite able to understand and tune the SQL.Kindly help.
I am pasting the explain plan for the worst SQL here.
--------------------------------------------------------------------------------
| Operation | PHV/Object Name | Rows | Bytes| Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT |----- 1315547026 ----| | | 16 |
|NESTED LOOPS OUTER | | 11 | 14K| 16 |
| NESTED LOOPS OUTER | | 3 | 2K| 15 |
| NESTED LOOPS OUTER | | 3 | 2K| 14 |
| NESTED LOOPS OUTER | | 3 | 2K| 13 |
| NESTED LOOPS OUTER | | 3 | 2K| 12 |
| NESTED LOOPS OUTER | | 3 | 2K| 11 |
| NESTED LOOPS | | 3 | 2K| 10 |
| NESTED LOOPS OUTER | | 1 | 742 | 9 |
| NESTED LOOPS | | 1 | 730 | 8 |
| NESTED LOOPS | | 1 | 633 | 7 |
| NESTED LOOPS OUTER | | 1 | 624 | 6 |
| NESTED LOOPS OUTER | | 1 | 607 | 5 |
| NESTED LOOPS OUTER | | 1 | 583 | 4 |
| NESTED LOOPS OUTER | | 1 | 519 | 3 |
| NESTED LOOPS OUTER| | 1 | 479 | 2 |
| TABLE ACCESS BY I|S_EVT_ACT | 1 | 441 | 1 |
| INDEX UNIQUE SCA|S_EVT_ACT_P1 | 1 | | 1 |
| TABLE ACCESS BY I|S_ORG_EXT | 280K| 10M| 1 |
| INDEX UNIQUE SCA|S_ORG_EXT_U3 | 1 | | 1 |
| TABLE ACCESS BY IN|S_ADDR_PER | 279K| 10M| 1 |
| INDEX UNIQUE SCAN|S_ADDR_PER_P1 | 1 | | 1 |
| TABLE ACCESS BY IND|S_ACT_PROD_APPL | 3M| 213M| 1 |
| INDEX UNIQUE SCAN |S_ACT_PROD_APPL_P1 | 1 | | 1 |
| TABLE ACCESS BY INDE|S_PROD_INT | 130 | 3K| 1 |
| INDEX UNIQUE SCAN |S_PROD_INT_P1 | 1 | | 1 |
| TABLE ACCESS BY INDEX|S_POSTN | 2K| 37K| 1 |
| INDEX UNIQUE SCAN |S_POSTN_U2 | 1 | | 1 |
| TABLE ACCESS BY INDEX |S_BU | 2 | 18 | 1 |
| INDEX UNIQUE SCAN |S_BU_P1 | 1 | | 1 |
| TABLE ACCESS BY INDEX R|S_CONTACT | 535K| 49M| 1 |
| INDEX UNIQUE SCAN |S_CONTACT_U2 | 1 | | 1 |
| TABLE ACCESS BY INDEX RO|S_ORG_EXT | 280K| 3M| 1 |
| INDEX UNIQUE SCAN |S_ORG_EXT_P1 | 1 | | 1 |
| VIEW |VW_NSO_1 | 3 | 96 | 1 |
| SORT UNIQUE | | 269 | 23K| 3 |
| FILTER | | | | |
| TABLE ACCESS BY INDEX |S_ACT_PROD_APPL | 269 | 16K| 1 |
| NESTED LOOPS | | 269 | 23K| 2 |
| INDEX SKIP SCAN |S_PROD_INT_M16 | 1 | 24 | 1 |
| INDEX RANGE SCAN |S_ACT_PROD_APPL_F1 | 26K| | 1 |
| TABLE ACCESS BY INDEX ROWI|S_ACT_EMP | 1 | 30 | 1 |
| INDEX RANGE SCAN |S_ACT_EMP_U1 | 1 | | 1 |
| TABLE ACCESS BY INDEX ROWID|S_ACT_EMP | 1 | 32 | 1 |
| INDEX RANGE SCAN |S_ACT_EMP_U1 | 1 | | 1 |
| INDEX UNIQUE SCAN |S_PARTY_P1 | 1 | 11 | 1 |
| TABLE ACCESS BY INDEX ROWID |S_CONTACT | 1 | 51 | 1 |
| INDEX UNIQUE SCAN |S_CONTACT_U2 | 1 | | 1 |
| TABLE ACCESS BY INDEX ROWID |S_ACT_SIGN | 1 | 41 | 1 |
| INDEX RANGE SCAN |S_ACT_SIGN_U1 | 1 | | 1 |
| TABLE ACCESS BY INDEX ROWID |S_EVT_ACT_X | 4 | 1K| 1 |
| INDEX RANGE SCAN |S_EVT_ACT_X_U1 | 1 | | 1 |
--------------------------------------------------------------------------------
If anybody who knows how to proceed with the explain plan then kindly share.I will really appreciate your help.
Also I am attaching the statsreport.Kindly provide help in understanding this.
Regards
Smita
|
|
|
|
Re: Explain Plan from statsreport [message #436649 is a reply to message #436644] |
Thu, 24 December 2009 21:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
smita_giri
Messages: 2 Registered: December 2009
|
Junior Member |
|
|
Hi,
My apologies for not pasting the SQL.I forgot to paste it.
SELECT
T15.CONFLICT_ID,
T15.LAST_UPD,
T15.CREATED,
T15.LAST_UPD_BY,
T15.CREATED_BY,
T15.MODIFICATION_NUM,
T15.ROW_ID,
T15.PAPER_SIGN_FLG,
T15.PAR_EVT_ID,
T15.TODO_PLAN_START_DT,
T15.TODO_PLAN_END_DT,
T15.X_POSTN_COMPANY,
T15.OWNER_POSTN_ID,
T15.OWNER_LOGIN,
T15.OWNER_PER_ID,
T15.PR_PRDIN
T_ID,
T15.RECREATE_RCPT_FLG,
T15.APPT_REPT_FLG,
T15.APPT_REPT_END_DT,
T15.SIGN_CAPTURE_FLG,
T13.CREATED,
T15.APPT_START_DT,
T12.STATE,
T15.CALL_SUBMIT_DT,
T15.CAL_DISP_FLG,
T15.TEMPLATE_FLG,
T15.ZIPCODE,
T15.TARGET_OU_ID,
T14.NAME,
T15.COMMENTS,
T15.ALARM_FLAG,
T15.AREA_ID,
T12.CITY,
T15.TARGET_PER_ADDR_ID,
T6.FST_NAME,
T15.TARGET_PER_ID,
T4.ATTRIB_12,
T4.ATTRIB_03,
T15.NAME,
T15.CAL_TYPE_CD,
T15.APPT_DURATION_MIN,
T15.X_BU_ID,
T1.X_FREETEXT_FLG,
T7.ANNLRVW_STDT_OFFST,
T15.X_SCHED_SYS_STATUS,
T15.X_SUBMIT_OVERRIDE,
T6.LAST_NAME,
T4.ATTRIB_13,
T4.ATTRIB_04,
T4.ATTRIB_05,
T6.OK_TO_SAMPLE_FLG,
T15.OPTY_ID,
T5.ROW_ID,
T15.ROW_STATUS,
T6.CON_CD,
T6.PER_TITLE,
T15.TODO_CD,
T15.EVT_STAT_CD,
T15.SUBTYPE_CD,
T12.COUNTRY,
T9.ROW_ID,
T2.ROW_STATUS,
T3.FST_NAME,
T3.LAST_NAME,
T10.NAME,
T11.PRDINT_ID,
T4.ROW_ID,
T4.MODIFICATION_NUM,
T4.CREATED_BY,
T4.LAST_UPD_BY,
T4.CREATED,
T4.LAST_UPD,
T4.CONFLICT_ID,
T4.PAR_ROW_ID,
T2.ROW_ID,
T9.ROW_ID,
T11.ROW_ID
FROM
SIEBEL.S_ORG_EXT T1, SIEBEL.S_ACT_EMP T2, SIEBEL.S_CONTACT T3, SIEBEL.S_EVT_ACT_X T4, SIEBEL.S_ACT_EMP T5,
SIEBEL.S_CONTACT T6, SIEBEL.S_BU T7, SIEBEL.S_POSTN T8, SIEBEL.S_PARTY T9, SIEBEL.S_PROD_INT T10, SIEBEL.S_ACT_PROD_APPL T11, SIEBEL.S_ADDR_PER T12, SIEBEL.S_ACT_SIGN T13, SIEBEL.S_ORG_EXT T14, SIEBEL.S_EVT_ACT T15
WHERE
T15.ROW_ID = T13.ACTIVITY_ID (+)
AND T15.TARGET_PER_ADDR_ID = T12.ROW_ID (+)
AND T15.TARGET_PER_ID = T6.PAR_ROW_ID
AND T15.TARGET_OU_ID = T14.PAR_ROW_ID (+)
AND T15.OWNER_POSTN_ID = T8.PAR_ROW_ID (+)
AND T15.X_BU_ID = T7.ROW_ID
AND T8.OU_ID = T1.ROW_ID (+)
AND T15.ROW_ID = T5.ACTIVITY_ID (+)
AND T5.EMP_ID (+) = :1
AND T15.ROW_ID = T4.PAR_ROW_ID (+)
AND T15.OWNER_PER_ID = T2.EMP_ID (+)
AND T15.ROW_ID = T2.ACTIVITY_ID (+)
AND T2.EMP_ID = T9.ROW_ID (+)
AND T2.EMP_ID= T3.PAR_ROW_ID (+)
AND T15.PR_PRDINT_ID = T11.ROW_ID (+)
AND T11.PRDINT_ID = T10.ROW_ID (+)
AND ((T15.SUBTYPE_CD IN ( :2 ) OR T15.SUBTYPE_CD IN ( :3 )) AND T15.SUBTYPE_CD >'0') AND (T15.ROW_ID IN ( SELECT SQ1_T2.ACTIVITY_ID FROM SIEBEL.S_PROD_INT SQ1_T1, SIEBEL.S_ACT_PROD_APPL SQ1_T2 WHERE ( SQ1_T2.PRDINT_ID = SQ1_T1.ROW_ID)
AND (SQ1_T1.NAME = :4)) AND T15.TODO_PLAN_START_DT >= TO_DATE(:5,'MM/DD/YYYY HH24:MI:SS') AND T15.TODO_PLAN_START_DT <= TO_DATE(:6,'MM/DD/YYYY HH24:MI:SS') AND T15.ROW_ID = :7 AND T15.EVT_STAT_CD IN ( :8 ))
I hope the indentation is fine.
|
|
|
Re: Explain Plan from statsreport [message #436650 is a reply to message #436649] |
Thu, 24 December 2009 21:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>I hope the indentation is fine.
What indentation?
SELECT t15.conflict_id,
t15.last_upd,
t15.created,
t15.last_upd_by,
t15.created_by,
t15.modification_num,
t15.row_id,
t15.paper_sign_flg,
t15.par_evt_id,
t15.todo_plan_start_dt,
t15.todo_plan_end_dt,
t15.x_postn_company,
t15.owner_postn_id,
t15.owner_login,
t15.owner_per_id,
t15.pr_prdin t_id,
t15.recreate_rcpt_flg,
t15.appt_rept_flg,
t15.appt_rept_end_dt,
t15.sign_capture_flg,
t13.created,
t15.appt_start_dt,
t12.state,
t15.call_submit_dt,
t15.cal_disp_flg,
t15.template_flg,
t15.zipcode,
t15.target_ou_id,
t14.NAME,
t15.comments,
t15.alarm_flag,
t15.area_id,
t12.city,
t15.target_per_addr_id,
t6.fst_name,
t15.target_per_id,
t4.attrib_12,
t4.attrib_03,
t15.NAME,
t15.cal_type_cd,
t15.appt_duration_min,
t15.x_bu_id,
t1.x_freetext_flg,
t7.annlrvw_stdt_offst,
t15.x_sched_sys_status,
t15.x_submit_override,
t6.last_name,
t4.attrib_13,
t4.attrib_04,
t4.attrib_05,
t6.ok_to_sample_flg,
t15.opty_id,
t5.row_id,
t15.row_status,
t6.con_cd,
t6.per_title,
t15.todo_cd,
t15.evt_stat_cd,
t15.subtype_cd,
t12.country,
t9.row_id,
t2.row_status,
t3.fst_name,
t3.last_name,
t10.NAME,
t11.prdint_id,
t4.row_id,
t4.modification_num,
t4.created_by,
t4.last_upd_by,
t4.created,
t4.last_upd,
t4.conflict_id,
t4.par_row_id,
t2.row_id,
t9.row_id,
t11.row_id
FROM siebel.s_org_ext t1,
siebel.s_act_emp t2,
siebel.s_contact t3,
siebel.s_evt_act_x t4,
siebel.s_act_emp t5,
siebel.s_contact t6,
siebel.s_bu t7,
siebel.s_postn t8,
siebel.s_party t9,
siebel.s_prod_int t10,
siebel.s_act_prod_appl t11,
siebel.s_addr_per t12,
siebel.s_act_sign t13,
siebel.s_org_ext t14,
siebel.s_evt_act t15
WHERE t15.row_id = t13.activity_id (+)
AND t15.target_per_addr_id = t12.row_id (+)
AND t15.target_per_id = t6.par_row_id
AND t15.target_ou_id = t14.par_row_id (+)
AND t15.owner_postn_id = t8.par_row_id (+)
AND t15.x_bu_id = t7.row_id
AND t8.ou_id = t1.row_id (+)
AND t15.row_id = t5.activity_id (+)
AND t5.emp_id (+) = :1
AND t15.row_id = t4.par_row_id (+)
AND t15.owner_per_id = t2.emp_id (+)
AND t15.row_id = t2.activity_id (+)
AND t2.emp_id = t9.row_id (+)
AND t2.emp_id = t3.par_row_id (+)
AND t15.pr_prdint_id = t11.row_id (+)
AND t11.prdint_id = t10.row_id (+)
AND ((t15.subtype_cd IN (:2)
OR t15.subtype_cd IN (:3))
AND t15.subtype_cd > '0')
AND (t15.row_id IN (SELECT sq1_t2.activity_id
FROM siebel.s_prod_int sq1_t1,
siebel.s_act_prod_appl sq1_t2
WHERE (sq1_t2.prdint_id = sq1_t1.row_id)
AND (sq1_t1.NAME = :4))
AND t15.todo_plan_start_dt >= To_date(:5,'MM/DD/YYYY HH24:MI:SS')
AND t15.todo_plan_start_dt <= To_date(:6,'MM/DD/YYYY HH24:MI:SS')
AND t15.row_id = :7
AND t15.evt_stat_cd IN (:8))
All the OUTER JOINs almost 100% guarentee poor performance.
|
|
|
Re: Explain Plan from statsreport [message #436667 is a reply to message #436644] |
Fri, 25 December 2009 00:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kuailingtong
Messages: 4 Registered: September 2009
|
Junior Member |
|
|
I think nobody can tune this complex sql.
But we can give you some suggestion.
1.The number of join tables are less than 5 is better.
2.Very complex sql should dis-join to some simple sql and re-aggregate.
3.Choose suitable table to outer table.
4.If row numbers more than 10000, you should replace nested loop by hash join.
|
|
|
Re: Explain Plan from statsreport [message #437027 is a reply to message #436667] |
Wed, 30 December 2009 06:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm afraid that while your advice might be appropriate to some other databases it is, in general, not applicable to Oracle:
Quote:1.The number of join tables are less than 5 is better. As long as your statistics are up to date, there is no requirement to keep the number of tables down. In more complex queries, the number of combinations of access paths can cause the optimiser to pick a sub optimal path, but fixing these queries is a fairly simple task.
Quote:2.Very complex sql should dis-join to some simple sql and re-aggregate. If you're suggesting that you should break a query down into smaller queries using temporary tables, then no, you're wrong.
Quote:3.Choose suitable table to outer table. That's fair.
Quote:4.If row numbers more than 10000, you should replace nested loop by hash join. If it were this simple, then the CBO would already do it.
|
|
|
Goto Forum:
Current Time: Fri Aug 23 21:24:12 CDT 2024
|