Home » RDBMS Server » Performance Tuning » Query taking long time
Query taking long time [message #348742] Wed, 17 September 2008 17:09 Go to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

Hi friends,

I am having one querry it is taking around 5 min but my user is expecting the putput very less time.Please provide some tips to complete the query with less time

And also i checked by passing the hard code value of payroll_id alos it is taking around 5 min

select * from
xxab.ab_payroll_reg_rpt_data abrd
where rowid = (select max(rowid) from xxab.ab_payroll_reg_rpt_data abrd1
where abrd.effective_date = abrd1.effective_date and abrd1.payroll_id = abrd.payroll_id and
abrd1.payroll_id = :$FLEX$.P_PAYROLL_NAME)
order by effective_date desc


THANKS
SREE
Re: Query taking long time [message #348807 is a reply to message #348742] Thu, 18 September 2008 00:07 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please format your code. Is this a Forms question or a scripting question?

Do an 'explain plan'. Does the table have indexes for any of the fields of interest?

David
Re: Query taking long time [message #349462 is a reply to message #348742] Sun, 21 September 2008 23:46 Go to previous messageGo to next message
sreenu80
Messages: 50
Registered: July 2006
Location: bangalore
Member

thanks for you are replay.

Yes table have indexs and we tried query by index based scan also still it is running 5 min


Please friends provide me the solution asap.

thaks
sree
Re: Query taking long time [message #349492 is a reply to message #349462] Mon, 22 September 2008 01:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have provided far too few information to get the answer.

So, did you check explain plan? TKPROF? What did you find out?

Because, if everything is OK, perhaps this query really takes 5 minutes (for example, table is HUGE, or you access it via database link, or it is a very complex view, or ...).


In my opinion, this thread does not belong to Forms forum so I'll move it to the Performance Tuning one. If you are unhappy with it, please, PM me (or "report this message to a moderator"), explain why you think it should remain in the Forms forum and it will be moved back.
Re: Query taking long time [message #349592 is a reply to message #348742] Mon, 22 September 2008 05:27 Go to previous message
joshirish
Messages: 3
Registered: September 2008
Junior Member
Please provide
1. Table information
2. Index information
3. Explain plan/autotrace/tkprof
4. Data sizes information
Previous Topic: Why This Execution Plan
Next Topic: Parallel inserts
Goto Forum:
  


Current Time: Sat Dec 10 22:31:37 CST 2016

Total time taken to generate the page: 0.30009 seconds