OraFAQ Forum: Performance Tuning » Help on First

Home » RDBMS Server » Performance Tuning » Help on First_Rows

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

Help on First_Rows [message #195218]

Wed, 27 September 2006 07:54

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

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

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

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

Re: Help on First_Rows [message #195230 is a reply to message #195224]

Wed, 27 September 2006 08:42

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

Mahesh Rajendran
Messages: 10707
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

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

Mahesh Rajendran
Messages: 10707
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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 06 19:29:17 CDT 2024