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
Mar 222015
 

Ahoj, dneska velice krátký post na pár PDF a přednášky, které mi v posledním dejmetomu měsíci zaujaly a myslím, že stojí za to si je přečíst. Jinak ale určitě budu pokračovat v načatém tracování CBO a jeho eventy 10053, případně 10054. A rozhodně s tím hnedtak nepřestanu ;) – v rámci možností omezeného času - příští víkend se jede na koncert Wintersun  do Vídně (áách).  Navíc vzhledem ke kráse naší vyučující pondělní švédštiny se musím pečlivě připravovat a psát domácí úkoly a v průběhu semestru ladit technologie jak jí ve švédštině pozvat na večeři..

Slajdy o tracování eventů. Koukal jsem, že ke všem věcem které jdou tracovat (ora-10000 až ora-10999 + nějaké další) se dotyčný dostal také přes brutal force na SQLERRM. Narozdíl od mého psa -  ne však náhodou ;) . S tím, že se mu zalíbily tyto eventy:

• 10046 (Millsap )Enable SQL statement timing
• 10053 CBO Enable optimizer trace
• 10079 Trace data sent/received via SQL*Net
• 10235 Check memory manager internal structures
• 10032 Dump sort statistics
• 10231 Skip corrupted blocks on full table scan
• 10015 Dump undo segment headers
• 10013 Monitor transaction recovery

Mě se jich líbí teda víc, například pro CBO jsou tam dva – s tim, že jeden je pro paralelní – 100054. A postrádám tam tracování deadlocku, což je užitečné (ačkoliv v případě ora-0060 oracle vybleje .trc soubor tak či tak). A hashjoinování, což je taky krásný trace report.

Dokument, který vysvětluje jak je to s Oracle a histogramama při joinování. Respektive jak je to s odhadem selectivty joinů. Nutno upozornit, že dotyčný dokument je docela starý a tudíš to na nověších verzích Oracle bude vypadat nejspíše úplně jinak. Co je ale super, věnuje se zkoseným datům. A na začátku je hned velice zajímavý vzorec na odhad cardinality, pokud nejsou histrogramy a množiny si matchnou na hranicích – “princip inkluze“:

join cardinality:=cardinality(A) * cardinality(B) * (1/max(ndv(a),ndv(b)))

A můj další tip je na přednášku/slajdy o latech, mutex apod.

A když už jsme u těch latch/mutexů tak tento dokument je trošku více specifičtější – také mutexy, ale tentokrát jejich chování na child/parent kurzorech. Hodně se věnuje zámkům na parent cursoru pokud je tam nutné hodit ještě nějaký child cursor pod parenta. Moc pěkné.

A pokud už snad máte dost všech těch vzorců a možností a view a mutexů a KGX, odhadů cardinality na HbH pro eq-join, tak něco hodně upovídaného na odlehčenou - edice.

Tak to by bylo všechno, a jinak pro kolegu, pravidlo palce: AUTO_SAMPLE_SIZE je cca o 2% méně přesné než estimate_percent = 100, ale za to 10x rychlejší. Note: neplatí pro extrémně zkosená data a platí pro 11g+.

Hébičkám a Winterusun zdar!

 Posted by at 17:37
Mar 182015
 

Možná to opět o delší odmlce vypadá, že mi přejelo auto nebo jsem snad dokonce přišel o lásku k Oracle ;) Ne, nic takového se neděje. Akorád mezi vztah já a Oracle se dostala švédština. A všichni víme, že k tomu aby byl den dokonalý mu chybí dobrejch 5-6 hodin..

Měl jsem poustu tipů na články, spoustu věcí na zkoušení - za dobu nepsaní jsem se spoustu věcí o Oracle dozvěděl, spoustu zapomnil.. atd. A dokonce mam na blogu spoustu nedokončených věcí a pokusím se s tim něco udělat. Nicméně v Oracle jsem narazil na něco, co je neuvěřitelně SEXY a musím to dostat z hlavy a mé srdce bije zrychleným tepem ;) Takže here we go!

Nevím jaké procent lidí, co čtou tenhle blog si něco vybaví při čísle 10046. Pokud nic, tak za domací úkol doplnit ;) Co znají si automaticky vybaví následující:

ALTER SESSION SET EVENTS '10046 trace name context forever,level 12'

Když už to programátor viděl xkrát za svuj život a podobný výstup si umí vytáhnout z různých view, tak to prostě použivá a nepřemýšlí nad tím, jak moc cool to je. Ovšem trace eventu 10046 neni zdaleka jedniné, co jde tracovat a ovlivňovat. Dlouho jsem hledal nějaký list a nenašel :( Paradoxně mi v tomhle pomohla švédština kdy jsem přes brutal force vypisoval chybové hlášky z SQLERRM a vypisoval si švédskou chybu a anglický překlad. V rámci brutal force jsem objevil spoustu zajímavých chybových hlášek, které jsem nikdy neviděl a asi ani nikdy neuvidím. Ale narazil jsem taky na list toho co je možné tracovat – konkrétně jsou to hodnoty mezi 55 a xxx.  Z takových těch zajímavých věcí je to deadlock, hashjoin (to je mazec, možná někdy napíšu nějaký článek) a pak trace event 10053 ze kterého jsem si sednul na prdel.

A nyní co to dělá:

Tracuje to CBO optimalizátor. A to do takové úrovně, že to píše všechny transformace které to zkouší, všechny možné rewrites, takové ty věci jako tranzitivní uzávěry, vykostění predikátu, následuje spočitání nákladu na jednotlivé přístupové cesty – s tím, že to vypíše estimate řádku, pamět, costy (a to ještě před zaokouhlením !), všechny parametry optimalizátoru, hinty, a pak postupně přijde něco co je uplně nejvíce cool – permutace v optimalizačním plánu a výpisy costu pro jednotlivé vypermutované plány + případně důvod zamítnutí, včetně takových věcí jako, že to Oracle killne uprostředka protože už to přelezlo přes cost prozatím nejlepšího nalezeného plánu, je tam vidět jak si při optimalizaci CBO spouští sql na dynamic sampling.  Prostě kurva mazec !

Asi si dovedete představit jak mi bylo - ještě líp než když se mi v pondělí povedlo na třetí hodině švédštiny vykoktat švédsky učitelce kompliment, že má nádherné modré oči a hezký úsměv ;)  Tohle asi bude trošku rozsáhlé, protože ty logy a výstupy z trace jsou velké, takže tenhle článek je první a hodně high level co to vlastně je:

Nejprve zjistit kde jsou trace logy:

select value,name from v$parameter2 where name like 'user%dump%des%';

U mě je to C:\app4\AZOR\diag\rdbms\orcl12\orcl12\trace. Dobře a ted

ALTER SESSION SET EVENTS10053 trace name context forever,level 1′;

A nyní nějaké SQL a začněme tím úplně nejjednodušším:

select * from dual;

A pak ideálně nic již nedělat, exit ze session nebo vypnout. A nyní co ten soubor obsabuje :

  • Hlavičku (verze Oracle, OS, umístění, počet CPU, velikost paměti..)
Trace file C:\APP4\AZOR\diag\rdbms\orcl12\orcl12\trace\orcl12_ora_4932.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Windows NT Version V6.1 Service Pack 1 
CPU                 : 8 - type 8664, 4 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:3546M/8043M, Ph+PgF:2830M/8042M 
Instance name: orcl12
Redo thread mounted by this instance: 1
Oracle process number: 9
Windows thread id: 4932, image: ORACLE.EXE (SHAD)
  • Informace o SESSION – id, modul, action..
*** 2015-03-18 22:16:18.321
*** SESSION ID:(60.7) 2015-03-18 22:16:18.321
*** CLIENT ID:() 2015-03-18 22:16:18.321
*** SERVICE NAME:(SYS$USERS) 2015-03-18 22:16:18.321
*** MODULE NAME:(sqlplus.exe) 2015-03-18 22:16:18.321
*** ACTION NAME:() 2015-03-18 22:16:18.321
  • Query blocky a jejich pojmenování
Registered qb: SEL$1 0x170b24b8 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=142 hint_alias="DUAL"@"SEL$1"

SPM: statement not found in SMB
SPM: capture of plan baseline is OFF

V tomhle případě je tam jeden query blok (SEL$1). A dole hláška SPM Zkratka neni dole vysvětlena, ale podle kontextu to bude SQL Plan Management (dbm_spm).

  • Check na ADOP (automaic degree of parallelism)
**************************
Automatic degree of parallelism (AUTODOP)
**************************
Automatic degree of parallelism is disabled: Parameter.

Jen informace o tom, že je to vypnuto.

  • Seznam a vysvětlení zkratek, které jsou použity (bohužel né všechny)
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
OJPPD - old-style (non-cost-based) JPPD
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
OST - old style star transformation
ST - new (cbqt) star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
JF - join factorization
SLP - select list pruning
DP - distinct placement
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUSPEED - CPU Speed 
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  128: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  256: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
AP - adaptive plans
***************************************
  • Parametry optimalizátory – všechny, včetně nastavení + fixy a jejich stav

To jako úplně vypisovat nebudu, je toho fakt hodně. Co je fajn, je že jsou tam vypsány i nedokumentované parametry. Což dělá tenhe trace sámo o sobě zajímavým ;)

  • Pak následují pokusy o transformace query blocků
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for a5ks9fhw2v9s1.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
OBYE:   Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:     OBYE bypassed: no order by to eliminate.
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for a5ks9fhw2v9s1.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
 ?? 
apadrv-start sqlid=11730480049179961089
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$1 (#0).
  :
    call(in-use=1136, alloc=16344), compile(in-use=54968, alloc=58616), execution(in-use=2632, alloc=4032)

Upřímně nevím co to dělá tady. Že od 10g Oracle permutuje bloky nikoliv celé sql jsem již někde postřehl. Oracle vzdal permutace na celém sql, protože pro takové SQL jako generuje například Siebel (150+ tabulek a joinu) jsou často permutace nemožné (ostatně ani faktoriál 10ti neni malé číslo – 362880, pokud si pamatuji tak před timhle přístupem bylo maximum 60000 a nazdar bazar). Takže raději rozsekání na části, ty permutovat a pak případně permutovat proti sobě. Proč ale přepisy nedělá na urovní celého sql netuším :(, ač si umím představit, že většina z nich nepůjde udělat přes hranice query bloku, tak něco by určitě šlo. Na mém select  from dual nemá co vymyslet  – zkusil a neuspěl.

  • Kontrola peek variables na cursoru

Tam jen zeje hláška, že nic. Nic být ani nemůže, nemam žádnou bid variable :D

  • Výpis SQL po transformaci, tzn ten který pujde na permutování
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "DUAL"."DUMMY" "DUMMY" FROM "SYS"."DUAL" "DUAL"
kkoqbc: optimizing query block SEL$1 (#0)
        
call(in-use=1136, alloc=16344), compile(in-use=64416, alloc=66952), execution(in-use=2632, alloc=4032)

kkoqbc-subheap (create addr=0x00000000170BF760)

Žádné transformace se nepovedly, takže stejné sql jako originál.

  • Výpis systémových statistik
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 3264 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM:  10 milliseconds (default is 10)
  MBRC:       NO VALUE blocks (default is 8)
  • Statistiky o tabulce
Table Stats::
  Table: DUAL  Alias: DUAL
  #Rows: 1  #Blks:  1  AvgRowLen:  2.00  ChainCnt:  0.00

Na tabulce dual není nic zajímavého ;) Jeden řádek, délka 2, žádné chainy.

  • Analýza přístupu do tabulky
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for DUAL[DUAL] 
SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: DUAL  Alias: DUAL
    Card: Original: 1.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  2.00  Resp: 2.00  Degree: 0
      Cost_io: 2.00  Cost_cpu: 7271
      Resp_io: 2.00  Resp_cpu: 7271
  Best:: AccessPath: TableScan
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0

Na tabulce dual není nic zajímavého. Stejně má Oracle jen možnost full table scan, žádný index tam neni.

  • Permutace
OPTIMIZER STATISTICS AND COMPUTATIONS
PJE:  Bypassed; QB has a single table SEL$1 (#0)

Oracle dokonce ani nezkoušel permutovat, nemá s čím, máme jen jednu tabulku, takže taky nic.

  • Finální plán
kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=0(MB) time=0(ms)
kkeCostToTime: using io calibrate stats maxpmbps=200(MB/s) 
 block_size=8192 mb_io_count=1 mb_io_size=8192 (bytes) 
 tot_io_size=0(MB) time=0(ms)
Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus.exe action=
sql_id=a5ks9fhw2v9s1 plan_hash_value=272002086 problem_type=3
----- Current SQL Statement for this session (sql_id=a5ks9fhw2v9s1) -----
select * from dual
sql_text_length=19
sql=select * from dual
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | DUAL    |     1 |     2 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+

Uf, pak už print other_xml a tedy print outline hintu a to je vše.  Takže to byl spíše takový rychlý popis tak tento trace file vypadá. Oracle neměl na takhle jednoduchém sql žádnout možnost optimalizace, transformace, permutace a předem byl předurčen jít full table scanem. Nicméně doufám, že jsem na to ukázal pár zajímavých věcí, ačkoliv to zajímavější ještě přijde, v nějaké v příštích postů bych rád prověřil vždycky nějakou zajímavou část.

A nyní bez zkladu co mi praštilo do oka:

  • Pokud se nejede hard-parse trace Oracle nevybleje
  • Když Oracle dělá dynamic sampling (měl jsem nastavo 2) tak používá klauzli SAMPLE BLOCK, ale to jen v případě, že tabulka má více datových bloku než dynamic samling chce. Pokud má méně datových bloků než chce dynamic sampling CBO nahulí sql na celou tabulku.

S tímhle číslem 10053 si tu doufám, ještě užijeme spoustu legrace na blogu, je to fakt vymazlená věcička. Hébičkám zdar!

 Posted by at 22:31
Nov 042014
 

Dneska jeden velice krátký tip, který však má své vlastní kouzlo. Funguje od PL/SQL Developer verze 9. Občas se v PL/SQL Developeru prostě hodí spustit více selectů/příkazů za sebou, nějak takto:

select * from dual;
select * from dual;
select * from dual;

(nepojmenované taby)

Funguje skvěle, PL/SQL Developer umístí výsledky do záložek. Co občas vadí je pojemování těchto záložek, v případě více selectů/příkazů se záložky jmenují podobně či dokonce stejně a to aby se v tom pak prase vyznalo;)

Takže co s tím? Komentář nad příkazem, který dokáže TAB s výsledkem pojmenovat:

--TAB=Select z dualu číslo 1 a žblunk
select * from dual;
--TAB=Select z dualu číslo 2 a hafík
select * from dual;
--TAB=Select z dualu číslo 3 a budlík
select * from dual;

(pojmenované taby)

 Posted by at 01:23
Oct 292014
 

Dnešní post bude o pořadí a vyhodnocování predikátů ve WHERE podmínce.  Původně to mělo být spíše o hintu ORDERED_PREDICATES, ale pojďme to trošku rozšířit ;) Je asi známá věc, že pokud píši do podmínky něco ve smyslu:

IF podmínka1 AND podmínka2 THEN

Že Oracle pokud nebude splněna podmínka1 již nebude spouštět podmínka2, protože k tomu není důvod – ifem již to nemůže projít a nic to na té skutečnosti již nezmění. Což se samozřejmě netýká jen Oracle - většina jazyků pracuje přesně takto. Co je méně známý fakt je, že podobně pracuje Oracle i ve vyhodnocování WHERE podmínky. Respektive pravida jsou trošku složitější. Toto je pořadí dle dokumentace:

  • Predikáty bez uživatelských funkcí, typových metod, subselectů jsou vyhodnocovány jako první.
  • Predikáty s uživatelskou funkčí či typových metod, které mají přidělenu cost jsou vyhodnocovány podle costu v zestupném pořadí.
  • Predikáty kde je uživatelská funkce či typová metoda a nemají přiřazeny costy.
  • Pak jsou vyhodnocovány podmínky, které se do WHERE dostaly tranzitivně přes jinou podmínku (propasoval je ta optimalizátor – push predicate apod.)
  • A po té jsou vyhodnocovány podmínky, které obsahují subselecty

Začněme příkladem, který je možné vidět prakticky na každém blogu:

create table t_predicates as
select 'Suicide silence' as kapela, 1 as id from dual union all
select '2' as kapela, 2 as id from dual union all
select 'Wintersun' as kapela, 3 as id from dual

(tabulka)

A příkladový select:
select * from t_predicates where kapela=2 and id=2;

Idea je myslím jasná – pokud Oracle začne vyhodnocovat nejprve kapela=2, pak jsme v pytli a skončíme na ORA-01722, vlastně již jsem na to kdysy nějaký post psal: Nebezpečná chyba a exekuční plán. Pokud Oracle začne nejprve s id=2, pak řádky id=1 a id=3 vyloučí automaticky a nebude nikdy testovat Suicide Silence=2 a Wintersun=2 a tedy ani zkoušet konvertovat oblíbené kapely na čísla a select doběhne ;)

A nyní jaký je rozdíl mezi:
select * from t_predicates where kapela=2 and id=2;
select * from t_predicates where id=2 and kapela=2;

Popravdě – nevím. V dokumentaci, se píše že predikáty, které jsou na stejné úrovni jsou vyhodnocovány odzadu, má to ovšem nějaké “ale” a tím je třeba i selectivita predikátů – ty nejvíce selektivní jdou pochopitelně jako první. V tomto případě selecty doběhnout v obou verzích  (12.1c) – což znamená, že mi Oracle kašle na “odzadu“, ale prioritně řadí id=2 z nějakého důvodu. Napadají mi dva - selectivita, ačkoliv oba predikáty jsou naprosto stejně selectivní (podle explain plánu), tak možná až po za okrouhlení a Oracle je interně seřadí před zaokrouhlením. Druhá varianta (a podle mě pravděpodobnější) je ta, že Oracle interně musí zkusit zkonvertovat sloupec kapela na number (alespoň to zkusit), což je volání funkce, což je drahé a proto až druhé.

Dobře, takže to zkusme s hintem ORDERED_PREDICATES:

Prvně jde Oracle do kapela=2, čímž okamžitě končí na ORA-01722, ale opačně je to lepšejší:

Tímhle většina blogu končí, nicméně pojďme dál - do funkcí. Ovšem nejprve založme nějakou zajímavějšejší tabulku:

create table t_pred_func as
select rownum id, case when rownum<=5 then 1 else 2 end haf from dual
connect by level<11;

A nyní tam začněme navěšovat funkce a hrát si s tim. Většinou se do funkcí dává dbms_output apod, aby bylo vidět kolikrát je daná funkce volaná. Nicméně dává mi smysl tam nyní nacpat dbms_lock.sleep.  Už jen proto si osvojit, že na tomhle pořadí záleží, a že to má výkonové dopady. Takže založme dvě funkce – jedna, která Oracle zdrží o 100 sekund a druhá, která zdrží Oracle o 5 sekund. Obě vrací hodnotu 2. Takhle o řád+ od sebe, aby to bylo krásně vidět a daly se počty volání dopočítat a krásně od sebe oddělit.

create or replace function f_sleep_100s return number
is
begin
dbms_lock.sleep(100);
return 2;
end;
/
create or replace function f_sleep_5s return number
is
begin
dbms_lock.sleep(5);
return 2;
end;

Fajn, a nyní pojďme začít hébičkovat:

select * from t_pred_func where id=f_sleep_5s and haf=f_sleep_100s;

Oracle má na predikátech navěšeny dvě uživatelské funkce. Nemá naprosto žádnou představu o costu ani selectivitě – respektive obě funkce mají defalutní cost a defalutní selectivitu.

Trvá 150 sekund. Konkrétně 10x Oracle testuje id=f_sleep_5s a v jednom případě je ta podmínka splněna (id=2) a v té chvíli musí jít i do testu  haf=f_sleep_100s tedy 10 x 5 sekund + 1x 100 sekund. Daleko horší je, když si to vynutíme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s and id=f_sleep_5s;

Tentokrát to však trvá 1025s . Konkrétně 10x Oracle testuje haf=f_sleep_100s s tím, že haf=2 ja splněno v 5ti případech a Oracle tedy musí jít ještě do testu id=f_sleep_5s.  Což dělá celkem 10x 100 sekund + 5x 5 sekund. No dobře, pojďme ještě do OR operátoru:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_100s OR id=f_sleep_5s;

V tomhle případě opět Oracle začne funkcí f_sleep100s, ovšem jeho možnosti logického vykydlení jsou naprosto jiné. Nejprve 10x ověří haf=f_sleep_100s a všude tam, kde je to nesplněno (5 případů, kde je haf=1) musí zkusit jestli se náhodou to nesplněné nepodaří splnit v druhé podmínce. Tedy opět 10x 100 sekund + 5x 5 sekund  = 1025s, ovšem tentokrát se id=f_sleep_5s volala u jiných řádků. Takže si to vynuťme opačně:

select /*+ ordered_predicates */ * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Pro tento případ Oracle musí otestovat haf=f_sleep_5s pro každý řádek a tam kde to není splněno (5x) ještě provolat id=f_sleep_100s. Což tentokrát dělá 10x 5sekund + 5×100 sekund. Inu, trváme 550 sekund. Ovšem slíbil jsem, že tenhle post nebude jen o hintu ordered_predicates, takže si pojďme vynutit pořadí provádění funkcí pomocí přiřazení jim nedefalutního costu:

ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,40,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,10,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Přiřazená COST samozřejmě zdaleka neodpovídá reálu, funkce která čeká 100 sekund by měla obrovský cost. Nicméně mým cílem není to přesně spočítat (pokud chcete, pak je třebas mrknout do tabulek sys.aux_stats$ jako či dbms_stats.get_system_stats/dbms_stats.gather_system_stats  a spočítat si to.). Po spuštění trvá tehle select 550 sekund.  Což znamená, že Oracle jde prvně do funkce f_sleep_5s, takže prohoďme costy:

DISASSOCIATE STATISTICS FROM FUNCTIONS f_sleep_100s,f_sleep_5s;
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_100s DEFAULT COST (10,10,0);
ASSOCIATE STATISTICS WITH FUNCTIONS f_sleep_5s DEFAULT COST (10,40,0);
select * from t_pred_func where haf=f_sleep_5s OR id=f_sleep_100s;

Kupodivu nezafungovalo a select trval opět 550 sekund. Tedy nejprve jsem zkusil exekuční plány zda se opravdu COST funkcím přiřadil a on přiřadil. Takže wtf? Popravdě jsem na to nepřišel – nastavení cursor_sharing=exact, byly parasovány dvě sql, ale z nějakého důvodu Oracle nebral vpotaz nově přiřazené COSTy a nevyhodnocoval podle toho predikáty, nicméně úspěch jsem slavil když jsem ho donutil vyprázdnit shared_pool:

ALTER SYSTEM FLUSH SHARED_POOL;

Je to teda s vakuovou pumou na vrabce, správně bych měl flushnout jen to sql nebo objekty pod ním. A teprve po té to trvalo 1050 sekund. Oracle opravdu začal nejprve funkcí f_sleep_100s na základě mnou špatně přirazeného costu ;)  A zkusme ještě najaký příklad na select:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_5s+6 or id=f_sleep_100s+7) and (id=f_sleep_100s);

Celé SQL mi trvalo 625 sekund takže pojdme do nějakého toho vysvětlení, pro připomenutí data vypadají takto:

(data)

Hintem jsem vynutil provádění zleva, takže takhle to vypadá:

ID=1 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=2 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=3 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=4 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=5 – netrvá prakticky nic, vyfiltruje to select 5 from dual
ID=6 -  select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=7 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.
ID=8 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – tentokrát nesplěno v závorce v závorce již Oracle nemusí testovat f_sleep_100s+7. To padá. Musí však ještě otestovat id=f_sleep_100s To je nesplněno, nicméně přidalo to 100s do exekuce.. Celkem 105 sekund.
ID=9 – select 5 from dualsplněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je splněno takže ještě test na,  id=f_sleep_100s. Celkem 205 sekund.
ID=10 – select 5 from dual splněno Oracle musí vyhodnocovat dál. f_sleep_5s+6 – nesplněno v závorce musí Oracle ještě otestovat f_sleep_100s+7 kvuli OR. To je nesplněno, takže konec. Celkem 105 sekund.

Což je 105+105+105+205+105+(select z dualu, který s tím nezahýbe)=625 sekund, přesně podle očekávání trhu ;) Dobře? No, umíme to lépe:

select /*+ ordered_predicates */ * from t_pred_func where (id>(select 5 from dual)) and (id=f_sleep_100s) and (id=f_sleep_5s+6 or id=f_sleep_100s+7)

500 sekund. Vcelku krásně spočítatelné. Pro ID<=5 vypadnou řádky díky select  select 5 from dual Pro ID>5 se však musí spouštět f_sleep_100s,  což je 5x 100 sekund.Nikdy nedojde ke spuštění dalších predikátů, takže 500 sekund.

Tohle byl hodně úmělý příklad – podle definice jde na vyhodnocení filtrování podle subselectu jako poslední. Nicméně zde  je však daleko rychlejší select konstanty z dualu než spouštění funkce (5 a 100 sekund). Nicméně pro tento případ je tu ORDERED_PREDICATES. Pokud to nechám na Oracle záleží pak na tom jak jsem nastavi costy funkcím f_sleep_100s a f_sleep_5s+6 se kterou začne – v horším případě 1105 sekund.

Tolik k pořadí vyhodnocování predikátů. A všem, co si chtějí zaletět do USA na pár hodin zabruslit chci říct, že i když to není třebas možné, tak dokud není konec, máme ve skříni brusle a latenci se kterou reagujem na změnu držíme proklatě nízko.

 Posted by at 21:19
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