Performance tunning [message #475994] |
Mon, 20 September 2010 09:09 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
vishalsrivastava
Messages: 5 Registered: September 2010
|
Junior Member |
|
|
Hi All,
This attached SP need to be tune.Its showing BAD performance.
Any type of suggestion to improve the performance?
Thanks in advance
-
Attachment: sql8i.txt
(Size: 56.41KB, Downloaded 1338 times)
|
|
|
|
Re: Performance tunning [message #476164 is a reply to message #475994] |
Tue, 21 September 2010 17:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/56289.jpg) |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Basic debugging man. You need to learn basic debugging techniques. Here are some ideas for you to practice:
1) You need to put some instrumentation into your code that will tell you where it is spending its time. Then you will know where to spend your time in improving performance. For example, if your procedure comes in two parts A and B, and A takes 1 second and B takes 10 minutes, what sense does it make to spend your time trying to tune part A? But you can't know this until you put some instrumentation into your code that will give you detail on where time is being spent.
To do this, look up AUTONOMOUS_TRASACTION. Here is something cheap you can use:
create table comment_table
(
comment_text varchar2(255)
, event_date date
)
/
create or replace procedure comment_procedure (comment_text_p in varchar2)
as
pragma autonomous_transaction;
begin
insert into comment_table (comment_text,event_date) values (comment_text_p,sysdate);
commit;
end;
/
show errors
begin
--
-- capture start time
--
comment_procedure('test1 start');
--
-- do some stuff
--
dbms_lock.sleep(3);
--
-- capture the end time
--
comment_procedure('test1 end');
end;
/
commit;
alter session set nls_Date_format = 'dd-mon-rrrr hh24:mi:ss';
col comment_text format a20
select * from comment_table;
select substr(comment_text,1,instr(comment_text,' ')-1) event_name
, round(
(
select (b.event_date-a.event_date)*24*60*60
from comment_table b
where substr(a.comment_text,1,instr(a.comment_text,' '))||'end' = b.comment_text
)
) seconds
from comment_table a
where comment_text like '%start'
order by event_date
/
Pay attention to how the name of the event is used in figuring out the duration of an event. Yes using this means you will need to edit your code but that is instrumentation ain't it. With something like this, you can figure out where your time is being spent.
2) one you have an idea where you are spending your time you need to split your code into pieces and figure out among the pieces where the time is going once again. Then tune to that. For example, you can comment out various parts of your plsql package and see how much runtime goes away. If you are able to identify a specific cursor as the culprit then you can run the sql from the cursor via sqlplus and do the same; comment out pieces of it and rebuild it one part at a time till you find out where a massive jump in time happens. Then you can see if you are doing something wrong and are maybe in need of a change like a re-write, or a new index, or something else.
3) you should consider the phrase slow-by-slow. It was coined by Tom Kyte to my knowledge and it is a play on row-by-row to suggest that same is slow-by-slow. Using plsql cursors is very slow when compared to any other method you can use. Try re-writing your procedure using only sql and not plsql.
The reason you are using the autonomous_transaction is so that you get your messages recorded even if your code rolls back.
Good luck, Kevin
[Updated on: Tue, 21 September 2010 17:38] Report message to a moderator
|
|
|
Re: Performance tunning [message #476187 is a reply to message #475994] |
Wed, 22 September 2010 00:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
In addition to Kevin's post..
While giving a first glance..I found something that you should not do.
Quote:
to_date(sysdate, 'dd/MM/yy') = to_date(vh.SRT_DT, 'dd/MM/yy')
This is wrong!
You should not use to_date on SYSDATE,or a column of date data type.This may lead to giving wrong result.This approach will not only lead to wrong result but also skip the indexes(this will make the optimizer not to use the index )on the date columns by optimizer.
There is bug in your pl/sql code. Please correct that.
Also, do you think that you are taking care of the exceptions
that can be raised? I think NO.
When WHEN OTHERS is not followed by RAISE its always almost a BUG! Of course, there is an option in 11g. You can use,
alter session set plsql_warnings='enable:all';
And ...
It should the caller to decide whether you are going to commit/rollback.
Regards
Ved
[Updated on: Wed, 22 September 2010 00:26] Report message to a moderator
|
|
|