| 9i Online Validate index is Taking More time. [message #57091] |
Tue, 20 May 2003 05:52  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
In Oracle 9i
sql> Analyze index "index_name" validate structure;
Index analyzed.
Elapsed: 00:00:13.02
But If Use Online Option
sql> Analyze index "index_name" validate structure online;
Index analyzed.
Elapsed: 01:03:16.01
What Makes online option to take 3 Hours Extra though
Oracle Claims, It doesn't report for Resource Busy
error, Then What is helding up in Oracle Engine to Consume this much amt of time. Please Some one Explain me.Or the Way of using this syntax is wrong ?
|
|
|
|
| Re: 9i Online Validate index is Taking More time. [message #57093 is a reply to message #57091] |
Tue, 20 May 2003 07:16  |
Naveen
Messages: 75
Registered: June 2001
|
Member |
|
|
Hi Prasad,
The statement you issued prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object. That is why oracle recommends not to issue this at the time of high database activity whereas the "Validate strucuture online" does not do this. What happens is oracle allows the queries to access the index while it is perfroming the validation. Following is a paragraph from documentation:
When the ONLINE keyword is used as part of the CREATE or ALTER syntax the current index is left intact while a new copy of the index is built, allowing DML to access the old index. Any alterations to the old index are recorded in a Index Organized Table known as a journal table. Once the rebuild is complete the alterations from the journal table are merged into the new index. This may take several passes depending on the frequency of alterations to the index. The process will skip any locked rows and commit every 20 rows. Once the merge operation is complete the data dictionary is updated and the old index is dropped. DML access is only blocked during the data dictionary updates, which complete very quickly.
As oracle has to make several passes, that is the reason why it is taking lot of time.
Hope this helps. Feel free to post again if you have some doubts.
Regards----Naveen.
|
|
|
|
| Re: 9i Online Validate index is Taking More time. [message #57108 is a reply to message #57093] |
Tue, 20 May 2003 23:05 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Naveen
Thanx for the Info,
But Here I am not Rebuilding the Index Online, I am just Validating the Index to Populate the Index_stats table with Max_del and row_height columns. So What Oracle Claims is if U use Online Validate structure
in 9i It won't report with "Resource Busy" Error,That Means parallel operations are supported,But Really
I don't feel it is happening.
What Ever U are trying to say is absolutely true for
Rebuilding Indexes online, But I am not doing that.
So Why don't try this option in U'r Test database with
Millions of records populated in that table and index.
and tell me What U hv exprienced.
Regards
Prasad
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
