Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PowerBuilder Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Using Oracle Stored Procedures in DataWindows  [new]
Black Savage
Member

Откуда:
Сообщений: 736
Давно хотел спросить. Случайно прочитал статью Using Oracle Stored Procedures in Your DataWindows.
Мне все не дает покоя вот, что. Автор перечисляет преимущества использования
Stored Procedures в DataWindows по сравнению с SQL statements:
Bruce Armstrong
First, stored procedures in the database are precompiled, which means they'll
significantly outperform uncompiled SQL statements. In addition, stored procedures provide for
data-hiding, i.e., the end user doesn't need to know or have the ability to access the underlying tables
in order to retrieve the data.


Далее идет фраза о том, что это все не применимо к Oracle:

Bruce Armstrong
Note that neither of these advantages are significant when using an Oracle
database. Provided the application is being run with binding enabled, the database is storing the bound
and compiled statements in its SHARED_SQL area, which means that SQL SELECT statements will
achieve similar performance as stored procedures. Further, Oracle's role-based security model can and
should be used to restrict the user's ability to access data only in the context of the application
session. That is, the role they need to access the data can be enabled only for the application session,
and only for the duration of the application session (nondefault roles expire when the session ends).


Вот и хотелось бы узнать. Правда ли это? Не ужели Stored Procedures и
SQL statements в DataWindows дают одинаковый перформенс? У меня, как
у человека раньше активно работающего с MS SQL Server 2000, это просто в голове
не укладывается...
28 апр 07, 18:28    [4085228]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Oleg1
Member

Откуда:
Сообщений: 121
Black Savage
Вот и хотелось бы узнать. Правда ли это? Не ужели Stored Procedures и
SQL statements в DataWindows дают одинаковый перформенс? У меня, как
у человека раньше активно работающего с MS SQL Server 2000, это просто в голове
не укладывается...
Там описано два способа. Про какой из них вопрос?
28 апр 07, 19:37    [4085337]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Black Savage
Member

Откуда:
Сообщений: 736
Oleg1
Там описано два способа. Про какой из них вопрос?

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

Bruce Armstrong - человек вполне известный и уважаемый, я его
мнение не оспариваю. Просто хочу понять, ПОЧЕМУ это так. Объясните,
плиз, если конечно не лень, ботать по клаве пальцАми...
30 апр 07, 11:12    [4086892]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
Black Savage
Не ужели Stored Procedures и
SQL statements в DataWindows дают одинаковый перформенс? У меня, как
у человека раньше активно работающего с MS SQL Server 2000, это просто в голове
не укладывается...

А почему они должны давать разную скорость?
Разница между ними только в том что в ХП запрос уже скомпилирован.
Но эта разница нивелируется из-за кеширования скомпилированных запросов в Оракле.
О чем и было сказано.
30 апр 07, 15:50    [4087173]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Локшин Марк
Member

Откуда: Воронеж
Сообщений: 3154
18-я весна
Разница между ними только в том что в ХП запрос уже скомпилирован.
Но эта разница нивелируется из-за кеширования скомпилированных запросов в Оракле.
О чем и было сказано.

Ну.... если бы все было так просто. В запросе же есть параметры. Как и в хранимой процедуре. Хранимая процедура скомпилирована для "средних" параметров - некой эвристики на которую ориентируется оптимизатор запросов.
В случае же запроса, в общем случае, каждый раз план исполнения строится заново. Да, есть кэш запросов, но он сработает если параметры будут такие же - план запроса все равно будет тот же. Конечно есть автоматическая параметризация запросов, но она применяется только к достаточно простым запросам (так по крайней мере в MS SQL).
Так что наличие наперед скомпилированного плана исполнения может давать как прирост (экономия на времени компиляции запроса), так и деградацию производительности ("средняя температура по больнице", с которой скомпилирован запрос никак не вяжется с конкретными значениями параметров запроса).
По-моему это справедливо для любой современной РСУБД, так что что-то в сомнениях Black Savage есть.
30 апр 07, 16:52    [4087269]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
Локшин Марк
В случае же запроса, в общем случае, каждый раз план исполнения строится заново. Да, есть кэш запросов, но он сработает если параметры будут такие же - план запроса все равно будет тот же.


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

Так что мнение, что должна быть какая-то разница в скорости ХП и SQL мне лично непонятно.
30 апр 07, 17:12    [4087324]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Локшин Марк
Member

Откуда: Воронеж
Сообщений: 3154
18-я весна
И производительность будет одинаковая, за исключением того, что запрос в ХП компилируется только один раз, а самостоятельный запрос компилируется каждый раз при выполнении. При кешировании и эта разница устраняется.

Такого быть не может. Потому, что даже для запроса
select * from table where id = 1
если id=1 в таблице встречается 2 раза и строк в таблице 10000 и есть индекс на id лучше делать index seek, то для
select * from table where id = 2
которых в таблице 1000 лучше делать table scan.
Поэтому наперед скомпилированный запрос для "усредненных" параметров не может содержать оптимальный план исполнения для любых параметров. Вот в чем проблема.
30 апр 07, 17:26    [4087348]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
Локшин Марк
18-я весна
И производительность будет одинаковая, за исключением того, что запрос в ХП компилируется только один раз, а самостоятельный запрос компилируется каждый раз при выполнении. При кешировании и эта разница устраняется.

Такого быть не может. Потому, что даже для запроса
select * from table where id = 1
если id=1 в таблице встречается 2 раза и строк в таблице 10000 и есть индекс на id лучше делать index seek, то для
select * from table where id = 2
которых в таблице 1000 лучше делать table scan.
Поэтому наперед скомпилированный запрос для "усредненных" параметров не может содержать оптимальный план исполнения для любых параметров. Вот в чем проблема.

Не понимаю с чем Вы спорите.
Про оптимальность речь вообще не шла.

Речь идет про различия между запросом в ХП и просто запросом.
В Оракле их нет.
30 апр 07, 19:39    [4087493]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Локшин Марк
Member

Откуда: Воронеж
Сообщений: 3154
18-я весна
Не понимаю с чем Вы спорите.
Про оптимальность речь вообще не шла.

Прочитайте самое первое сообщение еще раз
Black Savage
Не ужели Stored Procedures и
SQL statements в DataWindows дают одинаковый перформенс?

18-я весна
Речь идет про различия между запросом в ХП и просто запросом.
В Оракле их нет.

Этого не может быть, потому что в общем случае в ХП есть аргументы а в запросе нет, поэтому различия должны быть. Речь здесь как раз и идет про производительность.
А может быть Black Savage вообще что-то другое имел ввиду...
1 май 07, 11:48    [4087995]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
ЗоринАндрей
Member

Откуда: Санкт-Петербург
Сообщений: 3004
Локшин Марк
а в запросе нет
Это еще почему? Если ораклоиды выяснят что вы пользуетесь запросами без bind-variables то скажут что писал идиот, и в какой-то мере будут правы.
1 май 07, 23:51    [4088745]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
ЗоринАндрей
Member

Откуда: Санкт-Петербург
Сообщений: 3004
Локшин Марк
Поэтому наперед скомпилированный запрос для "усредненных" параметров не может содержать оптимальный план исполнения для любых параметров. Вот в чем проблема.

Итак для Оракла как мы выяснили и в случае SP и в случае запроса мы имеем СКОМПИЛИРОВАННЫЙ запрос. который МОЖЕТ быть неоптимальным ( с этим никто не спорит ). НО какое отношение неоптимальность запроса имеет к РАЗНИЦЕ между запросом и SP?
1 май 07, 23:54    [4088754]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
White Owl
Member

Откуда:
Сообщений: 12583
Простите чайнка в Оракле, но вы случаем не путаете компиляцию запроса и построение плана запроса? Или в Оракле это одно общее действие? Не верится что-то...

С точки зрения абстрактной БД и ХП и отдельный запрос должны быть скомпилированы перед запуском и здесь у ХП действительно есть мизерное преимущество - на нее sql парсер запускается всего один раз а на отдельные запросы каждый раз.
А вот решение какой индекс надо использовать, какую таблицу брать из кеша а какую подчитать с диска это все должно решаться каждый раз заново и тут ХП с отдельным запросом равны. Если вычесть время компиляции (читай вычесть время на работу sql парсера) то скорость у них должна быть одинаковой.
2 май 07, 01:31    [4088877]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
Локшин Марк
18-я весна
Не понимаю с чем Вы спорите.
Про оптимальность речь вообще не шла.

Прочитайте самое первое сообщение еще раз

Я как раз на него и ссылался :)
автор

Black Savage
Не ужели Stored Procedures и
SQL statements в DataWindows дают одинаковый перформенс?

18-я весна
Речь идет про различия между запросом в ХП и просто запросом.
В Оракле их нет.

Этого не может быть, потому что в общем случае в ХП есть аргументы а в запросе нет, поэтому различия должны быть. Речь здесь как раз и идет про производительность.
А может быть Black Savage вообще что-то другое имел ввиду...

Еще раз хочу обратить Ваше внимание, что речь идет про сравнение производительности ОДНОГО И ТОГО ЖЕ запроса в ХП и в самостоятельном операторе.
Если запросы разные, то я вообще не рискнул бы предсказать, какой из них будет быстрее, не видя их тексты :)
2 май 07, 02:38    [4088907]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
White Owl
Простите чайнка в Оракле, но вы случаем не путаете компиляцию запроса и построение плана запроса? Или в Оракле это одно общее действие? Не верится что-то...

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

С точки зрения абстрактной БД и ХП и отдельный запрос должны быть скомпилированы перед запуском и здесь у ХП действительно есть мизерное преимущество - на нее sql парсер запускается всего один раз а на отдельные запросы каждый раз.
А вот решение какой индекс надо использовать, какую таблицу брать из кеша а какую подчитать с диска это все должно решаться каждый раз заново и тут ХП с отдельным запросом равны. Если вычесть время компиляции (читай вычесть время на работу sql парсера) то скорость у них должна быть одинаковой.

Оракл кеширует в том числе и планы. Просто если план становится неактуальным он удаляется из кеша.
2 май 07, 03:07    [4088920]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Локшин Марк
Member

Откуда: Воронеж
Сообщений: 3154
White Owl
Простите чайнка в Оракле, но вы случаем не путаете компиляцию запроса и построение плана запроса? Или в Оракле это одно общее действие? Не верится что-то...

Да это в общем-то везде как одно действие. В MS SQL, например, чтобы план исполнения ХП каждый раз строился заново ее нужно создать с опцией
with recompile
ЗоринАндрей
Это еще почему? Если ораклоиды выяснят что вы пользуетесь запросами без bind-variables то скажут что писал идиот, и в какой-то мере будут правы.

Да, про binding enabled там написано :)
ЗоринАндрей
НО какое отношение неоптимальность запроса имеет к РАЗНИЦЕ между запросом и SP?

Ну, как я понимаю, имеется ввиду разница в производительности. Если пользоваться bind-variables, то я тогда тоже не очень понимаю в чем разница. Но кэшировать планы и запросы с параметрами выполнять, кстати, не только Oracle может.
2 май 07, 10:06    [4089453]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Black Savage
Member

Откуда:
Сообщений: 736
"Вы еще подеритесь тут - горячие финские парни..."
(с) не мой.

Ладно, так и порешили:
Bruce Armstrong
SQL SELECT statements will achieve similar performance as stored procedures


В моем недавнем отчете я, собственно, так и написал, просто хотелось еще раз все проверить
и услышать мнение коллег...
2 май 07, 10:17    [4089502]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
Ну вы, робяты тут и наговорили...


Хранимая процедура скомпилирована для "средних" параметров - некой эвристики на которую ориентируется оптимизатор запросов

И производительность будет одинаковая, за исключением того, что запрос в ХП компилируется только один раз, а самостоятельный запрос компилируется каждый раз при выполнении

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

Если ораклоиды выяснят что вы пользуетесь запросами без bind-variables то скажут что писал идиот, и в какой-то мере будут правы.


Внесу ка я свои две копейки...

1. в Oracle обычно говорят не про компиляцию запроса, а про разбор (parsing)
2. когда на сервер передается очередной запрос (впервые), Oracle производит его разбор, построение плана выполнения, связывание переменных (binding) и выполнение. В процессе разбора производится определение существования объектов (таблиц и т.д.), что в случае ХП происходит на этапе ее компиляции (здесь есть определенная экономия времени со стороны ХП, но небольшая).
3. разобранный запрос остается в кэше, поэтому если выдается точно такой же запрос, то повторно происходит только т.н. мягкий разбор (soft parsing), при котором, например, проверяются привилегии текущего пользователя на упомянутые в запросе объекты. Все остальное, в том числе и план выполнения, используются повторно. Здесь преимущество ХП м.б. только в том случае, если в приложении в нескольких местах используется одинаковый запрос, но написанный по разному (лишний пробел, регистр символов и т.д.). В этом случае повторного использования не будет, поскольку Oracle посчитает эти запросы разными и для каждого будет выполнен hard parse.
4. По поводу использования bind var тоже вопрос не однозначный. С одной стороны, их использование сокращает кол. hard parse, с другой, иногда наличие константы вместо bind var позволяет построить более оптимальный план выполнения (не зря в Oracle 9 введено т.н. "подсматривание значений" - bind peeking). Поэтому, в общем случае, для OLTP - систем, где запросы относительно короткоиграющие, рекомендуется использовать bind var, а для DSS - систем, где время выполнения значительно превышает время разбора и важен оптимальный план, рекомендуется использовать константы вместо bind var
2 май 07, 11:04    [4089747]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Black Savage
Member

Откуда:
Сообщений: 736
To tru55
Публикуя свой вопрос, я признаться, как раз и рассчитывал получить такой вот
грамотный ответ. СПАСИБО
2 май 07, 11:14    [4089800]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
White Owl
Member

Откуда:
Сообщений: 12583
18-я весна
Оракл кеширует в том числе и планы. Просто если план становится неактуальным он удаляется из кеша.
А по каким критериям Оракл решает что план стал неактуален?
2 май 07, 18:53    [4092252]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
White Owl
18-я весна
Оракл кеширует в том числе и планы. Просто если план становится неактуальным он удаляется из кеша.
А по каким критериям Оракл решает что план стал неактуален?

Ну например, если на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша.
По другим критериям надо смотреть в доках.
3 май 07, 00:33    [4092731]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Локшин Марк
Member

Откуда: Воронеж
Сообщений: 3154
18-я весна
если на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша

А это точно так? По-моему выглядит несколько нелогично. Что из того, что у кого-то отобрали права на часть объектов запроса? Я понимаю, если изменилась схема данных, удалили индекс, но права?
3 май 07, 09:37    [4093231]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
Локшин Марк
18-я весна
если на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша

А это точно так? По-моему выглядит несколько нелогично. Что из того, что у кого-то отобрали права на часть объектов запроса? Я понимаю, если изменилась схема данных, удалили индекс, но права?


Это точно НЕ так.
В кэше есть специальные защелки, которые отслеживают зависимости объектов. Если какой-то объект меняется (например, добавляется колонка в таблицу), то все зависимые объекты помечаются как инвалидные. Если в SQL (или PL/SQL), находящемся в кэше, есть обращение к подобным объектам, то он тоже помечается как инвалидный и при следующей выдаче такого SQL будет повторно разобран (hard parse).
По поводу прав ситуация другая, поскольку неизвестно, какой пользователь выдаст очередной такой SQL. Права проверяются на этапе soft parse, о чем я уже написал.
Другое дело, что размеры кэша ограничены и если для очередного запроса не хватит места, то из кэша будет стерт наиболее давно не использовавшийся запрос (алгоритм LRU)
3 май 07, 10:32    [4093522]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
Oleg1
Member

Откуда:
Сообщений: 121
Black Savage
Oleg1
Там описано два способа. Про какой из них вопрос?

Я не понял вопроса. Цитата, которую я привел выше, является общим утверждением,
которое было выдвинуто автором в начале статьи, без относительно методов и способов.
Зря. Там описан как старый способ, являющийся обходным решением, использование которого не дает никаких премуществ в скорости, и при этом добавляет накладные расходы. Для нового способа все утверждения по работе хранимых верны.
Про компиляцию уже выше разъяснили.
На практике скорость выполнения запроса напрямую или из хранимой в Оракле практически не отличается. А вот в MS как раз наоборот. Уж не знаю, что там навернули в этом сервере, но у него более-менее сложные запросы вызывают заметные затруднения и в хранимой могут выполняться раза в 2 быстрее, чем напрямую. Что IMHO ненормально.
3 май 07, 12:10    [4094156]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
tru55
Локшин Марк
18-я весна
если на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша

А это точно так? По-моему выглядит несколько нелогично. Что из того, что у кого-то отобрали права на часть объектов запроса? Я понимаю, если изменилась схема данных, удалили индекс, но права?


Это точно НЕ так.


А Вы докажите.
Вот мое доказательство:

-- Release 10.2.0.1.0 (XE)
-- имеем 3 сеанса:SQL1, SQL2, SQL3

SQL1> connect p1/qq@xe
Connected.
SQL1> grant select on t1 to p2;

Grant succeeded.

SQL2> connect p2/qq@xe
Connected.
-- прогоняем один запрос 2 раза, в кеше должна получится одна запись
SQL2> select 1 from p1.t1;

         1
----------
         1

SQL2> select 1 from p1.t1;

         1
----------
         1


SQL3> select sql_text from v$sql where sql_text like '%from p1.t1%';

SQL_TEXT
-----------------------------------------------------------------------

select sql_text from v$sql where sql_text like '%from p1.t1%'
select 1 from p1.t1

-- видим что запрос появился в кеше в одном экземпляре


SQL1> revoke select on t1 from p2;

Revoke succeeded.


SQL3> select sql_text from v$sql where sql_text like '%from p1.t1%';

SQL_TEXT
-----------------------------------------------------------------------

select sql_text from v$sql where sql_text like '%from p1.t1%'


-- видим что запрос исчез из кеша в момент изъятия прав

9 май 07, 08:06    [4117372]     Ответить | Цитировать Сообщить модератору
 Re: Using Oracle Stored Procedures in DataWindows  [new]
18-я весна
Member

Откуда: Odessa
Сообщений: 203
Локшин Марк
18-я весна
если на некоторые объекты из запроса забираются права, то запрос сразу удаляется из кеша

А это точно так? По-моему выглядит несколько нелогично. Что из того, что у кого-то отобрали права на часть объектов запроса? Я понимаю, если изменилась схема данных, удалили индекс, но права?

А по-моему логично: нет никакой разницы у вас нет прав на таблицу или таблица не существует - доступ к ней одинаково невозможен.
В Оракле даже ошибка будет и там и там одна и та же : Table or view does not exist.
9 май 07, 08:09    [4117373]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / PowerBuilder Ответить