Home » RDBMS Server » Performance Tuning » to find out time taken
to find out time taken [message #344978] Tue, 02 September 2008 01:22 Go to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
How to find whether which part of PLSQL procedure is taking time.

Report message to a moderator

Re: to find out time taken [message #344987 is a reply to message #344978] Tue, 02 September 2008 02:08 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Use PL/SQL profiler

http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php

Regards,
Dwarak.K

Report message to a moderator

Re: to find out time taken [message #345252 is a reply to message #344978] Tue, 02 September 2008 13:56 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
Also you can trace a session using

undef sid serial flg
exec sys.dbms_system.set_ev(&&sid,&&serial,10046,12,'');
prompt Trace will run for 5 minutes
exec dbms_lock.sleep(300);
exec sys.dbms_system.set_ev(&&sid,&&serial,10046,0,'');
prompt Trace is off

Go to the server, check the udump directory and read the trace file using tkprof

regards
--Mak

Report message to a moderator

Re: to find out time taken [message #345297 is a reply to message #344978] Tue, 02 September 2008 20:17 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
You can also read up on DBMS_UTILITY.GET_TIME and roll your own.

Good luck, Kevin

Report message to a moderator

Re: to find out time taken [message #345865 is a reply to message #345297] Fri, 05 September 2008 02:51 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
Thanks.......i got it using dbms_profiler

Report message to a moderator

Re: to find out time taken [message #345867 is a reply to message #345865] Fri, 05 September 2008 02:54 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
my procedure is having following stmt

INSERT INTO ams_lts_lead_summary
SELECT * FROM ams_lts_agent_hierarchy ;

is there any way to imperformance of above query
it is takiing 1 min to insert records.

Report message to a moderator

Re: to find out time taken [message #345893 is a reply to message #345867] Fri, 05 September 2008 04:05 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Post explain plan.
Tell us how many records are u trying to insert. There are lot of things that impact insert performance. Index used,Number of free lists..

Report message to a moderator

Re: to find out time taken [message #345899 is a reply to message #345893] Fri, 05 September 2008 04:10 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
it is inserting near about 2,90,000 records......
like this many inserts are there in loop inside procedure
that is why procedure is taking time

Report message to a moderator

Re: to find out time taken [message #345995 is a reply to message #344978] Fri, 05 September 2008 09:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are many things that can affect insert speeds, not all of which you have great control over. This was your insert statement:

INSERT INTO ams_lts_lead_summary
SELECT * FROM ams_lts_agent_hierarchy ;

I make several assumptions about your job.

Quote:
1) this statement is not inside a loop and is executed only once per job run.
2) there are no other people inserting/updating/deleting from this table while your job is running.
3) you do not delete from this table, only insert. Alternatively you user the TRUNC/LOAD strategy.

Different systems will load data at different rates so it is not possible to give you a "right" number for you system. Only you can figure that out. I can tell you this; I have a sophisticated insert in a ETL job and it easily loads 1 millions rows per minute.

If you are loading 1/4 million rows (your email was garbeled here) then first thing to my mind would be to try using the /*+ append */ hint in your insert statement. Read up on it via google to learn what it does.

You should also have some idea of what is an acceptable load time for yourself. Do you have a goal of how fast you need the insert to be? When will you be satisfied with results once it starts to improve or will you continue to try and squeeze more out of it?

Good luck, Kevin

Report message to a moderator

Re: to find out time taken [message #346296 is a reply to message #345899] Mon, 08 September 2008 02:28 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
Just to add to Kevin's Post,If you inserts are happening inside a loop,Try using Bulk feature (if it is possible in your business scenario) This could help you increasing your performance .

Look at
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html

Report message to a moderator

Re: to find out time taken [message #346359 is a reply to message #346296] Mon, 08 September 2008 06:03 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
I tried using bulk collect for

INSERT INTO ams_lts_lead_summary
SELECT * FROM ams_lts_agent_hierarchy ;

but there is no much performance improvement
this insert is not in loop

Report message to a moderator

Re: to find out time taken [message #346452 is a reply to message #345899] Mon, 08 September 2008 10:49 Go to previous messageGo to next message
dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad
Member
trupti111 wrote on Fri, 05 September 2008 14:40
it is inserting near about 2,90,000 records......
like this many inserts are there in loop inside procedure
that is why procedure is taking time



I suggested Bulk insert as you said the statements are inside a loop:)

insert into .. Select * is bulk insert by itself

Report message to a moderator

Re: to find out time taken [message #346586 is a reply to message #346452] Tue, 09 September 2008 00:49 Go to previous messageGo to next message
trupti111
Messages: 29
Registered: August 2008
Location: navi mumbai
Junior Member
Inside loop also some inserts are there but of the form insert into....select...
so i think bulk collect is not useful right

Report message to a moderator

Re: to find out time taken [message #346633 is a reply to message #344978] Tue, 09 September 2008 03:31 Go to previous message
tanyaa
Messages: 1
Registered: September 2008
Junior Member
On occasion you meet a developer who seems like a solid programmer. They know their theory, they know their language. They can have a reasonable conversation about programming. But once it comes down to actually producing code they just don’t seem to be able to do it well.
---------
Tanyaa
[Edit MC: Don't put your "Message Marketing" here, there is a Marketplace forum for this]

[Updated on: Tue, 09 September 2008 04:28] by Moderator

Report message to a moderator

Previous Topic: Tuning of delete stmt
Next Topic: Perfomance tuning of a view
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 20:38:15 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.