| pls help to reduce some loop [message #332885] |
Thu, 10 July 2008 00:53  |
simplesanju
Messages: 36
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 #332934 is a reply to message #332885] |
Thu, 10 July 2008 02:47  |
rleishman
Messages: 3728
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 #333215 is a reply to message #332885] |
Thu, 10 July 2008 19:35 |
 |
Kevin Meade
Messages: 2103
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 |
|
Kevin Meade
Messages: 2103
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 #333490 is a reply to message #332885] |
Fri, 11 July 2008 22:23 |
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.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
