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@xxxxxxxxx>
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@xxxxxxxxx>
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@xxxxxxxxx> 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
