24
www.i.cz Vaše jistota na trhu IT Optimalizace SQL Jiří Kiml, ICZ a.s. 20/09/2007, Praha
www.i.cz
Vaše jistota na trhu IT
Optimalizace SQL
Jiří Kiml, ICZ a.s.20/09/2007, Praha
www.i.cz
Vaše jistota na trhu IT
Agenda
►Úvod
►Obecná moudra (pravidla)
►Oracle specifické
► Informix specifické
►Pokročilé techniky optimalizace
►Závěr
►Otázky + diskuze
www.i.cz
Vaše jistota na trhu IT
Obecná moudra►Optimalizace je drahá
www.i.cz
Vaše jistota na trhu IT
Co může být špatně
►Datové typy
►Db design
►Pomalé disky
►Pomalá síť►Špatné/chybějící statistiky
►Chybějící indexy
►Špatné sql příkazy
►… hloupí uživatelé …
www.i.cz
Vaše jistota na trhu IT
Co si někteří neuvědomují►Jen jeden index při join a sort
►Některé konstrukce vylučují použití indexu
►NOT in a NOT EXIST
►Like ‘%A%’ matches ‘*[a|b]*’
►!=► I temporary tabulky mohou mít indexy
►Funkční indexy
►Cost based optimalizátor se rozhoduje na základě statistik
www.i.cz
Vaše jistota na trhu IT
Optimalizátor►Cost based
►Vypočte cenu dotazu: cena IO + W* cena CPU
►Potřebuje statistiky
►Max, min, počet řádek
►Distribuce dat►Hinty/directivy
►Proč je (ne)používat
►{+EXPLAIN, AVOID_EXECUTE}
►/*+dynamic_sampling …*/
► /*+ GO_FASTER */
www.i.cz
Vaše jistota na trhu IT
Jak měřit čas►Problémy
►Jiní uživatelé
►Databázová cache
►Dlouhé trvání dotazu
►Zámky►Cost odhady optimalizátoru
►Vzorek dat (ladit v testovacím prostředí)
►Critéria čeho chceme dosáhnout (produkční prostředí)
►Logování sql příkazů
www.i.cz
Vaše jistota na trhu IT
Agenda
►Úvod
►Obecná moudra (pravidla)
►Oracle specifické
► Informix specifické
►Pokročilé techniky optimalizace
►Závěr
►Otázky + diskuze
www.i.cz
Vaše jistota na trhu IT
Oracle specifické►Temporary tabulky
►Divná implementace
►Session/transaction specifické
►Definice je persistentní
►Pozor na: Select from select
►Plán dotazu
►Sql plus
►Toad
►SQL Developer
►SQL Access/Tuning advisors
www.i.cz
Vaše jistota na trhu IT
Ukázky
►SQL Developer
►create index trn.ii_tr_adresa on trn.tr_adresa(jmeno);
►select * from trn.tr_adresa where jmeno = 'ICZ';
►Enterprise Manager – SQL Advisors
►Reálný příklad
www.i.cz
Vaše jistota na trhu IT
Analytické funkce► SELECT empno, deptno, COUNT(*) OVER (PARTITION BY deptno)
DEPT_COUNT FROM emp WHERE deptno IN (20, 30);
► EMPNO DEPTNO DEPT_COUNT ► ---------- ---------- ---------- ► 7369 20 5 ► 7566 20 5 ► 7788 20 5 ► 7902 20 5 ► 7876 20 5 ► 7499 30 6 ► 7900 30 6 ► 7844 30 6 ► 7698 30 6 ► 7654 30 6 ► 7521 30 6
www.i.cz
Vaše jistota na trhu IT
Agenda
►Úvod
►Obecná moudra (pravidla)
►Oracle specifické
► Informix specifické
►Pokročilé techniky optimalizace
►Závěr
►Otázky + diskuze
www.i.cz
Vaše jistota na trhu IT
Informix specifické►Temporary tabulky WITH NO LOG►Select from select v IDS 11 ;-(►Plán dotazu
►Set explain on
►Na serveru, v home, v aktualním adresáři
►{+EXPLAIN, AVOID_EXECUTE}►Statistiky
►Update statistic [Low|medium|high]
►Před IDS 11 – nutný update po vytvoření indexu
www.i.cz
Vaše jistota na trhu IT
► INFO - catalog: Columns Indexes Privileges References Status ...
► Display column names and data types for a table.
► ----------------------- stores_demo@plch_tcp ----------------------► Column name Type Nulls
► catalog_num serial no► stock_num smallint no► manu_code char(3) no► cat_descr text yes► cat_picture byte yes► cat_advert varchar(255) yes
Tabulka Catalog
www.i.cz
Vaše jistota na trhu IT
Indexy Catalogu► INFO - catalog: Columns Indexes Privileges References
Status ...► Display information about indexes for the columns in a table.
► ----------------------- stores_demo@plch_tcp ---► Index_name Owner Type/Clstr Access_Method Columns
► 108_21 kiml unique/No btree catalog_num
► 108_22 kiml dupls/No btree stock_num► manu_code
www.i.cz
Vaše jistota na trhu IT
Where manu_code = ‘HRO’►QUERY:►------►select * from catalog where manu_code = 'HRO'
►Estimated Cost: 6►Estimated # of Rows Returned: 5
► 1) kiml.catalog: SEQUENTIAL SCAN
► Filters: kiml.catalog.manu_code = 'HRO'
www.i.cz
Vaše jistota na trhu IT
create index► create index ii_catalog_manu_code on catalog(manu_code);
QUERY:
------
select * from catalog where manu_code = 'HRO'
Estimated Cost: 6
Estimated # of Rows Returned: 5
1) kiml.catalog: SEQUENTIAL SCAN
Filters: kiml.catalog.manu_code = 'HRO'
www.i.cz
Vaše jistota na trhu IT
Update statistics► update statistics for table catalog;
►Kolik řádků dotaz opravu vrátil ?
QUERY:------select * from catalog where manu_code = 'HRO'
Estimated Cost: 1Estimated # of Rows Returned: 7
1) kiml.catalog: INDEX PATH
(1) Index Keys: manu_code (Serial, fragments: ALL) Lower Index Filter: kiml.catalog.manu_code = 'HRO'
www.i.cz
Vaše jistota na trhu IT
Update statistics high► Update statistics high for catalog
QUERY:
------
select * from catalog where manu_code = 'HRO'
Estimated Cost: 2
Estimated # of Rows Returned: 12
1) kiml.catalog: INDEX PATH
(1) Index Keys: manu_code (Serial, fragments: ALL)
Lower Index Filter: kiml.catalog.manu_code = 'HRO'
► V IDS 11 už to bude jinak
www.i.cz
Vaše jistota na trhu IT
Agenda
►Úvod
►Obecná moudra (pravidla)
►Oracle specifické
► Informix specifické
►Pokročilé techniky optimalizace
►Závěr
►Otázky + diskuze
www.i.cz
Vaše jistota na trhu IT
Pokročilé techniky
www.i.cz
Vaše jistota na trhu IT
Pokročilé techniky►Cluster tabulky
►Tabulky v DB cache
►Paralelní dotazy
►Velikost bloku
►Speciální tabulky
►Speciální indexy
►GO_FASTER
►…
www.i.cz
Vaše jistota na trhu IT
Závěr►Oracle a Informix mají svá specifika
►Performance profiling v Jave je OK, ale …
►Spousta věcí funguje analogicky
www.i.cz
Vaše jistota na trhu IT
Děkuji za vaši pozornost
Jiří [email protected]+420 737 280 021
ICZ a.s.Divize Zdravotnictvíwww.i.cz
