Oracle praktilisi soovitusi spetsilt - Redigeerimiste ajalugu

Tammet – 25. aprill 2006, kell 12:23

2006-04-25T12:23:06Z

←Vanem redaktsioon		Redaktsioon: 25. aprill 2006, kell 14:23
1. rida:		1. rida:
			== 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 ==		== Bind muutujate kasutamine ==

Tammet: /* PL protseduuride kommentaarid */

2006-04-25T10:46:34Z

PL protseduuride kommentaarid

←Vanem redaktsioon		Redaktsioon: 25. aprill 2006, kell 12:46
110. rida:		110. rida:

	Muudetud PL:		Muudetud PL:
			<pre>
	CREATE OR REPLACE function get_last_anumber(i_k varchar2) return varchar2 is		CREATE OR REPLACE function get_last_anumber(i_k varchar2) return varchar2 is
	begin		begin
138. rida:		139. rida:
	end;		end;
	/		/
			</pre>

Tammet: /* PL protseduuride kommentaarid */

2006-04-25T10:46:16Z

PL protseduuride kommentaarid

←Vanem redaktsioon		Redaktsioon: 25. aprill 2006, kell 12:46
103. rida:		103. rida:

	get_last_anumber asendada sql ja protseduur ka vastavaks muuta:		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;		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.		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.

Tammet: /* Kasutajaliidese ja äriloogika muutmise soovitused */

2006-04-25T10:45:45Z

Kasutajaliidese ja äriloogika muutmise soovitused

←Vanem redaktsioon		Redaktsioon: 25. aprill 2006, kell 12:45
54. rida:		54. rida:
	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).		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:		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:
			<pre>
	select /+ :4 /		select /+ :4 /
	ticket.ticket_id,ticket.ik,ticket.product_id,ticket.product_code,		ticket.ticket_id,ticket.ik,ticket.product_id,ticket.product_code,
76. rida:		77. rida:
	ticket.company_id=isik1.kood (+)		ticket.company_id=isik1.kood (+)
	order by :1 :2		order by :1 :2
			</pre>
	Ja rakendusest antakse tühja filtri korral :4 parameetrina kaasa väärtus FIRST_ROWS.		Ja rakendusest antakse tühja filtri korral :4 parameetrina kaasa väärtus FIRST_ROWS.

83. rida:		85. rida:

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

			== Andmebaasi hooldus ==

	~~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.		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.		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:		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;		Alter index TICKET_VTIMESTAMP_IDX coalesce;

	== Lisamaterjali ==		== Lisamaterjali ==

Tammet: /* SQL ja jõudlus */

2006-04-25T10:45:08Z

SQL ja jõudlus

←Vanem redaktsioon		Redaktsioon: 25. aprill 2006, kell 12:45
26. rida:		26. rida:

	Indeksi kasutamine ei ole võimalik, kui where osas on andmebaasi välja peal kasutatud funktsiooni. Näiteks:		Indeksi kasutamine ei ole võimalik, kui where osas on andmebaasi välja peal kasutatud funktsiooni. Näiteks:
	Vale
	Õige		Vale Õige
	trunc(vtimestamp) = trunc(sysdate)		trunc(vtimestamp) = trunc(sysdate) vtimestamp between trunc(sysdate) and trunc(sysdate)+1-interval ?1? second
	vtimestamp between trunc(sysdate) and trunc(sysdate)+1-interval ?1? second		substr(viitenr, 1, 11) = ?12345678901? viitenr LIKE ?12345678901%?
	substr(viitenr, 1, 11) = ?12345678901?		substr(viitenr, 1, 11) = ?12345678901? viitenr LIKE RPAD(?1234?, 11, ?X?)\|\|?%?
	viitenr LIKE ?12345678901%?		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
	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).		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:		Indeksit ei kasutata kõige efektiivsemalt kui piirang on kujul:
	Mkood LIKE ?%38102060215%?		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!		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.		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:		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);		select null from kseade where seadme_number=substr(code,12,20);
	Või veel parem:		Või veel parem:
	SELECT COUNT(1) FROM kseade WHERE seadme_number=substr(code,12,20);		SELECT COUNT(1) FROM kseade WHERE seadme_number=substr(code,12,20);

	== Kasutajaliidese ja äriloogika muutmise soovitused ==		== Kasutajaliidese ja äriloogika muutmise soovitused ==

Tammet – 25. aprill 2006, kell 10:43

2006-04-25T10:43:19Z

Uus lehekülg

== 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;
/