| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> statement tuning ?
Does anyone have any general recommendations for how to tune this query or
improve how it does what it does in 8174 in RBO mode?
SELECT
T5.POSITION_ID,
T3.ROW_ID,
T11.PR_POSTN_ID,
T11.LAST_UPD,
T1.X_SPECIAL_INFO,
T11.PR_REP_SYS_FLG,
T10.CONFLICT_ID,
T11.DIVISION,
T10.LAST_UPD,
T5.ROW_ID,
T11.DISA_CLEANSE_FLG,
T1.ROW_ID,
T11.PR_SHIP_PER_ID,
T11.CREATED_BY,
T11.X_SUB_TYPE,
T11.LOC,
T1.COUNTY,
T11.PR_REP_DNRM_FLG,
T11.INTEGRATION_ID,
T5.ACCNT_LOC,
T11.PR_MKT_SEG_ID,
T8.NAME,
T11.X_RIS_INTERFACE,
T11.CONFLICT_ID,
T11.PR_ADDR_ID,
T11.MAIN_PH_NUM,
T11.PR_TERR_ID,
T11.PR_REP_MANL_FLG,
T11.PAR_OU_ID,
T11.CURR_PRI_LST_ID,
T11.CREATED,
T11.PR_SRV_AGREE_ID,
T10.CREATED,
T5.ACCNT_NAME,
T11.ROW_ID,
T11.X_PR_MED_AUDIENCE,
T11.DEDUP_TOKEN,
T4.ROW_ID,
T1.ADDR_TYPE_CD,
T11.LOCATION_LEVEL,
T11.PAR_DUNS_NUM,
T11.PR_OU_TYPE_ID,
T11.X_ECC_CUSTOMER_TYPE,
T11.DUNS_NUM,
T11.BU_ID,
T11.BASE_CURCY_CD,
T10.CREATED_BY,
T11.EXPERTISE_CD,
T11.REGION,
T2.LOC,
T11.MAIN_FAX_PH_NUM,
T5.ROW_STATUS,
T11.NAME,
T11.X_PR_CONTACT_ID,
T10.ROW_ID,
T11.X_MANAGED_FLG,
T11.MODIFICATION_NUM,
T4.NAME,
T11.ECC_TYPE,
T1.ADDR,
T11.PR_INDUST_ID,
T10.PAR_ROW_ID,
T3.ROW_ID,
T11.URL,
T7.NAME,
T9.LOGIN,
T11.VAT_REGN_NUM,
T10.ATTRIB_07,
T5.ASGN_SYS_FLG,
T1.COUNTRY,
T2.NAME,
T11.PR_BL_ADDR_ID,
T11.PR_REP_ASGN_TYPE,
T11.CUST_STAT_CD,
T11.LAST_UPD_BY,
T1.ZIPCODE,
T1.STATE,
T11.PR_BL_PER_ID,
T6.ROW_ID,
T8.ROW_ID,
T1.CITY,
T11.OU_TYPE_CD,
T11.PR_SYN_ID,
T5.ASGN_DNRM_FLG,
T11.X_PR_MED_SUBJECT,
T6.NAME,
T11.PR_SHIP_ADDR_ID,
T10.MODIFICATION_NUM,
T3.PR_EMP_ID,
T10.LAST_UPD_BY,
T11.ASGN_USR_EXCLD_FLG,
T5.ASGN_MANL_FLG,
T3.NAME
FROM
SIEBEL.S_ADDR_ORG T1,
SIEBEL.S_ORG_EXT T2,
SIEBEL.S_POSTN T3,
SIEBEL.S_ORG_SYN T4,
SIEBEL.S_ACCNT_POSTN T5,
SIEBEL.S_ASGN_GRP T6,
SIEBEL.S_ORG_INT T7,
SIEBEL.S_INDUST T8,
SIEBEL.S_EMPLOYEE T9,
SIEBEL.S_ORG_EXT_X T10,
SIEBEL.S_ORG_EXT T11
WHERE
T11.BU_ID = T7.ROW_ID (+) AND
T11.PAR_OU_ID = T2.ROW_ID (+) AND
T11.ROW_ID = T10.PAR_ROW_ID (+) AND
T11.PR_INDUST_ID = T8.ROW_ID (+) AND
T11.PR_SYN_ID = T4.ROW_ID (+) AND
T11.PR_ADDR_ID = T1.ROW_ID (+) AND
T11.PR_TERR_ID = T6.ROW_ID (+) AND
T11.PR_POSTN_ID = T3.ROW_ID AND
T11.PR_POSTN_ID = T5.POSITION_ID AND T11.ROW_ID = T5.OU_EXT_ID AND
T3.PR_EMP_ID = T9.ROW_ID (+) AND
((T11.LOC = 'WGQIWG' OR T11.LOC = 'WGAIWG' OR T11.LOC = 'WGFIWG' OR
T11.LOC LIKE '%COUNCIL') AND T11.CUST_STAT_CD = 'Active')
ORDER BY
T11.NAME, T11.LOC
16 rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=RULE 1 0 NESTED LOOPS (OUTER)
2 1 NESTED LOOPS 3 2 NESTED LOOPS (OUTER) 4 3 NESTED LOOPS (OUTER) 5 4 NESTED LOOPS (OUTER) 6 5 NESTED LOOPS 7 6 NESTED LOOPS (OUTER) 8 7 NESTED LOOPS (OUTER) 9 8 NESTED LOOPS (OUTER) 10 9 NESTED LOOPS (OUTER) 11 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' 12 11 INDEX (FULL SCAN) OF 'S_ORG_EXT_U1'(UNIQUE) 13 10 TABLE ACCESS (BY INDEX ROWID) OF 'S_INDUST' 14 13 INDEX (UNIQUE SCAN) OF 'S_INDUST_P1'(UNIQUE) 15 9 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_INT' 16 15 INDEX (UNIQUE SCAN) OF 'S_ORG_INT_P1'(UNIQUE) 17 8 TABLE ACCESS (BY INDEX ROWID) OF 'S_ASGN_GRP' 18 17 INDEX (UNIQUE SCAN) OF 'S_ASGN_GRP_P1' (UNIQUE) 19 7 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_SYN' 20 19 INDEX (UNIQUE SCAN) OF 'S_ORG_SYN_P1' (UNIQUE) 21 6 TABLE ACCESS (BY INDEX ROWID) OF 'S_POSTN' 22 21 INDEX (UNIQUE SCAN) OF 'S_POSTN_P1' (UNIQUE) 23 5 TABLE ACCESS (BY INDEX ROWID) OF 'S_EMPLOYEE' 24 23 INDEX (UNIQUE SCAN) OF 'S_EMPLOYEE_P1' (UNIQUE) 25 4 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' 26 25 INDEX (UNIQUE SCAN) OF 'S_ORG_EXT_P1' (UNIQUE) 27 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ADDR_ORG' 28 27 INDEX (UNIQUE SCAN) OF 'S_ADDR_ORG_P1' (UNIQUE) 29 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ACCNT_POSTN' 30 29 INDEX (RANGE SCAN) OF 'S_ACCNT_POSTN_U1' (UNIQUE) 31 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT_X' 32 31 INDEX (RANGE SCAN) OF 'S_ORG_EXT_X_U1' (UNIQUE)Statistics
0 recursive calls
0 db block gets
731716 consistent gets
5505 physical reads
0 redo size
12214 bytes sent via SQL*Net to client
1468 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Fri Apr 30 2004 - 14:48:12 CDT
-----------------------------------------------------------------
 |
 |