Oracle Beer Day

Оговорочки и опечатушки

Вот уже больше десяти лет назад в дружном коллективе фирмы Irbicon, где я тогда работал, в ходе внедрения SAS Banking Intelligence Solution в МДМ-Банке родились вот такие оговорки и опечатки, причем вендор SAS совершенно не важен, ровно то же самое могло произойти и в случае с Oracle, или IBM, или Microsoft:

Обналичивание данных
Когда наши ребята готовили документ, в котором, помимо прочего, речь шла об обезличивании данных и использовании их вне стен банка, в текст усилиями начальника вкралась достойнейшая опечатка. Ее отловили в последний момент перед отправкой документа на согласование со службой безопасности, и как раз незадолго до того отшумела очередная история с кражей конфиденциальных банковских данных где-то за бугром. Хороши бы мы были!!!

На удобрения Заказчику
Еще одна опечатка. Конечно, никто не собирался всерьез отправить многостраничный труд на удобрения!

Объемный огром
Оговорка родилась в процессе создания денормализованной таблицы детальных данных для витрины по депозитам, размер которой физически на тот момент превышал 80 гигабайт.

Обирать счета по маскам
Да, и еще одна восхитительная идея, как содрать денег с Заказчика!

Oracle Beer Day Новости

Старший разработчик Д.Мороз за работой

Analyses and Dashboards Linux Publisher

Минутка с Linux и решение проблемы кириллических символов в OBIEE 12

Вот зря, зря я до сих пор не сходил на курс УЦ РДТЕХ Unix and Linux Essentials, ведь тогда найденное в интернете решение проблемы кириллических шрифтов в отчетах Oracle BI Analyses (в части выгрузки отчетов в формат PDF) и BI Publisher действительно заняло бы минуту. Но middleware-сервер оказался оснащен довольно скудно, поэтому, в отсутствии полезной утилиты Midnight Commander, пришлось вспоминать азы операций с файлами и директориями в командной строке. С помощью коллеги справились. Ведь решение просто предполагало скопировать недостающие шрифты из одной папки в другую (абсолютный путь к инсталляции Oracle Middleware на вашем сервере может быть другим)

[oracle@obiee12]$ cd /u00/app/oracle/Oracle/Middleware/Oracle_Home/bi/common/
[oracle@obiee12 common]$ mkdir fonts
[oracle@obiee12 common]$ cd /u00/app/oracle/Oracle/Middleware/Oracle_Home/oracle_common/internal/fonts/
[oracle@obiee12 fonts]$ ls
128R00.TTF  ADUOKB.ttf   ADUOTCB.ttf   ALBANWTK.ttf  B39R00.TTF
ADUOB.ttf   ADUOK.ttf    ADUOTC.ttf    ALBANWTS.ttf  MICR____.TTF
ADUOJB.ttf  ADUOSCB.ttf  ADUO.ttf      ALBANWTT.ttf  UPCR00.TTF
ADUOJ.ttf   ADUOSC.ttf   ALBANWTJ.ttf  ALBANYWT.ttf
[oracle@obiee12 fonts]$ cp *.ttf /u00/app/oracle/Oracle/Middleware/Oracle_Home/bi/common/fonts

Не обязательно быть крутым админом, чтобы быть программистом, но азы операций в командной строке, будь то Windows Shell или Linux (Unix), обязательно пригодятся! Ну и искусство поиска решений проблем в интернете тоже никто не отменял.

Oracle SQL and PL/SQL

Полезный DUAL

Псевдотаблица DUAL с единственным полем DUMMY и единственной строкой, содержащей «X» в качестве этого самого DUMMY, это просто бесценное изобретение инженеров Oracle, нелогично звучащая, но на самом деле изначально в ней было 2 строки. При помощи инструкции CONNECT BY из единственной строки DUAL получаются самые разные кортежи данных. Аналоги существуют во многих базах данных, ну, скажем так, не обязательно существуют, но могут быть созданы. К примеру, при переносе АПК «Нострадамус» (ныне — «ПрограмБанк.БизнесАнализ») на рельсы СУБД Firebird была искусственно создана таблица DUAL (позже — процедура селектного типа), в которую включили ряд вычисляемых столбцов по аналогии с Oracle, например, SYSDATE (для запросов SELECT SYSDATE FROM DUAL и ряда схожих).

В ходе разработки на Oracle DUAL проявляет себя в совершенно разных ипостасях. Например, запрос

SELECT * FROM DUAL WHERE TO_NUMBER('A') = 1
позволит определить номер (ORA-1722) и название оракловой ошибки invalid number, по аналогии в предикате можно определить ряд других ошибок преобразования типов, арифметики, логических операций.

SELECT NVL(MAX(DUMMY), '-') FROM DUAL WHERE 1 = 0
— запрос, демонстрирующий логику неявного GROUP BY.

SELECT LAST_DAY(DATE '2017-11-20') AS NOVEMBER_LAST_DAY FROM DUAL
— легко отлаживать функции даты-времени, CASE-конструкции и вообще любые системные или самописные функции, а также их вариации, за исключением попадающих под ограничения ANSI SQL — т.е. без использования типично PL/SQL типов вроде BOOLEAN или с OUT-параметрами.

И эти несколько запросов — лишь результат моего труда за два дня. Причем несколько куда более простых или зависимых от конкретной схемы запросов сюда не включил. Пользуйтесь DUAL, и будет Вам счастье!

Oracle SQL and PL/SQL

A problem with Bind Variable Peeking in DSS systems turns out to be a confederate

written by Victor Varlamov, OCP at 07-JUL-2017

There is a Reporting system, where a lot (more than 100) of complex long running queries are launched by Reporting Engine, triggered by different business events. Queries are executed with quite different input parameters (list of customers) and data ranges (daily, weekly, monthly). Because of skewed data in tables, one report can return anywhere from 1 row to 1 million rows depending on the input parameters of the report (different clients have different quantities of rows in fact tables). Every report is implemented as a PL/SQL package with a main function that accepts input parameters, prepares some additional data, and then opens a static cursor with PL/SQL variables as parameters, and finally returns the opened cursor. The CURSOR_SHARING parameter is set to FORCE in the DB server. In such scenarios, SQL can lead to poor performance if the optimizer re-uses unsuitable execution plans that otherwise would not be used if a hard parse was forced using literals. Bind variable peeking can cause a suboptimal plan.

In the book Expert Oracle Practices, Alex Gorbachev relates a story — told to him by Tom Kyte — in which the query plan would change on Monday mornings if it was raining. It’s a true story.

“According to the end-users’ observations, if it was raining heavily Monday morning, then database performance was terrible. Any other day of the week, or on Mondays without rain, there were no problems. Talking to the DBA responsible for the system, Tom found that the performance problems continued until the DBA restarted the database, at which point performance went back to normal. That was the workaround: Rainy Mondays = Reboots.”

It is a real case, and this case was solved without any mysteries and magic, just good knowledge of how to Oracle DB works. I’ll show the solution at the end of the article.

Here is a simple example of how bind variable peeking works:

читать далее »

Oracle Beer Day SQL and PL/SQL

Песня про 99 бутылок пива

Поистине замечательный ресурс для программиста любого ранга и языка — www.99-bottles-of-beer.net, в котором более чем 1500 примеров реализации вывода песни про 99 бутылок пива на самых разных языках программирования и с использованием различных приемов. Есть и варианты на разных диалектах SQL. В свободное от работы время я соорудил и свой скрипт, не совсем верно передав текст песни, но идея, думаю, будет понятна.

  1. SELECT CASE MOD (400 - LEVEL, 4)
  2. WHEN 3
  3. THEN
  4. TRUNC ((400 - LEVEL) / 4) || ' bottles of beer on the wall'
  5. WHEN 2
  6. THEN
  7. TRUNC ((400 - LEVEL) / 4) || ' bottles'
  8. WHEN 1
  9. THEN
  10. 'Drink 1 bottle...'
  11. ELSE
  12. CASE
  13. WHEN LEVEL < 396
  14. THEN
  15. TRUNC ((400 - LEVEL) / 4) - 1 || ' bottles left'
  16. ELSE
  17. 'No more bottles on the wall'
  18. END
  19. END
  20. FROM DUAL CONNECT BY LEVEL <= 396

Результат:

99 bottles of beer on the wall
99 bottles
Drink 1 bottle...
98 bottles left
98 bottles of beer on the wall
98 bottles
Drink 1 bottle...
...
Drink 1 bottle...
1 bottles left
1 bottles of beer on the wall
1 bottles
Drink 1 bottle...
No more bottles on the wall

Oracle

Интернет-журнал ФОРС

Полезная ссылка на полезное чтиво об Оракле — Интернет-журнал ФОРС. Тот случай, когда конкуренты делают общее дело!

SQL and PL/SQL

Отчеты по самодокументированной базе данных

Хорошей практикой в разработке баз данных Oracle являются комментарии к объектам физического и логического слоя (таблицам, представлениям, полям) — такая БД является самодокументированной. Вместе со стандартом именования объектов это значительно облегчает жизнь команды разработчиков, облегчает введение в должность новых сотрудников.

Однако приходится иногда делать отчет с группировкой по тем или иным объектам так, чтобы он хорошо выглядел в технической документации по модулю. Приводим пример запроса, который извлекает из БД документацию по интерфейсным представлениям бизнес-модуля «Форма 302». Использованы аналитические функции LAG (предыдущее значение согласно сортировке) и DENSE_RANK (порядковый номер согласно сортировке).

  1. SELECT CASE
  2. WHEN NVL(LAG(uv1.view_name)
  3. OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
  4. THEN DENSE_RANK()
  5. OVER (ORDER BY uv1.view_name)
  6. ELSE NULL
  7. END AS r_num,
  8. CASE
  9. WHEN NVL(LAG(uv1.view_name)
  10. OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
  11. THEN uv1.view_name
  12. ELSE NULL
  13. END AS first_view_name,
  14. tc1.column_name,
  15. cc1.comments
  16. FROM USER_VIEWS uv1
  17. LEFT JOIN USER_TAB_COLUMNS tc1
  18. ON tc1.table_name = uv1.view_name
  19. LEFT JOIN USER_COL_COMMENTS cc1
  20. ON cc1.table_name = uv1.view_name
  21. AND cc1.column_name = tc1.column_name
  22. WHERE LOWER(uv1.view_name) LIKE '%302%gui%v%' /* используйте свои критерии */
  23. ORDER BY uv1.view_name, tc1.column_name

Часть результатов, включая недокументированные представления, пустая — есть где поработать техническим писателям!

1 CBR_302_GUI_DICT_BS_V BUSINESS_SIZE_CD
BUSINESS_SIZE_NAME
SORT_ID
2 CBR_302_GUI_DICT_OKATO_V OKATO_CD
OKATO_CODE
SORT_ID
3 CBR_302_GUI_DICT_OKVED_V OKVED_CD
OKVED_CODE
SORT_ID
4 CBR_302_GUI_OKATO_V AS_OF_DATE Отчетная дата
FDDR_ID ID ФДП-отчета
OKATO Код Окато
SECT_ID Код раздела
Новости

Мужской квест… год спустя

Квест Диверсант
Спустя год наши коллеги-женщины опять подарили нам участие в квесте. За год из команды ушел Дима Грачев, а пришли новые сотрудники Виталий и Евгений. И если мы с Сергеем знали, с чем столкнемся, то для новичков квест был в диковинку. На самом деле час (58 минут 40 секунд) в двух комнатах с участием актера и холостой стрельбы из боевых пистолетов вышли очень напряженными, и если бы не некоторые подсказки, нас бы точно застрелили нам не удалось бы выбраться из запертых комнат и не пришлось бы сломя голову бежать куда глаза глядят. Было офигенно и с атмосферой Великой Отечественной Войны.

SQL and PL/SQL

Super Merge

На примере одной из витрин отчетности поверх хранилища данных возникла задача по поддержанию актуальности денормализованной таблицы. В рассматриваемом примере есть таблица клиентов и таблица телефонов клиентов. Мы хотим ускорить работу отчетов, дополнив таблицу клиентов актуальными номерами телефонов.

Решение в виде одного оператора Merge предложил Максим Медведев:

  1. MERGE INTO test_clients dest
  2. USING
  3. (WITH f AS
  4. (SELECT c.ROWID AS rid,
  5. NVL(p.client_id, c.id) AS id,
  6. p.phone,
  7. p.phone_type,
  8. CASE
  9. WHEN c.id IS NULL THEN 'I'
  10. WHEN p.client_id IS NULL THEN 'D'
  11. ELSE
  12. CASE ROW_NUMBER()
  13. OVER (PARTITION BY p.client_id
  14. ORDER BY p.phone)
  15. WHEN 1 THEN 'U'
  16. ELSE 'I'
  17. END
  18. END AS r,
  19. ROW_NUMBER()
  20. OVER (PARTITION BY NVL(c.id, p.client_id)
  21. ORDER BY 1) AS rn
  22. FROM test_phones p
  23. FULL OUTER JOIN
  24. test_clients c
  25. ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
  26. WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
  27. OR (p.client_id IS NULL))
  28. SELECT f.*, s.name
  29. FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
  30. ON (dest.ROWID = src.rid AND src.r IN ('U', 'D'))
  31. WHEN MATCHED THEN
  32. UPDATE SET phone = src.phone,
  33. phone_type = src.phone_type
  34. DELETE WHERE src.r = 'D' AND src.rn > 1
  35. WHEN NOT MATCHED THEN
  36. INSERT VALUES (src.id,
  37. src.name,
  38. src.phone,
  39. src.phone_type);

Теперь можно изучить результат, попробовать добавлять, менять и удалять данные из таблицы с номерами телефонов

  1. INSERT INTO test_phones
  2. VALUES (2, '8(496)245-55-86', 'Второй домашний');
  3.  
  4. DELETE FROM test_phones
  5. WHERE client_id = 2 AND ROWNUM <= 1;
Страница 1 из 212