помогите с Oracle

[Хирург]

Привет, есть вопрос, мне нужно этот код сделать для Оракла, поскольку оракла нету, вопрошаю ваше мнение:

-- Sybase:

create table CustPref
(
id integer DEFAULT AUTOINCREMENT,
product varchar(255) not null,
name varchar(255) not null,
value varchar(255) not null,
customer varchar(255) not null,
primary key clustered (id)
)

create nonclustered index bk_index on CustPref (product, name, customer)

Я сделал так, потестить негде, очень нужна помощь, спасибо!

-- Oracle:

CREATE TABLE CustPref (
id INT PRIMARY KEY,
product VARCHAR2(255) not null,
name VARCHAR2(255) not null,
value VARCHAR2(255) not null,
customer VARCHAR2(255) not null
);

CREATE INDEX bk_index ON CustPref (product, name, customer);

CREATE SEQUENCE custpref_sequence START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE TRIGGER custpref_trigger
BEFORE INSERT
ON CustPref
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT custpref_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

[Clink]

Re: помогите с Oracle

Привет.

Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0
Connected as rep

SQL>
SQL> CREATE TABLE CustPref (
2 id INT PRIMARY KEY,
3 product VARCHAR2(255) not null,
4 name VARCHAR2(255) not null,
5 value VARCHAR2(255) not null,
6 customer VARCHAR2(255) not null
7 );

Table created
SQL> CREATE INDEX bk_index ON CustPref (product, name, customer);

Index created
SQL> CREATE SEQUENCE custpref_sequence START WITH 1 INCREMENT BY 1;

Sequence created
SQL> CREATE OR REPLACE TRIGGER custpref_trigger
2 BEFORE INSERT
3 ON CustPref
4 REFERENCING NEW AS NEW
5 FOR EACH ROW
6 BEGIN
7 SELECT custpref_sequence.nextval INTO :NEW.ID FROM dual;
8 END;
9 /

Trigger created

SQL> INSERT INTO custpref VALUES (null, 'product1', 'name1', 'value1', 'customer1');

1 row inserted

SQL> INSERT INTO custpref VALUES (null, 'product2', 'name2', 'value2', 'customer2');

1 row inserted

SQL> INSERT INTO custpref VALUES (null, 'product3', 'name3', 'value3', 'customer3');

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT id, product FROM custpref;

ID PRODUCT
--------------------------------------- ---------------------------------------------
1 product1
2 product2
3 product3

SQL> DROP TABLE custpref;

Table dropped

SQL> DROP SEQUENCE custpref_sequence;

Sequence dropped

SQL>

[Хирург]

Re: помогите с Oracle

Привет.

огромное спасибо, земляк!

[dddd]

Re: помогите с Oracle

... INCREMENT BY 1;

Sequence created
SQL> CREATE OR REPLACE TRIGGER custpref_trigger
2 BEFORE INSERT
...
5 FOR EACH ROW

Под реальной нагрузкой работать не будет ни фига

[Хирург]

а как надо?

[Clink]

Re: помогите с Oracle

... INCREMENT BY 1;

Sequence created
SQL> CREATE OR REPLACE TRIGGER custpref_trigger
2 BEFORE INSERT
...
5 FOR EACH ROW

Под реальной нагрузкой работать не будет ни фига

В смысле не будет? А куда оно денется?
Работать будет по любому, а вот как быстро - это уже зависит от используемого железа и от смысла,
вкладываемого в каждом конкретном случае в понятия "реальная нагрузка" и "быстро"

Ради интереса посмотрел навскидку - просто пример из живой, так сказать, жизни:
Имеем - крупная рroduction система. На табличке X висит очень похожий триггер (даже чуть больше кода),
таким же образом генерирующий primary key для записи при операции INSERT.
Такой же sequence, правда параметр CACHE выставлен в 400.

За 1 час работы в штатном режиме:

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------

467,251 467,223 1.0 0.00 0.00 4107684946
INSERT INTO X...

т.е. вставка порядка 130 записей в секунду. Работает и не жужжит.
И это далеко не единственная таблица в БД с подобными триггерами и подобным insert rate.
Можно ли считать это реальной нагрузкой? - По-моему можно.
Хотя замечу, что железо используется конечно весьма и весьма приличное.

Так что смотреть надо что и как в каждом конкретном случае...

[Хирург]

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

[dddd]

Реальная нагрузка - 3-5 тысяч транзакций в секунду.

Все будет висеть на sequence с возрастанием 1 и без кэша.

[Z1024]

Реальная нагрузка - 3-5 тысяч транзакций в секунду.

Все будет висеть на sequence с возрастанием 1 и без кэша.

И как же сделать чтобы работало под такой загрузкой ? Чиста любопытно )

[dddd]

Ну, наверное не использовать sequences?

По-разному народ делает.
В основном делают sequence с кэшированием, что дает разрывы в значениях.

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

По-разному, короче, делают. И за все деньги берут с тех кто SQL.ru читать не хочет

[Z1024]

Ну, наверное не использовать sequences?

А чего, новый nextval или как его там - тяжелая операция что ли?
Я думал месье на триггер грешит...

[Хирург]

По-разному, короче, делают. И за все деньги берут с тех кто SQL.ru читать не хочет

ну там где вопрос во многих тысячах операций в секунду на оракле, вопрос в деньгах и спецах (не с sql.ru) особо не стоит, я думаю...

[Clink]

Реальная нагрузка - 3-5 тысяч транзакций в секунду.

Все будет висеть на sequence с возрастанием 1 и без кэша.

Не критики ради, а ясности для...

Простите, это почему же без кэша?
При создании последовательности CREATE SEQUENCE ... START WITH 1 INCREMENT BY 1;
т.е. без задания параметра CACHE как это было написано у Хирурга, устанавливается дефолтовый размер кэша,
а именно 20 (для Oracle версий 8i, 9i, 10g). Чтобы последовательность не кэшировалась, нужно явным образом прописывать NOCACHE.
Иначе она всегда кэшируется

Однозначно соглашусь в том, что некэшированный sequence однозначно является узким местом, он работает медленнее даже при одной монопольно использующей его сессии, за счет постоянного выполнения update seq$. В случае же интенсивного параллельного использования некэшированной последовательности, тормоза как правило оказываются очень и очень значительными (по времени ожидания могут быть на порядок-другой больше) - сессии просто тупо висят на row cache lock над табличкой seq$. Поэтому использовать sequence c NOCACHE я бы вообще крайне не рекомендовал без веской на то причины.

С другой стороны, sequence c адекватно выставленным значением CACHE, на нормальном железе может отрабатывать быстро
даже при Вашем определении реальной нагрузки. Я потестил ради интереса вставку в табличку Хирурга, с его же триггером
и его же последовательностью, с дефолтовым CACHE = 20.
Железо - скорее среднее, нежели hi-end, Oracle 10g, режим NOARCHIVELOG - для чистоты эксперимента, т.к. мы предполагаем что узким местом будет именно последовательность, а не подсистема ввода-вывода.
Каждая сессия вставляет по миллиону записей, потом делает COMMIT. Сессии стартуют одновременно.

1 single session -- 1 млн. записей -- 78,3 сек -- 12771 зап. / сек.
2 concurrent session -- 2 млн. записей -- 109,6 сек -- 18248 зап. / сек
4 concurrent session -- 4 млн. записей -- 158,3 сек -- 25268 зап. / сек.
8 concurrent session -- 8 млн. записей -- 448,0 сек -- 17857 зап. / сек.
16 concurrent session -- 16 млн. записей -- 976,7 сек -- 16381 зап. / сек.

Уже на 8 параллельно выполняемых сессиях начинаются разные нехорошие ожидания, а именно:
buffer busy wait на вставке (~ 14% времени работы сессии), и
enq: SQ - contention - т.е. непосредственно интересующая нас конкуренция за sequence (~ 16% работы сессии)

Попробуем увеличить CACHE (скажем до 400):

8 concurrent session -- 8 млн. записей -- 285,3 сек -- 28040 зап. / сек.
16 concurrent session -- 16 млн. записей -- 706,2 сек -- 22653 зап. / сек.

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

На 8 параллельно выполняемых сессиях:
buffer busy wait на вставке (~ 23% времени работы сессии), и (sic!)
enq: SQ - contention - т.е. конкуренция за sequence (~ 2 % работы сессии) Всего лишь 2%.

С увеличением числа сессий до 16 получаем еще более интересную картину -
buffer busy wait на вставке (~ 46,6% времени работы сессии),
enq: SQ - contention - т.е. конкуренция за sequence (~ 0,5 % работы сессии).

О чем это говорит?

A) О том, что мы можем значительно минимизировать влияние конкуренции за sequence путем установки для последовательности адекватного значения CACHE.
и
B) О том что основной угрозой масштабируемости при увеличении числа параллельно вставляющих сессий выступает отнюдь не конкуренция за sequence, а сама интенсивность инсертов - buffer busy wait на инсертах начинает быстро расти до неприемлемых величин. И думать надо именно над этим, а не о том использовать ли sequence или нет.

(Хочу заметить, что у Oracle 9i все гораздо более запущено - хотя там тоже основные проблемы лежат не на уровне последовательности как таковой, там они являются прямым следствием чересчур интенсивного чтения сессиями таблицы dual. Т.е. в реальной жизни сессии значительное время будут захлебываться от latch free (cache buffer chain latch) над блоком принадлежащим таблице dual (причем на 9i в это же упирается и альтернативный метод получения уникального значения - sys_guid). В 10g с этим гораздо лучше.)

Не знаю, наверное это все не совсем к месту - слишком много и непонятно написано... Ну да ладно.

Смысл вышеизложенного - мне кажется, на более-менее нормальном железе, при адекватно выставленном CACHE последовательности, Oracle легко и непринуженно проглотит вставку с рейтом 3-5 тыс. записей в секунду с выполнением приведенного Хирургом триггера и даже не поморщится.

Ну, наверное не использовать sequences?

По-разному народ делает.
В основном делают sequence с кэшированием, что дает разрывы в значениях.

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

По-разному, короче, делают. И за все деньги берут с тех кто SQL.ru читать не хочет

То что делают по разному - это да. Есть в том числе и варианты для тех, кто по каким-то причинам не не хочет дергать sequence очень часто. Например выставляя скажем INCREMENT BY в 100001, и один раз получив значение nextval, пачкой загонять сразу 100000 записей, самостоятельно прибавляя по единице для каждой последующей записи. Как я и говорил, все зависит от конкретной ситуации.

"Не вставляют значения ключа сразу". Тут несколько непонятно. А когда его вставляют? Просто интересно, как Вы себе представляете отложенную вставку ключа? UPDATE по ночам всех тех сотен миллиардов записей, что навставляли днем во время "реальной нагрузки"?
Не слишком ли большая цена за отказ использовать последовательности? Хотя, конечно, случаи разные бывают.
В любом случае, даже если допустить такую экзотику в каких либо обстоятельствах - это никак не катит в случае primary key.

Как ни крути, вариантов получить уникальное значение не так много - sequence, sys_guid() или самодельные синтетические значения (что в общем случае не всегда просто реализовать, особенно в случае массовой параллельной вставки). Все же для большинства задач (даже при достаточно интенсивной нагрузке), использование sequence будет являться вполне эффективным решением.


P.S. А что именно Вы имели в виду упоминая "значения, услужливо подготовленные ораклом для его внутренних целей"?