Вопрос о базах и кеше.

[zVlad]

I could find something in IBM problem data base regarding contention and Dynamic SQL (I put here whole cover letter for not only to show problem itself but also how IBM problem cover letter looks like. With monospace font it looks better). It was fixed in 1998:

APAR Identifier ...... PQ14893 Last Changed..02/05/14
INTERNAL LATCH SUSPEND TIMES ARE HIGH WHEN USING VERY HIGH
THROUGHPUT OF DYNAMIC SQL AND USING DYNAMIC STATEMENT CACHING

Symptom ...... PF PERFM Status ........... CLOSED PER
Severity ................... 4 Date Closed ......... 98/08/28
Component .......... 5740XYR00 Duplicate of ........
Reported Release ......... 510 Fixed Release ............ 999
Component Name 5740 IBM DATABA Special Notice
Current Target Date ..98/07/07 Flags
SCP ...................
Platform ............

Status Detail: SHIPMENT - Packaged solution is available for
shipment.

PE PTF List:

PTF List:
Release 510 : UQ21098 available 98/09/18 (F809 )


Parent APAR:
Child APAR list:


ERROR DESCRIPTION:
When running many concurrent threads, each submitting large
numbers of PREPARE statements, DB2 throughput is reduced by
internal latch contention. Reports show a high percentage of
DB2 class 2 time being spent in latch suspension. This was
seen when using dynamic SQL caching, but can also occur when
that feature is not used.

Additional keywords: OEM PeopleSoft hung thread loop excessive
cpu cycles in DBP1MSTR DSNXEUF0 DSNIDALC DSNXEUFP DSNSPOWN +300
DSNXPDAL PIPE MANAGER



LOCAL FIX:


PROBLEM SUMMARY:
****************************************************************
* USERS AFFECTED: DB2 Subsystems running with the Dynamic *
* Statement Cache feature (CACHEDYN=Y) and *
* have a high throughput of concurrent *
* dynamic SQL statements. *
****************************************************************
* PROBLEM DESCRIPTION: Performance gets gradually worse *
* over time for dynamic PREPARE *
* statements while the DB2 subsystem *
* is active. Throughput of *
* concurrent dynamic SQL PREPARE *
* requests is also reduced over time. *
****************************************************************
* RECOMMENDATION: *
****************************************************************
Several customers have reported performance problems when
running with dynamic statement caching in an environment where
there is a large volume of concurrent dynamic SQL.
Some of these problems are more likely to occur when there are
lower hit ratios in the dynamic SQL cache. Investigation has
turned up the following bottlenecks that contributed to this
slowdown:

1. All DB2 threads that retrieved statements from the dynamic
SQL statement cache kept their working copies in a shared
storage pool. This was a source of latch contention as these
threads attempted to get and free storage from that pool.
This appeared as contention in latch class 32.

2. Another shared storage pool - the RDS OP POOL experienced
high rates of contention when many concurrent prepares were
executed. This appeared as contention in latch class 32.

3. DB2 hash tables for tracking cached SQL statements and their
object dependencies experienced severe latch contention when
many concurrent PREPARE requests are processed. This appeared
as contention in latch classes 25 and 26.

4. An error in the logic for managing object dependencies caused
some storage to be acquired and never freed or reused. In a
storage dump, these blocks can be identified by the eyecatcher
"DMHE"

5. Also in the object dependency recording logic, some of the
code was found to be very inefficient, causing many CPU cycles
to be used.



PROBLEM CONCLUSION:
To address the performance problems listed in the APAR summary,
the following changes have been made to DB2 code:

1.)The working copies of cached SQL statements are no longer
stored in a shared pool. They have been moved to a thread-
based storage pool, eliminating storage pool latch contention.

2.)When preparing a dynamic SQL statement for insertion into
the cache, the RDS OP pool is no longer used. That storage is
acquired in a thread-based pool that lasts only for the
duration of the prepare request.

3.)Several changes were made to alleviate latch contention on
the statement and object hash tables:
a.)Logic was changed so that the latch used on the object
dependency tables is more granular and acquired less
frequently.
b.)The size of the statement hash table was increased
c.)The key generation logic for the statement hash table was
changed for better distribution.

4.)Code was changed so that storage is always freed or reused
correctly for the object dependency tables.

5.)Object dependency recording code has been restructured to
eliminate inefficiencies.

[hren]

Я не совсем понимаю кое-что. Должен сразу сказать, что DB2 не знаю совсем, поэтому не удивляйтесь, если мои вопросы не совсем в тему.

Во-первых, мы говорим о кэшировании любых SQL или только динамических? Динамический SQL поддерживается Oracle несколькими способами, но мне кажется, что никаких специальных механизмов кэширования при разборе и исполнении именно динамических выражений не предусмотрено, они обрабатываются на общих основаниях. Впрочем, я могу ошибаться. Приведенная Вами цитата из документации IBM относится к довольно специфической для Oracle вещи, которая на практике используется довольно редко - именно к особенностям разбора выражений при выполнении prepare. Я никогда не видел вживую OLTP приложения на Oracle, которое всерьез занималось бы генерацией динамических запросов, как правило, используется статика. В DB2 ситуация иная?

Во-вторых, наличие contention в общий областях памяти, иcпользуемых для кэширования SQL, является прямым следствием конечности мира . Я не могу себе представить систему, использующую буферную память фиксированного размера, которая была бы полностью лишена этой проблемы. Конечно, если приложение написано хорошо, коэффициент переиспользования SQL высок, соответственно hit ratio в shared pool правильного размера (как я понимаю, это понятие есть и в DB2) также высок и contention низок. Если переиспользование мало, а приложение велико, работает много пользователей и их активность разнообразна, то при любых разумных размеров буфера наступает момент, когда в нем не остается свободной памяти, а hit ratio по прежнему низок. В этот момент и начинается contention - для того, чтобы закэшировать новый запрос система выбрасывает из кэша старые. Чем больше размер пула, тем сложнее системе обслуживать конкурентные запросы при отсутствии свободных ресурсов - начинается latch contention и wait time тем больше, чем больше памяти выделено под буфер. Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью. Или в DB2 не бывает "плохих приложений"? Конечно, можно отключить кэширование (я подозреваю, что сама возможность в DB2 предусмотрена именно для таких ситуаций). В Oracle, по моему, так просто его не отключишь, тем более, что некоторые запросы все-равно полезно кэшировать (например, стандартные системные). Приходится играться с размерами буферов и более тонкими настройками.

Еще замечу, что на эффективность работу SQL-кэша сильно влияют и другие факторы. Классический пример в Oracle - статические SQL statements не опознаются как одинаковые, если содержат разное количество пробелов, или символы в различном регистре (несмотря на то, что семантически разницы нет, поскольку case insensitive). Для борьбы с этой проблемой есть даже специальный параметр, который меняет режимы разбора, байндинга переменных, а также позволяет игнорировать некоторые различия (режим similar в Oracle 9). Как все эти вещи обходятся в DB2, если там такая благодать с кэшированием запросов?

[Dmitry67]

Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью.

Можно бороться также как и MS SQL
То есть хотя запросов плохая аппликация может выдать огромное количество
но вследствие конечности мира и аппликации количество ТИПОВ запросов которое аппликация выдает довольно ограничено
Поэтому надо просто пытаться параметризовать запросы

К сожалению этот механизм у Oracle сделан из рук вон плохо
Не говоря о пробелах, в случае серии запросов

select * from X where A=1 and B=44 and C='33
select * from X where A=2 and B=44 and C='33
select * from X where A=3 and B=44 and C='33
...
select * from X where A=1000 and B=44 and C='33

он в зависимости от настроек либо запишет в къш тупо все 1000 запросов, либо сразу параметризирует ВСЕ ТРИ параметра A= B= C= (а зря ! - поэтому этим параметром и не рекомендуют пользоваться)

MS SQL догадается что параметризировать надо только A

[vc]

Мне непонятно, как можно избежать подобных эффектов с "плохими приложениями" при включенном кэшировании полностью.

К сожалению этот механизм у Oracle сделан из рук вон плохо
Не говоря о пробелах, в случае серии запросов

select * from X where A=1 and B=44 and C='33
select * from X where A=2 and B=44 and C='33
select * from X where A=3 and B=44 and C='33
...
select * from X where A=1000 and B=44 and C='33

он в зависимости от настроек либо запишет в къш тупо все 1000 запросов, либо сразу параметризирует ВСЕ ТРИ параметра A= B= C= (а зря ! - поэтому этим параметром и не рекомендуют пользоваться) ...

Well, it's not true.

Oracle has _two_ modes to make SQL statements with literals shareable:

1. cursor_sharing=force will autobind every constant (as described above).
2. cursor_sharing=similar will detect when substituting a bind variable may change the plan substantially and will generate multiple plans based on the bind variable value.

Both (1) and (2) are, of course, a crutch for a poorly written application.

Rgds.

[hren]

Я же говорю о "плохих приложениях". Хорошее, конечно, будет использовать в нужных местах bind variables и придерживаться стандартного форматирования запросов (что, кстати, легко автоматизируется при разработке). Насчет того, как именно Oracle параметризует константы в приведенном Вами примере при разных значениях параметра cursor_sharing, я не буду высказываться не знаю, надо попробовать. Тем более, что поведение оптимизатора вообще и в зависимости от значенияэтого параметра в частности меняется и очень сильно от версии к версии.

[Dmitry67]

Не знал
Видимо это новое
Я знал только про cursor_sharing=force и none

[hren]

Well, it's not true.

Oracle has _two_ modes to make SQL statements with literals shareable:

Справедливости ради, similar появился только в девятке.

[zVlad]

To Hren. I'am afraid I wouldn't be able to respond to all your questions in one shoot. I'll do it in few steps. OK?

By now, I want to clarify what Static and Dynamic SQL mean in Oracle and DB2. I wouldn't surprise they are different.

In DB2, Static SQL mean - when you compile your program, all SQLs are extracted from source and put into DBRM - Database Request Module. Then, with DB2 (non-SQL) command BIND we bind DBRM into package or plan (long time ago only plan was available). Package has access path for each single SQL, and until next BIND (or REBIND), DB2 never change generated? during binding, access path, just run it. This is Static. Staticaly bound applications never use PREPARE to run SQLs! There is EDM pool where static SQLs are cached.
In contrast, Dynamic SQL is preparing every time they come up. But, if you set up Dynamic SQL Cache, DB2 is tring to search if this SQL was already PREPARed? If yes DB2 will use access path from prepared before rather then do it again. DB2 has intelect like what Dmitry67 told about.
Of course, if we have small Cache DB2 will have to choose somebody to sweep out in order to accomodate new request. There is algorithm of doing that job.

What about Oracle?

Hren also wrote: "Я никогда не видел вживую OLTP приложения на Oracle, которое всерьез занималось бы генерацией динамических запросов, как правило, используется статика. В DB2 ситуация иная?"

PeopleSoft is an example of Dynamic Application. But usually, truly DB2 applications are static (in terms I mentioned above).

"Классический пример в Oracle - статические SQL statements не опознаются как одинаковые, если содержат разное количество пробелов, или символы в различном регистре (несмотря на то, что семантически разницы нет, поскольку case insensitive).... " you see, it is impossible for DB2 Static applications. I bet "Oracle static" and "DB2 static" are two different worlds. What about dynamic?

"... Для борьбы с этой проблемой есть даже специальный параметр, который меняет режимы разбора, байндинга переменных, а также позволяет игнорировать некоторые различия (режим similar в Oracle 9). Как все эти вещи обходятся в DB2, если там такая благодать с кэшированием запросов?"

Again, it is two different wolds (I guess). For static, DB2 has no such problem at all, and there is no special parametrs there. For Dynamic you might want or might not want to use cache - depence on nature of application you run. In my shop we used to use totally static application (with small amount of dynamic requests, mostly for maintanace and stuff like that), Few months ago, application was implemented to run Dynamic queries. This application has given number of SQLs, say 100. Monitor shows (see attached, "SQL Cache" - Dynamic SQL chace). Hit ration is very high 94.6% for static hit ration = 100%.

I was not able to attach. Privet says: "txt extansion is not allowed"

I put it here. It must be seen in monospace font:

% FOUND
# PAGES % PAGES REQUESTS LOADS IN POOL
------- ------- -------- ------- -------
FREE PAGES 1,260 8.4
SKCT'S 13 0.1
SKPKG'S 13,209 88.1
CT'S 13 0.1 2,973K 36 100.0
DBD'S 409 2.7 5,409K 98 100.0
PKG'S 96 0.6 100M 21,017 100.0

TOTAL 15,000 109M 21,151 100.0

SQL CACHE 2,500 1,099K 56,025 94.9

DSPACE-USED 2,500
DSPACE-FREE 0 0.0

[Dmitry67]

А вот как Вы объясните два отстойные вещи в Oracle

1. case insensitive. В век unicode... странное это
В MS SQL collation настраивается на уровне поля (!!!)

2. Вроде в Oracle пустая строка есть NULL

[vc]

А вот как Вы объясните два отстойные вещи в Oracle

1. case insensitive. В век unicode... странное это
В MS SQL collation настраивается на уровне поля (!!!)

Please elaborate. It's not clear what you mean.

2. Вроде в Oracle пустая строка есть NULL

This is true. Here (as elsewhere), Oracle does not conform to SQL-92.

Rgds.

[hren]

2Dmitry67

1. Вообще-то речь о ключевых словах и именах. Я почему-то думал, что ANSI SQL в этом смысле case insensitive по стандарту. Никогда не приходило в голову узнать, так ли это. В том, что касается литералов с case sensitivity в Oracle все в порядке. И настроить можно.

2. Никак нет, не есть Пустая строка - это пустая строка, а NULL - это NULL. Или я не совсем понял, что Вы имеете в виду?

Чуть позже. А понял. Я просто не привык думать на уровне SQL-а, я не разработчик. Да, в если делать инсерт в таблицу значения '', то вставится NULL. И при сравнениях они идентичны.

[Dmitry67]

Чуть позже. А понял. Я просто не привык думать на уровне SQL-а, я не разработчик. Да, в если делать инсерт в таблицу значения '', то вставится NULL. И при сравнениях они идентичны.

Как это ?
Это неправильно

''='' is NULL потому что лубое сравнение с NULL есть ПО ОПРЕДЕЛЕНИЮ NULL

[hren]

2zVlad: действительно, разница в понятиях "статический" и "динамический" SQL в DB2 и Oracle есть. Я, к сожалению, уезжаю сейчас, смогу ответить подробно только в воскресенье.

[hren]

''='' is NULL потому что лубое сравнение с NULL есть ПО ОПРЕДЕЛЕНИЮ NULL

Ничего не понял В Oracle в тексте запроса можно писать '', но вставить в символьное поле такое значение невозможно, вставится NULL. Поэтому сравнивать с '' нельзя - ошибки не будет, но и результат будет неверный, в таблице - то NULL. Надо всегда писать в сравнениях IS NULL или IS NOT NULL, поскольку '' всегда интерпретируется как NULL, а сравнивать с NULL с помощью = нельзя. Кстати, вроде Oracle уже давно обещал это дело поменять на стандарт, может в какой-нибудь из последних версий это уже и не так. Я под Oracle пишу только административные скрипты, поэтому у меня не самая точна яинформация об особенностях программирования под ним.

[zVlad]

Чуть позже. А понял. Я просто не привык думать на уровне SQL-а, я не разработчик. Да, в если делать инсерт в таблицу значения '', то вставится NULL. И при сравнениях они идентичны.

Как это ?
Это неправильно

''='' is NULL потому что лубое сравнение с NULL есть ПО ОПРЕДЕЛЕНИЮ NULL

I'm not with what are you guys talking about. Dmitry67, can you evaluate this:

CREATE TABLE,NUMBERS,(NUM VARCHAR(8));
INSERT INTO NUMBERS VALUES ('');
INSERT INTO NUMBERS VALUES (' ');
INSERT INTO NUMBERS VALUES (' ');

SELECT NUM, LENGTH(NUM) FROM NUMBERS;
---------+---------+---------+---------+-------
NUM
---------+---------+---------+---------+-------
0
1
4
DSNE610I NUMBER OF ROWS DISPLAYED IS 3

SELECT * FROM NUMBERS WHERE NUM IS NULL;
---------+---------+---------+---------+---------+------
NUM
---------+---------+---------+---------+---------+------
DSNE610I NUMBER OF ROWS DISPLAYED IS 0