Re: Ora error while during explain plan
Date: Wed, 10 Mar 2021 11:13:57 +0000
Message-ID: <CAGtsp8nthB4M6p+O7UyyzNfSRqigmvToedOWFJ+-a0oAD1=bXg_at_mail.gmail.com>
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
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
Jonathan Lewis
> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 10 2021 - 12:13:57 CET