| 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
|
|
|
|
|
|
|
|
| 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?
|
|
|
|
|
|
| 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?
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
