Dnes jsem musel nějakým způsobem poladit na testovací databázi DDL a DML operace.Daný skript nejdříve smazal schéma a poté ho vytvořil celé znovu. To znamená, že zde je spouštěno velké množství CREATE TABLE, INSERT INTO, CREATE OR REPLACE TRIGGER, CREATE OR REPLACE PACKAGE, CREATE VIEW atd.Podle logu jsem zjistil, že dochází hodně často k přepínání redo logu.Vypnul jsem tedy logování na úrovni celé databáze. Samozřejmně je možné vypnou logování i na určité tablespaces, do kterých se zapisuje.A druhá věc, na kterou jsem se zaměřil je ladění COMMIT, tedy potvrzování transakcí. Zde je možné nastavit vícero parametrů, které jsou na různých úrovních.Nastavíte-li jeden vypne se vám druhý. Jelikož se jednalo o testovací prostředí mohli jsme otestovat a nastavit parametr tak, aby daná operace byla co nejrychlejší.
Parametry, kterých se ladění týká jsou následující:
Defaultní nastavení
SQL> show parameter commit;NAME TYPE VALUE———————————— ———– ——————————commit_logging stringcommit_point_strength integer 1commit_wait stringcommit_write stringmax_commit_propagation_delay integer 0
Rozsah hodnot
COMMIT_WRITE = {IMMEDIATE | BATCH},{WAIT | NOWAIT }COMMIT_WAIT = { NOWAIT | WAIT | FORCE_WAIT }COMMIT_POINT_STRENGTH = hodnoty od 0 do 225COMMIT_LOGGING = { IMMEDIATE | BATCH }Redo logu se týká paramater commit_write, který určuje jakým způsobem budou data potvrzovaná.Parametr může být nastaven na úrovni session (připojení) nebo system (celé databáze)ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’WAIT‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’NOWAIT‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,WAIT‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’BATCH,NOWAIT‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,WAIT‘;ALTER [SYSTEM | SESSION] SET COMMIT_WRITE=’IMMEDIATE,NOWAIT‘;
IMMEDIATE – zápis do redo logu je ihned po commitBATCH – zápis do redo logu je v dávkách.WAIT – synchronní potvrzování. Informace o tom, že data jsou uložena je předána, jakmile dojde ke skutečnému zápisu do redo loguNOWAIT – Nečeká se na potvrzení o uložení dat do redo logu. dá se jen příkaz a bere se jako provedený. Při poškození disku může dojít ke ztrátě dat. Stejně jako v módu BATCH.V 11G je defaultní nastavení IMMEDIATE,WAIT
Udělal jsem dva testy, tak že jsem vytvořil testovací tabulky a do nich vkládal data s různým nastavení parametru commit_write. Třetí hodnoty vycházeli přímo z konkrétního skriptu.
Přikládám tabulku s výsledky (test case je přiložen na konci článku):
Parametr
test 1
test 2
skript
COMMIT WRITE WAIT
365
04.43
1m32.722s
COMMIT WRITE NOWAIT
21
03.47
1m4.399s
COMMIT WRITE BATCH
285
05.42
0m59.221s
COMMIT WRITE IMMEDIATE
365
05.02
1m25.123s
My jsme nechali parametry na těchto hodnotách
SQL> show parameter commit;NAME TYPE VALUE———————————— ———– ——————————commit_logging string BATCHcommit_point_strength integer 1commit_wait string NOWAITcommit_write string NOWAIT
Test case
TEST1 je od Tima Halla
SQL> CREATE TABLE commit_test ( id NUMBER(10), description VARCHAR2(50), CONSTRAINT commit_test_pk PRIMARY KEY (id)); 2 3 4 5Table created.Elapsed: 00:00:00.07SQL> SET SERVEROUTPUT ONDECLARE PROCEDURE do_loop (p_type IN VARCHAR2) AS l_start NUMBER; l_loops NUMBER := 1000; BEGIN EXECUTE IMMEDIATE ‚TRUNCATE TABLE commit_test‘; l_start := DBMS_UTILITY.get_time; FOR i IN 1 .. l_loops LOOP INSERT INTO commit_test (id, description) VALUES (i, ‚Description for ‚ || i); CASE p_type WHEN ‚WAIT‘ THEN COMMIT WRITE WAIT; WHEN ‚NOWAIT‘ THEN COMMIT WRITE NOWAIT; WHEN ‚BATCH‘ THEN COMMIT WRITE BATCH; WHEN ‚IMMEDIATE‘ THEN COMMIT WRITE IMMEDIATE; END CASE; END LOOP; DBMS_OUTPUT.put_line(RPAD(‚COMMIT WRITE ‚ || p_type, 30) || ‚: ‚ || (DBMS_UTILITY.get_time – l_start)); END;BEGIN do_loop(‚WAIT‘); do_loop(‚NOWAIT‘); do_loop(‚BATCH‘); do_loop(‚IMMEDIATE‘);SQL> END; 2 3 4 5 6 7 8 9 10 11 12 / 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28COMMIT WRITE WAIT : 365COMMIT WRITE NOWAIT : 21COMMIT WRITE BATCH : 285COMMIT WRITE IMMEDIATE : 365PL/SQL procedure successfully completed.Elapsed: 00:00:11.14
TEST2 od Lakshmi Venkatesh
SQL> CREATE TABLE TEST 2 (a number, 3 b varchar2(100)) 4 /set timing onCREATE TABLE TEST *ERROR at line 1:ORA-00955: name is already used by an existing objectElapsed: 00:00:00.00SQL> SQL> SQL> declare 2 j number := 1; 3 BEGIN 4 FOR i IN 1 .. 50000 loop 5 insert into test values ( j, ‚COMMIT WRITE WAIT‘); 6 j := j + 1; 7 end loop; 8 COMMIT WRITE WAIT; 9 END; 10 /truncate table test;set timing ondeclarej number := 1;BEGINFOR i IN 1 .. 50000 loopinsert into test values (j, ‚COMMIT WRITE NOWAIT‘);j := j+1;END LOOP;COMMIT WRITE NOWAIT;END;/truncate table test;set timing ondeclarej number := 1;BEGINFOR i IN 1 .. 50000 loopinsert into test values (j, ‚COMMIT WRITE BATCH;‘);j := j+1;END LOOP;COMMIT WRITE BATCH;END;/truncate table test;set timing ondeclarej number := 1;BEGINFOR i IN 1 .. 50000 loopinsert into test values (j, ‚COMMIT WRITE IMMEDIATE‘);j := j+1;END LOOP;COMMIT WRITE IMMEDIATE;END;/PL/SQL procedure successfully completed.Elapsed: 00:00:04.43SQL> SQL>Table truncated.Elapsed: 00:00:00.22SQL> SQL> 2 3 4 5 6 7 8 9 10PL/SQL procedure successfully completed.Elapsed: 00:00:03.47SQL> SQL>Table truncated.Elapsed: 00:00:00.71SQL> SQL> 2 3 4 5 6 7 8 9 10PL/SQL procedure successfully completed.Elapsed: 00:00:05.42SQL> SQL>Table truncated.Elapsed: 00:00:00.31SQL> SQL> 2 3 4 5 6 7 8 9 10PL/SQL procedure successfully completed.Elapsed: 00:00:05.02