Home » RDBMS Server » Performance Tuning » How to tune my sql script
How to tune my sql script [message #228412] Mon, 02 April 2007 23:39 Go to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
please tell me how i tune this sql file which is in attachment.
Re: How to tune my sql script [message #228434 is a reply to message #228412] Tue, 03 April 2007 01:07 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Dont have MS Word and I never will.
Re: How to tune my sql script [message #228445 is a reply to message #228412] Tue, 03 April 2007 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

  1. Never use WHEN OTHERS THEN without raising an error
  2. Use Temporary tables (GTT) instead of truncating permanent tables
  3. For more send me your wages

Regards
Michel
Re: How to tune my sql script [message #228455 is a reply to message #228434] Tue, 03 April 2007 02:08 Go to previous messageGo to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
hi tahpush ,

i'm sending same file in text format please check it out.
Re: How to tune my sql script [message #228456 is a reply to message #228412] Tue, 03 April 2007 02:10 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Why that code wasn't removed yet:

---Temp---Pls remove 2005-July-20
	update SIEBEL.CBR_PROD_BAL set eod_bal=999999999999
	WHERE eod_bal>9999999999999;
	---End emove block
???
Re: How to tune my sql script [message #228514 is a reply to message #228456] Tue, 03 April 2007 04:43 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Memory:

What is the expected time in which this procedure should finish and what is the actual time that it takes?

One thing that may help is run your statement that creates the main cursor, in a sql prompt and check whether this uses the indexes or not.


SELECT ATTRIB_03  FROM SIEBEL.S_CONTACT_FNXM WHERE ATTRIB_06=V_ATTRIB_06 AND LENGTH(ATTRIB_03) =V_ASSET_LENGTH)
		MINUS
		(SELECT ASSET.ASSET_NUM PID  FROM SIEBEL.S_ASSET ASSET, SIEBEL.S_PROD_INT PROD
		WHERE ASSET.PROD_ID=PROD.ROW_ID AND 
		PROD.DETAIL_TYPE_CD IN(V_PRODUCT_TYPE_SVNG,V_PRODUCT_TYPE_TD)
		AND LENGTH(ASSET.ASSET_NUM) =V_ASSET_LENGTH))
--run explain plan for this statement and check its output.

Similarly check the explain plans for the update statements like:
UPDATE SIEBEL.CBR_PROD_BAL  SET  DAT_LAST_MNT = V_MAX_FILE_DATE
				WHERE COD_CUST_NATL_ID = tblMissingPID(nRowCounter);
 are they using indexes are doing full table scans?

One more thing in the code is:

[B]SET TRANSACTION USE ROLLBACK SEGMENT SBP_BIG_RBS;[/B]

Instead of using RBS , consider using Undo tablespace.



Re: How to tune my sql script [message #228550 is a reply to message #228514] Tue, 03 April 2007 06:11 Go to previous messageGo to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
we r using oracle 8i not 9i?
how to use explain plan in toad?
Re: How to tune my sql script [message #228555 is a reply to message #228550] Tue, 03 April 2007 06:15 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Using 8i is not good...consider going to 10g! about explain plan , you can start reading about it from :
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#19259

read the 11th chapter -on autotrace as well.
Re: How to tune my sql script [message #228562 is a reply to message #228555] Tue, 03 April 2007 06:28 Go to previous messageGo to next message
memory
Messages: 11
Registered: April 2007
Location: India
Junior Member
no we can not use b'c company uses 8i.
Re: How to tune my sql script [message #228566 is a reply to message #228412] Tue, 03 April 2007 06:49 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
help the company understand the need of change.
Re: How to tune my sql script [message #228720 is a reply to message #228566] Tue, 03 April 2007 22:27 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Add a statement after each SQL that inserts a log message into a log table along with the current SYSDATE.

Inspect the log after a full run and work out which bit takes the longest time, and give us THAT to tune, not the entire program.

Ross Leishman
Previous Topic: Complex query
Next Topic: tuning of plsql program
Goto Forum:
  


Current Time: Sun Dec 08 06:49:51 CST 2024