Re: Ora error while during explain plan

Thank you. I think you are spot on. The database which i am testing the explain plan on is currently physical standby or is in Read Only mode. So i think i should see the error stating that the data cant be written to plan_table but anyway it seems the bug which you are pointing to is the one we are hitting here. I ran it on the other side(i.e primary side) and the explain plan works fine. Just for information, actually this switching between primary and standby databases happened as part of our resiliency testing last weekend(I almost missed that point) . Also it seems there is no workaround for this bug.

Regards
Pap

On Wed, Mar 10, 2021 at 4:46 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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?
> 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
>
> On Wed, Mar 10, 2021 at 2:14 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> 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-l