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 Go to next message
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 #571575 is a reply to message #571573] Tue, 27 November 2012 04:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nothing can be done.

Regards
Michel
Re: Query rewrite - high temp utilization [message #571577 is a reply to message #571573] Tue, 27 November 2012 04:17 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Query rewrite - high temp utilization [message #571581 is a reply to message #571577] Tue, 27 November 2012 05:16 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given the query, 23 tables joined by 22 outer joins, the only thing to do is to remove
the ORDER BY clause which is the only step that requires temporary space.

Regards
Michel
Previous Topic: How to capture the problem with some sql timeout
Next Topic: Tune the materialized view
Goto Forum:
  


Current Time: Fri Mar 29 17:31:38 CDT 2024