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

Hi Jon,
Have you tried setting the index degree?  Something like this:

SQL> alter index scott.pk_emp parallel 4;

Index SCOTT.PK_EMP altered.

Elapsed: 00:00:00.013

The "ALTER INDEX" command is not DML, so enabling parallel DML will probably not help. You can't explain plan for the ANALYZE command, either:

SQL> explain plan for
  2* select count(*) from scott.emp;

Explained.

Elapsed: 00:00:00.016
SQL> explain plan for
  2* analyze index scott.pk_emp validate structure;

Error starting at line : 1 in command -
explain plan for
analyze index scott.pk_emp validate structure Error report -
ORA-00905: missing keyword
00905. 00000 - "missing keyword"

*Document: YES
*Cause:    A required keyword was missing.
*Action:   Add the required keyword to correct the syntax.

The only thing that might work is setting the degree of parallelism for the index and then monitor using top, htop or nmon. On the other hand, an index is a hierarchical structure. Validation includes verifying the links from each and every branch block and verifying the number of entries in each and every leaf block. It's very hard to parallelize access to a hierarchical structure and index is an epitome of hierarchical structures, just like DMV. I live in the US for 30 years and they still haven't managed to parallelize work in DMV to any meaningful degree.

-- 
Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com


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