Re: analyze index validate structure in parallel ?
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 29 Jan 2024 16:59:28 -0500
On Mon, 2024-01-29 at 15:09 -0500, Jon Crisler wrote:
I had a case where a few indexes became corrupted. In setting up a test
where I do a "analyze index validate structure online" , I have not been
able to get it to run in parallel.
The problem is some of these indexes are huge, and take up to 20 days to
run even on a very fast 19c cluster with flash storage. I can run 50
different alter table validate structure, but it still takes 20 days for
the largest indexes.
So my challenge has been finding a way to run in parallel mode , and all
my extensive testing has produced negative results- its always single
threaded. The following never changes the behavior:
-alter table or index or both to parallel=48
-session settings to force parallel
-sql hints to do parallel.
Does anybody have a way to run "analyze index XX validate structure
online/offline" to get that operation to run with parallel / PQ ?
Thx- Jon
On the other hand, you don't need to do the "VALIDATE STRUCTURE" thing. A
query to the index should establish whether the index is formatted
correctly or not:
SQL> explain plan for
2* select empno from emp order by empno;
Explained.
Elapsed: 00:00:00.031
SQL> select * from table(dbms_xplan.display(format=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
Plan hash value: 179099197
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 14 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 |
--------------------------------------------
If the index contains corrupt or sleazy blocks, the query will throw an
error. Plus side of this method is that it is easy to make the query
parallel. BTW, the difference between a full scan and a fast full scan is
precisely that FFS only reads leaf blocks but the full scan reads all block
in the index, including the branch blocks. Here we do want the full scan.
Regards
--
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com
Other related posts:
