Home » RDBMS Server » Performance Tuning » time consuming query (oracle 10g)
time consuming query [message #417509] Sun, 09 August 2009 23:53 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
DECLARE
  job BINARY_INTEGER := :job;
  next_date DATE     := :mydate;
  broken BOOLEAN     := FALSE;
BEGIN
  EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
  :mydate := next_date;
  IF broken THEN
    :b := 1;
  ELSE
    :b := 0;
  END IF;
END;
/


hi the above query is taking more time.can you please tell me what is the reason like.

i have used this query to find out like

select sql_text,sql_id,fetches,executions from v$sql

DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 6gvch1xu9ca3g 0 1227

Re: time consuming query [message #417524 is a reply to message #417509] Mon, 10 August 2009 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use something you don't understand (see your previous topic).

Regards
Michel
Re: time consuming query [message #417525 is a reply to message #417524] Mon, 10 August 2009 01:14 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
thanks for your suggestion give me one favour like how to deactive my account from this.
Re: time consuming query [message #417861 is a reply to message #417525] Tue, 11 August 2009 21:44 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rajasekhar857 wrote on Mon, 10 August 2009 13:14
thanks for your suggestion give me one favour like how to deactive my account from this.


Should not be a boy rather than a man.
If you disagreed with Michel, you may prove that he was wrong.

According to my opinion, you had got some problem such as :

"Top 5 Events:

Library cache lock
Library cache pin
.....
"


Am I right?
Re: time consuming query [message #417871 is a reply to message #417861] Tue, 11 August 2009 23:47 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how can i resolve this as it is one of the time consuming query we
listed.
Re: time consuming query [message #417874 is a reply to message #417871] Tue, 11 August 2009 23:52 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rajasekhar857 wrote on Wed, 12 August 2009 11:47
how can i resolve this as it is one of the time consuming query we
listed.


Why do you want to tune this query?
How do you know about it? How much %per impact to your system?
Re: time consuming query [message #417875 is a reply to message #417874] Tue, 11 August 2009 23:55 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
it ia time consuming query.we need to analyse on this like where
exactly it went wrong and issue fix for it.
Re: time consuming query [message #417876 is a reply to message #417875] Wed, 12 August 2009 00:02 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
rajasekhar857 wrote on Wed, 12 August 2009 11:55
it ia time consuming query.we need to analyse on this like where
exactly it went wrong and issue fix for it.


First time, how much do you know about it?
Second time, if you tune it faster or better, will performance DB raise to light?

Finally, I sure your problem was not this query, if your DB really need to tune.
Re: time consuming query [message #417877 is a reply to message #417509] Wed, 12 August 2009 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>it ia time consuming query.we need to analyse on this like where exactly it went wrong and issue fix for it.


ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem SQL here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>
Re: time consuming query [message #417992 is a reply to message #417509] Wed, 12 August 2009 07:24 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
If you are sure that this EXECUTE_EM_DBMS_JOB_PROCS is the procedure which is taking time then you can debug where max time is being consumed for each statement by using DBMS_UTILITY.get_time.





Re: time consuming query [message #418056 is a reply to message #417509] Wed, 12 August 2009 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>you can debug where max time is being consumed for each statement by using DBMS_UTILITY.get_time.
Interesting

[Updated on: Wed, 12 August 2009 14:59]

Report message to a moderator

Re: time consuming query [message #420415 is a reply to message #418056] Sat, 29 August 2009 05:46 Go to previous message
andydba
Messages: 34
Registered: September 2008
Member

Which version of database you are working with?

You can use DBMS_PROFILER or DBMS_PROF to find out which statement in your pl/sql code is taking most of the time and can go from there.

Did you look into trace file?

Did you try to find out any wait issues on the database when this piece of code executes?

What tables/objects are being used in this procedure?

So many things you need to do before asking a question.




Previous Topic: A question about LRU in shared pool
Next Topic: tuning undo
Goto Forum:
  


Current Time: Sun Dec 04 22:45:28 CST 2016

Total time taken to generate the page: 0.12424 seconds