Solved

Rebuilt Oracle index and update statistics.

Posted on 2013-06-10
18
574 Views
Last Modified: 2013-08-11
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
Comment
Question by:marrowyung
  • 9
  • 5
  • 2
  • +1
18 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 39236123
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39236734
"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
 
LVL 1

Author Comment

by:marrowyung
ID: 39236735
DBMS_STATS is a command that is executable from the management console ?
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 39236763
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39236782
Please show some sync, it will be good ! URL also ok !
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39236831
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39237410
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39240213
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39278389
let me check
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
ID: 39384885
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39384919
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39386341
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39388197
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39390664
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39392311
"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
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 300 total points
ID: 39392674
>> 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
 
LVL 1

Author Comment

by:marrowyung
ID: 39396353
"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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 39396493
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now