Re: Ora error while during explain plan
Date: Wed, 10 Mar 2021 14:15:32 +0300
Message-ID: <CAOVevU7pkrgpvATYSZm16xFriU++uRm=vy_oEW0zxMmEFVKdvA_at_mail.gmail.com>
Hi Pap,
But the doc you show is not about ORA-00600: internal error code,
arguments: [ktrenvchk_1], [0], [1], [0], [1],
Is it happening on ADG standby?
On Wed, Mar 10, 2021 at 2:14 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
wrote:
>
ktr*env*chk_1 - I would check the doc Bug 20299690 - ORA-00600
[ktrenvchk_1] error occurs on Standby DB & sometimes leading to hang or
slow down (Doc ID 20299690.8)
and optimizer env values
> 2 minutes to parse "select 1 from dual" sounds like a memory problem - it
> may be that the problem will disappear if you restart the instance.
>
> Before doing that, though, I'd enable extended (level 12, i.e. waits and
> binds) sql-trace and repeat the explain plan for select 1 from dual to see
> if the resulting trace file showed me exactly what happened just before the
> error occurred - I'd guess that some recursive SQL that was trying to load
> something into the dictionaty cache was running into a problem. If that
> trace doesn't give you any clues I'd try the same with the simplest
> possible statement that does cause the ORA-00600.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 10 Mar 2021 at 11:00, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Hi All, Oracle version is - 12.1.0.2.0 and its Exadata. I am seeing
>> Ora-600 while fetching explain plan through "set autotrace traceonly
>> explain". Even explain plan for "Select 1 from dual" is working but that is
>> also taking ~2minutes and that too failing with the same Ora-600 for other
>> queries. I got a related bug from mos, but the work around which is
>> provided as setting "_optimizer_push_pred_cost_based" to false is also
>> not working, which means we are hitting some other bug. Has anybody
>> encountered such a scenario?
>>
>> Explain Plan for Query Errors with 'ORA-00600: internal error code,
>> arguments: [KGL-heap-size-exceeded]' (Doc ID 2469727.1)
>>
>> SQL> select 1 from dual;
>>
>>
>> Execution Plan
>>
>> ----------------------------------------------------------
>>
>> ERROR:
>>
>> ORA-00600: internal error code, arguments: [ktrenvchk_1], [0], [1], [0],
>> [1],
>>
>> [0], [0], [0], [], [], [], []
>>
>>
>>
>>
>> SP2-0612: Error generating AUTOTRACE EXPLAIN report
>>
>
-- Best regards, Sayan Malakshinov Oracle performance tuning engineer Oracle ACE Associate http://orasql.org -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 10 2021 - 12:15:32 CET