The following test case produces ORA-07445 in Oracle 18c and 19c:
create table t1 (n1 number);
create table t2 (n2 number);
create table t3 (n3 number);
create table t4 (n4 number);
set pagesize 200
set linesize 200
with
cte1 as (
select /*+ qb_name(qb_cte1) */ n1 from t1 join t2 on t1.n1 = t2.n2
),
cte2 as (
select /*+ qb_name(qb_cte2) inline */ t3.n3
from t3 left join cte1 on cte1.n1 = t3.n3
),
cte3 as (
select /*+ qb_name(qb_cte3) */ t4.n4
from t4 join cte2 on cte2.n3 = t4.n4
)
select /*+ qb_name(qb_main) */ 1
from cte3 cte3_1 join cte3 cte3_2 on cte3_1.n4 = cte3_2.n4 ;The query completes after removing the inline hint. In 12c, the hint doesn’t make any difference. That’s expected, because optimizer wouldn’t materialize qb_cte2 anyway, as it’s referenced only once (in qb_main).
The alternative syntax introduced in 18c [1] doesn’t help:
with
cte1 as (
select /*+ qb_name(qb_cte1) */ n1 from t1 join t2 on t1.n1 = t2.n2
),
cte2 as (
select /*+ qb_name(qb_cte2) */ t3.n3
from t3 left join cte1 on cte1.n1 = t3.n3
),
cte3 as (
select /*+ qb_name(qb_cte3) inline(@qb_cte2) */ t4.n4
from t4 join cte2 on cte2.n3 = t4.n4
)
select /*+ qb_name(qb_main) */ 1
from cte3 cte3_1 join cte3 cte3_2 on cte3_1.n4 = cte3_2.n4 ;What’s different in 18c?
Optimizer confused inline with materialize when transforming the query:
CBQT: copy not possible on query block QB_CTE2 (#-1) because materialized query blockConsequently, it preserved qb_cte2 as a unit:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("QB_MAIN") */ 1 "1" FROM "SYS"."T4" "T4", (SELECT /*+ QB_NAME ("QB_CTE2") */ "T3"."N3" "N3" FROM "SYS"."T3" "T3", (SELECT /*+ QB_NAME ("QB_CTE1") */ "T1"."N1" "N1" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."N1"="T2"."N2") "CTE1" WHERE "CTE1"."N1"(+)="T3"."N3") "CTE2", (SELECT /*+ QB_NAME ("QB_CTE3") */ "T4"."N4" "N4" FROM "SYS"."T4" "T4", (SELECT /*+ QB_NAME ("QB_CTE2") */ "T3"."N3" "N3" FROM "SYS"."T3" "T3", (SELECT /*+ QB_NAME ("QB_CTE1") */ "T1"."N1" "N1" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."N1"="T2"."N2") "CTE1" WHERE "CTE1"."N1"(+)="T3"."N3") "CTE2" WHERE "CTE2"."N3"="T4"."N4") "CTE3_2" WHERE "T4"."N4"="CTE3_2"."N4" AND "CTE2"."N3"="T4"."N4"Without the hint, qb_cte2 is taken apart:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("QB_MAIN") */ 1 "1" FROM (SELECT /*+ QB_NAME ("QB_CTE2") QB_NAME ("QB_CTE3") */ "T4"."N4" "N4" FROM "SYS"."T4" "T4","SYS"."T3" "T3", (SELECT /*+ QB_NAME ("QB_CTE1") */ "T1"."N1" "N1" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."N1"="T2"."N2") "CTE1" WHERE "T3"."N3"="T4"."N4" AND "CTE1"."N1"(+)="T3"."N3") "CTE3_1", (SELECT /*+ QB_NAME ("QB_CTE2") QB_NAME ("QB_CTE3") */ "T4"."N4" "N4" FROM "SYS"."T4" "T4","SYS"."T3" "T3", (SELECT /*+ QB_NAME ("QB_CTE1") */ "T1"."N1" "N1" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."N1"="T2"."N2") "CTE1" WHERE "T3"."N3"="T4"."N4" AND "CTE1"."N1"(+)="T3"."N3") "CTE3_2" WHERE "CTE3_1"."N4"="CTE3_2"."N4"The execution plan is the same as in 12c in this case:
with
cte1 as (
select /*+ qb_name(qb_cte1) */ n1 from t1 join t2 on t1.n1 = t2.n2
),
cte2 as (
select /*+ qb_name(qb_cte2) */ t3.n3
from t3 left join cte1 on cte1.n1 = t3.n3
),
cte3 as (
select /*+ qb_name(qb_cte3) */ t4.n4
from t4 join cte2 on cte2.n3 = t4.n4
)
select /*+ qb_name(qb_main) */ 1
from cte3 cte3_1 join cte3 cte3_2 on cte3_1.n4 = cte3_2.n4 ;
select * from table(dbms_xplan.display_cursor(null,null,'alias')) ;
Plan hash value: 167971766
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6E2A_1C8381D | | | | |
|* 3 | HASH JOIN OUTER | | 1 | 39 | 9 (12)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T4 | 1 | 13 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T3 | 1 | 13 | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 13 | 4 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 1 | 26 | 4 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E2A_1C8381D | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E2A_1C8381D | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3C08296D
2 - SEL$AC27D75A
5 - SEL$AC27D75A / T4@SEL$1
6 - SEL$AC27D75A / T3@SEL$3
7 - SEL$395D0DB9 / CTE1@SEL$3
8 - SEL$395D0DB9
9 - SEL$395D0DB9 / T1@SEL$4
10 - SEL$395D0DB9 / T2@SEL$4
12 - SEL$95F3F249 / CTE3_1@SEL$2
13 - SEL$95F3F249 / T1@SEL$95F3F249
14 - SEL$95F3F24A / CTE3_2@SEL$2
15 - SEL$95F3F24A / T1@SEL$95F3F24A
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CTE1"."N1"="T3"."N3")
4 - access("T3"."N3"="T4"."N4")
8 - access("T1"."N1"="T2"."N2")
11 - access("CTE3_1"."N4"="CTE3_2"."N4")Only qb_cte3 was materialized, because it’s referenced twice in qb_main.
The segmentation fault (with the hint) occurs in the Oracle C function qksqbCheckProp(), because the CPU register r14 contains an invalid address:
ORA-07445: exception encountered: core dump [qksqbCheckProp()+154] [SIGSEGV] [ADDR:0xD47A9DA] [PC:0xD47A9DA] [Address not mapped to object] []
...
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xD47A9DA] [PC:0xD47A9DA, qksqbCheckProp()+154] [flags: 0x0, count: 1]
Registers:
----------
%rax: 0x0000000000000000 %rbx: 0x0000000000000005 %rcx: 0x0000000000000017
%rdx: 0x0000000000000000 %rdi: 0x0001000000000001 %rsi: 0x00007fffbfff3948
%rsp: 0x00007fffbfff3630 %rbp: 0x00007fffbfff3710 %r8: 0x00007fffbd8a9d20
%r9: 0x0000000000000000 %r10: 0x00007fffbd8a76f8 %r11: 0x000000000d47a940
%r12: 0x00007fffbdafe990 %r13: 0x00007fffbfff38e0 %r14: 0x0001000000000001
%r15: 0x0000000000000020 %rip: 0x000000000d47a9da %rfl: 0x0000000000010202
Stack info:
----------
ss_sp: 0x00007fffbe000000 ss_size: 0x0000000002000000 ss_flags: 0
Swap entries = 1
path=swap, size=193273524224, free=193273524224, length=377487352
Disass information
qksqbCheckProp()+132 (0xd47a9c4) test $0x800,%r15d
qksqbCheckProp()+139 (0xd47a9cb) jne 0xd47ad95
qksqbCheckProp()+145 (0xd47a9d1) test $0x20,%r15d
qksqbCheckProp()+152 (0xd47a9d8) je 0xd47aa07
> qksqbCheckProp()+154 (0xd47a9da) mov 0xc0(%r14),%r8
qksqbCheckProp()+161 (0xd47a9e1) test %r8,%r8
qksqbCheckProp()+164 (0xd47a9e4) je 0xd47aa07
qksqbCheckProp()+166 (0xd47a9e6) cmpq $0,0x68(%r8)
qksqbCheckProp()+171 (0xd47a9eb) jne 0xd47ae78qksqbCheckProp() checks properties of a query block during the optimization.
Here’s the stack where this function is called:
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
...
sigacthandler()+216 call call_user_handler() 00000000B ? 7FFFBDAAFEF0 ?
7FFFBDAAF100 ? 7FFFBDAAF100 ?
7FFFBDA87830 ? 00764A8B0 ?
qksqbCheckProp()+15 signal sigacthandler() 00000000B ? 7FFFBDAAFEF0 ?
4 7FFFBDAAF100 ?
qksqbApplyToQbcLoc( call qksqbCheckProp() 1000000000001 ?
)+989 7FFFBDAAFEF0 ? 7FFFBDAAF100 ?
000000017 ? 7FFFBD8A9D20 ?
000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 1000000000001 ?
)+1202 ) 7FFFBFFF38E0 ? 7FFFBDAAF100 ?
000000017 ? 7FFFBD8A9D20 ?
000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FFFBD7DFFE0 ? 7FFFBFFF38E0 ?
)+816 ) 7FFFBDAAF100 ? 000000017 ?
7FFFBD8A9D20 ? 000000000 ?
qksqbApplyToQbcLoc( call qksqbApplyToQbcLoc( 7FFFBD7ED768 ? 7FFFBFFF38E0 ?
)+1250 ) 7FFFBDAAF100 ? 000000017 ?
7FFFBD8A9D20 ? 000000000 ?
qksqbApplyToQbc()+5 call qksqbApplyToQbcLoc( 7FFFBD777EE8 ? 7FFFBFFF38E0 ?
6 ) 7FFFBDAAF100 ? 000000017 ?
7FFFBD8A9D20 ? 000000000 ?
kkocfbMonitorTable( call qksqbApplyToQbc() 7FFFBD777EE8 ? 00D47A940 ?
)+331 7FFFBFFF3948 ? 000000005 ?
7FFFBD8A9D20 ? 000000000 ?
kkoFroAddXplAnnotat call kkocfbMonitorTable( 0884772E0 ? 7FFFBD77A5D0 ?
ions()+2593 ) 000000000 ? 000000005 ?
7FFFBD8A9D20 ? 000000000 ?
kkoUpdateFroAnn()+5 call kkoFroAddXplAnnotat 7FFFBD77A5D0 ? 7FFFBD7FA298 ?
978 ions() 7FFFBD7FA9B8 ? 7FFFBD7FA838 ?
7FFFBD8A9D20 ? 000000000 ?
kkoipt()+5515 call kkoUpdateFroAnn() 7FFFBD7FA298 ? 7FFFBD7FE068 ?
000000001 ? 7FFFBD7DBD08 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
kkoqbc()+5816 call kkoipt() 7FFFBD7FA298 ? 7FFFBD7FE068 ?
000000001 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
apakkoqb()+163 call kkoqbc() 7FFFBFFF6290 ? 7FFFBD7FE068 ?
000000001 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
apaqbdDescendents() call apakkoqb() 7FFFBFFF6290 ? 7FFFBD900A00 ?
+540 0884772E0 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
apaqbdDescendents() call apaqbdDescendents() 7FFFBFFF6290 ? 7FFFBD900A00 ?
+985 0884772E0 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
apadrv()+2549 call apaqbdDescendents() 7FFFBFFF6290 ? 7FFFBD777EE8 ?
0884772E0 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
opitca()+2610 call apadrv() 0884772E0 ? 7FFFBD777EE8 ?
0884772E0 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?
kksFullTypeCheck()+ call opitca() 7FFFBD87E0D8 ? 0884772E0 ?
73 7FFFBFFF8130 ? 000000000 ?
7FFFBFFF3E14 ? 7FFFBFFF3E40 ?In conclusion, ORA-07445 in qksqbCheckProp() can be a consequence of inline hint. A final confirmation is an optimizer trace entry showing that the block is materialized instead of inlined. It’s therefore a good practice to name query blocks with the qb_name() hint, to easier identify them in a large trace file.
You can manually embed the common table expression as a workaround, for example:
with
cte1 as (
select /*+ qb_name(qb_cte1) */ n1 from t1 join t2 on t1.n1 = t2.n2
),
cte3 as (
select /*+ qb_name(qb_cte3) */ t4.n4
from t4 join (
select /*+ qb_name(qb_cte2) */ t3.n3
from t3 left join cte1 on cte1.n1 = t3.n3
) cte2 on cte2.n3 = t4.n4
)
select /*+ qb_name(qb_main) */ 1
from cte3 cte3_1 join cte3 cte3_2 on cte3_1.n4 = cte3_2.n4 ;Update September 2, 2021: Oracle 21c
The problem is fixed in 21c.
References
[1] Jonathan Lewis, Inline Hint. October 9, 2020.
One Comment