Re: analyze index validate structure in parallel ?
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 29 Jan 2024 16:49:34 -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
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.
Elapsed: 00:00:00.017
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
Other related posts:
