Unix Technical Forum

Help interpreting TKPROF output

This is a discussion on Help interpreting TKPROF output within the Oracle Database forums, part of the Database Server Software category; --> Hi everyone, I'm trying to figure out what might have slowed down considerably a specific query these past days. ...


Go Back   Unix Technical Forum > Database Server Software > Oracle Database

Help interpreting TKPROF output Help interpreting TKPROF output

FAQ Members List Calendar Search Today's Posts Mark Forums Read

Reply
Page 1 of 2 1 2 >

 

LinkBack Thread Tools Display Modes
  #1 (permalink)  
Old 02-23-2008, 04:17 AM
Daniel Roy
 
Posts: n/a
Default Help interpreting TKPROF output
Hi everyone,
I'm trying to figure out what might have slowed down considerably
a specific query these past days. I asked for a TKPROF output for only
this statement, and the relevant sections of it are displayed at the
bottom of this message. First some background: They use Oracle 8.1.7,
and RBO (this is enforced by the vendor, Siebel, who digests "new"
database features like cost-based optimizing VERY slowly). I'm told
that this query used to run in about 20 seconds, and it takes now 11
minutes!! I asked if anything to the SQL generated by Siebel got
modified, and I was told no. (for you SybrandI went through the
Performance Tuning manual, in order to get some tips on how to
interpret TKPROF output, but I'm afraid that's not enough. I don't see
anything wrong with the execution plan. Here is the TKPROF output:


TKPROF: Release 8.1.7.3.0 - Production on Tue Nov 4 15:47:30 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: ora_2998_orpo.trc
Sort options: default

************************************************** ******************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for
update)
rows = number of rows processed by the fetch or execute call
************************************************** ******************************

select
T1.LAST_UPD_BY,
T1.ROW_ID,
T1.CONFLICT_ID,
T1.CREATED_BY,
T1.CREATED,
T1.LAST_UPD,
T1.MODIFICATION_NUM,
T15.NAME,
T19.FST_NAME,
T1.TARGET_OU_ADDR_ID,
T22.LAST_UPD,
T9.BL_CLASS_CD,
T1.TODO_CD,
T1.OWNER_PER_ID,
T1.PREV_ACT_ID,
T1.COMMENTS_LONG,
T1.OWNER_LOGIN,
T1.APPT_START_DT,
T4.PLAN_START_DT,
T1.OWNER_OU_ID,
T1.PROJ_ITEM_ID,
T1.TEMPLATE_FLG,
T21.LAST_UPD,
T1.X_GM_VEH_MAKE,
T11.ATTRIB_32,
T16.PRDINT_ID,
T1.APPT_REPT_APPT_ID,
T21.LAST_UPD_BY,
T3.CREATED,
T5.ACT_APPT_START_DT,
T5.ROW_STATUS,
T4.ACTL_END_DT,
T20.FST_NAME,
T1.ME_EVT_ID,
T22.PAR_ROW_ID,
T1.APPT_ALARM_TM_MIN,
T22.ROW_ID,
T5.ACT_TODO_PLNEND_DT,
T12.ROW_ID,
T9.NAME,
T1.ASGN_MANL_FLG,
T4.PLAN_END_DT,
T18.CREATED_BY,
T1.X_GM_VEH_MOD_YR,
T3.CURR_STG_ID,
T21.PAR_ROW_ID,
T9.PROJ_NUM,
T7.LAST_NAME,
T17.MODEL_CD,
T21.INTEGRATION_ID,
T1.PCT_COMPLETE,
T1.EVT_PRIORITY_CD,
T1.CAL_DISP_FLG,
T1.PROJ_ID,
T22.CNTNT_CTG_DB_ID,
T1.TARGET_OU_ID,
T12.ROW_ID,
T1.TEMPLATE_ID,
T23.SUB_TYPE_CD,
T22.CREATED,
T19.LAST_NAME,
T1.SRA_SR_ID,
T22.FORMAT_TYPE_CD,
T1.APPT_REPT_TYPE,
T1.APPT_REPT_FLG,
T1.TARGET_PER_ID,
T23.PROD_ID,
T22.CONFLICT_ID,
T3.SRC_TYPE_CD,
T1.APPT_END_DT,
T1.DONE_FLG,
T1.PYMNT_FLG,
T18.MODIFICATION_NUM,
T1.PR_PRDINT_ID,
T24.FST_NAME,
T1.CREATOR_LOGIN,
T5.ROW_ID,
T1.CAL_TYPE_CD,
T1.OPTY_ID,
T3.SRC_TYPE_CD,
T1.X_GM_AUTOCREATED_FLG,
T14.PR_POSTN_ID,
T10.NAME,
T1.PERFRM_BY_OU_ID,
T5.ACT_TEMPLATE_FLG,
T1.APPT_REPT_END_DT,
T1.COMMENTS,
T1.X_GM_ACT_OUTCOME,
T20.LAST_NAME,
T24.LAST_NAME,
T19.HOME_PH_NUM,
T22.MODIFICATION_NUM,
T22.LAST_UPD_BY,
T17.MODEL_YR,
T3.X_GM_PURCHASED_MAKE,
T1.ALARM_FLAG,
T1.PR_CONTAINER_ID,
T1.ACCNT_SRC_ID,
T1.TODO_PLAN_END_DT,
T18.CREATED,
T11.ATTRIB_31,
T18.LAST_UPD,
T21.CONFLICT_ID,
T23.ASSET_NUM,
T2.NAME,
T3.LEAD_QUALITY_CD,
T1.ACT_TMPL_ID,
T17.MAKE_CD,
T1.EVT_STAT_CD,
T21.CREATED_BY,
T3.CURCY_CD,
T5.ACT_CAL_DISP_FLG,
T23.EXT_COLOR_CD,
T1.TODO_AFTER_DT,
T5.ACT_APPT_REPT_FLG,
T2.ROW_ID,
T1.ASGN_SYS_FLG,
T1.ASSET_ID,
T5.EMP_ID,
T1.AGREEMENT_ID,
T1.PAR_EVT_ID,
T16.ROW_ID,
T1.X_GM_VEH_MODEL,
T1.COST_CURCY_CD,
T2.LOC,
T1.PERFRM_BY_PER_ID,
T1.TARGET_PER_ADDR_ID,
T18.CURCY_CD,
T1.TODO_ACTL_START_DT,
T21.OWN_INST_ID,
T3.NAME,
T5.ACT_ALARM_FLG,
T18.CONFLICT_ID,
T5.ACT_APPT_RPTEND_DT,
T3.X_GM_OPTY_TYPE,
T5.ACT_TODO_PLNSTRTDT,
T18.ROW_ID,
T23.ASSET_NUM,
T25.NAME,
T1.PR_ATT_ID,
T1.BILLABLE_FLG,
T18.LAST_UPD_BY,
T21.ROW_ID,
T22.CREATED_BY,
T1.X_GM_SUB_TYPE,
T1.APPT_REPT_REPL_CD,
T7.FST_NAME,
T1.PR_ORDER_ID,
T1.TODO_PLAN_START_DT,
T1.NAME,
T1.SUBTYPE_CD,
T3.NAME,
T19.PREF_LANG_ID,
T1.APPT_DURATION_MIN,
T3.X_GM_PURCHASED_MODEL,
T9.BL_CURCY_CD,
T16.PRDINT_ID,
T19.WORK_PH_NUM,
T4.ACTL_START_DT,
T1.PR_SYMPTOM_CD,
T18.PAR_ROW_ID,
T20.WORK_PH_NUM,
T1.TODO_ACTL_END_DT,
T8.NAME,
T2.BASE_CURCY_CD,
T1.X_GM_VEH_EXT_COL,
T21.CREATED,
T21.MODIFICATION_NUM,
T22.EMAIL_BODY
FROM
SIEBEL.S_EVT_ACT T1,
SIEBEL.S_ORG_EXT T2,
SIEBEL.S_OPTY T3,
SIEBEL.S_PROJITEM T4,
SIEBEL.S_ACT_EMP T5,
SIEBEL.S_PARTY_RPT_REL T6,
SIEBEL.S_CONTACT T7,
SIEBEL.S_STG T8,
SIEBEL.S_PROJ T9,
SIEBEL.S_EVT_ACT T10,
SIEBEL.S_OPTY_X T11,
SIEBEL.S_PARTY T12,
SIEBEL.S_CONTACT T13,
SIEBEL.S_CONTACT T14,
SIEBEL.S_PROD_INT T15,
SIEBEL.S_ACT_PROD_APPL T16,
SIEBEL.S_PROD_INT T17,
SIEBEL.S_SRV_ACT T18,
SIEBEL.S_CONTACT T19,
SIEBEL.S_CONTACT T20,
SIEBEL.S_EVT_ACT_SS T21,
SIEBEL.S_EVT_MAIL T22,
SIEBEL.S_ASSET T23,
SIEBEL.S_CONTACT T24,
SIEBEL.S_LANG T25
WHERE
T3.CURR_STG_ID = T8.ROW_ID (+) AND
T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
T1.ASSET_ID = T23.ROW_ID (+) AND
T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND
T1.OPTY_ID = T3.ROW_ID (+) AND
T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
T1.PROJ_ID = T9.ROW_ID (+) AND
T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND
T1.TEMPLATE_ID = T10.ROW_ID (+) AND
T1.OPTY_ID = T11.PAR_ROW_ID (+) AND
T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T19.ROW_ID (+) AND
T19.PREF_LANG_ID = T25.ROW_ID (+) AND
T23.PROD_ID = T17.ROW_ID (+) AND
T1.ROW_ID = T22.PAR_ROW_ID (+) AND
T1.ROW_ID = T21.PAR_ROW_ID (+) AND
T1.ROW_ID = T18.PAR_ROW_ID (+) AND
T1.OWNER_PER_ID = T5.EMP_ID (+) AND
T1.ROW_ID = T5.ACTIVITY_ID (+) AND
T5.EMP_ID = T12.ROW_ID (+) AND
T5.EMP_ID = T24.PAR_ROW_ID (+) AND
T1.PR_PRDINT_ID = T16.ROW_ID (+) AND
T16.PRDINT_ID = T15.ROW_ID (+) AND
T13.BU_ID = T6.SUB_PARTY_ID (+) AND
(((T1.TEMPLATE_FLG != 'Y' AND
T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL) AND
(T1.OPTY_ID IS NULL OR T3.SECURE_FLG = 'N' OR
T1.OPTY_ID IN ( SELECT SQ1_T1.OPTY_ID
FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY
SQ1_T2, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN SQ1_T4
WHERE
(SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) AND
SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID
AND
SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID) AND
(SQ1_T3.ROW_ID = '1-29WRP')))) AND
(T6.PARTY_ID = '1-2J-5235') AND
(T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
'1-2L-5407') AND
(T1.APPT_REPT_REPL_CD IS NULL))

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.34 0.53 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 366.73 659.36 140803 28921647 15
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 3 367.07 659.89 140803 28921647 15
0

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 5 (SYSTEM)

Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER
1 FILTER
1745637 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 NESTED LOOPS OUTER
873290 TABLE ACCESS FULL S_EVT_ACT
770325 TABLE ACCESS BY INDEX ROWID S_ASSET
1643614 INDEX UNIQUE SCAN (object id
163988)
869748 TABLE ACCESS BY INDEX ROWID
S_CONTACT
1743037 INDEX UNIQUE SCAN (object id
165128)
869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
1743037 INDEX UNIQUE SCAN (object id 165122)
2395 TABLE ACCESS BY INDEX ROWID S_LANG
875684 INDEX UNIQUE SCAN (object id 168360)
770256 TABLE ACCESS BY INDEX ROWID S_PROD_INT
1643545 INDEX UNIQUE SCAN (object id 170462)
0 TABLE ACCESS BY INDEX ROWID
S_ACT_PROD_APPL
873289 INDEX UNIQUE SCAN (object id 163543)
0 TABLE ACCESS BY INDEX ROWID S_PROD_INT
873289 INDEX UNIQUE SCAN (object id 170462)
869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
1743037 INDEX UNIQUE SCAN (object id 165128)
872359 TABLE ACCESS BY INDEX ROWID S_CONTACT
1745648 INDEX UNIQUE SCAN (object id 165128)
0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT
873289 INDEX UNIQUE SCAN (object id 166898)
0 TABLE ACCESS BY INDEX ROWID S_PROJ
873289 INDEX UNIQUE SCAN (object id 170716)
513307 TABLE ACCESS BY INDEX ROWID S_CONTACT
1386596 INDEX UNIQUE SCAN (object id 165128)
0 TABLE ACCESS BY INDEX ROWID S_PROJITEM
873289 INDEX UNIQUE SCAN (object id 170731)
839750 TABLE ACCESS BY INDEX ROWID S_OPTY
1713039 INDEX UNIQUE SCAN (object id 168882)
39271 TABLE ACCESS BY INDEX ROWID S_STG
912560 INDEX UNIQUE SCAN (object id 172136)
5 TABLE ACCESS BY INDEX ROWID S_ORG_EXT
873294 INDEX UNIQUE SCAN (object id 169420)
871010 TABLE ACCESS BY INDEX ROWID S_ACT_EMP
1747897 INDEX RANGE SCAN (object id 174811)
870996 TABLE ACCESS BY INDEX ROWID S_CONTACT
1744285 INDEX UNIQUE SCAN (object id 165128)
870996 INDEX UNIQUE SCAN (object id 169766)
170 TABLE ACCESS BY INDEX ROWID S_EVT_MAIL
873459 INDEX RANGE SCAN (object id 167006)
0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_SS
873289 INDEX RANGE SCAN (object id 166977)
31 TABLE ACCESS BY INDEX ROWID S_SRV_ACT
873320 INDEX RANGE SCAN (object id 171966)
809443 TABLE ACCESS BY INDEX ROWID S_OPTY_X
1682732 INDEX RANGE SCAN (object id 169153)
1744696 INDEX RANGE SCAN (object id 169797)
0 FILTER
0 NESTED LOOPS OUTER
0 NESTED LOOPS
0 NESTED LOOPS
0 INDEX RANGE SCAN (object id 169071)
0 INDEX UNIQUE SCAN (object id 169766)
0 TABLE ACCESS BY INDEX ROWID S_POSTN
0 INDEX UNIQUE SCAN (object id 170114)
0 TABLE ACCESS BY INDEX ROWID S_CONTACT
0 INDEX UNIQUE SCAN (object id 165128)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 FILTER
1 FILTER
1745637 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 NESTED LOOPS (OUTER)
873290 TABLE ACCESS (FULL) OF 'S_EVT_ACT'
770325 TABLE ACCESS (BY INDEX ROWID) OF
'S_ASSET'
1643614 INDEX (UNIQUE SCAN) OF
'S_ASSET_P1' (UNIQUE)
869748 TABLE ACCESS (BY INDEX ROWID) OF
'S_CONTACT'
1743037 INDEX (UNIQUE SCAN) OF
'S_CONTACT_U2' (UNIQUE)
869748 TABLE ACCESS (BY INDEX ROWID) OF
'S_CONTACT'
1743037 INDEX (UNIQUE SCAN) OF
'S_CONTACT_P1' (UNIQUE)
2395 TABLE ACCESS (BY INDEX ROWID) OF
'S_LANG'
875684 INDEX (UNIQUE SCAN) OF 'S_LANG_P1'
(UNIQUE)
770256 TABLE ACCESS (BY INDEX ROWID) OF
'S_PROD_INT'
1643545 INDEX (UNIQUE SCAN) OF
'S_PROD_INT_P1' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF
'S_ACT_PROD_APPL'
873289 INDEX (UNIQUE SCAN) OF
'S_ACT_PROD_APPL_P1' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF
'S_PROD_INT'
873289 INDEX (UNIQUE SCAN) OF 'S_PROD_INT_P1'
(UNIQUE)
869748 TABLE ACCESS (BY INDEX ROWID) OF
'S_CONTACT'
1743037 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
(UNIQUE)
872359 TABLE ACCESS (BY INDEX ROWID) OF
'S_CONTACT'
1745648 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
(UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF
'S_EVT_ACT'
873289 INDEX (UNIQUE SCAN) OF 'S_EVT_ACT_P1'
(UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_PROJ'
873289 INDEX (UNIQUE SCAN) OF 'S_PROJ_P1' (UNIQUE)
513307 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
1386596 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
(UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_PROJITEM'
873289 INDEX (UNIQUE SCAN) OF 'S_PROJITEM_P1'
(UNIQUE)
839750 TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY'
1713039 INDEX (UNIQUE SCAN) OF 'S_OPTY_P1' (UNIQUE)
39271 TABLE ACCESS (BY INDEX ROWID) OF 'S_STG'
912560 INDEX (UNIQUE SCAN) OF 'S_STG_P1' (UNIQUE)
5 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
873294 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)
871010 TABLE ACCESS (BY INDEX ROWID) OF 'S_ACT_EMP'
1747897 INDEX (RANGE SCAN) OF 'S_ACT_EMP_F1' (NON-UNIQUE)
870996 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
1744285 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
870996 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
170 TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_MAIL'
873459 INDEX (RANGE SCAN) OF 'S_EVT_MAIL_U1' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT_SS'
873289 INDEX (RANGE SCAN) OF 'S_EVT_ACT_SS_U1' (UNIQUE)
31 TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_ACT'
873320 INDEX (RANGE SCAN) OF 'S_SRV_ACT_U1' (UNIQUE)
809443 TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY_X'
1682732 INDEX (RANGE SCAN) OF 'S_OPTY_X_U1' (UNIQUE)
1744696 INDEX (RANGE SCAN) OF 'S_PARTY_RPT_REL_U2' (UNIQUE)
0 FILTER
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS
0 NESTED LOOPS
0 INDEX (RANGE SCAN) OF 'S_OPTY_POSTN_U1' (UNIQUE)
0 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN'
0 INDEX (UNIQUE SCAN) OF 'S_POSTN_U2' (UNIQUE)
0 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
0 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)

************************************************** ******************************

Trace file: ora_2998_orpo.trc
Trace file compatibility: 8.00.04
Sort options: default

1 session in tracefile.
5 user SQL statements in trace file.
70 internal SQL statements in trace file.
75 SQL statements in trace file.
14 unique SQL statements in trace file.
1 SQL statements EXPLAINed using schema:
SYSTEM.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
1235 lines in trace file.


Anyone has an idea as to what might be wrong? Or is there any other
tool (besides TKPROF) that they might use to better tune this
statement?

Daniel
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #2 (permalink)  
Old 02-23-2008, 04:17 AM
Sybrand Bakker
 
Posts: n/a
Default Re: Help interpreting TKPROF output
On 7 Nov 2003 12:54:25 -0800, danielroy10junk@hotmail.com (Daniel Roy)
wrote:

>
>Anyone has an idea as to what might be wrong? Or is there any other
>tool (besides TKPROF) that they might use to better tune this
>statement?

1 The datamodel
2 The datamodel
3 The datamodel

The statement shows they are outer-joining about everything, so what
would you expect?
The best tool to tune this statement is an open window: take the
statement and throw it out, and Siebel with it.
I don't think anything can be done about the execution path, or you
should provide full information on all the indexes on all the tables.
Apart from throwing the app out of the window, your best bet is to
throw more iron at the problem.



--
Sybrand Bakker, Senior Oracle DBA
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 02-23-2008, 04:17 AM
Howard J. Rogers
 
Posts: n/a
Default Re: Help interpreting TKPROF output
My Lord!

I had heard that Siebel was pretty dreadful. Now I have seen it with my own
eyes.

Call that an execution plan????

"Perm Any 8 from 16" would do as well.

Sorry, not much help I realise. I"ll take a look properly when I've got my
breath back from laughing so hard.

Regards
HJR



"Daniel Roy" <danielroy10junk@hotmail.com> wrote in message
news:3722db.0311071254.74f91dfe@posting.google.com ...
> Hi everyone,
> I'm trying to figure out what might have slowed down considerably
> a specific query these past days. I asked for a TKPROF output for only
> this statement, and the relevant sections of it are displayed at the
> bottom of this message. First some background: They use Oracle 8.1.7,
> and RBO (this is enforced by the vendor, Siebel, who digests "new"
> database features like cost-based optimizing VERY slowly). I'm told
> that this query used to run in about 20 seconds, and it takes now 11
> minutes!! I asked if anything to the SQL generated by Siebel got
> modified, and I was told no. (for you SybrandI went through the
> Performance Tuning manual, in order to get some tips on how to
> interpret TKPROF output, but I'm afraid that's not enough. I don't see
> anything wrong with the execution plan. Here is the TKPROF output:
>
>
> TKPROF: Release 8.1.7.3.0 - Production on Tue Nov 4 15:47:30 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Trace file: ora_2998_orpo.trc
> Sort options: default
>
>
************************************************** **************************
****
> count = number of times OCI procedure was executed
> cpu = cpu time in seconds executing
> elapsed = elapsed time in seconds executing
> disk = number of physical reads of buffers from disk
> query = number of buffers gotten for consistent read
> current = number of buffers gotten in current mode (usually for
> update)
> rows = number of rows processed by the fetch or execute call
>
************************************************** **************************
****
>
> select
> T1.LAST_UPD_BY,
> T1.ROW_ID,
> T1.CONFLICT_ID,
> T1.CREATED_BY,
> T1.CREATED,
> T1.LAST_UPD,
> T1.MODIFICATION_NUM,
> T15.NAME,
> T19.FST_NAME,
> T1.TARGET_OU_ADDR_ID,
> T22.LAST_UPD,
> T9.BL_CLASS_CD,
> T1.TODO_CD,
> T1.OWNER_PER_ID,
> T1.PREV_ACT_ID,
> T1.COMMENTS_LONG,
> T1.OWNER_LOGIN,
> T1.APPT_START_DT,
> T4.PLAN_START_DT,
> T1.OWNER_OU_ID,
> T1.PROJ_ITEM_ID,
> T1.TEMPLATE_FLG,
> T21.LAST_UPD,
> T1.X_GM_VEH_MAKE,
> T11.ATTRIB_32,
> T16.PRDINT_ID,
> T1.APPT_REPT_APPT_ID,
> T21.LAST_UPD_BY,
> T3.CREATED,
> T5.ACT_APPT_START_DT,
> T5.ROW_STATUS,
> T4.ACTL_END_DT,
> T20.FST_NAME,
> T1.ME_EVT_ID,
> T22.PAR_ROW_ID,
> T1.APPT_ALARM_TM_MIN,
> T22.ROW_ID,
> T5.ACT_TODO_PLNEND_DT,
> T12.ROW_ID,
> T9.NAME,
> T1.ASGN_MANL_FLG,
> T4.PLAN_END_DT,
> T18.CREATED_BY,
> T1.X_GM_VEH_MOD_YR,
> T3.CURR_STG_ID,
> T21.PAR_ROW_ID,
> T9.PROJ_NUM,
> T7.LAST_NAME,
> T17.MODEL_CD,
> T21.INTEGRATION_ID,
> T1.PCT_COMPLETE,
> T1.EVT_PRIORITY_CD,
> T1.CAL_DISP_FLG,
> T1.PROJ_ID,
> T22.CNTNT_CTG_DB_ID,
> T1.TARGET_OU_ID,
> T12.ROW_ID,
> T1.TEMPLATE_ID,
> T23.SUB_TYPE_CD,
> T22.CREATED,
> T19.LAST_NAME,
> T1.SRA_SR_ID,
> T22.FORMAT_TYPE_CD,
> T1.APPT_REPT_TYPE,
> T1.APPT_REPT_FLG,
> T1.TARGET_PER_ID,
> T23.PROD_ID,
> T22.CONFLICT_ID,
> T3.SRC_TYPE_CD,
> T1.APPT_END_DT,
> T1.DONE_FLG,
> T1.PYMNT_FLG,
> T18.MODIFICATION_NUM,
> T1.PR_PRDINT_ID,
> T24.FST_NAME,
> T1.CREATOR_LOGIN,
> T5.ROW_ID,
> T1.CAL_TYPE_CD,
> T1.OPTY_ID,
> T3.SRC_TYPE_CD,
> T1.X_GM_AUTOCREATED_FLG,
> T14.PR_POSTN_ID,
> T10.NAME,
> T1.PERFRM_BY_OU_ID,
> T5.ACT_TEMPLATE_FLG,
> T1.APPT_REPT_END_DT,
> T1.COMMENTS,
> T1.X_GM_ACT_OUTCOME,
> T20.LAST_NAME,
> T24.LAST_NAME,
> T19.HOME_PH_NUM,
> T22.MODIFICATION_NUM,
> T22.LAST_UPD_BY,
> T17.MODEL_YR,
> T3.X_GM_PURCHASED_MAKE,
> T1.ALARM_FLAG,
> T1.PR_CONTAINER_ID,
> T1.ACCNT_SRC_ID,
> T1.TODO_PLAN_END_DT,
> T18.CREATED,
> T11.ATTRIB_31,
> T18.LAST_UPD,
> T21.CONFLICT_ID,
> T23.ASSET_NUM,
> T2.NAME,
> T3.LEAD_QUALITY_CD,
> T1.ACT_TMPL_ID,
> T17.MAKE_CD,
> T1.EVT_STAT_CD,
> T21.CREATED_BY,
> T3.CURCY_CD,
> T5.ACT_CAL_DISP_FLG,
> T23.EXT_COLOR_CD,
> T1.TODO_AFTER_DT,
> T5.ACT_APPT_REPT_FLG,
> T2.ROW_ID,
> T1.ASGN_SYS_FLG,
> T1.ASSET_ID,
> T5.EMP_ID,
> T1.AGREEMENT_ID,
> T1.PAR_EVT_ID,
> T16.ROW_ID,
> T1.X_GM_VEH_MODEL,
> T1.COST_CURCY_CD,
> T2.LOC,
> T1.PERFRM_BY_PER_ID,
> T1.TARGET_PER_ADDR_ID,
> T18.CURCY_CD,
> T1.TODO_ACTL_START_DT,
> T21.OWN_INST_ID,
> T3.NAME,
> T5.ACT_ALARM_FLG,
> T18.CONFLICT_ID,
> T5.ACT_APPT_RPTEND_DT,
> T3.X_GM_OPTY_TYPE,
> T5.ACT_TODO_PLNSTRTDT,
> T18.ROW_ID,
> T23.ASSET_NUM,
> T25.NAME,
> T1.PR_ATT_ID,
> T1.BILLABLE_FLG,
> T18.LAST_UPD_BY,
> T21.ROW_ID,
> T22.CREATED_BY,
> T1.X_GM_SUB_TYPE,
> T1.APPT_REPT_REPL_CD,
> T7.FST_NAME,
> T1.PR_ORDER_ID,
> T1.TODO_PLAN_START_DT,
> T1.NAME,
> T1.SUBTYPE_CD,
> T3.NAME,
> T19.PREF_LANG_ID,
> T1.APPT_DURATION_MIN,
> T3.X_GM_PURCHASED_MODEL,
> T9.BL_CURCY_CD,
> T16.PRDINT_ID,
> T19.WORK_PH_NUM,
> T4.ACTL_START_DT,
> T1.PR_SYMPTOM_CD,
> T18.PAR_ROW_ID,
> T20.WORK_PH_NUM,
> T1.TODO_ACTL_END_DT,
> T8.NAME,
> T2.BASE_CURCY_CD,
> T1.X_GM_VEH_EXT_COL,
> T21.CREATED,
> T21.MODIFICATION_NUM,
> T22.EMAIL_BODY
> FROM
> SIEBEL.S_EVT_ACT T1,
> SIEBEL.S_ORG_EXT T2,
> SIEBEL.S_OPTY T3,
> SIEBEL.S_PROJITEM T4,
> SIEBEL.S_ACT_EMP T5,
> SIEBEL.S_PARTY_RPT_REL T6,
> SIEBEL.S_CONTACT T7,
> SIEBEL.S_STG T8,
> SIEBEL.S_PROJ T9,
> SIEBEL.S_EVT_ACT T10,
> SIEBEL.S_OPTY_X T11,
> SIEBEL.S_PARTY T12,
> SIEBEL.S_CONTACT T13,
> SIEBEL.S_CONTACT T14,
> SIEBEL.S_PROD_INT T15,
> SIEBEL.S_ACT_PROD_APPL T16,
> SIEBEL.S_PROD_INT T17,
> SIEBEL.S_SRV_ACT T18,
> SIEBEL.S_CONTACT T19,
> SIEBEL.S_CONTACT T20,
> SIEBEL.S_EVT_ACT_SS T21,
> SIEBEL.S_EVT_MAIL T22,
> SIEBEL.S_ASSET T23,
> SIEBEL.S_CONTACT T24,
> SIEBEL.S_LANG T25
> WHERE
> T3.CURR_STG_ID = T8.ROW_ID (+) AND
> T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
> T1.ASSET_ID = T23.ROW_ID (+) AND
> T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND
> T1.OPTY_ID = T3.ROW_ID (+) AND
> T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
> T1.PROJ_ID = T9.ROW_ID (+) AND
> T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND
> T1.TEMPLATE_ID = T10.ROW_ID (+) AND
> T1.OPTY_ID = T11.PAR_ROW_ID (+) AND
> T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T19.ROW_ID (+) AND
> T19.PREF_LANG_ID = T25.ROW_ID (+) AND
> T23.PROD_ID = T17.ROW_ID (+) AND
> T1.ROW_ID = T22.PAR_ROW_ID (+) AND
> T1.ROW_ID = T21.PAR_ROW_ID (+) AND
> T1.ROW_ID = T18.PAR_ROW_ID (+) AND
> T1.OWNER_PER_ID = T5.EMP_ID (+) AND
> T1.ROW_ID = T5.ACTIVITY_ID (+) AND
> T5.EMP_ID = T12.ROW_ID (+) AND
> T5.EMP_ID = T24.PAR_ROW_ID (+) AND
> T1.PR_PRDINT_ID = T16.ROW_ID (+) AND
> T16.PRDINT_ID = T15.ROW_ID (+) AND
> T13.BU_ID = T6.SUB_PARTY_ID (+) AND
> (((T1.TEMPLATE_FLG != 'Y' AND
> T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL) AND
> (T1.OPTY_ID IS NULL OR T3.SECURE_FLG = 'N' OR
> T1.OPTY_ID IN ( SELECT SQ1_T1.OPTY_ID
> FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY
> SQ1_T2, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN SQ1_T4
> WHERE
> (SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) AND
> SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID
> AND
> SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID) AND
> (SQ1_T3.ROW_ID = '1-29WRP')))) AND
> (T6.PARTY_ID = '1-2J-5235') AND
> (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
> '1-2L-5407') AND
> (T1.APPT_REPT_REPL_CD IS NULL))
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.34 0.53 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 366.73 659.36 140803 28921647 15
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 367.07 659.89 140803 28921647 15
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 5 (SYSTEM)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 FILTER
> 1 FILTER
> 1745637 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 TABLE ACCESS FULL S_EVT_ACT
> 770325 TABLE ACCESS BY INDEX ROWID S_ASSET
> 1643614 INDEX UNIQUE SCAN (object id
> 163988)
> 869748 TABLE ACCESS BY INDEX ROWID
> S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id
> 165128)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165122)
> 2395 TABLE ACCESS BY INDEX ROWID S_LANG
> 875684 INDEX UNIQUE SCAN (object id 168360)
> 770256 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 1643545 INDEX UNIQUE SCAN (object id 170462)
> 0 TABLE ACCESS BY INDEX ROWID
> S_ACT_PROD_APPL
> 873289 INDEX UNIQUE SCAN (object id 163543)
> 0 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 873289 INDEX UNIQUE SCAN (object id 170462)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165128)
> 872359 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1745648 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT
> 873289 INDEX UNIQUE SCAN (object id 166898)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJ
> 873289 INDEX UNIQUE SCAN (object id 170716)
> 513307 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1386596 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJITEM
> 873289 INDEX UNIQUE SCAN (object id 170731)
> 839750 TABLE ACCESS BY INDEX ROWID S_OPTY
> 1713039 INDEX UNIQUE SCAN (object id 168882)
> 39271 TABLE ACCESS BY INDEX ROWID S_STG
> 912560 INDEX UNIQUE SCAN (object id 172136)
> 5 TABLE ACCESS BY INDEX ROWID S_ORG_EXT
> 873294 INDEX UNIQUE SCAN (object id 169420)
> 871010 TABLE ACCESS BY INDEX ROWID S_ACT_EMP
> 1747897 INDEX RANGE SCAN (object id 174811)
> 870996 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1744285 INDEX UNIQUE SCAN (object id 165128)
> 870996 INDEX UNIQUE SCAN (object id 169766)
> 170 TABLE ACCESS BY INDEX ROWID S_EVT_MAIL
> 873459 INDEX RANGE SCAN (object id 167006)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_SS
> 873289 INDEX RANGE SCAN (object id 166977)
> 31 TABLE ACCESS BY INDEX ROWID S_SRV_ACT
> 873320 INDEX RANGE SCAN (object id 171966)
> 809443 TABLE ACCESS BY INDEX ROWID S_OPTY_X
> 1682732 INDEX RANGE SCAN (object id 169153)
> 1744696 INDEX RANGE SCAN (object id 169797)
> 0 FILTER
> 0 NESTED LOOPS OUTER
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 INDEX RANGE SCAN (object id 169071)
> 0 INDEX UNIQUE SCAN (object id 169766)
> 0 TABLE ACCESS BY INDEX ROWID S_POSTN
> 0 INDEX UNIQUE SCAN (object id 170114)
> 0 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 0 INDEX UNIQUE SCAN (object id 165128)
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: RULE
> 0 FILTER
> 1 FILTER
> 1745637 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 TABLE ACCESS (FULL) OF 'S_EVT_ACT'
> 770325 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_ASSET'
> 1643614 INDEX (UNIQUE SCAN) OF
> 'S_ASSET_P1' (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF
> 'S_CONTACT_U2' (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF
> 'S_CONTACT_P1' (UNIQUE)
> 2395 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_LANG'
> 875684 INDEX (UNIQUE SCAN) OF 'S_LANG_P1'
> (UNIQUE)
> 770256 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_PROD_INT'
> 1643545 INDEX (UNIQUE SCAN) OF
> 'S_PROD_INT_P1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_ACT_PROD_APPL'
> 873289 INDEX (UNIQUE SCAN) OF
> 'S_ACT_PROD_APPL_P1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_PROD_INT'
> 873289 INDEX (UNIQUE SCAN) OF 'S_PROD_INT_P1'
> (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
> (UNIQUE)
> 872359 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1745648 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
> (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_EVT_ACT'
> 873289 INDEX (UNIQUE SCAN) OF 'S_EVT_ACT_P1'
> (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_PROJ'
> 873289 INDEX (UNIQUE SCAN) OF 'S_PROJ_P1' (UNIQUE)
> 513307 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
> 1386596 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
> (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_PROJITEM'
> 873289 INDEX (UNIQUE SCAN) OF 'S_PROJITEM_P1'
> (UNIQUE)
> 839750 TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY'
> 1713039 INDEX (UNIQUE SCAN) OF 'S_OPTY_P1' (UNIQUE)
> 39271 TABLE ACCESS (BY INDEX ROWID) OF 'S_STG'
> 912560 INDEX (UNIQUE SCAN) OF 'S_STG_P1' (UNIQUE)
> 5 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'
> 873294 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_U3' (UNIQUE)
> 871010 TABLE ACCESS (BY INDEX ROWID) OF 'S_ACT_EMP'
> 1747897 INDEX (RANGE SCAN) OF 'S_ACT_EMP_F1' (NON-UNIQUE)
> 870996 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
> 1744285 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
> 870996 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
> 170 TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_MAIL'
> 873459 INDEX (RANGE SCAN) OF 'S_EVT_MAIL_U1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_EVT_ACT_SS'
> 873289 INDEX (RANGE SCAN) OF 'S_EVT_ACT_SS_U1' (UNIQUE)
> 31 TABLE ACCESS (BY INDEX ROWID) OF 'S_SRV_ACT'
> 873320 INDEX (RANGE SCAN) OF 'S_SRV_ACT_U1' (UNIQUE)
> 809443 TABLE ACCESS (BY INDEX ROWID) OF 'S_OPTY_X'
> 1682732 INDEX (RANGE SCAN) OF 'S_OPTY_X_U1' (UNIQUE)
> 1744696 INDEX (RANGE SCAN) OF 'S_PARTY_RPT_REL_U2' (UNIQUE)
> 0 FILTER
> 0 NESTED LOOPS (OUTER)
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 INDEX (RANGE SCAN) OF 'S_OPTY_POSTN_U1' (UNIQUE)
> 0 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN'
> 0 INDEX (UNIQUE SCAN) OF 'S_POSTN_U2' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_CONTACT'
> 0 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2' (UNIQUE)
>
>
************************************************** **************************
****
>
> Trace file: ora_2998_orpo.trc
> Trace file compatibility: 8.00.04
> Sort options: default
>
> 1 session in tracefile.
> 5 user SQL statements in trace file.
> 70 internal SQL statements in trace file.
> 75 SQL statements in trace file.
> 14 unique SQL statements in trace file.
> 1 SQL statements EXPLAINed using schema:
> SYSTEM.prof$plan_table
> Default table was used.
> Table was created.
> Table was dropped.
> 1235 lines in trace file.
>
>
> Anyone has an idea as to what might be wrong? Or is there any other
> tool (besides TKPROF) that they might use to better tune this
> statement?
>
> Daniel


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 02-23-2008, 04:17 AM
Ron Reidy
 
Posts: n/a
Default Re: Help interpreting TKPROF output


[ snip ]
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 FILTER
> 1 FILTER
> 1745637 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 TABLE ACCESS FULL S_EVT_ACT
> 770325 TABLE ACCESS BY INDEX ROWID S_ASSET
> 1643614 INDEX UNIQUE SCAN (object id
> 163988)
> 869748 TABLE ACCESS BY INDEX ROWID
> S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id
> 165128)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165122)
> 2395 TABLE ACCESS BY INDEX ROWID S_LANG
> 875684 INDEX UNIQUE SCAN (object id 168360)
> 770256 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 1643545 INDEX UNIQUE SCAN (object id 170462)
> 0 TABLE ACCESS BY INDEX ROWID
> S_ACT_PROD_APPL
> 873289 INDEX UNIQUE SCAN (object id 163543)
> 0 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 873289 INDEX UNIQUE SCAN (object id 170462)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165128)
> 872359 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1745648 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT
> 873289 INDEX UNIQUE SCAN (object id 166898)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJ
> 873289 INDEX UNIQUE SCAN (object id 170716)
> 513307 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1386596 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJITEM
> 873289 INDEX UNIQUE SCAN (object id 170731)
> 839750 TABLE ACCESS BY INDEX ROWID S_OPTY
> 1713039 INDEX UNIQUE SCAN (object id 168882)
> 39271 TABLE ACCESS BY INDEX ROWID S_STG
> 912560 INDEX UNIQUE SCAN (object id 172136)
> 5 TABLE ACCESS BY INDEX ROWID S_ORG_EXT
> 873294 INDEX UNIQUE SCAN (object id 169420)
> 871010 TABLE ACCESS BY INDEX ROWID S_ACT_EMP
> 1747897 INDEX RANGE SCAN (object id 174811)
> 870996 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1744285 INDEX UNIQUE SCAN (object id 165128)
> 870996 INDEX UNIQUE SCAN (object id 169766)
> 170 TABLE ACCESS BY INDEX ROWID S_EVT_MAIL
> 873459 INDEX RANGE SCAN (object id 167006)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_SS
> 873289 INDEX RANGE SCAN (object id 166977)
> 31 TABLE ACCESS BY INDEX ROWID S_SRV_ACT
> 873320 INDEX RANGE SCAN (object id 171966)
> 809443 TABLE ACCESS BY INDEX ROWID S_OPTY_X
> 1682732 INDEX RANGE SCAN (object id 169153)
> 1744696 INDEX RANGE SCAN (object id 169797)
> 0 FILTER
> 0 NESTED LOOPS OUTER
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 INDEX RANGE SCAN (object id 169071)
> 0 INDEX UNIQUE SCAN (object id 169766)
> 0 TABLE ACCESS BY INDEX ROWID S_POSTN
> 0 INDEX UNIQUE SCAN (object id 170114)
> 0 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 0 INDEX UNIQUE SCAN (object id 165128)
>
>
[snip]

The biggest [problem here is (get ready for it ...) **IO**. This query
appears to be visiting too many rows.

What waits are being generated when this is executing? What kind of sar
output do you have?

What did it do before?

--
Ron Reidy
Oracle DBA

Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 02-23-2008, 04:17 AM
Wolfgang Breitling
 
Posts: n/a
Default Re: Help interpreting TKPROF output
danielroy10junk@hotmail.com (Daniel Roy) wrote in
news:3722db.0311071254.74f91dfe@posting.google.com :

> Hi everyone,
> I'm trying to figure out what might have slowed down considerably
> a specific query these past days. I asked for a TKPROF output for only
> this statement, and the relevant sections of it are displayed at the
> bottom of this message. First some background: They use Oracle 8.1.7,
> and RBO (this is enforced by the vendor, Siebel, who digests "new"
> database features like cost-based optimizing VERY slowly). I'm told
> that this query used to run in about 20 seconds, and it takes now 11
> minutes!! I asked if anything to the SQL generated by Siebel got
> modified, and I was told no. (for you SybrandI went through the
> Performance Tuning manual, in order to get some tips on how to
> interpret TKPROF output, but I'm afraid that's not enough. I don't see
> anything wrong with the execution plan. Here is the TKPROF output:
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.34 0.53 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 366.73 659.36 140803 28921647 15
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 367.07 659.89 140803 28921647 15
> 0
>

Wow, almost 30 million logical reads to retrieve nothing. On the other
hand, look at your BCHR: 99.5% Most people would give their firstborn for
such an efficient buffer cache. What makes you think there is a problem?

Seriously, what changed? Something must have changed to bring about such
a drastic drop in performance. What was the execution plan when it ran in
20 seconds?

I'm not saying it has anything to do with the problem, but why is the
outer join S_EVT_ACT.TARGET_PER_ID = S_CONTACT.PAR_ROW_ID (+) repeated 3
times? Twice using alias T20 for S_CONTACT and once using alias T14.

--
What lies behind us and what lies before us are small matters when
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #6 (permalink)  
Old 02-23-2008, 04:17 AM
Daniel Roy
 
Posts: n/a
Default Re: Help interpreting TKPROF output
Thanx guys for your feedback,
even if that didn't provide anything concrete so far. As for your
suggestions to throw Siebel out the window, I perfectly agree with
you, and have been dreaming about this day for about 4 years now
(since I started my initial Siebel training), but I'm afraid it's not
a practical choice career-wise at the moment. The reason there are so
many outer-joins is that, for example, we still want to display an
"activity" (table SIEBEL.S_EVT_ACT T1) even if there is no
"opportunity" (table SIEBEL.S_OPTY T3) associated to it. That seems
reasonable to me. I basically have to accept the data model as it is,
with its strenghts (I'm still looking for them!) and its weaknesses.
What I would like with respect to this statement is to be able to
modify the "driving table" from SIEBEL.S_EVT_ACT (T1) to a smaller. I
realize that the outer joins limit my ability to do this (a table
"outer-joined" can't ever be chosen as the driving table by the RBO),
but I thought I'd ask anyway in case anyone came through the same
painful process as me.

Daniel



> wrote:
>
> >
> >Anyone has an idea as to what might be wrong? Or is there any other
> >tool (besides TKPROF) that they might use to better tune this
> >statement?
>
> 1 The datamodel
> 2 The datamodel
> 3 The datamodel
>
> The statement shows they are outer-joining about everything, so what
> would you expect?
> The best tool to tune this statement is an open window: take the
> statement and throw it out, and Siebel with it.
> I don't think anything can be done about the execution path, or you
> should provide full information on all the indexes on all the tables.
> Apart from throwing the app out of the window, your best bet is to
> throw more iron at the problem.
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #7 (permalink)  
Old 02-23-2008, 04:18 AM
Niall Litchfield
 
Posts: n/a
Default Re: Help interpreting TKPROF output


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Daniel Roy" <danielroy10junk@hotmail.com> wrote in message
news:3722db.0311071254.74f91dfe@posting.google.com ...
> Hi everyone,
> I'm trying to figure out what might have slowed down considerably
> a specific query these past days. I asked for a TKPROF output for only
> this statement, and the relevant sections of it are displayed at the
> bottom of this message. First some background: They use Oracle 8.1.7,
> and RBO (this is enforced by the vendor, Siebel, who digests "new"
> database features like cost-based optimizing VERY slowly). I'm told
> that this query used to run in about 20 seconds, and it takes now 11
> minutes!! I asked if anything to the SQL generated by Siebel got
> modified, and I was told no. (for you SybrandI went through the
> Performance Tuning manual, in order to get some tips on how to
> interpret TKPROF output, but I'm afraid that's not enough. I don't see
> anything wrong with the execution plan. Here is the TKPROF output:
>

At least this part of the sql statement looks, um interesting, to say the
least

select
<somestuff>
FROM

SIEBEL.S_CONTACT T7,
SIEBEL.S_CONTACT T13,
SIEBEL.S_CONTACT T14,
SIEBEL.S_CONTACT T19,
SIEBEL.S_CONTACT T20,
SIEBEL.S_CONTACT T24,
<some other tables snipped>
WHERE
<some stuff including repeating conditions all outer joins>

I can't believe anyone wrote this stuff, it must be from an application
generator or end-user ad-hoc query? Either way i'd be looking for ways to
change the sql statement, this probably means working in new and interesting
ways with the application. I don't know enough about siebel, but would hope
that the datamodel or query tool is amenable to defining queries rather
better.




--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************


Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #8 (permalink)  
Old 02-23-2008, 04:18 AM
Jan van Mourik
 
Posts: n/a
Default Re: Help interpreting TKPROF output
This is one BAD, BAD query! Doesn't make sense to me. Why all the
outer joins? I tried to format it a bit:

<snip>
WHERE
T3.CURR_STG_ID = T8.ROW_ID (+) AND
T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
T1.ASSET_ID = T23.ROW_ID (+) AND
T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND
T1.OPTY_ID = T3.ROW_ID (+) AND
T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
T1.PROJ_ID = T9.ROW_ID (+) AND
T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND
T1.TEMPLATE_ID = T10.ROW_ID (+) AND
T1.OPTY_ID = T11.PAR_ROW_ID (+) AND
T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND
T1.TARGET_PER_ID = T19.ROW_ID (+) AND
T19.PREF_LANG_ID = T25.ROW_ID (+) AND
T23.PROD_ID = T17.ROW_ID (+) AND
T1.ROW_ID = T22.PAR_ROW_ID (+) AND
T1.ROW_ID = T21.PAR_ROW_ID (+) AND
T1.ROW_ID = T18.PAR_ROW_ID (+) AND
T1.OWNER_PER_ID = T5.EMP_ID (+) AND
T1.ROW_ID = T5.ACTIVITY_ID (+) AND
T5.EMP_ID = T12.ROW_ID (+) AND
T5.EMP_ID = T24.PAR_ROW_ID (+) AND
T1.PR_PRDINT_ID = T16.ROW_ID (+) AND
T16.PRDINT_ID = T15.ROW_ID (+) AND
T13.BU_ID = T6.SUB_PARTY_ID (+) AND
( ( (T1.TEMPLATE_FLG != 'Y' AND T1.TEMPLATE_FLG != 'P' OR
T1.TEMPLATE_FLG IS NULL)
AND
(T1.OPTY_ID IS NULL
OR T3.SECURE_FLG = 'N'
OR T1.OPTY_ID
IN (SELECT SQ1_T1.OPTY_ID
FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY
SQ1_T2
, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN
SQ1_T4
WHERE (SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+)
AND SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID
AND SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID
)
AND (SQ1_T3.ROW_ID = '1-29WRP')
)
)
)
AND (T6.PARTY_ID = '1-2J-5235')
AND (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
'1-2L-5407')
AND (T1.APPT_REPT_REPL_CD IS NULL)
)

T1 outer joins to T13
T13 outer joins T6
but then (T6.PARTY_ID = '1-2J-5235')
Why the outer joins if it will be tossed out anyway if null?

What kind of indexes are there on SIEBEL.S_EVT_ACT T1?
You sure some indexes haven't been dropped?

I almost can't believe this query EVER was fast...

jan
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #9 (permalink)  
Old 02-23-2008, 04:18 AM
mcstock
 
Posts: n/a
Default Re: Help interpreting TKPROF output
the full table scan of S_EVT_ACT deep down in the nested loops does not look
very nice...
this table is included as T1 and T10 -- T10 is a self-join to T1, it's
access appears later in the plan

so you're first problem that's evident is that you must have very poor
selectivity for T1 -- this means Oracle's scanning it to get something like
873,290 rows, then joining it S_ASSET's 770,325 rows, then joining to every
other table in the database (well, not every one, but lots of them) to
return 1,745,637 rows before filtering (without indexes) down to 15 rows.

if you examine the non-join conditions on T1 you find (reformatted):

AND ( (
(
t1.template_flg != 'Y'
AND
t1.template_flg != 'P'
OR
t1.template_flg IS NULL
)
AND
(
t1.opty_id IS NULL
OR
t3.secure_flg = 'N'
OR
t1.opty_id IN (
SELECT sq1_t1.opty_id
FROM siebel.s_opty_postn sq1_t1
,siebel.s_party sq1_t2
,siebel.s_contact sq1_t3
,siebel.s_postn sq1_t4
WHERE
(
sq1_t4.pr_emp_id = sq1_t3.par_row_id(+)
AND
sq1_t2.row_id = sq1_t4.par_row_id
AND
sq1_t1.position_id = sq1_t2.row_id
)
AND (sq1_t3.row_id = '1-29WRP')
)
))
AND (t6.party_id = '1-2J-5235')
AND
(
t1.priv_flg = 'N'
OR
t1.priv_flg IS NULL
OR
t1.owner_per_id = '1-2L-5407'
)
AND
(
t1.appt_rept_repl_cd IS NULL
)

which has quite a fiew OR and IS NULL conditions that make it very
understandable why Oracle would not be able to use indexes, but would opt
for a full table scan

i would be really surprised if this same sql statement ever ran in 20
seconds with the same data and same indexes -- do you have anything to
verify the prior case?

anyway, bottom line is that the query needs to be rewritten to have better
selectivity on 'T1', with supporting indexes -- how that needs to be done is
hard to say without understaning the business logic behind this selection
criteria

-- mcs

"Daniel Roy" <danielroy10junk@hotmail.com> wrote in message
news:3722db.0311071254.74f91dfe@posting.google.com ...
> Hi everyone,
> I'm trying to figure out what might have slowed down considerably
> a specific query these past days. I asked for a TKPROF output for only
> this statement, and the relevant sections of it are displayed at the
> bottom of this message. First some background: They use Oracle 8.1.7,
> and RBO (this is enforced by the vendor, Siebel, who digests "new"
> database features like cost-based optimizing VERY slowly). I'm told
> that this query used to run in about 20 seconds, and it takes now 11
> minutes!! I asked if anything to the SQL generated by Siebel got
> modified, and I was told no. (for you SybrandI went through the
> Performance Tuning manual, in order to get some tips on how to
> interpret TKPROF output, but I'm afraid that's not enough. I don't see
> anything wrong with the execution plan. Here is the TKPROF output:
>
>
> TKPROF: Release 8.1.7.3.0 - Production on Tue Nov 4 15:47:30 2003
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Trace file: ora_2998_orpo.trc
> Sort options: default
>
>
************************************************** **************************
****
> count = number of times OCI procedure was executed
> cpu = cpu time in seconds executing
> elapsed = elapsed time in seconds executing
> disk = number of physical reads of buffers from disk
> query = number of buffers gotten for consistent read
> current = number of buffers gotten in current mode (usually for
> update)
> rows = number of rows processed by the fetch or execute call
>
************************************************** **************************
****
>
> select
> T1.LAST_UPD_BY,
> T1.ROW_ID,
> T1.CONFLICT_ID,
> T1.CREATED_BY,
> T1.CREATED,
> T1.LAST_UPD,
> T1.MODIFICATION_NUM,
> T15.NAME,
> T19.FST_NAME,
> T1.TARGET_OU_ADDR_ID,
> T22.LAST_UPD,
> T9.BL_CLASS_CD,
> T1.TODO_CD,
> T1.OWNER_PER_ID,
> T1.PREV_ACT_ID,
> T1.COMMENTS_LONG,
> T1.OWNER_LOGIN,
> T1.APPT_START_DT,
> T4.PLAN_START_DT,
> T1.OWNER_OU_ID,
> T1.PROJ_ITEM_ID,
> T1.TEMPLATE_FLG,
> T21.LAST_UPD,
> T1.X_GM_VEH_MAKE,
> T11.ATTRIB_32,
> T16.PRDINT_ID,
> T1.APPT_REPT_APPT_ID,
> T21.LAST_UPD_BY,
> T3.CREATED,
> T5.ACT_APPT_START_DT,
> T5.ROW_STATUS,
> T4.ACTL_END_DT,
> T20.FST_NAME,
> T1.ME_EVT_ID,
> T22.PAR_ROW_ID,
> T1.APPT_ALARM_TM_MIN,
> T22.ROW_ID,
> T5.ACT_TODO_PLNEND_DT,
> T12.ROW_ID,
> T9.NAME,
> T1.ASGN_MANL_FLG,
> T4.PLAN_END_DT,
> T18.CREATED_BY,
> T1.X_GM_VEH_MOD_YR,
> T3.CURR_STG_ID,
> T21.PAR_ROW_ID,
> T9.PROJ_NUM,
> T7.LAST_NAME,
> T17.MODEL_CD,
> T21.INTEGRATION_ID,
> T1.PCT_COMPLETE,
> T1.EVT_PRIORITY_CD,
> T1.CAL_DISP_FLG,
> T1.PROJ_ID,
> T22.CNTNT_CTG_DB_ID,
> T1.TARGET_OU_ID,
> T12.ROW_ID,
> T1.TEMPLATE_ID,
> T23.SUB_TYPE_CD,
> T22.CREATED,
> T19.LAST_NAME,
> T1.SRA_SR_ID,
> T22.FORMAT_TYPE_CD,
> T1.APPT_REPT_TYPE,
> T1.APPT_REPT_FLG,
> T1.TARGET_PER_ID,
> T23.PROD_ID,
> T22.CONFLICT_ID,
> T3.SRC_TYPE_CD,
> T1.APPT_END_DT,
> T1.DONE_FLG,
> T1.PYMNT_FLG,
> T18.MODIFICATION_NUM,
> T1.PR_PRDINT_ID,
> T24.FST_NAME,
> T1.CREATOR_LOGIN,
> T5.ROW_ID,
> T1.CAL_TYPE_CD,
> T1.OPTY_ID,
> T3.SRC_TYPE_CD,
> T1.X_GM_AUTOCREATED_FLG,
> T14.PR_POSTN_ID,
> T10.NAME,
> T1.PERFRM_BY_OU_ID,
> T5.ACT_TEMPLATE_FLG,
> T1.APPT_REPT_END_DT,
> T1.COMMENTS,
> T1.X_GM_ACT_OUTCOME,
> T20.LAST_NAME,
> T24.LAST_NAME,
> T19.HOME_PH_NUM,
> T22.MODIFICATION_NUM,
> T22.LAST_UPD_BY,
> T17.MODEL_YR,
> T3.X_GM_PURCHASED_MAKE,
> T1.ALARM_FLAG,
> T1.PR_CONTAINER_ID,
> T1.ACCNT_SRC_ID,
> T1.TODO_PLAN_END_DT,
> T18.CREATED,
> T11.ATTRIB_31,
> T18.LAST_UPD,
> T21.CONFLICT_ID,
> T23.ASSET_NUM,
> T2.NAME,
> T3.LEAD_QUALITY_CD,
> T1.ACT_TMPL_ID,
> T17.MAKE_CD,
> T1.EVT_STAT_CD,
> T21.CREATED_BY,
> T3.CURCY_CD,
> T5.ACT_CAL_DISP_FLG,
> T23.EXT_COLOR_CD,
> T1.TODO_AFTER_DT,
> T5.ACT_APPT_REPT_FLG,
> T2.ROW_ID,
> T1.ASGN_SYS_FLG,
> T1.ASSET_ID,
> T5.EMP_ID,
> T1.AGREEMENT_ID,
> T1.PAR_EVT_ID,
> T16.ROW_ID,
> T1.X_GM_VEH_MODEL,
> T1.COST_CURCY_CD,
> T2.LOC,
> T1.PERFRM_BY_PER_ID,
> T1.TARGET_PER_ADDR_ID,
> T18.CURCY_CD,
> T1.TODO_ACTL_START_DT,
> T21.OWN_INST_ID,
> T3.NAME,
> T5.ACT_ALARM_FLG,
> T18.CONFLICT_ID,
> T5.ACT_APPT_RPTEND_DT,
> T3.X_GM_OPTY_TYPE,
> T5.ACT_TODO_PLNSTRTDT,
> T18.ROW_ID,
> T23.ASSET_NUM,
> T25.NAME,
> T1.PR_ATT_ID,
> T1.BILLABLE_FLG,
> T18.LAST_UPD_BY,
> T21.ROW_ID,
> T22.CREATED_BY,
> T1.X_GM_SUB_TYPE,
> T1.APPT_REPT_REPL_CD,
> T7.FST_NAME,
> T1.PR_ORDER_ID,
> T1.TODO_PLAN_START_DT,
> T1.NAME,
> T1.SUBTYPE_CD,
> T3.NAME,
> T19.PREF_LANG_ID,
> T1.APPT_DURATION_MIN,
> T3.X_GM_PURCHASED_MODEL,
> T9.BL_CURCY_CD,
> T16.PRDINT_ID,
> T19.WORK_PH_NUM,
> T4.ACTL_START_DT,
> T1.PR_SYMPTOM_CD,
> T18.PAR_ROW_ID,
> T20.WORK_PH_NUM,
> T1.TODO_ACTL_END_DT,
> T8.NAME,
> T2.BASE_CURCY_CD,
> T1.X_GM_VEH_EXT_COL,
> T21.CREATED,
> T21.MODIFICATION_NUM,
> T22.EMAIL_BODY
> FROM
> SIEBEL.S_EVT_ACT T1,
> SIEBEL.S_ORG_EXT T2,
> SIEBEL.S_OPTY T3,
> SIEBEL.S_PROJITEM T4,
> SIEBEL.S_ACT_EMP T5,
> SIEBEL.S_PARTY_RPT_REL T6,
> SIEBEL.S_CONTACT T7,
> SIEBEL.S_STG T8,
> SIEBEL.S_PROJ T9,
> SIEBEL.S_EVT_ACT T10,
> SIEBEL.S_OPTY_X T11,
> SIEBEL.S_PARTY T12,
> SIEBEL.S_CONTACT T13,
> SIEBEL.S_CONTACT T14,
> SIEBEL.S_PROD_INT T15,
> SIEBEL.S_ACT_PROD_APPL T16,
> SIEBEL.S_PROD_INT T17,
> SIEBEL.S_SRV_ACT T18,
> SIEBEL.S_CONTACT T19,
> SIEBEL.S_CONTACT T20,
> SIEBEL.S_EVT_ACT_SS T21,
> SIEBEL.S_EVT_MAIL T22,
> SIEBEL.S_ASSET T23,
> SIEBEL.S_CONTACT T24,
> SIEBEL.S_LANG T25
> WHERE
> T3.CURR_STG_ID = T8.ROW_ID (+) AND
> T1.OWNER_PER_ID = T13.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T20.PAR_ROW_ID (+) AND
> T1.ASSET_ID = T23.ROW_ID (+) AND
> T1.TARGET_PER_ID = T14.PAR_ROW_ID (+) AND
> T1.OPTY_ID = T3.ROW_ID (+) AND
> T1.TARGET_OU_ID = T2.PAR_ROW_ID (+) AND
> T1.PROJ_ID = T9.ROW_ID (+) AND
> T1.PROJ_ITEM_ID = T4.ROW_ID (+) AND
> T1.TEMPLATE_ID = T10.ROW_ID (+) AND
> T1.OPTY_ID = T11.PAR_ROW_ID (+) AND
> T1.PERFRM_BY_PER_ID = T7.PAR_ROW_ID (+) AND
> T1.TARGET_PER_ID = T19.ROW_ID (+) AND
> T19.PREF_LANG_ID = T25.ROW_ID (+) AND
> T23.PROD_ID = T17.ROW_ID (+) AND
> T1.ROW_ID = T22.PAR_ROW_ID (+) AND
> T1.ROW_ID = T21.PAR_ROW_ID (+) AND
> T1.ROW_ID = T18.PAR_ROW_ID (+) AND
> T1.OWNER_PER_ID = T5.EMP_ID (+) AND
> T1.ROW_ID = T5.ACTIVITY_ID (+) AND
> T5.EMP_ID = T12.ROW_ID (+) AND
> T5.EMP_ID = T24.PAR_ROW_ID (+) AND
> T1.PR_PRDINT_ID = T16.ROW_ID (+) AND
> T16.PRDINT_ID = T15.ROW_ID (+) AND
> T13.BU_ID = T6.SUB_PARTY_ID (+) AND
> (((T1.TEMPLATE_FLG != 'Y' AND
> T1.TEMPLATE_FLG != 'P' OR T1.TEMPLATE_FLG IS NULL) AND
> (T1.OPTY_ID IS NULL OR T3.SECURE_FLG = 'N' OR
> T1.OPTY_ID IN ( SELECT SQ1_T1.OPTY_ID
> FROM SIEBEL.S_OPTY_POSTN SQ1_T1, SIEBEL.S_PARTY
> SQ1_T2, SIEBEL.S_CONTACT SQ1_T3, SIEBEL.S_POSTN SQ1_T4
> WHERE
> (SQ1_T4.PR_EMP_ID = SQ1_T3.PAR_ROW_ID (+) AND
> SQ1_T2.ROW_ID = SQ1_T4.PAR_ROW_ID
> AND
> SQ1_T1.POSITION_ID = SQ1_T2.ROW_ID) AND
> (SQ1_T3.ROW_ID = '1-29WRP')))) AND
> (T6.PARTY_ID = '1-2J-5235') AND
> (T1.PRIV_FLG = 'N' OR T1.PRIV_FLG IS NULL OR T1.OWNER_PER_ID =
> '1-2L-5407') AND
> (T1.APPT_REPT_REPL_CD IS NULL))
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.34 0.53 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 1 366.73 659.36 140803 28921647 15
> 0
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 3 367.07 659.89 140803 28921647 15
> 0
>
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 5 (SYSTEM)
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 0 FILTER
> 1 FILTER
> 1745637 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 NESTED LOOPS OUTER
> 873290 TABLE ACCESS FULL S_EVT_ACT
> 770325 TABLE ACCESS BY INDEX ROWID S_ASSET
> 1643614 INDEX UNIQUE SCAN (object id
> 163988)
> 869748 TABLE ACCESS BY INDEX ROWID
> S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id
> 165128)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165122)
> 2395 TABLE ACCESS BY INDEX ROWID S_LANG
> 875684 INDEX UNIQUE SCAN (object id 168360)
> 770256 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 1643545 INDEX UNIQUE SCAN (object id 170462)
> 0 TABLE ACCESS BY INDEX ROWID
> S_ACT_PROD_APPL
> 873289 INDEX UNIQUE SCAN (object id 163543)
> 0 TABLE ACCESS BY INDEX ROWID S_PROD_INT
> 873289 INDEX UNIQUE SCAN (object id 170462)
> 869748 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1743037 INDEX UNIQUE SCAN (object id 165128)
> 872359 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1745648 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT
> 873289 INDEX UNIQUE SCAN (object id 166898)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJ
> 873289 INDEX UNIQUE SCAN (object id 170716)
> 513307 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1386596 INDEX UNIQUE SCAN (object id 165128)
> 0 TABLE ACCESS BY INDEX ROWID S_PROJITEM
> 873289 INDEX UNIQUE SCAN (object id 170731)
> 839750 TABLE ACCESS BY INDEX ROWID S_OPTY
> 1713039 INDEX UNIQUE SCAN (object id 168882)
> 39271 TABLE ACCESS BY INDEX ROWID S_STG
> 912560 INDEX UNIQUE SCAN (object id 172136)
> 5 TABLE ACCESS BY INDEX ROWID S_ORG_EXT
> 873294 INDEX UNIQUE SCAN (object id 169420)
> 871010 TABLE ACCESS BY INDEX ROWID S_ACT_EMP
> 1747897 INDEX RANGE SCAN (object id 174811)
> 870996 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 1744285 INDEX UNIQUE SCAN (object id 165128)
> 870996 INDEX UNIQUE SCAN (object id 169766)
> 170 TABLE ACCESS BY INDEX ROWID S_EVT_MAIL
> 873459 INDEX RANGE SCAN (object id 167006)
> 0 TABLE ACCESS BY INDEX ROWID S_EVT_ACT_SS
> 873289 INDEX RANGE SCAN (object id 166977)
> 31 TABLE ACCESS BY INDEX ROWID S_SRV_ACT
> 873320 INDEX RANGE SCAN (object id 171966)
> 809443 TABLE ACCESS BY INDEX ROWID S_OPTY_X
> 1682732 INDEX RANGE SCAN (object id 169153)
> 1744696 INDEX RANGE SCAN (object id 169797)
> 0 FILTER
> 0 NESTED LOOPS OUTER
> 0 NESTED LOOPS
> 0 NESTED LOOPS
> 0 INDEX RANGE SCAN (object id 169071)
> 0 INDEX UNIQUE SCAN (object id 169766)
> 0 TABLE ACCESS BY INDEX ROWID S_POSTN
> 0 INDEX UNIQUE SCAN (object id 170114)
> 0 TABLE ACCESS BY INDEX ROWID S_CONTACT
> 0 INDEX UNIQUE SCAN (object id 165128)
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: RULE
> 0 FILTER
> 1 FILTER
> 1745637 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 NESTED LOOPS (OUTER)
> 873290 TABLE ACCESS (FULL) OF 'S_EVT_ACT'
> 770325 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_ASSET'
> 1643614 INDEX (UNIQUE SCAN) OF
> 'S_ASSET_P1' (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF
> 'S_CONTACT_U2' (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF
> 'S_CONTACT_P1' (UNIQUE)
> 2395 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_LANG'
> 875684 INDEX (UNIQUE SCAN) OF 'S_LANG_P1'
> (UNIQUE)
> 770256 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_PROD_INT'
> 1643545 INDEX (UNIQUE SCAN) OF
> 'S_PROD_INT_P1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_ACT_PROD_APPL'
> 873289 INDEX (UNIQUE SCAN) OF
> 'S_ACT_PROD_APPL_P1' (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_PROD_INT'
> 873289 INDEX (UNIQUE SCAN) OF 'S_PROD_INT_P1'
> (UNIQUE)
> 869748 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1743037 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
> (UNIQUE)
> 872359 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_CONTACT'
> 1745648 INDEX (UNIQUE SCAN) OF 'S_CONTACT_U2'
> (UNIQUE)
> 0 TABLE ACCESS (BY INDEX ROWID) OF
> 'S_EVT_ACT'
> 873289 INDEX (UNIQUE SCAN) OF 'S_EVT_ACT_P1'