O Oracle ludzkim głosem

regexp_count w Oracle 10g

2012-02-09T05:51:00.000-08:00

Ponieważ w 10g Express Edition brakowało mi funkcji regexp_count postanowiłem napisać jej własną implementację która na tej wersji działa. A może komuś się jeszcze przyda:

create or replace package utils is
function regexp_count(x varchar2,wzorzec varchar2) return number;
end;

create or replace package body utils is
function regexp_count(x varchar2,wzorzec varchar2) return number
is
wystapienie number:=1;
pozycja number:=1;
begin
loop
pozycja:=regexp_instr(x,wzorzec,1,wystapienie);
exit when pozycja=0;
dbms_output.put_line('wystapienie: '||wystapienie||' na pozycji: '||pozycja);
wystapienie:=wystapienie+1;
end loop;
return wystapienie-1;
end;
end;

Tak wiem, że można to napisać lepiej. Mocno się jednak śpieszę a to przynajmniej działa :D

Instalacja Apex z użyciem PL/SQL Gateway

2012-01-10T07:28:00.000-08:00

Aby instalacja się powiodła powinieneś mieć:

wersję bazy> 10.2.0.3 (może być express edition)
shared_pool>=100MB (jeśli masz ustawione automatyczne strojenie wielkości - tak jest domyślnie - to Cię to nie boli).
Tablespace na instalację apex z minimum 185MB miejsca wolnego (możesz go stworzyć z poziomu sysa: create tablespace apex datafile 'sciezka do pliku danych' size 300M)
Wersja PL/SQL Web Toolkit >=10.1.2.0.6 (sprawdzić jaką masz możesz tak: select owa_util.get_version from dual. Jeśli masz bazę 11G to się tym nie martwisz).

Pobierz z poniższego linka paczkę instalacyjną apexa:

Pobierz apex

Uruchamiasz skrypt instalacyjny:

sqlplus sys/haslo as sysdba @/home/oracle/apex/apexins.sql

Oczywiście u Ciebie ścieżka pewnie będzie inna, ważne byś odpalił skrypt apexins.sql jako sys.
Podczas wykonywania tego skryptu zostaniesz poproszony o 4 parametry.

- pierwszy to nazwa tablespace do którego zostanie zainstalowany engine apexa (pakiety w plsql etc)
- drugi to nazwa tablespace do ktorego będą lądowały pliki uploadowane przez użytkowników.
- trzeci to nazwa tablespace tymczasowego (np. temp)
- czwarty to ścieżka wirtualnego katalogu w którym będą obrazki (daj po prostu /i/ )

Możesz też po prostu odpalić to tak:

sqlplus sys/haslo as sysdba @/home/oracle/apex/apexins.sql apex apex temp /i/

Kolejny krok to odpalenie skryptów konfigurujących apexa:

sqlplus sys/haslo as sysdba @/home/oracle/apex/apex_epg_config.sql /home/oracle

W miejsce mojej ścieżki (/home/oracle) podajesz ścieżkę do katalogu w którym znajduje się rozpakowany katalog apexa.

sqlplus sys/haslo as sysdba @/home/oracle/apex/apxldimg.sql /home/oracle

Ścieżka analogicznie jak wcześniej.

Teraz musisz odlokować użytkowników:

alter user anonymous account unlock;
alter user xdb account unlock;
alter user apex_40100 account unlock;
alter user flows_files account unlock;
alter user apex_public_user account unlock;

Następny krok to konfiguracja hasła i portu nasłuchu dla apexa:

sqlplus sys/haslo as sysdba @/home/oracle/apex/apxconf.sql

Zostaniesz poproszony o te dane podczas wykonywania powyższego skryptu.

Jeśli za jakiś czas zechcesz sprawdzić na jakim porcie chodzi apex możesz wywołać:

select dbms_xdb.gethttpport from dual;

lub zmienić go:

execute dbms_xdb.sethttpport(port);

Do apexa logujesz się tak:

http://localhost:8080/apex

Korzystając z workspace internal
nazwy użytkownika admin
i hasła jakie podałeś w trakcie instalacji.

Nieśmiało polecam :)

2012-01-02T01:56:00.000-08:00

Cześć,
dzisiaj zupełnie z innej beczki. Moja przyjaciółka produkuje piękną biżuterię z sutaszu, do tej pory sprzedawała swoje wyroby tylko po znajomych i właśnie wchodzi na szerszy rynek. Biżuteria jest robiona ręcznie, na zamówienie. Do tej pory zamówiliśmy 4 komplety i muszę przyznać że zostały wykonane bardzo dokładnie i starannie. Do tej pory zachodzę w głowę jak coś takiego można zrobić bez mikroskopu. Tak więc moi drodzy - zapraszam na jej stronkę:

http://bizuteriaerty.blogspot.com/

i proszę pokażcie ją swoim dziewczynom/żonom/teściowym etc. :)

Klasy JAVA w Oracle

2011-12-20T11:45:00.000-08:00

Pewnych rzeczy nie da się oprogramować w PL/SQL - dajmy na to drukowanie, lub sterowanie zewnętrznym urządzeniem. Wtedy z pomocą przychodzi nam język JAVA. Możemy oprogramować niezbedną funkcjonalność w Javie, załadować ją do bazy, nałożyć taką nakładkę na klasę javową tak by była widoczna jako funkcja PL/SQL i po robocie :) Metody takiej klasy wywołuje się tak jak zwyczajne funkcje napisane w PL/SQL.

Pierwszy krok to klasa w JAVIE:

public class Przyklad {
public static int dawajliczbe() {
return 1;
}
}

Nie musimy jej kompilować. Następnie poleceniem

loadjava -user nazwauzytkownika/jegohaslo sciezkadopliku

ładujemy klasę do bazy. Klasa zostanie załadowana do schematu użytkownika którego nazwę i hasło podaliśmy przy ładowaniu.

Następnie z poziomu tego samego użytkownika robimy sobie nakładkę na metodę w klasie javowej:

create or replace function javova return number
as language java name 'Przyklad.dawajliczbe() return int';

i wywołujemy jak każdą inną funkcję w Oracle :

Ładowanie pliku do kolumny BLOB, CLOB

2011-12-20T09:54:00.000-08:00

Zanim podejdziesz do tego artykułu, zapoznaj się z tym : http://andrzejklusiewicz.blogspot.com/2011/12/bfile-referencje-do-plikow-zewnetrznych.html
Wiedza z niego będzie Ci tutaj niezbędna.

Najpierw stwórz (o ile jeszcze nie masz) tabelkę z kolumną typu BLOB :

create table obrazki(
nr number,
obrazek blob
);

Poniżej kod ładujący plik do kolumny typu danych blob w tabeli. Kilka linii niżej wyjaśnienie.

declare
plik bfile;
bl blob:=empty_blob();
wielkosc number;
nr number;
begin
plik:=bfilename('LOBY','obrazek3.jpeg');
if dbms_lob.fileexists(plik)=1 then
wielkosc:=dbms_lob.getlength(plik);
insert into obrazki values (moja.nextval,bl) returning nr, obrazek into nr, bl;
dbms_lob.open(plik,dbms_lob.lob_readonly);
dbms_lob.open(bl,dbms_lob.lob_readwrite);
dbms_lob.loadfromfile(bl,plik, wielkosc);
dbms_lob.close(bl);
dbms_lob.close(plik);

commit;
dbms_output.put_line('plik znalazl się w tabeli pod numerem '||nr||', zajmuje '||wielkosc||' bajtow');
else dbms_output.put_line('nie znalazlem takiego pliku...');
end if;
end;

Pliki ładujemy do formatu BLOB za pośrednictwem formatu BFILE. Taka już uroda tego RDBMS...
Najpierw sekcja deklaracji.
plik bfile; - to będzie referencja do naszego pliku , zmienna plik będzie pośrednikiem w ładowaniu. Musi być typu bfile.
bl blob:=empty_blob(); - typ BLOB jest typem obiektowym, musimy więc użyć konstruktora do zainicjalizowania obiektu.

wielkosc number; - Do zmiennej wielkość trafi wielkość pliku w bajtach. Musisz mieć taką zmienną. Będzie niezbedna przy ładowaniu danych do tabeli (przy procedure loadfromfile).

nr number; - nie jest konieczne, ja sobie taką zmienną zrobiłem by później do niej załadować wartość pod którą w kluczu głównym tabeli znajdzie się mój wiersz z obiektem blob.

Część wykonawcza:

plik:=bfilename('LOBY','obrazek3.jpeg'); - zmienną plik znanego już z artykułu poprzedniego typu danych bfile podpinamy pod istniejący plik. LOBY to alias katalogu (również omawiane w poprzednim rozdziale).

if dbms_lob.fileexists(plik)=1 - znane z poprzedniego rozdziału. Sprawdzam czy plik istnieje. Zwraca 1 albo 0.

wielkosc:=dbms_lob.getlength(plik); - znane z poprzedniego rozdziału. Pobieram wielkość pliku pod którego podpięta jest moja zmienna typu BFILE.

insert into obrazki values (moja.nextval,bl) returning nr, obrazek into nr, bl; - ładuję do tabeli na razie pusty obiekt BLOB, jednak jest on zwracany poprzez referencję. Taki wymóg konstrukcyjny...

dbms_lob.open(plik,dbms_lob.lob_readonly);
dbms_lob.open(bl,dbms_lob.lob_readwrite);

Oba obiekty muszę otworzyć. Obiekt typu BFILE w trybie tylko do odczytu, obiekt BLOB w trybie do zapisu i odczytu.

dbms_lob.loadfromfile(bl,plik, wielkosc); - Faktyczne ładowanie danych. Pierwszy parametr to zmienna typu BLOB (czyli obiekt docelowy) , druga to obiekt typu BFILE (czyli obiekt źródłowy), trzecia to wielkość pliku który trzeba załadować. Czemu muszę mieć podpięty niejako "dwukrotnie" plik? Pamiętaj że BFILE trzymane są poza bazą a BLOB w niej.

dbms_lob.close(bl);
dbms_lob.close(plik);

Jak otworzyłem to muszę zamknąć. Jeśli tego nie zrobię to w końcu dostanę info że przekroczyłem dopuszczalną ilość otwartych referencji do plików.

commit; - Pamiętaj by zatwierdzić zapis. Dane generalnie po odpaleniu procedury loadfromfile znajdują się już w tabeli, niemniej jednak podlegają tranzakcyjności jak wszystkie inne dane.

Możesz już korzystać ze swoich blobów :) Puść np. takie zapytanie:

select dbms_lob.getlength(obrazek) from obrazki;

Generalnie CLOBy ładuje się identycznie. Wszędzie gdzie tutaj wyżej pojawiło się BLOB, piszesz w to miejsce CLOB :)

BFILE. Referencje do plików zewnętrznych w Oracle

2011-12-13T10:29:00.000-08:00

Czołem,
dziś na tapetę bierzemy typ danych BFILE. Jest to typ służący do przetrzymywania referencji do plików przetrzymywanych poza bazą. Zapytacie , na co komu taki rodzaj danych skoro można przechowywać ścieżkę do pliku w zwykłym varcharze? BFile pozwoli nam np. sprawdzić czy plik istnieje, jaką ma wielkość , porównać dwa pliki.

Początek

Ponieważ BFile trzyma referencję do plików zewnętrznych, stwórz sobie najpierw katalog na dysku, a następnie wrzuć do niego parę plików (wszystko jedno jakich).

Mój katalog znalazł się pod ścieżką "C:\loby". Następnie {!} z poziomu sysa {!} tworzymy sobie obiekt directory który pozwoli nam na dostęp do tego katalogu z poziomu Oracle. Należy też nadać uprawnienia do korzystania z tego katalogu użytkownikowi z poziomu którego mamy zamiar z tych plików korzystać:

create directory loby as 'c:\loby';
grant read, write on directory loby to testowy;

Ja nadałem uprawnienia użytkownikowi "testowy", a mój katalog w Oracle będzie widoczny pod nazwą "loby".

Sprawdzanie czy plik istnieje

Teraz sprawdzę czy wskazany przeze mnie plik istnieje:

Declare
plik bfile;
Begin
Plik:=Bfilename('LOBY','oko.jpeg');
If Dbms_Lob.Fileexists(Plik)=1 Then
     Dbms_Output.Put_Line('jest taki plik!');
Else Dbms_Output.Put_Line('pliku niet!');
end if;
end;

Wykorzystałem tutaj procedurę fileexists z pakietu dbms_lob. Zwraca ona wartość 0 lub 1 w zależności od
tego czy plik istnieje czy nie. Funkcja (?) bfilename podpina referencję do pliku "oko.jpeg" znajdującego się w katalogu o aliasie "loby" (c:\loby) do na razie jeszcze pustej zmiennej plik. Pamiętać należy by alias katalogu podawać zawsze dużymi literami. Jak widzimy plik o który zapytałem istnieje.

Sprawdzanie wielkości

Poszerzmy teraz nasz test. Sprawdzimy , jeśli istnieje to jaką ma wielkość:

Declare
plik bfile;
Begin
Plik:=Bfilename('LOBY','oko.jpeg');
If Dbms_Lob.Fileexists(Plik)=1 Then
     Dbms_Output.Put_Line('jest taki plik!');
     Dbms_Lob.Fileopen(Plik);
     Dbms_Output.Put_Line('ten plik ma '||Dbms_Lob.Getlength(plik)||' bajtów');
     dbms_lob.fileclose(plik);
Else Dbms_Output.Put_Line('pliku niet!');
end if;
end;

Taki plik możemy otworzyć przy użyciu procedury fileopen z pakietu dbms_lob. Skoro otworzyliśmy to musimy go po wszystkim zamknąć (dbms_lob.fileclose).
Do sprawdzenia wielkości służy nam procedura getlength z pakietu dbms_lob.
Gdyby Oracle rzucał nam wyjątkiem dotyczącym liczby otwartych plików, należy zmodyfikować parametr session_max_open_files określający ile możemy mieć jednocześnie podpiętych i otwartych plików w sesji.

Przechowywanie

Takie dane (typu bfile) przechowywać mogę również w tabelkach. Na początek tworzymy sobie tabelkę z kolumną bfile. Przy okazji tworzę też sekwencję która będzie mi za moment potrzebna. Tych dla których pojęcie sekwencji jest nowością odsyłam do rozdziału "sekwencje" w znajdującym się na tej stronie kursie SQL.

Create Table Obrazki (
Nr Number Primary Key,
obrazek bfile
);

Create Sequence Moja
Start With 1 Increment By 1;

Dalej dodaję wiersze do tej tabeli:

insert into obrazki values (moja.nextval, bfilename('LOBY','aniol.jpg') );
insert into obrazki values (moja.nextval, bfilename('LOBY','smerfy.jpg') );
commit;

Na podobnej zasadzie odbywa się update - muszę wykorzystać poznaną już w tym rozdziale funkcję (?) bfilename.
Jeśli na takiej tabeli wykonam select , niewiele uzyskam informacji:

Mogę jednak posługując się funkcjami z pakietu dbms_lob poszerzyć swój zasób wiedzy np.
dowiadując się jaką ma wielkość:

BFILE w PLSQL i sprawdzanie ścieżki

Przy użyciu PL/SQL również mogę obsługiwać taki typ danych. Poszerza to również możliwości o weryfikację nazwy pliku i aliasu katalogu w którym znajduje się plik:

declare
plik bfile;
nazwapliku varchar2(50);
katalog varchar2(50);
begin
select obrazek into plik from Obrazki where nr=2;
dbms_output.put_line('wielkość pliku: '||Dbms_Lob.Getlength(plik));
dbms_lob.filegetname(plik,katalog,nazwapliku);
dbms_output.put_line('plik nazywa się: '||nazwapliku);
dbms_output.put_line('plik znajduje się w katalogu: '||katalog);
end;

Poszerza, ponieważ wykorzystuję procedurę filegetaname (która jest przecież procedurą więc nie mogę jej wykorzystywać w SQLu) która wykorzystuje parametry typu OUT. Zwraca do nich (nazwapliku,katalog) żądane informacje. Zwracana jest nazwa aliasu, ale jeśli chcesz poznać dokładną ścieżkę to możesz przeszukać słownik all_directories w poszukiwaniu dokładnych informacji na temat danego katalogu.

cdn...

Varray. Tabele zagnieżdżone o stałej długości.

2011-12-12T12:07:00.000-08:00

Przed lekturą tej notki przeczytaj:
http://andrzejklusiewicz.blogspot.com/2011/12/nested-tables-tabele-zagniezdzone.html

To co jest tutaj łatwo Ci będzie zinterpretować przez analogię.
Tworzymy sobie typ obiektowy taki jak przy nested tables:

Create Type Linia_W_Fakturze2 Is Object(
Nr Number,
Nazwa Varchar2(400),
Pkwiu Varchar2(10),
Cena_Netto Number
);

Tworzymy sobie typ tablicowy, tym razem nie "table" a varray o wielkości początkowej 50 elementów:

create type produkty_w_fakturze2 is varray(50) of linia_w_fakturze2;

Następnie tworzymy tabelę która będzie zawierała takie zagnieżdżone listy:

Create Table Faktury2 (
Nr_Faktury Varchar2(20) Primary Key,
Data_Wystawienia Date,
Termin_Platnosci Date,
Data_Sprzedazy Date,
Produkty Produkty_W_Fakturze2
);

Dodajemy dwa wiersze:

Insert Into Faktury2 Values
('01/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze2( Linia_W_Fakturze2(1,'migomat','365635',1000) )
);
Insert Into Faktury2 Values
('02/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze2( Linia_W_Fakturze2(1,'migomat','365635',1000) )
);

Sposób odpytywania taki jak wcześniej:

select * from faktury2 f, table(Produkty) p where p.nazwa like '%migomat%';

Ten typ danych w PL/SQL również możemy rozszerzać przy użyciu EXTEND.

Nested Tables. Tabele zagnieżdżone

2011-12-12T11:18:00.000-08:00

Ciekawym , aczkolwiek z mojej perspektywy mało wygodną opcją w Oracle są tabele zagnieżdżone. Przedstawię tutaj ideę na przykładzie faktur i produktów w nich zawartych , a sami najlepiej ocenicie czy taki byt Wam się przyda czy nie.
Generalnie działa to tak , że każdy wiersz w tabeli ma zagnieżdżoną inną tabelę. Tutaj posłużyłem się relacją faktura <==> produkty w fakturze. Zaraz się podniesie larum , że jakto tak zwielokratnianie danych. Tymczasem takie rozwiązanie zamiast intersekcji jest jak najbardziej sensowne. Pomyślcie, wystawiacie tysiąc faktur z tym samym produktem. Rok później zmienia się cena produktu. Czy wobec tego wszystkie stare faktury powinny mieć nową zaktualizowaną cenę produktu? Raczej nie... A tak stałoby się gdybyśmy posłużyli się np. intersekcją. Zamiast tego stosuje się (w najprostszym rozwiązaniu) kopię danych produktu, a następnie referencję do tej kopii zapisuje się do intersekcji łączącej faktury z produktami. W tym przypadku założyłem że dla każdego wiersza w tabelce faktury będzie przypisana zagnieżdżona tabelka zawierająca listę produktów.

Podążaj proszę za przykładami, a na koniec artykułu wszystko stanie się jasne. Aby korzystać z tabel zagnieżdżonych w innych tabelach musimy najpierw stworzyć typ obiektowy:

Create Type Linia_W_Fakturze Is Object(
Nr Number,
Nazwa Varchar2(400),
Pkwiu Varchar2(10),
Cena_Netto Number
);

Ten typ jest mi potrzebny jako format przechowywania linii w fakturze - tj. pojedynczego produktu w fakturze. Następnie tworzę inny typ, który będzie formatem do przechowywania listy obiektów których typ stworzyłem powyżej. Z polskiego na nasze - to jest typ który określa format przechowywania listy produktów w fakturze (czyli tej zagnieżdżonej tabelki) :

Create Type Produkty_W_Fakturze Is Table Of Linia_W_Fakturze;

Kiedy już mam niezbędne typy danych, przystępuję do tworzenia samej tabeli:

Create Table Faktury (
Nr_Faktury Varchar2(20) Primary Key,
Data_Wystawienia Date,
Termin_Platnosci Date,
Data_Sprzedazy Date,
Produkty Produkty_W_Fakturze
) nested table produkty store as prod_w_fak;

Produkty to nazwa kolumny pod którą będzie kryć się nasza zagnieżdżona tabelka. Produkty_w_fakturze to nazwa typu danych tej kolumny. To jak np. cena number czyli nazwa_kolumny typ_danych.
Ta ostatnia linia określa sposób przechowywania danych związanych z tą zagnieżdżoną tabelą. Interesuje nas fragment "store as prod_w_fak". Na potrzeby przechowywania wierszy związanych z tabelkami zagnieżdżonymi tworzona jest dodatkowa tabela o nazwie "prod_w_fak".

Wrzucamy teraz dane do takiej tabelki:

Insert Into Faktury Values
('01/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze(    Linia_W_Fakturze(1,'lodówka','365635',1000)     )
);

Wszystko co nie dotyczy tabeli zagnieżdżonej jest całkiem zwyczajne. Nas jednak interesuje ta linijka:

Produkty_W_Fakturze(    Linia_W_Fakturze(1,'lodówka','365635',1000)     )

Zaznajomionym z programowaniem obiektowym zapewne w pierwszym skojarzeniu przyjdzie do głowy pojęcie konstruktora. I nie pomylą się. To jest właśnie wykorzystanie konstruktorów w typach obiektowych w Oracle. Przyjrzyjmy się bliżej:

Produkty_W_Fakturze(    blablablabla     )

To jest konstruktor listy. Zawsze możemy zamiast tego podać null. Wtedy dana faktura nie będzie miała związanych z nią produktów. Aby wstawić jakąkolwiek listę , musimy ją wpierw stworzyć. To jest właśnie ten moment. Teraz taką listę wypełniamy :

blablabla(    Linia_W_Fakturze(blablabla)     )

Ponieważ są to typy obiektowe, każdy element listy również trzeba stworzyć przy użyciu konstruktora. Podczas konstruowania takich obiektów jednocześnie zapełniamy je wartościami:

Linia_W_Fakturze(1,'lodówka','365635',1000)

. Spójrz jeszcze raz na całość:

Insert Into Faktury Values
('01/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze(    Linia_W_Fakturze(1,'lodówka','365635',1000)     )
);

Stało się to już wyraźniejsze? Możemy też dodać więcej niż jedną linię do takiej zagnieżdżonej tabeli:

Insert Into Faktury Values
('02/12/2011',Sysdate,Sysdate,Sysdate,
Produkty_W_Fakturze(
        Linia_W_Fakturze(3,'pralka','3545635',1500),
        Linia_W_Fakturze(3,'wentyl od jelcza','4342',12)
        )
);

Jak korzystać z takich tabel? Porównaj wyniki tych 3 zapytań:

Select * From Faktury;
Select * From Faktury , Table(Produkty);
select * from faktury f, table(Produkty) p where p.nazwa like '%lodówka%';

Taki byt jak tabele zagnieżdżone możemy również wykorzystywać w PL/SQL:

Objaśnienie (wg. nr. linii):

2: stworzenie pustej tablicy.
4-7: Zainicjalizowanie tablicy przy użyciu konstruktora, jednocześnie ładując do niej pierwsze dwa elementy (na takich zasadach jak przy insercie).
8: Wypisuję na outpucie nazwę drugiego produktu z listy. W tym przypadku będzie to "dętka od kamaza".
9: Nie mógłbym się odwołać do 3 elementu tej tablicy, skoro zawiera tylko dwa elementy (czyli inaczej niż w tablicach indeksowanych po binary_integer czy pls_integer). Z tego powodu rozszerzam ją o 4 pozycje.
10: Trzecią na razie pustą pozycję wypełniam nowym obiektem stworzonym przy użyciu konstruktora.

I to jest to!

2011-12-07T10:27:00.001-08:00

http://demotywatory.pl/3538540/Ilu-swoich-nauczycieli-moglbys-obrazic-cytujac-Einsteina

Podpowiedzi optymalizatora

2011-12-05T06:01:00.003-08:00

Podpowiedzi optymalizatora

Podpowiedzi optymalizatora stosuje się kiedy chcemy wpłynąć na sposób działania optymalizatora kosztowego. Możemy mu np. kazać skorzystać z indeksu, lub tego zabronić. Możemy nakazać mu wykorzystanie określonego algorytmu łączenia tabel, a także wpływać na wiele innych procesów.

Generalnie podpowiedzi (hintów) używa się w ostatniej kolejności w procesie optymalizacji i tylko wtedy gdy optymalizator kosztowy ewidentnie się myli, albo mamy do czynienia z sytuacjami o których optymalizator kosztowy nie wie (np. czas wykonania zapytania dla algorytmu o teoretycznie wyższym koszcie jest krótszy).

Podpowiedzi powinny następować zaraz po klauzuli SELECT i mają konstrukcję następującą:

SELECT /*+ podpowiedź */ LAST_NAME, FIRST_NAME FROM EMPLOYEES;

Podpowiedź musi się znaleźć pomiędzy /*+ a */ i następować zaraz po SELECT. Jeśli popełnimy błąd (np. literówka) podpowiedź zostanie zignorowana i potraktowana jako komentarz. Nie możemy wymusić hintem czynności niemożliwych, takich jak np. zastosowanie indeksu który założony jest na kolumnę innej tabeli niż ta do której sięgamy.

INDEX

SELECT /*+ index(e nazwaindeksu)*/ nazwakolumny from nazwatabeli e;

Wymusi zastosowanie indeksu do pobierania wartości z kolumny (np. zamiast Full scana po tabeli).

NO_INDEX

SELECT /*+ no_index(e nazwaindeksu)*/ nazwakolumny from nazwatabeli e;

Zabroni wykorzystania indeksu do pobrania wartości z kolumny.

ALL_ROWS

Select /*+ALL_ROWS*/ Employee_Id From Employees e;

Wymusi stosowanie takich algorytmów by optymalizacja była nastawiona na efektywny zwrot wszystkich wierszy.

FIRST_ROWS

Select /*+FIRST_ROWS(n)*/ Employee_Id From Employees e;

Wymusi stosowanie takich algorytmów by optymalizacja była nastawiona na efektywny zwrot pierwszych „n” wierszy.

FULL

Select /*+FULL(e)*/ employee_id from employees;

Wymusi wykorzystanie pełnego skanu po tabeli przy pobieraniu danych.

USE_NL

Select /*+use_nl(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu nested loops (pętle zagdzieżdżone) do łączenia tabel.

NO_USE_NL

Select /*+no_use_nl(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu nested loops (pętle zagdzieżdżone) do łączenia tabel.

USE_MERGE

Select /*+use_merge(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu sort merge join podczas łączenia tabel.

NO_USE_MERGE

Select /*+no_use_merge(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu sort merge join podczas łączenia tabel.

USE_HASH

Select /*+use_hash(e d)*/ * From Employees e join departments d using(department_id);

Wymusi wykorzystanie algorytmu hashującego do łączenia tabel.

NO_USE_HASH

Select /*+no_use_hash(e d)*/ * From Employees e join departments d using(department_id);

Zabroni wykorzystania algorytmu hashującego do łączenia tabel.

CURSOR_SHARING_EXACT

Select /*+cursor_sharing_exact*/ * from employees where employee_Id=:x

Wymusi potraktowanie zmiennych bindowanych w zapytaniu tak jakbyśmy mięli włączony parametr CURSOR_SHARING na EXACT w sytuacji kiedy mamy ustawiony inaczej niż domyślnie.

Podpowiedzi optymalizatora jest znacznie więcej. Tutaj przedstawiłem najczęściej używane.

Co dalej?

2011-12-05T05:29:00.000-08:00

Znasz już różne techniki związane z optymalizacją SQL. Przyszedł czas by to wykorzystać. Masz tutaj listę rzeczy które trzeba wykonać na bazie:

<>
- Sprawdzić jak wyglądają plany wykonania (tkprof)
- Jeśli zbyt często pojawiają nam się nie optymalne metody dostępu (np. Full scan po tabeli) tam gdzie to nie jest naprawdę konieczne, to przyszedł czas na przemyślenie konstrukcji (indeksy,tabele zorganizowane indeksowo). Poszukaj przede wszystkim najbardziej obciążających zapytań.
- Poodświeżaj statystyki
- Sprawdź czy gdzieś da się wykorzystać zmienne bindowane
- Poustawiaj różne parametry instancji które mogą być pomocne przy optymalizacji (np. cursor_sharing)
- Sprawdź czy optymalizator nie podejmuje błędnych decyzji, sprawdź różne alternatywy przy użyciu hintów
- goto start;

Now you know...

Powodzenia!

Andrew

Używanie zmiennych bindowanych

2011-12-05T05:28:00.001-08:00

Zmienne bindowane

W Oracle funkcjonują zmienne bindowane, powiązane z sesją. Możemy wprowadzić do takiej zmiennej wartość, a następnie wykonać zapytanie z użyciem tej zmiennej. Jakie to ma znaczenie przy strojeniu SQL?

Jeżeli wykonujemy wiele podobnych zapytań:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=100;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=104;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=107;

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=109;

dla każdego z tych zapytań wymyślany jest osobny plan wykonania. To nie jest dobre z przynajmniej dwóch powodów:

Oracle musi poświęcić czas i zasoby na analizę i opracowanie explain planu (planu wykonania).
Zapełniana jest przestrzeń w shared_pool w sumie niemal identycznymi zapytaniami – a właściwie to związanymi z nimi planami wykonania.

Sytuacja taka jest dość powszechna. Wykorzystujemy słowniki, a te jako z reguły intensywnie eksploatowane powinny być wykorzystywane w sposób możliwie najbardziej optymalny.

Poszukaj w słowniku v$sql zapytań o podobnej konstrukcji np.

select * from v$sql where lower(sql_text) like '%from%employees%';

Dzięki temu sprawdzisz czy taka sytuacja ma również miejsce u Ciebie.

Aby się przed tym uchronić możemy nieco zmodyfikować zapytania. Przede wszystkim tworzymy zmienną bindowaną :

variable x number;

następnie przypisz do niej wartość:

begin

:x:=100;

end;

Pamiętaj że nazwy zmiennych bindowanych przy przypisaniu i odwołaniu poprzedzamy dwukropkiem. Teraz możesz odwołać się do tej zmiennej w zapytaniach:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:X;

Pamiętaj że wartość zmiennej możesz zmieniać dowolną ilość razy. Jednak niezależnie od tego, dla optymalizatora kosztowego będzie to to samo zapytanie. Będzie mógł więc wykorzystać wcześniejszy plan wykonania. Na tym polega „trick”. Po prostu zamiast produkować właściwie identyczne zapytania różniące się tylko jedną wartością, korzystamy ze zmiennej bindowanej i wykonujemy zapytanie z jej użyciem. Podmieniamy tylko wartość tej zmiennej.

Zmienne bindowane a CURSOR_SHARING

Możesz ustawić parametr cusor_sharing na force:

ALTER SESSION SET CURSOR_SHARING=FORCE;

ALTER SYSTEM SET CURSOR_SHARING=FORCE;

dzięki czemu w przypadku takich drobnych różnic literalnych (jak employee_id w poprzednich przykładach), Oracle sam sobie stworzy zmienną bindowaną na potrzeby tej wartości i będzie działał tak, jakbyśmy stworzyli zmienną bindowaną i podstawiali jej wartości.

W przypadku ustawienia CURSOR_SHARING na SIMILAR i jednoczesnym korzystaniu ze zmiennych bindowanych, Oracle będzie traktował tak samo dwa zapytania o takiej budowie:

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=:x;

ponieważ employee_id jest kolumną na którą jest założony klucz główny. Jakakolwiek wartość trafi do zmiennej bindowanej X, sens zapytania i sposób wykonania się nie zmienią. Zawsze uzyskamy jeden wiersz. W przypadku takiego zapytania:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=:x;

sens zapytania w zależności od wartości X może ulec zmianie. Może być wiele wierszy które mają daną wartość w kolumnie department_id, a ich liczebność oraz rozłożenie może ulec zmianie w zależności od wartości x.

Statystyki i histogramy

2011-12-05T04:38:00.000-08:00

Czym są statystyki?

W skrócie statystyki są zbiorem informacji na temat tabel, kolumn i indeksów. Zawierają informacje o np. ilości wierszy w tabeli, ich zróżnicowaniu, największej i najmniejszej wartości występującej w kolumnie. W oparciu o te informacje, optymalizator kosztowy (CBO) podejmuje decyzje o algorytmie dostępu do danych (przypominamy sobie analogię do podróży i różnych środków transportu z rozdziału o wprowadzeniu do optymalizacji), szacuje koszty.

Należy je odświeżać, bo w przeciwnym przypadku CBO opierając się o nieprawdziwe informacje pochodzące z przedawnionych statystyk podejmować będzie decyzje błędne odnośnie doboru algorytmów i metod dostępu do danych.

Rodzaje statystyk

Statystyki tabel

Zawierają informacje o :

ilości wierszy w tabeli
ilości bloków zajmowanych przez tabelę
średnia długość wierszy

Słownik: DBA_TAB_STATISTICS

Statystyki indeksów

Zawierają informacje o :

ilości poziomów w drzewie indeksu
ilości unikalnych wartości (lub ich kombinacji) w kolumnie (lub kolumnach) na którą został założony indeks.

Słownik: DBA_IND_STATISTICS

Statystyki systemowe

Zawierają informacje o :

wydajności I/O
wydajności procesora

Statystyki kolumn

Zawierają informacje o:

ilości unikalnych wartości w kolumnie
ilości nulli w kolumnie
średniej długości wartości w kolumnie
maksymalnej wartości
minimalnej wartości
zróżnicowanie danych w kolumnie.

Słownik: DBA_TAB_COL_STATISTICS

Histogramy

Same statystyki zawierają informacje podstawowe o zróżnicowaniu zawartości kolumn – takie jak np. maksymalna czy minimalna wartość oraz ilości unikalnych wartości w kolumnie. Histogramy zawierają znacznie bardziej szczegółowe informacje o zróżnicowaniu. Znajdziesz tutaj np. informacje o częstotliwości występowania konkretnych wartości. Zasadniczo informacje dotyczące histogramów znajdziesz w tej samej tabeli co statystyki tj. DBA_TAB_COL_STATISTICS i dodatkowo w DBA_TAB_HISTOGRAMS.

Generalnie tworzenie histogramów jest najbardziej czasochłonne podczas odświeżania statystyk.

Dzięki histogramom możesz wywnioskować jaki rodzaj indeksu można nałożyć na daną kolumnę. Pamiętaj że indeks bitmapowy nakładamy tam gdzie zróżnicowanie w kolumnie jest małe, indeks b-tree tam gdzie zróżnicowanie jest duże. Jeśli się pomylisz, może się okazać że optymalizator kosztowy nie będzie chciał wykorzystywać indeksu.

Odświeżanie statystyk i histogramów

Odświeżanie statystyk systemowych:

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS;

Odświeżanie statystyk indeksu:

EXECUTE DBMS_STATS.GATHER_INDEX_STATS('nazwaschematu','nazwaindeksu');

Odświeżanie statystyk tabeli

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('nazwaschematu','nazwatabeli');

Takie wywołanie spowoduje odświeżenie statystyk i histogramów dla tabeli którą wskażemy, ale również dla indeksów założonych na kolumny w tej tabeli.

Odświeżanie statystyk schematu

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('nazwaschematu');

Takie wywołanie odświeży statystyki i histogramy dla wszystkich tabel i wszystkich indeksów w schemacie.

Mamy możliwość włączenia specjalnego monitoringu dla kilku tabel i odtwarzać statystyki tylko dla tych obiektów które tego wymagają. To czy tego wymagają czy nie określamy my, poprzez wskazanie wartości procentowej danych w obiekcie które zostały zmienione. Określamy np. że jeżeli zmieniło się 14% danych w tabeli employees to dla tej tabeli mają zostać wygenerowane statystyki.
Pierwsze co czynimy to ustawienia dla tabel:

alter table employees monitoring;

Następnie co jakiś czas otwarzamy statystyki dla schematu, ale w sposób nieco zmieniony:

execute dbms_stats.gather_schema_stats(ownname=>'hr',estimate_percent=>14,options=>'GATHER STALE');

Powyżej wywołałem odtwarzanie statystyk w schemacie hr dla tabel które zmieniły sie w 14 lub więcej procentach. Objęte tym będą tylko tabele dla których włączyliśmy monitoring.
Teraz wystarczy to wrzucić w jakiegoś joba, by statystyki odtwarzały nam się cyklicznie.

Odświeżanie statystyk całej bazy danych

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;

Wszystkie statystyki w całej bazie zostaną odświeżone. Baardzo czasochłonna operacja.

Parametr OPTIMIZER_DYNAMIC_SAMPLING

Dynamiczne próbkowanie wykorzystywane jest gdy brak statystyk dla tabeli, lub gdy są one na tyle przestarzałe że nie można im ufać.

Poziom próbkowania określa parametr optimizer_dynamic_sampling który możemy zweryfikować w słowniku v$parameter. Domyślnie ten parametr ustawiony jest na wartość 2. Dopuszczalne wartości mieszczą się w przedziale 0 do 2.

0 oznacza zupełne wyłączenie dynamicznego próbkowania. 1 – próbkowanie stosowane jest tylko w przypadku gdy:

jest przynajmniej jedna niezanalizowana tabela wykorzystywana w zapytaniu.
Nieprzeanalizowana tabela nie posiada indeksów

Poziom 2 to włączenie dynamic sampling w taki sposób by próbkował wszystkie nieprzeanalizowane tabele, wyższe stopnie określają coraz bardziej agresywne próbkowanie.

Kasowanie statystyk

EXECUTE DBMS_STATS.DELETE_TABLE_STATS('hr', 'employees');

Blokowanie statystyk

EXECUTE DBMS_STATS.LOCK_TABLE_STATS('HR','EMPLOYEES');

Używane jest gdy chcemy zablokować stan kiedy nie ma statystyk dla obiektów, a chcemy by zawsze wykorzystywał DYNAMIC SAMPLING, albo kiedy chcemy zatrzymać statystyki w pewnym momencie tak by nie były automatycznie odświeżane.

Odblokowujemy w ten sposób:

EXECUTE DBMS_STATS.UNLOCK_TABLE_STATS('HR','EMPLOYEES');

Jeśli zablokujesz statystyki dla tabeli, wszystkie statystyki zależne (np. statystyki indeksów) również zostaną zablokowane.

Odtwarzanie statystyk

Czasami odświeżanie statystyk idzie w złym kierunku, i chcielibyśmy przywrócić stan statystyk np. sprzed odświeżenia. Za każdym razem kiedy odświeżamy statystyki, ich poprzednia wersja jest zachowywana byśmy mogli przywrócić ich stan. Wykorzystujemy w tym celu procedurę restore_table_stats z pakietu dbms_stats:

Execute Dbms_Stats.Restore_Table_Stats(Ownname=>'hr', tabname=>'employees',as_of_timestamp=>to_timestamp('05-12-2011 12:00:00','dd-mm-yyyy hh24:mi:ss'));

Historia statystyk

Zawsze możemy sprawdzić kiedy były generowane statystyki dla tabeli, dzięki czemu będziemy wiedzieli np. do którego miejsca przywrócić statystyki. Aby obejrzeć historię statystyk przeglądamy słownik DBA_TAB_STATS_HISTORY.

SELECT * FROM DBA_TAB_STATS_HISTORY WHERE TABLE_NAME='EMPLOYEES';

Automatyczne generowanie statystyk przez Oracle

Statystyki generują się również automatycznie. W wersji 10g Oracle, automatyzacja generowania statystyk związana była z programem GATHER_STATS_PROG wywoływanym przez job o nazwie GATHER_STATS_JOB. Przy użyciu słowników :

DBA_SCHEDULER_JOBS
DBA_SCHEDULER_SCHEDULES

możemy odnaleźć szczegóły dotyczące częstotliwości odświeżania statystyk. W wersji 11g job ten nie jest już widoczny. Informacje o szczegółach automatycznego generowania statystyk znajdziemy w słowniku DBA_AUTOTASK_CLIENT_JOB .

Operatory złączeniowe

2011-12-05T03:02:00.000-08:00

Operatory złączeniowe

Kiedy wykorzystujemy łączenie tabel w zapytaniach (joiny) , łączenie ich może odbywać się na kilka sposobów. Poniżej ich lista i charakterystyka.

Nested loops

Działa to w oparciu o pętle zagnieżdżone. Podczas łączenia dwóch tabel bierzemy wiersz z jednej tabeli i przeszukujemy całą druga tabelę w poszukiwaniu odpowiedników. Stosowany najczęściej kiedy jedna z tabel jest mała.

Sort Merge Join

W tym rodzaju łączenia pobierane są dane z obu tabel po kawałku i sortowane po kolumnach wg których łączymy tabele. Następnie posortowane są porównywane ze sobą. Samo sortowanie jest operacją kosztową , więc taki rodzaj łączenia spotkamy rzadko. Wykorzystywany jest kiedy sortowanie i tak jest wymagane w związku z samym zapytaniem, lub przy połączeniach nierównościowych (kiedy warunkiem łączenia jest inny operator niż = ).

Hash join

Mniejsza tabela jest zamieniana w pamięci na tablicę hashową, druga jest hashowana i porównywana z pierwszą.

Cartesian Join

Join kartezjański stosowany jest kiedy połączymy dwie tabele bez podania warunku łączenia. Następuje wtedy łączenie na zasadzie „każdy z każdym”.

Metody dostępu do danych. Indeksy i dostęp po indeksach

2011-12-05T02:03:00.000-08:00

Co to jest index?

Jest obiektem bazodanowym niezależnym logicznie i fizycznie od tabeli. Pozwala uzyskać szybszy dostęp do danych. Indeksy zakłada się na kolumnę w tabeli, kilka kolumn naraz (do 32) . Bez nich wszystko będzie działać, jednak indeksy pozwolą nam szybciej dostać się do danych. Indeksy przechowują wartości kolumn na które są nakładane oraz ROWID wiersza.

Rodzaje indeksów

B-tree : Najczęściej wykorzystywane indeksy. Wykorzystuje się je tam gdzie dane w kolumnach na które zakłada się taki indeks są dość mocno zróżnicowane. Przykładowo nr pesel w tabeli ze wszystkimi obywatelami Polski. Nie przechowuje wartości nullowych. Tworzenie:

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Bitmapowe: Wykorzystuje je się tam gdzie jest małe zróżnicowanie danych np. nazwa województwa w tabeli ze wszystkimi obywatelami Polski. Przechowuje wartości nullowe. Nie występują w wersji Express Edition Oracle.

CREATE BITMAP INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Złożone: Składają się z większej niż jedna ilości kolumn. Stosuje się je kiedy w warunku where, podczas łączenia tabel lub w grupowaniu występuje kilka kolumn naraz. Powinno się stosować w indeksie taką kolejność kolumn jak występuje w zapytaniach.

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY1, NAZWAKOLUMNY2)

Unikalne: Zakładane na te kolumny w których wartości są unikalne. Umożliwiają stosowanie unique scan.

CREATE UNIQUE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY)

Funkcyjne: Oparte na funkcjach które przetwarzają zawartość kolumn na które są nakładane. Stosuje się je kiedy w tabeli mamy dane nieprzetworzone, a wykorzystujemy przetworzone w zapytaniach.

CREATE INDEX NAZWAINDEKSU ON NAZWATABELI(NAZWAKOLUMNY*2)

Tabele zorganizowane indeksowo: To tabele o strukturze indeksu b-tree. Wykorzystuje się je często przy słownikach (w bazodanowym pojęciu). Taka tabela musi posiadać klucz główny.

CREATE TABLE NAZWATABELI(

KOLUMNA1 TYPDANYCH PRIMARY KEY,

KOLUMNA2 TYPDANYCH

) ORGANIZATION INDEX;

Skany po indeksach

Unique Scan

Wiemy już że przeszukiwanie indeksów będzie szybsze niż przeszukiwanie tabeli. Wiemy też że indeksy przechowują rowidy wierszy.

Jeżeli na kolumnie założymy indeks, Oracle będzie mógł go przeszukać pod kątem wartości przez nas podanych by odnaleźć rowid wiersza. Kolejność w tym przypadku jest taka:

Oracle przeszukuje indeks w poszukiwaniu wartości 100
Znajduje wartość w indeksie i odczytuje przypisany do tej wartości rowid.
Poprzez rowid (adres fizyczny wiersza na dysku) Oracle dostaje się do wiersza w tabeli.

Ten rodzaj skanu wykorzystywany jest kiedy poszukujemy dane poprzez kolumnę z kluczem głównym, lub jeśli na kolumnie której dotyczy warunek nałożony jest indeks unikalny.

Range Scan

Skan zakresowy, wykorzystywany jest kiedy stosujemy warunki typu < lub > na kolumnach na które założony jest index.

Full Scan

Jest to liniowe przeszukiwanie całego indeksu. Po co jest to not null? Założony mamy index b-tree, a jak wspomniałem wcześniej taki indeks nie przechowuje wartości null! Co za tym idzie , jeśli nie zagwarantujemy optymalizatorowi że albo w kolumnie nie ma wartości null (kolumna z warunkiem not null) albo nie powiemy mu że ewentualne wartości null nawet jeśli występują to nas nie interesują (warunkiem użytym w tym przykładzie), to optymalizator nie mając pewności czy zwróci wszystkie dane będzie wykonywał full scan ale po tabeli. Odnosi się to również do innnych rodzajów skanów!

Fast Full Scan

Wykorzystywany w przypadkach kiedy wszystkie informacje niezbędne do pobrania znajdują się

w indeksie. W takim przypadku w ogóle nie ma potrzeby sięgania do tabeli.

Skip Scan

Wykorzystywane wtedy gdy podczas przeszukiwania indeksu można pominąć bloki, w których z całą pewnością nie znajdziemy poszukiwanych przez nas danych.

Skany po tabelach indeksowych

Zauważ że mimo że wykonuję pełny skan po tabeli, mam rodzaj skanu charakterystyczny tylko dla indeksów. Taka tabela zbudowana jest jak indeks (struktura drzewiasta). Dodatkową korzyścią

z tworzenia tabeli zorganizowanych indeksowo, jest możliwość stosowania na nich skanów typowych dla indeksów – np. unique scan. To sprawia że takie tabele użyteczne są np. przy

słownikach.

Metody dostępu do danych - dostęp do tabeli

2011-12-04T14:20:00.000-08:00

Oracle w celu pobrania danych może zastosować różne metody. Może wykorzystywać przeszukiwanie tabel w całości, lub korzystać z indeksów w celu odnalezienia danych w tabelach. Poniżej zamieszczam listing różnych metod dostępu dostępnych dla tabel i dla indeksów.

Tabele	Full Table Scan Rowid Scan Sample Table Scan
Indeksy	Unique Scan Range Scan Full Scan Fast Full Scan Skip Scan Index Join

Dane mogą zostać pobrane przy użyciu wymienionych wyżej metod, w zależności oczywiście od tego jakie możliwości mu damy (przypominamy sobie historię z trasą z Gdyni do Krakowa). W pierwszej kolejności omówię każdą z tych metod dostępu do danych. W żadnym wypadku nie pomijaj tego rozdziału! Znajomość tych metod i ich dobre zrozumienie jest bardzo ważne!

Full Table Scan

To przeglądanie całej tabeli wiersz po wierszu w celu znalezienia informacji które nas interesują. Tego typu przeglądanie tabeli jest naprawdę mało wydajne. Wyobraź sobie że szukasz jednego wiersza. Pracownika o numerze 100, który znajduje się „gdzieś” w tabeli zawierającej ogólne milion wierszy. Aby go znaleźć tą metodą musisz przejrzeć całe te milion wierszy jeden po drugim aż nie znajdziesz właściwego. Czy to nie jest marnotrawstwo czasu i zasobów? Optymalizator kosztowy wybiera tą metodę dostępu do danych kiedy nie ma indeksów z których mógłby skorzystać, kiedy żądamy od bazy całej zawartości tabeli , lub gdy zapytanie jest skonstruowane tak że uniemożliwia skorzystanie z indeksu (na razie tym się nie przejmuj, wyjaśni się nieco później).

Rowid Scan

Przede wszystkim muszę Ci przybliżyć pojęcie rowid. Każdy wiersz w tabeli zawiera rowid, który reprezentuje fizyczną lokalizację danego wiersza na dysku. Możesz wyświetlić te wartości:

Jeśli taki adres jest znany, Oracle może pominąć przeszukiwanie tabeli wiersz po wierszu i od razu dostać się do poszukiwanego wiersza korzystając z ROWID:

Oczywiście rzadko kiedy stosuje się takie wyszukiwanie jak na powyższym obrazku. Taki adres (rowid) pobierany jest z indeksu najczęściej z indeksu i dopiero w kolejnym etapie następuje dostęp do tabeli z wykorzystaniem rowid.

Sample Table Scan

Ten rodzaj dostępu do tabeli wykorzystywany jest do pobierania próbek danych. Zwraca losowe wiersze z tabeli.

Struktura fizyczna Oracle

2011-12-04T13:49:00.001-08:00

Struktura fizyczna

Na bazę danych składa się kilka podstawowych elementów. Poza samymi plikami danych przechowującymi informacje gromadzone w bazie danych, mamy do czynienia jeszcze z takimi rodzajami plików jak:

Pliki kontrolne

Zawierają informacje o bazie danych jako takiej. Przechowują informacje o ostatnim SCN, o strukturze bazy. Są bardzo ważne, bez nich nie uruchomimy instancji.

Plik parametrów

Zawiera parametry niezbędne do pracy instancji -np. wielkość buforów w SGA. Czytany jest przy starcie instancji.

Pliki dziennika powtórzeń

Do nich zapisywane są informacje o zmianach w bazie danych, zanim jeszcze te zmiany zostaną uwzględnione w plikach danych. W sytuacji gdy nastąpi awaria i dane które występowały tylko pod postacią tzw. brudnych bloków w buforze db_cache mogą zostać odtworzone na podstawie tych plików.

Zarchiwizowane pliki dziennika powtórzeń

Są kopiami plików dziennika powtórzeń które nie są potrzebne do samego działania instancji, za to są wykorzystywane przy tworzeniu kopii zapasowych i odtwarzaniu bazy danych.

Pliki backupu

Pliki kopii zapasowych są plikami wykorzystywanymi do odtworzenia bazy danych po awarii.

Pliki trace i alert log

Alert log jest plikiem do którego pisane są informacje o ewentualnych problemach instancji lub ważnych wydarzeniach – takich jak wyłączenie czy włączenie bazy danych. Do trace file zapisywane są szczegóły błędów, a także informacje nie istotne dla samego działania instancji – takie jak wyniki śledzenia sesji pod kątem stosowanych planów wykonania zapytań.

Struktura logiczna Oracle

2011-12-04T13:48:00.001-08:00

Struktura logiczna

Tablespace

Baza danych podzielona jest na logiczne struktury zwane tablespace.Tworzy się je w np. celu izolacji danych dwóch systemów. Istnieja predefiniowane przestrzenie tablespace : System, sysaux,temp, undo, example. Z każdym tablespace związany jest przynajmniej jeden fizyczny plik.

Segment

Obiekty np. tabele czy indeksy, przechowywane są pod postacią segmentów. Każdy segment podzielony jest na extenty.

Extent

Są składowymi segmentów. Na extenty składają się bloki.

Bloki

Bloki są najmniejszym elementem logicznym w bazie danych Oracle. Ich wielkość musi być zawsze wielokrotnością wielkości bloku dysku twardego. Ich domyślna wielkość to 8KB.

Wielkość tą można ustawić podczas tworzenia bazy danych.

Kurs optymalizacji SQL

2011-12-04T13:35:00.000-08:00

Niniejszym udostępniam kurs optymalizacji SQL w Oracle. Zasadniczo mechanizmy są zbliżone w innych bazach, więc nawet jeśli zajmujesz się bazami Microsoftu czy Teradaty, możesz zapoznać się z tym materiałem bo w znacznym stopniu działa to w sposób bardzo zbliżony.
Ponieważ tuning sql należy do zagadnień bardzo złożonych, najlepiej będzie jeśli najpierw przejrzysz ten kurs dosyć ogólnie by mieć pogląd na całość i mniej więcej orientować się co jest co, a następnie przejdziesz ten kurs jeszcze raz skupiając się na szczegółach. W razie pytań pozostaję jak zwykle do dyspozycji. Jeśli macie jakieś pytania techniczne, zadawajcie je najlepiej w komentarzach by inni też mogli skorzystać z odpowiedzi.
Przed podejściem do tego kursu powinieneś mieć "w małym palcu" zawartość kursu SQL ( http://andrzejklusiewicz.blogspot.com/2010/11/kurs-oracle-sql.html). Jeśli nie znasz zagadnień z kursu SQL, będzie Ci bardzo ciężko przebrnąć przez ten kurs. Powinieneś też wiedzieć czym jest SGA, jak funkcjonuje zapis danych oraz ich odczyt. Jeśli nie wiesz, zapoznaj się z tym tematem choćby z kursu administracji. Tutaj wrzucam małe przypomnienie, rozszerzone o dodatki istotne z punku widzenia optymalizacji. Powinieneś też przynamniej w zakresie podstawowym posługiwać się narzędziami SQL Developer i SQL*Plus. Informacje z tego zakresu znajdziesz w kursie SQL oraz PL SQL na tej stronie internetowej.
Umieściłem tutaj opis struktur fizycznych i logicznych Oracle, ale jest on bardzo ogólnikowy - po prostu informacje niezbedne do podejścia do tego kursu. Nie traktuj tego jako kompedium, chodzi o to byś mniej więcej orientował się co jest co, bo dalej w kursie odwołuję się do wiadomości z tych rozdziałów.
Osoby troszeczkę bardziej zorientowane w temacie zauważą pewnie że zawartość tego kursu w dużej mierze pokrywa się z zawartością autoryzowanego szkolenia Oracle:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D52163GC10&p_org_id=1001&lang=US

Jak się nie jest trudno domyślić, nie jest to przypadek :) Niech będzie to taki mój przyspieszony nieco gwiazdkowy prezent dla rzeszy analityków, administratorów aplikacji, programistów dla których nie starczyło w tym roku budżetu szkoleniowego oraz dla tych których na takie szkolenie zwyczajnie nie stać. Czołem studencka braci!
Mała prośba z mojej strony: jeśli podoba Ci się ten kurs albo blog jako całość udostępnij go na swoim "fejsie", nk czy innym portalu. Na dole masz przyciski do tego. Być może pomożesz w ten sposób komuś innemu w nauce. Będzie to też dla mnie sygnał że warto to rozwijać i poświęcać temu projektowi czas. Wiele rzeczy tutaj jest "po łebkach" - z braku czasu chwilowo. Jeśli będę widział zainteresowanie, to wydziergam parę chwil na rozszerzenie.
Dzięki i zapraszam.

Śledzenie sesji , śledzenie aplikacji pod kątem planów wykonania. Narzędzie tkprof.

2011-12-04T12:58:00.001-08:00

Zawartość tego rozdziału stanie się użyteczna gdy będziemy chcieli obserwować co się dzieje w naszej sesji, lub sesji wykorzystywanej przez aplikację którą zarządzamy. Dzięki tej opcji będziemy mogli przejrzeć jakie są wykorzystywane explain plany dla zapytań, oraz szczegółowe informacje statystyczne.

Pierwsze co musimy zrobić to włączyć śledzenie dla tej sesji. Jeśli chcemy śledzić własną sesję włączamy po prostu:

ALTER SESSION SET SQL_TRACE = TRUE;

lub

execute dbms_session.set_sql_trace(true);

Możemy też ustawić parametr TIMED_STATISTICS na true:

ALTER SESSION SET TIMED_STATISTICS=TRUE;

Dzieki temu będziemy mięli informacje na temat czasów wykonania poszczególnych elementów zapytania, oraz na temat zdarzeń wait. Ze zdarzeniami wait mamy do czynienia wtedy, kiedy Oracle czeka na jakiś proces zanim będzie mógł kontynuować wykonywanie zapytania . Przykładowo chcemy zmienić coś w bazie. Bloki które będziemy zmieniać muszą trafić do bufora db_cache. Muszą zostać zaczytane z dysku. Zanim to się jednak stanie, proces naszego update musi poczekać. To jest właśnie takie zdarzenie.

Możemy również obserwować inną sesję:

execute dbms_system.set_sql_trace_in_session(SID, SERIAL#,TRUE|FALSE);

Sid i serial# są wartościami które jednoznacznie identyfikują pojedyńczą sesję. Możemy te wartości pobrać ze słownika v$session:

W powyższym przypadku wyszukałem sobie sesję użytkownika HR. Gdybym zechciał obserwować jego sesję mogę wykonać:

execute dbms_system.set_sql_trace_in_session(33,3,TRUE);

Wyłączenie śledzenia sesji wygląda tak:

dla własnej sesji:

ALTER SESSION SET SQL_TRACE = FALSE;

śledząc własną sesję możesz też wykonać polecenie :

ALTER SESSION SET tracefile_identifier='jakasnazwa';

Dzięki temu, plik trace będzie w nazwie posiadał to co podamy między "dziabągami" i łatwiej go będzie zidentyfikować.

Śledzenie dla innej sesji:

execute dbms_system.set_sql_trace_in_session(33,3,FALSE);

Wyniki śledzenia zarówno mojej, jak i innej sesji znajdą się w pliku na dysku. Plik ten znajdzie się w katalogu określonym przez parametr USER_DUMP_DEST.

Najlepiej jest przejść do tego katalogu i wyszukać właściwy plik po dacie utworzenia:

Następnie wywołujemy narzędzie tkprof by przetworzyć ten plik śladu do postaci czytelnej dla człowieka:

Pierwszy parametr to źródłowy plik śladu, drugi to plik do którego ma zostać wyrzucony czytelny wynik. Po włączeniu śledzenia sesji wykonałem zapytanie :

SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID);

następnie po przetworzeniu pliku śladu tkprofem znalazłem go w swoim pliku wynikowym. Oto efekt:

********************************************************************************

select *

from

employees join departments using (department_id)

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.00 0.04 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1 0.00 0.00 0 13 0 50

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.00 0.04 0 13 0 50

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 33

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 20 0.28 0.66 0 0 4 0

Execute 21 1.81 4.63 100 206 144 1

Fetch 18 0.09 0.22 5 1614 0 179

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 59 2.18 5.51 105 1820 148 180

Misses in library cache during parse: 11

Misses in library cache during execute: 3

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1852 1.10 3.33 17 142 10 0

Execute 4952 1.06 6.24 120 5178 4365 1422

Fetch 6780 2.98 23.69 28217 44647 1 10017

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 13584 5.15 33.27 28354 49967 4376 11439

Misses in library cache during parse: 214

Misses in library cache during execute: 75

129 user SQL statements in session.

3453 internal SQL statements in session.

3582 SQL statements in session.

********************************************************************************

Trace file: C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP\xe_ora_2144.trc

Trace file compatibility: 10.01.00

Sort options: default

1 session in tracefile.

129 user SQL statements in trace file.

3453 internal SQL statements in trace file.

3582 SQL statements in trace file.

227 unique SQL statements in trace file.

37791 lines in trace file.

766 elapsed seconds in trace file.