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

[RGoo]

Проблема у меня странная : из одной большой таблицы <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]

Проблема у меня странная : из одной большой таблицы <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]

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

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

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

[Dmitry67]

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

[Olegus]

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

[Helmsman]

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

[RGoo]

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

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

[vc]

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

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]

Я бы попробовал
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]

Я бы попробовал
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]

Я бы попробовал
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]

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

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]

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

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

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

[Lazy44]

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

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

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

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

[vc]

да я имел ввиду использовать 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

[Lazy44]

да я имел ввиду использовать 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

OK, My bad

[RGoo]

Еще раз спасибо всем за советы. Расклад такой : базу сильно мне менять никто не даст (в смысле менять db_multiblock_read_count и пр. параметры), поэтому работа пошла по пути переделывания стейтмента и хинтов (статистика показывает, что основная засада в join-ах) и отключения\подключения индексов.