Улучшение времени DELETE и INSERT для большой таблицы, имеющей структуру индекса

Question

Наше приложение управляет таблицей, содержащей набор строк для каждого пользователя, который является результатом интенсивного вычисления. Сохранение этого результата в таблице кажется хорошим способом ускорения дальнейших вычислений.

Структура этой таблицы в основном следующая:

CREATE TABLE per_user_result_set
           ( user_login         VARCHAR2(N)
           , result_set_item_id VARCHAR2(M)
           , CONSTRAINT result_set_pk PRIMARY KEY(user_login, result_set_item_id)
           )
           ;

Типичный пользователь нашего приложения будет рассчитывать этот набор результатов 30 раз в день, причем набор результатов будет содержать от 1 до 500000 элементов. Типичный клиент объявит около 500 пользователей в производственную базу данных. Таким образом, эта таблица обычно состоит из 5 миллионов строк.

Типичный запрос, который мы используем для обновления этой таблицы:

BEGIN
    DELETE FROM per_user_result_set WHERE user_login = :x;
    INSERT INTO per_user_result_set(...) SELECT :x, ... FROM ...;
END;
/

После того, как мы столкнулись с проблемами производительности (часть DELETE заняла бы много времени), мы решили использовать GLOBAL TEMPORARY TABLE (при фиксации удаления строк) для хранения "дельты" строк для подавления из таблицы и строк для вставки в нее:

BEGIN
    INSERT INTO _tmp
    SELECT ... FROM ...
     MINUS SELECT result_set_item_id
             FROM per_user_result_set
            WHERE user_login = :x;

    DELETE FROM per_user_result_set
          WHERE user_login = :x
            AND result_set_item_id NOT IN (SELECT result_set_item_id
                                             FROM _tmp
                                          );
    INSERT INTO per_user_result_set
    SELECT :x, result_set_item_id
      FROM _tmp;

    COMMIT;
END;
/

Это немного улучшило производительность, но все же это неудовлетворительно. Итак, мы ищем способы ускорить этот процесс, и вот проблемы, с которыми мы сталкиваемся:

• Мы бы хотели использовать разбиение таблиц (разбиение user_login). Но разбиение не всегда доступно (в наших тестовых базах мы нажимаем ORA-00439). Наши клиенты не могут позволить себе Oracle Enterprise Edition с платными дополнительными функциями.
• Мы могли бы сделать per_user_result_set таблица GLOBAL TEMPORARY, так что она изолирована и мы можем TRUNCATE это например... но наше приложение иногда теряет соединение с Oracle из-за проблем с сетью и автоматически переподключается. К этому времени мы теряем содержание наших вычислений.
• Мы могли бы разбить эту таблицу на определенное количество сегментов, создать представление, что UNION объединит ВСЕ все эти сегменты, и запустить INSTEAD OF UPDATE и DELETE в этом представлении, а также перезапустить строки в соответствии с ORA_HASH(user_login) % num_buckets, Но мы боимся, что это может сделать SELECT операции намного медленнее. Это привело бы к постоянному количеству таблиц с меньшими индексами, затронутыми в операциях DELETE или INSERT. Короче говоря, "таблица деления на бедных".
• Мы пытались ALTER TABLE per_user_result_set NOLOGGING, Это не сильно улучшает ситуацию.
• Мы пытались CREATE TABLE ... ORGANIZATION INDEX COMPRESS 1, Это ускоряет процесс в соотношении 1:5.
• Мы пытались иметь одну таблицу на user_login. Это именно то, что мы могли бы получить, разделив, используя количество разделов, равное количеству различных user_logins и хорошо выбранную хеш-функцию. Коэффициент производительности 1:10. Но мне бы очень хотелось избежать этого решения: приходится поддерживать огромное количество индексов, таблиц, представлений для каждого пользователя. Это было бы интересным приростом производительности для пользователей, но не для нас, сопровождающих системы.
• Поскольку пользователи работают одновременно, мы не можем создать новую таблицу и заменить ее старой.

Что вы могли бы предложить в дополнение к этим подходам?

Заметка. Наши клиенты работают с базами данных Oracle с 9i до 11g, а с XE выпускают до версии Enterprise. Это широкий спектр версий, с которыми мы должны быть совместимы.

Благодарю.

Answer 1

Если бы все ваши пользователи были в 11g Enterprise Edition, я бы порекомендовал вам использовать встроенное кэширование набора результатов Oracle, а не пытаться свернуть свое собственное. Но это не так, так что давайте двигаться дальше.

Другим привлекательным вариантом может быть использование коллекций PL/SQL, а не таблиц. Находясь в памяти, они быстрее извлекаются и требуют меньше обслуживания. Они также поддерживаются во всех нужных вам версиях. Тем не менее, они являются переменными сеанса, поэтому, если у вас много пользователей с большими наборами результатов, которые могут повлиять на ваши распределения PGA. Также их данные будут потеряны, когда сетевое соединение обрывается. Так что это, вероятно, не то решение, которое вы ищете.

Суть вашей проблемы заключается в следующем утверждении:

DELETE FROM per_user_result_set WHERE user_login = :x;

Само по себе это не проблема, но у вас есть большие различия в распределении данных. Грубо говоря, удаление одной строки будет иметь совсем другой профиль производительности, чем удаление полумиллиона строк. А поскольку ваши пользователи постоянно обновляют свои данные, вы никак не можете справиться с этим, кроме как путем предоставления своим пользователям собственных таблиц.

Вы говорите, что не хотите иметь таблицу на пользователя, потому что

"[это] было бы интересным повышением производительности для пользователей, но не для нас, сопровождающих системы",

Системы существуют на благо наших пользователей. Удобство для нас - это здорово, если оно помогает нам предоставлять им лучший сервис. Но их потребность в хорошем опыте работы превосходит нашу: они оплачивают счета.

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

Я предлагаю вам придерживаться таблиц, организованных по индексу. Вам нужны только столбцы, которые находятся в первичном ключе, и поддержание отдельного индекса не требует дополнительных затрат (как для вставки, так и для удаления). Большим преимуществом наличия таблицы на пользователя является то, что вы можете использовать TRUNCATE TABLE в процессе обновления, который намного быстрее, чем удаление.

Итак, ваша процедура обновления будет выглядеть так:

BEGIN
    TRUNCATE TABLE per_user_result_set REUSE STORAGE;
    INSERT INTO per_user_result_set(...) 
          SELECT ...  FROM  ...;
    DBMS_STATS.GATHER_TABLE_STATS(user
          , 'PER_USER_RESULT_SET'
          , estimate_percent=>10);
    COMMIT;
END;
/

Обратите внимание, что вам больше не нужно включать столбец USER, поэтому в таблице yur будет только один столбец result_set_item_id (еще одно указание на пригодность IOT.

Сбор статистических данных таблицы не является обязательным, но это целесообразно. У вас есть широкий разброс в размере наборов результатов, и вы не хотите использовать план выполнения, разработанный для 500000 строк, когда таблица имеет только одну строку, или наоборот.

Единственным дополнительным расходом является необходимость создания таблицы в схеме пользователя. Но, вероятно, у вас уже есть некоторые настройки для нового пользователя - создание учетной записи, предоставление привилегий и т. Д. - так что это не должно быть большим испытанием.

Answer 2

Мы пытались иметь одну таблицу на user_login. Это именно то, что мы могли бы получить, разделив, используя количество разделов, равное количеству различных user_logins и хорошо выбранную хеш-функцию. Коэффициент производительности 1:10. Но мне бы очень хотелось избежать этого решения: приходится поддерживать огромное количество индексов, таблиц, представлений для каждого пользователя. Это было бы интересным приростом производительности для пользователей, но не для нас, сопровождающих системы.

Можете ли вы сделать хранимую процедуру для генерации этих таблиц для каждого пользователя? Или, что еще лучше, эта хранимая процедура делает наиболее подходящую вещь в зависимости от поддерживаемого лицензирования Oracle?

If Partitioning option 
  then create or truncate user-specific list partition
Else 
  drop user-specific result table
  Create user-specific result table 
      as Select from template result table
  create indexes
  create constraints
  perform grants
end if
Perform insert