Oracle mechanics

03.06.2016

Синтаксическая ошибка в запросе, приводящая к бесконечному parsing-у

Filed under: CBO,Oracle — Игорь Усольцев @ 00:16
Tags:

Наблюдали с коллегами запрос, часами не выходящий из фазы Hard Parse:

SQL> @ash_sqlmon2 81vm5p8sr91zr
 
LAST_PLSQL                SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                    COST ASH_ROWS WAIT_PROFILE
------------------------- ------------- --------------- ---- --------------------------------- ---- -------- --------------------------------------------------------------------
Hard Parse                81vm5p8sr91zr               0    0 sql_plan_hash_value = 0                   32514 ON CPU(32514)
Main Query w/o saved plan 81vm5p8sr91zr               0                                                      
SQL Summary                                           0    0 ASH fixed 0 execs from 1 sessions         32514  ash rows were fixed from 01.06.2016 03:26:52 to 01.06.2016 12:29:00

— и так не сформировавшему за 8 часов плана выполнения!

Оказалось, что поведение запроса поменялось после незначительных изменений в тексте запроса (что ожидаемо и неудивительно), запрос «плотно висел» ON CPU, oradebug dump errorstack 3 стабильно указывал:

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+45        call     kgdsdst()            7FFF9031CA68 000000003
                                                   7FFF902FE4A0 ? 7FFF902FE5B8 ?
                                                   7FFF9031C298 ? 000000083 ?
ksedst()+119         call     skdstdst()           7FFF9031CA68 000000001
                                                   000000001 7FFF902FE5B8 ?
                                                   7FFF9031C298 ? 000000083 ?
dbkedDefDump()+1119  call     ksedst()             000000001 000000001 ?
                                                   000000001 ? 7FFF902FE5B8 ?
                                                   7FFF9031C298 ? 000000083 ?
ksedmp()+261         call     dbkedDefDump()       000000003 000000000
                                                   000000001 ? 7FFF902FE5B8 ?
                                                   7FFF9031C298 ? 000000083 ?
ksdxdmp()+1493       call     ksedmp()             000000003 000000000 ?
                                                   000000001 ? 000000000
                                                   7FFF9031C298 ? 000000083 ?
ksdxfdmp()+145       call     ksdxdmp()            00E572608 7FFF9031D828
                                                   000000003 000000000
                                                   7FFF9031C298 ? 000000083 ?
ksdxcb()+918         call     ksdxfdmp()           7FFF9031E0D0 000000011
                                                   000000003 7FFF9031DF90
                                                   7FFF9031E030 000000083 ?
sspuser()+224        call     ksdxcb()             000000001 000000011 ?
                                                   000000003 ? 7FFF9031DF90 ?
                                                   7FFF9031E030 ? 000000083 ?
__sighandler()       call     sspuser()            000000001 ? 000000011 ?
                                                   000000003 ? 7FFF9031DF90 ?
                                                   7FFF9031E030 ? 000000083 ?
qksopFindStrtype()+  signal   __sighandler()       2AAAAEB73C28 ? 000000009 ? -- *
97                                                 000000002 ? 000000000 ?
                                                   000000000 ? 2AAAAEAD4EB0 ?
qksopFindStrtype()+  call     qksopFindStrtype()   2AAAAEB73C28 ? 000000009 ?
109                                                000000002 ? 000000000 ?
                                                   000000000 ? 2AAAAEAD4EB0 ?
expCheckExprEquiv()  call     qksopFindStrtype()   2AAAAEB73C28 ? 000000009 ?
+952                                               000000002 ? 000000000 ?
                                                   000000000 ? 2AAAAEAD4EB0 ?
qecgoc2()+196        call     expCheckExprEquiv()  000000000 ? 000000007 ?
                                                   000000000 00000000C ?
                                                   2AAAAEB76860 2AAAAEAD4EB0 ?
qecgoc2()+417        call     qecgoc2()            2AAAAEACE720 2AAAAEACE518
                                                   2AAAAEC74E98 00000000C ?
...
----- Abridged Call Stack Trace -----
ksedsts()+244<-kjzdssdmp()+321<-kjzduptcctx()+692<-kjzdpcrshnfy()+347<-kstdmp()+352<-dbkedDefDump()+8728<-ksedmp()+261<-ksdxdmp()+1493<-ksdxfdmp()+145<-ksdxcb
()+918<-sspuser()+224<-__sighandler()<-qksopFindStrtype()+97<-qksopFindStrtype()+109<-expCheckExprEquiv()+952
<-qecgoc2()+196<-qecgoc2()+417<-qecgoc()+445<-qecsel()+370<-qecpqbcheck()+130
----- End of Abridged Call Stack Trace -----

на вызов qksopFindStrtype (*), неопознанный Metalink-ом ни прямым поиском, ни поиском по Search by Stack Trace (что сводится к прямому поиску imho), и относящийся к qksop — Query Compilation Service for Operand Processing (rdbms/src/server/sqllang/compsvc/qksop.c)

И при внимательном рассмотрении текста запроса такое поведение оказалось характерно для комплексных запросов (этот около 360-ти строк с использованием аналитических функций, CONNECT BY, SUBQUERY FACTORING, CASE, ANSI JOIN в комбинациях) на этапе синтаксического разбора (Operand Processing?) простой неточности, обычно оканчивающейся:

ORA-00979: not a GROUP BY expression

— для менее сложных запросов, т.е. подвесить/hang пользовательский процесс бд оказалось возможно простым пропуском/ошибкой в написании поля в кляузе GROUP BY — но такие запросы надо уметь писать)

4 комментария »

  1. Так а что за запрос-то был?

    комментарий от Aleksey Panin — 04.06.2016 @ 13:22 | Ответить

    • Алексей, запрос был, собственно, следующего типа:

      with src as
       (select d.*, p.*
          from SOME_COMPLICATED_VIEW d -- view using SUBQUERY FACTORING, CONNECT BY LEVEL, GROUP BY
         cross join (select :p as pct from dual) p
         where ...),
      data as
       (select d.*,
               max(field1) over(partition by agent_id, dt) as field1_max
          from (select ...
                  from (select contract_id || decode(exclusion, ...) as contract_id, -- syntax mismatch
                               ...
                          from src
                         group by contract_id,                                       -- syntax mismatch
                                  ...)
                 group by ...) d)
      select ...,
             SOME_FUNCTION(...) as results
        from data

      но идея поста была не в типе запроса, а типе поведения Oracle: бесконечный hard parse на qksopFindStrtype, как ошибочная (типа bug) реакция на вроде бы несложную синтаксическую ошибку

      комментарий от Игорь Усольцев — 04.06.2016 @ 23:08 | Ответить

  2. Интересно было бы посмотреть, как это выглядит в трейсе 10053.

    комментарий от Александр — 13.06.2016 @ 15:10 | Ответить

    • к сожалению, ничего интересного: в 10053 трейс совсем немного пишется на этапе синтаксического разбора (совсем немного в смысле совсем ничего) — иначе зачем бы я oradebug dump errorstack 3 приводил?)

      комментарий от Игорь Усольцев — 13.06.2016 @ 23:02 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: