Re: optimizer

In article <7j77bg$2hn$3_at_news.udel.edu>,   "Hemei" <Hemei_at_yahoo.com> wrote:
> I want to user oracle optimizer to tune my query. Here I have some
questions
> and need your help.
> a.when I execute a query, I get the following message
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 TABLE ACCESS (FULL) OF 'BP_OPTION' (Cost=33 Card=2299)
> Could you tell me what the '(cost=33 card=1)' means?

Cost is a value the Optimizer uses. You can usually ignore it. Card is short for Cardinality. It's an estimate of the number of rows returned on that operation. Your query is doing a FULL SCAN so it will check every row (about 2300 rows in your BP_OPTION table, right?) If you ANALYZE your tables regularly, the cardinality of a FULL SCAN should match the number of rows in the table. The card value onlu should concern you if it is very large (say >1million for one table maybe).

>
> b.If I execute another query to do the same thing and get a different
> execute plan, how do I know which query is better?

Rough guide (worst to better) for single table: FULL SCAN
INDEX RANGE SCAN
INDEX UNIQUE SCAN
ACCESS BY ROWID Note that is a VERY rough guide. Also performance takes a hit anytime Oracle must do a SORT UNIQUE operation in the PLAN.

There are other types of access when you get into joining tables. Too many combinations to list in this short answer.

>
> c.when I execute a similar query on different table, say 'select count
(1)
> from table_name', the cost values I get are different. Couls you tell
me
> what elements may affect the cost value?

Only Oracle can say for sure what goes into the COST number. And it may be different between different version of ORACLE. Again, don't go by the cost number.

If you really want to look at the real cost factors, then you should consider using the AUTOTRACE option. Then you can compare statistics such as the number of sorts in memory versus on disc, the number of disc reads, the number of bytes processed, and other factors.

Judging from the sample you present, optimizing COUNT(*) on a single table is really a waste of time. Most often it executes in seconds. So unless you are doing this frequently in a realtime control system, don't bother.

 Now optimizing queries with large joins and subqueries, that's another matter. 8^)
>
> Thanks.
>
> Peng Nie
> peng.nie_at_mortgagefamily.com
>

You're welcome.
  Ed

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jun 04 1999 - 12:15:48 CDT