Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 854
  • Last Modified:

Rebuilt Oracle index and update statistics.

Dear all,

  I come from MSSQL background, may I know if it is the same concept of Oracle to first rebuilt index and then update statistics?

  From the Oralce 10g and 11g management console to:
1) what is the command/script to rebuilt all index/rebuitl index of some./all table.
2) what is the % of defragementation of the index befoer we should rebuilt for Oracle ? any online rebuilt option and what is it is ?
3) what is the script to update the statistis of all index?
0
marrowyung
Asked:
marrowyung
  • 9
  • 5
  • 2
  • +1
3 Solutions
 
MikeOM_DBACommented:
In 10g or 11g Enterprise Manager console:

From Schema section:

1) Indexes -> select the index -> (top right) Actions -> Reorganize
   Tables -> select the table -> (top right) Actions -> Manage  Optimizer statistics

From the table/indexes list page you can select "multiple" mode, flag the tables/indexes and select the option you want from the Actions drop-down.

2) When you select index, there you will find this information.

3) Use DBMS_STATS package.
0
 
marrowyungAuthor Commented:
"Use DBMS_STATS package."

how to use it ? any step by step guide ?


'1) Indexes -> select the index -> (top right) Actions -> Reorganize
   Tables -> select the table -> (top right) Actions -> Manage  Optimizer statistics"

Any script based?
0
 
marrowyungAuthor Commented:
DBMS_STATS is a command that is executable from the management console ?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
DBMS_STATS is an oracle inbuilt package which has a lot of procedures/functions to do the stats gathering at object level, schema level etc....

I do not have management console, so not sure whether the package.procedure/function call can be made from it.

But if you have sql*plus session, then you can easily do it with 1 or 2 line commands.

Thanks,
0
 
marrowyungAuthor Commented:
Please show some sync, it will be good ! URL also ok !
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Just read through these and they have all commands with syntax etc. As you know, no need to read everyline etc, just read through & grab whatever you are interested in from those links.

http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php
http://www.oracle-base.com/articles/11g/statistics-collection-enhancements-11gr1.php
http://psoug.org/reference/dbms_stats.html
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm
0
 
MikeOM_DBACommented:
DBMS_STATS is a command that is executable from the management console .
Just look for the section "Managing Optimizer Statistics".

Here are some useful scripts if that if what you prefer.
0
 
Franck PachotCommented:
Hi,

When you rebuild the index, statistics are automatically calculated (when >= 10g)

On Oracle, indexes do not become fragmented in most cases:
 - space released by deleted rows can be reused by inserts/update that go in the same range of values
 - when the whole index block is free, it can be reused by any insert/update.

And when you are in the rare case where space is not reused (lots of delete on a range of value that will never come in again - but not whole blocks) an ALTER INDEX COALESCE is probably sufficient -> online, faster and cheaper.

Regards,
Franck.
0
 
marrowyungAuthor Commented:
let me check
0
 
marrowyungAuthor Commented:
franckpachot,

"When you rebuild the index, statistics are automatically calculated (when >= 10g)"

this is good point as MS SQL don't do this and Oracle know if we rebuilt the index, the statistics needs to be update anyway.

"On Oracle, indexes do not become fragmented in most cases:
 - space released by deleted rows can be reused by inserts/update that go in the same range of values
 - when the whole index block is free, it can be reused by any insert/update.:"

This is good when compare with MS SQL. But any URL show this ?

"ALTER INDEX COALESCE"

this will rebuilt all index of all database on that database server/instance?
0
 
marrowyungAuthor Commented:
one of my question is :

"what is the % of defragementation of the index befoer we should rebuilt for Oracle ? any online rebuilt option and what is it is ?"

What is the % ? or no need to worry about that in Oralce as the data page can be reuse for data on the same data range ?

franckpachot,

"And when you are in the rare case where space is not reused (lots of delete on a range of value that will never come in again - but not whole blocks)"

based on this, there must be some figure tells that we need to do it now and what is it and where can I found that?
0
 
Franck PachotCommented:
Hi,

No, there is no figures. An index need to be fragmented to be able to accept inserts without too much block splits. I mean:
If a block is fully packed (100% full) then the next insert will split it and you will have two blocks filled at 50%. So on average an index that have 20-30% of free space is a good index.

If you want to read more, the Oracle index specialist is http://richardfoote.wordpress.com/

An idea: check the index size, to an alter index <index_name> coalesce; wait a few days and check the size again. Most of indexes will come to their original size.

If the index is still much smaller, then you can investigate why it became too big.

Regards,
Franck.
0
 
marrowyungAuthor Commented:
franckpachot,

"If a block is fully packed (100% full) then the next insert will split it and you will have two blocks filled at 50%. So on average an index that have 20-30% of free space is a good index."

same as MS SQL.

"An idea: check the index size, to an alter index <index_name> coalesce; wait a few days and check the size again. Most of indexes will come to their original size.

What is the logic behind that? why I need to do this and what am I suppose to see ?

come to their original size mean the index compacted in anyway?

"If the index is still much smaller, then you can investigate why it became too big."

there is a bit confused, still smaller but big later?
0
 
Franck PachotCommented:
Hi,

Here is what I mean.

You have index A it is 100GB. You rebuild it it is 60GB. Then some days later it is 100GB and stays like that -> rebuild was not useful. Don't do it again.

You have index B it is 100GB, you rebuild it is 20GB, Some days later it is still 20GB. -> rebuild was useful. You find what happend in the table (maybe purge old data) and you will plan to rebuild it again in the same situation.

You have index C it is constantly growin. You rebuild it. It is smaller but again constantly growing. Regular rebuilds may be a quick workaround, but you need to investigate what is done on the table that makes that situation.
0
 
marrowyungAuthor Commented:
"You have index A it is 100GB. You rebuild it it is 60GB. Then some days later it is 100GB and stays like that -> rebuild was not useful. Don't do it again."

ahha, so rebuilt index is not a good idea in Oracle as the index can be reuse for the same data range, right?

"You have index B it is 100GB, you rebuild it is 20GB, Some days later it is still 20GB. -> rebuild was useful. You find what happend in the table (maybe purge old data) and you will plan to rebuild it again in the same situation."

then I do this "ALTER INDEX COALESCE", right?

"You have index C it is constantly growin. You rebuild it. It is smaller but again constantly growing. Regular rebuilds may be a quick workaround, but you need to investigate what is done on the table that makes that situation. "

Same as A and what is the difference ? but index size growing should be ok as data is growing ?
0
 
Franck PachotCommented:
>> ahha, so rebuilt index is not a good idea in Oracle as the index can be reuse for the same data range, right?

Totally right. As soon as the transaction that has deleted rows is commited, the space can be reused in the leaf blocks for same data ranges.

>> then I do this "ALTER INDEX COALESCE", right?
Yes. In most of the cases coalece is sufficient.

>> Same as A and what is the difference ? but index size growing should be ok as data is growing ?

I was thinking of cases where data is not growing a lot (because of regularily purging old data) but index grows as if there were no purge.

Those are the very rare cases where a regular coalesce can be good.

An example:
An ORDERS table. You purge all data older than one year except for the few VIP customers that you want to keep longer. Indexes on increasing values (date, sequence number, etc) may keep growing even when you purge. because:
1- you never insert in the same range
2- blocks are released only when they are 100% empty.
So the blocks that have only 1 row in them will stay and you never insert into them -> growing as if you did not purge anything.
0
 
marrowyungAuthor Commented:
"I was thinking of cases where data is not growing a lot (because of regularily purging old data) but index grows as if there were no purge. "

the index will be dropping also at the same time ? it should be ?

"1- you never insert in the same range
2- blocks are released only when they are 100% empty.
So the blocks that have only 1 row in them will stay and you never insert into them -> growing as if you did not purge anything. "

an empty table with indentity column that increase without data?  


I don't insert anything and it still grow ?


"You have index A it is 100GB. You rebuild it it is 60GB."

One thing, why rebuild the index will make it smaller ? but later on growth again !!
0
 
Franck PachotCommented:
Sorry. Not easy to explain in few sentences.
Maybe you can read some posts on  http://richardfoote.wordpress.com/
especially http://richardfoote.files.wordpress.com/2007/12/index-internals-rebuilding-the-truth.pdf I should be clear after :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now