Re: analyze index validate structure in parallel ?

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


--
http://www.freelists.org/webpage/oracle-l