Low "Parse CPU to Parse Elapsd %"

What could be the reason of low "Parse CPU to Parse Elapsd %"?

Instance Efficiency Percentages (Target 100%)
Buffer Nowait %:              99.97    Redo NoWait %:    100.00
Buffer Hit %:                 99.90    In-memory Sort %: 100.00
Library Hit %:                99.48    Soft Parse %:     98.56
Execute to Parse %:           91.34    Latch Hit %:      99.97
Parse CPU to Parse Elapsd %:  16.97    % Non-Parse CPU:  99.65

Cursor_sharing is EXACT, the application already uses bind variables, and the Library Cache Hit ratio is 99.48%, percentage of soft parses is 98.56%!

Since,

Parse CPU = CPU time spent parsing
Parse Elapsed = Total time spent parsing

Parse Elapsed = CPU time + waiting time.

there is some waiting during parsing. About 83% of the parse time is spent for waiting!
Where could this waiting be coming from?

Maybe from dynamic sampling?
Oracle could be trying to read table or index blocks in order to dynamicly gather approximate statistics for those who has no or stale statistics?

Invalidating the execution plan of an SQL statement

Recently, I noticed that although I gathered statistics on a table, the execution plan wasn't invalidated in Oracle 10g. I googled it and found the solution at Coskan's blog that you can invalidate an execution plan by executing a simple ddl statement on a table, like:

grant select on t1 to user1;

Today I came across this note on metalink [Doc ID: 557661.1] about this topic. It says that prior to 10g, by default the execution plan was invalidated when the statistics was gathered on underlying objects:

Cursor Invalidations on Gathering Statistics prior to Oracle10g
In releases prior to Oracle10g gathering statistics using DBMS_STATS resulted in immediate invalidations of dependent cached cursors, unless NO_INVALIDATE was set to TRUE.

But starting with 10g, by default statistics gathering MAY OR MAY NOT invalidate the execution plan:

Starting with Oracle10g, the DBMS_STATS package offers the AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows the user to specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.
According to the documentation the values NO_INVALIDATE can take are:

TRUE: does not invalidate the dependent cursors
FALSE: invalidates the dependent cursors immediately
AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors

So if you want to be sure that the cursor is invalidated after statistics gathering, use parameter NO_INVALIDATE => FALSE with DBMS_STATS.GATHER_TABLE_STATS and other procedures.

exec dbms_stats.gather_table_stats('USER','EMP', no_invalidate => false);

As for the default AUTO_INVALIDATE option , when Oracle decides him/herself whether to invalidate or not invalidate an execution plan, they give a description of how it works. To be frank, I didn't read it all now, maybe I will read it when I need this info next time. So here is the exceprt from [Doc ID: 557661.1]:

Cursor Invalidations with Oracle10g and AUTO_INVALIDATE
With the AUTO_INVALIDATE option the goal is to spread out the cursor invalidations over a time period long enough for hard-parses not to cause noticeable spikes.

In this way a cached cursor depending on an object whose statistics have been modified by DBMS_STATS will be invalidated as follows:

when DBMS_STATS modifies statistics for an object, all current cached cursors depending on this object are marked for rolling invalidation. Let's call this time T0.

the next time a session parses a cursor marked for rolling invalidation, it sets a timestamp. This timestamp can take a random value up to _optimizer_invalidation_period sec from the time of this parse. The default for this parameter is 18000 sec i.e. 5 hours. Let's call the time of this parse T1 and the timestamp value Tmax. On this (first) parse we reuse the existing cursor i.e. we do not hard-parse and do not use the modified statistics to generate a new plan (it is a soft parse.)

on every subsequent parse of this cursor (which is now marked for rolling invalidation and timestamped) we check whether the current time T2 exceeds the timestamp Tmax. If not, we reuse the existing cursor again, as happened on the first (soft) parse at time T1. If Tmax has been exceeded, we invalidate the cached cursor and create a new version of it (a new child cursor) which uses the new statistics of the object to generate its execution plan. The new child is marked ROLL_INVALID_MISMATCH in V$SQL_SHARED_CURSOR to explain why we could not share the previous child.
From the above descriptions, it follows that:

a cursor which is never parsed again after being marked for rolling invalidation will not be invalidated and may eventually be flushed out of the shared pool if memory becomes scarce
a cursor which is only parsed once after being marked for rolling invalidation will not be invalidated (it will only be timestamped) and again may be eventually flushed out if memory in the shared pool becomes scarce
cursors which are regularly reused will become invalidated on the next parse that happens after the timestamp Tmax has been exceeded
It should be clear that the above method is efficient in that it incurs the overhead of invalidations only for frequently reused cursors.

Exception: parallel SQL are immediately invalidated in order to ensure consistency between execution plans of slaves and Query Coordinator across multiple RAC instances. This is not a problem as parallel SQL are usually heavy and therefore hard-parse resources are insignificant to their total resource usage.

Оптимальный размер для лог буффера (log buffer)

В продолжение темы об ожиданиях log file sync - сегодня опять с ними столкнулась во время нагрузочного тестирования. Знаю, что причиной ожиданий log file sync могут быть:
1) частые коммиты
2) слишком большой log_buffer
3) медленные диски, на которых находятся лог файлы.

Первую и третью причину отбросила, с ними я и так ничего не могу поделать.
Взялась за вторую причину: размер лог буфера оказался равен 45MB. У Бурлесона прочитала советы об оптимальном размере лог буфера:

MetaLink note 216205.1 Database Initialization Parameters for Oracle Applications 11i, recommends a log_buffer size of 10 megabytes for Oracle Applications, a typical online database:

A value of 10MB for the log buffer is a reasonable value for Oracle Applications and it represents a balance between concurrent programs and online users.

The value of log_buffer must be a multiple of redo block size, normally 512 bytes.

10MB? Не много ли? Слышала очень много советов о том, что нет смысла устанавливать его больше 1МБ. Правда ли это?
Бурлесон пишет, что есть увеличение размера лог буфера больше 1МБ реально улучшала прозводительность:

Even though Oracle has traditionally suggested a log_buffer no greater than one meg, I have seen numerous shops where increasing log_buffer beyond one meg greatly improved throughput and relieved undo contention.

На металинке пишут, что нет смысла устанавливать его больше 5МБ:

It has been noted previously that values larger than 5M may not make a difference.

Решила пока уменьшить лог буфер с 45МБ до 10МБ, посмотрю. Если не поможет, попробую уменьшить до 5МБ.

Enterprise Manager Java Console в Oracle10g

Давно искала Enterprise Manager Java Console для Oracle 10g, вначале вообще думала, что джава консоль, к которому все привыкли с предыдущих версий, заменили на Enterprise Manager Database Console для управление одной базой, и Enterprise Manager Grid Control для централизованного управления многими базами и что джава консоля в 10ке НЕТ.

Вчера была приятно удивлена, когда на неизвестном компе случайно увидела джава консоль Enterprise Manager'a 10ой версии!

Он оказывается устанавливается в опции Administrator в стандартном пакете установки. В документации Oracle пишут:

In addition to using Oracle Enterprise Manager Database Control or Grid Control to manage an Oracle Database 10g database, you can also use the Oracle Enterprise Manager Java Console to manage databases from this release or previous releases. The Java Console is installed by the Administrator installation type.

При установке клиента Oracle:
Теперь у меня есть джава консоль 10ки! (до этого при установке 10го клиента специально оставила 9го клиента, чтоб только пользоваться его джава консолью!)

Вопросы о чекпоинтах (Questions about checkpoints)

На прошлой неделе на очередном тренинге для коллег-дба, я подготовила вопросы о чекпоинтах.
Вот несколько из них:

10. Какое из следующих утверждений НЕверно о различиях между полным и инкрементальным чекпоинтами?
a) инкрементальный чекпоинт выполняется намного чаще полного
b) полный чекпоинт сбрасывает все грязные блоки, а инкрементальный – только часть
c) полный чекпоинт обновляет заголовки всех online файлов данных, инкрементальный - заголовки файлов данных, в которых произошли изменения
d) полный чекпоинт обычно вызывается переключением лог файлов, а инкрементальный – увеличением кол-ва грязных блоков и превышением их порогового значения

Ответ: с

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

Количество ожиданий в Oracle 10g (Number of Wait Events in Oracle 10g)

Сегодня читала про ожидания в Oracle 10g и наткнулась на эту презентацию, где увидела этот график:
Я знала, что в Oracle 10g появилось очень много новых событий ожидания (wait events) но даже не думала, что настоолько много! Первым делом решила проверить это и посчитала кол-во событий ожидания в версиях 9i и 10g:

В Oracle 9.2.0.8:

SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       406

В Oracle 10.2.0.4:

SQL> select count(*) from v$event_name;

  COUNT(*)
----------
       889

В 10g кол-во ожиданий увеличилось больше чем вдвое: было 406, стало 889! Здесь пишут, что события ожидания в Oracle 10g стали более "descriptive", то есть более описательными, более детальными.

Wait event names in Oracle 10g are more descriptive in the areas of latches, enqueues, and buffer busy waits.

Здесь я писала об одном из таких примеров, когда из ожидания buffer busy waits "родилось и отколось" ожидание read by other session.

Размер redo блоков (Redo log block size)

Сегодня хотела бы написать об размере блоков redo log buffer'а. Все мы знаем, о размере блока базы данных, который устанавливается параметром db_block_size, и который задает размер блоков в файлах данных и размер буфферов в буфферном кеше.

А какая структура у redo log buffer'а? Понятно, что она - цикличная, запись в него выполняется последовательно, не то что в буфферном кеше или в файлах данных, когда чтение и запись выполняется вразброс.

Мне всегда казалось, что если она цикличная и запись в него последовательная, то и думать тут не о чем: значит у него и структура памяти какая-то неразрывная, что ли.

Сегодня, когда пыталась понять смысл латча redo allocation, не могла понять, зачем вообще нужен этот латч... Что тут выделять-то? Память под redo log buffer уже выделена же в SGA. У Стива Адамса прочитала следующее:

The redo allocation latch must be taken to allocate space in the log buffer. This latch protects the SGA variables that are used to track which log buffer blocks are used and free.

Вот так я узнала, что redo log buffer состоит из блоков одинакового размера, а его цикличность - это структура данных, а в памяти они могут находится вразброс.

А теперь, собственно, про размер блоков redo log buffer'а. Стив Адамс пишет здесь:

Although the size of redo entries is measured in bytes, LGWR writes the redo to the log files on disk in blocks. The size of redo log blocks is fixed in the Oracle source code and is operating system specific.

Перевод: Хотя размер redo измеряется в байтах, LGWR пишет red в лог файлы на дисках в блоках. Размер redo блоков зашит в код ядра Oracle и зависит от операционной системы.

То есть размер redo блоков невозможно изменить параметром инициализации как размер блоков данных db_block_size.

Ниже, он приводит размеры redo блоков в разных ОС:

Log Block Size    Operating Systems
512 bytes         Solaris, Windows, UnixWare
1024 bytes        HP-UX, Tru64 Unix
2048 bytes        SCO Unix, Reliant Unix
4096 bytes        MVS, MPE/ix

А так же, фактический размер redo блоков можно узнать следующим способом:

SQL> select max(lebsz) from sys.x$kccle;

MAX(LEBSZ)
----------
       512