Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help interpreting TKPROF output

Re: Help interpreting TKPROF output

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 9 Nov 2003 18:20:30 -0000
Message-ID: <bom0k4$l7a$1$8300dec7@news.demon.co.uk>

Since the RBO can't change plans according to the data content, someone is not telling you the whole truth.

Judging from the current plan, with it's minimal elimination of data and wealth of outer joins, I'd have to say that the only change that could take you from 20 seconds to 11 minutes without a change in the code or indexing is an increase by a factor of 33 in the data returned from T! (SIEBEL.S_EVT_ACT). Is there any reason why the data size could have grown - is there a code change elsewhere that has allowed more data to get through the conditions

    > (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) Other options -

    Something has changed some indexes.

    Something has re-arranged the order of     tables in the where clause.

Look particularly at T6 (SIEBEL.S_PARTY_RPT_REL) which is the only other table with an eliminating condition, which currently is applied almost at the end of the plan.

One thing that puzzles me - the query has fetched 15 rows, but the stats show a zero row count in the first line of the Row Source Operation. The two results don't seem to be compatible.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Daniel Roy" <danielroy10junk_at_hotmail.com> wrote in message
news:3722db.0311071254.74f91dfe_at_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 Sybrand:)I 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
Received on Sun Nov 09 2003 - 12:20:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US