ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация сортировки
54 сообщений из 54, показаны все 3 страниц
  все  
оптимизация сортировки
    #40062547
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Всем привет.

Есть 2 запроса, которые соединяются между собой UNION ALL. Результирующая выборка из 20 полей должна быть отсортирована по 6 полям. Объем выборки около 100 миллионов. Соответственно, как вы понимаете, занимает это ни один час.
Вопрос - как можно оптимизаровать сортировку в данном кейсе?

UPDATED:
v$sql_workarea_active говорит, что сортировка однопроходная с объемом темпа в 7 Гб.
...
Рейтинг: 0 / 0
14.04.2021, 19:02
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062566
Правильный Вася
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильный Вася
Участник
А для чего нужно сортировать 100 млн записей, да еще и по 6 полям?!
...
Рейтинг: 0 / 0
14.04.2021, 20:02
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062575
Фотография Валерий Юринский
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Валерий Юринский
Участник
Правильный Вася
А для чего нужно сортировать 100 млн записей, да еще и по 6 полям?!
+1
Присоединяюсь к вопросу.
...
Рейтинг: 0 / 0
14.04.2021, 20:28
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062576
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
mlc
как можно оптимизаровать сортировку в данном кейсе?

Какими ресурсами готовы пожертвовать ради оптимизации?
...
Рейтинг: 0 / 0
14.04.2021, 20:29
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062579
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
А я бы, идя навстречу "заказчику", спросил бы, а каждая из половин в отсортированном виде отдаётся быстро?
...
Рейтинг: 0 / 0
14.04.2021, 20:39
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062584
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
mlc, нет таких людей которые способны "глазками" просмотреть 100 млн. rows.

Признайся им ведь нужно всего-лишь top 10 строк?
...
Рейтинг: 0 / 0
14.04.2021, 20:45
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062586
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
mayton
mlc, нет таких людей которые способны "глазками" просмотреть 100 млн. rows.
Признайся им ведь нужно всего-лишь top 10 строк?

Ну сортировка не обязательно делается под "глазки".
Я знаю вариант, в котором приходится сортировать ~100млн, правда, не из UNION ALL и, в конечном итоге, там удалось отделаться порциями помельче, порядка 20 млн.
...
Рейтинг: 0 / 0
14.04.2021, 20:49
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062602
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
Elic
А я бы, идя навстречу "заказчику", спросил бы, а каждая из половин в отсортированном виде отдаётся быстро?

Предложение суперпривлекательное.
Я несколько лет на такое облизываюсь.

Останавливает, главным образом, то, что после меня это поддерживать будет некому.
А с учётом того, что в моём случае, не из "половин", а из "десятин" сила клиентского мержа вызывает сомнение.
Хотя руки продолжают чесаться.
...
Рейтинг: 0 / 0
14.04.2021, 22:53
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062632
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
mlc
Всем привет.

Есть 2 запроса, которые соединяются между собой UNION ALL. Результирующая выборка из 20 полей должна быть отсортирована по 6 полям. Объем выборки около 100 миллионов. Соответственно, как вы понимаете, занимает это ни один час.
Вопрос - как можно оптимизаровать сортировку в данном кейсе?

UPDATED:
v$sql_workarea_active говорит, что сортировка однопроходная с объемом темпа в 7 Гб.
с таким кол-вом информации вероятность получить адекватный ответ равна нулю. Помимо уже заданных уточняющих вопросов, я бы спросил еще версию оракла, текст запроса, план c format=advanced, и отчет rtsm, и разрешено ли параллельное выполнение на сервере?

зы. Сортировка 100млн ни один час - в принципе, подозрительно долго. Видимо, там еще проблем хватает помимо сортировки.
...
Рейтинг: 0 / 0
15.04.2021, 04:02
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062648
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Sayan Malakshinov
Сортировка 100млн ни один час - в принципе, подозрительно долго. Видимо, там еще проблем хватает помимо сортировки.

Да в принципе адекватно для сортировки на временном сегменте, если темп не на SSD.
...
Рейтинг: 0 / 0
15.04.2021, 08:31
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062655
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Постараюсь ответить на все вопросы в одном сообщении.
Версия 12.1. Насколько мне известно все датафайлы продуктовой бд живут на SSD (хотя могу ошибаться).
Насчёт времени работы сортировки - опираюсь на данные из v$session_longops , v$sql_workarea_active и v$sql_plan_monitor.
Сам текст запроса выложить не могу, но там ничего мудреного: select ... from джойн_нескольких_таблиц union all select ... from джойн_нескольких_таблиц order by 1,2,3,4,5,6. Запрос с gather_plan_statistics не выполнялся.
Сортировка 100 млн - это требование внешней системы для формирования ЦБ-шного отчёта. Система получает данные в плоском файле, обрабатывает данные, обогащает их и отправляет в ЦБ. В отчёте транзакции по картам.
Насчёт параллельного выполнения - почему нет. Возможность есть и первая мысль была накрутить параллелей.
Насчёт какими ресурсами готов пожертвовать - а какие есть предложения?
...
Рейтинг: 0 / 0
15.04.2021, 09:07
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062663
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
mlc
Постараюсь ответить на все вопросы в одном сообщении.
Пока не на все.

P.S. Если ты не разработчик и не можешь на них повлиять, то можешь не отвечать.
...
Рейтинг: 0 / 0
15.04.2021, 09:31
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062665
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
mlc
Насчёт какими ресурсами готов пожертвовать - а какие есть предложения?

Предложения есть разные.
Поскольку в топике проявился Саян, то про варианты надвинуть шляпу оптимизатору у него получится лучше.

Я ограничусь вариантами разработчика:
- рассмотреть возможность создания мат. представления с целью заменить сортировку индексным доступом (разменять время при выборке на лишнюю нагрузку в процессе работы и дисковые просторы для хранения).
- вариация на тему: разработать кастомный доменный индекс, который будет готовить искомый пресортированный датасет.
- накидать pipelined, принимающую на вход пресортированные датасеты отдельных веток union all и выполняющую объединение в стиле Sort-Merge Join.
... прочие способы под общим девизом "готовь сортированный набор по мере изменения данных, отдавай одним проходом"
...
Рейтинг: 0 / 0
15.04.2021, 09:35
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062670
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Elic,

Ох, Elic, ваш вопрос пропустил.
Если я правильно вас понял - вы предлагаете отдавать по частям отсортированно. Но как это поможет в сортировке всего набора вместе? Выше я писал, что все складываются в плоский файл в отсортированном виде.

mayton
mlc, нет таких людей которые способны "глазками" просмотреть 100 млн. rows.

Признайся им ведь нужно всего-лишь top 10 строк?
Нет, нужны все 100 миллионов.
...
Рейтинг: 0 / 0
15.04.2021, 09:43
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062673
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
mlc
Если я правильно вас понял - вы предлагаете отдавать по частям отсортированно. Но как это поможет в сортировке всего набора вместе?
Принцип:
andrey_anonymous
- накидать pipelined, принимающую на вход пресортированные датасеты отдельных веток union all и выполняющую объединение в стиле Sort-Merge Join.
...
Рейтинг: 0 / 0
15.04.2021, 09:50
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062674
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
mlc
Elic,
...Но как это поможет в сортировке всего набора вместе? ...

вот andrey_anonymous понял, как - почитайте его последний пост внимательно.
andrey_anonymousв стиле Sort-Merge Join
С учетом того, что фаза sort уже будет выполненной и останется только выполнить merge, то
скажем, при числе веток в пределах 5, я может и не задумывался даже сильно, просто взял бы и так и сделал...
И без всякого сомнения, если сортировка в пределах ветки условно-бесплатна - например обеспечивается подходящим индексом.
...
Рейтинг: 0 / 0
15.04.2021, 09:53
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062683
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
Хм.. Если сортировка так дорого стоит (не один час) то возможо имеет смысл подумать отказаться от сортировок
вообще. И держать отдельную сортированную всегда структуру данных. И обновлять ее по мере поступления
новых данных.
...
Рейтинг: 0 / 0
15.04.2021, 10:21
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062684
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
mayton
Хм.. Если сортировка так дорого стоит (не один час) то возможо имеет смысл подумать отказаться от сортировок
вообще. И держать отдельную сортированную всегда структуру данных. И обновлять ее по мере поступления
новых данных.


это тоже занимательная идея.

Но при особо рьяном и тщательном выполнении легко можно неопределенное количество порядков потерять.
А так как идея похожа на идею архитектурного типа, то почти наверно речь идет не о постройке отдельно стоящего сарая,
а о пристройке такой веранды, для которой нужно разобрать существующий дом. Хорошо, если не целиком.
...
Рейтинг: 0 / 0
15.04.2021, 10:28
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062696
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
Авто пишет

>> Система получает данные в плоском файле, обрабатывает данные, обогащает их и отправляет в ЦБ.

Тоесть у нас полюбому есть фаза загрузки. И ничего с этим не поделать. Задача - конвейер, и рассматривать одну
фазу сортировки - не интересно.
...
Рейтинг: 0 / 0
15.04.2021, 10:58
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062702
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
mayton,

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

То есть, в космическом масштабе, оно может и про то, сколько будет стоить поменять способ интеграции.
Но локально вопрос о том, как умеренно дешево ускорить сортировку, без замены интеграционных рельсов, шпал и костылей.
...
Рейтинг: 0 / 0
15.04.2021, 11:10
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062723
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
booby

...наворочена многоступенчатая "интеграция".

И, может быть, его задача, как раз в том и состоит, чтобы испечь сортированный плоский файл,
который потом будет грузить другая внешняя система.

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

все в точности так и есть.
...
Рейтинг: 0 / 0
15.04.2021, 12:03
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062725
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
andrey_anonymous
mlc
Насчёт какими ресурсами готов пожертвовать - а какие есть предложения?

Предложения есть разные.
Поскольку в топике проявился Саян, то про варианты надвинуть шляпу оптимизатору у него получится лучше.

Я ограничусь вариантами разработчика:
- рассмотреть возможность создания мат. представления с целью заменить сортировку индексным доступом (разменять время при выборке на лишнюю нагрузку в процессе работы и дисковые просторы для хранения).
- вариация на тему: разработать кастомный доменный индекс, который будет готовить искомый пресортированный датасет.
- накидать pipelined, принимающую на вход пресортированные датасеты отдельных веток union all и выполняющую объединение в стиле Sort-Merge Join.
... прочие способы под общим девизом "готовь сортированный набор по мере изменения данных, отдавай одним проходом"

Это уже что-то. Спасибо за наводку.
...
Рейтинг: 0 / 0
15.04.2021, 12:04
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062727
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Вариант с pipelined может сильно облегчить жизнь если выборке требуется частичная сортировка (кластеризация относительно какого-то атрибута). В этом случае можно делать pipelined parallel enabled, указав ключ partitioning.
Это позволяет не только задействовать на сортировке несколько ядер, но и экономить время на темпе (у каждого слейва будет свой sort area + кратно меньший объем для сортировки + возможность культурно обойти предел PGA для процесса)
...
Рейтинг: 0 / 0
15.04.2021, 12:10
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062733
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
andrey_anonymous
Вариант с pipelined может сильно облегчить жизнь если выборке требуется частичная сортировка (кластеризация относительно какого-то атрибута). В этом случае можно делать pipelined parallel enabled, указав ключ partitioning.
Это позволяет не только задействовать на сортировке несколько ядер, но и экономить время на темпе (у каждого слейва будет свой sort area + кратно меньший объем для сортировки + возможность культурно обойти предел PGA для процесса)


выглядит сложно.
Если на коленке для слепить файл - взял горсть сортированных курсоров, в виде массива, например,
и мержи их до полной готовности файла синхронно-последовательно, как честный union all всегда раньше и работал.
Убийство pga происходит при этом под твоим контролем.

Если окажется, что получить сортированную ветку - тоже слишком дорого, то может и описанного вида канальная функция сгодится,
тогда, вероятно, таки со вставкой в таблицу имени интеграции с индексами, поддерживающими нужный файлу порядок.
...
Рейтинг: 0 / 0
15.04.2021, 12:31
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062738
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
а если промежуточная таблица с партициями, штук на 100 партиций
и локальными индексами, которые создавать после перезаливки?
...
Рейтинг: 0 / 0
15.04.2021, 12:43
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062753
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
mlc, а сколько исходный файл занимает? В мегабайтах.
...
Рейтинг: 0 / 0
15.04.2021, 13:47
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062777
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
mayton
mlc, а сколько исходный файл занимает? В мегабайтах.
~8 Gb
...
Рейтинг: 0 / 0
15.04.2021, 14:45
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062801
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
booby
andrey_anonymous
Вариант с pipelined может сильно облегчить жизнь если выборке требуется частичная сортировка (кластеризация относительно какого-то атрибута). В этом случае можно делать pipelined parallel enabled, указав ключ partitioning.
Это позволяет не только задействовать на сортировке несколько ядер, но и экономить время на темпе (у каждого слейва будет свой sort area + кратно меньший объем для сортировки + возможность культурно обойти предел PGA для процесса)


выглядит сложно.

Да не особо.
Это часть декларации pipelined.
...
Рейтинг: 0 / 0
15.04.2021, 16:15
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062812
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
andrey_anonymous
booby
пропущено...


выглядит сложно.

Да не особо.
Это часть декларации pipelined.

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

В заявленном случае умение воспользоваться этим волшебством, содержательно может оказаться отдельным искусством.
Само по себе, в принципе, это не отменяет возможность канальной функции, но помогают ей декларированные чудеса в конкретном
случае, или нет - отсюда не видать.
...
Рейтинг: 0 / 0
15.04.2021, 16:41
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062816
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
mlc
mayton
mlc, а сколько исходный файл занимает? В мегабайтах.
~8 Gb

Хороший размер. Укладывается в memory для типичной рабочей станции.
...
Рейтинг: 0 / 0
15.04.2021, 17:00
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062840
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
booby
В заявленном случае умение воспользоваться этим волшебством, содержательно может оказаться отдельным искусством.

Вообще-то нет.
Если читать доку внимательно и иметь базовое понимание параллельной обработки, то особых проблем нет.
...
Рейтинг: 0 / 0
15.04.2021, 18:24
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062859
Правильный Вася
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильный Вася
Участник
mlc
Система получает данные в плоском файле, обрабатывает данные, обогащает их и отправляет в ЦБ.

Значит ли это, что на входе количество записей совпадает с количеством на выходе?
Если да, может, они уже и так отсортированы? Или хотя бы можно потребовать их предварительной сортировки ДО всего процесса?
...
Рейтинг: 0 / 0
15.04.2021, 19:42
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062971
mlc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mlc
Участник
Правильный Вася,

Вы видимо не поняли о чем я. Этот плоский файл готовится на моей стороне. То есть мне надо отсортировать эти 100кк и затолкать в файл. А принимающая внешняя система обогощяет то, что я там наваял. Если говорить про количество, то они не только обогаюащют мои данные, но и доливают свои по аналогии отсортированные (мои уже не сортируют).
Отвечая на ваш вопрос, значит ли это, что на входе количество записей совпадает с количеством на выходе - нет, не совпадает.
...
Рейтинг: 0 / 0
16.04.2021, 10:37
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40062990
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
mlc,

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

В вашем случае, если проблема точно и именно в сортировке
(например, параллельно с этим, нет проблем с триллионами вызовов функций из вашего сортируемого набора),
то у вас два способа действий, распадающихся на три варианта -
А) - передать проблему товарищу:
А.1) - обратиться к администратору с просьбой об увеличении доступной процессу памяти для сортировки,
чтобы ваша задача не падала на диск в процессе сортировки
А.2) заявить потребителю, что файл вы поставляете без сортировки, сортировать его - забота получателя.

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

Для образования надежной фигуры достаточно трех пальцев.
Но у вас две руки, так что можно манипулировать фигурами, составляемыми сразу на обеих руках.
...
Рейтинг: 0 / 0
16.04.2021, 11:43
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063014
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andrey_anonymous
Да в принципе адекватно для сортировки на временном сегменте, если темп не на SSD.
да, брось, минимум пару часов сортировать 8ГБ? это явно не адекватно
...
Рейтинг: 0 / 0
16.04.2021, 12:42
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063017
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
mlc
Насчёт времени работы сортировки - опираюсь на данные из v$session_longops , v$sql_workarea_active и v$sql_plan_monitor.
и где они? Я и просил отчет RTSM.
mlc
Запрос с gather_plan_statistics не выполнялся.
можно и без статистик выполнения, если есть RTSM, но план с advanced все равно нужен.
mlc
Сам текст запроса выложить не могу
не можешь выложить публично, так отправь хоть на почту. Или поменяй названия таблиц, обфусцируй.
mlc
Возможность есть и первая мысль была накрутить параллелей.
ну и где план с параллелью?
...
Рейтинг: 0 / 0
16.04.2021, 12:47
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063019
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andrey_anonymous
- накидать pipelined, принимающую на вход пресортированные датасеты отдельных веток union all и выполняющую объединение в стиле Sort-Merge Join.
в принципе можно даже форсировать параллельный union all c сортировкой каждого сета отдельно и параллельной сортировкой поверх них (с range distribution)
...
Рейтинг: 0 / 0
16.04.2021, 12:51
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063022
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Sayan Malakshinov
andrey_anonymous
Да в принципе адекватно для сортировки на временном сегменте, если темп не на SSD.
да, брось, минимум пару часов сортировать 8ГБ? это явно не адекватно

100 лямов - это 100 лямов.
У меня был опыт с сортировкой таких наборов, правда, записи, видимо, пошире были, но ~час - время довольно характерное при интенсивном использовании temp.
т.е. примерно 15-20 лямов на ручном управлении памятью сортировались считанные минуты, после 20лямов уже без вариантов падало в темп и время резко подскакивало до 20-30 минут на 40 лямов и порядка часа+ на 100 преимущественно за счет direct path read/write.
Сортировка в идеальных условиях стоит NLogN от количества элементов, объем за счет ширины записи дает лишь линейный коэффициент. А тут еще ввод-вывод внезапно.
...возможно, ты просто привык работать с более серьезным оборудованием, где оперативка терабайтами меряется и которому эти объемы - что семечки.
...
Рейтинг: 0 / 0
16.04.2021, 13:00
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063023
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Sayan Malakshinov
andrey_anonymous
Да в принципе адекватно для сортировки на временном сегменте, если темп не на SSD.
да, брось, минимум пару часов сортировать 8ГБ? это явно не адекватно

100 лямов - это 100 лямов.
У меня был опыт с сортировкой таких наборов, правда, записи, видимо, пошире были, но ~час - время довольно характерное при интенсивном использовании temp.
т.е. примерно 15-20 лямов на ручном управлении памятью сортировались считанные минуты, после 20лямов уже без вариантов падало в темп и время резко подскакивало до 20-30 минут на 40 лямов и порядка часа+ на 100 преимущественно за счет direct path read/write.
Сортировка в идеальных условиях стоит NLogN от количества элементов, объем за счет ширины записи дает лишь линейный коэффициент. А тут еще ввод-вывод внезапно.
...возможно, ты просто привык работать с более серьезным оборудованием, где оперативка терабайтами меряется и которому эти объемы - что семечки.
...
Рейтинг: 0 / 0
16.04.2021, 13:00
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063026
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
При 100 млн rows, и при размере файла 8 Гб, средняя длина строки будет

8 Гб / 100 000 000 = 8,589,934,592 / 100 000 000 = 85 байт на строку.

И при 20 полях средняя длина 1 поля будет 4 байта. Что там внутри?
...
Рейтинг: 0 / 0
16.04.2021, 13:12
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063030
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
Sayan Malakshinov
andrey_anonymous
- накидать pipelined, принимающую на вход пресортированные датасеты отдельных веток union all и выполняющую объединение в стиле Sort-Merge Join.
в принципе можно даже форсировать параллельный union all c сортировкой каждого сета отдельно и параллельной сортировкой поверх них (с range distribution)

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

(На 12.1, например, я, скорее всего, не решился бы на подвиги, по посмотреть было бы всё равно интересно).
...
Рейтинг: 0 / 0
16.04.2021, 13:16
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063034
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
booby
(На 12.1, например, я, скорее всего, не решился бы на подвиги, по посмотреть было бы всё равно интересно).

https://docs.oracle.com/database/121/VLDBG/GUID-1F4C90F9-3EF5-423A-B55B-2593FB3F1433.htm
...
Рейтинг: 0 / 0
16.04.2021, 13:24
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063046
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
пока я даже не уверен, что основное время уходит на сортировку. В условиях такого недостатка информации, знать бы хотя бы сколько выполняется по отдельности, скажем CTAS этого запроса без сортировки и сортировка полученной таблицы
...
Рейтинг: 0 / 0
16.04.2021, 13:45
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063048
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
версия целевой системы топикстартером и не была указана.
точная не указана, но сказано 12.1:
mlc
Версия 12.1.

смею надеяться, что в 2021-то уж все-таки наверное не 12.1.0.1 хотя и у меня есть пара таких клиентов :(
...
Рейтинг: 0 / 0
16.04.2021, 13:47
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063055
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
andrey_anonymous
booby
(На 12.1, например, я, скорее всего, не решился бы на подвиги, по посмотреть было бы всё равно интересно).

https://docs.oracle.com/database/121/VLDBG/GUID-1F4C90F9-3EF5-423A-B55B-2593FB3F1433.htm

enabled не значит, что автоматически выбирается.
Пока мне в 12.1 ни разу нигде не пришлось поставить no_pq_concurrent_union, чтобы предотвратить "новое поведение".

2Sayan Malakshinov
и все-таки, было бы интересно, либо увидеть точную хинтовку, либо какие-то пояснения, почему просто pq_concurrent_union,
сам по себе обеспечит желаемое в данном случае поведение.
...
Рейтинг: 0 / 0
16.04.2021, 14:16
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063058
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
booby

enabled не значит, что автоматически выбирается.

ммм? Саян предложил форсировать план, в котором на вход общей сортировки придут параллельно собранные пресортированные наборы.
Это делается на базе указанной новофичи.
...
Рейтинг: 0 / 0
16.04.2021, 14:26
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063060
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
andrey_anonymous,

я всего лишь хочу увидеть, как именно это делается.
...
Рейтинг: 0 / 0
16.04.2021, 14:33
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063061
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby,

 
элементарно же
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
create table a as select a a1,a a2,a a3,a a4, rpad(a,30,'x') s from (select level a from dual connect by level<=1e5);
create table b as select a b1,a b2,a b3,a b4, rpad(a,30,'x') s from (select level a from dual connect by level<=1e5);

explain plan for
select *
from (select * from a
      union all
      select * from b
     )
order by 5,4,3,2,1;

Plan hash value: 1167466619

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   200K|    21M|       |  5580   (1)| 00:00:01 |
|   1 |  SORT ORDER BY       |      |   200K|    21M|    24M|  5580   (1)| 00:00:01 |
|   2 |   VIEW               |      |   200K|    21M|       |   434   (1)| 00:00:01 |
|   3 |    UNION-ALL         |      |       |       |       |            |          |
|   4 |     TABLE ACCESS FULL| A    |   100K|  4980K|       |   217   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| B    |   100K|  4980K|       |   217   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

alter table a parallel 8;

explain plan for
select *
from (select * from a
      union all
      select * from b
     )
order by 5,4,3,2,1;

Plan hash value: 2954979701

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |   200K|    21M|       |   249   (2)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR           |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)      | :TQ10001 |   200K|    21M|       |   249   (2)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY          |          |   200K|    21M|    24M|   249   (2)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE            |          |   200K|    21M|       |   247   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE        | :TQ10000 |   200K|    21M|       |   247   (1)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       VIEW                |          |   200K|    21M|       |   247   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        UNION-ALL          |          |       |       |       |            |          |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| A        |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |         PX SELECTOR       |          |       |       |       |            |          |  Q1,00 | PCWP |            |
|  11 |          TABLE ACCESS FULL| B        |   100K|  4980K|       |   217   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property

alter table b parallel 8;

explain plan for
select *
from (select * from a
      union all
      select * from b
     )
order by 5,4,3,2,1;

Plan hash value: 1745857249

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |   200K|    21M|       |    62   (4)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR           |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)      | :TQ10001 |   200K|    21M|       |    62   (4)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY          |          |   200K|    21M|    24M|    62   (4)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE            |          |   200K|    21M|       |    60   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE        | :TQ10000 |   200K|    21M|       |    60   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       VIEW                |          |   200K|    21M|       |    60   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        UNION-ALL          |          |       |       |       |            |          |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| A        |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |         PX BLOCK ITERATOR |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |          TABLE ACCESS FULL| B        |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property

...
Рейтинг: 0 / 0
16.04.2021, 14:36
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063065
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
booby
как именно это делается.
как именно включить параллель и как выбираются dop - это уже не в тему будет. Конкретнее надо вопрос
...
Рейтинг: 0 / 0
16.04.2021, 14:46
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063070
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
забыл такой показать:
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
explain plan for
select *
from (select * from 
        (select * from a order by 5,4,3,2,1)
      union all
      select * from 
        (select * from b order by 5,4,3,2,1)
     )
order by 5,4,3,2,1;

Plan hash value: 1069718480

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |   200K|    21M|       |    65   (8)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |          |       |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)           | :TQ10003 |   200K|    21M|       |    65   (8)| 00:00:01 |  Q1,03 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY               |          |   200K|    21M|    24M|    65   (8)| 00:00:01 |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                 |          |   200K|    21M|       |    63   (5)| 00:00:01 |  Q1,03 | PCWP |            |
|   5 |      PX SEND RANGE             | :TQ10002 |   200K|    21M|       |    63   (5)| 00:00:01 |  Q1,02 | P->P | RANGE      |
|   6 |       BUFFER SORT              |          |   200K|    21M|       |            |          |  Q1,02 | PCWP |            |
|   7 |        VIEW                    |          |   200K|    21M|       |    63   (5)| 00:00:01 |  Q1,02 | PCWP |            |
|   8 |         UNION-ALL              |          |       |       |       |            |          |  Q1,02 | PCWP |            |
|   9 |          VIEW                  |          |   100K|    10M|       |    32   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|  10 |           SORT ORDER BY        |          |   100K|  4980K|  6680K|    32   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|  11 |            PX RECEIVE          |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  12 |             PX SEND RANGE      | :TQ10000 |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|  13 |              PX BLOCK ITERATOR |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  14 |               TABLE ACCESS FULL| A        |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|  15 |          VIEW                  |          |   100K|    10M|       |    32   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|  16 |           SORT ORDER BY        |          |   100K|  4980K|  6680K|    32   (7)| 00:00:01 |  Q1,02 | PCWP |            |
|  17 |            PX RECEIVE          |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  18 |             PX SEND RANGE      | :TQ10001 |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,01 | P->P | RANGE      |
|  19 |              PX BLOCK ITERATOR |          |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|  20 |               TABLE ACCESS FULL| B        |   100K|  4980K|       |    30   (0)| 00:00:01 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of table property

...
Рейтинг: 0 / 0
16.04.2021, 14:52
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063071
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
А может у него 1 SATA диск на всю базу?
...
Рейтинг: 0 / 0
16.04.2021, 14:52
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063081
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
Sayan Malakshinov,

ладно, спасибо - я успокоился.
Хорошо, что я необразованный, и не вижу отличий в размере TempSpc на этапе Order By, ни в каком из вариантов.
Поэтому понять этого волшебства всё равно точно не смогу.
...
Рейтинг: 0 / 0
16.04.2021, 15:11
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063125
Правильный Вася
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Правильный Вася
Участник
mlc
Результирующая выборка из 20 полей должна быть отсортирована по 6 полям. Объем выборки около 100 миллионов.

Что из себя представляют эти самые 6 полей?

Как вариант, если первое поле - это дата (без времени) или код подразделения или тип операции или ещё какая достаточно ёмкая категория, которая позволяет всю выборку условно разделить на несколько частей, например, по 5-10 млн, то можно сделать функцию, которая будет получать на вход эту категорию/конкретную дату/тип операции и возвращать в результате набор данных уже только по ней.
При этом сортировка в функции будет нужна уже только по оставшимся 5 полям, а соединять результат из кусков можно в вызывающей функции/клиенте.

Т.е. просто раздробить выборку на части и результат соединить уже в конце без сортировки.
...
Рейтинг: 0 / 0
16.04.2021, 17:35
| Ответить | Цитировать | Написать  
оптимизация сортировки
    #40063132
Фотография mayton
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mayton
Участник
Правильный Вася
mlc
Результирующая выборка из 20 полей должна быть отсортирована по 6 полям. Объем выборки около 100 миллионов.

Что из себя представляют эти самые 6 полей?

Как вариант, если первое поле - это дата (без времени) или код подразделения или тип операции или ещё какая достаточно ёмкая категория, которая позволяет всю выборку условно разделить на несколько частей, например, по 5-10 млн, то можно сделать функцию, которая будет получать на вход эту категорию/конкретную дату/тип операции и возвращать в результате набор данных уже только по ней.
При этом сортировка в функции будет нужна уже только по оставшимся 5 полям, а соединять результат из кусков можно в вызывающей функции/клиенте.

Т.е. просто раздробить выборку на части и результат соединить уже в конце без сортировки.

Тяготеет к сортировки низко-кардинальных коллекций. Если-б не было неключевых полей тогда
сортировка подсчетом фактически решает задачу.
...
Рейтинг: 0 / 0
16.04.2021, 17:49
| Ответить | Цитировать | Написать  
54 сообщений из 54, показаны все 3 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / оптимизация сортировки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1880280]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 63ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 79ms
get tp. blocked users:
 1ms
others: 12ms
total:  194ms
созд. / загр.: 194ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]