Страницы

понедельник, 21 июля 2014 г.

Bug 15931756 - ORA-4031 Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds)

В одной из промышленных БД версии 11.2.0.3.7 обнаружил большое количество системных SQL, не использующих литералы.
/*
Find queries that are not using bind variables
*/
select s.*, 
      (select sql_text from v$sqlarea where force_matching_signature=sig and rownum=1) sql_text
  from (
       select inst_id, to_char(force_matching_signature) sig,
              count(exact_matching_signature) cnt
         from (
              select inst_id, force_matching_signature, exact_matching_signature
                from gv$sql
               group by inst_id, force_matching_signature, exact_matching_signature
              )
        group by inst_id, force_matching_signature
       having count(exact_matching_signature) > 1
        order by cnt desc
       ) s
 where rownum <= 10
В выводе оказалось множество запросов вида:
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865226
select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = 1864581 and bitand(FLAGS, 128)=0
С кол-вом подобных курсоров в кол-ве 501 и 399 соответственно.
SQL> select sql_text from v$sql where force_matching_signature=15893216616221909352 and rownum<=10;
SQL_TEXT
---------------------------------------------------------------------------------
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865226
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865182
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865129
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864747
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864989
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1865069
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864718
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864786
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864815
select FLAGS from SYS_FBA_TRACKEDTABLES where OBJ# = 1864679
Поиск на MOS по SYS_FBA_TRACKEDTABLES тут же выдает, что имеет место быть баг: Bug 15931756 - ORA-4031 / Queries against SYS_FBA_TRACKEDTABLES not shared (do not use binds) (Doc ID 15931756.8)

Комментариев нет:

Отправить комментарий