| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: statement tuning ?
This is a Siebel query, is it not?
It's really not that bad, all things considered.
Officially, the vendor's policy is that these are not to be changed, however, I've had some success using outlines to elicit different behaviors for queries of this type.
Try entering an outline with the USE_HASH index hint.
Michael Fontana
Sr. DBA
NTT/Verio
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Green
Sent: Friday, April 30, 2004 2:51 PM
To: oracle-l_at_freelists.org
Subject: 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) OFStatistics
'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)
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.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Apr 30 2004 - 15:29:57 CDT
 |
 |