Как составить запрос к базе

← →
Кщд (2009-05-06 07:59) [80]

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

← →
Игорь Шевченко © (2009-05-06 11:16) [81]

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

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

INSERT INTO tempt (f1,f2,f3,f4,f5) SELECT t4.f1,t4.f2,"FOO" t3, CASE WHEN t4.aa = "BAR" THEN "BAR" WHEN t4.aa = "BAZ" THEN "FOO" END f4, t4.f5 FROM med_t4 t4, sma_t5 t5,smallt1 t1,bigtable big_t3, sma_t2 t2 WHERE t4.aa IN ("BAR","BAZ") AND big_t3.f1 = t4.f1 AND t4.bb BETWEEN :ParamS AND :ParamE AND t4.cc = t5.cc AND TRIM(t5.dd) = t1.dd AND t1.ee = :Param AND big_t3.zz = t2.field2 AND CASE WHEN TRANSLATE(TRIM(big_t3.field2),"x01234567890", "x") IS NULL THEN TO_NUMBER(big_t3.field2) ELSE 999999 END = t2.yy AND big_t3.field3 BETWEEN t2.s AND t2.e AND t2.xx = :Param1 AND NOT EXISTS ( SELECT NULL FROM med_t6 WHERE aaa = :param2 AND bbb = t4.f1 )

← →
Кщд (2009-05-06 12:32) [82]

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

← →
Игорь Шевченко © (2009-05-06 12:59) [83]

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

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

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

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

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

← →
Кщд (2009-05-06 13:12) [84]

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

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

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

← →
Игорь Шевченко © (2009-05-06 13:38) [85]

Кщд (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-х Гб

← →
Кщд (2009-05-06 14:02) [86]

>Игорь Шевченко © (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, поэтому и хотел бы свести всё к индексным

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

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

← →
Игорь Шевченко © (2009-05-06 14:33) [87]

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

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

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

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

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

← →
Кщд (2009-05-06 14:50) [88]

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

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

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

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

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

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

← →
Игорь Шевченко © (2009-05-06 16:48) [89]

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

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

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

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 4 7.19 433.51 96337 323853 61 292 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 7.20 433.51 96337 323853 61 292

Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 96245 0.24 428.98 db file scattered read 11 0.00 0.03 SQL*Net message to client 4 0.00 0.00 SQL*Net message from client 4 51.29 51.32

и

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 43.92 2150.75 567201 2856992 5844 4017 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 43.92 2150.75 567201 2856992 5844 4017

Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 567130 0.26 2124.65 latch: object queue header operation 2 0.00 0.00 db file scattered read 9 0.00 0.03 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 0.00 0.00

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

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

← →
Игорь Шевченко © (2009-05-06 16:49) [90]

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

← →
ANB (2009-05-07 17:04) [91]

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

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

← →
Игорь Шевченко © (2009-05-07 17:19) [92]

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

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

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

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

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

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

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

← →
ANB (2009-05-08 16:07) [93]

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

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

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

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

← →
Игорь Шевченко © (2009-05-08 20:45) [94]

ANB (08.05.09 16:07) [93]

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

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

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

← →
ANB (2009-05-13 14:09) [95]

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

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

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

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

Это как ?

← →
ANB (2009-05-13 16:15) [97]

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]

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

← →
Кщд (2009-05-14 07:21) [99]

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

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

Кщд (14.05.09 07:21) [99]

> разберетесь, выложите, пожалуйста, здесь результат?

Постараюсь, только неизвестно, сколько времени займет этот процесс - он в фоновом режиме

← →
имя (2009-08-19 14:22) [101]

Удалено модератором

← →
имя (2009-08-19 14:22) [102]

Удалено модератором

Как составить запрос к базе Найти похожие ветки