OraFAQ Forum: Performance Tuning » time consuming query

Home » RDBMS Server » Performance Tuning » time consuming query (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

time consuming query [message #417509]

Sun, 09 August 2009 23:53

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

Report message to a moderator

Re: time consuming query [message #417524 is a reply to message #417509]

Mon, 10 August 2009 01:08

Michel Cadot
Messages: 68653
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

Report message to a moderator

Re: time consuming query [message #417525 is a reply to message #417524]

Mon, 10 August 2009 01:14

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

thanks for your suggestion give me one favour like how to deactive my account from this.

Report message to a moderator

Re: time consuming query [message #417861 is a reply to message #417525]

Tue, 11 August 2009 21:44

trantuananh24hg
Messages: 744
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?

Report message to a moderator

Re: time consuming query [message #417871 is a reply to message #417861]

Tue, 11 August 2009 23:47

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

how can i resolve this as it is one of the time consuming query we
listed.

Report message to a moderator

Re: time consuming query [message #417874 is a reply to message #417871]

Tue, 11 August 2009 23:52

trantuananh24hg
Messages: 744
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?

Report message to a moderator

Re: time consuming query [message #417875 is a reply to message #417874]

Tue, 11 August 2009 23:55

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.

Report message to a moderator

Re: time consuming query [message #417876 is a reply to message #417875]

Wed, 12 August 2009 00:02

trantuananh24hg
Messages: 744
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.

Report message to a moderator

Re: time consuming query [message #417877 is a reply to message #417509]

Wed, 12 August 2009 00:03

BlackSwan
Messages: 26766
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>

Report message to a moderator

Re: time consuming query [message #417992 is a reply to message #417509]

Wed, 12 August 2009 07:24

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.

Report message to a moderator

Re: time consuming query [message #418056 is a reply to message #417509]

Wed, 12 August 2009 14:56

BlackSwan
Messages: 26766
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

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 05:31:26 CDT 2024