Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

Как составить запрос к базе [D7, FB 2]

Страницы: < 1 2 3 4 5 6 >
OtherSie © (2009-04-23 22:29:00)
Есть 2 таблицы. 1-я - справочник. Содержит в себе список работников и первичный ключ. Вторая - список заказов с прикреплёнными к ним работниками. Содержит 4 поля - внешние ключи - ссылки на первичный ключ первой таблицы и 5-е поле - первичный ключ заказов.

Нужно выбрать фамилии из первой таблицы по запросу нужного значения первичного ключа во второй таблице.

Как сделать с хранимой процедурой с четырьмя подзапросами - знаю.

Можно ли как-то выполнить задачу одним запросом? Спасибо.

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

Игорь Шевченко (2009-05-06 11:16:00) [22]
Кщд (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 07:59:00) [23]
>ANB (05.05.09 14:10) [67]
>Скорее всего ваши админы вообще мышей не ловят.
никакой dba не в силах изменить простую аксиому: данные explain plan и данные трейса - это вовсе не одно и то же)

Кщд (2009-05-06 07:56:00) [24]
>Игорь Шевченко © (05.05.09 17:35) [78]
покажите, пожалуйста, сам запрос

Игорь Шевченко (2009-05-05 17:35:00) [25]
Кщд (05.05.09 14:51) [71]

Кстати, обманул насчет версии Oracle

SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Игорь Шевченко (2009-05-05 17:06:00) [26]
Anatoly Podgoretsky © (05.05.09 17:03) [76]

Тогда бы не наблюдалось явления, описанного в [65], из двух Тб оно бы преспокойно быстренько выбиралось в течение недели, а не только по выходным. На выходные память не наращивают, это точно.

Anatoly Podgoretsky (2009-05-05 17:03:00) [27]
Может у тебя 2 террабайта памяти...

Игорь Шевченко (2009-05-05 16:17:00) [28]
ANB (05.05.09 15:43) [74]

> Сколько всего записей в BIG_T3 и сколько примерно из них
> попадает в запрос ?

всего десятки миллионов, попадают десятки тысяч

> Сколько записей в таблице индекса IX_SMA_T2 и сколько из
> них попадает в запрос при первичном отборе ?

Сколько-то тысяч, сколько-то сотен попадает.

> Хотя даже навскидку - выкини нафик индекс

Какой именно ?

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

То есть, через неделю после создания, работы на сервер, в выходные на пустом сервере он все еще оставался в кэше ?

Любопытная жизнь у индексов.

ANB (2009-05-05 15:43:00) [29]
| 8 | TABLE ACCESS BY INDEX ROWID| BIG_T3 | 219 | 4818 | 4 |
| 9 | INDEX RANGE SCAN | IX_ | 15 | | 1 |

Сколько всего записей в BIG_T3 и сколько примерно из них попадает в запрос ?

Сколько записей в таблице индекса IX_SMA_T2 и сколько из них попадает в запрос при первичном отборе ?

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

После он потерялся.

Игорь Шевченко (2009-05-05 15:10:00) [30]

> кстати, ф-ция в function based index - deterministic?

Явно задана в при создании индекса

CREATE INDEX ix_ ON bigtable (field2, (CASE WHEN TRANSLATE(TRIM(field1),'x01234567890', 'x') IS NULL THEN TO_NUMBER(field1) ELSE 999999 END),field3)

Кщд (05.05.09 14:54) [72]

> навскидочку, если 6-ой hash join заменить на nested loops?

Это план без индекса

> если навскидку, то я бы посмотрел, чего ждет сессия(v$session_wait),
> в которой работает данный запрос

db_file_sequential_read она ждет

> затем - смотрел бы на план из трейса

а это исключено политикой безопасности

план смотрелся из SQL*Plus с включенной опцией autotrace

собственно план с индексом:

------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1940 | 251K| 7384 | | 1 | HASH JOIN | | 1940 | 251K| 7384 | | 2 | TABLE ACCESS FULL | SMALLT1 | 9 | 117 | 2 | | 3 | NESTED LOOPS ANTI | | 2511 | 294K| 7381 | | 4 | HASH JOIN | | 2582 | 252K| 7380 | | 5 | NESTED LOOPS | | 2582 | 226K| 7375 | | 6 | NESTED LOOPS | | 17517 | 821K| 355 | | 7 | INDEX FULL SCAN | IX_SMA_T2 | 80 | 2080 | 2 | | 8 | TABLE ACCESS BY INDEX ROWID| BIG_T3 | 219 | 4818 | 4 | | 9 | INDEX RANGE SCAN | IX_ | 15 | | 1 | | 10 | TABLE ACCESS BY INDEX ROWID | MED_T4 | 1 | 42 | 1 | | 11 | INDEX UNIQUE SCAN | PK_MED_T4 | 1 | | 1 | | 12 | TABLE ACCESS FULL | SMA_T5 | 13014 | 127K| 5 | | 13 | INDEX UNIQUE SCAN | IX_MED_T6 | 655 | 13100 | 1 | -------------------------------------------------------------------------------

Из которого видно, что 6-ой HAS JOIN заменился на NESTED LOOPS

Кщд (2009-05-05 14:54:00) [31]
>Игорь Шевченко © (05.05.09 14:47) [70]
навскидочку, если 6-ой hash join заменить на nested loops?

Кщд (2009-05-05 14:51:00) [32]
>Игорь Шевченко © (05.05.09 13:50) [65]
>COST запроса сократился, IO сократились, запрос стал выполняться пару >минут. Это при незагруженном сервере. А при загруженном сервере >запрос выполняется полтора часа, то есть, в три раза медленней
если навскидку, то я бы посмотрел, чего ждет сессия(v$session_wait), в которой работает данный запрос, при выполнении на продуктивном сервере
затем - смотрел бы на план из трейса

кстати, ф-ция в function based index - deterministic?

PS и, конечно, патчился бы до 10.2.0.4
понимаю-понимаю, что патчить продуктив не всегда можно)

Игорь Шевченко (2009-05-05 14:47:00) [33]

> План покажи

Смотри

-------------------------------------------------------------------------------- |Id |Operation | Name | Rows | Bytes | Cost | -------------------------------------------------------------------------------- | 0 |SELECT STATEMENT | | 1940 | 251K| 30528 | | 1 | HASH JOIN | | 1940 | 251K| 30528 | | 2 | TABLE ACCESS FULL |SMALLT1 | 9 | 117 | 2 | | 3 | NESTED LOOPS ANTI | | 2511 | 294K| 30525 | | 4 | HASH JOIN | | 2582 | 252K| 30524 | | 5 | NESTED LOOPS | | 2582 | 226K| 30519 | | 6 | HASH JOIN | | 17517 | 821K| 23499 | | 7 | INDEX FULL SCAN |IX_SMA_T2 | 80 | 2080 | 2 | | 8 | TABLE ACCESS FULL | BIG_T3 | 50M| 1069M| 22488 | | 9 | TABLE ACCESS BY INDEX ROWID| MED_T4 | 1 | 42 | 1 | | 10 | INDEX UNIQUE SCAN | PK_MED_T4 | 1 | | 1 | | 11 | TABLE ACCESS FULL | SMA_T5 | 13014 | 127K| 5 | | 12 | INDEX UNIQUE SCAN | IX_MED_T6 | 655 | 13100 | 1 | --------------------------------------------------------------------------------

ANB (2009-05-05 14:33:00) [34]

> Вопрос в следующем: В какую строну рыть ? :)

План покажи

ANB (2009-05-05 14:30:00) [35]

> хинт в серверном коде - зло

Но неизбежное.

ANB (2009-05-05 14:10:00) [36]

> в любой сколько-нибудь "тяжелой"(миллионники, запутанная
> логика в SQL) системе - это данность

Скорее всего ваши админы вообще мышей не ловят.
У нас как раз запутанная логика в SQL, нету внешних ключей и все прочее, от чего нормальный архитектор БД пришел бы в ужас.
Таблица с несколькими миллионами записей у нас считается маленькой.

Однако, повторюсь, на наших базах ни разу не нарывался, что план на тесте по Ф5 не совпадает с планом на реалке по трэйсу.

ANB (2009-05-05 14:05:00) [37]

> В Firebird это может аукнуться. Например, генеришь IBExpert-
> ом тестовых записей миллиона два-три, чтобы скучно ему
> не было. Выполняешь скрипт - вроде и план правильный подхватился.
> А потом на реальных данных "вес" индекса другой получается,
> т.к реальные данные далеко не равномерно распределенные
> случайные величины, как в генераторе IBExpert-a. И план
> твоего запроса (имеется в виду внутри ХП) после первого
> backup/restore "поедет" куда-то. Так что все-таки бывают
> случаи, когда планы иногда надо указывать явно. Лично у
> мну есть пример запроса с "дефолтным" планом, выполняющийся
> за 18 секунд, и "ручным", выполняющимся за миллисекунды.
>

1) Я план то смотрю на реальных данных. План, есно, может поехать в любой момент, но на то у нас и админы есть, чтобы возникшие тормоза выявлять и нам на правку отдавать. За год ни одного такого случая не было.
2) У оракла - хинты - это комментарии, которые он волен игнорить.

Игорь Шевченко (2009-05-05 13:50:00) [38]
Кщд (05.05.09 13:33) [64]

Понятно. А вот такой вопрос - есть таблица, с порядком записей 10^8. Есть запрос к этой таблице и еще нескольким (у одной из нескольких порядок записей 10^6, у других несущественный (меньше 10^4))
Оптимизатор определяет, что толстую таблицу он будет обрабатывать FULL SCAN, запрос выполняется порядка получаса. Создал я нужный индекс по толстой таблице (функция от одного поля и и еще набор полей), индекс стал использоваться, COST запроса сократился, IO сократились, запрос стал выполняться пару минут. Это при незагруженном сервере. А при загруженном сервере запрос выполняется полтора часа, то есть, в три раза медленней, чем при FULL SCAN толстой таблицы. Время выполнения запроса с FULL SCAN от загруженности сервера практически не зависит, полчаса, плюс-минус 5 минут.

Oracle 10.2.0.3 под Linux, сервер достаточно толстый по памяти, процессору и проч. Статистика собирается регулярно, в том числе и системная.
В сессии выставлены параметры:
Optimizer_Index_Caching=90
Optimizer_Index_Cost_Adj=40

Вопрос в следующем: В какую строну рыть ? :)

Кщд (2009-05-05 13:33:00) [39]
>Игорь Шевченко © (05.05.09 12:41) [63]
имел в виду таблицы с порядком записей 10^6 и выше

Игорь Шевченко (2009-05-05 12:41:00) [40]

> миллионники

это что такое ?

Страницы: < 1 2 3 4 5 6 >

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »