Home » RDBMS Server » Performance Tuning » Need Assistance in Fine tuning the SQL
Need Assistance in Fine tuning the SQL [message #352304] Tue, 07 October 2008 00:50 Go to next message
saurav.raj
Messages: 8
Registered: May 2008
Location: BANGALORE
Junior Member

Hi

i need a help in doing fine tuning in the attached SQL.
  • Attachment: sql_1.txt
    (Size: 3.67KB, Downloaded 264 times)
Re: Need Assistance in Fine tuning the SQL [message #352330 is a reply to message #352304] Tue, 07 October 2008 02:51 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here is the SQL for everyone's convenience:
SELECT
			'"Default Organization" IS NEEDED FOR YOUR DIVISION AND POSITION HIERARCHY TO OPERATE PROPERLY BUT THE "Default Organization" DOES NOT EXIST IN YOUR SYSTEM.' AS Error_message
		FROM
			SIEBEL.S_ADDR_PER T1,
			SIEBEL.S_PARTY T2,
			SIEBEL.S_POSTN T3,
			SIEBEL.S_ORG_EXT T4,
			SIEBEL.S_CON_ADDR T5,
			SIEBEL.S_ORG_EXT T6,
			SIEBEL.S_ORG_EXT T7,
			SIEBEL.S_BU T8,
			SIEBEL.S_ORG_EXT T9
		WHERE
			T9.PAR_BU_ID = T6.PAR_ROW_ID (+) AND
			T9.PAR_DIVN_ID = T7.PAR_ROW_ID (+) AND
			T2.PAR_PARTY_ID = T4.PAR_ROW_ID (+) AND
			T9.PR_MGR_POSTN_ID = T3.PAR_ROW_ID (+) AND
			T2.ROW_ID = T9.PAR_ROW_ID AND
			T2.ROW_ID = T8.PAR_ROW_ID (+) AND
			T9.PR_ADDR_ID = T5.ADDR_PER_ID (+) AND
			T9.ROW_ID = T5.ACCNT_ID (+) AND
			T9.PR_ADDR_ID = T1.ROW_ID (+) AND
			(T9.INT_ORG_FLG = 'Y') AND
			UPPER(T9.NAME) = 'DEFAULT ORGANIZATION'
			GROUP BY T9.ROW_ID HAVING COUNT(T9.ROW_ID)=0
UNION
SELECT
 'YOUR SYSTEM CONTAINS MORE THAN ONE "Default Organization". THIS MAY CAUSE YOUR DIVISION AND POSITION HIERARCHY NOT TO OPERATE PROPERLY.' AS error_message
FROM
			SIEBEL.S_ADDR_PER T1,
			SIEBEL.S_PARTY T2,
			SIEBEL.S_POSTN T3,
			SIEBEL.S_ORG_EXT T4,
			SIEBEL.S_CON_ADDR T5,
			SIEBEL.S_ORG_EXT T6,
			SIEBEL.S_ORG_EXT T7,
			SIEBEL.S_BU T8,
			SIEBEL.S_ORG_EXT T9
		WHERE
			T9.PAR_BU_ID = T6.PAR_ROW_ID (+) AND
			T9.PAR_DIVN_ID = T7.PAR_ROW_ID (+) AND
			T2.PAR_PARTY_ID = T4.PAR_ROW_ID (+) AND
			T9.PR_MGR_POSTN_ID = T3.PAR_ROW_ID (+) AND
			T2.ROW_ID = T9.PAR_ROW_ID AND
			T2.ROW_ID = T8.PAR_ROW_ID (+) AND
			T9.PR_ADDR_ID = T5.ADDR_PER_ID (+) AND
			T9.ROW_ID = T5.ACCNT_ID (+) AND
			T9.PR_ADDR_ID = T1.ROW_ID (+) AND
			(T9.INT_ORG_FLG = 'Y') AND
			UPPER(T9.NAME) = 'DEFAULT ORGANIZATION'
			GROUP BY T9.NAME HAVING COUNT(T9.NAME)>1
UNION
SELECT	DISTINCT
			'THE ROW_ID OF THE "Default Organization" IN YOUR SYSTEM IS'||' '||CHR(39)||T9.ROW_ID||CHR(39)||'.'||' '||'THIS IS INCORRECT AND SHOULD BE'||' '||CHR(39)||'0-R9NH'||CHR(39)||'.'||' '||'THIS ERROR MAY CAUSE YOUR DIVISION AND POSITION HIERARCHY NOT TO OPERATE PROPERLY.' AS error_message
		FROM
			SIEBEL.S_ADDR_PER T1,
			SIEBEL.S_PARTY T2,
			SIEBEL.S_POSTN T3,
			SIEBEL.S_ORG_EXT T4,
			SIEBEL.S_CON_ADDR T5,
			SIEBEL.S_ORG_EXT T6,
			SIEBEL.S_ORG_EXT T7,
			SIEBEL.S_BU T8,
			SIEBEL.S_ORG_EXT T9
		WHERE
			T9.PAR_BU_ID = T6.PAR_ROW_ID (+) AND
			T9.PAR_DIVN_ID = T7.PAR_ROW_ID (+) AND
			T2.PAR_PARTY_ID = T4.PAR_ROW_ID (+) AND
			T9.PR_MGR_POSTN_ID = T3.PAR_ROW_ID (+) AND
			T2.ROW_ID = T9.PAR_ROW_ID AND
			T2.ROW_ID = T8.PAR_ROW_ID (+) AND
			T9.PR_ADDR_ID = T5.ADDR_PER_ID (+) AND
			T9.ROW_ID = T5.ACCNT_ID (+) AND
			T9.PR_ADDR_ID = T1.ROW_ID (+) AND
			(T9.INT_ORG_FLG = 'Y') AND
			UPPER(T9.NAME) = 'DEFAULT ORGANIZATION' AND
			T9.ROW_ID <>'0-R9NH'
UNION
SELECT
			'DIVISION NAMED'||' '||CHR(39)||T9.NAME||CHR(39)||' '||'HAS THE VALUE OF ITS "ORGANIZATION" FIELD AS'||' '||CHR(39)||T6.NAME||CHR(39)||'.'||' '||'THIS IS INCORRECT AND ITS ORGANIZATION FIELD VALUE SHOULD BE "Default Organization".' AS errro_message
		FROM
			SIEBEL.S_ADDR_PER T1,
			SIEBEL.S_PARTY T2,
			SIEBEL.S_POSTN T3,
			SIEBEL.S_ORG_EXT T4,
			SIEBEL.S_CON_ADDR T5,
			SIEBEL.S_ORG_EXT T6,
			SIEBEL.S_ORG_EXT T7,
			SIEBEL.S_BU T8,
			SIEBEL.S_ORG_EXT T9
		WHERE
			T9.PAR_BU_ID = T6.PAR_ROW_ID AND
			T9.PAR_DIVN_ID = T7.PAR_ROW_ID (+) AND
			T2.PAR_PARTY_ID = T4.PAR_ROW_ID (+) AND
			T9.PR_MGR_POSTN_ID = T3.PAR_ROW_ID (+) AND
			T2.ROW_ID = T9.PAR_ROW_ID AND
			T2.ROW_ID = T8.PAR_ROW_ID (+) AND
			T9.PR_ADDR_ID = T5.ADDR_PER_ID (+) AND
			T9.ROW_ID = T5.ACCNT_ID (+) AND
			T9.PR_ADDR_ID = T1.ROW_ID (+) AND
			(T9.INT_ORG_FLG = 'Y')
		AND
			UPPER(T6.NAME) <> 'DEFAULT ORGANIZATION';

Could you please run a SQL TRACE of the SQL and post the TKPROF output (instructions here)

At first glance, you are joining a lot of tables in an unfiltered manner (most rows included from most tables). I would be hoping to see a lot of FULL TABLE SCANS and HASH JOINS in the plan.

Also consider replacing UNION with UNION ALL. UNION will remove duplicates, but it requires a sort (slow) to do so. If you NEED to remove duplicates, ignore this.

Ross Leishman

Re: Need Assistance in Fine tuning the SQL [message #352386 is a reply to message #352304] Tue, 07 October 2008 07:43 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try replaceing 2 first parts of UNION with a singe statement:

SELECT
  CASE WHEN CNTR  = 0 THEN '"Default Organization" IS NEEDED FOR YOUR DIVISION AND POSITION HIERARCHY TO OPERATE PROPERLY BUT THE "Default Organization" DOES NOT EXIST IN YOUR SYSTEM.'
       ELSE 'YOUR SYSTEM CONTAINS MORE THAN ONE "Default Organization". THIS MAY CAUSE YOUR DIVISION AND POSITION HIERARCHY NOT TO OPERATE PROPERLY.'
  END AS ERROR_MESSAGE
FROM (SELECT COUNT(*) CNTR
      FROM 
        SIEBEL.S_ORG_EXT T9, SIEBEL.S_PARTY T2
        WHERE (T9.INT_ORG_FLG = 'Y') AND UPPER(T9.NAME) = 'DEFAULT ORGANIZATION'
           AND T2.ROW_ID = T9.PAR_ROW_ID
        HAVING ( CNTR(*) = 0 OR CNTR(*) > 1 )


1. It makes only a single pass over SIEBEL.S_ORG_EXT table.
2. It does NOT access any UN-needed tables.
3. If NAME column is selective then consider using
function base index ( CREATE INDEX ... ON SIEBEL.S_ORG_EXT ( UPPER(NAME), INT_ORG_FLG ) ... ).

4. Remove all un-needed table references fromthe 3-rd part of your SQL as well.

HTH.
Michael

[Updated on: Tue, 07 October 2008 07:44]

Report message to a moderator

Previous Topic: Fine tuning
Next Topic: How to tune UNION
Goto Forum:
  


Current Time: Wed Dec 07 10:27:23 CST 2016

Total time taken to generate the page: 0.10733 seconds