| 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 -> Performance problems during INSERTs and OPTIMIZER
Hello.
I have a problem with an ORACLe instance that I could not figure out how to fix.
I have an application, that usually, was pretty fast.
We changed to another faster machine. and in this particular case, the INSERT combined
with SELECT (like "insert into toto select ......") are a lot slower.
It seems that the rollbacks and the redo logs are eating a lot of time, eventhough
they are pretty close to what they looked like in the previous machine.
In fact, I don't really know in which part of oracle, this kind of operation is really
wasting time....Who has any clue on that ?
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).
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).
Help.
thanks in advance.
riad
 |
 |