Включение/выключение режима архивных логов в Oracle DB

перепост http://vistababa.wordpress.com/2008/06/12/how-to-turn-archivelog-mode-on-and-off-in-oracle/

Включение режима архивных логов
Вначале убедимся, что БД не в режима архивных логов

SQL> select log_mode from v$database;
LOG_MODE
————
NOARCHIVELOG

или

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7193
Current log sequence 7194
SQL>

Переводим в режим архивных логов:

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter database open;

Проверяем:

SQL> select log_mode from v$database;
LOG_MODE
———-
ARCHIVELOG
SQL>

Убеждаемся:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195

Чтобы работало после перезагрузки запишем в файл параметров: 

SQ> alter system set log_archive_start=TRUE scope=spfile;

и перезагрузим БД
(параметр LOG_ARCHIVE_START является устаревшим с 10.1. Автоматическое архивирование достигается переводом базы в ARCHIVELOG состояние (http://docs.oracle.com/cd/E18283_01/server.112/e17222/changes.htm)

Note1: Необходимо сразу снять резервную копию.
Note2: Желательно установить параметры init.ora: log_archive_dest, log_archive_dest_1, log_archive_format

Выключение режима архивных логов  
Убеждаемся, что БД в режиме архивных логов:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195
SQL>

Выключаем:

SQL> startup mount excluseve;
SQL> alter database noarchivelog;
SQL> alter database open;

Проверяем:

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7194
Current log sequence 7195
SQL>

Note1: Все предыдущие копии архивных логов идут в /dev/null.

Битовые индексы соединения Oracle DB (bitmap join indexes, BJI)

(http://www.interface.ru/fset.asp?Url=/oracle/or9inewvozmojnosti.htm)

Невозможно оставить без внимания ещё одно оригинальное новшество – битовые индексы соединения (bitmap join indexes, BJI). Это обычные битовые индексы, но построенные по столбцам, которых в индексируемой таблице нет. Например, в таблице продаж (назовем её по традиции Sales) есть код региона продажи, но нет его названия. Название есть в справочнике регионов (Regions). В то же время запрос, выбирающий все продажи по региону с заданным названием, довольно типичен. При его выполнении каждый раз требуется соединение (join) таблиц Sales и Regions. Рассмотрим пример.

Создадим таблицы:

create table Regions
(
region_id number primary key,   -- первичный ключ обязателен для создания BJI
region_name varchar2(200)
);
create table Sales
(
id number primary key,
region_id number,               -- опустим ограничения внешнего ключа
product_id number,
amount number,
sal_date date
);

Выполним запрос:

Select sum(amount)
from sales, regions
where
     regions.region_id = sales.region_id
 and regions.region_name = 'Центр'

Рассмотрим план выполнения этого запроса (это не единственно возможный, но вполне вероятный план). В плане, естественно, присутствует просмотр обеих таблиц и операция их соединения:

SELECT STATEMENT Optimizer=ALL_ROWS
SORT (AGGREGATE)
NESTED LOOPS
  TABLE ACCESS (FULL) OF 'SALES'
  TABLE ACCESS (BY INDEX ROWID) OF 'REGIONS'
    INDEX (UNIQUE SCAN) OF 'REGIONS_PK' (UNIQUE)

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

Создадим индекс:

create bitmap index Sales_reg_bji
on Sales(regions.region_name)
from Sales, Regions
where sales.region_id=regions.region_id

Чтобы побудить оптимизатор использовать этот индекс, применим подсказку (hint). В реальности, если таблицы будут достаточно большими, подсказки скорее всего не понадобятся, т.к. стоимость выполнения этого запроса с использованием индекса будет существенно меньше, чем без него:

select /*+ index(sales sales_reg_bji) */
 sum(amount) from sales, regions
 where
       regions.region_id   = sales.region_id
   and regions.region_name = 'Центр'

Рассмотрим план выполнения и убедимся, что просмотр таблицы Regions и операция соединения исчезли:

SELECT STATEMENT Optimizer=ALL_ROWS
SORT (AGGREGATE)
  TABLE ACCESS (BY INDEX ROWID) OF 'SALES'
    BITMAP CONVERSION (TO ROWIDS)
      BITMAP INDEX (SINGLE VALUE) OF 'SALES_REG_BJI'

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

Каркасные планы выполнения и их применение в практике

на тестовом сервере:

SQL> connect system as sysdba
SQL> @e:\oracle\ora92\rdbms\admin\dbmsol.sql

SQL> grant create any outline to LOGIN;
SQL> grant execute on dbms_outln to LOGIN;
SQL> grant execute on dbms_outln_edit to LOGIN;
SQL> ALTER SYSTEM SET CREATE_STORED_OUTLINES = TRUE;

далее параллельно запускаем проблеммное приложение, заставляем его делать проблеммные запросы
после этого:

SQL> ALTER SYSTEM SET CREATE_STORED_OUTLINES = FALSE

проконтроллировать полученные каркасные планы и хинты можно так:
select * from user_outlines;
select * from user_outline_hints;
название конкретно нужного плана можно посмотреть по user_outlines.sql_text

далее необходимо править хинты у полученных каркасных планов:
подготовка:
SQL> connect LOGIN
SQL> execute dbms_outln_edit.create_edit_tables;

копирование в приватный каркасный план (править можно только его):
SQL> create private outline priv_outln1 from SYS_OUTLINE_081007125758140;
SQL> column hint# format 999999
SQL> column hint_text format a28
SQL> column user_table_name format a16
SQL> set pages 1000
SQL> select hint#, hint_text, user_table_name from ol$hints where ol_name = 'PRIV_OUTLN1';

HINT# HINT_TEXT USER_TABLE_NAME
------- ---------------------------- ----------------
1 NOREWRITE
2 RULE
3 NOREWRITE
4 NO_EXPAND
5 USE_NL(LR)
6 USE_NL(CT)
7 USE_NL(LZ)
8 USE_NL(CN
9 ORDERED
10 NO_FACT(LR)
11 NO_FACT(CT)
12 NO_FACT(LZ)
13 NO_FACT(CN
14 NO_FACT(CX)
15 AND_EQUAL(CX M2_CTSXR_LTTLZ_FRGN M2_CTSRX_LTTPN_FRGN)
16 INDEX(CN M2_CITNM_PK)
17 INDEX(LZ M2_LTZON_PK)
18 INDEX(CT M2_CTS_PK)
19 INDEX(LR M2_LTMTSRG_PK)

SQL> update ol$hints set hint_text='USE_HASH(LR)' where hint# = 5;
SQL> update ol$hints set hint_text='USE_HASH(CT)' where hint# = 6;
SQL> update ol$hints set hint_text='USE_HASH(LZ)' where hint# = 7;
SQL> update ol$hints set hint_text='USE_HASH(CN)' where hint# = 8;
SQL> select hint#, hint_text, user_table_name from ol$hints where ol_name = 'PRIV_OUTLN1';

HINT# HINT_TEXT USER_TABLE_NAME
------- ---------------------------- ----------------
1 NOREWRITE
2 RULE
3 NOREWRITE
4 NO_EXPAND
5 USE_HASH(LR)
6 USE_HASH(CT)
7 USE_HASH(LZ)
8 USE_HASH(CN)
9 ORDERED
10 NO_FACT(LR)
11 NO_FACT(CT)
12 NO_FACT(LZ)
13 NO_FACT(CN)
14 NO_FACT(CX)
15 AND_EQUAL(CX M2_CTSXR_LTTLZ_FRGN M2_CTSRX_LTTPN_FRGN)
16 INDEX(CN M2_CITNM_PK)
17 INDEX(LZ M2_LTZON_PK)
18 INDEX(CT M2_CTS_PK)
19 INDEX(LR M2_LTMTSRG_PK)

SQL> commit;
SQL> execute dbms_outln_edit.refresh_private_outline('PRIV_OUTLN1');

копируем приватный обратно в общий:
SQL> create or replace outline SYS_OUTLINE_081007125758140 from private priv_outln1;

переименовываем и перекидываем в другую группу (для переноса на ПРОД):
SQL> alter outline SYS_OUTLINE_081007125758140 rename to taraandr001;
SQL> alter outline taraandr001 change category to TARAANDR;

и очистим дефолтную группу, дабы не мешалось:
SQL> exec outln_pkg.drop_by_cat('DEFAULT');

проконтроллируем, что остались только нужные:
select * from user_outlines;

далее переносим с ТЕСТа:
exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) statistics=none
на ПРОД:
imp system/ file=myoutln.dmp full=y ignore=y

и далее на ПРОДе включаем использование:
SQL> alter system set use_stored_outlines = TARAANDR;

используются до следующего рестарта базы.
для постояшного включения при перезагрузке (используется категория DEFAULT):
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;

67536.1 Stored Outline Quick Reference
144194.1 Editing Stored Outlines in Oracle9i - an example
728647.1 How to Transfer Stored Outlines from One Database to Another (9i and above)
560331.1 How to Enable USE_STORED_OUTLINES Permanently

дополнительно:
132547.1 Using Stored Outlines