OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query problem (10.2)

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

Query problem [message #280338]

Tue, 13 November 2007 04:53

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

Report message to a moderator

Re: Query problem [message #280361 is a reply to message #280338]

Tue, 13 November 2007 05:56

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?

Report message to a moderator

Re: Query problem [message #280378 is a reply to message #280338]

Tue, 13 November 2007 06:30

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

Report message to a moderator

Re: Query problem [message #280388 is a reply to message #280361]

Tue, 13 November 2007 06:49

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?

Report message to a moderator

Re: Query problem [message #280493 is a reply to message #280388]

Tue, 13 November 2007 20:12

rleishman
Messages: 3728
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

Report message to a moderator

Re: Query problem [message #280645 is a reply to message #280493]

Wed, 14 November 2007 05:28

amit_p_dixit
Messages: 31
Registered: November 2006
Location: Mumbai

Member

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

Amit

Report message to a moderator

Re: Query problem [message #280648 is a reply to message #280645]

Wed, 14 November 2007 05:36

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

And what does TimeDiff do?

Report message to a moderator

Previous Topic:	Pls. help Tune this query
Next Topic:	Need to perform INDEX RANGE SCAN instead of FTS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 11:09:16 CDT 2024