Re: ORA-00600: [xplSetRws:1] and a query with large number of nested ORs
Date: Thu, 26 Mar 2015 18:50:51 +0100 (CET)
Message-ID: <743052557.250036.1427392251593.JavaMail.open-xchange_at_app09.ox.hosteurope.de>
Hi Jeff,
ok, so you can not change the SQL generation or the SQL itself as this is a 3rd party app and not your own.
I would go for this "procedure":
1) Generate the smallest (100 subqueries as mentioned by you) and the largest (1000 subqueries as mentioned by you) SQL that is possible / common
2) Check the amount of used memory in library cache for both SQLs. I would expect that the larger SQLs exceed 4,400 bytes
(_shared_pool_reserved_min_alloc) of memory and are appropriate for shared_pool_reserved_size.
3) Scale up the measured memory usage in library cache and adjust shared_pool_reserved_size accordingly
4) Verify execution plan (due to CBO default limits with table functions and variable in-lists) and convince software vendor to implement "the
extensible optimizer" as needed, if SQL runtime does not fit business requirements and the root cause is a bad execution plan.
Jonathan Lewis explained the handling of larger SQLs in library cache in great detail in his book "Oracle Core" (Chapter 7 - PARSING AND OPTIMIZING). I think it is important to understand how the (reserved) free list buckets are handled and how the chunks (for reserved pool) are allocated / reserved in each extent to get this working properly.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Jeff Thomas <dbmangler_at_gmail.com> hat am 26. März 2015 um 14:41 geschrieben:
>
> Stefan - thanks for your reply. I was amiss by not mentioning our system: 11.2.0.3 4-node cluster on x86-64. The code is basically coming from
> a 3rd-party engine with some in-house customizations. I don't think the developers understood the consequences of what they were doing here. My
> questions were more an exercise in considering this situation rather than I'm looking for definite solutions.
>
> Best,
> Jeff
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 26 2015 - 18:50:51 CET