>ANB   (05.05.09 14:10) [67]
>Скорее всего ваши админы вообще мышей не ловят.
никакой dba не в силах изменить простую аксиому: данные explain plan и данные трейса - это вовсе не одно и то же)

Кщд   (06.05.09 07:56) [79]


> покажите, пожалуйста, сам запрос

>Игорь Шевченко ©   (06.05.09 11:16) [81]
результаты тайминга  на сервере без нагрузки были приведены для insert или - всё же - для select?
есть стойкое подозрение, что на продуктиве реальный план другой
снять план из трейса категорически невозможно?

Кщд   (06.05.09 12:32) [82]


> результаты тайминга  на сервере без нагрузки были приведены
> для insert или - всё же - для select?


Для insert, разумеется.


> снять план из трейса категорически невозможно?


Да, невозможно. План для SELECT с использованием AUTOTRACE снимался с выдачей данных, при этом время выполнения одного SELECT примерно соответствовало времени выполнения INSERT. Я к тому, что основная доля времени падает на SELECT

>Игорь Шевченко ©   (06.05.09 12:59) [83]
в порядке эксперимента предложил бы
1. убрать оставшийся hash join (индекс на sma_t5(cc))
2. использовать --+ append

если это выполнить возможно, то, пожалуйста, выложите планы и тайминги, но именно для insert

PS
как известно, были баги, связанные с тем, что в отлаженных select'ах при использовании в insert into select from "разлетались" планы

Кщд   (06.05.09 13:12) [84]


> 2. использовать --+ append


то, куда insert-ится - это global temporary table, смысла в append как-то не вижу.


> 1. убрать оставшийся hash join (индекс на sma_t5(cc))


Таблица sma_t5 мала - десятки тысяч записей.


> PS
> как известно, были баги, связанные с тем, что в отлаженных
> select'ах при использовании в insert into select from "разлетались"
> планы


Дело в том, что как я писал:

"План для SELECT с использованием AUTOTRACE снимался с выдачей данных, при этом время выполнения одного SELECT примерно соответствовало времени выполнения INSERT. Я к тому, что основная доля времени падает на SELECT"

так что я бы не очень принимал во внимание "были баги при insert ... select"

и INSERT и SELECT ждут dbfile sequential read и dbfile scattered read.

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

Я даже не могу к себе данные перетащить и потрассировать запросы - у меня XE, а данных всяко больше 4-х Гб

>Игорь Шевченко ©   (06.05.09 13:38) [85]

то, куда insert-ится - это global temporary table, смысла в append как-то не вижу.

согласен


Таблица sma_t5 мала - десятки тысяч записей.

тем не менее, хотелось бы увидеть максимально оттюненный SQL и уже потом, разбираться с "тормозами"


так что я бы не очень принимал во внимание "были баги при insert ... select"

как понимаете, время не аргумент
если план для insert показывает то же, что план select, то (при отсутствии возможности снять настоящий план из трейса) предложение снимается


и INSERT и SELECT ждут dbfile sequential read и dbfile scattered read.

т.е., другими словами, это индексные чтения и FTS, поэтому и хотел бы свести всё к индексным


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

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

Кщд   (06.05.09 14:02) [86]

Из поста [65] "Статистика собирается регулярно, в том числе и системная." :)


> тем не менее, хотелось бы увидеть максимально оттюненный
> SQL и уже потом, разбираться с "тормозами"


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

Тут еще момент - сейчас индекс отключен, а развлекаться с ним я могу, когда сервер не занят, то есть, в очередные выходные.

>Игорь Шевченко ©   (06.05.09 14:33) [87]

Из поста [65] "Статистика собирается регулярно, в том числе и системная." :)

прошу прощения, взгляд "замылился")


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

возможно, под нагрузкой собранная статистика становится недостоверной(в течение дня интенсивные insert/update/delete) и индекс слетает
в общем, всё это без трейса, увы, гадание на кофейной гуще...
ну, разве что посмотреть, на каком именно индексе висят ожидания sequential read
и попробовать увеличить buffer cache

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

Кщд   (06.05.09 14:50) [88]

собрали трассировку с криками е.т.м :)

Получили странное из статистики:







и







Сильно не понравилось близость цифр elapsed на Execute и db file sequential read в ожиданиях.

Будем думать.

План кстати не изменился

Странноватая ситуация. Обычно индекс_ранг_скан слабо зависит от нагрузки.
Я мог предположить, что завис шел при очистке целевой таблицы транкейтом (была у нас такая грабля), но тут явно другое.

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

> Странноватая ситуация


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

В больших таблицах первого варианта по ~30 и ~50 миллионов, в больших таблицах второго варианта ~50 и ~150 миллионов, второй вариант выполняется быстрее и статистика ожиданий у него более похожа на правду.

Я просто к чему - дело не в запросе. С запросами я бороться более или менее умею. Мне интересно, в чем может причина.


> Скорее всего надо внимательно посмотреть на табличное пространство,
>  в которое посажен новый функциональный индекс


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

Все индексы в одном tablespace

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

Сколько всего строк в таблице с этим индексом и сколько из них отбирается ?

Хотя если без нагрузки работает быстро . . .

Табличное пространство дефрагментрировать не пробовали ?

ANB   (08.05.09 16:07) [93]

Ты как тот раввин - "а жаль, еще столько идей было".


> Табличное пространство дефрагментрировать не пробовали ?


Пробовали, не помогло. На праздниках попробую ANALYZE INDEX VALIDATE STRUCTURE по индексам (их немного) таблиц проблемного запроса.

> Ты как тот раввин - "а жаль, еще столько идей было".

Угу. Оптимизация толстых запросов - это почти всегда пляски с бубном. :)

На 2 запроса разбить не пробовал ? У нас частенько разбиение толстого запроса по нескольким таблицам на несколько отдельных инсертов (перекладкой) с селектом в связке по двум таблицам дает ускорение на порядок а то и на 2. И проблемный кусок быстрее выявляется.

> У нас частенько разбиение толстого запроса по нескольким
> таблицам на несколько отдельных инсертов (перекладкой) с
> селектом в связке по двум таблицам дает ускорение на порядок
> а то и на 2.


Это как ?

insert into T_Rpt
select
 *
from
 T1
,T2
,T4
,T5
,T6
where
 условия

заменяем на несколько инсертов типа

insert into T_Rpt_1
select
 *
from
 T1
,T2
where
 условия

insert into T_Rpt_2
select
 *
from
 T_Rpt_1
,T4
where
 условия

и. т. Есно с набором полей вместо *

В каждой порции подбираем оптимальный план, (хэш джойн или нестед лупс), при надобности - распараллеливаем.

Ресурсов, правда, жрет такой метод до хрена. Зато работает в приемлемые сроки. И отлаживать проще.

ANB   (13.05.09 16:15) [97]

Такой путь неприемлем

>Игорь Шевченко ©   (06.05.09 16:48) [89]
разберетесь, выложите, пожалуйста, здесь результат?

>ANB   (13.05.09 14:09) [95]
>Угу. Оптимизация толстых запросов - это почти всегда пляски с бубном. :)
оптимизатор - это набор правил, статистики и багов
бубна требуют лишь баги для разработки workaround
да и то - проблема с последними - зачастую - решается прочтением соответствующей ноты с metalink
поэтому всякое упоминание о "колдунстве" со стороны разработчиков БД лично я воспринимаю, как попытку набить себе цену
уж извините)