[MLUG] Oracle issue driving me NUTS

[Jason McIntosh <EMAIL:PROTECTED>]

Ok, had to ask someone else for some advice on this one.  We've got two 
servers, where we're migrating from one to another.  The old server, 
when running queries, runs extremely fast, seems to use pretty good 
indexing, etc.  The new server, runs dog slow half the time.  Analyzing 
the queries shows a lot of problems with how Oracle is selecting 
indexes for it's optimizer.  For example, a sql query will do a full 
table scan of a table with 1.5 million records instead of making use of 
an index which negates all physical reads.
To test this out, I imported JUST this one table and it's associated 
indexes.  Then, did the following:

set autot on
analyze table mytable compute statistics;
/* The following takes a LOT of time and does a full table scan - ends 
up with a LOT of consistent gets/physical reads*/
select blah blah blah * from mytable where parameters set blah blah;

analyze table mytable delete statistics;
/*  Executes VERY quickly, using indexes  - does zero to minimal 
consistent gets/physical reads */
select blah blah blah * from mytable where parameters set blah blah;

The interesting thing, is then I have other queries which will make use 
of one index but not another.  For example, they'll make use of a less 
efficient index instead of using one that's FAR faster.  Further, 
trying to do hints, i.e. /*+ index(index_name) */ doesn't seem to work 
at all.
Anyone seen this before?  Any suggestions on fixing this, or at least a 
better explanation of why statistics would screw up Oracle so badly?
Thanks!
Jason


/--------------------------------------|---------------------------\
| Jason McIntosh                       | CELL: 573-424-7612        |
| Webmaster, thinker, Programmer, etc. | WORK: 573-884-3865        |
| http://poetshome.com/                |                           |
|------------------------------------------------------------------|
|"How should I know if it works?  That's what beta testers are     |
|for.  I only coded it."                                           |
|(Attributed to Linus Torvalds, somewhere in a posting)            |
\--------------------------------------|---------------------------/
GnuPG Key:  
http://poetshome.com/about/jmcintosh_mlug.missouri.edu.gpgkey

_______________________________________________
members mailing list
EMAIL:PROTECTED
http://mlug.missouri.edu/mailman/listinfo/members