Второй раз запрос выполняется намного дольше первого или Cardinality feedback

Недавно столкнулся с проблемой, когда запрос первый раз выполняется за 1 секунду, а второй и последующие разы за 30 секунд. Если добавить пробел или поменять регистр любой буквы в запросе, что бы oracle вновь сделал парсинг - то запрос снова выполняется за 1 секунду. Поиски привели меня к Cardinality feedback. В двух словах, это фича, которая позволяет оптимизатору учится на своих ошибках. То есть при первом выполнении делается предполагаемая оценка, а в процессе выполнения собирается реальная оценка и если они отличаются - правильная оценка сохраняется для последующего использования. В следующий раз, когда запрос выполняется, то он будет оптимизирован снова и на этот раз оптимизатор будет использовать скорректированные оценки вместо своих обычных оценок.
В моем случае это приводило к зависаниям при последующих вызовах запроса. И что бы отключить фичу можно использовать либо параметр сессии:
alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;
либо хинт:
/*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */

 

Raspberry Pi & PIR-датчик & перемычка

Купил себе на новый год "малинку" и сразу решил, что соединю ее с датчиком движения, прикручу ее где-то в коридоре и, в случае, когда кто-то посторонний будет шастать - буду отправлять себе смс, что в квартире есть движение.
Вот такая модель пришла: https://www.raspberrypi.org/products/raspberry-pi-2-model-b/
Датчик движения hc-sr501 и пару проводков мама-мама купил в местном интернет-магазине.
Когда все детальки были собраны, свободное время выделено, я нашел две статьи, по которым планировал научиться только детектить движение:
https://www.raspberrypi.org/learning/parent-detector/worksheet/

http://diyhacking.com/raspberry-pi-gpio-control/
О том, как установить операционку писать не буду, в интернете есть много статей.
Подключил датчик к Raspberry, как описано в первой статье, оттуда же скопировал скрипт. Запускаю... На мониторе пишет, что движение есть, хотя я до запуска, специально, развернул датчик в стену. Ну, думаю, стена мешает - повернул в пустой коридор. Все равно пишет, что есть движение. Подумал, что допустил ошибку в скрипте - проверил, все правильно. В общем пробовал, я и так и сяк - датчик выдает, что движение есть и хоть ты тресни. На самом датчике есть два винтика, которые регулируют чувствительность и время реагирование на движение. Покрутил и один и второй - то же самое "Есть движение". Подумал, что датчик поломанный.
Попробовал во время выполнения скрипта отсоединить провод от GP4 - появилась надпись "Движения нет". Ага, значит датчик исправен. Решил покопаться в интернете: перепробовал и дополнительные параметры в процедурах библиотеки GPIO, и подключение к другим пинам, и другие скрипты - результат тот же.
В итоге на поиски в интернете потратил около 3 часов, а результата нет.
Захожу еще на один форум, где люди кидали ссылки с алиэкспресса/ибея на датчики движения, которые они используют. В основном это были hc-sr501, точно такие как и у меня. Решил узнать сколько стоят датчики в Китае (свой брал в Киеве) - открываю ссылку, смотрю цену, смотрю фотки датчика и вижу, что у моего датчика расположение конденсаторов другое. У меня по одному конденсатору с каждого угла, а на фотке тоже 4 конденсатора, только 2 из них находятся рядом. Решил найти такую же модель, как и у меня. По одной из ссылок на ибей нашел "мой" датчик, но самое главное на картинке было описание некоторых элементов датчика:

  

Видите слева перемычку и 3 пина, и подпись repeatable trigger - как вы уже наверное догадались, у меня перемычка стояла в положении non-repeatable trigger. Поменял и ВСЕ ЗАРАБОТАЛО. Я был счастлив =)

Позже я нашел еще несколько картинок, где положения перемычки значились как "L"(Low) и "H"(High)-position. 

Перекомпил подчиненного типа

У нас есть два типа, один объектный, другой - таблица значений первого типа.
Первый тип:
create or replace type t_test1 as object
(
  ROW_ID          NUMBER,
  G_ID            NUMBER,
  QTY             NUMBER,
  PRICE           NUMBER
)
Второй тип:
create or replace type tb_test1 as table of t_test1

Если мы захотим добавить/убрать поле в первом типе, то при компиле мы получим ошибку:
ORA-02303: cannot drop or replace a type with type or table dependents

Что бы не делать подобных манипуляций:
- удалять второй тип, который ссылается на первый;
- редактировать первый;
- повторно создавать второй;

можно добавить в описание первого типа ключевое слово FORCE:
create or replace type t_test1 FORCE as object
(
  ROW_ID          NUMBER,
  G_ID            NUMBER,
  QTY             NUMBER
)
При перекомпиле второй тип станет инвалидным, поэтому его тоже надо перекомпилить.

FORCE действует только для типов данных, если на тип ссылается таблица, то FORCE не поможет.

Как проверить кто ссылается на пакет

Для этого используем системное представление (user/all/dba)_dependencies:

SELECT * FROM user_dependencies

NAME - имя объекта, для которого проверяем, на какие объекты он ссылается;
REFERENCED_NAME - имя объекта, для которого проверяем, какие объекты на него ссылаются.

Такие зависимости можно проверять не только для пакетов, а и для хранимых процедур/функций, триггеров, синонимов и т.д.

RollBack в цикле и ORA-1002 Fetch out of sequence

В рабочем проекте начала появляться ошибка "ORA-1002 Fetch out of sequence". Продебажил и заметил что ошибка появляется в неявном курсоре на следующей итерации. До самого курсора есть еще куча DML, после какого-то стоит Commit, после какого-то нет.
Смысл курсора такой: идет выгрузка документов в центральную базу с обновлением признака выгрузки. Вся внутренность цикла завернута в exception:
exception
  when others then
    v_st := SQLERRM;
    rollback;
    dbms_output.put_line(v_st);
end;

После первой неудачной итерации вижу как дебагер переходит на for s in (select... и выпадает ошибка ORA-1002 Fetch out of sequence
В итоге пришлось поставить Commit перед циклом и все заработало.

Поискал в интернете и нашел статью 

Провел свои эксперименты. Первоначально создал две таблички tbl_1 и tbl_2 с одним числовым полем id1 и id2 соответственно. В Test Windows в PL/SQL Developer попытался выполнить такой код:

И после первой итерации получаем "ORA-01002 Fetch out of sequence", а в DBMS Output только одну запись: ORA-01476: divisor is equal to zero.
Меняем код - добавляем Commit перед циклом и все отрабатывает без ошибок. На выходе 2 записи:
ORA-01476: divisor is equal to zero
ORA-01476: divisor is equal to zero
Пробуем использовать Savepoint:

На выходе получаем 2 записи:
ORA-01476: divisor is equal to zero => 1
ORA-01476: divisor is equal to zero => 2     
  
Вольный перевод причины возникновения ошибки: Rollback возвращает нас к состоянию до открытия курсора, а значит курсор должен быть закрыт. А когда следующая итерация пробует получить данные с закрытого курсора возникает ошибка.
НО! Я пробовал посмотреть атрибут %ISOPEN, предварительно переделав все на явный курсор и НИЧЕГО. То есть атрибут после Rollback все-равно True. 
Но на будущее будем знать =)



 

Поднять упавший джоб

begin
  dbms_job.broken(job => 904,broken => false);
end;

Передача массива с Delphi в коллекцию PL/SQL

Предыстория: понадобилось мне получать данные с кассового аппарата (КА) и передавать их в Оракл для дальнейшей обработки. Данные в КА хранятся в виде таблицы товаров с указанием цены, количества, к-ва продаж/возвратов и т.д. Получать данные можно только по одной строке с таблицы (такие методы у КА). И вот, что бы не передавать по одной строке с Делфи в Оракл решено было узнать как можно передать сразу все строки. Изначально план был такой: заполняем многомерный массив и передаем его в Оракл, но, скажу сразу, что ни многомерный, ни массив массивов передать не удалось и судя по некоторым форумам, которые я облазил за эти 2 дня, это сделать невозможно. У меня постоянно возникала ошибка несоответствия типов. Если все-таки такая возможность есть - комментируйте. 
В итоге я передавал в функцию, в качестве параметров одномерные массивы. Количество этих массивов соответствовало количеству необходимых мне для обработки колонок в таблице КА. 
Начну, пожалуй, с того, что я создал в 2 глобальных типа в PL/SQL:
create or replace type T_ExportDataFromMINI as object
  (
    ed_NumDoc           NUMBER,   -- Номер дока
    ed_NumRow           NUMBER, -- Номер строки в доке     
    ed_Price            NUMBER(12,2),   --Цена
    ed_QtySale          NUMBER,
    ed_SumSale          NUMBER(12,2),
    ed_QtyRet           NUMBER,
    ed_SumRet           NUMBER(12,2)
  ) 
и второй:
create or replace type t_exportdatafrommini_tree is table of t_exportdatafrommini 
Потом, в пакете, я прописал 7 локальных типов:
type t_mini_numdoc is table of number index by binary_integer; 
type t_mini_numrow is table of number index by binary_integer;
type t_mini_price is table of number(12,2) index by binary_integer;
type t_mini_qtysale is table of number index by binary_integer;
type t_mini_sumsale is table of number(12,2) index by binary_integer;
type t_mini_qtyret is table of number index by binary_integer;
type t_mini_sumret is table of number(12,2) index by binary_integer;   
и написал функцию, которая принимает параметры. Каждый параметр - это коллекция с типом, который я прописал выше. Сама функция:
Далее переходим к Делфи. На форму был добавлен элемент TOracleQuery, прописаны параметры сессии, добавлен SQL текст:
begin
  :res := PKG_TEST.PARSEEXPORTDATAFROMMINI2(:p_numdoc, :p_numrow, :p_price, :p_qtysale, :p_sumsale, :p_qtyret, :p_sumret);
end;
И определены переменные через Variables Editor:

Самое главное, что бы для переменных, с помощью которых мы хотим передавать массивы была установлена галка PL/SQL Table.
Далее оглашаем необходимое количество массивов и инициализируем их:
.....
  arNumdoc, arNumrow, arPrice, arQtysale, arSumsale, arQtyret, arSumret: variant;
.....
  arNumdoc := VarArrayCreate([0, 3999], varVariant);
  arNumRow := VarArrayCreate([0, 3999], varVariant);
  arPrice := VarArrayCreate([0, 3999], varVariant);
  arQtysale := VarArrayCreate([0, 3999], varVariant);
  arSumsale := VarArrayCreate([0, 3999], varVariant);
  arQtyret := VarArrayCreate([0, 3999], varVariant);
  arSumret := VarArrayCreate([0, 3999], varVariant);

Я указал размер массива равным 4000 элементов, так как в КА нет метода, что бы получить количество элементов в памяти. После заполнения массивов данными (метод VarArrayPut) я меняю верхнюю границу массивов с помощью метода VarArrayRedim.
Далее заполняем SQL переменные:
with oqExportData do
    begin
      SetVariable('p_numdoc', arNumdoc);
      SetVariable('p_numrow', arNumrow);
      SetVariable('p_price', arPrice);
      SetVariable('p_qtysale', arQtysale);
      SetVariable('p_sumsale', arSumsale);
      SetVariable('p_qtyret', arQtyret);
      SetVariable('p_sumret', arSumret);
      Execute;
    end
 
И после всего этого обнуляем переменные:
   arNumdoc := Unassigned;
  arNumrow := Unassigned;
  arPrice := Unassigned;
  arQtysale := Unassigned;
  arSumsale := Unassigned;
  arQtyret := Unassigned;
  arSumret := Unassigned;