ORA-07445 in qksqbCheckProp() with Inline Hint

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 block

Consequently, 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 0xd47ae78

qksqbCheckProp() 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.

Thanks for sharing

Nenad Noveljic

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.