Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Оптимизация ORDER BY  [new]
Roobl
Guest
Здравствуйте!

В Оракле замечена "неприятная" особенность. Почти всегда выборка без ORDER BY работает в несколько раз быстрее чем с этим классом. Для примера:

SELECT id, name FROM table WHERE ...

3 секунды. Результат порядка 19'000 записей.

SELECT id, name FROM table WHERE ... ORDER BY id

29 секунд не зависимо от того, сортируется по id (NUMBER) или по name (VARCHAR). "Тестовая" таблица имеет порядка 400'000 записей.

Пожалуйста, направьте на путь истинный, если он существует, тоесть, научите, как оптимизировать выборки с сортировкой. Пробовал создавать индексы, но они в основном влияют на класс WHERE. Или я много хочу при таком количестве записей?

Спасибо.
6 мар 06, 19:35    [2423111]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116217
Roobl
Здравствуйте!

В Оракле замечена "неприятная" особенность. Почти всегда выборка без ORDER BY работает в несколько раз быстрее чем с этим классом.


Чудеса да и только :-) :-)
Это не только у Oracle такая проблема. Назовите фамилии Ваших коллег по работе. А теперь назовите фамилии Ваших коллег по работе в алфавитном порядке.
И причем тут "класс" ?
6 мар 06, 19:44    [2423146]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Anton Demidov
Member

Откуда: Atlanta, GA
Сообщений: 1187
Судя по тому, что результат 19 тыс записей, сортировка может происходить на диске. Есть такой параметр у БД sort_area_size - возможно, что его увеличение даст некий выигрыш.

--
Антон
Per rectum ad astrum
6 мар 06, 19:50    [2423170]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Relic Hunter
Member

Откуда: AB
Сообщений: 7431
SELECT id, name FROM table WHERE ... ORDER BY id
Если квири переделать так
SELECT id, name FROM table WHERE ... ORDER BY id, name
и создать индекс по id + name,то можна добиться INDEX RANGE SCAN вместо Full Table Scan.
6 мар 06, 20:02    [2423205]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Anton Demidov
Member

Откуда: Atlanta, GA
Сообщений: 1187
Relic Hunter
SELECT id, name FROM table WHERE ... ORDER BY id, name
и создать индекс по id + name,то можна добиться INDEX RANGE SCAN вместо Full Table Scan.

это если в разделе WHERE нет других неиндексированных полей
6 мар 06, 20:29    [2423273]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Roobl
Guest
Спасибо огромное за ответы. Завтра прийду на работу попробую. Хотя остаётся странным, что сортировка по числам работает "на той же скорости", что и по строкам.
6 мар 06, 22:01    [2423437]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Anton Demidov
Member

Откуда: Atlanta, GA
Сообщений: 1187
Roobl
Спасибо огромное за ответы. Завтра прийду на работу попробую. Хотя остаётся странным, что сортировка по числам работает "на той же скорости", что и по строкам.

размер памяти для сортировки требуется другой - числа наверняка меньше места занимают.
6 мар 06, 22:56    [2423545]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Anton Demidov
Member

Откуда: Atlanta, GA
Сообщений: 1187
Anton Demidov
Roobl
Спасибо огромное за ответы. Завтра прийду на работу попробую. Хотя остаётся странным, что сортировка по числам работает "на той же скорости", что и по строкам.

размер памяти для сортировки требуется другой - числа наверняка меньше места занимают.

тормознул, блин. Чушь написал.
Имеем 19,000 строк на сортировку. При средней длине строки в 50 байт мы ну никак не влезаем в дефолтные 64К sort_area_size. А скидывание в ТЕМР примерно одинаково медленно для обоих типов колонок.
6 мар 06, 23:18    [2423604]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
222
Member [заблокирован]

Откуда: из форума
Сообщений: 1251
Ох и вопрос...

Кстати, на форуме тоже есть своя "неприятная особенность". - обычно вопросы такого рода всегда задают под "гостем"..
7 мар 06, 09:56    [2424137]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Roobl
Guest
автор
Кстати, на форуме тоже есть своя "неприятная особенность". - обычно вопросы такого рода всегда задают под "гостем"..


А в чем собственно проблема? :)
Не люблю я регистрится лишний раз...

PS:
автор
...вопросы такого рода...


Вот с этого места поподробнее пожалуйста. Особенно интересует, к чему это меня лично может привести...

Фух... Флуд какой-то получается... Сорри, админы... Дальше буду по теме...
7 мар 06, 11:23    [2424603]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Roobl
Guest
автор
dmidek

Чудеса да и только :-) :-)
Это не только у Oracle такая проблема. Назовите фамилии Ваших коллег по работе. А теперь назовите фамилии Ваших коллег по работе в алфавитном порядке.
И причем тут "класс" ?


Я конечно дико извиняюсь, но зачем в таком случае умные люди придумывают оптимизированные алгоритмы сортировки (не обязательно связано с базами, чистое программирование и алгоритмизация)?

автор
Anton Demidov

Судя по тому, что результат 19 тыс записей, сортировка может происходить на диске. Есть такой параметр у БД sort_area_size - возможно, что его увеличение даст некий выигрыш.


Хм... Покурил доку... Да, Оракл имею 9.2.0.1.0, W2K. В доке говорят:

автор
Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility.


Посмотрел в параметрах инстанса базы:

PGA - 24 Мб
SORT_AREA_SIZE - 500К+

Вроде нормально всё... Подумал - менять пока ничего не буду - слабоват ещё в понимании некоторых вещей... Да и выборка хитрая.

SELECT id, name FROM table WHERE ... ORDER BY id

Умные люди заметили, WHERE не приведён. Для общего понимания моей задачи, в WHERE может указываться некий тип, например

SELECT id, name FROM table WHERE type=1 ORDER BY id

"шУТка" в том, что при его значении равным 2 в конечной выборке присутствует около 180"000 тысяч записей... 1 и 2 - фейковые значения, как и приведённый запрос, но суть они передают... Тут я думаю PGA и SORT_AREA_SIZE смогут помочь только в том случае, если физической памяти на машине будет ОЧЕНЬ много...

Вот...

Будем мучить дальше... Мож че прийдёт в голову... Мож структуре таблиц голову скручу... Должно же быть в конце концов что-то гениально простое...
Спасибо...
7 мар 06, 12:18    [2424944]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Колобок
Member

Откуда:
Сообщений: 122
Relic Hunter
создать индекс по id + name,то можна добиться INDEX RANGE SCAN вместо Full Table Scan.


разве что индекс по id + nlssort(name)
7 мар 06, 13:32    [2425420]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
Anton Demidov
Member

Откуда: Atlanta, GA
Сообщений: 1187
ну зачем же так сложно.
Дя приведенного примера нужно создать индекс на (type, id)
То есть на первом месте все поля из класса WHERE и в конце из ORDER BY.
Тогда сортировки не будет (будет INDEX RANGE SCAN и TABLE ACCESS BY INDEX ROWID).
А если добавить в конец индекса поле name, то и обращения к таблице можно избежать - останется только INDEX RANGE SCAN.

--
Антон
Per rectum ad astrum
7 мар 06, 21:41    [2427098]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Оптимизация ORDER BY  [new]
Paranoiac
Member

Откуда: Saint Petersburg
Сообщений: 389
Добрый день, а почему возможно такое:
SQL ID: 1cha0xk6rwfmy Plan Hash: 3494369951

SELECT NVL(B1.C_START_SUM,A1.C_SALDO) START_SUM, NVL(B1.C_START_SUM_NAT,
  A1.C_SALDO_NT) START_SUM_NAT
FROM
 Z#RECORDS B1, Z#AC_FIN A1 WHERE A1.ID = :B2 AND A1.C_ARC_MOVE =
  B1.COLLECTION_ID(+) AND B1.C_DATE(+) >= :B1 ORDER BY B1.C_DATE,B1.C_STAMP,
  B1.ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      434      0.00       0.01          0          0          0           0
Execute 7619323   1239.01    1256.28          0          0          0           0
Fetch   7619323    359.69     375.54       1079   61587525          0     7619323
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   15239080   1598.71    1631.83       1079   61587525          0     7619323

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28     (recursive depth: 2)
Number of plan statistics captured: 434

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT ORDER BY (cr=9 pr=2 pw=0 time=17026 us cost=3 size=130 card=2)
         1          2         38   NESTED LOOPS OUTER (cr=9 pr=2 pw=0 time=16990 us cost=2 size=130 card=2)
         1          1          1    TABLE ACCESS BY INDEX ROWID Z#AC_FIN (cr=4 pr=0 pw=0 time=21 us cost=1 size=22 card=1)
         1          1          1     INDEX UNIQUE SCAN PK_Z#AC_FIN_ID (cr=3 pr=0 pw=0 time=12 us cost=1 size=0 card=1)(object id 11794)
         0          1         38    TABLE ACCESS BY INDEX ROWID Z#RECORDS (cr=5 pr=2 pw=0 time=16957 us cost=1 size=86 card=2)
         0          1         38     INDEX RANGE SCAN IDX_Z#RECORDS_COL_DATE_STAMP (cr=4 pr=1 pw=0 time=11053 us cost=1 size=0 card=2)(object id 517645)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                      1079        0.49         10.84
  cursor: pin S                                  57        0.01          0.10
  latch: cache buffers chains                     8        0.00          0.00
  latch free                                      1        0.00          0.00
********************************************************************************

хотя
SQL> select column_name, column_position from dba_ind_columns where index_name='IDX_Z#RECORDS_COL_DATE_STAMP';
 
COLUMN_NAME                                                                      COLUMN_POSITION
-------------------------------------------------------------------------------- ---------------
COLLECTION_ID                                                                                  1
C_DATE                                                                                         2
C_STAMP                                                                                        3
ID                                                                                             4

чего это он делает сортировку,да еще так долго((
24 янв 14, 15:26    [15463442]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
воткактотак
Guest
Paranoiac,

1. Почему не должно быть сортировки?
2. Где вы видите, что долго?
24 янв 14, 15:38    [15463521]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
oracle1
Guest
Параноик, это статистика агрегированая, как из неё можно сделать вывод что запрос выполняеться долго, да и ещё виновата сортировка?
24 янв 14, 15:51    [15463615]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
В каком месте долго? 36 микросекунд (17026 - 16990 )? У тебя запрос выполнился 7619323 раз. БОльшая часть времени скорее всего вообще на запись трэйса ушла. Так что не вижу откуда претензии к запросу.
24 янв 14, 15:52    [15463624]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация ORDER BY  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Если учесть что запрос вообще всегда возвращал одну строку то о сортировке речь вообще не идет.
24 янв 14, 16:00    [15463675]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить