| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Performance problems during INSERTs and OPTIMIZER
A copy of this was sent to read_at_the.signature (El Gringo)
(if that email address didn't require changing)
On 21 Jan 1999 11:36:39 GMT, you wrote:
>
[snip]
>Another probleme, even though "analyze compute, etc" has been run on every table (for
>all columns, all indexed columns, everything tried), the OPTIMIZER seems to prefer
>taking into account EXPLAIN PLANS that have costs a lot higher than some others (when
>I force /*+ index(table index)*/.
>For example, between /*+ index(table)*/ and /*+ index(table index)*/, oracle chooses
>in the first case the index that really slows everything down, so I am forced to use
>complete hints, with the risk of having it being invalidated when someone in the
>future changes the name of the index, etc...(brieffly, "analyze" screws up).
would have to see the table definitions, the indexes invovled and the query itself to say why this occurrs but....
>How could I correctly make oracle , by itself, take the appropriate explain plan ? (it
>should be easy, between cost=2.2 and cost=5000, the first one is to take, not the
>other one, and in my case, oracle takes the second one, that is, cost=5000).
>
the costs for queries CANNOT be compare across query plans. You cannot compare the 2.2 from query 1 to 5000 for query 2. When the optimizer gets a query, it makes a couple of plans and assigns costs to them and compares those costs -- the costs for a given query with different plans can be compared (but you never get to see the other plans that were developed and discarded).
The costs for 2 different queries are *not* comparable. For example, in your case, the cost is probably really really low because you HINTED to the optimizer to use a specific index. That made the optimizer assign a *really* low cost to the access plan that used that index to help it become the plan of choice -- by using the hint with the index name -- YOU lowered the cost.
>Help.
>thanks in advance.
>riad
>
>----------------------------------------------
>To email, replace the "ANTI-SPAM.ORG" by "com"
>----------------------------------------------
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jan 21 1999 - 10:58:41 CST
 |
 |