Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

index move - now queries are really slow

SOLVED
Go to solution
Ratzie
Super Advisor

index move - now queries are really slow

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
16 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: index move - now queries are really slow

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.
If it ain't broke, I can fix that.
Sandman!
Honored Contributor

Re: index move - now queries are really slow

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
Exalted Contributor

Re: index move - now queries are really slow

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Bill Hassell
Honored Contributor

Re: index move - now queries are really slow

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.


Bill Hassell, sysadmin
Ratzie
Super Advisor

Re: index move - now queries are really slow

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
Esteemed Contributor

Re: index move - now queries are really slow

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
Super Advisor

Re: index move - now queries are really slow

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

They are all local /permanent tablespaces
Yogeeraj_1
Honored Contributor

Re: index move - now queries are really slow

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: index move - now queries are really slow

hi again,

i would also try to refresh the statistics.

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

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ratzie
Super Advisor

Re: index move - now queries are really slow

Explain Plan is doing a full table scan instead of using the index, this happens mostly with joins.

How do I set to use the index instead.
Sandman!
Honored Contributor

Re: index move - now queries are really slow

How about supplying hints to your SQL statement forcing it to pick up the indexes. Other options can be re-writing the SQL to be optimized or creating indexes which will be picked up.

~cheers
Julio Yamawaki
Esteemed Contributor

Re: index move - now queries are really slow

Hi,

Can you post the execution plan, table describe and indexes of the table?

Regards,
Ratzie
Super Advisor

Re: index move - now queries are really slow

It is long winded but I hope you can decifer it

Julio Yamawaki
Esteemed Contributor
Solution

Re: index move - now queries are really slow

Hi,

You see why the query is not using the needed indexes?
Because your fields are nullable.
Nullable fields are not used in a query.
You must change this fields for NOT NULL.

Regards,
Yogeeraj_1
Honored Contributor

Re: index move - now queries are really slow

hi,

Julio is right.

If you are using CBO, you must not also ignore your table statistics!

select * from user_tables where table_name in ('vt_hist','vt_hist_qlog');

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Ratzie
Super Advisor

Re: index move - now queries are really slow

Thanks