Re: query hanged in parsing

From: Mladen Gogala <gogala.mladen_at_gmail.com>
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-l
Received on Sat May 11 2024 - 22:59:40 CEST

Original text of this message