index move - now queries are really slow

[Ratzie]

I moves some indexes that were in the same tablespace as the data, the index tablespace that I needed to migrate to, what already created.
ALTER INDEX indexNameIDX REBUILD TABLESPACE MISC_IDX

Now my queries are really slow.

Oracle 8.1.7
on HP 11.00

[A. Clay Stephenson]

It's quite possible that you moved your indices from a disk/LUN that was moderately busy to one that was already very busy. Even worse would be the case where the tablespaces reside on the same physical disk but are now physically located much farther apart on the disk so that the head is thrashing. You need to gather some perfornce metrics to analyze what is actually happening. You may also need to update statistics.

[Sandman!]

Logical separation of data and index should be enforced with actual physical separation i.e. LUNs on a different bus/controller should mitigate your performance problems.

And as stated before performance metrics need to be collected in order to make that judgement. Ideally you should have metrics before and after the data/index separation so that you can compare them.

[Steven E. Protter]

Shalom,

Your back end OS and database are both out of support.

Short term recommendations:

1) Have the disk array checked for bottlenecks.
2) http://www.hpux.ws/system.perf.sh check your systems for bottlenecks.
3) Run oracle stats back.
4) Patch the system as current as possible with an emphasis on i/o.
5) Act on what you find in steps 1-3.

Long term:
Replace the system with 11i v1 and a version of Oracle thats under support.

SEP

[Bill Hassell]

Was Oracle informed of the change in location for the index? Oracle has had this annoying habit of ignoring indexes that were unbalanced (or missing?) and silently doing a serial search rather than an indexed search. Do an EXPLAIN on one of your SQL queries to see what it reports.

[Ratzie]

Hey I like that.
But, I can not figure out where it is pulling the index from...
It does not tell me the tablespace?

[Julio Yamawaki]

Hi,

Besides checking hardware, you must check it your database is in choose mode.
If the DB is in choose mode, try analyzing all indexes and tables of your database to see it it's missing.

Regards,

[Ratzie]

I ran an analyze on it and it comes back clean.

They are all local /permanent tablespaces

[Yogeeraj_1]

hi,

what about the execution plan?

did you notice any changes?

make sure the index is usable.

select * from user_indexes where index_name="indexNameIDX";


kind regards
yogeeraj

[Yogeeraj_1]

hi again,

i would also try to refresh the statistics.

exec dbms_stats.gather_table_stats( user, '', cascade=>true );

kind regards
yogeeraj