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. В свободное от работы время я соорудил и свой скрипт, не совсем верно передав текст песни, но идея, думаю, будет понятна.

SELECT CASE MOD (400 - LEVEL, 4)
          WHEN 3
          THEN
             TRUNC ((400 - LEVEL) / 4) || ' bottles of beer on the wall'
          WHEN 2
          THEN
             TRUNC ((400 - LEVEL) / 4) || ' bottles'
          WHEN 1
          THEN
             'Drink 1 bottle...'
          ELSE
             CASE
                WHEN LEVEL > 396
                THEN
                   TRUNC ((400 - LEVEL) / 4) - 1 || ' bottles left'
                ELSE
                   'No more bottles on the wall'
             END
       END
  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 (порядковый номер согласно сортировке).

SELECT CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN DENSE_RANK() 
                   OVER (ORDER BY uv1.view_name)
          ELSE NULL
       END AS r_num,
       CASE
          WHEN NVL(LAG(uv1.view_name) 
                   OVER (ORDER BY uv1.view_name, tc1.column_name), '#') <> uv1.view_name
          THEN uv1.view_name
          ELSE NULL
       END AS first_view_name,
       tc1.column_name,
       cc1.comments
  FROM USER_VIEWS uv1
       LEFT JOIN USER_TAB_COLUMNS tc1 
         ON tc1.table_name = uv1.view_name
       LEFT JOIN USER_COL_COMMENTS cc1 
         ON cc1.table_name = uv1.view_name
        AND cc1.column_name = tc1.column_name
 WHERE LOWER(uv1.view_name) LIKE '%302%gui%v%' /* используйте свои критерии */
 ORDER BY uv1.view_name, tc1.column_name

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

1CBR_302_GUI_DICT_BS_VBUSINESS_SIZE_CD 
  BUSINESS_SIZE_NAME 
  SORT_ID 
2CBR_302_GUI_DICT_OKATO_VOKATO_CD 
  OKATO_CODE 
  SORT_ID 
3CBR_302_GUI_DICT_OKVED_VOKVED_CD 
  OKVED_CODE 
  SORT_ID 
4CBR_302_GUI_OKATO_VAS_OF_DATEОтчетная дата
  FDDR_IDID ФДП-отчета
  OKATOКод Окато
  SECT_IDКод раздела
Новости

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

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

SQL and PL/SQL

Super Merge

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

CREATE TABLE test_clients
(
   id         NUMBER,
   name       VARCHAR2(30),
   phone      VARCHAR2(30),
   phone_type VARCHAR2(20)
);

INSERT INTO test_clients
VALUES (1, 'Вася', NULL, NULL);

INSERT INTO test_clients
VALUES (2, 'Петя', NULL, NULL);

CREATE TABLE test_phones
(
   client_id   NUMBER,
   phone       VARCHAR2 (30),
   phone_type  VARCHAR2 (20)
);

INSERT INTO test_phones
VALUES (1, '8(905)133-30-10', 'Мобильный');

INSERT INTO test_phones
VALUES (2, '8(905)247-89-17', 'Мобильный');

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

MERGE INTO test_clients dest
  USING 
    (WITH f AS 
      (SELECT c.rowid AS rid,
              NVL(p.client_id, c.id) AS id,
              p.phone,
              p.phone_type,
              CASE
                WHEN c.id IS NULL THEN 'I'
                WHEN p.client_id IS NULL THEN 'D'
              ELSE
                CASE ROW_NUMBER() 
                     OVER (PARTITION BY p.client_id 
                     ORDER BY p.phone)
                WHEN 1 THEN 'U' 
                ELSE 'I'
                END
              END AS r,
              ROW_NUMBER() 
              OVER (PARTITION BY NVL(c.id, p.client_id)
                    ORDER BY 1) AS rn
         FROM test_phones p
              FULL OUTER JOIN
              test_clients c
              ON c.id = p.client_id AND NVL(c.phone, p.phone) = p.phone
        WHERE (p.client_id IS NOT NULL AND NVL(c.phone_type, '-') != p.phone_type)
           OR (p.client_id IS NULL))
     SELECT f.*, s.name
       FROM f JOIN (SELECT DISTINCT id, name FROM test_clients) s ON s.ID = f.ID) src
    ON (dest.rowid = src.rid AND src.r IN ('U', 'D'))
WHEN MATCHED THEN
   UPDATE SET phone = src.phone,
              phone_type = src.phone_type
   DELETE WHERE src.r = 'D' AND src.rn > 1
WHEN NOT MATCHED THEN
   INSERT VALUES (src.id,
                  src.name,
                  src.phone,
                  src.phone_type);

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

INSERT INTO test_phones 
VALUES (2, '8(496)245-55-86', 'Второй домашний');

DELETE FROM test_phones
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