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
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 #436645 is a reply to message #436644] Thu, 24 December 2009 20:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I am pasting the explain plan for the worst SQL here.
This post is more challenging than most.
Why do you expect us to tune SQL we can not see?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Explain Plan from statsreport [message #436649 is a reply to message #436644] Thu, 24 December 2009 21:07 Go to previous messageGo to next message
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 messageGo to next message
BlackSwan
Messages: 25036
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 messageGo to next message
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
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.
Previous Topic: Sql query tuning
Next Topic: Current snap shot level?
Goto Forum:
  


Current Time: Sun Dec 04 22:47:42 CST 2016

Total time taken to generate the page: 0.15415 seconds