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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Green
Sent: Friday, April 30, 2004 2:51 PM
To: oracle-l@xxxxxxxxxxxxx
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) 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
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx 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
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: