Вопрос к спецам по Oracle

RGoo · Post by **RGoo** » 23 Mar 2004 15:51

Проблема у меня странная : из одной большой таблицы <source> (от 0.5 до 1 миллиона записей, но много полей, в том числе и больших текстовых, индексы почти отстутствуют) надо перекачать примерно 70% записей в другую таблицу <target>, да не просто перекачать, а всякие вычисления-групппирования <calc> по пути, да еще в связке с третьми таблицами <other>.
Написаный в лоб INSERT INTO <target> SELECT <calc> FROM <source>, <other> WHERE <where> завис на почти сутки, что совсем не приемлимо. Проблема в том, что изменять <source> нельзя - ни индексов, ни пре-вычислений. Пробовал создать промежуточную таблицу, влить туда только то, что мне нужно из <source>, а затем вычислять\группировать - особого эффекта не дает.
Какие еще есть варианты ? SNAPSHOT aka MATERIALIZED VIEW ? Они вроде быстрее, но время на их создание тоже уходит.....

Спасибо за советы

Mark · Post by **Mark** » 23 Mar 2004 20:02

RGoo wrote:Проблема у меня странная : из одной большой таблицы <source> (от 0.5 до 1 миллиона записей, но много полей, в том числе и больших текстовых, индексы почти отстутствуют) надо перекачать примерно 70% записей в другую таблицу <target>, да не просто перекачать, а всякие вычисления-групппирования <calc> по пути, да еще в связке с третьми таблицами <other>.
Написаный в лоб INSERT INTO <target> SELECT <calc> FROM <source>, <other> WHERE <where> завис на почти сутки, что совсем не приемлимо. Проблема в том, что изменять <source> нельзя - ни индексов, ни пре-вычислений. Пробовал создать промежуточную таблицу, влить туда только то, что мне нужно из <source>, а затем вычислять\группировать - особого эффекта не дает.
Какие еще есть варианты ? SNAPSHOT aka MATERIALIZED VIEW ? Они вроде быстрее, но время на их создание тоже уходит.....

Спасибо за советы

Построить индексы на source table и после перекачки дропнуть их.

JustMax · Post by **JustMax** » 23 Mar 2004 20:46

Mark wrote:Построить индексы на source table и после перекачки дропнуть их.

Если время вставки в исходную таблицу приемлемое - построить все необходимые индексы один раз.

Кроме того ОБЯЗАТЕЛЬНО поиграться с execution plan и возможно использвать hints (nested_loop, rule, ordered, index, join etc.). Помогает на порядки увеличить скорость, особенно если по source table и другим таблицам не собирается статистика. Вообще для таких специальных случаев - rule оптимизатор и fine tuning c помощью хинтов лучшее средство(IMHO).

Dmitry67 · Post by **Dmitry67** » 23 Mar 2004 21:17

При чтении 70% записей из талицы индексы не только бесплезны и оптиматор не должен их испльзовать
Задачу надо решать декомпозицией
Убрать insert into, select заменить на select count(*)
Какова скорость ?
Убраьвсе трансформации
Убрать joins с другим таблицами наверняка в них дело

Olegus · Post by **Olegus** » 23 Mar 2004 21:18

Vot zdes' pochitajte.
http://docs.linux.cz/Oracle8/server/a58227/ch_dlins.htm

Helmsman · Post by **Helmsman** » 23 Mar 2004 23:08

Можно попробовать опцию parallel при создании промежуточной таблицы, которую создавать с частью условий (хотя это сильно зависит от способностей железа и ОС).

RGoo · Post by **RGoo** » 23 Mar 2004 23:43

Спасибо всем за советы. Завтра буду пробовать предложенное.

P.S. #$% , а про хинты-то я забыл начисто

vc · Post by vc » 24 Mar 2004 01:19

RGoo wrote:Спасибо всем за советы. Завтра буду пробовать предложенное.

P.S. #$% , а про хинты-то я забыл начисто

So far, the best piece of advice came from Dmitry67.

1. Firstly, you have to understand where your problem lies before trying to apply some random 'solutions' like hints, indexes, MVs and such.

2. Undoubtedly, if you process about 70% of the source table, any indexes are useless, at best.

3. On the practical side, you can run your SQL in the trace mode on some reasonable subset of the source data limiting the number of rows with a predicate (such as rownum <= 10000). Then, with the help from tkprof, you can obtain :

a. the execution statistics
a. the actual execution plan
b. the wait time break-down which will show on what events exactly the SQL spent its time.

E.g.:

Code: Select all

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1    417.52    1914.01    2171938    1071811         21          78
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    417.53    1914.02    2171938    1071811         21          78


Rows     Row Source Operation
-------  ---------------------------------------------------
     79  SORT GROUP BY
18939618   HASH JOIN
5505696    TABLE ACCESS FULL XX_ITEM
22492514    TABLE ACCESS FULL XX_INVENTORY PARTITION: START=12 STOP=12


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  file open                                       4        0.05          0.05
  db file scattered read                      26087        0.81        143.09
  db file sequential read                       770        0.08          0.26
  direct path write                           82283        0.43         63.17
  latch free                                    144        0.02          1.46
  direct path read                           558661        1.05       1350.19

In this case, one can see that the bulk of the time was spent on reading the hash join results from the TEMP tablespace (direct path read). Probably increasing hash_area_size will help to decrease the amount of time spent on waiting for this event. Alternaively, re-writing the query may lead to a better execution plan.

... and so on...

Rgds.

serger · Post by **serger** » 24 Mar 2004 02:15

Я бы попробовал
1. Hint /* +APPEND */ плюс надо на target таблицу поставить nologging
ALTER TABLE ... NOLOGGING.
Убрать все индексы с нее.
Потом создать если надою
2. Может самое действенное в этом случае использовать bulk collect.
3. Провеверить параметр db_multiblock_read_count (может быть наврал с названием). Он должен ну очень помочь. Должен быть равен размеру chunk size логического диска.
4. можно использовать для source datafile физически другой диск + использование async io.
5. Обязательно сгенерить explain plan для сессии (использовать tkproff). На предмет явных промахов.
6. Если есть лицензия на partitioning то можно подумать как использовать parallel operations. Если можно обойтись без них то лучше обойтись без них.

Скорее всего надо напирать на пункты 1 и 2. Плюс проверить 3

vc · Post by vc » 24 Mar 2004 02:47

serger wrote:Я бы попробовал
1. Hint /* +APPEND */ плюс надо на target таблицу поставить nologging
ALTER TABLE ... NOLOGGING.
Убрать все индексы с нее.
Потом создать если надою
2. Может самое действенное в этом случае использовать bulk collect.
3. Провеверить параметр db_multiblock_read_count (может быть наврал с названием). Он должен ну очень помочь. Должен быть равен размеру chunk size логического диска.
4. можно использовать для source datafile физически другой диск + использование async io.
5. Обязательно сгенерить explain plan для сессии (использовать tkproff). На предмет явных промахов.
6. Если есть лицензия на partitioning то можно подумать как использовать parallel operations. Если можно обойтись без них то лучше обойтись без них.

Скорее всего надо напирать на пункты 1 и 2. Плюс проверить 3

1. OK
2. bulk collect cannot out-perform "insert /*+ append */ select ... from ..", ever.
3. db_multiblock_read_count makes sense only for raw partitions and direct i/o. A buffered filesystem breaks read requests into 4 - 8k blocks anyway.

Lazy44 · Post by **Lazy44** » 24 Mar 2004 05:00

vc wrote:
serger wrote:Я бы попробовал
1. Hint /* +APPEND */ плюс надо на target таблицу поставить nologging
ALTER TABLE ... NOLOGGING.
Убрать все индексы с нее.
Потом создать если надою
2. Может самое действенное в этом случае использовать bulk collect.
3. Провеверить параметр db_multiblock_read_count (может быть наврал с названием). Он должен ну очень помочь. Должен быть равен размеру chunk size логического диска.
4. можно использовать для source datafile физически другой диск + использование async io.
5. Обязательно сгенерить explain plan для сессии (использовать tkproff). На предмет явных промахов.
6. Если есть лицензия на partitioning то можно подумать как использовать parallel operations. Если можно обойтись без них то лучше обойтись без них.

Скорее всего надо напирать на пункты 1 и 2. Плюс проверить 3

1. OK
2. bulk collect cannot out-perform "insert /*+ append */ select ... from ..", ever.
3. db_multiblock_read_count makes sense only for raw partitions and direct i/o. A buffered filesystem breaks read requests into 4 - 8k blocks anyway.

2 - Я думаю, что для удобства трансформаций можно попробовать bulk collect, если памяти хватит. Все расчеты/трансформации произвести в PL/SQL tables, а потом FORALL .. insert ...

а вообще хотелось бы посмотреть план выполнения запроса вместе со стоимостью, а потом уже думать дальше.

serger · Post by **serger** » 24 Mar 2004 05:18

расчеты вести можно порционно.

Oracle использует direct I/O для Windows NT, 2000, XP принудительно, а это согласитесь ооочень немалая часть серверов. Для больштнства unix систем direct I/O тоже можно и НУЖНО использовать.
Кеш файловой системы только мешает оптимизатору. По крайней мере он очень мешает оптимизатору Oracle 9.2. Oracle 9.2 использует статистику доступа к данным для оптимизации запросов.

Опять же попробовать очень легко
alter session set db_file_multiblock_read_count = ...;

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

Совсем забыл.
a) Посмотреть redo log buffer activity. может надо добавить размера...
b) Если Oracle 8i то может надо создать большой rollback segment. Но может и не надо если использовать +APPEND

serger · Post by **serger** » 24 Mar 2004 05:33

да я имел ввиду использовать bulk collect для считки в PL/SQL table
далее преобразование
и только потом вставка +APPEND в таблицу уже из памяти.

Выбор большой )) Это же ORACLE
Да при наличии primary key materialized view очень хороший вариант. Небольшой недостаток snapshot log.

И совсем уже правильная идея. Oracle Streams и преобразование данных.
Опробовал я пока только без преобразования на Oracle9i... Ну очень многообещающая технология. Бум внедрять.Сейчас девелопмент а через год в production.

Lazy44 · Post by **Lazy44** » 24 Mar 2004 14:06

serger wrote:да я имел ввиду использовать bulk collect для считки в PL/SQL table
далее преобразование
и только потом вставка +APPEND в таблицу уже из памяти.

Выбор большой )) Это же ORACLE
Да при наличии primary key materialized view очень хороший вариант. Небольшой недостаток snapshot log.

И совсем уже правильная идея. Oracle Streams и преобразование данных.
Опробовал я пока только без преобразования на Oracle9i... Ну очень многообещающая технология. Бум внедрять.Сейчас девелопмент а через год в production.

Сделайте FORALL .... INSERT /*+ APPEND *? - Будет очень даже быстро

vc · Post by vc » 24 Mar 2004 15:47

Lazy44 wrote:
serger wrote:да я имел ввиду использовать bulk collect для считки в PL/SQL table
далее преобразование
и только потом вставка +APPEND в таблицу уже из памяти.

Выбор большой )) Это же ORACLE
Да при наличии primary key materialized view очень хороший вариант. Небольшой недостаток snapshot log.

И совсем уже правильная идея. Oracle Streams и преобразование данных.
Опробовал я пока только без преобразования на Oracle9i... Ну очень многообещающая технология. Бум внедрять.Сейчас девелопмент а через год в production.

Сделайте FORALL .... INSERT /*+ APPEND *? - Будет очень даже быстро

In the FORALL...INSERT /*+ APPEND */ statement, the 'append' hint is useless because it works only for 'insert .. select from ...'.

VC

Привет

Вопрос к спецам по Oracle

Вопрос к спецам по Oracle

Re: Вопрос к спецам по Oracle

Re: Вопрос к спецам по Oracle

Re: Вопрос к спецам по Oracle

Re: Вопрос к спецам по Oracle