analyze index validate structure in parallel ?
From: Jon Crisler <joncrisler_at_gmail.com>
Date: Mon, 29 Jan 2024 15:09:18 -0500
Message-ID: <CAB44qRS_X=F_tZZdEHO4XSJJhqY+PQDa2PwPQsNsRK7ePY0vxA_at_mail.gmail.com>
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.
Date: Mon, 29 Jan 2024 15:09:18 -0500
Message-ID: <CAB44qRS_X=F_tZZdEHO4XSJJhqY+PQDa2PwPQsNsRK7ePY0vxA_at_mail.gmail.com>
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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 29 2024 - 21:09:18 CET