Re: query hanged in parsing
Date: Sat, 11 May 2024 16:59:40 -0400
Message-ID: <bf175cba76c32b2db38faf75945fe4801624a9d6.camel_at_gmail.com>
On Sat, 2024-05-11 at 18:06 +0300, Laurentiu Oprea wrote:
> qkaPlanSignatureCB: +SCAN_INDEX_QKNTYP(44) (nil) qkn_qksctxPlanSig seed =
> 586bc363 -.-> hangs for minutes sometimes
>
> What is particular about this :SCAN_INDEX_QKNTYP(44) is an index with an
> interval table with over 65K partitions.
>
> Any hint on how I can overcome this problem will be highly appreciated.
> Or maybe how I can dig more into what that step qkaPlanSignatureCB is
> doing (tracing I did is SQL_Compile* and PART* disk high)
Is that a global index or a local one? If that is a global index, in my
opinion 65K partitions is too many for a global index. That table is
probably huge, terabytes in size. Please do a classic 10046 trace and see
what event can this "hanging" be attributed to. I would expect "db file
sequential scan" to be at the top, with minutes of time accounted to it. In
that case, the only help is super-duper hardware: SSD disks, the fastest
CPU that you can find (I would try ARM based servers) and a lot of DDR5
RAM. In other words, what you need is HAL 9000, Holly from the Red Dwarf or
maybe Skynet from one popular SF franchise.
-- Mladen Gogala Database SME https://dbwhisperer.wordpress.com -- http://www.freelists.org/webpage/oracle-lReceived on Sat May 11 2024 - 22:59:40 CEST