Home » SQL & PL/SQL » SQL & PL/SQL » PERFORMANCE TUNING (10g)
PERFORMANCE TUNING [message #420377] Fri, 28 August 2009 13:25 Go to next message
Bindu_007
Messages: 2
Registered: August 2009
Junior Member
Sorry for my incomplete query. I am still typing but I don't how my previous message posted.

In Detail :-

When I first run simple Query(given below) on large table. it took 10 sec to come up the results. So I tried to include ROWNUM in query, it took 0.15 sec. Again I did the same test, what suprised me is that without ROWNUM it took 0.15 sec and when I re-added ROWNUM it took 10 sec. Can any one explain me this behavior. I looked at plan_table for both queries but there is no difference.

select x.*
from (select rownum as rnum, q.*
from (select
--rownum as rno,
t.* from cg_document t
order by t.cg_document_id asc) q
where rownum <= 20) x.


I should Thank you FOR YOUR FAST RESPONSE. I REALLY APPRECIATE

[Updated on: Fri, 28 August 2009 13:35]

Report message to a moderator

Re: PERFORMANCE TUNING [message #420378 is a reply to message #420377] Fri, 28 August 2009 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Impact commpare to what?
What is your query intended to do?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: PERFORMANCE TUNING [message #420405 is a reply to message #420377] Fri, 28 August 2009 22:51 Go to previous messageGo to next message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
Hi,

In oracle whenever we execute any query oracle do the query parsing which takes time and if you run the same query again it do not do the parsing again which leads to performance.
But in case you change any thing in previous query even if you add any space in that oracle will again parse it and will take time.

Regards,
Brihaspati Rai
Re: PERFORMANCE TUNING [message #420436 is a reply to message #420405] Sat, 29 August 2009 11:56 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Yes parsing takes time. In this case, probably a millisecond. You still need to account for the other 9.999 seconds. There are a couple of reasons your query's time may vary. First, the DB's resources may be occupied with other processes at the times you run the query. Second, the data is already cached from the first query. Did you try simply running the same query twice? Does it go from 10 seconds the first time to .15 seconds the second time?
Re: PERFORMANCE TUNING [message #420696 is a reply to message #420377] Tue, 01 September 2009 10:25 Go to previous messageGo to next message
Bindu_007
Messages: 2
Registered: August 2009
Junior Member
Hi,

Thank you for replying. I agree with you parsing takes time and when it's executed the next time it fetchs from the cached. But what suprised me But when I run the query for first without ROWNUM it took 10 sec to execute and then included ROWNUM to see the performance, it took 0.02 sec.

I want to test the query again to see what happens, so, excluded ROWNUm it took 0.02 sec and then with ROWNUM column it took 10 sec to execute for the first time and consequently for second time it took around 9.05 sec. I am stuck with this.

I was trying to get help, and I found this blog:- link ://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html but got little from that.

thank you for shedding light on it.
Re: PERFORMANCE TUNING [message #420697 is a reply to message #420696] Tue, 01 September 2009 11:00 Go to previous message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
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>

post the contents of trace_results.txt back here

using SQL_TRACE is the best way to know with certainty where time is being spent by any SQL statement.
Previous Topic: SQL Query
Next Topic: grouping , based on similarity in percentage (merged 2) 8i-9
Goto Forum:
  


Current Time: Wed Dec 07 04:37:58 CST 2016

Total time taken to generate the page: 0.08592 seconds