Mar 272016
 

Godiva,

tak pod dlouhé době zase nějaký post.  Ne, že bych snad něměl nápady, co sem spát, ale prostě není čas -ale i takto tu tak nějak hodlám tento rok udržovat :) Inu, dnešní téma je DEFAULT na 12c a nová featura/optimalizace Oracle, která nás tak trošku potrápila, ale ve skutečnosti je neskutečně sexy.

Kolega nedávno objevil záhadný hidden sloupec v tabulce (a tudíš jemu patří credit) a ptal se jak se tam sakra dostal. Popravdě Oracle 12c si vlastní systémové sloupce přidává docela rád a ne vždy to člověka potěší. Při takovém SWITCH PARTITIONS to nepotěší nikdy ;) Nicméně, na otázku kolegy jsem mu nabídl vysvětlení extended statistic, Oracle 12c (v defaulutu zapnuto) si pro korelované sloupce v predikátech založí virtuální sloupec a nad nim spočitá statistiky. Což se nepotvrdilo. Určit exaktně o co přesně jde z následující tabulky:

<code>  property      number not null,           /* column properties (bit flags): */
/* 0×0001 =       1 = ADT attribute column                   */
/* 0×0002 =       2 = OID column                             */
/* 0×0004 =       4 = nested table column                    */
/* 0×0008 =       8 = virtual column                         */
/* 0×0010 =      16 = nested table’s SETID$ column           */
/* 0×0020 =      32 = hidden column                          */
/* 0×0040 =      64 = primary-key based OID column           */
/* 0×0080 =     128 = column is stored in a lob              */
/* 0×0100 =     256 = system-generated column                */
/* 0×0200 =     512 = rowinfo column of typed table/view     */
/* 0×0400 =    1024 = nested table columns setid             */
/* 0×0800 =    2048 = column not insertable                  */
/* 0×1000 =    4096 = column not updatable                   */
/* 0×2000 =    8192 = column not deletable                   */
/* 0×4000 =   16384 = dropped column                         */
/* 0×8000 =   32768 = unused column – data still in row      */
/* 0×00010000 =   65536 = virtual column                         */
/* 0×00020000 =  131072 = place DESCEND operator on top          */
/* 0×00040000 =  262144 = virtual column is NLS dependent        */
/* 0×00080000 =  524288 = ref column (present as oid col)        */
/* 0×00100000 = 1048576 = hidden snapshot base table column      */
/* 0×00200000 = 2097152 = attribute column of a user-defined ref */
/* 0×00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0×00800000 = 8388608 = string column measured in characters   */
/* 0×01000000 = 16777216 = virtual column expression specified    */
/* 0×02000000 = 33554432 = typeid column                          */
/* 0×04000000 = 67108864 = Column is encrypted                    */
/* 0×20000000 = 536870912 = Column is encrypted without salt       */

/* 0×000800000000 = 34359738368 = default with sequence                */
/* 0×001000000000 = 68719476736 = default on null                      */
/* 0×002000000000 = 137438953472 = generated always identity column    */
/* 0×004000000000 = 274877906944 = generated by default identity col   */
/* 0×080000000000 = 8796093022208 = Column is sensitive                */
</code>

No dobře, neni to přesně tabulka, ale jsou to vykopírované komentáře z rdbms/admin/dcore.bsq. A obecně jsou tyto typy souboru (admin/*.bsq) vhodné pro zkoumání, co která hodnota PROPERTY asi tak znamená (pro mně novinka).

Zkrátka jsme po chvíli přišli na to, že se jednalo o přidání sloupce s DEFALUTEM na NULLABLE sloupec – zatímco v 11g si Oracle musí naupdatovat všechny řádky s novou DEFULAT hodnotou, na 12c se jedná pouze o změnu definici tabulky a běh v řádu častí sekundy – ovšem za cenu nového hidden sloupce, který nyní udržuje informaci, zda-li je v řádku reálně NULL nebo DEFAULT hodnota. Krásný článek je o tom přímo u Oracle: http://www.oracle.com/technetwork/articles/database/ddl-optimizaton-in-odb12c-2331068.html. Který navíc krásně popisuje jak to pak funguje, když se na sloupec dotazuji (použití NVL na určení, co je je opravdová hodnota).

Takže zkouška:
CREATE TABLE T_TEST(a VARCHAR2(1), b VARCHAR2(2));
INSERT INTO T_TEST VALUES ('a', 'b');
INSERT INTO T_TEST VALUES ('a', 'b');
INSERT INTO T_TEST VALUES ('a', 'b');
ALTER TABLE T_TEST ADD (c VARCHAR2(1) DEFAULT 'z');
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);
INSERT INTO T_TEST(a,b,c) VALUES ('a', 'b',null);

A nyní pohled na data (s jedním řádkem, před přidáním constrainu):

V tabulce je nově sloupce SYS_N00003$, který určuje, jestli se jedná reálně o NULL, nebo o sloupec ve kterém byl NULL před přidáním constrainu a tudíš by nyní měl obsahovat “z”. Oracle na to musí aplikovat funkce a rozhodnout, která hodnota je tedyy platná – ve sloupci C/default či SYS_N00003$. Jak to dělá ukazuje linknutý článek na Oracle.com – co je nutné podotknout, není to vidět před spuštěním ale až v reálném exekučním plánu. Článek to ukazuje na predikátu, ale stačí kouknout do PROJECTION : DECODE(TO_CHAR(SYS_OP_VECBIT(“SYS_NC00003$”,0)),NULL,NVL(“C”,’z’),’0′,NVL(“C”,’z’),’1′,”C”). A to samé si pak mohu použít v SQL:

Jak se toho zbavit?

Expost, popravdě nevím – sloupec nelze droponout (ORA-14148) a ani s pár pokusama změnit pod system PROPERTY jsem neuspěl :(. Nicméně jedná se o novinku a obyčejně Oracle “somehow” umožňuje nové chování potlačit. Obyčejně se jedná o zkryté parametry. Což se hledá velice špatně – většinou řeším LIKE + kombinace s hodnotou o které si myslím, že je právě nastavena. Tedy zkrytý parametr obsahující slovo NULL, který je nastaven na TRUE:

select * from (
SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%' escape '/'
AND b.ksppstvl='TRUE'
ORDER BY
1)
where upper(descr) like '%NULL%'
;

S tím, že mi to našlo celkem 5 parametrů. Většina se týká joinu a pak _add_nullable_column_with_default_optim. Tento zkrytý parametr je možné nastavit i na úrovni session i na úrovni instance a neni statistký. Sice nepodporovaný, ale když si před přidáním DEFAULT hodnoty na NULLABLE sloupce vylatrujete session :

alter system set "_add_nullable_column_with_default_optim"=false;

So, nepříjemnost při SWITCHOVÁNÍ PARTITIONS či použití ALL_TAB_COLS místo ALL_TAB_COLUMNS nebo sexy věc při přidání DEFAULTu na sloupec (nyní nově na nullable sloupec) v tabulce s 100mil záznamy a během v řádu desetin sekundy ;) Podle toho, kdo jste ;)

 Posted by at 15:31
Dec 052015
 

Godiva, minulý týden jsem měl možnost účasnit se školení, které poskytoval sir. Jonathan Lewis. Jestli u mě na blogu nejste poprvé zajisté jméno znáte. Nebudu hledat ty všechny články, kde jsem ho zmínil, ale minimálně jsem vychvaloval dvě jeho knihy CBO (přesný název Cost-Based Oracle Fundamentals) a Essential Internals for DBAs and Developers. Bohužel obě knihy ač jsou výborné trpí atributem “zastaralý”. Ostatně i sám Lewis to v úvodu CBO knihy píše, že vždy když si nastuduje nějakou verzi Oracle napíše k tomu knihu, že už v době releasu knihy je 2 verze Oracle pozadu.  A bohužel tim, že je často řeč na pomezí interních věcí Oracle, které nejsou standardizovány, se vše mění dost podstatně (v porovnáním s knížkama jinýma, kde nás před velkou změnou mezi verzemi chrání SQL standard či snaha o backwards compatibilty).  Ono vypadá, že některé věci by mohly zůstat přes všechny verze, protože třeba matematický výpočet selectivity na intervalu zůstane navždy stejný. Ovšem jakmile se přidají do toho histogramy, tak se to od verze od verze dost mění (ve 12c třebas nově větší histogramy >256 bucketu, Top N a hybridní histogram, interni vypocet je trošku jiný…). Tak či tak je to dobrá knížka a jedna z nejlepších o Oracle.

Přednáška byla rozdělat na tři části Designing Optimal SQL, Troubleshooting and Tuning a diskuze. Nejsem způsobilý k tomu, abych mohl recenzovat ani snad komentovat  jak někdo vykládá. Prostě vpořádku, vypadá jak typický britský účitel a vykládat dost dobře. Pouze jedna poznámka, co se mi tom hodně líbilo a což jsem od něj tak čakal: Velmi často řiká “možná”, “když budete mít štěstí” atd. Do výkladu vkládá krásnou nestabilitu, nikoliv protože by nevěděl, ale protože ví, že jsou za tim další komplikovaná rozhodnutí CBO, které třebas nemá čas popisovat a které se třeba objevují jen zřídka. Alá: “na pořadí v jakém napíšu tabulky do joinu ve skutečnosti záleží” – nicméně to je na úrovni internal Oracle a je strašně nepravděpodobné, že to někdy někdo z nás uvidí či to bude problém. Lewis sice píše pořadí tabulek ve FROM klauzuli v nějakém specifickém pořadí nicméně důvodem neni performance ale čitelnost.

Fakt luxusní povidání, třebas se mi hodně líbilo jak si dal pozor na to, aby uvedl na pravou míru, že na reverzním indexu je opravdu možné použít operaci “range scan”, ovšem pouze a jenom na equality operátoru, jinak bohužel. Většina knížek se s tim nesere a napíše, že prostě “range scan” neni na možný na reverse indexu. Někde na svém blogu se o tom zminoval. Ostatně většinu věcí co vykládal jsem poznával, že je relativní ke konkrétnímu postu na jeho blogu.

Technologie, co popisoval byly známé. Nicméně jsem si vědom, že je možné někdo nezná, tedy dohledejte si případně – budu jen vyjmenovávat. A na konci, co bylo nového pro mou psa a chci si zapamatovat:

Designing Optimal SQL:

Zmíněné featury: index_join ,index_combine, index rebuild, index coalescence, clusters, data analysis, qb_name, readibility, leading, index, use_nl, use_hash, swap:_joins_inputs, full, no_push_subq, materialize, sql baselines, or explain gather_plan_statistic, adaptive curshor sharing, index_ffs.

A taky ukazoval zjednodušené vzorce pro NL/HASH/MERGE joiny. Bohužel to strašně zjednodušil a zrovna tam by mi zajímalo jaký benefit má nová implementace NL v 11g kdy se lehce prohodilo pořadí v exekučním plánu a přidalo bufferovani, aby se zvednula pravděpodobnost brani bloku z cache.

Pokud je query bloku větší množství tabulek, tak Oracle vezme jednu, přijoinuje druhou, třetí, čvrtou. Občas ale chceme join mezi 1 a 4 a join mezi 2 a 3 a teprve pak najoinovat na sebe výsledky. De-facto rozpadnout jeden query block na dva. Jonathan to nazval dvěma pojmy, které jsem předtím nikdy neslyšel: Left-deep (to co dělá Oracle 1,2,3,4) a Bushy tree (to co se mu snažíme vnutit my pomocí with as, zavorek, redesingu, unnest a no_unnest (1,4)@(2,3)). Další pozoruhodná poznámka byla, že Oracle 12c už je v tomhle chytřejší.

A co si odnáším páč jsem nevěděl:

  • table_cached_blocks v dbms_stats u preferencích tabulky lze setnout i tenhle attribut
  • index rebuild online – nejde na indexech enforsujícíh PK/UK, dobré vědět. Nadruhou stranu na 11+ Oracle je rebuild potřeba jen vyjmečně a zrovna u PK, kde by se sloupec neměl updatovat vubec, to vubec nevadi ;)
  • index rebuild online – vyžaduje stejně lock při applikování journalu (stejně jako při switch dbms_redefinition)
  • sys_op_lbid – při mém štěstí jsem tuhle funkce už použil (jednou a nedávno :D), takže jsem věděl, nicméně stojí za připomenutí interní funkce vracící leaf block id z indexu na základě daného rowid
  • materialize hint – nemá to ověřené ale temp tabulka je vždy když tabulku z with as odkazujeme více než jednou, což by krásně odpovídalo tomu co dělá star_transformation u které je to tak nějak naznačeno přímo v dokumentaci
  • _optimalizer_adaptive_cursor_sharing – ha, zkrytý databázový parametr jak vypnout tuhle featuru ;) Na úrovni statementu by mělo stačit normálně NO_BIND_AWARE hint, který teda Oracle zdokumentoval až ve 12c, pokud se nepletu.

Troubleshooting and Tuning

Zmíněné featury: ASH, session trace,  sqlmonitoring, dbms_sqltune, v$sess_time_model , index reverse, cardinality hint, step by step slow processing, hprofiler, pragma udf, no segment indexy, invisible indexy, fixed views, waiting metriky, sql plan statistic, awr, obarvování sql, deklarace funkce with as v 12.1c

Jinak to bylo zajímavé povidání o konci tabulek a hot blokách, že trapných 600 session potkávajících se v library cache na jedno sql může udělat problém. A hodně řešil fixed views a data dictionary pohledy a ukazoval jak jsou napsány obecně a ne vždy se hodí z peformance důvodu – aneb view je query blok (pokud to oracle neztransformuje) optimalizovaný zvlášt a maximálně tam může pushnout predikát, což ne vždy stačí.

A co si odnáším páč jsem nevěděl:

  • index reverse – nikdy jsem nad tím nepřemýšlel, ale index reverse pochopitelně ovlivní clustering_factor (česky: nejspíše ho totálně rozmrdá)
  • temporary tabulky sice mají na 12c vlastní statistiky pro usery, ale ne by default, navíc jdou setnout do temporary tablespace
  • dbms_stats.num_array – musim si zkusit
  • mohu sám setnout clustering_factor na indexu
  • virtual column + check constraint (+hidden ideálně na 12c) může pomoci exekučnímu plánu. Check je pochopitelně vyčerveněno schválně, protože fakt to Oracle umí i s expression (COOL !)
  • pragma udf – uplně jsem na ní zapomněl. PL/SQL funkce volaná z SQL – pomůže to eliminovat overhead na přepinání contextu nez pl/sql a sql engines (ač tomu Lewis řikal lehce jinak). Nicméně na 12.1 je tohle rychlejšejší.
  • v$sql_workarea_histogram – luxusni view, neznal jsem

Diskuze

To bylo dost volné řešily se MVIEW Logy, ukazoval parallení exekuci a nějaká menší zmínka o DBMS_REDEFINITION,  SQL Baselines a pár nelichotivých poznámek na 12c ;) Na mých slovíčkách ze švédštiny se třebas objevil na 12c nový sloupec neb Oracle přišel na to, že je tam nějaká kolerace, takže si chlapec založil virtuální sloupec a hodil na něj extented statistiky, nicméně lze vypnout a jen nechat Oracle to reportovat.

A co si odnáším páč jsem nevěděl

  • treedump indexu bloku jsem nikdy nezkoušel, vypadá zajímavě (eventu nepamatuji)
  • Pokud jsem to dobře pochopil, tak rozpartišnování tabulky pujde na 12.2 ONLINE ;) wohowo
  • DBMS_REDEFINITION a aplikace journalu – Jonathan o tom mluvil, jako že aplikace journalu muže nějaký čas trvat a vyžaduje LOCK, musim zjistit – do ted jsem si myslel, že po synchronizaci hlavni tabulky, mužu/bude journal ještě pořád sychrnonizován online a obecně mužu dosynchrnizovovát libovolně dokud to neni skoro fresh a pak teprve LOCK.
  • 40x ? Jestli jsem slyšel dobře tak overhead na MATERIALIZED VIEW LOG je 40 rekurzivnich selectu – budu muset najit, přijde mi to podezřele vysoké

Bloom filtr – tohle je hroznej chyták, funguje to od Oracle 10g a zdokumentováno to bylo až v 12.1c. Lewis mluvil o tom, že v 11g se to neukazuje v exekučním plánu. Což je možná dobré upřesnit, protože záleží na tom který.  Bloom fitr na úrovni filtrování rows při paralelní exekuci je v plánu jen v OTHER/XML na 11.2. Ovšem i na 11.2 je vidět v exekučním plánu pokud je to na úrovni partitions – prostě eliminace partitions na bloom filtru.

Suma sumárum hodně děkuji svému zaměstanavateli za luxusní školení, nevim koho bych na školení raději než Lewise určitě zajímavější než Kyte. Možná ještě zajímavej by byl Julian Dyke (+ a pochopitelně jakákoliv prsatá samička, která třebas ani Oracle nezná ale nosí těsné džíny) ;)

 Posted by at 14:19
Aug 022015
 

Godiva, mohlo by se zdát, že rok po takovém čase od vydání 12.1c nemá cenu psát o hintech z 11.2g. Nicméně tento článek jsem rozepsal před více než rokem a půl, zanechal ve složce “drafts” a zapomněl. Navíc je to tak nějak pokračování, takže by bylo fajn to dokončit, speciálně když jsem ho v draftech našel de-facto dokončený ;) Takže jen lehké update a publish.

K hintu IGNORE_ROW_ON_DUPKEY_INDEX se toho asi mnoho nedá napsat – lze jej použít jen v INSERT, je nový od 11.2g (předtím šlo v 10.2g potlačit chybu pomocí DBMS_ERRLOG).  A co je zajímavé – Oracle explicitně zmiňuje, že v případě nalezení neunikátního záznamu se pokračuje dále a row-level lock řádku pustí – tzn, ty řádky které insertuje a již existují v tabulce to nelockne.

Synaxe je následující:

Takže ukázka, data stejná jako minule:

create table tbl_ignore_key as
select rownum as col_unique from dual connect by level<6;

 

Zase nějaký unikátní index:

create unique index inx_tbl_ignore_key_col_unique on tbl_ignore_key(col_unique);

A pokus o insert s hintem IGNORE_ROW_ON_DUPKEY_INDEX:

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
5 rows inserted.

Dokonce kdyby nikdo nevěřil, to mohu pustit znovu :

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(tbl_ignore_key,inx_tbl_ignore_key_col_unique) */ into
tbl_ignore_key select rownum as col_unique from dual connect by level<11;
0 rows inserted.

Kdy mi to sice nic neinsertuje, ale nespadne na constrain, zatímco bez hintu:

insert into tbl_ignore_key
select rownum as col_unique from dual connect by level<11;
00001. 00000 - "unique constraint (%s.%s) violated"

Nebo nyní nově pro nás, co se učíme švédsky ORA-00001: brott mot unik begränsning ;). Takže tolik k tomuto hintu, ještě zbývají nějaká ta upozornění – pochopitelně nelze použít v kombinaci s APPEND hitem (který bude ignorován), odkazuje se tam na konkrétní objekt – což v hintu většinou typicky znamená, že hint na neexistující objekt vyfailuje celý insert, nelze použít v bukách a je to rychlejší než EXCEPTION klauzule.

 Posted by at 12:27
Jul 282015
 

Godiva, pojďmě si představit další funkci z balíčku funkcí, které tu jsou nově v 12.1c – APPROX_COUNT_DISTINCT. Jak název napovídá funkce vrátí počet distinctních hodnot a slovo APPROX znamená, že je to “asi tak nějak” :) Výměnou za nějakou tu nepřesnost dostaneme mnohonásobné zrychlení. Takže hurá na nějaký test a další informace dále :

CREATE TABLE TEST_APROX(
ID NUMBER(30) PRIMARY KEY,
TEST_RANDOM   NUMBER,
TEST_1_100    NUMBER,
TEST_1_2_3    NUMBER,
TEST_1_25000  NUMBER,
TEST_10_1     NUMBER
);

A ještě je třebas jí naplnit:

begin
for i in 0..100 loop
INSERT INTO TEST_APROX
  SELECT ROWNUM+(i*30000) AS ID,
  dbms_random.value AS TEST_RANDOM,
  MOD(rownum,100)   AS TEST_1_100,
  CASE WHEN ROWNUM=15555 THEN 1
       WHEN ROWNUM=15556 THEN 2
  ELSE 3 END        AS TEST_1_2_3,
  CASE WHEN ROWNUM<25000 THEN 1 
     ELSE ROWNUM END AS TEST_1_25000,
  CASE WHEN MOD(ROWNUM,10)<>2 THEN MOD(ROWNUM,10)
  ELSE 1 END AS TEST_10_1
 FROM dual CONNECT BY LEVEL <=30000;
 end loop;
end;

Tak nějak nějaký vzorek dat, některá lehce zkosená některá náhodná. Doporučuji případně ještě napočítat statistiky během CTASu se sice nově napočítávají, ale takhle v LOOPu to nezafunguje, bohužel.  Mě to vychází nějak takto:

Sloupec Popis dat DISTINCT APR CNT DIS APR CNT DIS result Chyba
ID Čísla od 1 do 3030000 2,8 (jedeme po indexu) 1s 2975859 1,78%
TEST_RANDOM Náhodná čísla cca 19s 1s 2986718 1,42%
TEST_1_100 Čísla od 0 do 99, každé 30300x 2s pod 1s 100 0%
TEST_1_2_3 čísla 1,2 101x číslo 3 3029798x pod 1s pod 1s 3 0%
TEST_1_25000 1x 2524899,101x 25000-30000 1s-2s pod 1s 5018 3,2%
TEST_10_1 30300x 0,3-9, 606000x 1 1s-2s pod 1s 9 0%

Takže nějaký závěr: narozdíl od DISTINCT se závislost na datech neprojeví zpomalením, ale na nepřesnosti. Což je hodně sympatické, bohužel jsem podcenil svůj komp a zrychení je vidět de-facto jen ve dvou případech, ale i tak je vidět že o proti normálnímu COUNTU hraje APPROX_COUNT_DISTINCT naprosto jinou ligu :) A to i proti indexu je daleko rychlejšejší. V exekučním plánu to vypadá nějak takto:

Aprox exekuční plán

Aprox exekuční plán

Nicméně ačkoliv to ukazuje sort mě se na tomhle příkladu nic reálně nesortovalo, jestli to takhle zafunguje budeme muset zjistit ;) Enjoy APPROX_COUNT_DISTINCT !

 Posted by at 22:42
Jul 042015
 

Po delší době zase Godiva, jsem zpět.

Ačkoliv jsem byl z možnosti tracování CBO velice potěšen (a stále sem) a rozepsal jsem hned další díl, tak jsem byl potrápen nedostatkem času za který mohla převážně moje nádherná vyučující švédštiny. Ano, v mém věku jsem prvně dělal a odevzdával úkoly, které bych na škole vyměnil za pětku či poznámku a označil slovem nepovinný. Ale nyní jsem se snažil dělat úkoly v  A++ kvalitě (ve smyslu ano, musí rozesmát/potěšit moji feministickou učitelku a ne, nemusí být gramaticky správně). Nicméně z úkolu mi právě vyvázal konec kurzu v kombinaci se zjištěním, že je samička zasnoubená :/. A taky, a to si přiznejme, za delší odmklu na blogu může Serious Sam II a Trine 2.

Ale nyní zpět k CBO tracování, v minulém postu jsem tak nějak naznačil jak moc cool je tracování eventu 10053. To byl ovšem začátek, který tak akoád ukazuje, že něco takového existuje. Na optimaizaci select * from dual prostě Oracle nemá co vymyslet. Doby, kdy se sekvence tahala přes select into a v rámci vyhnutí se latchů se dělala podobná tabulka vícekrát, jsou taky pryč, takže obecně ani my programátoři nic na select * from dual nevymýšlíme, není co – akorád to nepoužívat jako hulvát, když nemusíme.  Takže zkusme hodit trace něčeho odvážnějšího, ale zase né moc, začínáme – dvě tabulky postačí.

create table tbl_1000 as select rownum as id, mod(rownum,10) as tbl_1000id from dual connect by level<1001;
create table tbl_10000 as select rownum as id, mod(rownum,100) as tbl_10000id from dual connect by level<10001;

Jedna tabulka tbl_1000 s 1000 řádky a druhá tbl_10000 s 10000 řádky. A ještě jednu věc udělejme:

begin
DBMS_STATS.delete_table_stats('AZOR6','tbl_1000');
end;

Proč? Protože nově na 12c, kterou mám doma Oracle počítá statistiky během CTASu a já opravdu o statistiky zájem nemám. O co mám zájem je, aby mi zase ukázal jak si CBO krásně nasampluje.

Zapnutí tracování:

ALTER SESSION SET EVENTS '10053 trace name context forever,level 1';

A select:

select count(*) from tbl_1000 join tbl_10000 on (tbl_1000.tbl_1000id = tbl_10000.tbl_10000id);

A pak vzhůru do souboru: orcl12_ora_6508.trc

O proti select * from dual jsou zajímavější pokusy Oracle o rewrite – ve smyslu to přepsat tak, aby to bylo efektivnější. Pokusit se vyhodit zbytečný join apod. A ani nyní neuspěl se žádnou optimaizací. Jediný přepis byl přesunu join podmínky z standardu SQL (join on) do WHERE podmínky. Z hlediska optimlizace žádná změna.  Nyní rozhodnutí o přístupových cestách:

Access path analysis for TBL_1000
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TBL_1000[TBL_1000] 
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: TBL_100  Alias: TBL_100
    Card: Original: 1000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  Access Path: TableScan
    Cost:  3.01  Resp: 3.01  Degree: 0
      Cost_io: 3.00  Cost_cpu: 205607
      Resp_io: 3.00  Resp_cpu: 205607
  Best:: AccessPath: TableScan
         Cost: 3.01  Degree: 1  Resp: 3.01  Card: 1000.00  Bytes: 0

Bohužel nemá víc na výběr než jet full table scan s costem 3 a odhadem 1000 řádků. Zato druhá tabulka je daleko zajímavější :

Access path analysis for TBL_1000
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TBL_1000[TBL_1000] 
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

*** 2015-03-19 23:48:26.731
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated table stats.: blocks=21

Statistiky pro TBL_1000 jsem mu smazal, takže si CBO musí nasamplovat tabulku, což podle trace dělá následujícím způsobem:

SELECT
  /* OPT_DYN_SAMP */
  /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */
  NVL(SUM(C1),0),
  NVL(SUM(C2),0),
  COUNT(DISTINCT C3),
  NVL(SUM(
  CASE
    WHEN C3 IS NULL
    THEN 1
    ELSE 0
  END),0)
FROM
  (SELECT
    /*+ NO_PARALLEL("TBL_1000") FULL("TBL_1000") NO_PARALLEL_INDEX("TBL_1000") */
    1                       AS C1,
    1                       AS C2,
    "TBL_1000"."TBL_1000ID" AS C3
  FROM "AZOR6"."TBL_1000" "TBL_1000"
  )

Z čehož mu vypadne nějaký takovýto výstup:

*** 2015-03-19 23:48:26.794
** Executed dynamic sampling query:
    level : 2
    sample pct. : 100.000000
    actual sample size : 10000
    filtered sample card. : 10000
    orig. card. : 1716
    block cnt. table stat. : 21
    block cnt. for sampling: 21
    max. sample block cnt. : 64
    sample block cnt. : 21
    ndv C3 : 100
        scaled : 100.00
    nulls C4 : 0
        scaled : 0.00
    min. sel. est. : -1.00000000
** Dynamic sampling col. stats.:
  Column (#2): TBL_1000ID(NUMBER)  Part#: 0
    AvgLen: 22 NDV: 100 Nulls: 0 Density: 0.000000
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
   Scaled NDVs using cardinality = 10000.
** Using dynamic sampling card. : 10000
** Dynamic sampling updated table card.
  Table: TBL_1000  Alias: TBL_1000
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  7.05  Resp: 7.05  Degree: 0
      Cost_io: 7.00  Cost_cpu: 1849550
      Resp_io: 7.00  Resp_cpu: 1849550
  Best:: AccessPath: TableScan
         Cost: 7.05  Degree: 1  Resp: 7.05  Card: 10000.00  Bytes: 0

Počet řádků (10000), počet distinctních hodnot (100), počet nulls (0) a cost 7. A nyní ty zajímavé věci a zjištění – original cardinality 1716. Kde se to vzalo? Je to výpočet Oracle pro tabulku, která nemá statistiky. Píší výpočet, protože když Oracle přecházel z RBO na CBO měly tabulky by default cardinalitu 100. Kolik mají nyní netuším, ale rozhodně se jedná výpočet (možná někdy prozkoumám) – patrně na základě počtu segmentů, velikosti bloků a max délky řádky – dávalo by to smysl, přecejenom nějaké info Oracle má.  Jak zjistit default bez statistik se dá klidně potlačením dynamic samplingu bez nutnosti tracování:

select /*+ dynamic_sampling(0) */ count(*) from tbl_100 join tbl_1000 on (tbl_100.tbl_100id=tbl_1000.tbl_1000id);

Další zajímavá věta je “max. sample block cnt. : 64″ je vidět, že CBO byl pro mě ochoten nasbírat 64 bloků a tabulka má jen 21. Takže select běžel proti celé tabulce bez jakéhokoliv omezení. A ještě jedna řádka je zajímavá – filtered sample card. – což bude souviset s levelem dynamic samplingu a případně where podmínkou. Level nízký a žádný where, takže nebylo co řešit – počet řádků stejný jako v tabulce.

A nyní přijde to co přijít muselo. Perumtování a permutování a permutováné ;) Naštětí jsem si nezasral za uši najoinoval jen dvě tabulky na sebe, což mu dává přecejenom dost omezený počet permutací.

Join order[1]:  TBL_1000[TBL_1000]#0  TBL_10000[TBL_10000]#1

***************
Now joining: TBL_10000[TBL_10000]#1
***************
NL Join
  Outer table: Card: 1000.000000  Cost: 3.005178  Resp: 3.005178  Degree: 1  Bytes: 
Access path analysis for TBL_10000
  Scan IO  Cost (Disk) =   5.689000
  Scan CPU Cost (Disk) =   1849550.240000
  Total Scan IO  Cost  =   5.689000 (scan (Disk))
                         + 0.000000 (io filter eval) (= 0.000000 (per row) * 10000.000000 (#rows))
                       =   5.689000
  Total Scan CPU  Cost =   1849550.240000 (scan (Disk))
                         + 500000.000000 (cpu filter eval) (= 50.000000 (per row) * 10000.000000 (#rows))
                       =   2349550.240000
  Inner table: TBL_10000  Alias: TBL_10000
  Access Path: TableScan
    NL Join:  Cost: 5751.176415  Resp: 5751.176415  Degree: 1
      Cost_io: 5692.000000  Cost_cpu: 2349755847
      Resp_io: 5692.000000  Resp_cpu: 2349755847

  Best NL cost: 5751.176415
          resc: 5751.176415  resc_io: 5692.000000  resc_cpu: 2349755847
          resp: 5751.176415  resp_io: 5692.000000  resc_cpu: 2349755847
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
Join Card:  100000.000000 = outer (1000.000000) * inner (10000.000000) * sel (0.010000)
Join Card - Rounded: 100000 Computed: 100000.000000
  Outer table:  TBL_1000  Alias: TBL_1000
    resc: 3.005178  card 1000.000000  bytes:   deg: 1  resp: 3.005178
  Inner table:  TBL_10000  Alias: TBL_10000
    resc: 7.046579  card: 10000.000000  bytes:   deg: 1  resp: 7.046579
    using dmeth: 2  #groups: 1
    SORT ressource         Sort statistics
      Sort width:          10 Area size:      131072 Max Area size:     2097152
      Degree:               1
      Blocks to Sort: 4 Row size:     25 Total Rows:           1000
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 40156645
      Total Temp space used: 0
    SORT ressource         Sort statistics
      Sort width:          10 Area size:      131072 Max Area size:     2097152
      Degree:               1
      Blocks to Sort: 18 Row size:     14 Total Rows:          10000
      Initial runs:   2 Merge passes:  1 IO Cost / pass:         12
      Total IO sort cost: 30.000000      Total CPU sort cost: 46137630
      Total Temp space used: 254000
  SM join: Resc: 42.451655  Resp: 42.451655  [multiMatchCost=0.226657]
SM Join
  SM cost: 42.451655 
     resc: 42.451655 resc_io: 40.000000 resc_cpu: 97349433
     resp: 42.451655 resp_io: 40.000000 resp_cpu: 97349433
  Outer table:  TBL_1000  Alias: TBL_1000
    resc: 3.005178  card 1000.000000  bytes:   deg: 1  resp: 3.005178
  Inner table:  TBL_10000  Alias: TBL_10000
    resc: 7.046579  card: 10000.000000  bytes:   deg: 1  resp: 7.046579
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.044072  #ptns: 1
    hash_area: 32 (max=512) buildfrag: 4  probefrag: 19  ppasses: 1
  Hash join: Resc: 10.322486  Resp: 10.322486  [multiMatchCost=0.226657]
HA Join
  HA cost: 10.322486  
     resc: 10.322486 resc_io: 10.000000 resc_cpu: 12805157
     resp: 10.322486 resp_io: 10.000000 resp_cpu: 12805157
Best:: JoinMethod: Hash
       Cost: 10.322486  Degree: 1  Resp: 10.322486  Card: 100000.000000 Bytes: 
***********************
Best so far:  Table#: 0  cost: 3.005178  card: 1000.000000  bytes: 13000.000000
              Table#: 1  cost: 10.322486  card: 100000.000000  bytes: 1600000.000000

Oracle má k dispozici pouze dvě tabulky na optimalizaci, takže pro permutace má jen dvě možnosti A-B a B-A respektive tbl_1000-tbl_10000 a tbl_10000_tbl-tbl_1000. Prvně zkusil nejprve pořadí tbl_1000-tbl_10000.  S tím, že zkoušel tři kombinace (což být vždycky nemusí, ale velmi často bude) – NL – neasted loopu, SM – sort merge join a HA – hash join.  Pro NL byl cost 5751, pro SM 42 a vyhrál HA s krásným costem pouhých 10 ;).  Že Oracle nekecá jsem si ověřil a zkusil si to tak hintnout:

select /*+ use_nl(tbl_10000 tbl_1000) leading(tbl_1000) */ count(*) from tbl_1000 join tbl_10000 on (tbl_1000.tbl_1000id = tbl_10000.tbl_10000id);

A funguje, horší bylo když jsem si zkusil hintnout merge join a dostal jsem se na dvojnásobný cost, což nepotěší, žejo. Nakonec jsem si po delším pátrání uvědomil, že jsem se pro NL původně snažil přepočítat její COST s čímž jsem jednak neuspěl a jednak jsme si kvůli tomu pustil:

begin
dbms_stats.gather_system_stats() ;
end;

Abych věděl sreadtim/mreadtim což byla hlupárna, žejo – jednak to nemělo šanci na úspěch, protože jak mam něco zkoumat, když si to sám změním? A druhak mam ted pochopitelně jiné costy, všude.. Po té, co CBO provedlo přepočítalo tyto tři joiny pro první pořadí je třebas se posunout dále:

***********************
Best so far:  Table#: 0  cost: 3.005178  card: 1000.000000  bytes: 13000.000000
              Table#: 1  cost: 10.322486  card: 100000.000000  bytes: 1600000.000000
***********************

Krásně ještě printne jaký z toho má výstup a já se snad někdy tady na blogu (sám již jsem viděl) dostanu k tomu, že ukáži jak stavový prostor odřízne když mu při permutování vzroste cena nad cenu nejlepšího plánu. Bohužel na dvou tabulkách ukázat nejde. Dále následuje druhá a poslední permutace:

***********************
Join order[2]:  TBL_10000[TBL_10000]#1  TBL_1000[TBL_1000]#0

***************
Now joining: TBL_1000[TBL_1000]#0
***************
NL Join
  Outer table: Card: 10000.000000  Cost: 7.046579  Resp: 7.046579  Degree: 1  Bytes: 
Access path analysis for TBL_1000
  Scan IO  Cost (Disk) =   1.354300
  Scan CPU Cost (Disk) =   205607.200000
  Total Scan IO  Cost  =   1.354300 (scan (Disk))
                         + 0.000000 (io filter eval) (= 0.000000 (per row) * 1000.000000 (#rows))
                       =   1.354300
  Total Scan CPU  Cost =   205607.200000 (scan (Disk))
                         + 50000.000000 (cpu filter eval) (= 50.000000 (per row) * 1000.000000 (#rows))
                       =   255607.200000
  Inner table: TBL_1000  Alias: TBL_1000
  Access Path: TableScan
    NL Join:  Cost: 13614.418874  Resp: 13614.418874  Degree: 1
      Cost_io: 13550.000000  Cost_cpu: 2557921550
      Resp_io: 13550.000000  Resp_cpu: 2557921550

  Best NL cost: 13614.418874
          resc: 13614.418874  resc_io: 13550.000000  resc_cpu: 2557921550
          resp: 13614.418874  resp_io: 13550.000000  resc_cpu: 2557921550
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = JOIN
Join Card:  100000.000000 = outer (10000.000000) * inner (1000.000000) * sel (0.010000)
Join Card - Rounded: 100000 Computed: 100000.000000
  Outer table:  TBL_10000  Alias: TBL_10000
    resc: 7.046579  card 10000.000000  bytes:   deg: 1  resp: 7.046579
  Inner table:  TBL_1000  Alias: TBL_1000
    resc: 3.005178  card: 1000.000000  bytes:   deg: 1  resp: 3.005178
    using dmeth: 2  #groups: 1
    SORT ressource         Sort statistics
      Sort width:          10 Area size:      131072 Max Area size:     2097152
      Degree:               1
      Blocks to Sort: 18 Row size:     14 Total Rows:          10000
      Initial runs:   2 Merge passes:  1 IO Cost / pass:         12
      Total IO sort cost: 30.000000      Total CPU sort cost: 46137630
      Total Temp space used: 254000
    SORT ressource         Sort statistics
      Sort width:          10 Area size:      131072 Max Area size:     2097152
      Degree:               1
      Blocks to Sort: 4 Row size:     25 Total Rows:           1000
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 40156645
      Total Temp space used: 0
  SM join: Resc: 42.451655  Resp: 42.451655  [multiMatchCost=0.226657]
SM Join
  SM cost: 42.451655 
     resc: 42.451655 resc_io: 40.000000 resc_cpu: 97349433
     resp: 42.451655 resp_io: 40.000000 resp_cpu: 97349433
  Outer table:  TBL_10000  Alias: TBL_10000
    resc: 7.046579  card 10000.000000  bytes:   deg: 1  resp: 7.046579
  Inner table:  TBL_1000  Alias: TBL_1000
    resc: 3.005178  card: 1000.000000  bytes:   deg: 1  resp: 3.005178
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.055405  #ptns: 1
    hash_area: 32 (max=512) buildfrag: 19  probefrag: 4  ppasses: 1
  Hash join: Resc: 10.356484  Resp: 10.356484  [multiMatchCost=0.249322]
  Outer table:  TBL_1000  Alias: TBL_1000
    resc: 3.005178  card 1000.000000  bytes:   deg: 1  resp: 3.005178
  Inner table:  TBL_10000  Alias: TBL_10000
    resc: 7.046579  card: 10000.000000  bytes:   deg: 1  resp: 7.046579
    using dmeth: 2  #groups: 1
    Cost per ptn: 0.044072  #ptns: 1
    hash_area: 32 (max=512) buildfrag: 4  probefrag: 19  ppasses: 1
  Hash join: Resc: 10.322486  Resp: 10.322486  [multiMatchCost=0.226657]
HA Join
  HA cost: 10.322486 swapped 
     resc: 10.322486 resc_io: 10.000000 resc_cpu: 12805157
     resp: 10.322486 resp_io: 10.000000 resp_cpu: 12805157
Join order aborted: cost > best plan cost

Nejlepší plán již máme a zdálo by se že by to měl být konec, nicméně co je dále ještě zajímavé je část, kde nově nyní v Oracle 12.1c počitá inflexní bod pro adaptivní plán :) Nicméně u mě došel k celkem jasnému závěru:

DP: skipping adaptive plan due to NLJ heuristics

A do je vše ;) Ještě je tam zvažování jestli se to bude cachovat a pak už jen print všech parametru včetně patches a výpis exekučního plánu.  Kdyby někdo byl snad liný a měl zájem se na trc jen podívat, tak zde download – je tam jedna ještě jedno nebo dvě sql, které jsem spustil v té session.

 Posted by at 21:48
Oct 242014
 

Kolega si kdys postěžoval, že blog moc často neaktualizuji a má pravdu ;) Nicméně dneska si ten post doslova vynutil, když mi potrápil s jedním selectem. Prvně bych chtěl všem připomenout, že to v Oracle funguje (alespon v zapatchované 11.2g) tak, že pokud Vám select vrací něco, co vypadá jako nesmysl – máte to špatně vy, ne Oracle. V mé první práci mě tohle myšlení naučil kolega, který nebyl ochoten přemýšlet o bugu Oracle dříve než za několik hodin doumání nad selectem. A já jsem tohle posunul ještě dál – tak 14 dní. Pokud jste daným selectem neztrávili alespoň 14 dní, pak není racionální přemýšlet nad tím, že je to bug Oracle. Za svůj život jsem potkal přesně 3 selecty, které vrátily špatný výsledek a byl to bug (hashjoin u subpartitions , left join na dual a to poslední si nepamauji – všechno již dávno fixnuto Oraclem). Zatímco já jsem na svůj select koukal asitak přesně bžilionkrát s tím, že “to není možné” a vždycky jsem končil přiznáním vlastní chyby a chutí ukousknout si vlastní ucho, když jsem zjistil, že to celou dobu přehlížím..

Jedinou vyjímkou, kdy bych přemýšlel nad chybou Oracle jsou ještě chvíle, kdy jste si hráli s datovým slovníkem (updaty pod sysem apod.) či je to chyba, kterou by mohlo vyvolat poškození datového bloku či indexu/objektu na mediu. Ale to je jen tak mé doporučení.

A nyní čím mi potrápil kolega (vypadalo nechtěně ačkoliv v průběhu zjišťování, v čem je zakopaná ponorka jsem ho podezíral se spousty věcí včetně VPD policy). Nejprve když mi popisoval svůj problém mluvil o constrainech a o tom, že mu “nefunguje cizí klíč”, což neznělo moc zajímavě, upřímně ;) Do chvíle, než mi ukázal výsledky těchto dvou selectů:

Jiný výsledek

Všimněte, že výsledek je pokaždý jiný (počet řádků – sloupce jsou vždy z jiné tabulky) ačkoliv jediné co jsem změnil jsou sloupce v projection – join a predikát je naprosto stejný. Takže kde, že je ta zakopaná ponorka? Většinou v takovýhle případech zkouším exekuční plány – minimálně pro svou představu a oživení nápadů:

(explain plan)

Tohle píšu doma, takže to nemá naprosto stejný exekuční plán jako to mělo u nás v práci (speciálně to dělám na 12.1c tudíš tam mám navíc krok COLLECTORU a mám naprosto jiné tabulky), nicméně jako v práci to není ničím zajímavým, zkusme to pro b.*:

Tohle již vypadá zajímavěji  – mám tam join na dvou tabulkách, ale exekuční plán jde jen do jedné tabulky. Krásná síla Oracle, který zjistí, že join na tabulku A vůbec přístup do ní není třeba. Důvodem je FK constrain. Ten zaručuje, že join by zafungoval všude, zároveň na a.id je primární klíč, který zamezuje “pronásobení řádků”.  Takže nakonec po optimaizaci zbyde jen čtení z tabulky na predikát dsk=1 a join Oracle ztransformoval na  b.album_id is not null.

Nicméně to je “co je v exekučním plánu” a úplně to nenasvěčuje, proč dva selecty vrací jiné výsledky. Takže jsem si řekl, co to udělá, když Oracle donutím si tu tabulku vzít a fyzicky je na sebe najoinovat – zkusil jsem hinty na full, use_hash. – a odnáším si z toho ponaučení: tohle se ohintovat nedá, jakmile to Oracle vykydlí hned na pravidlech, neni žádná šance na to šahat hintem. Což je věc nad kterou jsem nikdy nepřemýšlel – většinou člověk hintuje, to co drhne v exekučním plánu, nikoliv takhle od pohledu do selectu. Každopádně nejde. Co mi ale napadlo až doma je vypnout tuhle featuru kydlení pomocí hintu OPT_PARAM. Což má nevýhodu v tom, že já vlastně ani nevím, co přesně vypnout – je to nějaký ze bžilonu zkrytých parametrů. Takže jsem nahodil brutálnější hint OPTIMALIZER_FEATURE_ENABLE, který umožní zadat pouze verzi ;) A tak se stačí jen modlit, aby to nebylo hned od první verze, ale nebylo:

Fajn zanfungovalo – hned na poprvé ;) Taky jsem teda zbaběle střelil nízkou verzi Oracle. Nicméně případně hrubou silou by šlo zjistit od kdy to začne zase vracet dva řádky a pak případně udělat rozdíl hidden parametrů a tipnout si, co přesně kde je třeba vypnout. Ale to bylo jen tak pro zajímavost.

Každopádně to visí a padá na constrainu, mrknu jsem co kolega psal ve scriptech a s úsměvěm jsem na tváři objevil slovo “novalidate”. Řikám si ha, má tam bordel v datech nedodržuje FK/špinavá data a přidal constrain novalidate, takže zvalidovat:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
table TRACK altered

Po té, co mi tenhle příkaz nevyfuckoval, mi to začalo postupně kazit pátek :-( Každopádně po delší odmlce jsme přišli, na to, že je problém s attributem RELY na constrainu. A to je můj druhý nový poznatek pro dnešek – pokud je na constrainu RELY, tak vás následné VALIDATE nevyhodí a normálně proběhne a co horší, proběhne pak i zpětné NORELY, jinými slovy, udělá to tahle kombinace:

ALTER TABLE track MODIFY CONSTRAINT fk_track RELY;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track NORELY;

Pekelné ;) Takže je třebas to zpravit:

ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE NOVALIDATE;
ALTER TABLE track MODIFY CONSTRAINT fk_track ENABLE VALIDATE;

SQL Error: ORA-02298: není možno zkontrolovat platnost (AZOR6.FK_TRACK) - nebyly nalezeny nadřízené klíče
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause: an alter table validating constraint failed because the table has
child records.
*Action: Obvious

A od téhle chvíle již to není vůbec zajímavé, v  tabulce je nějaký bordel, který je třebas promazat nebo se rozloučit sconstrainem. Takže asi tak, hébičkám zdar a pěkný víkend.

 Posted by at 21:35
Oct 052014
 

Pokračování krátkého zkoumání jaké hodnoty se objevují ve sloupci STARTS v pohledu V$SQL_PLAN_MONITOR. Předchozí díl se věnoval počtu startů u tabulky, která není partišnovaná a dnešní díl se tedy bude věnovat tomuto sloupci u partitiovaných tabulek. Další díl budu věnovat paralelním selectům a pak se uvidí ;) Protože je to dost zajímavý sloupec a těch kombinací a zajímavých operací je spousty, ale muj čas je omezen. Takže hurá na to!

Takže pojďme udělat rovnou tabulku s nějakýma partitions:

create table tbl_range (a_column)
PARTITION BY RANGE(a_column)
(
PARTITION p1 VALUES LESS THAN(1),
PARTITION p2 VALUES LESS THAN(2),
PARTITION p3 VALUES LESS THAN(3),
PARTITION p4 VALUES LESS THAN(4),
PARTITION p5 VALUES LESS THAN(5),
PARTITION p6 VALUES LESS THAN(6)
) as
select mod(rownum,6) from dual connect by level<1001;

A nyní select:

select /*+ MONITOR */ * from tbl_range;

Asi by člověka napadlo, že bude mít tolik startů, kolik má partitions. A přesně tak tomu je, ovšem s menším ale – protože první SQL Developer fetchne pouze 50 řádků (defalutní hodnota), takže je třeba proscrollovat a dostáváme počet startů rovných 6ti:

(počet startů – range)

Nebylo zajímavé, že? Prozradím, že s INLIST a HASH partitions to funguje naprosto stejně, daleko zajímavější je INVERVAL partitions. A vlastně důvodu proč tenhle článek vzniknul:

CREATE TABLE tbl_rang_i (a_column NUMBER(10))
PARTITION BY RANGE(a_column)
INTERVAL(13)
(PARTITION base VALUES LESS THAN (0)
);

A nyní insertneme nějaká data:

insert into tbl_rang_i
select rownum from dual connect by level<=400 union all
select rownum+800 from dual connect by level<=400;

Konkrétně je to 800 řádků. Čísla od 1..400 a 800..1200. Takže se podívejme, kolik má tabulka partitions:

select owner,table_name,partition_count from all_part_tables where lower(table_name)='tbl_rang_i';

Počet partitions v tomhle view je 1048575, překvani? Taky jsem nedávno byl ;) Ale Oracle v tomhle view pro tabulky na kterých je interval partitioning udává Oracle vždy číslo 1048575. Číslo není nějak moc magické, udává maximální počet partitions, který tabulka může být.  Takže zkusime jiné view:

select count(*) from all_tab_partitions where lower(table_name)='tbl_rang_i';

Je jich 64. Lehce podezřele binárně kulaté číslo ;) Ale to je jen náhoda – začínání od 0 a dva intervaly v rozmezí 1..400 a 800..1200 (takže něco jako select 2*(ceil(400/13))+1+1 from dual).  A nyní konečně počet startů:

select /*+ MONITOR */ * from tbl_rang_i;

(počet startů – range-interval)

94. Což je lehce podezřelé číslo. Speciálně, když čekáte až Vám doběhne select a full table scan nad tabulkou co má 64 partitions přesáhne 65 a vesele si pokračuje. Nicméně po několika mých testech jsem doše k tomu, že Oracle pro interval partitions připočítává do STARTS i “mezery”. Tedy výpočet pro tento případ je select ceil(1200/13)+1 from dual tzn. 94 (jedna je pro BASE partition).

Inu počty startů:

  • Pro range, list, hash – vždy počet partitions
  • Pro range-interval – (spread mezi LOW a HIGH hodnotou v partiton sloupci) / intervalem partišnování (případně +1 pokud base partition je mimo)
  • Precedenci berou “nižší karta” tedy full scanu nad supartišnovanou tabulkou je ve sloupcu STARTS počet čtených subpartitions.

Bohužel vzhledem k mému omezenému času tento článek končím, ale napadají mi daleko zajímavější scénáře – systém a referenční partitioning, partition wise-join a počet startů..

 Posted by at 11:52