Suorituskyvyn monitorointi ja testiympäristön luonti © Jouni Huotari K2007 (IIO30200 Tietokannan hallinta) ===================================================== Käynnistä ensin Virtuaalikone (esim. jupe-huojo) ja tarvittaessa Oracle-palvelut (OracleServiceXE ja OracleXETNSListener) A. Suorituskyvyn monitorointi ----------------------------- 1. Käynnistä Oracle Enterprise Manager (Go To Database Home Page) 2. Kytkeydy XE-instanssiin SYSTEM-tunnuksella (pw: root66). 3. Katso Usage Monitor -kohdasta (keskusmuisti jne.). 4. Katso myös Administration > Monitor > System Statistics ja Top SQL Lisätehtävä: vertaa Oraclen monitorointityökaluja MS SQL Serverin ja MySQL:n työkaluihin. B. Testiympäristön luonti ------------------------- 1. Luo ensin tilaus-hakemisto oradatan alle, esim. md C:\oraclexe\oradata\tilaus Vaihtoehtoisesti voit luoda tilaus-tietokannan Configuration Assistant -ohjelmalla (ks. ohje: Oracle-instassin luonti). 2. Käynnistä SQL*Plus ja kirjaudu system-tunnuksella (salasana on root66). Jos kohdassa 1 loit tilaus-hakemiston, luo sinne uusi taulualue: CREATE TABLESPACE TILAUS LOGGING DATAFILE 'C:\oraclexe\oradata\tilaus\tilaus.dbf' SIZE 800M 3. Käy katsomassa Enterprise Managerista, kuinka mm. USER I/O on muuttunut. 4. Luo uusi käytäjä: CREATE USER Demox IDENTIFIED BY demox DEFAULT TABLESPACE TILAUS; 5. Anna perusoikeudet uudelle käyttäjälle: GRANT CONNECT, RESOURCE TO Demox; 6. Uusi käyttäjä kytkeytyy kantaan: CONN Demox/demox; 7. Laita ajastus päälle> SET TIMING ON 8. Luo PLAN_TABLE: @?/rdbms/admin/utlxplan.sql (= C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlxplan.sql) 9. Laita jäljitys päälle + selitykset näkyviin (Oracle 8 ja Oracle 9): SET AUTOTRACE ON EXPLAIN STATISTICS => Oracle 10g2:ssa tulee virheilmoitus "SP2-0618: Istunnon tunnistetta ei löydy. Tarkista, että PLUSTRACE-rooli on käytössä SP2-0611: Virhe otettaessa käyttöön raporttia STATISTICS" 10. Luo Demox Oy:n taulut; aja skripti createDemoxOySQL_Oracle.sql Kopioi http://student.labra.jypoly.fi/~huojo/opetus/IIO30200/harj/createDemoxOySQL_Oracle.sql virtuaalikoneeseen esim. C-aseman TEMP-kansioon ja anna sitten käsky start C:\TEMP\createDemoxOySQL_Oracle.sql Seuraavaksi otetaan ODBC-yhteys Oracle-kantaan ja luodaan testidata 11. Käynnistä DemoxGen.xls. Salli makrot. 12. Luo ODBC-määritys Oracle-kantaan. Anna ODBC-yhteyden nimeksi XE (eli instanssin nimi eli Service ID). 13. Muuta OLE DB Connection String: Provider=OraOLEDB.Oracle.1;Password=test1;Persist Security Info=True;User ID=testi;Data Source=XE 14. Luo aluksi 100 asiakasta ja tuotetta. Palaa sitten SQL*Plussaaan ja katso, mitä Oracle kertoo kun teet pari hakua tietokantaan. 15. Listaa kaikki asiakkaat. SELECT * FROM asiakas; (Jos et antanut käskyä SET LIN 1000, niin saatat saada listauksena melko epäselvän rivijoukon :). Elapsed: 00:00:00.32 Execution Plan ---------------------------------------------------------- Plan hash value: 4165228809 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 52200 | 5 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| ASIAKAS | 100 | 52200 | 5 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ----------------------------------------------------- 584 recursive calls 0 db block gets 135 consistent gets 15 physical reads 0 redo size 41151 bytes sent via SQL*Net to client 446 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 100 rows processed Huom. Oraclessa optimoijan tekemän suunnitelman saa selville lauseella EXPLAIN PLAN: EXPLAIN PLAN SET STATEMENT_ID = 'Asiakashaku' INTO plan_table FOR SELECT * FROM asiakas; 15. Tee vielä monimutkaisempi haku; hae esim. asiakkaiden nimet, tilauspvm:t ja tilattujen tuotteiden määrät ja hinnat. Vinkki: kyselyn voit tehdä Accessilla ja liittää sen Oracleen (siivottuasi sitä hieman esim. tekstieditorilla). SELECT ASIAKAS.ASNIMI, MTILAUS.TILAUSPVM, TUOTE.TUOTENIMI, MTILAUSRIVI.KPL, TUOTE.HINTA, KPL*HINTA AS YHT FROM TUOTE INNER JOIN ((ASIAKAS INNER JOIN MTILAUS ON ASIAKAS.ASTUNNUS = MTILAUS.ASTUNNUS) INNER JOIN MTILAUSRIVI ON MTILAUS.TILAUSNRO = MTILAUSRIVI.TILAUSNRO) ON TUOTE.TUOTENRO = MTILAUSRIVI.TUOTENRO; 10000 rows selected. Elapsed: 00:00:11.51 Execution Plan ---------------------------------------------------------- Plan hash value: 348991559 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10000 | 1728K| 32 (7)| 00:00:01 | |* 1 | HASH JOIN | | 10000 | 1728K| 32 (7)| 00:00:01 | | 2 | TABLE ACCESS FULL | TUOTE | 304 | 17632 | 3 (0)| 00:00:01 | |* 3 | HASH JOIN | | 10000 | 1162K| 28 (4)| 00:00:01 | |* 4 | HASH JOIN | | 1000 | 80000 | 19 (6)| 00:00:01 | | 5 | TABLE ACCESS FULL| ASIAKAS | 100 | 4500 | 5 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL| MTILAUS | 1000 | 35000 | 13 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | MTILAUSRIVI | 10000 | 380K| 9 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("MTILAUSRIVI"."TUOTENRO"="TUOTE"."TUOTENRO") 3 - access("MTILAUSRIVI"."TILAUSNRO"="MTILAUS"."TILAUSNRO") 4 - access("MTILAUS"."ASTUNNUS"="ASIAKAS"."ASTUNNUS") Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 133 recursive calls 0 db block gets 905 consistent gets 0 physical reads 0 redo size 580154 bytes sent via SQL*Net to client 7706 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 10000 rows processed 16. Luo 500000 asiakasta ja pidä kahvitauko :) 17. Tee haut uudestaan ja tutki selitykset. 18. Tee haku, joka listaa helsinkiläiset miespuoliset yhteyshenkilöt. SELECT astunnus, yhteyshlo FROM asiakas WHERE sukup = '1' AND postitmp = 'Helsinki' ORDER BY yhteyshlo; 19. Luo 3 tähden indeksi em. haulle. Vinkki: indeksin nimi on i_postitmp_sukup_yhteyshlo_astunnus 20. Tee edellinen haku uudestaan ja tutki selitykset. Mitä opit? Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 INDEX (RANGE SCAN) OF 'ASIAKAS_PTMI_SUKUP_YHT_ASTUN' (NO N-UNIQUE) Huom. - MySQL 5.0 InnoDB-taulut, 10000 asiakasta, haku ennen 3*indeksiä: 1) hakuaika: 0,0156 s. (0,0387 s.); palautti 112 riviä 2) 100000 riviä generoitiin n. 2 minuutissa => hakuaika: 0,0367 s. (1,1231 s.); palautti 1285 riviä 3) 500000 riviä generoitiin n. 9 minuutissa => hakuaika: 0,1321 s. (4,1571 s.); palautti 6405 riviä Taulun koko: n. 100 Mt (keskim. n. 200 t / rivi)