Rebuild index online [message #245466] |
Sun, 17 June 2007 13:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Rebuild index online [message #245500 is a reply to message #245467] |
Sun, 17 June 2007 22:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Re: Rebuild index online [message #245574 is a reply to message #245526] |
Mon, 18 June 2007 02:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Rebuild index online [message #245617 is a reply to message #245574] |
Mon, 18 June 2007 04:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Rebuild index online [message #245640 is a reply to message #245617] |
Mon, 18 June 2007 04:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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
|
|
|
|