Oracle praktilisi soovitusi spetsilt

Sissejuhatus

Järgnevad soovitused on antud ühe Oracle administreerimisspetsialisti poolt ühe konkreetse Eestis kasutatava, suhteliselt suurte andmemahtudega rakenduse jaoks. Soovituste põhieesmärgiks oli tõsta rakenduse efektiivsust Oracle baasi kasutamisel.

Bind muutujate kasutamine

Tegemist on Oracle andmebaasi jõudluse jaoks üliolulise asjaga. Kuna XStone asendab xml failis olevad bind muutujad ise ära, siis on ainsaks võimaluseks kasutada oracle init parameetrit CURSOR_SHARING=FORCE (10g-s tasuks mõelda pigem CURSOR_SHARING=SIMILAR peale). Tootebaasi peal tehtud statspack aruandest oli väga ilusti näha praegune olukord. Caches olevad SQL päringud raiskasid 60MB mälu (SQLArea Size) ja samas sellest raisatud mälust pole midagi kasu (Library cache miss oli 10%). Tootebaasis peale cursor_sharing=force sisselülitamist teha kindlasti uus statspacki aruanne ja vaadata, kuidas mõjus.

Indeksid

Kasutage indekseid julgelt! Indeksid on ikkagi mõeldud otsingute kiirendamiseks ja kui pole indekseid, siis ei jää andmebaasil ka muud üle kui terve tabel läbi kammida. Kui on võimalik indeksile öelda, et on unikaalne, siis seda teha! Kindlasti tasub ka seda meeles pidada, et DML lausete puhul peab andmebaas ka indekseid hooldama ja muudatuste tegemine on seetõttu aeglasem.

Samuti tasub ära kasutada võimalust, et andmebaas ei peakski üldse tabeli poole pöörduma ja vastus antakse indeksi pealt. Näiteks: SELECT MAX(vtimestamp) FROM ticket WHERE vtimestamp BETWEEN TRUNC(sysdate) AND sysdate Kui vtimestamp peal on indeks, siis ei pea üldse tabeli poole pöördumagi.

Oracle pakub võimalus ka terve tabel indeksisse paigutada (index organized table, IOT). IOT puhul moodustatakse indeksipuu tabeli primary key järgi ja samasse indeksiblokki lisatakse primaarvõtme juurde ka muud väljad. Väga kasulik on see juhul kui tabelist päritakse andmeid põhiliselt primaarvõtme järgi. Näiteks ISIK tabel ja teised dimensioonitabelid (maksekanal, ticket_type, ticket_type_in, ...). Juhul kui tabeli kõik väljad moodustavad primary key (näiteks TICKET_TYPE_IN) on tavalise tabeli loomine lihtsalt ruumi raiskamine, sest indeksis on täpselt samad andmed niikuinii juba olemas.

Indekseerige foreign key-d ära. Vastasel juhul lukustatakse tabel kui dimensioonitabelist kustutatakse või uuendatakse rida. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:292016138754

SQL ja jõudlus

Indeksi kasutamine ei ole võimalik, kui where osas on andmebaasi välja peal kasutatud funktsiooni. Näiteks:

 Vale  Õige
 trunc(vtimestamp) = trunc(sysdate)    vtimestamp between trunc(sysdate) and trunc(sysdate)+1-interval ?1? second
  substr(viitenr, 1, 11) = ?12345678901?   viitenr LIKE ?12345678901%?
  substr(viitenr, 1, 11) = ?12345678901?  viitenr LIKE RPAD(?1234?, 11, ?X?)||?%?
  To_char(timestamp,?yyyymm?) = to_char(sysdate, ?yyyymm?)     timestamp BETWEEN trunc(sysdate)-to_number(to_char(sysdate,'dd'))+1 AND  (trunc(sysdate)-to_number(to_char(sysdate,'dd'))+1)+interval '1' month)-interval '1' second

Antud juhul on võimalik abi saada funktsioonipõhistest indeksitest, aga neid 8i standard ei toeta (10g standard toetab).

Indeksit ei kasutata kõige efektiivsemalt kui piirang on kujul:

 Mkood LIKE ?%38102060215%?

Antud juhul kasutataks MKOOD peal olevat indeksit kui väikest tabelit, mis kammitakse tervenisti läbi.

  MKOOD LIKE ?3810206%? 

juba kasutab indeksit märksa paremini. Ehk siis otsingustringi algusesse panna % ainult siis kui seda tõesti on vaja!

Kuna tavalisse B*Tree indeksisse ei kirjutata sisse NULL väärtusi, siis piirangud väli IS NULL ja väli IS NOT NULL indeksit ei kasuta. Kui saab kasutada funktsioonipõhiseid indekseid, siis saab IS NULL asemel kasutada näiteks NVL(väli, -100) = -100 ja indeks teha NVL(väli, -100) peale, kus -100 asemel kasutada väärtust, mida välja sees ei leidu.

Selectige välja ainult VAJALIKUD väljad. Näiteks pl/sql funktsioon checkauth. Seal on kursor mc2 puhul huvitatud ainult sellest, kas tuli ridu või mitte. Antud juhul võiks mc2 ja mc3 päringu teha näiteks selliseks:

 select null from kseade where seadme_number=substr(code,12,20);

Või veel parem:

 SELECT COUNT(1) FROM kseade WHERE seadme_number=substr(code,12,20);

Kasutajaliidese ja äriloogika muutmise soovitused

Notice_log kirjade saatmise päring teha nii, et kord minutis võtaks näiteks viimase 5 minuti pealt päringu ja harvem võtaks rohkem.

Kasutajaliideses määrake ära vaikimisi filtrid. Eriti käib see funktsioonide /ticket/*/xswc_list_sort kohta. Suurte tabelite puhul (ticket) vältida kasutajaliidesest võimalust otsida kõiki kirjeid. Vajadusel rakendada ka kuupäevapiirangut (mõlemalt poolt). Kui kõikide kirjete otsimine on siiski vajalik, siis võib kasutada SELECT päringus hinti FIRST_ROWS, aga seda ainult juhul, kui filtrid puuduvad. Ticketrc.xml failis võiks /ticket/ticket/xswc_list_sort päring välja näha selline:

select /*+ :4 */
	      ticket.ticket_id,ticket.ik,ticket.product_id,ticket.product_code,
	      ticket.price,ticket.fee,ticket.maksekanal,
	      maksekanal.descr maksekanaldescr,
	      to_char(ticket.start_date,'dd.mm.yyyy hh24:mi:ss') start_date,
	      to_char(ticket.end_date,'dd.mm.yyyy hh24:mi:ss') end_date,
	      to_char(ticket.timestamp,'dd.mm.yyyy hh24:mi:ss') timestamp,	      
	      ticket.is_live,
	      ticket.ik_myyja,ticket.ik_ostja,ticket.teavitus,
	      ticket.a_number,ticket.price_add,ticket.company_id,ticket.rr_code,
	      ticket.disc,ticket.vat,ticket.end_of_day_id,ticket.sess_id,
	      ticket.dept,
	      isik1.nimi as company_name, isik2.nimi as debt_name,
	      contract_id, stan, doc_num,
	      to_char(ticket.vtimestamp,'dd.mm.yyyy hh24:mi:ss') vtimestamp,
	      teavvalik, payment_type
	     from maksekanal,ticket,isik isik1, isik isik2	    
	     where :3 and 
	           ticket.dept=isik2.kood (+) and	          
	           ticket.maksekanal=maksekanal.code and
	           ticket.company_id=isik1.kood (+)
	     order by :1 :2

Ja rakendusest antakse tühja filtri korral :4 parameetrina kaasa väärtus FIRST_ROWS.

Kasutajaliideses muuta ära vaikimisi filter SISALDAB, kuna kasutajad ei vaevu vaikeväärtust muutma. Vaikimisi võiks olla näiteks täpne võrdus või siis ALGAB.

Aruannetes võiks kuupäevavahemiku valik olla kohustuslik.

Uued primary keyd

  ticket_type_in (type_id, type_id_in)
  CHANNEL_TICKET_TYPE primary key (channel_id, type_id)

Andmebaasi hooldus

Kui mõnes tabelis on toimunud suuremad muudatused, siis peaks selle tabeli (ja tabeli indeksite) statistika uuesti koguma. Ticket tabeli statistikat tasuks regulaarselt koguda. Palju harrastatakse ka terve andmebaasi statistika regulaarset kogumist. Enne statistika uuendamist tasub ka backup teha vanast statistikast juhuks kui uue statistikaga on andmebaasi töö märgatavalt aeglasem. PL/SQL paketis DBMS_STATS on vastavad funktsioonid olemas.

Ticket ja status_log tabelitest tasub perioodiliselt ära kustutada tekkinud rämpskirjed. Näiteks igal öösel. Skriptis 01_delete_junk.sql on kommentaaridena kirjas vajalikud muudatused. Kustutamine tekitab auke indeksitesse ja date väljale tehtud indeksis seda auku enam ei täideta. Sellepärast tasuks regulaarselt (kord nädalas? Kord kuus?) teha ticket ja status_log olevatele date väljade indeksitele coalesce:

 Alter index TICKET_VTIMESTAMP_IDX coalesce;

Lisamaterjali

http://www.kriso.ee/cgi-bin/shop/510000000720528.html?mv_arg=

PL protseduuride kommentaarid

get_last_anumber asendada sql ja protseduur ka vastavaks muuta:

 select MAX(a_number) from ticket where  ik=i_k and company_id='MPC' order by ticket_id desc;

Funktsioonis check_code peaks RTRIM(mkood)=RTRIM(mcode) asendama mkood = RTRIM(mcode). Ja inserdi ajal hoolitsema, et soodus_maakonnad tabelisse saaks mkood välja lisatud korrektne väärtus.

Funktsioonides revoketicket* kirjutage kostruktsiooni sysdate-0.02083333 asemel sysdate-interval ?30? minute. On paremini loetav.

Muudetud PL:

CREATE OR REPLACE function get_last_anumber(i_k varchar2) return varchar2 is
begin
declare
cursor mc is
    select MAX(a_number) max_a_number
    from ticket
    where
    ik=i_k
    and
    company_id='MPC'
    order by ticket_id desc;
    mr mc%ROWTYPE;

    ret varchar2(20);

begin
    ret:=NULL;
    open mc;
    fetch mc into mr;
    if mc%FOUND then
        ret:=mr.max_a_number;
    end if;
    close mc;
    return ret;

end;
end;
/