Home » RDBMS Server » Performance Tuning » pls help to reduce some loop (10g)
pls help to reduce some loop [message #332885] Thu, 10 July 2008 00:53 Go to next message
simplesanju
Messages: 34
Registered: July 2008
Member
hi all


pls help to tune this procedure.it is taking more then three hrs.i think procedure have lots of loop so if we reduce some loop by adding loop condition in cursor declaration. or if you have any other suggestion pls help...


thanks
sanjana
Re: pls help to reduce some loop [message #332889 is a reply to message #332885] Thu, 10 July 2008 00:58 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
What a joke!
Get a grip on reality!
Re: pls help to reduce some loop [message #332896 is a reply to message #332885] Thu, 10 July 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should change something in line 42.

Regards
Michel
Re: pls help to reduce some loop [message #332900 is a reply to message #332896] Thu, 10 July 2008 01:12 Go to previous messageGo to next message
simplesanju
Messages: 34
Registered: July 2008
Member
thanks for reply but pls clarifie for which one you are talking?
Re: pls help to reduce some loop [message #332904 is a reply to message #332885] Thu, 10 July 2008 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>thanks for reply but pls clarifie for which one you are talking?

ALL!
Re: pls help to reduce some loop [message #332913 is a reply to message #332896] Thu, 10 July 2008 01:29 Go to previous messageGo to next message
simplesanju
Messages: 34
Registered: July 2008
Member
Michel sir,

for which line you are talking?

Regards
Sanjana
Re: pls help to reduce some loop [message #332931 is a reply to message #332913] Thu, 10 July 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
42

Regards
Michel
Re: pls help to reduce some loop [message #332934 is a reply to message #332885] Thu, 10 July 2008 02:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sanjana,

The truth is that your procedure is too long and complex for any rational person to want to look at.

Whoever tunes it is going to expend HOURS or perhaps DAYS of effort. What you are asking is for someone here to provide free consulting for which YOU will be paid.

That doesn't seem fair.

Performance tuning is a specialist skill acquired over many years. There is no shame in not being able to do it; but if you are given a task that is beyond your abilities then you need to make that clear to your manager.

If you have the luxury of time to explore and learn ON YOUR OWN, you can start with this article; it may contain some techniques you can use.

Ross Leishman
Re: pls help to reduce some loop [message #333028 is a reply to message #332934] Thu, 10 July 2008 05:57 Go to previous messageGo to next message
orafan23
Messages: 13
Registered: December 2005
Junior Member
Also try using DBMS_PROFILER,pretty useful for finding the bottlenecks.
Re: pls help to reduce some loop [message #333215 is a reply to message #332885] Thu, 10 July 2008 19:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
if you want to tune this monster, is is certainly possible. There are a couple of rules of thumb you should consider:

1) sql a single sql solution is generally way faster than a plsql equivelant. You can always try to rewrite this into a single select statement that does the job. In the old days (pre-oracle9i) this would have taken quite some doing, but today Oracle SQL support the WITH clause so if you understand the calculations being done, you should be able to build your sql in steps.

2) it is likely that most of this code goes well.. not slow, and there is only one piece that is responsible for most of the work. You should be doing some basic tuning exercises to understand where in your code all your time is being spent. You could do something as simple as dbms_output that dumps the times in which you enter various sections of the the code. Or you could write a somewhat more sophisticated autonomous plsql procedure that saves timings to a table. Either way you should be able in a day to figure out where all you time is being spent. Then you can try tuning away the time there. Hey... maybe one of the cursors you are using is very expensive because you are missing an index, who knows. You don't know becuase you have not done the necessary work to tune the procedure. This is not a crticism of you, most people don't tune any of their code before they have to and this I am guessing is not your stuff to begin with.

Also, just as a side comment, if you are going to post a file like this, you could at least take some time to format the code. Maybe people would look at it rather than complain about it if it looked a bit neater.

Good luck, Kevin
Re: pls help to reduce some loop [message #333217 is a reply to message #332885] Thu, 10 July 2008 20:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Contrary to apparent opinion, number of lines means nothing if code is modular and organized.

Still, there are obvious things wrong with this code.

For example:

what is the different between this which is what you have:

       CNT := 0;
       SELECT NVL(COUNT(1),0)
	   INTO   CNT
	   FROM   CHM_ALT_POL_HDR H, PS_POLICY_MST P
	   WHERE  H.STRPOLNBR = POL_ID(I)
	          AND TO_CHAR(H.DTCREATED,'YYYY') = CHK_YEAR
			  AND H.STRPOLNBR = P.STRPOLNBR
			  AND P.NSTATUS = 15;
...
      IF CNT = 0 THEN
         AFYP_AMOUNT_YTD := AFYP_AMOUNT_YTD+AFYP_AMT(J);
      END IF;
...

and this which is what I think you should have:

       CNT := 0;
       SELECT count(*)
	   INTO   CNT
	   FROM   CHM_ALT_POL_HDR H, PS_POLICY_MST P
	   WHERE  H.STRPOLNBR = POL_ID(I)
	          AND TO_CHAR(H.DTCREATED,'YYYY') = CHK_YEAR
			  AND H.STRPOLNBR = P.STRPOLNBR
			  AND P.NSTATUS = 15
           and rownum = 1;
...
      IF CNT = 0 THEN
         AFYP_AMOUNT_YTD := AFYP_AMOUNT_YTD+AFYP_AMT(J);
      END IF;
...

This code commits one of the most basic newbie errors, counting all rows when it only needs to know if there is at least one. What makes this a possibly very sore issue is that you do two of these counts inside a loop so if the second version does in fact perform faster for you, it will do so for each row in your loop. You might even consider creating some function based indexes on the two tables involved in order to skip doing table rowid lookups, and making sure they are doing a "first_rows" style plan, so that these counts are as fast as they can be.

Try fixing these and see if it makes any difference.

Good luck, Kevin



Re: pls help to reduce some loop [message #333229 is a reply to message #333217] Thu, 10 July 2008 23:23 Go to previous messageGo to next message
simplesanju
Messages: 34
Registered: July 2008
Member
kevin sir,

thanks a lot.

Regards
Sanjana
Re: pls help to reduce some loop [message #333409 is a reply to message #332885] Fri, 11 July 2008 10:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe Cool

he called me sir!

Kevin
Re: pls help to reduce some loop [message #333490 is a reply to message #332885] Fri, 11 July 2008 22:23 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
And I most strongly urge you to change your exception handler thusly:

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE (SQLERRM);
        RAISE;
 



As it stands, you have a horrible bug in your code.
Re: pls help to reduce some loop [message #333685 is a reply to message #333409] Mon, 14 July 2008 03:05 Go to previous messageGo to next message
simplesanju
Messages: 34
Registered: July 2008
Member
sir

i m female.


sanjana
Re: pls help to reduce some loop [message #333850 is a reply to message #332885] Mon, 14 July 2008 10:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My appologies Dear Lady. Kevin
Re: pls help to reduce some loop [message #333939 is a reply to message #333850] Mon, 14 July 2008 23:11 Go to previous message
simplesanju
Messages: 34
Registered: July 2008
Member
Smile


thanks
sanjana
Previous Topic: Oracle Query Tuning
Next Topic: Index Partitioning
Goto Forum:
  


Current Time: Mon Dec 05 21:08:56 CST 2016

Total time taken to generate the page: 0.10623 seconds