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;
Новости

Разработчики РДТЕХ успешно решают не только SQL-задачки!

Квест Чокнутый профессор

Команда разработчиков РДТЕХ (Максим Медведев, Тагир Билалов, Дмитрий Грачев и Сергей Шеболденков), получив в подарок от женской половины коллектива ко дню защитника отечества возможность выбраться из запертой комнаты в ходе квеста «Чокнутый профессор», успешно преодолела данный квест и, ничего не сломав и воспользовавшись парой подсказок от ведущей, выбралась из полной загадок комнаты за 58 минут!

Пруфлинк

SQL and PL/SQL

Повторы

Задача: есть таблица с одним полем из целых положительных чисел. Нужно запросом вывести все числа столько раз, чему равно само число.

N
1
3
2

Должно получиться так:

N
1
3
3
3
2
2
SELECT q.n
  FROM (    SELECT DISTINCT t.ROWID rn, 
                            t.n, 
                            LEVEL l
              FROM test_numbers t
           CONNECT BY LEVEL <= t.n) q
 ORDER BY q.rn

Интересный вариант с рекурсивным запросом предложил Дмитрий Грачев. Обратите внимание, что такой пример сработает только начиная с 11-ой версии Oracle и результат, вообще говоря, будет отличаться от того, что выдаст первый пример:

WITH f(a, b) 
  AS (SELECT n a, n b
        FROM test_numbers
       WHERE n > 0
       UNION ALL
      SELECT a - 1, b
        FROM f
       WHERE a > 1)
SELECT b
  FROM f
 ORDER BY b
SQL and PL/SQL

Наращивание пустых строк непустыми записями старше по дате

Вопрос родился в качестве теста на испытательном сроке по одной из реальных задач отчетности. Есть таблица с пропусками в атрибутах, для целей примера считаем, что записи упорядочены по дате создания или по иной дате. В данном примере записи упорядочиваются по ROWID, что не мешает заменить нужным критерием.

Col1 Col2
1
1 10
1
1 15
2
2 20

Нужно SELECT-запросом во втором столбце пустоты заполнить нижеследующими непустыми значениями вот так:

Col1 Col2
1 10
1 10
1 15
1 15
2 20
2 20
SELECT col1,
       NVL(col2, 
           LEAD(col2) OVER (PARTITION BY col1 
                            ORDER BY ROWID)) col2
  FROM t1

Это, пожалуй, один из самых простых примеров, когда использование аналитических функций значительно облегчает разработку запроса. В Учебном Центре РДТЕХ читается двухдневный, очень интенсивный курс по аналитическим функциям в применении к хранилищам данных: Oracle Database 12c: Analytic SQL for Data Warehousing.

SQL and PL/SQL

Разбиение суммы на монетки

Сумму в S рублей нужно разбить монетами по 1, 2 или 5 рублей.

WITH t
     AS (    SELECT LEVEL n
               FROM DUAL
         CONNECT BY LEVEL <= :S)
SELECT t1.n || ' по 1 рублю, ' ||
       t2.n || ' по 2 рубля, ' ||
       t3.n || ' по 5 рублей'
  FROM t t1, t t2, t t3
 WHERE 1 * t1.n + 2 * t2.n + 5 * t3.n = :S
 ORDER BY t1.n DESC, t2.n DESC, t3.n DESC
Страница 1 из 212