Home » RDBMS Server » Performance Tuning » Help on First_Rows
Help on First_Rows [message #195218] Wed, 27 September 2006 07:54 Go to next message
rajaobj
Messages: 9
Registered: September 2006
Location: Chennai
Junior Member
Hi Friends,
I am new to SQL Tuning,I have query where it has /*+ FIRST_ROWS */.I hope this will returned the first row asap.when i run the explain plan for this the cost is shown like this

SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=45964 Card
=14981 Bytes=2067378)

The same query when i run without the hint i am getting the cost as
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9888 Card=14981 By tes=2067378)
why is it so..

Regards,
Raja

[Updated on: Wed, 27 September 2006 07:55]

Report message to a moderator

Re: Help on First_Rows [message #195219 is a reply to message #195218] Wed, 27 September 2006 07:56 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Cost is a figure used by the optimizer for various explain plans for the SAME query. Comparing cost values across multiple statements is pointless.

Report message to a moderator

Re: Help on First_Rows [message #195220 is a reply to message #195219] Wed, 27 September 2006 08:00 Go to previous messageGo to next message
rajaobj
Messages: 9
Registered: September 2006
Location: Chennai
Junior Member
But how far it will affect the performance of SQL.Moreover i want to reduce the cost of the query.what is the best practice

Report message to a moderator

Re: Help on First_Rows [message #195224 is a reply to message #195220] Wed, 27 September 2006 08:15 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Moreover i want to reduce the cost of the query

No you don't Smile You want Oracle to pick the execution plan from the potentially thousands of execution plans for your query that will be most eficient. Take no notice of cost.

Quote:

I hope this will returned the first rows asap

Correct, this will have the effect of taking LONGER to return ALL of the rows, but you will get the first rows returned to you more quickly.

Once again, do not base the efficiency of different queries on the cost value displayed in the execution plan.

There are guys here who are phenomenal on tuning, maybe one of them cn explain the mechanics of it better than I can. Alternatively, find out for yourself by reading the Tuning documentation @
http://tahiti.oracle.com

Report message to a moderator

icon8.gif  Re: Help on First_Rows [message #195230 is a reply to message #195224] Wed, 27 September 2006 08:42 Go to previous messageGo to next message
rajaobj
Messages: 9
Registered: September 2006
Location: Chennai
Junior Member
Thanks for your reply friend.But i have to find someone for this tuning

Report message to a moderator

Re: Help on First_Rows [message #195234 is a reply to message #195230] Wed, 27 September 2006 09:08 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It is an expected behaviour as said in oracle docs and simply put b y Connor here.
http://www.oracledba.co.uk/tips/9i_first_rows.htm

>>Moreover i want to reduce the cost of the query.what is the best practice
"Cost" depends on many factors.
Oracle version, data distribution, your stats, size of histograms etc. You have provided no useful information to work with.

[Updated on: Wed, 27 September 2006 09:08]

Report message to a moderator

Re: Help on First_Rows [message #195244 is a reply to message #195234] Wed, 27 September 2006 09:37 Go to previous messageGo to next message
rajaobj
Messages: 9
Registered: September 2006
Location: Chennai
Junior Member
Thanks Mahesh,
But as newbee for Tuning,what is your advice.How we can go further in this.Looking for your reply

Report message to a moderator

Re: Help on First_Rows [message #195247 is a reply to message #195244] Wed, 27 September 2006 09:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Same as JSI2001 already stated.
Collect the statistics on table/indexes and let CBO decide.
Again, you have not provided any information to work with.
Apart from cost, what is your problem here?
is query working slow or what?

Report message to a moderator

Re: Help on First_Rows [message #195309 is a reply to message #195247] Wed, 27 September 2006 20:15 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you want the first row returned as quickly as possible, and you suspect it could be better, post the entire SQL and the entire EXPAIN PLAN here and we'll give you an opinion on whether it can be tuned.

Now, this is important. You MUST enclose your SQL in [code] and [/code] tags, otherwise we can't (and won't) read it. Use the theme/orafaq/images/b_code.gif button to do this.
Ross Leishman

Report message to a moderator

Previous Topic: Statspack without perstat user
Next Topic: DBLinks and performance Issues
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jan 08 04:18:53 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.