marrowyung
asked on
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DBMS_STATS is a command that is executable from the management console ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please show some sync, it will be good ! URL also ok !
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
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
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.
Just look for the section "Managing Optimizer Statistics".
Here are some useful scripts if that if what you prefer.
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.
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.
ASKER
let me check
ASKER
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?
"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?
ASKER
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?
"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?
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.
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.
ASKER
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?
"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?
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.
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.
ASKER
"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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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 !!
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 !!
Sorry. Not easy to explain in few sentences.
Maybe you can read some posts on http://richardfoote.wordpress.com/
especially http://richardfoote.files.
Maybe you can read some posts on http://richardfoote.wordpress.com/
especially http://richardfoote.files.
ASKER
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?