Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /DISK2/WWW/plsql.cz/www/wp-content/themes/suffusion/library/suffusion-walkers.php on line 0
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

 Leave a Reply

(required)

(required)

(required)

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