Aug 302015
 

Je to už hoooodně dlouho co jsem na podobné téma něco napsal – vlastně pokud se nepletu, to byl jen tip na hint NO_STMT_QUEUING. A z nějakého důvodu jsem tam tvrdil, že je to nedokumentovaný hint – je na čase si to opravit :) – či vnést do toho více světla a méně chyb. Dokumentovaný je, ale neni mezi hintama v SQL Reference, ostatně takových je více.

Možná bych měl začít tím, co je to Parallel Statement Queuning – někdy na Oracle 10g2 to funtovalo tak, že pokud si někdo poručil DOP (degree of parallel) třeba 32 a odčerpal tím tak nějaké množství ze zdrojů tak u následujícího SQL mohlo dojít k downgrade stupně parallelizmu. Dobře? Těžko říct, v závislosti na business požadavcích, konkrétním SQL, konkrétní degradaci. Každopádně jak usoudil Oracle bychom rádi i jiné chování – takové, že když si poručím parallel 32 tak ho prostě dostanu – ovšem zdroje jsou omezené, takže sice dostanu, ale musím si daný zdroj počkat – což právě dělá Parallel Statement Queuing.

No, pojďme to rovnou zkusit, první prerekvizita je nastavení parametru PARALLEL_DEGREE_POLICY na AUTO, defaulut je totiž MANUAL – tedy že parallel řídí programátor či jak to nazvat – na úrovni session, hinty, objekty, statementy které to mají nastavitelné atd. Parametr je možné nastavit na úrovni session nebo system:

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY=AUTO SCOPE=MEMORY;

Tak, další věc kterou je třeba nastavit je maximum paralelních serverů, které je možné použít než půjde SQL do fronty. By default je to 4*CPU*PARALLEL THREADS PER CPU (alternativně krát ještě počet instancí na RACu). Hodnota by určo měla být pod PARALLEL_MAX_SERVERS, aby se system nepřetížil jen na parallelch:

ALTER SYSTEM SET PARALLEL_SERVERS_TARGET=4 SCOPE=MEMORY;

Tak ještě bych měl správně nastavit PARALLL_MAX_SERVERS, ale vzhledem k tomu, že jsem PARALLL_SERVERS_TARGET snížil, tak to není potřeba – hodnota je dostatečná (odvozeno od CPU, threads na cpu, pga). No a tohle nastavení by mohlo stačit. Takže to pojďme zkusit, já s tim docela bojoval ;) Založil jsem si totiž tabulku na zkoušku - 15 bloků což je přesně cca 112KB, při defaulutu 200MB/S to prostě nemá cenu paralelizovat, to chápu, speciálně když je tu ještě databázový parametr PARALLEL_MIN_ TIME_THRESHOLD, který by default řiká že nic s odhadem pod 10s nemá cenu optimalizovat parallelně. Sundal jsem to na minimum, to jest na 1s a pořád jsem se potýkal s nepěknou hláškou automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold. Nicméně ač neúspěšné pro mě hezké cvičení při snaze ukecávat Oracle na statistikách – inserty do dba_rsrc_io_calibrate, mreadtim, sreadtim, ioseektime, mbrs čímž jsem vyhnal cost někam k 7 x 10E23, ale pořád nic, občas Oracle změnil hlášku na ještě nepříjemnější: “no expensive parallel operation“.

Ještě než ukáži jak jsem nakonec “zvítězil”, pár věcí na které jsem při tom narazil, tak prvně při nastavování parametrů a snaha přesvědčit Oracle, že čtení 112KB mu bude trvat minuty jsem začal přepočítávat costy – vzorce si nepamatuji, nicméně když už jsem v tom byl, tak jsem si vyjel tenhle select:

SELECT (BLOCKS * (IOSEEKTIM + MBRC * BLKSIZ / IOTFRSPEED) / (MBRC * (IOSEEKTIM + BLKSIZ / IOTFRSPEED )) FROM DUAL;

Což je cost full table scanů, na 12c mi tedy nějak nevychází – ale liší se to jen v řádu jednotek procet, inu proč ne – takže pro dosazení pro všechny tabulky:

SELECT BLOCKS * (1 + 8 * 8192 / 4096) / (1 * round(8 + 8192 / 4096 )) const, owner,table_name FROM dba_tables ORDER BY BLOCKS DESC NULLS LAST;

Další věc, co jsem zjistil

  • Oracle mi nechá nastavit db_file_multiblock_read_count pod 1 – a obecně je to integer, tak nějak podle očekávání trhu. Ovšem přes begin dbms_stats.set_system_stats( ‘MBRC’, 0.0001 ); end; si mužu poručit i opravdu malé číslo.
  • Největší tabulka v prázdné databázi na počet bloků je SYS.IDL_UB1$ (zdrojové kody)
  • S hitem parallel queuing funguje, ovšem ne vždy to píše hlášku because of hint, což by mohlo být nepříjemné

No, nic zpět k ukázce queuing. Možností jak ven z toho, že mi Oracle nechce paralelně číst tabulku s 15ti bloky a zároveň chci ukázat čekání SQL na parallení zdroje je víc. To nejhorší možné – co jsem viděl na webu je založit si nějakou obrovskou tablku – děkuji, nechci ve virtual boxu. Přehintovat SQL (vnořené neasted loopy), aby běželo déle, ale to už bych rovnou mohl dát parallel :) Takže pojďme na to funkcí, které přiřadím ošklivé statistiky, navíc krásně můžu řídit za jak dlouho mi doběhne SQL.

CREATE TABLE TEST_PARALLEL_2 AS SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL<11;

No a ještě tu funkci:

CREATE OR REPLACE FUNCTION f_sleep_me(p_id NUMBER) RETURN NUMBER PARALLEL_ENABLE IS
BEGIN
  dbms_lock.sleep(10);
  RETURN p_id;
END;

A k ní ty statistiky:

ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_me DEFAULT COST (10000000,10000000,10000000);

A nyní již pro mě konečně Oracle pouští SQL parallelně:

Parallelně

Parallelně

Mám nastaveno PARALLEL_SERVERS_TARGET=4 v tomhle případě si vezme 2, takže stačí spustit ve dvou sessions a třetí si již počká:

Čekání ;)

Čekání ;)

A když už jsme u toho, tak ještě zkusme parametr, který udává minimální množství dostupným parallel serverů, které jsou potřeba dostupné, aby vůbec Oracle sql spustil. Možná to vypadá jako zvláštní přístup, ale když si pes představí SQL, které s parallelelm 20 běží 2 hodiny, tak rozhodně nemá chuť si nechat si automaticky zkrhouhnout si DOP na 2 ;). Tedy ještě vyzkoušení PARALLEL_MIN_SERVERS, prvně je však nutné vrátit parallel na manual:

ALTER SYSTEM SET parallel_degree_policy=MANUAL SCOPE=BOTH;

A pak tedy nastavit ještě další potřebné parametry:

ALTER SYSTEM SET PARALLEL_MIN_PERCENT=80 SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_MIN_SERVERS=10 SCOPE=SPFILE;
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=20 SCOPE=SPFILE;

Důvodem proč SPFILE je parametr PARALLEL_MIN_PRECENT, který neni modifiable a tímpádem asi i všechno, ať to neni na půlku konfigurované v paměti a půlku na spfile. A ještě dva parametry upravme:

ALTER SYSTEM SET parallel_threads_per_cpu=10 SCOPE=SPFILE;
ALTER SYSTEM SET parallel_servers_target=30  SCOPE=SPFILE;

Fajn a po otočení Oracle:

/*SESSION 1*/  select /*+ parallel(a 10) */ * from test_parallel_2 a where id=f_sleep_me(id);
/*SESSION 2*/  select /*+ parallel(a 10) */ * from test_parallel_2 a where id=f_sleep_me(id);

Čímž si vezmu všechno 2×10=20 a tudíš ve třetí session již mi to Oracle nedovolí spustit a skončím na:

ORA-12827: otillräckliga parallella frågeslavar (begärda 10, tillgängliga 0, parallel_min_percent 80)

Což je švédská hláška řikající, že chci 10, dostupných je 0 a tudíš mam smůlu, tohle je hodně extrémní případ, kdyt jsem všechno vyčerpal na nulu, takže ještě jednou :

/*SESSION 1*/  select /*+ parallel(a 3) */ * from test_parallel_2 a where id=f_sleep_me(id);
/*SESSION 2*/  select /*+ parallel(a 3) */ * from test_parallel_2 a where id=f_sleep_me(id);

Tedy si zkusim požádat o 19 další session:

ORA-12827: otillräckliga parallella frågeslavar (begärda 19, tillgängliga 11, parallel_min_percent 80)

Jestli mi zbývá 11 pak (1/0,8)*11=13,75, kolik múžu použít, to jest:

 select /*+ parallel(a 14) */ * from test_parallel_2 a where id=f_sleep_me(id);
 ORA-12827: otillräckliga parallella frågeslavar (begärda 14, tillgängliga 11, parallel_min_percent 80)
 /* ale s 13 to je ok */
 select /*+ parallel(a 13) */ * from test_parallel_2 a where id=f_sleep_me(id); -- normálně běží

A to je asi pro dnešek vše.

 Posted by at 21:47

 Leave a Reply

(required)

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>