ORA-600[qernsRowP] from DBMS_STATS
(Doc ID 1922432.1)
Last updated on MARCH 24, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
DBMS_STATS fails with the following error in the alert log:
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
The associated incident trace file shows this Current SQL Statement:
select min(minbkt),maxbkt,substrb(dump(min(val),16,0,64),1,240) minval,substrb(dump(max(val),16,0,64),1,240) maxval,sum(rep) sumrep, sum(repsq) sumrepsq, max(rep) maxrep, count(*) bktndv, sum(case when rep=1 then 1 else 0 end) unqrep from (select val,min(bkt) minbkt, max(bkt) maxbkt, count(val) rep, count(val)*count(val) repsq from (select /*+ no_expand_table(t) index_rs(t) parallel(t,4) parallel_index(t,4) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl no_substrb_pad */"SAVTIME" val, ntile(254) over (order by "SAVTIME") bkt from "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" t where "SAVTIME" is not null) group by val) group by maxbkt order by maxbkt
and this PL/SQL Call Stack:
----- PL/SQL Call Stack -----
object line object
handle number name
0x1d3187228 27187 package body SYS.DBMS_STATS
0x1d3187228 27635 package body SYS.DBMS_STATS
0x1d3187228 31630 package body SYS.DBMS_STATS
0x1d3187228 33137 package body SYS.DBMS_STATS
0x1d3187228 34029 package body SYS.DBMS_STATS
0x1d3187228 34972 package body SYS.DBMS_STATS
0x1d3187228 35711 package body SYS.DBMS_STATS
0x1d3187228 36230 package body SYS.DBMS_STATS
0x1d3187228 36301 package body SYS.DBMS_STATS
0x1cde52d98 1 anonymous block
object line object
handle number name
0x1d3187228 27187 package body SYS.DBMS_STATS
0x1d3187228 27635 package body SYS.DBMS_STATS
0x1d3187228 31630 package body SYS.DBMS_STATS
0x1d3187228 33137 package body SYS.DBMS_STATS
0x1d3187228 34029 package body SYS.DBMS_STATS
0x1d3187228 34972 package body SYS.DBMS_STATS
0x1d3187228 35711 package body SYS.DBMS_STATS
0x1d3187228 36230 package body SYS.DBMS_STATS
0x1d3187228 36301 package body SYS.DBMS_STATS
0x1cde52d98 1 anonymous block
It also shows this Call Stack Trace:
... qernsRowP qerpxFetch qernsFetch qervwFetch qergsFetch opifch2 opifch opipls opiodr rpidrus skgmstack rpiswu2 rpidrv psddr0 psdnal pevm_EXIM pfrinstr_EXIM pfrrun_no_tool pfrrun plsql_run peicnt kkxexe opiexe kpoal8 opiodr ...
and the Plan Table mentions NOSORT:
============
Plan Table
============
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
| 0 | SELECT STATEMENT | | | | 1513 | | | | | | |
| 1 | SORT GROUP BY | | 27K | 1788K | 1513 | 00:00:18 | | | | | |
| 2 | VIEW | | 27K | 1788K | 1512 | 00:00:18 | | | | | |
| 3 | SORT GROUP BY NOSORT | | 27K | 694K | 1512 | 00:00:18 | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10001 | 323K | 8394K | 1512 | 00:00:18 |:Q1001| P->S |QC (RANDOM)| | |
| 6 | VIEW | | 323K | 8394K | 1512 | 00:00:18 |:Q1001| PCWP | | | |
| 7 | WINDOW SORT | | 323K | 4197K | 1512 | 00:00:18 |:Q1001| PCWP | | | |
| 8 | PX RECEIVE | | 323K | 4197K | 1508 | 00:00:18 |:Q1001| PCWP | | | |
| 9 | PX SEND RANGE | :TQ10000 | 323K | 4197K | 1508 | 00:00:18 |:Q1000| P->P |RANGE | | |
| 10 | PX BLOCK ITERATOR | | 323K | 4197K | 1508 | 00:00:18 |:Q1000| PCWC | | 1 | 1048575|
| 11 | TABLE ACCESS FULL | WRI$_OPTSTAT_HISTHEAD_HISTORY| 323K | 4197K | 1508 | 00:00:18 |:Q1000| PCWP | | 1 | 1048575|
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
Plan Table
============
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ Distrib | Pstart| Pstop |
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
| 0 | SELECT STATEMENT | | | | 1513 | | | | | | |
| 1 | SORT GROUP BY | | 27K | 1788K | 1513 | 00:00:18 | | | | | |
| 2 | VIEW | | 27K | 1788K | 1512 | 00:00:18 | | | | | |
| 3 | SORT GROUP BY NOSORT | | 27K | 694K | 1512 | 00:00:18 | | | | | |
| 4 | PX COORDINATOR | | | | | | | | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10001 | 323K | 8394K | 1512 | 00:00:18 |:Q1001| P->S |QC (RANDOM)| | |
| 6 | VIEW | | 323K | 8394K | 1512 | 00:00:18 |:Q1001| PCWP | | | |
| 7 | WINDOW SORT | | 323K | 4197K | 1512 | 00:00:18 |:Q1001| PCWP | | | |
| 8 | PX RECEIVE | | 323K | 4197K | 1508 | 00:00:18 |:Q1001| PCWP | | | |
| 9 | PX SEND RANGE | :TQ10000 | 323K | 4197K | 1508 | 00:00:18 |:Q1000| P->P |RANGE | | |
| 10 | PX BLOCK ITERATOR | | 323K | 4197K | 1508 | 00:00:18 |:Q1000| PCWC | | 1 | 1048575|
| 11 | TABLE ACCESS FULL | WRI$_OPTSTAT_HISTHEAD_HISTORY| 323K | 4197K | 1508 | 00:00:18 |:Q1000| PCWP | | 1 | 1048575|
--------------------------------------------------------------------+-----------------------------------+-------------------------+----------------+
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |