wtorek, 11 września 2012

Słowniki przydatne przy programowaniu w PL/SQL

user_source - Kod źródłowy programów których jesteśmy właścicielami.
user_arguments - Argumenty naszych procedur i funkcji
user_procedures - Lista naszych procedur, funkcji, wyzwalaczy
user_dependencies - zależności pomiędzy naszymi obiektami (w tym programami pl sql wzajemnie, oraz programami pl sql a innymi obiektami bazodanowymi).
user_plsql_object_settings - W tym słowniku znajdziemy informacje na temat sposobu kompilacji (natywna/interpretowana) naszych programów.

Jeśli w miejsce przedrostka user_ damy all_ to zamiast informacji na temat tylko naszych obiektów, dostaniemy informacje na temat wszystkich obiektów do których mamy dostęp.

VPD : Virtual Private Database

W dużym skrócie, VPD umożliwia odgórne ustawienie filtrowania tabeli dla różnych użytkowników.  Najlepiej sprawdzić to na przykładzie. Dla mnie to przypomnienie tematu i notatka, ale może kogoś ten temat zainteresuje.
Założenie projektu:
W schemacie HR mamy tabelę EMPLOYEES z listą 107 pracowników. Każdy z pracowników ma swojego użytkownika bazodanowego. Chcemy by ci użytkownicy widzieli w tabeli tylko wiersze dotyczące ich kolegów z tego samego działu, ale by nie widzieli wierszy dotyczących pozostałych osób.  Nazwy użytkowników będą takie jak emaile w tabeli.
Sposób działania VPD w tym zastosowaniu będzie taki:

1. Użytkownik loguje się. Wyzwalacz pobiera numer departamentu w którym pracuje użytkownik o emailu takim jak nazwa logującego się właśnie użytkownika. Ustawia w zmiennej pakietowej specjalnego pakietu obsługującego VPD  numer departamentu przy użyciu procedury procedury z tego pakietu.
2. Kiedy użytkownik odwoła się do tabeli EMPLOYEES w schemacie HR,  do jego zapytania zostaną dodana w tle warunki zwrócone przez specjalną funkcję z tego samego pakietu.
3. Zapytanie użytkownika z dodanymi warunkami zostanie wykonane, a ograniczony wynik wyświetlony.

Potrzebujemy więc kilku rzeczy;
1. Wyzwalacza sprawdzającego numer departamentu danego pracownika.
2. Pakietu w którym znajdą się:
              -  zmienna przechowująca wartość owego numeru departamentu
              -  procedura ustawiająca wartość w w/w zmiennej , wywoływana przez wyzwalacz
              -  funkcja która będzie dodawała warunki do zapytań
3. Konfiguracja która powiąże zadawanie zapytań przez tego użytkownika z ograniczeniami  obsługiwanymi przez pakiet.


W pierwszej kolejności tworzymy więc pakiet:




Następnie wyzwalacz który będzie sprawdzał nazwę logującego się użytkownika i na tej podstawie będzie szukał numeru departamentu do którego przypisany jest pracownik o adresie email takim jak nazwa logującego się użytkownika.


Wyzwalacz znaleziony numer departamentu przekazuje do procedury ustaw_departament w pakiecie koledzy, która wstawia numer tego departamentu do zmiennej w pakiecie.
Został ostatni element, a mianowicie ogniwo które to wszystko ze sobą zwiąże.
Dodajemy politykę. Pierwsze  dwa parametry dotyczą obiektu którego polityka ma dotyczyć.  Schemat w którym znajduje się dany obiekt, oraz nazwa tego obiektu. Trzeci parametr to nazwa polityki. Jeden obiekt może mieć wiele polityk. Czwarty i piąty parametr dotyczą pakietu który zarządza dostępem a który przed momentem stworzyliśmy. Schemat w którym znajduje się owy pakiet, oraz nazwa funkcji która zwraca warunki do zapytań. Ostatni parametr to operacje których nasza polityka ma dotyczyć.





Sprawdzamy efekty. Tworzę sobie użytkownika o nazwie SKING (pracownik o employee_id=100).
Trzeba mu też nadać standardowy zestaw uprawnień , oraz uprawnienia do operacji na tabeli employees w schemacie użytkownika hr.


Teraz już tylko się loguję i sprawdzam czy działa:



Gdyby objawiły się jakoweś ORA-XXXX, sprawdź trace file, albowiem tam właśnie objawia się oświecenie zaprawdę powiadam Ci :)


PS. Pytanie roku na szkoleniu dzisiaj. Pytanie dotyczyło wyników znajdujących się w RESULT_CACHE , które w związku ze zmianą danych na podstawie których owe wyniki zostały wyliczone stają się INVALID , czyli nieprawidłowe:  "Ale po co trzymać tych inwalidów? Nie lepiej usunąć?"   : D
Thanks :) You made my day :) Już myślałem że po pobudce przy użyciu kosiarek pod blokiem o 6 rano (słownie: szóstej rano) i kilkukrotnym wyrwaniu ze snu przez kretynów bez tłumika nic tego dnia nie uratuje. A jednak :)
PS2. I jeszcze po serii dziwnych testów i obliczeń doszliśmy dziś do wniosku że Arabowie i Chińczycy mają ograniczoną wolność wypowiedzi w kolumnach typu varchar2 ze względu pochodzenie i język.  Nie pytajcie.... :)

poniedziałek, 10 września 2012

Bufor result cache : optymalizacja SQL i PL/SQL

Uwaga: funkcjonalność ta jest dostępna dopiero od Oracle 11g!!!

Moim zdaniem jedna z lepszych funkcjonalności dodanych w tej wersji. Jeśli często zadajemy zapytania które zwracają nam ten sam wynik, ponieważ dane na podstawie których ten wynik jest wyliczany rzadko ulegają zmianom, możemy przy użyciu bufora RESULT CACHE wykorzystać wcześniejszy wynik.
Działa to w taki sposób, że wynik zapytania jest zapisywany do bufora, a jeśli ponownie zażądamy wyniku tego samego zapytania, wynik zostanie pobrany z RESULT CACHE. Domyślnie jednak funkcjonalność ta nie jest wykorzystywana.  Aby ją wykorzystać, musimy posłużyć się hintem optymalizatora kosztowego, lub ustawić taką opcję  jako domyślną dla sesji/systemu.  Jeśli ulegną zmianie dane na podstawie których został wyliczony wynik znajdujący się w RESULT CACHE, wynik taki zostaje oznaczony jako nieaktualny i zostanie przy kolejnym wywołaniu przeliczony ponownie.

Zwykły explain plan bez użycia RESULT_CACHE:




Plan z wykorzystaniem hinta:




Korzystając z pakietu dbms_result_cache możemy sprawdzić użycie bufora. Musimy jednak włączyć output, ponieważ to na niego zostanie wyrzucony raport:



Zamiast korzystać z hinta, możemy ustawić korzystanie z result seta jako domyślne dla systemu. Poniżej dwie opcje:



Opcja z FORCE ustawi korzystanie z result seta jako domyślne. To oznacza że wynik każdego zapytanie wyląduje w RESULT_SET. Czy jest to dobre rozwiązanie? Myślę że każdy powinien rozważyć to dla swojego przypadku. Należy pamiętać że w buforze obowiązuje kolejka LRU. W przypadku dużej rotacji, może się okazać że wynik zapytania jest z bufora wypychany zanim zostanie ponownie użyty, natomiast przez RESULT_SET przelatują wyniki rzadko zadawanych zapytań, które zapychają nam bufor.
Opcja MANUAL przywróci stan domyślny tj. aby wynik zapytania wylądował w RESULT SETcie trzeba będzie użyć hinta.

Istnieje też możliwość ustawienia domyślnego korzystania z RESULT SETa  dla pojedynczej sesji:




WYNIKI ZNAJDUJĄCE SIĘ W BUFORZE RESULT SET SĄ DOSTĘPNE POMIĘDZY SESJAMI (to akurat raczej dobra wiadomość :) ) !


RESULT SET możemy również wykorzystywać w PL SQL.



Rozchodzi się o tą linijkę : "result_cache relies_on(employees)" . Taki dopisek sprawi , że wyniki pochodzące z wywołania tej funkcji będą cache'owane w RESULT SETcie. Wynik ten zostanie wykorzystany przy ponownym wywołaniu tej funckji dla takich samych wartości parametrów.


Przetestujmy więc działanie tego:




Wywołałem funkcję którą zdeklarowałem wcześniej tak by jej wyniki były domyślnie cachowanie w RESULT SETcie i sprawdzam bufor:


Elegancko się zbuforowało :) Smacznego :)



niedziela, 9 września 2012

Kompilacja kodu do postaci natywnej i kompilowanej [tuning pl sql]

Mamy dwa rodzaje kompilacji kodu PL SQL w Oracle. Program może zostać skompilowany do postaci natywnej lub interpretowanej.

Kompilacja do postaci interpretowanej : tak jest ustawione domyślnie. Kod programu ląduje w słowniku systemowym i jest interpretowany przed każdym wykonaniem. Program szybciej się kompiluje, ale wolniej uruchamia w porównaniu do postaci natywnej. Dobre do środowisk testowych, gdzie często przebudowujemy kod. Mało fajny na środowiskach produkcyjnych, zwłaszcza jeśli program będzie wywoływany często.

Kompilacja do postaci natywnej: to trzeba sobie ustawić (dla systemu , albo dla sesji). Kod programu jest kompilowany do kodu natywnego i przechowywany w tablespace SYSTEM. Nie musi być za każdym uruchomieniem interpretowany. Uruchamia się szybciej w porównaniu z postacią interpretowaną. Dobre do środowisk produkcyjnych.




Możemy włączyć typ kompilacji dla sesji lub dla systemu, jak widać to powyżej.  Dopiero od tej pory programy będą kompilowane do wybranej postaci. Wszystkie skompilowane do tej pory programy pozostają w postaci pierwotnej! Jeśli istniejące już programy chcemy mieć w postaci natywnej, to po ustawieniu trybu kompilacji na natywną należy je ponownie skompilować.

Włączyłem kompilację natywną dla sesji i skompilowałem procedurę. Zrekompilowałem też istniejący wcześniej wyzwalacz. Po wyświetleniu danych ze słownika widzimy że i nowa procedura i rekompilowany wyzwalacz są skompilowane do postaci natywnej. Pozostałe nadal są przechowywane w postaci interpretowanej.






Kompresja i deduplikacja w Oracle Secure Files

Uwaga! Własność ta jest dostępna dopiero od Oracle 11g!

Od wersji 11g wprowadzono nowe algorytmy przetwarzania i nowe sposoby przechowywania LOBów. Dzięki nim uzyskamy szybszy odczyt i zapis LOBów, a także mamy możliwość zmniejszenia ilości miejsca przez nie zajmowanych.



Domyślnie LOBy są przechowywane po staremu i dopiero my musimy powiedzieć że chcemy przechowywać loby jako Secure Files. Na powyższej ilustracji prezentuję jak stworzyć tabelkę z kolumną dostosowaną do przechowywania nowego formatu LOBów.

Istnieje też możliwość kompresji i deduplikacji danych (to ta linijka z alter table). Sztuczka polega na tym, że Oracle trzyma tylko jedną kopię tego samego LOBa, a nie jak wcześniej wiele kopii. Korzyść z włączenia deduplikacji wynika wprost z ilości duplikatów. Kompresja "HIGH" pozwala na oszczędzenie nawet 60-, 70% miejsca , w zależności od rodzaju danych.

LOBy tymczasowe

Cześć,
dziś chciałbym przedstawić Wam w żołnierskich słowach loby tymczasowe. Odróżnia je od zwykłych lobów fakt, że są przechowywane w tablespace tymczasowym , a nie normalnym z danymi. Można je fajnie wykorzystać do przetwarzania lobów, jeśli np. nie chcemy modyfikować loba bazowego, a potrzebujemy przepchnąć dalej loba po przetworzeniu w jakiś sposób.


Powyżej krótka prezentacja. Stworzyłem cloba tymczasowego, załadowałem do niego trochę danych. Sprawdziłem wielkość i wyświetliłem ją na ekranie. Teraz mógłbym przekazać takiego cloba tymczasowego do kolejnej procedury/ funkcji / czegokolwiek. Na koniec usunąłem cloba z tablespace tymczasowego. Nawet gdybym tego nie zrobił, to taki clob istnieje domyślnie maksymalnie do końca sesji.

Odtwarzanie procedury / funkcji a uprawnienia do niej

Taka szybka notko-ciekawostka na temat uprawnień w PL/SQL :

Jeśli skasujesz procedurę lub funkcję, a następnie stworzysz nową o takiej samej nazwie to wszystkie uprawnienia do wykonywania owej procedury nadane użytkownikom idą do diabła. Jeśli zastosujesz CREATE or REPLACE, poprzednia procedura zostanie nadpisana, ale uprawnienia pozostaną.
Dziękuję, dobranoc.

poniedziałek, 3 września 2012

Oracle - zmiana dbid bazy (łatwo, prosto i przyjemnie)

Kursant na szkoleniu zapytał mnie ostatnio o jakąś bezproblemową możliwość zmiany dbid bazy. Wrzucam , bo pewnie nie jeden z Was spotkał się z taką potrzebą (np. podczas duplikacji).

Step 1:
Odpalamy bazę w trybie mount  :
shutdown immediate;
startup mount; (z sqlplusa)

Step 2:

nid =xe  lub nid=/ (jeśli sid wybranej bazy mamy w zmiennej ORACLE_SID) -z konsoli

powinniśmy zobaczyć coś takiego:


Step 3 :

odpalamy bazkę z RESETLOGS :

startup mount;
alter database open resetlogs;

Step 4:

Robimy backup, bo po zmianie dbid nie będziemy mogli odtworzyć bazy z poprzednich backupów, a warto mieć jakieś zabezpieczenie (chyba że jesteś hardcorem)


PS. Dzięki Ci Sebastian  za to pytanie, bo to w sumie dobry temat do odnotowania.