Home » RDBMS Server » Performance Tuning » Query problem (10.2)
Query problem [message #280338] Tue, 13 November 2007 04:53 Go to next message
amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai
Member

hi,


the following query makes the CPU utilization to 100%.... pls let me know ASAP what is the problem in this query.




select

v_cvdpl_no,Hrs1 from (select jm.v_cvdpl_no v_cvdpl_no,min( TimeDiff(D_MAX_RET_PERIOD , SYSDATE)) Hrs1
from

job_ticket_mst jm,job_work_flow_dtl jd where jd .N_LINENUMB = jm.N_LINENUMB
and

jd.V_CVDPL_NO = jm. V_CVDPL_NO
and

jd.D_PROD_IN_TIME is null
and

jd.V_PROCESS_CODE = jm. V_STATUS
and

V_SCHEDULE_TO = '10281'
and

jm.V_STATUS = 'production'
group

by jd.V_SCHEDULE_TO, jm.v_cvdpl_no
order

by Hrs1,jm. v_cvdpl_no)
where

rownum <=1




Regards,



Amit
Re: Query problem [message #280361 is a reply to message #280338] Tue, 13 November 2007 05:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ewwwww.
I've seen some repulsive coding layouts in my time, but this is unreadable.
I've reformatted it just for the sake of my own sanity:
select  v_cvdpl_no
       ,Hrs1 
from   (select jm.v_cvdpl_no v_cvdpl_no
              ,min( TimeDiff(D_MAX_RET_PERIOD , SYSDATE)) Hrs1 
        from   job_ticket_mst jm
              ,job_work_flow_dtl jd 
        where  jd .N_LINENUMB = jm.N_LINENUMB 
        and    jd.V_CVDPL_NO = jm. V_CVDPL_NO 
        and    jd.D_PROD_IN_TIME is null 
        and    jd.V_PROCESS_CODE = jm. V_STATUS 
        and    V_SCHEDULE_TO = '10281' 
        and    jm.V_STATUS = 'production' 
        group by jd.V_SCHEDULE_TO
                ,jm.v_cvdpl_no 
        order by Hrs1,jm. v_cvdpl_no) 
where rownum <=1 

I thought the point of code layout was to enhance readability, not to induce migranes.

What is the explani plan of this query, and what indexes do you have on the tables?
Re: Query problem [message #280378 is a reply to message #280338] Tue, 13 November 2007 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64101
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel
Re: Query problem [message #280388 is a reply to message #280361] Tue, 13 November 2007 06:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thinking about it, if you're having CPU problems, can you tell us what this function TimeDiff does?
Re: Query problem [message #280493 is a reply to message #280388] Tue, 13 November 2007 20:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I concur with JR, TimeDiff() is almost certainly the problem.

Are you running this just once? In a loop? Or lots of users running it at once?

Ross Leishman
Re: Query problem [message #280645 is a reply to message #280493] Wed, 14 November 2007 05:28 Go to previous messageGo to next message
amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai
Member

lots of users are running the query at once...


Amit
Re: Query problem [message #280648 is a reply to message #280645] Wed, 14 November 2007 05:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what does TimeDiff do?
Previous Topic: Pls. help Tune this query
Next Topic: Need to perform INDEX RANGE SCAN instead of FTS
Goto Forum:
  


Current Time: Fri Dec 02 13:51:36 CST 2016

Total time taken to generate the page: 0.16501 seconds