OraFAQ Forum: Performance Tuning » Rebuild index online

Home » RDBMS Server » Performance Tuning » Rebuild index online

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Rebuild index online [message #245466]

Sun, 17 June 2007 13:04

tmathew
Messages: 18
Registered: May 2007

Junior Member

Experts,

I've few questions

we have 24x7 online CASE processing database and the main tables we have are DATA_TRANS,DATA_HISTORY and DATA_DETAIL table. we have also a logical standby db too.

DATA_TRANS table we have about 102335187 records
,DATA_HISTORY has about 43446797
and DATA_DETAIL table has got about 110566493 records.

Indexes for the DATA_TRANS table grows sooo fast and space is my concern here. i think we have too many usused areas in most of the indexes and i need to reclaim them.Please propose your suggestion here

1) Can I rebuild indexes online? as per oracle,I know we can rebuild indexes online.but I need to get some expert opinion before i go ahead and try.we process too many transaction each day and i am bit concerned doing online index rebuilding.

2) We haven't collected statitsics for any tables for the last 8 months and i could see some performance issues with some of the queries. Can I collect db statistics using job 'GATHER_STATS_JOB' online? we use 10.2.2 db and we have OEM grid. automatic collection of statistics are not running for some reason.I think 'ALTER TABLE COMPUTE STATISTICS' might takes for ever to complete but how about trying 'ANALYZE TABLE T1Tab ESTIMATE STATISTICS SAMPLE 15 PERCENT;'

3)Can you change OPTIMIZER_MODE=FIRST_ROWS to ALL_ROWS dynamically for the database instance?

4) Can you partition tables online? Oracle says we can but expecting some expert comments on this? proc and corns of doing online table partition with DBMS_REDEFINITION built-in
package.has anybody done online? please share some experience here

Thanks,

mathew

Report message to a moderator

Re: Rebuild index online [message #245467 is a reply to message #245466]

Sun, 17 June 2007 13:16

Michel Cadot
Messages: 68652
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1) Does performances and availability not greater concerns than space?
Are you sure you need rebuild the index?
Do you check their structures?

2) Use dbms_stats, don't use analyze to gather optimizer statistics

3) I think this is in the doc. no need of an expert

4) If you have a 24x7 you have no choice use dbms_redefinition.
Otherwise if you can plan downtime, recreate the table.

Regards
Michel

Report message to a moderator

Re: Rebuild index online [message #245500 is a reply to message #245467]

Sun, 17 June 2007 22:50

tmathew
Messages: 18
Registered: May 2007

Junior Member

Michel,

Thanks sooo much for your prompt reply.

1)
Performance,availability and space are greater concern for now:). we cannot add more disk space on the system. but we can delete/purge old transactions and release more space.I haven't got any chance to check the index structuters yet.

2) "Use dbms_stats, don't use analyze to gather optimizer statistics"

i think dbms_stats may take a while to complete. can I run the dbms_stats at anytime? can it affect the system if I run this for the whole database OR main tables?

3) "I think this is in the doc. no need of an expert"

haven't found anywhere in the Document saying we can change it dynamically. But saw we can set it at the session level and also can change it in the pfile/spfile.

4) "If you have a 24x7 you have no choice use dbms_redefinition.
Otherwise if you can plan downtime, recreate the table."

i would appreciate if anyone who can share their experience with
table partion done online on a 24x7 db using dbms_redefinition

Regards
Mathew

Report message to a moderator

Re: Rebuild index online [message #245526 is a reply to message #245500]

Mon, 18 June 2007 00:45

Michel Cadot
Messages: 68652
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1) Almost each index has a natural fill ratio to which it tends to return when you rebuild.
So rebuilding you:
- either shut down your application (if you do it offline) or put it in a risk of error (if you do it online)
- have a risk of a long downtime if the rebuilding is going wrong
- impact the performances while the index tries to return to its natural fill ratio
- have a risk (little but not nul) to waste more space!

2) dbms_stats may be take longer but it is online when analyze locks the object

3) you don't search very well, in Reference:

Quote:

OPTIMIZER_MODE
...
Modifiable ALTER SESSION, ALTER SYSTEM

Querying v$parameter:

SQL> select ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE
  2  from v$parameter
  3  where name='optimizer_mode';
ISSES ISSYS_MOD ISINS
----- --------- -----
TRUE  IMMEDIATE TRUE

1 row selected.

4) Database Administrator's Guide, Chapter 15 Managing Tables, section Redefining Tables Online contains a very detailled description of how to do it.

Regards
Michel

Report message to a moderator

Re: Rebuild index online [message #245574 is a reply to message #245526]

Mon, 18 June 2007 02:57

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

There are very few instances when index rebuilds are of value.

The only two I know of are:
- A lot of rows are deleted from the table and not replaced. Such a table will benefit from a table rebuild, do that and rebuild indexes at the same time.

- Certain indexed values have been updated or deleted and never reused by similar values in the same range by other rows.
An example of this is a PK allocated by a sequence. Newer rows have higher values, older rows have lower values. If the table is periodically archived, the lower values disappear from the index and are never replaced (because all new rows contain high values).

If neither of these apply, then you will be creating more work for your database by filling up index blocks that will just have to be split again as the index fills.

I would be very wary of changing the OPTIMIZER_MODE globally. It will cause almost every multi-table query to respond differently. Definitely something you would want to test first.

Ross Leishman

Report message to a moderator

Re: Rebuild index online [message #245617 is a reply to message #245574]

Mon, 18 June 2007 04:08

tmathew
Messages: 18
Registered: May 2007

Junior Member

Thanks much Michel and Ross for the valuable inputs.

Yes,we have some script scheduled to auto deletes old transactions from the DATA_TRANS table but that won't really release space until you do rebuild indexes and deallocate space on the table. i was trying to see if there a way i can release these space back on to the tablespace online than doing it offline.would you please let me know if there any other suggestions you might have than doing the table partition.

Re: the OPTIMIZER_MODE,oracle defaults the optimizer mode=ALL_ROWS from all 10 and up versions. FIRST_ROWS is the default value on version 9. I've setup a new 10.2.3 version which has a default optimizer_mode=ALL_ROWS but it's 'FIRST_ROWS' on all other existing 10.2.2 versions(not sure how did that happen,may be it was carried forward from 9 versions or a manual update).I think most of the query performance would imporove if i change optimizer_mode=ALL_ROWS and OPEN_CURSOR to say 500(currently it has the default value 300). I also expect a better performance if the optimizer statistics are collected as most of the table statistics are stale or have none.

2) "dbms_stats may be take longer but it is online when analyze locks the object"

Michel,analyze locks the object? thought dbms_stats is calling an ANALYZE TABLE ESTIMATE STATISTCIS ESTIMATE PERCENT=10 OR 15 which minimises the lock. if it locks the object,I would be very wary of running it cause no locks would be expected on table DATA_TRANS.

Thanks again,

Mathew

Report message to a moderator

Re: Rebuild index online [message #245640 is a reply to message #245617]

Mon, 18 June 2007 04:50

Michel Cadot
Messages: 68652
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

analyze locks the object?

I have to correct myself, it depends on the analyze statement and with some it is only for small amount of time not during the whole statement.
dbms_stats is the preferred way from 9i. Oracle recommend to no more use analyze to gather statistics.
In addition, mixing analyze and dbms_stats to gather statistics may fool the optimizer.

Regards
Michel

[Updated on: Mon, 18 June 2007 14:59]

Report message to a moderator

Re: Rebuild index online [message #245780 is a reply to message #245640]

Mon, 18 June 2007 14:54

tmathew
Messages: 18
Registered: May 2007

Junior Member

Thanks Michel.

Mathew

Report message to a moderator

Previous Topic:	dbms_advisor
Next Topic:	PLan Cost?-Sql Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 17 04:17:18 CDT 2024