OS환경 : Oracle Linux7.2(64bit)
DB 환경 : Oracle Database 12.2.0.2 이상 18.3 까지
에러 : ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
테스트 테이블 생성
rem
rem Script: descending_bug_06.sql
rem Author: Jonathan Lewis
rem Dated: Aug 2018
rem Purpose:
rem
rem Last tested
rem 18.3.0.0 Crashes
rem 12.2.0.1 Crashes
rem 12.1.0.2 Crashes
rem 11.2.0.4 Bad Plan
rem
create table t1
nologging
pctfree 95 pctused 5
as
with generator as (
select
rownum id
from dual
connect by
level <= 1e4 -- > comment to avoid WordPress format issue
)
select
rownum id,
lpad(rownum,10,'0') v1,
case mod(rownum,1000)
when 0 then 'A'
when 3 then 'B'
when 6 then 'C'
else 'D'
end sts,
case mod(rownum,1000)
when 0 then '1'
when 3 then '2'
when 6 then '3'
else '4'
end cnt,
lpad('x',100,'x') padding
from
generator v1,
generator v2
where
rownum <= 1e5 -- > comment to avoid WordPress format issue
;
Table created.
인덱스 생성
create index t1_i1a on t1(sts) nologging;
create index t1_i1d on t1(sts desc) nologging;
Index created.
Index created.
통계정보 수집
begin
dbms_stats.gather_table_stats(
ownname => null,
tabname => 'T1',
method_opt => 'for all columns size skewonly'
);
end;
/
PL/SQL procedure successfully completed.
설명
There is one oddity in this script ? if you’ve got every column in an index declared as DESC you’ve made a mistake and none of the columns should be declared as DESC. The feature is relevant only if you want a mixture of ascending and descending column in a single index.
An important detail of the script is that I’ve gathered stats AFTER creating the objects ? it’s important to do this, even in 18.3, because (a) creating the “descending” index will result in a hidden virtual column being created to represent the descending column and I want make sure I have stats on that column and (b) the “stats on creation” code doesn’t generate histograms and I want a (frequency) histogram on columns sts and the hidden, virtual, descending version of the column.
After generating the data and checking that I have the correct histograms for sts and sys_nc00006$ (the relevant hidden column) I can then run the following test:
통계정보 수집 레벨 변경
set serveroutput off
alter session set statistics_level = all;
Session altered.
인덱스(t1_i1d) 숨김처리
alter index t1_i1d invisible;
Index altered.
테스트 쿼리로 조회
select sts, count(*)
from t1
where sts in ('B','C')
group by
sts
;
S COUNT(*)
- ----------
B 100
C 100
2 rows selected.
실행계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9c44akky7va9v, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by
sts
Plan hash value: 1752550624
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 5 |
| 1 | SORT GROUP BY NOSORT| | 1 | 2 | 2 |00:00:00.01 | 5 |
| 2 | INLIST ITERATOR | | 1 | | 200 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | T1_I1A | 2 | 200 | 200 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("STS"='B' OR "STS"='C'))
21 rows selected.
인덱스 t1_i1d 보임처리 후 다시 숨김처리
alter index t1_i1d visible;
alter index t1_i1a invisible;
Index altered.
Index altered.
테스트 쿼리로 재조회
select sts, count(*)
from t1
where sts in ('B','C')
group by
sts
;
select sts, count(*)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
(에러발생)
실행계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9c44akky7va9v, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by
sts
Plan hash value: 1217312333
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 0 |
| 1 | SORT GROUP BY NOSORT| | 1 | 2 | 0 |00:00:00.01 | 0 |
| 2 | INLIST ITERATOR | | 1 | | 101 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | T1_I1D | 2 | 200 | 101 |00:00:00.01 | 5 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"."SYS_NC00006$"=SYS_OP_DESCEND('B') OR "T1"."SYS_NC00006$"=SYS_OP_DESCEND('C')))
filter((SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='B' OR SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='C'))
24 rows selected.
통계정보 수집 레벨 기존값으로 변경
alter session set statistics_level = typical;
set serveroutput on
설명
The code makes one index invisible then runs a query that should use an inlist iterator; then it switches indexes making the invisible one visible and vice versa and repeats the query. I’ve enabled rowsource execution statistics and pulled the execution plans from memory to make sure I don’t get fooled by any odd glitches that might exist within “explain plan”.
해결방법
Set <event:10119> to disable no-sort fetch and then Reparse the failing SQL.
<event : 10119>를 설정하여 no-sort fetch를 사용 불가능하게 한 다음 실패한 SQL을 재실행(Reparse)하십시오.
SQL> alter session set events '10119 trace name context forever, level 12';
SQL> alter system flush shared_pool;
테스트 쿼리로 재조회
select sts, count(*)
from t1
where sts in ('B','C')
group by
sts
;
S COUNT(*)
- ----------
C 100
B 100
2 rows selected.
실행계획 확인
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9c44akky7va9v, child number 0
-------------------------------------
select sts, count(*) from t1 where sts in ('B','C') group by
sts
Plan hash value: 3770517443
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 5 | | | |
| 1 | HASH GROUP BY | | 1 | 2 | 2 |00:00:00.01 | 5 | 1600K| 1600K| 646K (0)|
| 2 | INLIST ITERATOR | | 1 | | 200 |00:00:00.01 | 5 | | | |
|* 3 | INDEX RANGE SCAN| T1_I1D | 2 | 200 | 200 |00:00:00.01 | 5 | | | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("T1"."SYS_NC00006$"=SYS_OP_DESCEND('B') OR "T1"."SYS_NC00006$"=SYS_OP_DESCEND('C')))
filter((SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='B' OR SYS_OP_UNDESCEND("T1"."SYS_NC00006$")='C'))
22 rows selected.
복사 붙여넣기용 테스트 스크립트
내림차순버그.TXT참조 : http://www.oaktable.net/content/descending-bug
메타링크 문서(285913.1)