Home » RDBMS Server » Performance Tuning » Query rewrite - high temp utilization (Oracle, 8.1.7.4.0, sunOS)
| Query rewrite - high temp utilization [message #571573] |
Tue, 27 November 2012 04:09  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
The below query is utilizing more than 17 Gb temp space. But still it is getting failed out due to insufficient temp space. is there any way to rewrite this query to reduce the temp utilization?
SELECT T12.FRGHT_AMT_CURCY_CD,T23.LAST_UPD,T11.PAR_OU_ID,T9.MAIN_PH_NUM,T23.DISCNT_PERCENT,T23.X_ERROR_NUM,T18.ADDR,T14.X_ECO_B_END_1141,
T14.X_ECO_A_END_1141,T9.X_ECO_VALIDATION_FLG,T23.X_ECO_ERR_DESCR,T14.ASSET_NUM,T20.NAME,T23.X_ECO_REASON2,T14.X_ECO_B_END_ID,
T14.ASSET_NUM,T14.X_ECO_B_END_IWPC,T23.X_AE_CON_PH_NUM,T23.SHIP_ADDR_ID,T19.NAME,T23.X_BE_CON_LST_NAME,T23.CREATED_BY,T23.X_ECO_LOCATION,T8.LOC,
T3.MODIFICATION_NUM,T10.INTEGRATION_ID,T23.INTEGRATION_ID,T23.X_MESSAGE,T9.PR_ADDR_ID,T12.ACCNT_ID,T23.X_BEARERNO,T23.X_SUB_STATUS_CD,
T23.X_ERROR,T14.X_ECO_A_END_1141,T23.CONFLICT_ID,T23.X_BE_CON_FST_NAME,T8.MAIN_PH_NUM,T13.PROVINCE,T23.X_COSMOSS_ID,T23.X_BE_ZIPCODE,
T8.X_SITE_USAGE_CD,T23.CREATED,T23.REQ_SHIP_DT,T23.X_ECO_PICK_A_SITE_ID,T23.X_AE_CON_TITLE,T3.CREATED,T3.X_START_DIRECTORY_TYPE,
T3.X_AMEND_COUNTER,T23.ROW_ID,T23.X_AE_CON_FST_NAME,T23.X_ECO_FLOOR,T9.NAME,T23.X_ECO_SITE_ID,T23.X_ECO_CUST_CIR_REF,T23.X_ECO_ORDER_PRODUCT_FAMILY,
T3.LAST_UPD_BY,T23.X_ECO_MUST_COMPLETE_BY_DT,T8.X_ECO_VALIDATION_FLG,T23.X_ECO_QMON_ORDER_VAL_DT,T23.X_ECO_FASTTRACK_FLAG,T14.X_ECO_CIRCUIT_VLD_FLG,
T23.X_ECO_NUMBER_AMENDS,T23.X_QTY,T7.PART_NUM,T1.PR_ADDR_ID,T23.SVCD_PROD_ID,T19.NAME,T8.X_CUST_SITE_ID,T23.SHIP_CON_ID,T23.X_B_END_1141_CD,
T3.X_START_DIRECTORY_ENTRY,T23.X_DESC_TEXT,T23.X_ECO_PICK_B_SITE_ID,T12.X_YOUR_REF,T23.X_BE_CON_PH_NUM,T3.ATTRIB_35,T8.X_SITE_USAGE_CD,
T10.ITEM_SIZE,T21.LOC,T23.X_BE_CON_TITLE,T8.NAME,T23.X_QMON_FLG,T23.LINE_NOTE,T22.PR_ADDR_ID,T6.LOC,T23.X_BULK_VAL_FLAG,T14.X_ECO_A_END_IWPC,
T23.PROD_ID,T23.X_ECO_ADDR_FREE_TEXT,T14.X_A_END_SWITCH_CODE,T23.ASSET_ID,T23.X_ECO_LI_API_ERR_MSG,T23.X_SEND_EMAIL,T23.SHIP_OU_ID,
T9.LOC,T23.X_PROD_NAME,T23.MODIFICATION_NUM,T7.PROD_ATTRIB02_CD,T14.X_ECO_B_END_1141,T10.UOM_CD,T15.X_ORDERTYPE_GROUP,T23.DISCNT_METH_CD,
T3.CREATED_BY,T23.X_ECO_AC_POWER,T9.X_SITE_USAGE_CD,T12.BLOCK_DLVRY_CD,T3.ATTRIB_44,T23.PRICING_COMMENT,T12.ORDER_NUM,T23.VOL_UPSELL_MSG_TXT, T23.X_A_END_1141_CD,T3.X_START_TAKEOVER_TIME,T23.STATUS_CD,T23.X_AE_ON_SITE_LOC,T8.PR_ADDR_ID,T3.ROW_ID,T2.PROVINCE,T4.ATTRIB_03,
T23.X_ECO_LOCATION2,T14.PROD_ID,T23.PROD_STATUS_CD,T23.X_ECO_STATUS,T23.X_ECO_FLOOR2,T16.PROVINCE,T23.X_BE_ON_SITE_LOC,T23.TAX_EXEMPT_NUM,
T9.LOC,T9.X_CUST_SITE_ID,T15.X_ORDERTYPE_NAME,T23.X_ECO_MOVE_A_B,T23.X_ECO_INTERFACE_NAME,T3.CONFLICT_ID,T23.ORDER_ID,T23.CRSE_OFFR_ID,
T3.LAST_UPD,T18.PROVINCE,T10.PART_NUM,T23.X_ENGG_NOTE,T3.PAR_ROW_ID,T12.X_ECO_INTEGRATION_ID,T12.ORDER_TYPE_ID,T23.X_ECO_GRID_REF,T23.SRC_INVLOC_ID,
T23.QTY_REQ,T23.ACT_PRDINT_ID,T23.LAST_UPD_BY,T7.PAR_PROD_INT_ID,T7.NAME,T12.X_ECO_PROD_ID,T23.X_BILL_OU_ID,T11.NAME,T23.X_ECO_DCODE,T5.PAR_PROD_INT_ID,
T23.X_MESSAGE_CODE,T8.X_CUST_SITE_ID,T23.X_APPOINMENT_FLG,T23.LN_NUM,T14.X_B_END_SWITCH_CODE,T23.X_CTR_DEL_DT,T9.NAME,T17.ACTIVITY_ID,
T23.X_ECO_DCODE_LEAD_TM,T23.X_ERROR_MESS,T23.TAX_EXEMPT_FLG,T23.X_ECO_ERR_STATUS,T23.X_RECOVER_EQUIP_FLG,T14.X_ECO_A_END_ID,T3.X_ACCEPT_REJECT,
T23.X_ECO_DISC_REC_END,T23.X_AE_CON_LST_NAME
FROM SIEBEL.S_ORG_EXT T1,
SIEBEL.S_ADDR_ORG T2,
SIEBEL.S_ORDER_ITEM_X T3,
SIEBEL.S_EMPLOYEE_X T4,
SIEBEL.S_PROD_INT T5,
SIEBEL.S_ORG_EXT T6,
SIEBEL.S_PROD_INT T7,
SIEBEL.S_ORG_EXT T8,
SIEBEL.S_ORG_EXT T9,
SIEBEL.S_PROD_INT T10,
SIEBEL.S_ORG_EXT T11,
SIEBEL.S_ORDER T12,
SIEBEL.S_ADDR_ORG T13,
SIEBEL.S_ASSET T14,
SIEBEL.S_ORDER_TYPE T15,
SIEBEL.S_ADDR_ORG T16,
SIEBEL.S_ACT_PRDINT T17,
SIEBEL.S_ADDR_ORG T18,
SIEBEL.S_PROD_INT T19,
SIEBEL.S_INVLOC T20,
SIEBEL.S_ORG_EXT T21,
SIEBEL.S_ORG_EXT T22,
SIEBEL.S_BATCH_INFO T23
WHERE T8.PR_ADDR_ID=T2.ROW_ID(+) AND
T14.PROD_ID=T7.ROW_ID(+) AND
T9.PR_ADDR_ID=T16.ROW_ID(+) AND
T23.SVCD_PROD_ID=T19.ROW_ID(+) AND
T14.X_ECO_A_END_ID=T22.ROW_ID(+) AND
T22.PR_ADDR_ID=T18.ROW_ID(+) AND
T14.X_ECO_B_END_ID=T1.ROW_ID(+) AND
T1.PR_ADDR_ID=T13.ROW_ID(+) AND
T12.ACCNT_ID=T6.ROW_ID(+) AND
T23.SHIP_OU_ID=T11.ROW_ID(+) AND
T23.X_ECO_PICK_A_SITE_ID=T8.ROW_ID(+) AND
T23.X_ECO_PICK_B_SITE_ID=T9.ROW_ID(+) AND
T12.X_ECO_PROD_ID=T5.ROW_ID(+) AND
T23.ACT_PRDINT_ID=T17.ROW_ID(+) AND
T23.ASSET_ID=T14.ROW_ID(+) AND
T23.CREATED_BY=T4.ROW_ID(+) AND
T23.ORDER_ID=T12.ROW_ID(+) AND
T12.ORDER_TYPE_ID=T15.ROW_ID AND
T23.X_BILL_OU_ID=T21.ROW_ID(+) AND
T23.PROD_ID=T10.ROW_ID(+) AND
T23.SRC_INVLOC_ID=T20.ROW_ID(+) AND
T23.ROW_ID=T3.PAR_ROW_ID(+)
ORDERBY T23.LN_NUM;
SQL> select table_name,owner,last_analyzed,num_rows from dba_tables where table_name='S_ORDER_ITEM';
TABLE_NAME OWNER LAST_ANAL NUM_ROWS
------------------------------ ------------------------------ --------- ----------
S_BATCH_INFO SIEBEL 26-NOV-12 2453386
OPERATION OPTIONS OBJECT_NOD OBJECT_NAME OBJECT_TYP OPTIMIZER SEARCH_COLUMNS ID COST BYTES
------------------------------ ---------------- ---------- ------------------ ---------- ------------ -------------- ---------- ---------- ----------
SELECT STATEMENT RULE 0
SORT ORDER BY 1
NESTED LOOPS OUTER 2
NESTED LOOPS OUTER 3
NESTED LOOPS OUTER 4
NESTED LOOPS OUTER 5
NESTED LOOPS OUTER 6
NESTED LOOPS OUTER 7
NESTED LOOPS OUTER 8
NESTED LOOPS OUTER 9
NESTED LOOPS OUTER 10
NESTED LOOPS OUTER 11
NESTED LOOPS OUTER 12
NESTED LOOPS OUTER 13
NESTED LOOPS OUTER 14
NESTED LOOPS 15
NESTED LOOPS OUTER 16
NESTED LOOPS OUTER 17
NESTED LOOPS OUTER 18
NESTED LOOPS OUTER 19
NESTED LOOPS OUTER 20
NESTED LOOPS OUTER 21
NESTED LOOPS OUTER 22
NESTED LOOPS OUTER 23
TABLE ACCESS FULL S_BATCH_INFO ANALYZED 24
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 25
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 26
TABLE ACCESS BY INDEX ROWID S_INVLOC ANALYZED 27
INDEX UNIQUE SCAN S_INVLOC_P1 UNIQUE ANALYZED 28
TABLE ACCESS BY INDEX ROWID S_PROD_INT ANALYZED 29
INDEX UNIQUE SCAN S_PROD_INT_P1 UNIQUE ANALYZED 30
TABLE ACCESS BY INDEX ROWID S_ACT_PRDINT ANALYZED 31
INDEX UNIQUE SCAN S_ACT_PRDINT_P1 UNIQUE ANALYZED 32
TABLE ACCESS BY INDEX ROWID S_ASSET ANALYZED 33
INDEX UNIQUE SCAN S_ASSET_P1 UNIQUE ANALYZED 34
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 35
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 36
TABLE ACCESS BY INDEX ROWID S_ADDR_ORG ANALYZED 37
INDEX UNIQUE SCAN S_ADDR_ORG_P1 UNIQUE ANALYZED 38
TABLE ACCESS BY INDEX ROWID S_ORDER ANALYZED 39
INDEX UNIQUE SCAN S_ORDER_P1 UNIQUE ANALYZED 40
TABLE ACCESS BY INDEX ROWID S_ORDER_TYPE ANALYZED 41
INDEX UNIQUE SCAN S_ORDER_TYPE_P1 UNIQUE ANALYZED 42
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 43
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 44
TABLE ACCESS BY INDEX ROWID S_PROD_INT ANALYZED 45
INDEX UNIQUE SCAN S_PROD_INT_P1 UNIQUE ANALYZED 46
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 47
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 48
TABLE ACCESS BY INDEX ROWID S_ADDR_ORG ANALYZED 49
INDEX UNIQUE SCAN S_ADDR_ORG_P1 UNIQUE ANALYZED 50
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 51
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 52
TABLE ACCESS BY INDEX ROWID S_PROD_INT ANALYZED 53
INDEX UNIQUE SCAN S_PROD_INT_P1 UNIQUE ANALYZED 54
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 55
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 56
TABLE ACCESS BY INDEX ROWID S_PROD_INT ANALYZED 57
INDEX UNIQUE SCAN S_PROD_INT_P1 UNIQUE ANALYZED 58
TABLE ACCESS BY INDEX ROWID S_EMPLOYEE_X ANALYZED 59
INDEX UNIQUE SCAN S_EMPLOYEE_X_P1 UNIQUE ANALYZED 60
TABLE ACCESS BY INDEX ROWID S_ADDR_ORG ANALYZED 61
INDEX UNIQUE SCAN S_ADDR_ORG_P1 UNIQUE ANALYZED 62
TABLE ACCESS BY INDEX ROWID S_ORG_EXT ANALYZED 63
INDEX UNIQUE SCAN S_ORG_EXT_P1 UNIQUE ANALYZED 64
TABLE ACCESS BY INDEX ROWID S_ADDR_ORG ANALYZED 65
INDEX UNIQUE SCAN S_ADDR_ORG_P1 UNIQUE ANALYZED 66
TABLE ACCESS BY INDEX ROWID S_ORDER_ITEM_X ANALYZED 67
INDEX RANGE SCAN S_ORDER_ITEM_X_U1 UNIQUE ANALYZED 68 |
|
|
|
|
|
| Re: Query rewrite - high temp utilization [message #571577 is a reply to message #571573] |
Tue, 27 November 2012 04:17  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You appear to be using the rule based optimizer, which is very twentieth century
and means that analyzing the tables has no effect. Set the optimizer_mode to ALL_ROWS,
then try again - and please generate the execution plan in a way that makes
it readable, use DBMS_XPLAN to format it.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 17:13:16 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
