Блог администратора баз данных Oracle

Low "Parse CPU to Parse Elapsd %"

2009-07-17T03:57:00.000-07:00

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

2009-04-02T04:31:00.000-07:00

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)

2009-03-31T05:44:00.000-07:00

В продолжение темы об ожиданиях 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

2009-03-20T01:54:00.000-07:00

Давно искала 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)

2009-03-02T03:10:00.000-08:00

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

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

Ответ: с

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

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

2009-01-30T03:08:00.000-08:00

Сегодня читала про ожидания в 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)

2009-01-29T04:56:00.000-08:00

Сегодня хотела бы написать об размере блоков 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

Ожидания log file sync

2009-01-28T05:45:00.000-08:00

Сессия, ожидает события log file sync, в то время как LGWR сбрасывает redo-информации из redo log buffer в redo log файлы.

Подробней: скажем какая-то сессия апдейтит какую-то таблицу. То есть выполняя апдейт, она генерит redo-информацию и записывает их в redo log buffer. В тот самый момент, когда сессия пишет redo-информацию в redo log buffer, LGWR сидит и ждет. Как только сессия коммитит транзакцию, LGWRу нужно сбросить redo-информацию из redo log buffer в redo log файлы и выдать сессии подверждение, что транзакция закоммичена. И пока LGWR сбрасывает redo-информацию из redo log buffer в redo log файлы, сессия ожидает события log file sync.

Мой коллега говорит, что это ожидание возникает только при commit'е сессии. Том Кайт тоже здесь пишет, что это ожидание возникает только при коммите:

log file sync - это клиентское ожидание события. Именно этого события ваши клиенты ждут, когда говорят "commit". Это ожидание, пока процесс LGWR фактически запишет их данные повторного выполнения на диск и фиксация транзакции будет завершена. Можно "настроить" этот процесс, ускорив работу процесса lgwr (отказавшись от использования raid 5, например) и фиксируя транзакции реже, генерируя меньше данных повторного выполнения (множественные изменения генерируют меньше данных повторного выполнения, чем построчные)

А как же сброс redo-информации из redo log buffer в redo-файлы по истечению 3 секунд, при заполнении redo log buffer'а на 1/3 и тд?

Если кто-то знает точный ответ на этот вопрос, напишите здесь.

"buffer busy waits" и "read by other session"

2009-01-21T04:21:00.000-08:00

В Oracle 10g появились ожидания read by other session, которые являются частным и "отколовшимся" случаем ожиданий buffer busy waits. Вот что пишут в документации Oracle о них:

This event ['read by other session'] occurs when a session requests a buffer that is currently being read into the buffer cache by another session. Prior to release 10.1, waits for this event ['read by other session'] were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event.

Наталья Гусева, одна из суперских ДБА, которых я знаю, подсказала, что read by other session - это тот же buffer busy waits с reason code = 130.

Reason code = 130: Block is being read by another session, and no other suitable block image was found, so we wait until the read is completed. This may also occur after a buffer cache assumed deadlock. The kernel can't get a buffer in a certain amount of time and assumes a deadlock. Therefore it will read the CR version of the block.

Полный список и описание reason code можно почитать здесь, правда там говорится применительно версии 9i.

Ожидания read by other session возникают, когда какая-то сессия пытается прочитать блок из диска в буферный кеш, в тот момент когда другая сессия уже читает ее из диска в буферный кеш.

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

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

Новый шаблон в новом году

2009-01-16T05:31:00.000-08:00

Сегодня обновила шаблон блога на новый, захотелось сделать очередное "что-то новое" в новом году.

Новый шаблон очень необычный, по крайней мере для блога по Ораклу, но мне нравится.

Я его скачала на этом сайте - они раздают бесплатные шаблоны для блогов Blogspot.com, видимо, зарабатывают на рекламе.

FGA в 9i и 10g

2008-12-23T01:03:00.000-08:00

Оказывается в Oracle 9i FGA позволяет аудитить только SELECTы, а в 10g появилась возможность аудитить и INSERT, UPDATE, DELETE.
Здесь пишут как настраивать этот параметр:

Under Oracle 9i Database, this policy could only audit SELECT statements. In Oracle Database 10g, however, you can extend it to include INSERT, UPDATE, and DELETE as well. You would do so by specifying a new parameter:

statement_types => 'INSERT, UPDATE, DELETE, SELECT'


begin
 dbms_fga.add_policy(object_schema   => 'SCOTT',
                     object_name     => 'DEPT',
                     policy_name     => 'DEPT_AUDIT',
                     audit_column    => 'DNAME',
                     statement_types => 'INSERT, UPDATE',
                     audit_trail     => DBMS_FGA.DB_EXTENDED);
end;

Сайт об оптимизации производительности БД Oracle

2008-11-26T01:48:00.000-08:00

Сегодня наткнулась на сайт http://www.novoelba.ru об оптимизации производительности БД Oracle.

Авторы сайта предлагают соптимизировать производительность любых "безнадежных" баз данных Oracle:

"Мы поможем Вам ускорить «безнадёжные» отчеты, даже если Вам, возможно, уже сообщили, что ускорить их невозможно без покупки нового дорогого оборудования."

Прикольно, кажется впервые в Рунете вижу службу, которая предлагает такую услугу. Чем-то напоминает консалтинг от Бурлесона http://www.dba-oracle.com.

Как узнать старый пароль дблинка (если он был изменен недавно)?

2008-09-16T05:34:00.000-07:00

Как вы думаете, как можно узнать старый пароль дблинка (database link), который был изменен совсем недавно?
Один мой коллега (НЕ ДБА!) предложил суперский вариант, как это можно сделать: посмотреть флешбеком (flashback) состояние системной таблицы, в которой хранится информация о дблинках.
Смотрим, к каким системным таблицам обращается вьюшка dba_db_links:

create or replace view dba_db_links
(owner, db_link, username, host, created)
as
select u.name, l.name, l.userid, l.host, l.ctime
from sys.link$ l, sys.user$ u
where l.owner# = u.user#

Дальше, флешбеком смотрим состояние системной таблицы sys.link$ на час назад:

SQL> select name, userid, password
     from sys.link$ as of timestamp(sysdate-1/24)
     where name ='MYDBLINK';

NAME                 USERID     PASSWORD
-------------------- ---------- ----------
MYDBLINK             USERNAME   OLDPASSWD

OLDPASSWD - это наш старый пароль. Чтоб убедиться, что мы нашли то, что нужно посмотрим текущий пароль:

SQL> select name, userid, password
     from sys.link$ as of timestamp(sysdate)
     where name ='KI_ICR002';

NAME                 USERID     PASSWORD
-------------------- ---------- ----------
MYDBLINK             USERNAME   NEWPASSWD

Вот так вот нашли только что измененный пароль. А я уже хотела логмайнером (logminer) порыться в реду-логах (redo). Правда, описанный выше метод сработает только если изменения были сделаны совсем недавно, то есть в пределах undo_retention (не всегда гарантированно).

Flush buffer cache

2008-09-01T01:22:00.000-07:00

Эта команда используется и в Oracle 9i и в 10g для сброса разделяемого пула.

alter system flush shared_pool

В Oracle 10g есть аналогичная команда, сбрасывающая буферный кеш:

alter system flush buffer_cache;

Только было бы замечательно уточнить, что именно происходит при сбросе буферного кеша: во-первых, записываются измененные блоки в файлы данных. Может вся хеш-таблица буферного кеша тоже чистится?

В Oracle 9i такой команды нет, но, оказывается можно сбросить буферный кеш установкой события:

alter session set events = 'immediate trace name flush_cache';

Тюнинг базы на Oracle 10g

2008-08-29T01:41:00.000-07:00

Давно не писала в блоге, сегодня решила написать хоть что-нить. Раньше всегда пыталась писать в блоге чисто с технической точки зрения, не включая мое личное отношение к разным фичерам и особенностям Оракла. Мне кажется в последующих моих постингах будет больше "меня".

Недавно оптимизировала базу на Oracle 10.2.0.3, которая используется для CRM-системы. Провели нагрузочное тестирование, по результатам которого выяснили, что на 300-400 одновременных пользователях активно выполняющих те или иные операции, база начинает виснуть. Удалось поднять производительность более чем в 4 раза.

Если честно, очень понравилось оптимизировать базу на 10ке: даже самые незначительные новые особенности облегчают работу по оптимизации. Например, динамические представления V$SESSION и V$SQL значительно расширены и содержат много полезной и необходимой информации.

В Oracle 10g в V$SESSION можно посмотреть ожидания каждой сессии, не связывая его с V$SESSION_WAIT. То есть V$SESSION содержит все столбцы V$SESSION_WAIT:

SQL> set pagesize 100
SQL> column event# format 999
SQL> column wait_class format A15
SQL> column event format A30
SQL> select event#, event, wait_class, state
  2  from v$session where username is not null;

EVENT# EVENT                          WAIT_CLASS      STATE
------ ------------------------------ --------------- -------------------
   256 SQL*Net message from client    Idle            WAITING
    30 Backup: sbtwrite2              Administrative  WAITING
   256 SQL*Net message from client    Idle            WAITING
   256 SQL*Net message from client    Idle            WAITING
   252 SQL*Net message to client      Network         WAITED SHORT TIME

SQL>

Ожидания при чтении и записи LOBов

2008-08-05T01:37:00.000-07:00

Вчера я пыталась создать копию таблицы, у которой был столбец типа BLOB. Заливка данных повисла, и я оставила ее висеть до утра :D К тому же это было не к спеху. Таблица была средненькая: около 700 тыс. записей, весит 2 гига. Знала, что это из-за LOBа так висит, и сегодня решила докапаться до истины. И так все по-порядку.

Сперва пыталась создать копию таблицы обычным "create table ... as select", думала средненькая таблица скопируется быстро, но не тут-то было. Висела больше часа, пока не кильнула сессию. С опцией nologging тоже самое, через несколько часов и ее убила:

create table new_blob_table as select * from orig_blob_table;

create table new_blob_table nologging as select * from orig_blob_table;

А direct path insert командой insert /*+ append */ тоже висела пару часов, и я, добрая, оставила ее висеть до утра:

create table new_blob_table as select * from orig_blob_table where 1=2;

insert /*+ append */ into new_blob_table select * from orig_blob_table;

Во время всех этих попыток, сессия ожидала события direct path write (lob):

SQL> select event
     from v$session_wait
     where sid=969;

EVENT
-------------------------
direct path write (lob)

При прямой записи - direct path write - запись происходит В ОБХОД буферного кеша прямо в файлы данных. Запись выполняется серверным процессом (а не процессом DBWR, как в обычной записи), который в pga сессии подготавливает блоки и записывать их за отметкой HWM.

Бурлесон перечисляет список операций, которые могут выполнять операцию прямой записи (direct path write) и вызывать соответствущие ожидания:

Operations that could perform direct path writes include when
1) a sort goes to disk,
2) during parallel DML operations,
3) direct-path inserts,
4) parallel create table as select, and
5) some LOB operations.

Наш случай - последний, связанный с LOBами. Вот что пишут в документации Oracle о прямой записи (direct path write) и параметре CACHE/NOCACHE LOB-сегментов:

With the CACHE option, LOB data reads show up as wait event 'db file sequential read', writes are performed by the DBWR process.

With the NOCACHE option, LOB data reads/writes show up as wait events:
direct path read (lob)/direct path write (lob).

Corresponding statistics are:
physical reads direct (lob) and physical writes direct (lob).

О параметре CACHE BLOB-сегментов, я писала недавно здесь. Получается, что когда я создавала таблицу командой create table ... as select ..., LOB-сегмент таблицы создались с параметром NOCACHE, который и ведет к ожиданиям direct path write (lob).

SQL> select table_name, column_name, cache
     from dba_lobs
     where table_name='NEW_BLOB_TABLE';

TABLE_NAME           COLUMN_NAME          CACHE
-------------------- -------------------- --------
NEW_BLOB_TABLE       BLOB_COLUMN          NO

То есть новую таблицу нужно либо изначально создать с параметром CACHE LOB-сегмента:

create table new_blob_table
lob (blob_column) store as (cache)
as select * from orig_blob_table where 1=2;

или после создания пустой таблицы (where 1=2), изменить параметр CACHE LOB-сегмента:

alter table new_blob_table modify lob (request_content) (cache);

Теперь, заливка длится чуть больше 1 минуты (!!!):

SQL> create table new_blob_table
     lob (blob_column) store as (cache)
     as select * from orig_blob_table where 1=2;

Table created.

SQL> set timing on
SQL> insert /*+ append */ into new_blob_table select * from orig_blob_table;

642966 rows created.

Elapsed: 00:01:12.83

Даже обычный инсерт отработал всего 4 минуты!

SQL> insert into new_blob_table select * from orig_blob_table;

642966 rows created.

Elapsed: 00:04:18.85

Странно, что если создавать НЕпустую таблицу командой create table ... as select ... с параметром CACHE LOB-сегмента, он все равно зависает с ожиданиями direct path write (lob):

create table new_blob_table
lob (blob_column) store as (cache)
as select * from orig_blob_table;

Теперь, при заливке командой insert /*+ append */, ожидания записи direct path write (lob) исчезли, есть коротенькие ожидания, но это уже ожидания чтения db file sequential read и db file scattered read. А статистика physical writes direct (lob) заменилась на physical writes direct.

Вот. Получается, что с insert /*+ append */ происходит прямая запись, но видимо эта запись уже отличается прямой записи БЛОБов. Если у кого-то есть более подробное описание всего этого, буду рада послушать.

Партиционирование существующей таблицы

2008-08-01T07:11:00.000-07:00

Как лучше всего партиционировать существующую таблицу, которая уже содержит большой объем данных?

1) Создать новую партиционированную таблицу и залить данные из старой
CREATE TABLE ... PARTITION BY (RANGE)...
INSERT INTO ... SELECT * FROM not_partitioned_table;
или
INSERT /*+ append */INTO ... SELECT * FROM not_partitioned_table;

2) Создать новую партиционированную таблицу из старой таблицы
CREATE TABLE partitioned_table
PARTITION BY (RANGE) ...
AS SELECT * FROM not_partitioned_table;

3) С помощью EXCHANGE PARTITION
ALTER TABLE partitioned_table
EXCHANGE PARTITION calls_01012008
WITH TABLE not_partitioned_table;

4) С помощью пакета DBMS_REDEFITION

Во всех вышеперечисленных вариантах партиционирования существующей таблицы для активной системы по любому понадобится даунтайм таблицы, кроме 4 пункта(использование DBMS_REDEFINITION).

Теперь, попробую более подробно описать каждый из вариантов.
Скажем, у нас есть партиционированная таблица NOT_PARTITIONED_TABLE, в которой хранятся данные о звонках.

SQL> create table not_partitioned_table (
  id number,
  dialed_number varchar2(100),
  call_date date
);

Table created.

SQL> --- заполним таблицу звонковыми данными: 1 миллион записей, маловато, но пойдет
SQL> declare
     start_date date;
begin
     start_date:=to_date('01.01.2008','DD.MM.YYYY');
     for i in 1..1000000 loop
         insert into not_partitioned_table values
                      (i, '1234567890', start_date + (i-1)/24/60/10);
     end loop;
     commit;
end;
/

PL/SQL procedure successfully completed.

В итоге, у нас есть непартиционированная таблица NOT_PARTITIONED_TABLE с 1 миллионом записей о звонках с 1 по 7 января 2008 года:

SQL> select trunc(call_date), count(*)
     from not_partitioned_table
     group by trunc(call_date);

TRUNC(CAL   COUNT(*)
--------- ----------
01-JAN-08     144000
02-JAN-08     144000
03-JAN-08     144000
04-JAN-08     144000
05-JAN-08     144000
06-JAN-08     144000
07-JAN-08     136000

7 rows selected.

Наша цель: партиционировать эту таблицу с наименьшим даунтаймом таблицы.

Вариант 1: Создать новую партиционированную таблицу и залить данные из старой

План действий такой:
- Создаем новую партиционированную таблицу
- Можно заблокировать непартиционированной таблицу, чтоб никто не смог изменить данные во время заливки
- Заливаем данные из непартиционированной таблицу
- Удаляем непартиционированную таблицу
- Переименовываем новую партиционированную таблицу

SQL> create table partitioned_table (
       id number,
       dialed_number varchar2(100),
       call_date date)
     partition by range (call_date)
     (
     partition calls_01012008 values less than (to_date('02.01.2008','DD.MM.YYYY')),
     partition calls_02012008 values less than (to_date('03.01.2008','DD.MM.YYYY')),
     partition calls_03012008 values less than (to_date('04.01.2008','DD.MM.YYYY')),
     partition calls_04012008 values less than (to_date('05.01.2008','DD.MM.YYYY')),
     partition calls_05012008 values less than (to_date('06.01.2008','DD.MM.YYYY')),
     partition calls_06012008 values less than (to_date('07.01.2008','DD.MM.YYYY')),
     partition calls_maxvalue values less than (maxvalue)
     );
Table created.

Перед заливкой заблокируем таблицу в режиме EXCLUSIVE командой LOCK TABLE, чтоб пока мы заливаем данные, никто не смог изменить данные в них или добавить новые.

SQL> set timing on
SQL> set autotrace on statistics
SQL> lock table not_partitioned_table in exclusive mode;

Table(s) Locked.

Elapsed: 00:00:00.07

Теперь зальем данные командой INSERT. Сперва посмотрим заливку обычным INSERTом:

SQL> insert into partitioned_table select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:07:53.31

Statistics
----------------------------------------------------------
       5666  recursive calls
      42061  db block gets
      11930  consistent gets
       4035  physical reads
   35909076  redo size
        357  bytes sent via SQL*Net to client
        345  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

Заливка 1 миллиона записей длилась почти 8 минут. Инсерт сгенерил 35 909 076 байт redo - это почти равно размеру непартиционированной таблицы, таблица весит 33 554 432 байт.

SQL> select bytes from dba_segments
     where segment_name like 'NOT_PARTITIONED_TABLE';

     BYTES
----------
  33554432

Теперь попробуем залить те же данные прямым инсертом (direct path insert), командой INSERT /*+ APPEND */.

Direct Path Insert отличается от обычного инсерта тем, что вставка происходит:
1) в обход буферного кеша
2) новые блоки добавляются за отметкой HWM

То есть новые блоки данных подготавливаются в pga сессии и в обход буферного кеша добавляются ЗА отметкой HWM, если даже до отметки HWM есть свободное место для данных. Как объясняет Том Кайт, неправильно считать, что при таком инсерте (direct path insert) вообще не генерится redo. Redo по-любому генерится, но совсем малюсенький по сравнению с обычным инсертом, что существенно увеличивает скорость заливки.
После таких заливок следует сделать полный бэкап базы на всякий случай.

SQL> insert /*+ append */ into partitioned_table
     select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:03.15

Statistics
----------------------------------------------------------
       5710  recursive calls
       3735  db block gets
       5928  consistent gets
       3670  physical reads
     343352  redo size
        351  bytes sent via SQL*Net to client
        359  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

Заливка "инсерт аппендом" закончилась за 3 минуты (почти в 3 раза быстрей обычного инсерта), и сгенерил 343352 байтов redo (почти в 100 раз меньше чем при обычном инсерте).

В три раза быстрей чем обычный инсерт, но как можно еще как-то ускорить заливку? Есть один способ: можно заранее выделить необходимый объем экстентов партициям, чтоб во время заливки на это не тратилось время.

SQL> alter table partitioned_table
     modify partition calls_01012008 allocate extent (size 5M);

Table altered.
....
SQL> alter table partitioned_table
     modify partition calls_maxvalue allocate extent (size 5M);

Table altered.

После предварительного выделения эктентов партициям, инсерт аппенд отработал всего за 5 секунд!!!!

SQL> insert /*+ append */ into partitioned_table
    select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:05.06

А обычный инсерт отработал за 11 секунд:

SQL> insert into partitioned_table select * from not_partitioned_table;

1000000 rows created.

Elapsed: 00:00:11.13

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

Теперь осталось удалить старую таблицу и переименовать партиционированную таблицу.

SQL> drop table not_partitioned_table;

Table dropped.

SQL> alter table partitioned_table rename to new_table;

Table altered.

Можно и партиции переименовать (но их можно было бы создать с нужным именем изначально):

SQL> alter table new_table rename partition calls_01012008 to new_calls_01012008;

Table altered.
...

В следующие варианты - в следующем выпуске новостей.

Параметр CACHE LOB-сегмента

2008-07-31T06:41:00.000-07:00

На прошлой неделе при поддержке запуска одной системы, столкнулась с резким снижением производительности в системе. Администраторы приложения доложили, что процессы еле-еле шевелятся. Я начала копать, правда не сразу докопалась до истины. Большинство сессий этих процессов ожидали объекта SYS_LOB0000086738C00025$$ - LOB сегмента по одному из столбцов (скажем PAYMENT_INFO) одной таблицы (скажем PAYMENT). Содержимое столбца извлекались этим запросом:

select PAYMENT_INFO
from PAYMENT
where PAYMENT_ID = :1 AND PAYMENT_TYPE = :2  
for update;

Выяснилось, что сегмент SYS_LOB0000086738C00025$$ имеет параметр NOCACHE в отличие от аналогичной базы данных, в котором этот же LOB-сегмент имеет параметр CACHE. Вот, что пишут в документации Oracle об этом параметре LOB-сегментов:

CACHE, NOCACHE

Definition

The CACHE storage parameter causes LOB data blocks to be read/written via buffer cache.

With the NOCACHE storage parameter, LOB data is read/written using direct reads/writes. This means that the LOB data blocks are never in the buffer cache and the Oracle server process performs the reads/writes.

Если честно, до этого не знала, что по умолчанию, LOB сегменты читаются в обход буфферного кеша. А установкой параметра LOB-сегмента в CACHE, можно попросить Oracle, чтоб он извлекал блоки этого сегмента через буфферный кеш, что увеличивает производительность системы. Только нужно быть осторожным с LOB-сегментами больших размеров, чтоб они не забили весь буферный кеш. Там же пишут, что использование параметра CACHE вместо NOCACHE улучшает производительность операций ввода-вывода:

The CACHE option gives better read/write performance than the NOCACHE option.

В нашем случае, изменения параметра LOB-сегмента в CACHE помогло:

alter table PAYMENT modify lob ("PAYMENT_INFO") (cache);

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

Working days between two dates

2008-07-31T05:34:00.000-07:00

Функция Оказывается в Oracle нет такой стандартной функции для подсчета количества рабочих дней между двумя датами. Есть функия для подсчета количества месяцев между двумя датами MONTHS_BETWEEN:

MONTHS_BETWEEN
Calculates the number of months between two dates.

В инете поискала такие самописные функции, которые бы считали количество месяцев между двумя датами. Больше всех понравилась этот метод подсчета, описанная в Tips of the Week на сайте Oracle, мне кажется он самый оптимальный из тех, что я посмотрела:

create table date_test (start_dt date, end_dt date);

select start_dt,
       end_dt,
       trunc(end_dt - start_dt) age,
       (trunc(end_dt - start_dt) - ((case
         WHEN (8 - to_number(to_char(start_dt, 'D'))) >
              trunc(end_dt - start_dt) + 1 THEN 0
         ELSE
          trunc((trunc(end_dt - start_dt) - (8 - to_number(to_char(start_dt, 'D')))) / 7) + 1
       END) + (case
         WHEN mod(8 - to_char(start_dt, 'D'), 7) >
              trunc(end_dt - start_dt) - 1 THEN
          0
         ELSE
          trunc((trunc(end_dt - start_dt) -
                (mod(8 - to_char(start_dt, 'D'), 7) + 1)) / 7) + 1
       END))) workingdays
  from date_test

Здесь он считает суммарное количество суббот и воскресений и отнимает от общего количества дней между двумя датами. Правда, глубоко вникать не стала, на тествых датах работает правильно. Можно переписать в функцию:

create or replace function working_days_between (start_dt in date, end_dt in date) return number is
wdays number;
begin
select (trunc(end_dt - start_dt) - ((case
         WHEN (8 - to_number(to_char(start_dt, 'D'))) >
              trunc(end_dt - start_dt) + 1 THEN 0
         ELSE
          trunc((trunc(end_dt - start_dt) - (8 - to_number(to_char(start_dt, 'D')))) / 7) + 1
       END) + (case
         WHEN mod(8 - to_char(start_dt, 'D'), 7) >
              trunc(end_dt - start_dt) - 1 THEN 0
         ELSE
          trunc((trunc(end_dt - start_dt) - (mod(8 - to_char(start_dt, 'D'), 7) + 1)) / 7) + 1
       END))) into wdays from dual;
       return wdays;
end working_days_between;

Сравнение двух схем

2008-07-31T00:13:00.000-07:00

Недавно столкнулась с необходимостью сравнить объекты двух схем, что-то влом было самой писать скрипты по сравнению, поэтому поискала готовые методы сравнения:
1) Сравнение схем с помощью Oracle Change Manager

Сегодня еще рассказали, что в Тоаде тоже есть такая возможность:
2) С помощью Toad for Oracle
Правда начиная с поздних версий, например в 9.1 точно есть, а в 6.4 - нет.
Database -> Compare -> Schemas

"Кем была вызвана процедура или функция?"

2008-07-23T01:31:00.000-07:00

Мой коллега нашел метод позволяющий в процедуре узнать кем она была вызвана, например, в таком примере:

Есть 3 процедуры: процедура 1, процедура 2 и процедура 3
При этом процедура 3 вызывается как из процедуры 1, так и из процедуры 2
Может ли процедура 3 не используя входные параметры понять из какой процедуры она вызвана, из 1-й или 2-й?

Он нашел функцию пакета dbms_utility.format_call_stack, который оказывается возвращает полную цепочку процедур, функций или анонимного PL/SQL блока, которая вызвала эту процедуру:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3902e1880         4  procedure MYSCHEMA.PROC3
3902e8470         3  procedure MYSCHEMA.PROC1
3902d3400         2  anonymous block

В этом примере, анонимный блок вызвал процедуру PROC1, а PROC1 вызвала процедуру PROC3.
Процедура 3, которая вызывается процедурой 1 или 2:

create or replace procedure proc3 is
  call_stack varchar2(4096);
begin
  call_stack := dbms_utility.format_call_stack;
  dbms_output.put_line(call_stack);
end;

Процедуры 1 и 2, которые вызывают процедуру 3:

create procedure proc1 is
begin
     proc3;
end;

create procedure proc2 is
begin
     proc3;
end;

Вызов процедур 1 и 2:

SQL> exec proc1;

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3902e1880         4  procedure MYSCHEMA.PROC3
3902e8470         3  procedure MYSCHEMA.PROC1
390175a80         1  anonymous block

PL/SQL procedure successfully completed

SQL> exec proc2;

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
3902e1880         4  procedure MYSCHEMA.PROC3
3902db938         3  procedure MYSCHEMA.PROC2
390180160         1  anonymous block

PL/SQL procedure successfully completed

Только, из стэка вызывающих процедур и функций нужно вытащить то, что нужно. В данном случае вторую строку стэка. У Тома Кайта есть процедура who_called_me, которая парсит этот стэк и возвращяет кем была вызвана процедура или функция.

Мои второй тренинг

2008-07-22T06:17:00.000-07:00

Вчера во второй раз прочитала свой тренинг SQL Tuning Workshop, правда только одну треть своего тренинга. Мне показалось, что мне уже легче читается, ведь после первого тренинга много чего добавила и улучшила. Темы были такие:

Краткое описание архитектуры Oracle
Шаги выполнения SQL запросов
Совместное использование курсоров
Построение и чтение плана выполнения
Трассировка, чтение трассировочного файла
Форматирование файла трассировки утилитой TKPROF, чтение отформатированного файла.

Теперь, своей первой группе прочитаю вторую часть тренинга и сделаю выводы о том, что можно улучшить.

Дамп структуры индекса

2008-07-11T04:51:00.000-07:00

Иногда очень полезно посмотреть на внутреннюю структуру индекса. Для этого можно выгрузить индекс в дамп следующей командой:

alter session set events 'immediate trace name treedump level nnnn;

где nnnn равен object_id индекса, который можно посмотреть в dba_objects:

SQL> column object_name format A15
SQL> select object_id, object_name
  from dba_objects a
  where owner = 'SCHEMA' and
  object_type = 'INDEX' and
  object_name = 'CUSTOMERS_PK';

OBJECT_ID OBJECT_NAME
---------- ---------------
33243 CUSTOMERS_PK

SQL> alter session set events 'immediate trace name treedump level 33243';

Session altered.

Последняя команда выгрузит структуру указанного индекса в файл в папку user_dump_dest, который обычно указывает на $ORACLE_BASE/admin/SID/udump.
Содержимое файла выглядит приблизительно так (заголовок не включен):

----- begin tree dump
branch: 0x2402004 37756932 (0: nrow: 104, level: 1)
   leaf: 0x2402005 37756933 (-1: nrow: 517 rrow: 517)
   leaf: 0x2402006 37756934 (0: nrow: 514 rrow: 514)
   leaf: 0x2402007 37756935 (1: nrow: 481 rrow: 481)
   leaf: 0x2402008 37756936 (2: nrow: 481 rrow: 481)
   leaf: 0x2403309 37761801 (3: nrow: 481 rrow: 481)
...
   leaf: 0x240336d 37761901 (97: nrow: 481 rrow: 481)
   leaf: 0x240336e 37761902 (98: nrow: 482 rrow: 482)
   leaf: 0x240336f 37761903 (99: nrow: 481 rrow: 481)
   leaf: 0x2403370 37761904 (100: nrow: 481 rrow: 481)
   leaf: 0x2403372 37761906 (101: nrow: 481 rrow: 481)
   leaf: 0x2403373 37761907 (102: nrow: 377 rrow: 377)
----- end tree dump

По дампу видно, что индекс состоит из одного корневого блока и 104 листовых блоков (с -1 до 102 включительно). Строки начинающиеся на branch - это либо корневой блок, либо блоки ветвления. Листовые блоки начинаются на leaf. Вторая цифра после него 37756932 - это адрес блока. Используя этот адрес блока, можно получить id файла и блока функциями пакета dbms_utility и выгрузить содержимое конкретных блоков индекса. Об этом чуть позже. Дальше, nrow - это суммарное количество ключей или строк включая строки, которые отмечены как удаленные, а rrow - количество ключей или строк.

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

SQL> select dbms_utility.data_block_address_file(37756932) file_id,
            dbms_utility.data_block_address_block(37756932) block_id
     from dual;

   FILE_ID   BLOCK_ID
---------- ----------
         9       8196

SQL> alter system dump datafile 9 block 8196;

System altered.

Index Skip Scan

2008-07-10T07:22:00.000-07:00

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

В 9 версии появился новые индексный доступ INDEX SKIP SCAN, который позволяет использовать составной индекс для поиска данных, даже если в условиях поиска отсутствует лидирующий столбец.

SQL> create table t (a number, b number);

Table created.

SQL> create index t_ix on t(a, b);

Index created.

SQL>
SQL> begin
  2  for i in 1 .. 10000
  3  loop
  4  insert into t values(mod(i, 2), i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
  2  dbms_stats.gather_table_stats(user, 'T', cascade=>true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> explain plan set statement_id='SKIP'
  2  for select * from t where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','SKIP', 'ALL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6      3
*  1   INDEX SKIP SCAN      T_IX             1      6      3 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------

   1 - access("T"."B"=5)
       filter("T"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>

В этом примере использовался индексный доступ INDEX SKIP SCAN по составному индексу, при отсутствии поиска по лидирующему столбцу.

Так, как же работает INDEX SKIP SCAN? На одной из публикаций на сайте Оракла, пишут:

During a skip scan, the composite index is accessed once for each distinct value of the leading column(s). For each distinct value, the index is searched to find the query's target values.

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

В документации Оракла пишут, что INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец составного индекса имеет минимальное количество различных значений.

Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.

В нашем примере лидирующий столбец имеет всего 2 различных значения (1 и 0), но если бы он был более селективным, то есть содержал больше различных значений, то скорее всего оптимизатор предпочел бы TABLE FULL SCAN. Как, например, здесь:

SQL> create table t2 (a number, b number);

Table created.

SQL> create index t2_ix on t2(a, b);

Index created.

SQL>
SQL> begin
  2  for i in 1 .. 10000
  3  loop
  4  insert into t2 values(mod(i, 10), i);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> begin
  2  dbms_stats.gather_table_stats(user, 'T2', cascade=>true);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> explain plan set statement_id='NOSKIP'
  2  for select * from t2 where b=5;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display('PLAN_TABLE','NOSKIP', 'ALL'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6      5
*  1   TABLE ACCESS FULL    T2               1      6      5 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

   1 - filter("T2"."B"=5)

Note: cpu costing is off

14 rows selected.

SQL>

Мы всего лишь увеличили количество различных значений лидирующего столбца с 2 до 10, и оптимизатор посчитал, что в этом случае ему выгодней использовать TABLE FULL SCAN чем INDEX SKIP SCAN. Есть хинт index_ss(алиас_таблицы название_индекса), который можно использовать для принудетельного использования индексного доступа INDEX SKIP SCAN:

SQL> explain plan set statement_id='INDEX_SS'
  2  for select /*+ index_ss (t2 t2_ix) */* from t2 where b=5;

Explained.

SQL> select * from table(dbms_xplan.display('PLAN_TABLE','INDEX_SS', 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------


--------------------------------------------------------------------
 Id   Operation              Name        Rows   Bytes  Cost
--------------------------------------------------------------------
   0  SELECT STATEMENT                       1      6     11
*  1   INDEX SKIP SCAN      T2_IX            1      6     11 
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------

   1 - access("T2"."B"=5)
       filter("T2"."B"=5)

Note: cpu costing is off

15 rows selected.

SQL>

Как уже было сказано выше, INDEX SKIP SCAN эффективен в тех случаях, когда лидирующий столбец имеет минимальное количество различных значений. INDEX SKIP SCAN - это не "silver bullet", то есть не идеальное и эффективнейшее решение для всех поисков по составному ключу без лидирующего столбца. Джонатан Льюс (Jonathan Lewis) в своей книге о стоимостном оптимизаторе (кстати, это одна из моих любимых книг по Ораклу) пишет, что INDEX SKIP SCAN может наоборот снижать производительность:

This is a beneficial feature in some cases, particularly if you have compressed indexes with a very low number of distinct values in the first column(s). But occasionally you may find that a skip-scan is causing performance problems.

Есть еще скрытый параметр _optimizer_skip_scan_enabled, который можно установить в false, чтоб отключить индексный путь доступа INDEX SKIP SCAN.

alter session set "_optimizer_skip_scan_enabled"=false;

Подключение к iSQL*Plus как SYSDBA или SYSOPER в Oracle 9i

2008-05-20T08:40:00.000-07:00

Как sysdba или sysoper можно подключиться через iSQL*Plus в Oracle 9i можно по другому url:
http://hostname:7776/isqlplusdba

По умолчанию на /isqlplusdba включена базовая аутентификация и при перед открытием страницы, запросит имя пользователя и пароль.
Чтоб добавить пользователя для базовой аутентификации в Oracle 9i используется стандартная утилита Apache - htpasswd (в Oracle10g - джава утилита jazn, о ней можно почитать здесь):

oracle@myhost $ cd $ORACLE_HOME/Apache/Apache/bin
oracle@myhost $ ./htpasswd -b $ORACLE_HOME/sqlplus/admin/iplusdba.pw username password

Тепер, при открытие в http://hostname:7776/isqlplusdba можно ввести логин (username) и пароль пользователя (password) и подключиться к базе данных как SYSDBA и SYSOPER.

Или же можно отключить базовую аутентификация в /isqlplusdba закомментировав 4 строчки в ORACLE_HOME/sqlplus/admin/isqlplus.conf:

<Location /isqlplusdba>
SetHandler fastcgi-script
Order deny,allow
#AuthType Basic
#AuthName 'iSQL*Plus DBA'
#AuthUserFile /u01/app/oracle/product/9.2.0.8.0/sqlplus/admin/iplusdba.pw
#Require valid-user
</Location>

Не забудьте перегрузить Apache после изменений:

oracle@hostname $ cd $ORACLE_HOME/Apache/Apache/bin
oracle@hostname $ ./apachectl stop
./apachectl stop: httpd stopped
oracle@hostname $ ./apachectl start
./apachectl start: httpd started

Теперь при открытии http://hostname:7776/isqlplusdba не будет запрашивать логин и пароль.

DBMS_STATS или ANALYZE? (3)

2008-05-15T07:01:00.000-07:00

Это продолжение темы "DBMS_STATS или ANALYZE?" Здесь я написала о том, почему Oracle рекомендует использовать dbms_stats вместо analyze, а здесь - о том, отличаются ли статистические данные собраные с помощью dbms_stats от данных, собранных с помощью analyze и если отличаются, то чем именно и кому из них верить.

Очередной вопрос: как определить, каким образом была собрана статистика? Пакетом dbms_stats или оператором analyze?

Столбец global_stats в представлениях dba_tables, dba_indexes, dba_tab_cols, dba_tab_columns, dba_tab_col_statistics определяет собрана глобальная статистика или нет.

Так как analyze не умеет собирать глобальную статистику (ее может собрать только dbms_stats), то можем сделать вывод, что если глобальная статистика собрана, то статистика была собрана с помощью dbms_stats, а если нет глобальной статистики - то статистика была собрана оператором analyze:

YES - собрана глобальная статистика, то есть статистика собрана с помощью dbms_stats
NO - статистика собрана с помощью аггрегирования статистики низлежащих партиций, субпартиций, то есть с помощью analyze

Вырезка из документации:

GLOBAL_STATS
For partitioned tables, indicates whether statistics were
collected for the table as a whole (YES) or were estimated from statistics on
underlying partitions and subpartitions (NO)

Подробней можно прочитать об этом на металинке Note: 236935.1.

SQL> analyze table emp compute statistics;
Table analyzed.

SQL> select table_name, partitioned, global_stats
  2  from user_tables where table_name='EMP';
TABLE_NAME   PARTITIONED GLOBAL_STATS
------------ ----------- ------------
EMP          NO          NO

SQL> exec dbms_stats.gather_table_stats(user, 'EMP', cascade=>true);
PL/SQL procedure successfully completed.

SQL> select table_name, partitioned, global_stats
  2  from user_tables where table_name='EMP';
TABLE_NAME   PARTITIONED GLOBAL_STATS
------------ ----------- ------------
EMP          NO          YES

DBMS_STATS или ANALYZE? (1)

2008-05-15T06:09:00.000-07:00

И dbms_stats и analyze используются для сбора статистики, которая используются стоимостным оптимизатором (Cost-Based Optimizer) для построения планов выполнения. То есть их правильное использование оказывает очень большое влияние на производительность всей системы.
Но почему Oracle "настоятельно" рекомендует использовать пакет dbms_stats для сбора статистики вместо analyze?
Из документации Oracle:

"Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics."

Так что же может dbms_stats, чего не может analyze:
- с помощью dbms_stats можно собирать системную статистику (analyze нет такой возможности)
(о системной статистике напишу отдельно, но "Oracle Corporation highly recommends that you gather system statistics.")
- с помощью dbms_stats можно собирать статистику в удаленной базе данных (через дблинк)

SQL> exec dbms_stats.gather_table_stats@remotedb('REMOTEUSER','REMOTETABLE',cascade=>true);

и еще:

"That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways."

Кажется, что если Oracle рекомендует использовать dbms_stats для сбора статистики и имеет ряд преимуществ перед analyze, то зачем нужен analyze?

Здесь Том Кайт объясняет, что dbms_stats собирает статику только для оптимизатора (CBO).
А такие статистические данные, как количество перенесенных строк (chained rows), средний объем свободного места в блоке, количество неиспользованных блоков можно собрать только с помощью analyze.
Из документации Oracle :

"... you must use the ANALYZE statement rather than DBMS_STATS for statistics collection not related to the cost-based optimizer, such as:
To use the VALIDATE or LIST CHAINED ROWS clauses
To collect information on freelist blocks"

Если dbms_stats и analyze собирают одну и ту же статистику по объектам, то они собирают одинаковые данные?
Или эти данные отличаются?
Если отличаются, то кому из них верить?

Updated: 20 февраля, 2009
Попытаюсь ответить на эти вопросы: Некоторые столбцы/компоненты статистики, собранные dbms_stats-ом отличаются от той, которые собраны командой analyze.
Например кол-во строк (num_rows), высота индекса (blevel), конечно же НЕ отличаются.
А такие столбцы как avg_row_len, avg_col_len - отличаются: dbms_stat добавляет 1 байт за хранение длины столбца, а analyze - не учитывает байт, в котором хранится длина столбца. Вот что пишет Джонатан Льюис об этом:

As a general rule, the figures for bytes in execution plans are derived from the avg_col_len columns of user_tab_columns. The deprecated analyze command excludes the length byte(s) for the column, but the call to dbms_stats.gather_table_stats includes the length byte(s). Since the choice of build table in a hash join is affected by the size of the data sets involved, a switch from analyze to dbms_stats could (in principle) change the order of a hash join, or even cause the optimizer to use a different join mechanism.

В-общем, он говорит, что, в принципе, из-за этой разницы в статистике, оптимизатор может изменить план выполнения в зависимости от метода сбора статистики, так как статистика немного отличается.
Ответ на последний вопрос "Если отличаются, то кому из них верить?": Думаю достаточно иметь ввиду, что некоторые столбцы/компоненты статистики собранные dbms_stats-ом отличаются от той, которая собрана командой analyze. И, к тому же статистика нужна только оптимизатору, а вам она не нужна. Поэтому долго не думайте и собирайте статистику dbms_stats-ом, а чтоб увеличить скорость сбора, распараллельте или же собирайте с небольшим estimate-ом.

Здесь я написала о том, как определить, каким образом была собрана статистика: пакетом dbms_stats или оператором analyze.

Где именно находится оптимизатор в архитектуре Oracle RDBMS?

2008-05-14T12:28:00.001-07:00

Oracle DBA обычно не интересуются вопросом "Где именно находится оптимизатор в архитектуре Oracle RDBMS?" Некоторые из моих знакомые ДБА отнесли его к категории вопросов о смысле жизни.
Но самый популярный ответ был "в ядре Oracle".

В книге Oracle Database 10g Insider Solutions пишут тоже самое:

"The Cost Based Optimizer is at the heart of the Oracle kernel and plays a large part in the efficient execution of SQL statements in Oracle Database 10g."

Но где именно находится это ядро (kernel)? Это обычный процесс? Если да, то можно ли его увидеть в юниксе в списке процессов командой "ps"?

Отрывок из книги Тома Кайта "Oracle для профессионалов: Архитектура и основные особенности":

"При получении запроса SELECT * FROM EMP именно выделенный/разделяемый сервер Oracle будет разбирать его и помещать в разделяемый пул (или находить соответствующий запрос в разделяемом пуле). Именно этот процесс создает план выполнения запроса. Этот процесс реализует план запроса, находя необходимые данные в буферном кеше или считывая данные в буферный кеш с диска. Такие серверные процессы можно назвать "рабочими лашадками" СУБД. Часто именно они потребляют основную часть процессорного времени в системе, поскольку выполняют сортировку, суммирование, соединения - в общем, почти все."

То есть функции оптимизатора выполняются серверными процессами и их мы можем увидеть в списке процессов:

oracle@myhost$ ps -ef  grep ora  grep LOCAL  more
  oracle 22790     1  0 09:13:33 ?        0:03 oracleTESTDB (LOCAL=NO)
  oracle  4426     1  0 11:20:58 ?        0:02 oracleTESTDB (LOCAL=NO)
  oracle 29167     1  0 11:11:32 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 12778     1  0 09:53:02 ?        0:03 oracleTESTDB (LOCAL=NO)
  oracle 14349     1  0 12:26:34 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 21141     1  0 11:47:35 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 11220     1  0 09:49:18 ?        0:06 oracleTESTDB (LOCAL=NO)
  oracle 16823     1  0 11:40:49 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 26760     1  0 11:57:20 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 20814     1  0 09:09:42 ?        0:01 oracleTESTDB (LOCAL=NO)
  oracle 17374     1  0 12:32:28 ?        0:02 oracleTESTDB (LOCAL=NO)
  oracle  8911     1  0 22:14:38 ?        0:00 oracleTESTDB (LOCAL=NO)

Если серверные процессы разбирают все запросы (выполняют все функции оптимизатора), значит ли это, что:

Код самого оптимизатора находится в каждом серверном процессе?
Или серверные процессы всего лишь вызывают эти функции из ярда Oracle?
Или ядро Oracle - это и есть серверные процессы?

Для меня этот вопрос все еще остается открытым, если у кого-то есть идеи, буду рада их услышать.

Добавлено 16 мая, 2008:
Это ответ Джонатана Льюиса на этот вопрос (публикую с его разрешения):

There is one main executable for the database in Oracle distribution, and that is called oracle (on Unix systems, but oracle.exe on Windows).
This is the program that becomes pmon, smon, dbwr, s000, and all the other background processes when the instance starts up. The bits of code run from that executable vary across the different roles played in the instance.

As such, the optimiser is just part of the code that is called only by a program which is taking on the role of a dedicated server (oracle_{SID}_nnn in unix variants) or a shared server (oracle_{SID}_Snnn).

When people talk about the 'Oracle kernel' it's actually a very informal and inaccurate expression - they are trying to give a vague impression of the most commonly used part of the code with an emphasis, perhaps, on the code segments that do a lot of synchronised work in the shared memory area. But there is no specific process that you can see that is "the" kernel.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Перевод:

Есть один основной бинарник в дистрибутиве Oracle, который так и называется oracle (в юникс системах, и oracle.exe в Windows). Когда стартуется инстанс, эта программа превращается в фоновые процессы pmon, smon, dbwr, s000 и тд.
В зависимости от роли, которую он выполняет в составе инстанса, выполняются отдельные биты кода этого бинарника.

Оптимизатор - это всего лишь кусочек кода, который вызывается программой, выполняющей роль выделенного сервера (oracle_{SID}_nnn в юниксе) или разделяемого сервера (oracle_{SID}_Snnn).

Когда люди говорят о "ядре Oracle", они на самом деле используют неформальное и не совсем точное выражение - они стараются дать смутное ощущение о часто используемой части кода, возможно имея ввиду сегменты кода, которые выполняют кучу синхронизированных операций в разделяемой памяти.
Но на самом деле нет отдельного процесса, которого можно увидеть и который являлся бы "ядром".

Прикольно, значит код самого оптимизатора находится в каждом серверном процессе.
Спасибо всем, кто участвовал в процессе выяснения местонахождения оптимизатора в архитектуре Oracle. Отдельное спасибо Джонатану Льюису, кстати всем, кто занимается тюнингом, рекомендую почитать его книжку Основы Стоимостной Оптимизации (Cost-Based Oracle Fundamentals).

Одноблочное и многоблочное чтения (single-block & multi-block read)

2008-05-06T02:41:00.000-07:00

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

Кол-во блоков, которое будет прочитано из файла в буферный кэш за одну системную операцию ввода/вывода устанавливается параметром db_file_multiblock_read_count. В OLTP системах он обычно равен от 8 до 16, а в OLAP системах от 32 и выше. На большинстве платформ значение db_file_multiblock_read_count может быть равен максимум 128, об это написано в предыдущей теме.

С помощью представления v$filestat можно посмотреть статистику по количеству одноблочных и многоблочных чтений. Описание полей из документации (только те, что интересуют нас и относятся к чтению):

FILE# Number of the file
PHYRDS Number of physical reads done
PHYBLKRD Number of physical blocks read
SINGLEBLKRDS Number of single block reads

В списке полей нет поля о кол-ве многоблочных чтений, но его мы можем получить отняв SINGLEBLKRDS (так как, за одну операцию одноблочного чтения читается один блок) от суммарного количества физических чтений PHYRDS:
MULTIRDS = PHYRDS-SINGLEBLKRDS
А кол-во блоков прочитанных в режиме многоблочного чтения можно получить отняв SINGLEBLKRDS от суммарного кол-ва прочитанных блоков PHYBLKRD:
MULTIBLKRDS = PHYBLKRD-SINGLEBLKRDS

Еще один важный параметр, который можно получить - это среднее количество блоков прочитанных в многоблочном чтении. Если этот параметр близок к db_file_multiblock_read_count, то это хорошо. Это значит, что при многоблочном чтении из файла в буферный кэш, за минимальное количество операций ввода/вывода, читается максимальное количество блоков.
BLKSMULTIBLKRDS = MULTIBLKRDS/MULTIRDS

Скрипт:

select round(SINGLEBLKRDS/PHYBLKRD,2) SINGLEBLKRDS_PCT,
round((PHYBLKRD-SINGLEBLKRDS)/PHYBLKRD,2) MULTIBLKRDS_PCT,
round((PHYBLKRD-SINGLEBLKRDS)/(PHYRDS-SINGLEBLKRDS),2) BLKSMULTIBLKRDS,
k.tablespace_name
from v$filestat t, dba_data_files k
where t.file#=k.file_id
order by 1

Здесь приблизительный результат:

SINGLEBLKRDS_PCT MULTIBLKRDS_PCT BLKSMULTIBLKRDS TABLESPACE_NAME
0         1     7,74     CCCTBS
0         1     7,83     CCCTBS
0         1     7,88     CCCTBS
0         1    15,73     USERS
0,01   0,99     7,88     CCCTBS
0,01   0,99    14,65     DDDDTBS
0,02   0,98    11,82     DATA_MED
0,03   0,97    11,73     DATA_MED
0,05   0,95    13,54     AAA_BBB
0,07   0,93     1,03     EEEETBS
0,26   0,74    14,49     DATA_SML
0,3     0,7    10,32     GGGDATA
0,31   0,69     9,35     SYSTEM
0,49   0,51    11,32     DATA_BIG
0,5     0,5    11,41     DATA_BIG
0,52   0,48       10     DATA_BIG
0,52   0,48    11,47     DATA_BIG
0,53   0,47    10,21     DATA_BIG
0,68   0,32    14,52     DATA_BIG
0,91   0,09    14,68     DATA_BIG_IX
0,91   0,09    15,28     DATA_BIG_IX
0,92   0,08        1     UNDO
0,92   0,08    14,81     DATA_BIG_IX
0,92   0,08    15,08     DATA_BIG_IX
0,93   0,07    15,32     DATA_BIG_IX
0,98   0,02        1     AAA_BBB_IX
0,99   0,01     1,65     FFFFTBS
   1      0        1     DATA_SML_IX
   1      0        1     DATA_MED_IX

Как показывает отчет, в табличных пространствах, где хранятся индексы (_IX) почти нет (или очень низкий процент) многоблочного чтения, думаю это из-за того, что в них нет таблиц, соответсвенно не бывает полных просмотров таблиц (table full scan) и очень низок процент fast full index scan.

Максимальное значение db_file_multiblock_read_count

2008-05-05T06:10:00.000-07:00

Параметр db_file_multiblock_read_count определяет количество блоков, которые будут считаны за одну системную операцию чтения.

Он используется стоимостным оптимизатором (Cost-based Optimizer) для вычисления суммарного кол-ва операций чтения для полного просмотра таблицы (full table scan).

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

Самый лёгкий способ узнать максимальный размер db_file_multiblock_read_count - это присвоить этому параметру очень большое значение и он скорректируется до максимально возможного размера:

SQL> select name, value from v$parameter
  2  where name = 'db_file_multiblock_read_count';

NAME                           VALUE
------------------------------ ----------
db_file_multiblock_read_count  8

SQL> alter session set db_file_multiblock_read_count=100000000;

System altered

SQL>
SQL> select name, value from v$parameter
  2  where name = 'db_file_multiblock_read_count';

NAME                           VALUE
------------------------------ ----------
db_file_multiblock_read_count  128

Этот запрос был выполнен на Oracle 9.2.0.8, размер блока БД - 8K, Solaris 5.9.
Максимальное значение этого параметра зависит от внутреннего параметра (константы) SSTIOMAX, который зависит от версии Oracle и размера блока базы данных. Это вырезка из Металинка Note:131530.1:

What is SSTIOMAX?
-----------------

SSTIOMAX is an internal parameter/constant used by oracle, which limits the
maximum amount of data transfer in a single IO of a read or write operation.
This parameter is fixed and cannot be tuned/changed.

Relationship between SSTIOMAX and db_file_multiblock_read_count (MBRC)
----------------------------------------------------------------------

More often than not, DBAs try to increase the db_file_multiblock_read_count
parameter (which can be set in the init.ora), in an attempt to optimize the
IO performance of the read and write operations.

Normally, with a higher value of MBRC, the IO performance is expected to be
better. So, users tend to increase this parameter to a higher value, in case
they find it beneficial. But, there is a limitation on this.

The limitation is, the product of db_block_size and MBRC cannot exceed the
SSTIOMAX. For example:

db_block_size * db_file_multiblock_read_count <= SSTIOMAX (which is predefined for a particular version of oracle) If the value of the product exceeds this, then the value of db_file_multiblock_read_count set in the init.ora is ignored and it is set as follows: db_file_multiblock_read_count = SSTIOMAX/db_block_size (rounded)

В Oracle 9i SSTIOMAX = 1M, то есть если
размер блока БД 16K, то максимальный размер db_file_multiblock_read_count = 1M / 16K = 64;
размер блока БД 8K, то максимальный размер db_file_multiblock_read_count = 1M / 8K = 128;
размер блока БД 4K, то максимальный размер db_file_multiblock_read_count = 1M / 4K = 256;

Для сравнения, в Oracle 7.3 SSTIOMAX был равен 128K.

Создание табличного пространства, размер блока которого отличается от размера блока БД

2008-05-04T07:13:00.000-07:00

Для создания табличного пространства, размер блока которого отличается от размера блока БД, необходимо выделить память под соответствующий db_Xk_cache_size
(db_2k_cache_size,db_4k_cache_size,db_8k_cache_size, db_16k_cache_size, db_32k_cache_size).

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
SQL> create tablespace tbs4k blocksize 4096
  2  datafile '/u01/oradata/TESTDB/tbs4kTESTDB01.dbf'
  3  size 10M autoextend on next 1M;
create tablespace tbs4k blocksize 4096
*
ERROR at line 1:
ORA-29339: tablespace block size 4096 does not match configured block sizes

SQL> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 536870912
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

SQL> alter system set db_4k_cache_size=16M scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1159537920 bytes
Fixed Size                   730368 bytes
Variable Size             603979776 bytes
Database Buffers          553648128 bytes
Redo Buffers                1179648 bytes
Database mounted.
Database opened.

SQL> show parameter cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 16777216
db_8k_cache_size                     big integer 0
db_cache_advice                      string      ON
db_cache_size                        big integer 536870912
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
object_cache_max_size_percent        integer     10
object_cache_optimal_size            integer     102400

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     100
SQL> create tablespace tbs4k blocksize 4096
  2  datafile '/u01/oradata/TESTDB/tbs4kTESTDB01.dbf'
  3  size 10M autoextend on next 1M;

Tablespace created.

Загрузка данных из Агента в OMS

2008-04-28T12:09:00.001-07:00

English version of this article

Oracle Management Agent 10g был установлен на узле с помощью скрипта agentDownload script. Несмотря на то, что установка и настройка агента прошла успешно, новый узел не появился в списке хостов в веб-интерфейсе Enterprise Manager Grid Control.

Статус агента:

gc@agenthost $ emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.0
OMS Version       : 10.2.0.1.0
Protocol Version  : 10.2.0.0.0
Agent Home        : /u01/app/gc/product/agent10g
Agent binaries    : /u01/app/gc/product/agent10g
Agent Process ID  : 26774
Parent Process ID : 26765
Agent URL         : https://agenthost.domain.com:3872/emd/main/
Repository URL    : https://omshost:1159/em/upload
Started at        : 2008-04-24 12:20:17
Started by user   : gc
Last Reload       : 2008-04-24 12:20:17
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :        6
Size of XML files pending upload(MB)         :     5.88
Available disk space on upload filesystem    :    18.10%
Last successful heartbeat to OMS             : 2008-04-24 12:20:26
---------------------------------------------------------------
Agent is Running and Ready

В статусе агента видно, что Агент не может загрузить данные в OMS, а если вручную попробывать загрузить данные, то выводит такое сообщение:

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

В логах агента, нашла такие ошибки в ORACLE_HOME/sysman/log/emagent.trc:

2008-04-23 19:54:26,063 Thread-20 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,064 Thread-20 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:26,067 Thread-20 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,067 Thread-20 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:26,359 Thread-5 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,359 Thread-5 ERROR command: nmejcn: failed http connection to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:28,368 Thread-5 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)

Причина ошибки оказалась простой, из узла agenthost не определялся omshost:

gc@agenthost $ ping -a omshost
ping: unknown host omshost

После, прописания omshost в /etc/hosts (555.555.555.555 omshost), загрузка данных в OMS прошла успешно


gc@agenthost $ ping -a omshost
omshost (555.555.555.555) is alive

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload timed out before completion.
Number of files to upload before the upload: 13, total size (MB): 5.25.
Remaining number of files to upload: 13, total size (MB): 5.25.

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload timed out before completion.
Number of files to upload before the upload: 6, total size (MB): 1.38.
Remaining number of files to upload: 6, total size (MB): 1.38.

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Теперь в статусе агента видно, что загрузка прошла успешно:

gc@agenthost $ emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.0
OMS Version       : 10.2.0.1.0
Protocol Version  : 10.2.0.0.0
Agent Home        : /u01/app/gc/product/agent10g
Agent binaries    : /u01/app/gc/product/agent10g
Agent Process ID  : 26774
Parent Process ID : 26765
Agent URL         : https://agenthost.domain.com:3872/emd/main/
Repository URL    : https://omshost:1159/em/upload
Started at        : 2008-04-24 12:20:17
Started by user   : gc
Last Reload       : 2008-04-24 12:20:17
Last successful upload                       : 2008-04-24 12:39:02
Total Megabytes of XML files uploaded so far :     7.44
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    18.13%
Last successful heartbeat to OMS             : 2008-04-24 12:39:27
---------------------------------------------------------------
Agent is Running and Ready

Problem with Management Agent 10g Upload

2008-04-28T12:04:00.000-07:00

На русском

I had a problem adding another target to Oracle Grid Control 10g. The Management Agent was installed by agentDownload script, the installation finished successfully, but I the target didn’t show up in the Host list of Grid Control web page.

I checked the status of agent on target host:

gc@agenthost $ emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.0
OMS Version       : 10.2.0.1.0
Protocol Version  : 10.2.0.0.0
Agent Home        : /u01/app/gc/product/agent10g
Agent binaries    : /u01/app/gc/product/agent10g
Agent Process ID  : 26774
Parent Process ID : 26765
Agent URL         : https://agenthost.domain.com:3872/emd/main/
Repository URL    : https://omshost:1159/em/upload
Started at        : 2008-04-24 12:20:17
Started by user   : gc
Last Reload       : 2008-04-24 12:20:17
Last successful upload                       : (none)
Last attempted upload                        : (none)
Total Megabytes of XML files uploaded so far :     0.00
Number of XML files pending upload           :        6
Size of XML files pending upload(MB)         :     5.88
Available disk space on upload filesystem    :    18.10%
Last successful heartbeat to OMS             : 2008-04-24 12:20:26
---------------------------------------------------------------
Agent is Running and Ready

The status report showed that Agent could NOT upload to OMS, so I tried to manually upload to OMS:

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

I checked the agent logs and came across this message in ORACLE_HOME/sysman/log/emagent.trc:

2008-04-23 19:54:26,063 Thread-20 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,064 Thread-20 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:26,067 Thread-20 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,067 Thread-20 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:26,359 Thread-5 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)
2008-04-23 19:54:26,359 Thread-5 ERROR command: nmejcn: failed http connection to https://omshost:1159/em/upload: retStatus=-32
2008-04-23 19:54:28,368 Thread-5 ERROR http: snmehl_connect: Failed to get address for omshost: Non-Authoritive Host not found (error = 2)

The omshost could not be resolved from agenthost:

gc@agenthost $ ping -a omshost
ping: unknown host omshost

After configuring /etc/hosts (adding 555.555.555.555 omshost), and re-trying the upload, upload was successful:

gc@agenthost $ ping -a omshost
omshost (555.555.555.555) is alive

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload timed out before completion.
Number of files to upload before the upload: 13, total size (MB): 5.25.
Remaining number of files to upload: 13, total size (MB): 5.25.

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload timed out before completion.
Number of files to upload before the upload: 6, total size (MB): 1.38.
Remaining number of files to upload: 6, total size (MB): 1.38.

gc@agenthost $ emctl upload
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

The agent status shows that upload is successful now:

gc@agenthost $ emctl status agent
Oracle Enterprise Manager 10g Release 4 Grid Control 10.2.0.4.0.
Copyright (c) 1996, 2007 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.0
OMS Version       : 10.2.0.1.0
Protocol Version  : 10.2.0.0.0
Agent Home        : /u01/app/gc/product/agent10g
Agent binaries    : /u01/app/gc/product/agent10g
Agent Process ID  : 26774
Parent Process ID : 26765
Agent URL         : https://agenthost.domain.com:3872/emd/main/
Repository URL    : https://omshost:1159/em/upload
Started at        : 2008-04-24 12:20:17
Started by user   : gc
Last Reload       : 2008-04-24 12:20:17
Last successful upload                       : 2008-04-24 12:39:02Total Megabytes of XML files uploaded so far :     7.44
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    18.13%
Last successful heartbeat to OMS             : 2008-04-24 12:39:27
---------------------------------------------------------------
Agent is Running and Ready

Installation of Oracle Application Server 10g in Windows Vista

2008-04-28T12:02:00.000-07:00

Installation of Oracle Application Server 10g in Windows Vista failed with the following error:

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.0, 5.1 or 5.2 . Actual 6.0
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at C:\User
s\Raku\AppData\Local\Temp\OraInstall2008-03-22_02-00-54AM\installActions2008-03-
22_02-00-54AM.log

Please press Enter to exit...

It's a pity that OAS 10g is not supported in Windows Vista.
However, you can add 6.0 (for Vista) in install/oraparam file:
Windows=5.0,5.1,5.2,6.0
and restart the installation.
There is, of course, no guarantee that it will work error-free.
I installed the same way, I will look if it works correctly.

Error in upgrading 9.2.0.1 database to 10g

2008-04-28T12:00:00.001-07:00

Today, I was trying to upgrade my 9.2.0.1 database to 10g. I installed the 10g server, and run the Pre-Upgrade Information Tool (utlu102i.sql) to analyze and prepare my 9.2.0.1 database for upgrade.

SQL> @?/rdbms/admin/utlu102i
DECLARE
*
ERROR at line 1:
ORA-20000: Version 9.2.0.1.0 not supported for upgrade to release 10.2.0
ORA-06512: at line 1523

It turned out that I should have upgraded it to at least 9.2.0.4 before installing 10g server software. Should have....
Here is the link to the upgrade documentation.

Error Creating Snapshot in AWR

2008-04-28T11:58:00.000-07:00

When trying to run an ADDM Report in Enterprise Manager Grid Control 10g, I got the following error:

“Insufficient Data in Interval. For displaying data on this page, two historical snapshots are needed. Make sure that two snapshots are present in the target database instance. In addition modify the interval so that it is contained within two available snapshots”

I looked at Automatic Workload Repository, it wasn’t configured automatically.
Then I tried to manually create snapshot in Automatic Workload Repository:

“Are you sure you want to create a manual snapshot?
Snapshots are created automatically by the database. Creating one manually may affect the results of the automatic snapshot immediately following.”

I answered Yes, but snapshot couldn’t be created:

ORA-13516: SWRF Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31
ORA-06512: at line 1

Then I tried the same thing to manually create snapshot using the package:

SQL> exec dbms_workload_repository.create_snapshot();

begin dbms_workload_repository.create_snapshot(); end;

ORA-13516: SWRF Operation failed: SWRF Schema not initialized
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 8
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 31
ORA-06512: at line 1

The metalink Note:287818.1:
Error: ORA-13516 (ORA-13516)
Text: SWRF Operation failed: %s
---------------------------------------------------------------------------
Cause: The operation failed because SWRF is not available. The possible
causes are: SWRF schema not yet created; SWRF not enabled; SWRF
schema not initialized; or database not open or is running in
READONLY or STANDBY mode.
Action: check the above conditions and retry the operation.

Note:459887.1:

Cause
These errors would be caused because of wrong or invalid objects with respect to AWR

Solution
In order to resolve this issue it is recommended to drop and recreate the AWR objects , which can be done using CATNOAWR.SQL and CATAWR.SQL.

But from 10.2 onwards, the script name has changed. The catalog script for AWR Tables, used to create the Workload Repository Schema is CATAWRTB.SQL .
Dropping and recreating the AWR objects and bouncing the database:

SQL> @$ORACLE_HOME/rdbms/admin/catnoawr.sql
SQL> @$ORACLE_HOME/rdbms/admin/catawr.sql

Ошибки в Oracle Management Agent 10g: 'No username specified for WBEM fetchlet'

2008-04-28T11:29:00.000-07:00

English version of this article

После добавления нового узла в Oracle Grid Control 10g, в логах агента я заметила такие ошибки в emagent.trc:

2008-04-25 15:32:17,560 Thread-14019 WARN  collector:  Error exit. Error message: No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 ERROR fetchlets.wbem: No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 ERROR engine: [host,eccisdb,ProjectResourceUsage] : nmeegd_GetMetricData failed : No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 WARN  collector:  Error exit. Error message: No username specified for WBEM fetchlet.

Такая же ошибка была в веб-интерфейсе Oracle Grid Control:

Host: agenthost55  >  Metric Collection Errors  >
 Error Details
 Target:  agenthost55
 Type:  Host
 Metric:  Aggregate Resource Usage Statistics (By Project)
 Collection Timestamp:  09-Apr-2008 12:24:18
 Error Type:  Collection Failure
 Message:  No username specified for WBEM fetchlet.

Ниже описание причины ошибки и ее решение из Металинка Note:271632.1:

Cause:
Beginning with EM 10g, certain OS vendors have enhanced their systems to include a new metric - Aggregate resource usage statistics, gathered by User and by Project. These metrics are collected by using the "CIM Object Manager" on the Solaris 9 host. In order to get this information, the CIM Object Manager needs to be supplied with Username and Password of a user on that host so that authentication can successfully happen 

Solution:
From within the EM 10g Grid Control UI,
1. Click on Targets -> Host tab
2. Select your Solaris 9 host and go to the Hosts Home page (repeat these steps for each Solaris 9 host)
3. Click on the Monitoring Configuration link at the bottom right side of the page
4. Enter your OS username/password. It's best to enter a user that has DBA privileges.
5. Click save.
This will update the central Management Agent's targets.xml file on the Solaris 9 host and force a reload of the configuration files. The error above should stop.

'No username specified for WBEM fetchlet' errors in Agent 10g

2008-04-25T06:21:00.000-07:00

Прочитать на русском

After adding a new target to Oracle Grid Control 10g, I noticed these errors in emagent.trc:

2008-04-25 15:32:17,560 Thread-14019 WARN  collector:  Error exit. Error message: No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 ERROR fetchlets.wbem: No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 ERROR engine: [host,eccisdb,ProjectResourceUsage] : nmeegd_GetMetricData failed : No username specified for WBEM fetchlet.
2008-04-25 15:37:34,865 Thread-14064 WARN  collector:  Error exit. Error message: No username specified for WBEM fetchlet.

The same error was in the Grid Control Console:

Host: agenthost55  >  Metric Collection Errors  >
 Error Details
 Target:  agenthost55
 Type:  Host
 Metric:  Aggregate Resource Usage Statistics (By Project)
 Collection Timestamp:  09-Apr-2008 12:24:18
 Error Type:  Collection Failure
 Message:  No username specified for WBEM fetchlet.

Here is the solution from Metalink Note:271632.1:

Cause:
Beginning with EM 10g, certain OS vendors have enhanced their systems to include a new metric - Aggregate resource usage statistics, gathered by User and by Project. These metrics are collected by using the "CIM Object Manager" on the Solaris 9 host. In order to get this information, the CIM Object Manager needs to be supplied with Username and Password of a user on that host so that authentication can successfully happen 

Solution:
From within the EM 10g Grid Control UI,
1. Click on Targets -> Host tab
2. Select your Solaris 9 host and go to the Hosts Home page (repeat these steps for each Solaris 9 host)
3. Click on the Monitoring Configuration link at the bottom right side of the page
4. Enter your OS username/password. It's best to enter a user that has DBA privileges.
5. Click save.
This will update the central Management Agent's targets.xml file on the Solaris 9 host and force a reload of the configuration files. The error above should stop.

Automatic Optimizer Statistics Collection

2008-04-23T11:24:00.000-07:00

В Oracle 10g сбор статистики для оптимизатора выполняется автоматически запланированным джобом (scheduled job) GATHER_STATS_JOB. Данная статистика об объектах используется оптимизатором (Cost-Based Optimizer) для построения эффективных планов выполнения для запросов, тем самым значительно уменьшая время выполнения запросов.

По умолчанию, сбор статистики выполняется по ночам с 22:00 до 06:00 утра и весь день в выходные дни.
Собирается статистика только по тем объектам, у которых отсутствует статистика, или устарела.

Каким образом Oracle узнает, что статистика устарела?
Данные о кол-ве DML операциий (INSERT, DELETE, UPDATE) над объектом с момента последнего сбора статистики фиксируются в SGA, которые периодически записываются в таблицу DBA_TAB_MODIFICATIONS. База данных использует эти данные для того, чтобы определить устарела ли статистика объекта.

Из документации Oracle:

Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:
Missing statistics
Stale statistics

This job is created automatically at database creation time and is managed by the Scheduler. This Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends. The GATHER_STATS_JOB continues until it finishes, even if it exceeds the allocated time for the maintenance window. The default behavior of the maintenance window can be changed.

Запланированный джоб GATHER_STATS_JOB:

SQL> select owner, job_name, program_name, enabled from dba_scheduler_jobs
where job_name='GATHER_STATS_JOB';

OWNER      JOB_NAME           PROGRAM_NAME         ENABLED
---------- ------------------ -------------------- -----
SYS        GATHER_STATS_JOB   GATHER_STATS_PROG    TRUE

Выключить Автоматический Сбор Статистики для Оптимизатора, можно отключив джоб:

SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB');
PL/SQL procedure successfully completed.


SQL> select owner, job_name, program_name, enabled from dba_scheduler_jobs
where job_name='GATHER_STATS_JOB';

OWNER      JOB_NAME           PROGRAM_NAME         ENABLED
---------- ------------------ -------------------- -----
SYS        GATHER_STATS_JOB   GATHER_STATS_PROG    FALSE

Если в базе данных имеются таблицы, которые часто обновляются, то частый сбор статистики может негативно повлиять на производительность базы данных. Для того, чтоб исключить объекты из автоматического или любого другого сбора статистики можно "закрепить" ее статистику:

begin
 dbms_stats.gather_table_stats('SCOTT','EMP');
 dbms_stats.lock_table_stats('SCOTT','EMP');
end;
/

Теперь, по этой таблице невозможно будет собрать статистику ни автоматически, ни вручную:

SQL>exec dbms_stats.gather_table_stats('SCOTT','EMP');

begin dbms_stats.gather_table_stats('SCOTT','EMP'); end;
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 2

Снять блокировку статистики:

dbms_stats.unlock_table_stats('SCOTT','EMP');

Statspack (9i) и Automatic Workload Repository (10g)

2008-04-22T07:00:00.000-07:00

Для сбора информации о производительности базы данных в Oracle 9i использовался statspack, в Oracle 10g statspack эволюционировал в Automatic Workload Repository (Автоматически управляемый репозитарий рабочей нагрузки).

В Oracle 9i statspack устанавливался дополнительно скриптом spcreate.sql, который создавал пользователя perfstat, под которым и создавался пакет statspack и другие необходимые объекты.

В Oracle 10g Automatic Workload Repository устанавливается автоматически прямо в схеме SYS, и по умолчанию собирает статистику о производительности каждый час и хранит ее 7 дней.

В Oracle 9i, снимок (snapshot) делался с помощью пакета statspack:
exec statspack.snap;

В Oracle 10g используется новый пакет dbms_workload_repository:
exec dbms_workload_repository.create_snapshot;

Список снимков (snapshots) в Oracle 9i:
select * from stats$snapshot;

Список снимков (snapshots) в Oracle 10g:
select * from dba_hist_snapshot;

Для создания отчета на основе двух снимков, в Oracle 9i использовался скрипт:
SQL> @?/rdbms/admin/spreport.sql

В Oracle 10g используется скрипт, который может сгенерировать отчет и текстовом, и в html формате:
SQL> @?/rdbms/admin/awrrpt.sql

В Oracle 10g все эти операции, то есть сделать снимок, просмотреть список снимков, и создать отчет в формате html, можно проделать в Enterprise Manager Database Control или Grid Control.

Prstat и процессы oracle

2008-04-21T07:01:00.000-07:00

Отчет ниже показывает, что процессы пользователя oracle занимают 94% всей оперативной памяти, то есть почти 188GB, в то время как оперативная память на сервере в сумме равна всего 32GB (16GB физической памяти + 4GB виртуальной памяти).

Физическая память:
oracle@myhost $ prtdiag grep Memory
Memory size: 16384 Megabytes

Виртуальная память:
oracle@myhost $ vmstat 3 3
kthr memory page disk faults cpu r b w swap free re mf pi po fr de sr s0 sd sd sd in sy cs us sy id
0 0 0 16319144 8342704 15 104 7 8 8 15688 0 0 0 0 0 473 2256 2086 2 1 97
0 0 0 4425048 280488 33 409 0 3 3 11448 0 0 0 0 0 1076 408191 5033 24 15 60
0 0 0 4425048 280440 25 255 0 13 13 8352 0 0 0 0 0 867 429324 6717 25 15 59

oracle@myhost $ prstat -a -s size
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
29689 user1 2540M 2313M cpu1 42 0 10:24:56 6.3% java/103
24113 user2 1689M 1153M sleep 14 0 0:48:16 0.0% java/114
21867 user3 1270M 829M sleep 15 0 1:19:42 0.1% java/81
20270 user1 1267M 931M sleep 6 0 0:09:12 0.0% java/83
13020 user3 1259M 821M sleep 1 0 1:15:39 0.0% java/81
22021 oracle 1117M 1069M sleep 59 0 0:00:52 0.0% oracle/258
22023 oracle 1115M 1064M sleep 59 0 0:01:17 0.0% oracle/11
22086 oracle 1112M 1061M sleep 1 0 0:00:00 0.0% oracle/1
22088 oracle 1112M 1061M sleep 59 0 0:00:00 0.0% oracle/1
22025 oracle 1111M 1064M sleep 59 0 0:00:12 0.0% oracle/11
2768 oracle 1110M 1072M sleep 59 0 0:00:01 0.0% oracle/11
19012 oracle 1110M 1072M sleep 59 0 0:00:00 0.0% oracle/1
19030 oracle 1110M 1069M sleep 59 0 0:00:00 0.0% oracle/1
11409 oracle 1110M 1069M sleep 59 0 0:00:00 0.0% oracle/1
22027 oracle 1110M 1070M sleep 1 0 0:00:31 0.0% oracle/1
4125 oracle 1109M 1071M sleep 59 0 0:00:00 0.0% oracle/1
22019 oracle 1109M 1064M sleep 59 0 0:03:36 0.0% oracle/1
4005 oracle 1109M 1069M sleep 59 0 0:00:00 0.0% oracle/1
27534 oracle 1109M 1068M sleep 59 0 0:00:00 0.0% oracle/1
27536 oracle 1109M 1068M sleep 59 0 0:00:00 0.0% oracle/1
22048 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22037 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22035 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22042 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22050 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22054 oracle 1109M 1062M sleep 21 0 0:00:00 0.0% oracle/1
22056 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22060 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22064 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22058 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22070 oracle 1109M 1062M sleep 53 0 0:00:00 0.0% oracle/1
22066 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
22068 oracle 1109M 1062M sleep 59 0 0:00:00 0.0% oracle/1
NPROC USERNAME SIZE RSS MEMORY TIME CPU
184 oracle 196G 188G 94% 0:44:30 0.1%
1 vmpapp2 2540M 2313M 1.1% 10:24:56 6.3%
1 vmpapp3 1689M 1153M 0.6% 0:48:16 0.0%
4 vmpods5 1272M 935M 0.5% 0:09:12 0.0%
1 vmpods2 1270M 829M 0.4% 1:19:42 0.1%
1 vmpods4 1259M 821M 0.4% 1:15:39 0.0%
6 vmpapp1 1012M 537M 0.3% 0:06:59 0.0%
1 vmpapp4 1005M 516M 0.3% 0:06:23 0.0%
2 vmpapp5 978M 492M 0.2% 0:08:46 0.6%
1 vmpjms4 975M 479M 0.2% 0:04:49 0.0%
Total: 277 processes, 2763 lwps, load averages: 2.62, 2.61, 2.52

Многие утилиты Solaris не совсем правильно считают статистику по процессам, которые работают с разделяемой памятью (shared memory), в данном случае с разделяемой памятью Oracle.

Получается, что утилита prstat суммирует разделяемую память на все процессы oracle.

Кол-во процессов oracle:
oracle@myhost $ ps -ef grep ora wc -l
185

Общий размер SGA экземпляра oracle:
oracle@myhost $ sqlplus '/as sysdba'
SQL> show sga
Total System Global Area 1075651720 bytes

Если умножим кол-во процессов на общий размер SGA, то мы приблизимся к 188GB, которое нам показал prstat. Конечно, для более точного подсчёта, нужно добавить еще размер PGA под каждый процесс.

185 * 1 075 651 720 bytes = 198 995 568 200 bytes = 185GB

Табличные пространства в Oracle 10g

2008-04-19T12:36:00.000-07:00

В Oracle 10g появились следующие новые возможности по работе с табличными пространствами:

1) Возможность переименовать табличные пространства
2) Установка постоянного табличного пространства по умолчанию (default permanent tablespace)
3) Поддержка больших файлов (bigfile tablespaces)
4) Возможность переносить табличные пространства на другие платформы (cross platform transportable tablespaces)
5) Группы временных табличных пространств (temporary tablespace groups)
6) Передача файлов (DBMS_FILE_TRANSFER)

1) Возможность переименовать табличные пространства

SQL> select tablespace_name, count(*) from dba_segments where tablespace_name like 'USERS%' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
USERS 43

SQL> alter tablespace users rename to users_new;

Tablespace altered

SQL> select tablespace_name, count(*) from dba_segments where tablespace_name like 'USERS%' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
USERS_NEW 43

Правда, если вы не используете OMF, то файл данных не переименуется автоматически.

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like 'USERS%';

TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
USERS D:\ORACLE\PRODUCT\ORADATA\TESTDB\USERS01.DBF

SQL> alter tablespace users rename to users_new;

Tablespace altered

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like 'USERS%';

TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------------------------------------
USERS_NEW D:\ORACLE\PRODUCT\ORADATA\TESTDB\USERS01.DBF

Табличные пространства SYSTEM и SYSAUX нельзя переименовать таким образом.

SQL> alter tablespace sysaux rename to sysaux2;

alter tablespace sysaux rename to sysaux2

ORA-13502: Cannot rename SYSAUX tablespace

SQL> alter tablespace system rename to system2;

alter tablespace system rename to system2

ORA-00712: cannot rename system tablespace

При переименовании табличного пространства UNDO, ссылка на него в файле параметров тоже автоматически меняется после перегруза, если используется spfile.
Если экземпляр был старторван с pfile, но в alert.log выводится напоминание вручную обновить pfile.

SQL> show parameter pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\PRODUCT\10.2.0\DATAB
ASE\SPFILETESTDB.ORA
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> alter tablespace undotbs1 rename to undotbs_new;

Tablespace altered.

SQL>
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1292036 bytes
Variable Size 348129532 bytes
Database Buffers 255852544 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS_NEW
SQL>

2) Установка постоянного табличного пространства по умолчанию (default permanent tablespace)

SQL> alter database default tablespace example;

Database altered

SQL> select property_name, property_value from database_properties where property_name like 'DEFAULT_PERMANENT_TABLESPACE%';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE EXAMPLE

SQL> create user rahat identified by agivetova;

User created

SQL> select username, default_tablespace from dba_users where username ='RAHAT';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
RAHAT EXAMPLE

3) Поддержка табличных пространств состоящих из большого файла (bigfile tablespaces)

Одним из основных новинок в работе с табличными пространствами в Oracle 10g являются поддержка больших файлов.
Табличные пространства bigfile состоят только из 1 файла данных, который может расти до 128TB в зависимости от размера блока.
Например, если размер блока табличного пространства 8К, то табличное пространство может расти до 32ТВ.
Внизу таблица с максимальными размерами табличных пространств в зависимости от размера блока:

Размер блока табличного пространства Максимальный размер табличного пространства
2K 8TB
4K 16TB
8K 32TB
16K 64TB
32K 128TB

В представление dba_tablespaces добавлено поле bigfile, которое указывает, является ли табличное пространство - bigfile tablespace.

SQL> select tablespace_name, bigfile from dba_tablespaces;

TABLESPACE_NAME BIGFILE
------------------------------ -------
SYSTEM NO
UNDOTBS_NEW NO
SYSAUX NO
TEMP NO
USERS NO
EXAMPLE NO

6 rows selected

SQL> create bigfile tablespace big_users datafile 'D:\ORACLE\PRODUCT\ORADATA\TESTDB\BIG_USERS01.DBF' size 10M autoextend on next 10M;

Tablespace created

SQL> select tablespace_name, bigfile from dba_tablespaces;

TABLESPACE_NAME BIGFILE
------------------------------ -------
SYSTEM NO
UNDOTBS_NEW NO
SYSAUX NO
TEMP NO
USERS NO
BIG_USERS YES
EXAMPLE NO

7 rows selected

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

SQL> alter tablespace big_users add datafile 'D:\ORACLE\PRODUCT\ORADATA\TESTDB\BIG_USERS02.DBF' size 20M;

alter tablespace big_users add datafile 'D:\ORACLE\PRODUCT\ORADATA\TESTDB\BIG_USERS02.DBF' size 20M

ORA-32771: cannot add file to bigfile tablespace

По умолчанию, все табличные пространства создаются c smallfile, но эту настройку можно изменить следующей командой:

SQL> select property_name, property_value from database_properties where property_name like 'DEFAULT_TBS_TYPE';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TBS_TYPE SMALLFILE

SQL> alter database set default bigfile tablespace;

Database altered

SQL> select property_name, property_value from database_properties where property_name like 'DEFAULT_TBS_TYPE';

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TBS_TYPE BIGFILE

Теперь, команда create tablespace будет создавать bigfile табличные пространства.

Ошибка при установке SOA Suite 10g

2008-04-18T01:25:00.000-07:00

При установке SOA Suite 10g в Solaris 5.10, я столкнулась с такой ошибкой на последнем этапе инсталляции:
Configuration assistant "Oracle Application Server Configuration Assistant" failed

Так как данная настройка была необязательной, то есть "Optional", инсталляция в-общем считалась успешной. Но не стартовал ни один процесс, не стартовал сам Oracle Aplication Server.

Логи этого ассистента, которая завершилась неуспешно можно посмотреть в следующих файлах:

1) $ORACLE_BASE/oraInventory/logs/installActionstimestamp.log - сюда пишутся логи любого из неуспешно заверщенных ассистентов

2) $ORACLE_HOME/cfgtoollogs/configtoolstimestamp.log - здесь логи Oracle Application Server Configuration Assistant, с которой связана данная ошибка.
Здесь можно посмотреть куда кладутся логи других ассистентов.

3)$ORACLE_HOME/opmn/logs/opmn.log - один из логов OPMN, который тоже не стартовал после установки.
Здесь есть описание каждого из логов OPMN.

Логи из ~/oraInventory/logs/oraInstall2008-04-17_02-22-00PM.out:
Oracle JAAS [Thu Apr 17 14:27:05 MSD 2008] admin password is changed successfully
opmnctl: starting opmn and all managed processes...
opmnctl: opmn start failed.
--------------------------------------
The following configuration assistants have not been successfully completed. These assistants must be completed for your product to be completely configured.
Execute file /zones/u01/app/oas/product/oas10g/cfgtoollogs/configToolCommands to re-run all skipped/failed configuration assistants.
/zones/u01/app/oas/product/oas10g/jdk/bin/java -cp /zones/u01/app/oas/product/oas10g/j2ee/home/applications/ascontrol/ascontrol/WEB-INF/lib/ascontrol.jar:/zones/u01/app/oas/product/oas10g/j2ee/home/applications/ascontrol/ascontrol/WEB-INF/lib/log4j-core.jar:/zones/u01/app/oas/product/oas10g/jlib/oraclepki.jar:/zones/u01/app/oas/product/oas10g/jlib/ojmisc.jar: oracle.sysman.ias.studio.installer.ASControlConfigAssistant -sso true -j2eeinstance home -username oc4jadmin -password *Protected value, not to be logged* -oraclehome /zones/u01/app/oas/product/oas10g
/zones/u01/app/oas/product/oas10g/jdk/bin/java -jar /zones/u01/app/oas/product/oas10g/bpel/system/services/lib/bpm-install.jar installSOABasic -oracle-home "/zones/u01/app/oas/product/oas10g" -http-proxy-required false -dbvendor oracle -database myhost 1521 OASDB -username ORABPEL -password *Protected value, not to be logged* -ias-name APPSRV.myhost -iasadmin-password *Protected value, not to be logged* -sso true -homeContainer home -ohstype oc4j -ohshost myhost -ohsport 8889
/zones/u01/app/oas/product/oas10g/owsm/bin/wsmadmin.sh install
/zones/u01/app/oas/product/oas10g/perl/bin/perl /zones/u01/app/oas/product/oas10g/config/launchopmnCA.pl
/zones/u01/app/oas/product/oas10g/ant/bin/ant -buildfile /zones/u01/app/oas/product/oas10g/webservices/lib/wsil-install.xml -logfile /zones/u01/app/oas/product/oas10g/cfgtoollogs/wsil.txt -DHOST=myhost -DOPMNPORT="6003" -DADMIN_USER=oc4jadmin -DOPMNINSTANCE=home -Denv.JAVA_HOME=/zones/u01/app/oas/product/oas10g/jdk -Denv.ANT_HOME=/zones/u01/app/oas/product/oas10g/ant -Denv.ORACLE_HOME=/zones/u01/app/oas/product/oas10g -DENABLE_SSO=true *Protected value, not to be logged*
--------------------------------------

Вырезка из $ORACLE_HOME/cfgtoollogs/configtools2008-04-17_02-22-00PM.log:
------------------------------------------------
Launched configuration assistant 'Oracle Application Server Configuration Assistant'
------------------------------------------------

Tool type is: Optional.
The command being spawned is: '/zones/u01/app/oas/product/oas10g/jdk/bin/java -cp /zones/u01/app/oas/product/oas10g/j2ee/home/applic
ations/ascontrol/ascontrol/WEB-INF/lib/ascontrol.jar:/zones/u01/app/oas/product/oas10g/j2ee/home/applications/ascontrol/ascontrol/WE
B-INF/lib/log4j-core.jar:/zones/u01/app/oas/product/oas10g/jlib/oraclepki.jar:/zones/u01/app/oas/product/oas10g/jlib/ojmisc.jar: ora
cle.sysman.ias.studio.installer.ASControlConfigAssistant -sso true -j2eeinstance home -username oc4jadmin -password *Protected value
, not to be logged* -oraclehome /zones/u01/app/oas/product/oas10g'

Configuration assistant "Oracle Application Server Configuration Assistant" failed

Содержимое лог файла $ORACLE_HOME/opmn/logs/opmn.log: (Реальные айпи адреса заменены на 555.555.555.555)
08/04/17 14:27:07 [ons-internal] ONS server initiated
08/04/17 14:27:07 [pm-internal] Create pm state directory: /zones/u01/app/oas/product/oas10g/opmn/logs/states
08/04/17 14:27:07 [pm-internal] PM state file does not exist: /zones/u01/app/oas/product/oas10g/opmn/logs/states/.opmndat
08/04/17 14:27:07 [pm-internal] OPMN server ready. Request handling enabled.
08/04/17 14:27:07 [ons-listener] 555.555.555.555,6200: BIND (Cannot assign requested address)
08/04/17 14:33:46 [ons-internal] ONS server initiated
08/04/17 14:33:46 [pm-internal] PM state directory exists: /zones/u01/app/oas/product/oas10g/opmn/logs/states
08/04/17 14:33:46 [pm-internal] PM state file does not exist: /zones/u01/app/oas/product/oas10g/opmn/logs/states/.opmndat
08/04/17 14:33:46 [pm-internal] OPMN server ready. Request handling enabled.
08/04/17 14:33:46 [ons-listener] 555.555.555.555,6200: BIND (Cannot assign requested address)

Решение:

Причиной ошибки оказались настройки сети, о которой можно прочитать на металинке Note:549091.1 под темой OPMN Tries To Bind To Wrong IP Address During Install.

Вырезка из металинка Note:549091.1:

- The OPMN local port above should be binding to "localhost" i.e 127.0.0.1
- "ping localhost" resolves to 127.0.0.1
- "nslookup localhost" resolves to 127.0.0.1
- "nslookup 3.3.3.3" resolves to localhost.subdn.us.oracle.com

Cause
The problem here is caused by the fact "localhost" is stored within DNS. Normally localhost should not be in DNS.

Solution
-- To implement the solution, execute the following steps::
Either:
1. Remove localhost entry from DNS (preferred option)
Or:
2. Change the search order in /etc/resolv.conf so it searches the local domain first, so it reads:

search us.oracle.com uk.oracle.com subdns.us.oracle.com
nameserver 1.1.1.1
nameserver 2.2.2.2

У меня localhost определялся правильно, но оказалось, что у самого хоста оказалось 2 разных IP адреса:

oas@myhost $ ping -a myhost
myhost (333.333.333.333) is alive
oas@myhost $ ping -a myhost.domain.com
myhost.domain.com (555.555.555.555) is alive

Содержимое /etc/hosts:

root@myhost.domain.com # cat /etc/hosts
#
# Internet host table
#
127.0.0.1 localhost
333.333.333.333 myhost myhost.domain.com loghost

Из справочника по ОС Solaris (ссылка здесь):

On Solaris 10, IPv4 addresses are looked up in /etc/inet/ipnodes before /etc/inet/hosts.

Содержимое /etc/inet/ipnodes:

root@myhost.domain.com # cat /etc/inet/ipnodes
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost
555.555.555.555 myhost.domain.com loghost

Ошибка возникала из-за того, что IP адрес хоста был прописан неправильно в /etc/inet/ipnodes:
555.555.555.555 myhost.domain.com loghost

Теперь хост определяется правильно:
oas@myhost $ ping -a myhost
myhost (333.333.333.333) is alive
oas@myhost $ ping -a myhost.domain.com
myhost.domain.com (333.333.333.333) is alive

После исправления этой ошибки, деисталлировала Oracle Suite 10g и установила заново. Инсталляция прошла успешно.

Удаление репозитария Enterprise Manager Database Control

2008-04-17T06:11:00.000-07:00

При удалении Enterprise Manager Database Control repository с помощью команды

emca -deconfig dbcontrol db -repos drop

процесс может зависнуть на неопреленное время:

oracle@myhost$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 17, 2008 5:08:41 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: TESTDB
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 17, 2008 5:08:53 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /zones/u01/app/oracle/product/10.2.0/cfgtoollogs/emca/TESTDB/emca_2008-04-17_05-08-41-PM.log.
Apr 17, 2008 5:08:54 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 17, 2008 5:08:59 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...

Одной из причин, может быть то, что у пользователя SYSMAN есть активные сессии в базе данных. Вырезка из документации:

"While installing Enterprise Manager using existing database, Oracle Management Service configuration hangs while dropping the repository. This is due to active SYSMAN sessions connected to the database.

To resolve this issue, shutdown any existing Enterprise Manager sessions (both Grid Control and Database Control) or other SQLPLUS SYSMAN sessions."

Также, следует удалить файлы с расширением *.lck в директориях $ORACLE_HOME/cfgtoollogs/emca и $ORACLE_HOME/cfgtoollogs/emca/$ORACLE_SID.

Установка Oracle Application Server 10g

2008-04-16T08:42:00.000-07:00

Установить Oracle Application Server 10g можно по следующим шагам:
1) Загрузить и установить базу данных 10.2.0
2) Загрузить дистрибутивы Oracle Application Server 10g
2) Загрузить SOA схемы в созданную базу данных
3) Установить Oracle Application Server 10g

Подробней:

1) База данных будет использоваться для хранилища данных. Ее можно установить под тем же пользователем, можно и под другим, а можно и на отдельном сервере создать.

2) Загрузить дистрибутивы в зависимости от платформы и разархивировать:

cpio -idmv знак_меньше soa_Solaris_101310_disk1.cpio
cpio -idmv знак_меньше soa_Solaris_101310_disk2.cpio

3) Схемы SOA: ORABPEL, ORAESB, ORAWSM. Их можно загрузить с помощью скрипта irca.sh.
Скрипт является частью дистрибутива Oracle Application Server 10g и лежит в папке Disk1/install/soa_schemas/irca
irca - Integration Repository Creation Assistance

oracle@myhost$ sh irca.sh
Integration Repository Creation Assistant (IRCA) 10.1.3.1.0
(c) Copyright 2006 Oracle Corporation. All rights reserved.

Enter database "host port serviceName" [localhost 1521 orcl]: myhost 1521 OASDB
Enter sys password:
Running IRCA for all product(s):
connection="myhost 1521 OASDB", , orabpelUser=ORABPEL, esbUser=ORAESB, orawsmUser=ORAWSM

Validating database ...
Validating database character set ...
WARNING: The target database character set is CL8ISO8859P5
For multi-byte support, the AL32UTF8 character set is recommended.

Running prerequisite checks for ORABPEL ...
Enter password for ORABPEL:
Loading ORABPEL schema (this may take a few minutes) ...

Running prerequisite checks for ORAESB ...
Enter password for ORAESB:
Loading ORAESB schema (this may take a few minutes) ...

Running prerequisite checks for ORAWSM ...
Enter password for ORAWSM:
Loading ORAWSM schema (this may take a few minutes) ...

INFO: ORABPEL schema contains 225 valid objects.

INFO: ORAESB schema contains 180 valid objects.

INFO: ORAWSM schema contains 90 valid objects.

IRCA completed.
Please check for any ERROR message above and also check the log file
/var/tmp//irca2008-04-16_07-41-45PM.log for any error or other information.

oracle@myhost$

4) Установка сервера приложений:

./runInstaller

Я выбрала тип установки Advanced, чтоб самой выбрать опции установки.

Укажите параметры базы данных, которую вы создали:

Укажите пароли для схем ORABPEL, ORAESB, ORAWSM:

Следующий шаг:

Следующий:

Следующий...

И наконец:

Ошибка после установки патча 10.2.0.3

2008-04-16T07:42:00.000-07:00

После апгрейда 10.2.0.1 до 10.2.0.3, база не поднялась с такой ошибкой:

ORA-00202: control file: '/zones/u01/app/oracle/oradata/OASDB/control01.ctl'
ORA-27037: unable to obtain file status
SVR4 Error: 25: Inappropriate ioctl for device

Платформа: Solaris 10. Вырезка из металинка:
"The 10.2.0.3 patchset code changes attempted to use directio() calls in a manner not supported by the filesystem.

This issue affects VxFS filesystems which are typically managed by Veritas or Solstice. This issue may also affect QFS filesystems and SAM-FS filesystems. It is not known to affect UFS filesystems even if they are managed by Veritas or Solstice."

Решение: установка пачта 5752399.

Block Change Tracking в Oracle 10g

2008-04-05T03:39:00.000-07:00

Новая опция block change tracking в Oracle 10g позволяет отслеживать измененные блоки, чтобы при инкрементальном бэкапе через RMAN, не нужно было сканировать весь датафайл, чтоб отыскать измененные блоки, тем самым уменьшая время выполнения инкрементального бэкапа.

Чтобы включить эту опцию нужно выполнить следующую команду:
alter database enable block change tracking using file '/u01/app/oracle/admin/TESTDB/rman/block_change.log';

Это команда запустит новый background process CTRW (Change Tracking Writer), который будет логировать блоки, измененные с момента последнего бэкапа, в файл block_change.log.

Далее, можно увидеть статус отслеживания измененных блоков:
select * from v$block_change_tracking;

Выключение опции:
alter database disable block change tracking;

Подключение к iSQL*Plus как SYSDBA или SYSOPER в Oracle 10g

2008-04-04T07:07:00.000-07:00

О подключении к iSQL*Plus как SYSDBA или SYSOPER в Oracle 9i можно почитать здесь.
На http://hostname:5560/isqlplus невозможен доступ под SYSDBA или SYSOPER, если даже в Connect Identifier допишите "as sysdba" после названия базы данных:
ERROR - ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Чтобы подключиться к iSQL*Plus как SYSDBA или SYSOPER, нужно воспользоваться ссылкой http://hostname:5560/isqlplus/dba.
По умолчанию, доступ к этому URL закрыт, а при попытке попасть получите сообщение:

"Для входа на сервер по адресу iSQL*Plus DBA нужны имя пользователя и пароль. ..."

Для того, чтобы разрешить пользователю подключаться URL к iSQL*Plus DBA, нужно с помощью утилиты JAZN (Java AuthoriZatioN):

1) Создать пользователя для iSQL*Plus DBA URL
2) Назначить ему роль webDba
3) Выйти из jazn и при необходимости перегрузить iSQL*Plus.

Запуск утилиты jazn:

D:\>set ORACLE_HOME=D:\oracle\product\10.2.0

D:\>set JAVA_HOME=%ORACLE_HOME%\jdk

D:\>cd %ORACLE_HOME%\oc4j\j2ee\isqlplus\application-deployments\isqlplus

D:\oracle\product\10.2.0\oc4j\j2ee\isqlplus\application-deployments\isqlplus>%JAVA_HOME%\bin\java -Djava.security.properties=%ORACLE_HOME%\oc4j\j2ee\home\config\jazn.security.props -jar %ORACLE_HOME%\oc4j\j2ee\home\jazn.jar -user "iSQL*Plus DBA\admin" -password welcome -shell

JAZN:>

В unix среде команды аналогичные, только нужно заменить % на $, set на export, и слэши.

JAZN:> adduser "iSQL*Plus DBA" urldba urlpasswd
JAZN:>
JAZN:> listusers
iSQL*Plus DBA/admin
iSQL*Plus DBA/urldba
JAZN:>
JAZN:> grantrole webDba "iSQL*Plus DBA" urldba
JAZN:> exit

Тепер, при входе в http://hostname:5560/isqlplus/dba можно ввести логин (urladmin) и пароль пользователя (urlpasswd) и попасть в iSQL*Plus DBA URL, где есть возможность подключиться к базе данных как SYSDBA и SYSOPER.

Autoallocate vs. Uniform

2008-04-02T00:21:00.000-07:00

Сегодня в очередной раз столкнулась с дилеммой: какие табличные пространства создавать для продуктивной базы данных (OLTP, объем около 1тб) - uniform или autoallocate?

Смысл uniform в том, что экстенты выделяются одного размера. Например, если мы создаем табличное пространство:

CREATE TABLESPACE "LARGE_DATA" DATAFILE
'/kazcare/u01/oradata/KAZCARE/large_data${ORACLE_SID}01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO;

Экстенты таблицы, которая будет создана в этом табличном пространстве, будут одинакового размера - 16M.

А таблицы, созданные в следующем табличном пространстве будут автоматически регулироваться и размер каждого последующего экстента будет увеличиваться от 64K до 64M по мере роста сегмента.

CREATE TABLESPACE "ANY_DATA" DATAFILE
'/u01/oradata/TESTDB/any_data01.dbf' SIZE 1024M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

Металинк:

"If the tablespace is expected to contain objects of varying sizes requiring
different extent sizes and having many extents, then AUTOALLOCATE is the best
choice."

"On the other hand, if you want exact control over unused space, and you can
predict exactly the space to be allocated for an object or objects and the
number and size of extents, then UNIFORM is a good choice."

1 Апреля

2008-04-01T06:35:00.000-07:00

Don't send a lame St. Patrick's Day eCard. Try JibJab Sendables!

Создание Enhancement Request (Запрос на Улучшение)

2008-03-24T13:10:00.000-07:00

Если вы считаете, что в продуктах Oracle не хватает функциональности, которая была бы очень полезна, то можете завести Enhancement Request через Metalink. Не факт, что каждый созданный Enhancement Request будет вообще принят, и если даже будет принят, Oracle не обещает, что оно появиться в следующей версии продукта.
Это из металинка Note:214168.1:

"Customers can only file enhancement requests from MetaLink.

Please be aware that not all Enhancement Requests will be approved. The most common reason why an Enhancement is refused is because it is not in line with Oracle’s future product direction. ...
For Enhancement Requests that are approved for future releases, there is no guarantee that the Enhancement will take place in any specific release."

Как завести Запрос на Улучшение?
Логинитесь в Metalink, переходите в Service Request. Дальше нажимаете на Create SR, в появившемся окне указываете информацию о продукте в Product Description, а в блоке Problem Summary выбираете в Type of Problem -> Product Enhancement Request и описываете свои пожелания.

Например такой запрос на улучшение:
"Почему бы Ораклу не сделать возможным переименование пользователя одной командой?"
Один из моих моих хороших знакомых ДБА написал скрипт, который бы переименовал пользователя путём апдейта системных таблиц. Я сама скрипт еще не видела, но безопасно ли таким образом переименовывать пользователя в продуктивной базе данных?
Именно по этому вопросу уже существует Enhancement Request под номером 158508, но так как есть workaround к нему, Oracle считает, что пользователи пока могут и так обойтись:
Это из Note:14013.1:
Can you rename a user through Oracle?
Enhancement Request 158508.
Common workaround(s):
a - Do a user-level export of user A.
create new user B.
import dba/dba_passwd fromuser=A touser=B
drop user A.

Но например такой Enhancement Request (тоже из Note:14013.1):
Can you rename a tablespace?
Enhancement Request 148742.
Common workaround(s):
a - Export all of the objects from the tablespace.
Drop the tablespace including contents.
Recreate the tablespace.
Import the objects back in.

Наверно благодаря этому Enhancement Request возможность переименовать табличное пространство появилась в версии 10g:
ALTER TABLESPACE <oldname> RENAME TO <newname>;

Как вы считаете, какой функциональности не хватает в RDBMS Oracle?
Кстати, я перевела "Enhancement Request" как "Запрос на Улучшение"? Если у кого-то есть идеи получше, напишите о них.

Создание standby с помощью rman

2008-03-24T08:16:00.000-07:00

Поднятие standby из бэкапа с помощью rman имеет ряд преимуществ:
1) не нужно останавливать основную базу
2) не повлияет на производительность основной базы данных

Здесь я опишу главные моменты, где идет отличие от ручного поднятия standby. Предполагается, что настройка параметров инициализации для основной и резеврной БД, и другие моменты, уже знакомы.

И так, если еще нет полного бэкапа основной БД, то нужно ее забэкапить:
rman target / catalog rman/mypassword@RMCAT
RUN {
ALLOCATE CHANNEL ch1 TYPE 'SBT_TAPE';
CROSSCHECK ARCHIVELOG ALL;
BACKUP
INCREMENTAL LEVEL=0
FILESPERSET 5 FORMAT 'bk_%s_%p_%t'
DATABASE include current controlfile for standby;
BACKUP FORMAT 'al_%s_%p_%t' ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch1;
}

Дальше, на резервном сервере, после поднятия резервной БД в nomount:
rman
connect catalog rman/password@CATDB
connect target sys/"mypassword"@mydb
connect auxiliary /
run{
ALLOCATE AUXILIARY CHANNEL c1 TYPE 'SBT_TAPE' parms="ENV=(NB_ORA_CLIENT=primary_host, NB_ORA_SERV=backupsrv)";
duplicate target database for standby dorecover NOFILENAMECHECK;
}

Здесь список ошибок, которые могут возникнуть, и их решения:
Ошибка №1:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of allocate command on c1 channel at 03/24/2008 19:59:18
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library
Additional information: 2

Это из документации Oracle:
"The most important line of the error output is the ORA-27211 error. It indicates the basic problem, that the media management library could not be loaded. ......By default, the database expects to find the media management library at $ORACLE_HOME/lib/libobk.so on UNIX, or %ORACLE_HOME%/bin/orasbt.dll on NT."

Решение от Виталия Воронцова, Oracle DBA и продвинутого пользователя по Netbackup:
слинковать библиотеку Netbackupа
oracle@myhost$ ./usr/openv/netbackup/bin/oracle_link
Mon Mar 24 20:09:47 UZT 2008
All Oracle instances should be shutdown before running this script.
Please log into the Unix system as the Oracle owner for running this script
Do you want to continue? (y/n) [n] y
LIBOBK path: /usr/openv/netbackup/bin
ORACLE_HOME: /u01/app/oracle/product/9.2.0.7.0
Oracle version: 9.2.0.7.0
Linking LIBOBK:
ln -s /usr/openv/netbackup/bin/libobk.so64.1 /u01/app/oracle/product/9.2.0.7.0/lib/libobk.so
Done
Please check the trace file located in /tmp/make_trace.6960
to make sure the linking process was successful.

Ошибка №2:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/25/2008 17:49:18
RMAN-03015: error occurred in stored script Memory Script
ORA-19507: failed to retrieve sequential file, handle="bk_15539_1_650240673", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
Failed to process backup file

Это из документации Veritas NetBackup 6 for Oracle: System Administrator's Guide:

"Ensure that the NetBackup server is configured to allow a redirected restore. The administrator can remove restrictions for all clients by creating the following file on the Netbackup master server:
/usr/openv/netbackup/db/altnames/No.Restrictions
Or, to restrict clients to restore only from certain other clients, create the following file:
/usr/openv/netbackup/db/altnames/client_name
Where client_name is the name of the client allowed to do the redirected restore (the destination client). Then, add the name of the NetBackup for Oracle source client to that file."

Установка OAS 10g на Windows Vista

2008-03-21T16:00:00.000-07:00

Установка Oracle Application Server 10g на Windows Vista завершилась безуспешно:

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.0, 5.1 or 5.2 . Actual 6.0
Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at C:\User
s\Raku\AppData\Local\Temp\OraInstall2008-03-22_02-00-54AM\installActions2008-03-
22_02-00-54AM.log

Please press Enter to exit...

Жаль, что OAS10g не поддерживается в Windows Vista. Конечно можно обойти это, добавив в файле oraparam 6.0: Windows=5.0,5.1,5.2,6.0, но не факт, что будет работать без неприятных сюрпризов.

Временный останов передачи архивных логов на standby

2008-03-21T06:17:00.001-07:00

останов:
alter system set log_archive_dest_state_?='defer' scope=both;

возобновление:
alter system set log_archive_dest_state_?='enable' scope=both;

ORA-01630

2008-03-19T03:50:00.000-07:00

ORA-01630: max # extents (505) reached in temp segment in tablespace TEMP2

Решение:
ALTER TABLESPACE SYSTEM default storage (MAXEXTENTS UNLIMITED);
либо пересоздать temporary tablespace - locally managed tablespace.

Проблемы с listener ORA-12535: TNS:operation timed out в Oracle 9i

2008-03-13T07:57:00.000-07:00

Одной из причин таких ошибок в часы пик может быть то, что листенер всего-навсего не успевает обработать поступающие запросы на подключение.
Например, когда одновременно запускаются очень много клиентских джобов, которые все в один момент обрушиваются на листенер, листенер успевает обработать только первые поступившие запросы, а остальные падают с ошибкой ORA-12535.

Для борьбы с ними можно попробовать следующие варианты:

1) Параметр INBOUND_CONNECT_TIMEOUT
Можно попробовать установить параметр в файле listener.ora: INBOUND_CONNECT_TIMEOUT_ = 600
Этот параметр задает кол-во секунд, в течение которых должна быть завершена обработка запроса клиента. Если за указанное время листенер не успевает обработать запрос клиента на подключение, он выдает ошибку ORA-12535 и обрывает соединение с клиентом.
Это аналог параметра CONNECT_TIMEOUT, который является устаревшим в версии 9i.

Еще можно попробовать в файле sqlnet.ora добавить параметр:
SQLNET.INBOUND_CONNECT_TIMEOUT = 700

2) Параметр QUEUESIZEС помощью этого параметра можно установить кол-во запросов, которые может обрабатывать листенер одновременно.

LISTENER_TESTDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2483)(QUEUESIZE=200))
)
SID_LIST_LISTENER_TESTDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB)
)
)

3) Поднятие дополнительных листенеров
Установка параметров INBOUND_CONNECT_TIMEOUT и QUEUESIZE не всегда может помочь, так как листенер работает с той же скоростью, а те запросы, которые ожидают подключения к базе, всего-навсего будут дольше удерживаться, пока не рассосется очередь. Но если очередь все-таки не рассосется за указанное время, клиенты опять упадут с ошибкой ORA-12535.

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

И так, на сервере поднимаем 2 листенера на 2 разных портах 2483 и 2484.
В файле listener.ora прописываем:

LISTENER_TESTDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2484))
)
SID_LIST_LISTENER_TESTDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB)
)
)

LISTENER_TESTDB2 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = myhost)(Port = 2483))
)
SID_LIST_LISTENER_TESTDB2 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB2)
(ORACLE_HOME = /testdb/u01/app/oracle/product/9.2.0.8.0)
(SID_NAME = TESTDB2)
)
)

Запускаем листенеры:
oracle@myhost $ lsnrctl
LSNRCTL> start LISTENER_TESTDB
LSNRCTL> start LISTENER_TESTDB2

Теперь нужно на стороне клиента прописать в tnsnames.ora:
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 2483))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 2484))
)
(CONNECT_DATA = (SERVICE_NAME = TESTDB))
)
Теперь можно в $ORACLE_HOME/network/log посмотреть размеры лог файлов каждого листенера. Они будут расти по мере обработки запросов на подключение.

oracle@myhost $ ls -la *.log

Про лягушку

2007-12-11T07:53:00.000-08:00

В Тридевятом царстве, в тридесятом государстве жила-была прекрасная, независимая, самостоятельная и умная принцесса. Вот однажды сидела она на берегу живописного пруда в зеленой долине близ своего замка, размышляла о смысле жизни и вдруг увидала лягушку. Лягушка прыгнула ей на колени и сказала:

"Милая, добрая девушка. Когда-то я был прекрасным принцем, но злая колдунья заколдовала меня, превратив в лягушку.Если ты меня поцелуешь, я снова превращусь в принца, и тогда, моя прелесть, я поселюсь в твоем замке, а ты будешь готовить мне еду, чистить моего коня, стирать мою одежду, растить моих детей и радоваться, что я взял тебя в жены."

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

Роль CONNECT в 10g

2007-11-22T04:11:00.000-08:00

Только сегодня заметила, что в 10g роль CONNECT лишили всех системных привилегий, оставили только CREATE SESSION.

...beginning in Oracle Database 10g Release 2 (10.2), the CONNECT role has only the CREATE SESSION privilege, all other privileges are removed.

Объясняют это тем, что:
"Making this change enables new and existing database customers to enforce good security practices more easily."

Oracle Critical Patch Update

2007-11-16T02:53:00.000-08:00

Critical Patch Update выпускается Ораклом каждый квартал в виде патчей для устранения критических багов и уязвимостей.
Раньше были security alerts, но теперь только critical patch update.
http://www.oracle.com/technology/deploy/security/alerts.htm

Последний CPU был в выпущен в октябре.
Кто-нибудь ставил их на Oracle Database 10g?

1Z0-033

2007-11-16T01:12:00.000-08:00

сдала экзамен, все ок, теперь жду письма с моим сертификатам от Оракла:)
Кстати, не забудьте заполнить Hands-on Course Requirement Form - для того, чтобы Оракл подтвердил, что вы прослушали один обязательный курс. Без него, письма придется ждать бесконечно долго.

1Z0-033

2007-11-09T07:46:00.000-08:00

Говорят, экзамен 1Z0-033 - самый сложный из трэка OCP 9i. Посмотрим...
Вопросы по тюнингу Oracle Shared Server не вдохновляют. Если честно, ни в одной компании не видела shared server в продуктиве.

как заменить ^M в юниксе

2007-06-22T05:15:00.000-07:00

Виндузовый перевод строки в юниксе будет выглядеть как ^M.
Чтобы удалить их, откройте файл в vi и наберите следующую команду:

:%s/^M$//g

Для того, чтобы набрать ^M нужно нажать CTRL-V CTRL-M.

checking sqlplus connection succeeded

2006-06-22T04:22:00.000-07:00

#!/usr/bin/bash
INICIO=$(sqlplus -s user/password@dbname<< END awk '{printf $1}')

if [ -z ${INICIO} ]
then
echo "connected";
else
echo "not connected";
fi