 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| C/C++ / CGI / Sieć Novell / PHP / Java / SQL / Oracle / WebSphere MQ / WebSphere Message Broker / JavaScript / IT Quiz | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
Informacje podstawowe O bazie Oracle Architektura Oracle Instalacja Oracle Analiza zapytań Tworzenie bazy Typy danych Funkcje SQL Hurtownie danych Uprawnienia Obiekty bazy danych Rodzaje złączeń Kopie bezpieczeństwa MTS Przywileje systemowe EXPLAIN PLAN Dynamiczny SQL PL/SQL JDBC Oracle Parametry startowe Pliki serwera Słownik bazy danych Strojenie bazy Transakcje Literatura Jeden plik Legenda w przygotowaniu * w trakcie tworzenia Struktury pamięciowe Procesy drugoplanowe Segmenty wycofania SGA Globalny obszar systemu jest specjalnym blokiem pamięci, w którym baza danych zapisuje najczęściej wykonywane operacje na bazie, czy tez informacje związane z funkcjonowaniem bazy danych. Obszar ten stanowi statycznie podzielony obszar pamięci współużytkowanej przez wszystkie procesy bazy Oracle. Do wersji 9i wielkość tego bloku była pobierana z pliku init.ora i nie mogła być zmieniana w czasie pracy bazy. W celu zmiany tego parametru należy zatrzymać bazę, zmienić wartość parametru i uruchomić bazę ponownie. W wersji 9i bazy wartość ta może być generowana dynamicznie w czasie pracy aplikacji. Wielkość tego buforu określamy w parametrze DB_CACHE_SIZE. Od wersji 9i bazy Oracle wprowadzono również parametr SGA_MAX_SIZE określający, do jakiej wielkości może się rozrosnąć obszar SGA. Pamięć podręczna słownika Pamięć podręczna słownika jest częścią SGA zawierającą informacje często pobierane z tabel słownikowych. Tabele słownikowe zawierają informacje dotyczące wszystkich obiektów bazy danych. Do takich tabel odwołujemy bardzo często i wskazane jest, aby dane w nich zawarte nie były za każdym razem czytane z dysku, tylko z pamięci, co jest zdecydowanie szybsze. Rozmiar tej pamięci określa się poprzez parametr SHARED_POOL_SIZE pliku init.ora. Bufor dziennika powtórzeń Bufor dziennika powtórzeń zawiera informacje potrzebne do odtwarzania transakcji, które z tego bufora co pewien czas są zapisywane do plików czynnego dziennika powtórzeń. Polepsza to wydajność bazy danych, ponieważ dane nie są zapisywane do plików, tylko są buforowane w pamięci. Obszar dzielony Na obszar dzielony składa się omawiana wcześniej pamięć podręczna słownika oraz biblioteczna pamięć podręczna oraz dzielony obszar SQL. Biblioteczna pamięć podręczna (library cache) pozwala na współużytkowanie najczęściej używanych poleceń SQL. Dzielony obszar SQL zawiera informacje dotyczące planu wykonania oraz sposób parsowania wykonanych instrukcji SQL. Obszar ten pozwala na przyspieszanie wykonywania tych instrukcji. Jeśli wykonujemy instrukcje, która znajduje się już w tym buforze system nie musi ponownie generować plan wykonania oraz sposób parsowania tej instrukcji, ponieważ dane te znajdują się w buforze. Dzielony obszar SQL, tak jak większość buforów bazy danych Oracle działa w oparciu o algorytm LRU. Bufor ma określoną wielkość, dane są zapisywane do bufora nieustannie. W przypadku, kiedy nie ma już miejsca w buforze najdłużej nie używane dane znajdujące się w buforze są z niego usuwane, aby zrobić miejsce na nowe dane. Obszar dużych obiektów Obszar dużych obiektów jest dodatkowym buforem danych do przechowywania dużych obiektów np. przy archiwizacji i odtwarzania danych. Wielkość tego bufora określa parametr LARGE_POOL_SIZE. Obszar JAVA Obszar JAVA jest wykorzystywany do buforowania poleceń języka JAVA. Zwielokrotniony obszar buforów Wartości dotyczące wielkości buforów danych dotyczą standardowej wielkości bufora danych. Tak samo jak możemy w bazie Oracle 9i używać segmentów o rożnych wielkościach bloków danych tak samo możemy ustawiać wielkości buforów w zależności od używanych w bazie wielkości bloków danych. Parametry te to np. DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE. PGA Obszar ten, to globalny obszar programu wykorzystywany przez pojedynczy proces i nie jest dzielony przez inne procesy. SMON Proces SMON (System Monitor) jest procesem drugoplanowym zajmującym się kontrola, czy podczas startu bazy danych nie wymaga ona automatycznego odtwarzania za pomocą plików czynnego dziennika powtórzeń, usuwanie zbędnych obiektów transakcji. Dodatkowo proces ten zajmuje się zarządzaniem fragmentacja bazy poprzez łączenie sąsiednich wolnych bloków w jedna większą całość. PMON Proces PMON (Process Monitor) zajmuje się zarządzaniem procesów użytkownika poprzez usuwanie pozostałości po niepoprawnie zakończonych procesach użytkownika. DBWR Proces DBWR (Database Writer) zajmuje się cyklicznym zapisywaniem danych zapisanych w obszarze SGA do plików danych. Takich procesów może być więcej niż jeden. Liczbę tych procesów określamy parametrem DB_WRITER_PROCESSES. W przypadku, kiedy system operacyjny, na którym zainstalowana jest baza danych nie obsługuje asynchronicznych operacji wejścia - wyjścia, stosujemy jeden proces DBWR oraz kilka podrzędnych procesów wejścia - wyjścia. Liczbę tych procesów określamy za pomocą parametru DBWR_IO_SLAVES LGWR Proces LGWR (Log Writer) zajmuje się zarządzaniem zapisem zawartości bufora dziennika powtórzeń do plików czynnego dziennika powtórzeń. Tak jak i w przednich procesach proces ten zapisuje dane cyklicznie, a nie na bieżąco. CKPT Proces CKPT (Checkpoint) jest procesem zajmującym się rejestracjami punktów kontrolnych. Punkty te poprzez aktualizacje nagłówków plików danych oraz plików sterujących mogą spowodować przyspieszenie czasu potrzebnego do odtworzenia instancji po awarii. Z procesem tym związane są dwa parametry pliku konfiguracyjnego LOG_CHECKPOINT_INTERVAL oraz LOG_CHECKPOINT_TIMEOUT. ARCH Proces ARCH (Archiver) działa w przypadku, kiedy baza danych uruchomiona jest w trybie ARCHIVELOG. W trybie tym, kiedy plik dziennika powtórzeń zapełni się tworzona jest kopia tego pliku. Za to właśnie odpowiada proces ARCH. RECO CJQx LMSx Dxxx Sxxx Segmenty wycofania są specjalnymi obiektami Oracle stosowanymi do zarządzania transakcjami. Kiedy rozpoczynamy transakcje segmenty wycofania wykorzystywane są do zapamiętania stanu modyfikowanych danych, tak, aby w przypadku wycofania transakcji łatwo można było wrócić do stanu sprzed rozpoczęcia tej transakcji. W tej części postaram sie wyjaśnic krok po kroku proces instalacji Oracle. poniższy proces był przeprowadzany w systemie Red Hat 9 z bazą danych Oracle 9i. (9.2.0). Stworzenie użytkownika oracle po zalogowaniu się jako użytkownik root. groupadd dba Dla przykładu przy instalacji bazy w katalogu /opt/ora9: mkdir -p /opt/ora9/product/9.2 compat-gcc-7.3-2.96.118.i386.rpm W pliku /etc/sysctl.conf należy wprowadzić linie: kernel.shmmax = 536870912 oracle soft nofile 65536 echo 250 32000 100 128 > /proc/sys/kernel/sem export ORACLE_BASE=/opt/ora9 Kiedy pojawi się drugi problem należy w nowej konsoli wykonać komendę: cd $ORACLE_HOME/install gcc -o ctxhx -L/opt/ora9/product/9.2/ctx/lib/ -L/opt/ora9/product/9.2/lib/ Teraz po wykonaniu tej instrukcji ignorujemy ten błąd. Po zakończeniu instalacji należy wykonać poniższe komendy: cd $ORACLE_HOME/network/lib ctxhx: $(CTXHXOBJ) ctxhx: $(CTXHXOBJ) $ make -f $ORACLE_HOME/ctx/lib/ins_ctx.mk install Teraz możemy uruchomić agentów poleceniem: $ /opt/ora9/product/9.2/bin/agentctl start Następnie ustawiamy dodatkowe zmienne środowiskowe export ORACLE_BASE=/home1/orahome Celem uruchomienia instancji należy zalogować się na prawach sysdba poleceniem: sqlplus 'sys/passwd as sysdba' a następnie wykonać polecenie: create spfile from pfile='fullpathofthe<init>.ora'; gdzie: fullpathofthe<init>.ora : pełna ścieżka do pliku <init>.ora stworzonego narzędziem dbca. Następnie wykonujemy: shutdown immediate; bfile : Dane binarne przechowywane poza bazą (max 4GB) blob : Dane binarne o zmiennej długości (max 4GB) char : Łańcuch o stałej długości (max 2KB) clob : Łańcuch o zmiennej długości (max 4GB) date : Data i czas (max 7) long : Łańcuch o zmiennej długości (max 2GB) long raw : Dane binarne o zmiennej długości (max 2GB) nchar : Łańcuch o stałej długości (max 2KB) nclob : Łańcuch o zmiennej długości (max 4GB) number : Liczba (max 38) nvarchar2 : Łańcuch o zmiennej długości (max 4KB) raw : Dane binarne o zmiennej długości (max 2KB) rowid : Identyfikator wiersza (max 10) varchar : Łańcuch o zmiennej długości (max 4KB) varchar2 : Łańcuch o zmiennej długości (max 4KB) Indeksy Klastry Kursory Partycje Perspektywy materializowane Sekwencje : Synonimy : Tabele : Przestrzenie nazw : Widoki : Indeks jest specjalna struktura mającą na celu przyspieszenie operacji odczytu danych z tabel. Indeksy zakłada się na kolumnie lub kilku kolumnach tabeli. Założenie takiego indeksu oznacza, że powstanie specjalna struktura, którą możemy porównać z indeksem w książce, gdzie w szybki sposób przeglądając indeks w poszukiwania słowa mamy dostęp do informacji, gdzie w książce znajduje się poszukiwane słowo. Indeks w zdecydowany sposób przyspiesza dostęp do danych w tabeli jeśli selektywność jest niska ( w odpowiedzi na zapytanie zwracana mala ilość danych ). Zaleca się stosowanie indeksu w przypadku zwracania do ok. 5% rekordów tabeli. Kiedy selektywność jest wyższa (powyżej 5%). Rodzaje indeksów Indeksy można podzielić na dwa główne typy, indeksy b-drzewa oraz indeksy bitmapowe. Indeksy b-drzewa Indeksy b-drzewa do wyszukiwania położenia rekordów stosuje się strukturę podobna do odwróconego drzewa. Na samej górze tego drzewa znajduje się korzeń, poniżej są poziomy z gałęziami, a na samym dole znajdują się liście zawierające informacje o położeniu rekordu bądĽ rekordów w tabeli. Indeksy bitmapowe Indeks bitmapowy działa zupełnie inaczej niż indeks b-drzewa. Jest on najlepszy w przypadku niskiej selektywności. Dla przykładu kolumna z wartościami 1 lub 0 nadaje się do założenia na niej indeks bitmapowy. Informacje przechowywane w takim indeksie są w postaci mapy bitów dla każdej wartości występującej w danej kolumnie. Główną zaletą takich indeksów jest to, że bardzo łatwo takie mapy bitów można łączyć. Przykładem takiego efektywnego wykorzystania indeksów jest sytuacja, kiedy kilka wartości o malej selektywności może być połączonych w złączenie o wysokiej selektywności. Tabele zorganizowane obiektowo Tabele zorientowane obiektowo są bardzo podobne do indeksów b-drzewa. Zasadnicza różnica polega na tym, że w liściu nie znajduje się adres ROWID, a właściwe dane. Zaletą takiego rozwiązania jest to, że ograniczamy tu ilość operacji wejścia-wyjścia. W przypadku zwykłych indeksów najpierw przeszukujemy indeks, a potem dopiero pobieramy dane z tabeli znajdujące się pod wskazanym adresem. Tutaj, w przypadku tabel zorientowanych obiektowo przeszukujemy tylko jeden raz. Indeksy oparte na funkcjach Indeksy oparte na funkcjach stanowią specyficzną odmianę indeksów b-drzewa. Różnica polega na tym, ze zamiast wartości kolumn w indeksie zapisane są rezultaty funkcji podanej podczas tworzenia indeksu. Takie indeksy są efektywne, kiedy mamy zapytanie SQL, gdzie w klauzuli where występuje funkcja. Przykład: SELECT * from users where current_sales- last_sales > 0; Dla takiego zapytania możemy stworzyć indeks poleceniem: Create index ind_sales on users (current_sales- last_sales); Klaster, to specjalny obiekt, łączący w jeden byt kilka tabel ze sobą związanych. Oczywiście sensowne jest tworzenie indeksu klastrowego tylko wtedy, kiedy w klastrze znajdują się tabele występujące w złączeniach zapytań SQL. Utworzenie w takiej sytuacji klastra na tych tabelach zdecydowanie przyspiesza wykonywanie zapytań SQL. Przyspieszenie polega na tym, ze w przypadku złączenia tabel w klastrze dane mogą być buforowane w SGA. Dodatkowo przyspieszenie to jest spowodowane tym, że złączenie w klastrze odbywa się za pomocą jednej operacji wejścia-wyjścia. Należy tu podkreślić, że klastry mogą przyspieszyć wykonywanie zapytań, ale nie musza. Jeśli np. w klastrze z niewiadomych przyczyn umieścimy tabele, które w żadnym zapytaniu SQL nie są ze sobą złączane, to stworzenie indeksu może przynieść wręcz odwrotne skutki i spowodować spadek wydajności wykonywania zapytań. Dane w tym wypadku będą niepotrzebnie buforowane w SGA. Klastry nie należy zakładać na tabelach, na których będą często generowane pełne przeszukiwania na pojedynczych tabelach z tego klastra. Do takiej operacji wymagana jest dodatkowa ilość pamięci, co za sobą powoduje wykonywanie dodatkowych operacji wejścia-wyjścia. Klastry haszujące Klastry haszujące są podobne do zwykłego klastra. Różnica polega na tym, ze zamiast indeksu wskazujacego na klucz stosuje się specjalna funkcje haszujaca. Wynik tej funkcji jednoznacznie okresla miejsce, gdzie znajduja się dane. W przypadku klastra haszujacego na podstawie wyniku funkcji haszujacej od razu wiadomo, gdzie znajduja się dane ograniczajac liczbe operaci wejścia-wyjścia do jednej. W przypadku klastrow haszujacych prz identycznych danych funkcja haszujaca bedzie zwracala identyczna wartość. Systemowe pseudo kolumny Deklaracja kursora Pobieranie danych z kursora Użycie pętli FOR ... LOOP Kursory FOR UPDATE Zmienne kursorowe Systemowe pseudo kolumny nie sa typowymi kolumnami tabeli lecz kolumnami wirtualnymi, które możemy jedynie odczytywać. Do tych kolumn należą: ROWID : Adres rekordu w tabeli. Aby móc przechowywać tą wartość należy użyć specjalnego typu UROWID ROWNUM : Numer rekordu (jego położenie) względem początku rekordów zwracanych przez zapytanie. Tak dla przykładu jeśli zapytanie SQL zwróci np. 10 rekordów dla pierwszego zwróconego rekordu wartość ROWNUM będzie miała wartość 1, drugiego 2, ..., a ostatniego 10. Poniższy przykład pokazuje w jaki sposób pobrać 5 pierwszych rekordów zapytania. select * from tab1 where rownum <=5 Składnia deklaracji kursora ma postać: CURSOR nazwa [(parametr[, parametr,...])] [RETURN typ_zwracany] IS zapytanie; Przykładowa deklaracji kursora: CURSOR c1 is select * from tab1; Pobieranie danych z kursora odbywa sie za pomocą wyrażenia FETCH, które pobiera wiersz z rezultatu zapytania i zapisuje go do odpowiednich zmiennych. Ogólny schemat wykorzystania w programach kursorów przebiega w kolejności jak poniżej: otwarcie kursora (OPEN)w pętli pobranie wiersza z kursora do odpowiedniej zmeinnej (FETCH)zamknięcie kursora (CLOSE)Przykład wykorzystania kursora: DECLARE Pętla ta pozwala nam w alternatywny sposób operować na kursorach, w którym wiersz kursora zapisywany jest do zmiennej rekordowej. Schemat takiej pętli ma postać: FOR zmienna_rekordowa IN nazwa_kursora LOOP DECLARE Za pomocą kursora z opcją FOR UPDATE mamy możliwość blokowania wierszy w tabeli aby mieć pewność, że się nie zmienią przed wykonaniem poleceń update, czy też delete. W przypadku deklaracji takiego kursora wszystkie jego wiersze są blokowane kiedy otwieramy kursor (nie w przypadku pobrania danych instrukcją FETCH). W związku z czym nie możemy używać instrukcji FETCH po zatwierdzeniu transakcji lub jej wycofaniu. Najczęściej tego typu kursor jest stosowany w przypadku instrukcji UPDATE bądĽ DELETE z sentencją CURRENT OF. Mozliwa jest tu również opcja NOWAIT oznaczająca, że system nie będzie czekał na odblokowanie rekordów, jeśli będa one zablokowanie przez innego uzytkownika. W takiej sytuacji nastapi powrót do programu i innych zadań a następnie kolejna próba sprawdzenia blokady. Klauzula kursora FOR UPDATE posiada jesze swoją odmianę FOR UPDATE OF. Ta forma jest stosowana w przypadku kursora definiującego zapytanie z połączonymi tabelami, gdzie po zwrocie FOR UPDATE OF podajemy nazwę kolumny z jednej z połączonych tabel, co będzie oznaczało, że nastapi blokada tylko wierszy z tej tabeli. Wiersze pozostałych tabel biorących udział w złączeniu nie będą blokowane. Przykład: DECLARE Zmienne kursorowe zachowujmą sie jak zwykłe kursory, z tym że jemu dymicznie przypisujemy zapytanie. Oznacza to, że w przypadku zwykłego kursora zapytanie mu przypisane jest statyczne. W przypadku zmeinnej kursorowej zapytanei jest generowane dynamicznie, a to oznacza, że temu kursorowi możemy przypisać dowolne zapytanie zaleznie od okoliczności. Tworzenie zmiennej kursorowej przebiega w dwóch etapach. W pierwszej fazie musimy zdefiniować specjalny typ kursorowy wg. poniższej składni: TYPE nazwa_typu IS REF CURSOR [ RETURN zwracany_typ]; gdzie: zwracany_typ : typ rekordowy zwracany przez kursor Następnie deklarujemy zmienną o typie zdefiniowanym wcześniej. Skłądnia pozwalająca otworzyć kursor ma postać:OPEN nazwa_zmiennej_kursorowej FOR zapytanie_SQL; Przykłady wykorzystania zmiennych kursorowych DECLARE DECLARE Partycjonowanie jest mechanizmem pozwalającym na podział tabel lub indeksów ma mniejsze części - "partycje". Stosuje się je najczęściej przy bardzo dużych tablicach, czy indeksach, kiedy mimo stosowania indeksów, wydajność nie jest zadowalająca. Dzieje się tak ponieważ partycje są mniejsze i dostęp do nich jest bardziej wydajny. Partycje można uznać za podzbiór tabeli w jakiś sposób ze sobą związanych ograniczając dane zapisane w tabeli do węższego grona. Przykładem może być tutaj tabela zawierająca dane o sprzedaży. Tabele taka, jeśli byłaby tak duża, ze dostęp do niej byłby mało wydajny można podzielić na partycje, gdzie każda partycja będzie zawierała dane o sprzedaży tylko i wyłącznie z jednego roku. Zbiór wszystkich partycji będzie stanowił dopiera pełną tabelę. Partycje, podobnie jak to ma miejsce w przypadku indeksów zakłada się na kluczach określających kolumnę bądĽ kolumny tabeli. We wcześniej podanym przypadku takim kluczem będzie rok. Tak samo jak to ma miejsce w innych przypadkach podstawa do zaprojektowania partycji powinna być charakterystyka zapytań odwołujących się do tej tabeli. Rozróżniamy kilka sposobów partycjonowania: partycjonowanie zakresowe z lista wartości z kluczem haszowym partycje złożone {%1} Partycjonowanie zakresowe Kluczem w takich partycjach jest konkretny zakres danych. Przykład: CREATE TABLE sales ( Partycje z lista wartości W partycjach z lista wartości zamiast na przedziałach klucz partycji zakłada się na konkretnych wartościach kolumn. Przykład: CREATE TABLE tab2 ( Partycje z kluczem haszowym Partycje takie używają specjalnej funkcji haszujacej do określenia w której partycji znajdzie się konkretny rekord. Stosuje się ja dla bardzo dużych tabel, gdzie nie ma żadnych reguł umożliwiających wykorzystanie poprzednich dwóch sposobów, ale ze względu na swoja wielkość mamy pewność, że taka partycja poprawi wydajność. W tego rodzaju partycjach podajemy tylko ilość i nazwy partycji, a system sam zadba o odpowiednie rozłożenie rekordów pomiędzy tymi partycjami. Przykład: CREATE TABLE tab2 ( Partycje złożone Partycje złożone polegają na podziale tabeli na 2 kryteria. Najpierw tabele można podzielić zakresowo i dodatkowo te partycje można wewnętrznie podzielić wg klucza haszowego. Przykład: CREATE TABLE tab2 ( Perspektywy materializowane są lokalna replika odległej bazy danych. Rozróżniamy perspektywy materializowane tylko do odczytu lub modyfikowalne. Innym podziałem takich perspektyw jest podział na proste i złożone. Perspektywy proste jak sama nazwa wskazuje opierają się na prostych zapytaniach, które nie zawierają agregacji, klauzuli connect by złączeń lub operacji ustawień. Jeśli perspektywa opiera się na zapytaniu zawierającym powyższe klauzule określamy jako złożone. Ważne jest ze w przypadku perspektyw złożonych zawsze następuje replikacja całkowita. W przypadku perspektyw prostych natomiast możemy wybrać pomiędzy replikacją całkowitą a replikacją przyrostową. Aby móc korzystać z takiej perspektywy najpierw należy stworzyć połączenie lokalnej bazy danych ze zdalna baza. Połączenie takie tworzy się poleceniem: Create database link db_linkconnect to user_name identyfied by user_passwordusing 'service' db_link : nazwa połączenia user_name: nazwa użytkownika odległej bazy danych user_password : hasło tego użytkownika service : nazwa usługi lub serwisu odległej bazy danych Przykład polecenia tworzącego taka perspektywę na lokalnym serwerze może mięć postać: Create snapshot local_view Przykład takiego polecenia może mięć postać: Create materialized view log on emp Tak samo jak zapytania SQL na zdalnym serwerze możemy z naszej lokalnej bazy również wywoływać zdalne procedury. Dla zdalnej procedury calc (id IN NUMBER), aby ją wykonać na lokalnym serwerze poprzez połączenie db_link wykonujemy polecenie: execute calc@db_link(1265); Nested loops Algorytm nested loops (petli zagnierzdzonej) polega na pszeszukiwaniu zewnetrznej tabeli i dla kazdego rekordu,ktory ma byc zlaczony przegladana jest wewnetrzna tabela celem znalezienia pasujacych rekordów. Algorytm ten generuje bardzo duzo operacji wejścia-wyjścia, natomiast w dosc niewielkim stopniu obciaza procesor. Sort-merge W przypadku algorytmu sort-merge obie tabele, ktore laczymy są odczytywane i sortowane, nastepnie jednoczesnie obie tabele są przegladane od ich poczatku. Podczas tego przegladania rekord z pierwszej tabeli porownywany jest z rekordami znajdujacymi się w poczatkowej czesci drugiej tabeli. Jako, ze obie tabele są posortowane, nie ma problemu, ze stwierdzeniem, ze rekordy do siebie nie pasuja. Proces ten jest powtarzany az do konca tabeli. Hash join Algorytm hash join polega na stworzeniu specjalnej funkcji haszujacej za pomocą ktorej nastepuje laczenie rekordów dwoch tabel. Liczba operacji wejścia-wyjścia jest tu najmniejsza ze wszystkich metod złączania. Niestety procesor jest tu najbardziej obciążony. Polecenie EXPLAIN PLAN jest wykorzystywane do strojenia zapytań SQL. Jako parametr przyjmuje ono analizowane zapytanie SQL, a jako wynik podaje plan wykonania tego zapytania w zależności od wybranego w systemie optymalizatora. Jest to narzędzie ułatwiające analizę efektów poczynionych zmian w systemie, np. po utworzeniu lub usunięciu indeksów, czy tez tez po wprowadzeniu podpowiedzi do zapytania itp. Wyniki tego polecenia są umieszczane w specjalnej tabeli. Domyślna nazwa tej tabeli jest plan_table. Taka tabele można utworzyć skryptem utlxplan.sql. Przykłądowe wywołanie tego polecenia ma postać: EXPLAIN PLAN SELECT SUBSTR(LPAD(' ', 2*( LEVEL-1))||operation,1,30) Do wykonywania dynamicznych instrukcji SQL wykorzystywane jest wyrażenie EXECUTE IMMEDIATE. Przykład: EXECUTE IMMEDIATE 'create table tab1(id NUMBER)'; Przykład: query:='create table :name (id NUMBER)'; W powyższym przykładzie do zmiennej przypisaliśmy łańcuch, w którym zamiast :name spodziewamy się zmiennej dynamicznej podanej podczas wywoływania wyrażenia EXECUTE IMMEDIATE zaraz za słowem USING. Ważne jest, że zapytanie może mieć więcej dynamicznych zmiennych. W takiej sytuacji pod pierwszy argument zapytania zostanie podstawiona pierwsza zmienna znajdująca się po klauzuli USING. Pod drugi argument zostanie podstawiona druga zmienna itd. Przykład: query:='grant :right ON :object TO :trustee'; query:='update tab1 set col1=100 where col2=:1 RETURNING col2 INTO :2'; Struktura bloku Zmienne, stale Atrybuty zmiennych Kolekcje Kursory Obsługa błędów Procedury i funkcje Pakiety Rekordy Transakcje Autonomiczne transakcje Typy obiektowe Struktura bloku PL/SQL ma postać :
Deklaracje zmiennych i stałych używanych w bloku PL/SQL umieszczane są w sekcji DECLARE bloku. Przykład deklaracji zmiennych może wyglądać następująco: ... Następnie w sekcji BEGIN do zmiennych możemy przypisać wartości : ... Przykład deklaracji stałych natomiast może wyglądać następująco: ... Deklarując zmienne w PL/SQL system Oracje daje nam możliwość zadeklarowania pośrednio typ danych, określając, żę dana zmienna będzie takiego same typu jak kolumna w konkretnej bazie, lub jak rekord tabeli lub widoku, lub kursora. Typy te określa się przez poniższe atrybuty: %TYPE : Atrybut określający typ zmiennej na typ odpowiadający zadanej kolumnie w tabeli, widoku lub kursora. nl Przykład:value table1.col2%TYPE; Powyższa składnia oznacza, że zmienna value będzie tego samego typu, co kolumna o nazwie col2 tabeli table1. %ROWTYPE : Atrybut określający typ zmiennej na typ rekordowy odpowiadający kolumną w tabeli, widoku lub kursorze. nl : Przykład: rec1 tab%ROWTYPE; Przykład określa, że zmienna rec1 będzie typu rekordowego, której pola będą odpowiadały kolumną tabeli tab. I tak, jeśli tabela tab będzie zawierać kolumnę col1, to do pobrania wartości tej kolumny z rekordu rec1 będziemy używać notacji kropkowej. nl : Przykład: colvalue:=rec1.col1; Kolekcja jest uporządkowaną grupą elementów tego samego typu. Rozróżniamy trzy zasadnicze typy kolekcji: VARRAY, tabela zagnieżdżona i tabela INDEX BY. Kolekcja typu VARRAY Kolekcja typu VARRAY jest stosowana raczej do kolekcji mniejszych rozmiarów. Przy tego typu kolekcji przy definicji kolekcji wymagane jest podanie maksymalnej jej wielkości. Kolekcje te są zawsze gęste, tzn., ze operacje usuwania elementów nie powoduje powstawania luk w kolekcji. Składnia definicji: TYPE nazwa IS {VARRAY | VARYING ARRAY} (rozmiar) OF typ elementu [NOT NULL] Tabela zagnieżdżona Kolekcje te są początkowo gęste, lecz podczas usuwania elementów z kolekcji stają sie one rzadkie. W przypadku kolekcji typu TABLE nie podajemy jej rozmiaru. Poniższe typy danych nie być elementami tabel zagnieżdżonych: BINARY_INTEGER, pls_integer, boolean, blob, clob, (kolekcje typu VARRAY), long, long raws, natural, naturaln, nchar, nclob, nvarchar2, obiekty z elementami typu BLOB lub CLOB, positive, positiven, ref coursor, signtype, string, table, varray. Składnia definicji: TYPE nazwa IS TABLE OF typ elementu [NOT NULL] Kolekcja typu INDEX BY Kolekcje te są uporządkowanymi elementami dostępnymi jedynie w języku PL/SQL, a nie w bazie danych. Są one początkowo rzadkie. Kolekcja ta nie ma ograniczeń co do typu możliwych elementów, jak to ma miejsce w przypadku tabel zagnieżdżonych. Składnia definicji: TYPE nazwa IS TABLE OF typ elementu [NOT NULL] INDEX BY BINARY_INTEGER Przykłady deklaracji kolekcji type dates is VARRAY(366) OF DATE; Inicjowanie kolekcji odbywa się poprzez konstruktor systemowy o nazwie odpowiadającej nazwie typy kolekcji. W konstruktorze tym jako parametry możemy podać początkowe elementy kolekcji Przykład type Names is VARRAY(60) OF VARCHAR2(30); Odwoływanie się do elementów kolekcji Odwołując się do elementów kolekcji używamy nazwy kolekcji wraz z podanym w nawiasach indeksem elementu w kolejce. Indeks ten zawiera się w przedziale od -2 do potęgi 31 do 2 do potęgi 31 dla tabel INDEX BY, od 1 do 2 do potęgi 31 dla tabel zagnieżdżonych oraz od 1 do wartości określonej w deklaracji dla kolekcji typu VARRAY. Przykład IF names(1)='Marcin' THEN W związku z tym, że tabele zagnieżdżone i kolekcje typu VARRAY mogą być automatycznie puste kolekcje te możemy tylko przyrównywać do wartości null określającej, że kolekcja jest pusta jak to ma miejsce w poniższym przykładzie. IF members IS NULL THEN ... Niestety kolekcje nie mogą być porównywane między sobą. Poniższa sentencja wywoła błąd. IF coll1=coll2 THEN ... Przykład wykorzystywania tabel zagnieżdżonych create type Worker as Object ( insert into firms values ('Nazwa','Adres', Przykład aktualizacji rekordu declare Przypisywanie tabeli zagnieżdżonej do zmiennej declare Funkcja ta przypisuje kolekcje do tabeli bazy danych (na kolekcjach możemy operować jak na zwykłych tabelach). Przykład Insert into TABLE(select Workers from firms where name='Nazwa' values update TABLE(select Workers from firms where name='Nazwa') set name='Bocian' Uogólniając składnia zwracającą rekordy z tabeli zagnieżdżonej ma postać: TABLE(zapytanie SQL), gdzie: zapytanie SQL : zapytanie SQL określające o jaką tabelę zagnieżdżoną nam chodzi i jakiej tabeli 'standardowej' jest ona elementem. Dodatkowo przez klauzulę where możemy zawęzić listę rekordów z tabeli zagnieżdĽonej select * from TABLE(select tabela_zagnieżdżona from tabela_rodzic where kolumna_tabeli_zagnieżdzonej=jakaś_wartość); Metody te pozwalają w łatwy sposób operować na kolekcji. Wywołąnie następuje poprzez notacje kropkową: kolejka.metoda[(argument)] Metody kolekcji nie mogą być wywoływane w zapytaniach SQL. Metody EXTEND i TRIM nie mogą być używane z tabelami typu INDEX BY. Metody EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR i NEXT są funkcjami, EXTEND, TRIM i DELETE są procedurami. Metody EXISTS, PRIOR, NEXT, EXTEND, TRIM i DELETE wywoływane są z argumentami. W przypadku pustej kolekcji możemy wywołać tylko metodę EXISTS, w innym przypadku zostanie wygenerowany wyjątek COLLECTION_IS_NULL. nl : COUNT Dla kolekcji typu VARRAY zwraca tą samą wartość co funkcja LAST. W przypadku tabel zagnieżdżonych COUNT jest równoważne LAST tylko przed usunięciem jakiegokolwiek rekordu z kolekcji. nl : DELETE nl : DELETE(i) nl : DELETE(i,j) nl : EXISTS(i) IF nestedtab.EXISTS(2) then ... W przypadku podania jako argumentu wartości będącej poza dozwolonym przedziałem zostanie wygenerowany wyjątek SUBSCRIPT_OUTSIDE_LIMIT. nl : EXTEND nl : EXTEND[i] nl : EXTEND(i,j) nl : FIRST nl : LAST nl : LIMIT ograniczeniem ilości rekordów, więc funkcja ta zwraca wartość NULL. Dla kolekcji typu VARRAY funkcja zwraca maksymalną ilość rekordów, jaką może pomieścić ta kolekcja. Wartość tą podaje się zawsze w deklaracji kolekcji typu VARRAY. nl : PRIOR(i) nl : NEXT(i) Przykład przejścia po wszystkich elementach kolekcji ... nl : TRIM nl : TRIM(i) Jeżeli n jest większe od wartości zwróconej przez metodę COUNT system wygeneruje wyjątek SUBSCRIPT_BEYOND_COUNT. Osobiście nie polecam korzystać z tej metody. Preferuje metodę DELETE, gdzie mam większą kontrolę przy usuwaniu elementów. COLLECTION_IS_NULL : Próba wykonania operacji na pustej kolekcji NO_DATA_FOUND : Próba wykonania operacji na elemencie kolekcji, który nie istnieje SUBSCRIPT_BEYOND_COUNT : Próba wykonania operacji na elemencie, którego indeks przewyższa liczbę elementów w kolekcji SUBSCRIPT_OUTSIDE_LIMIT : Próba wykonania operacji na elemencie, którego indeks jest poza dozwolonym przez system Oracle zakresem (np. indeks o wartości -1) VALUE_ERROR : Próba wykonania operacji na elemencie o indeksie nie będącym liczbą (np. wartość NULL) Jest to mechanizm stosowany przy kolekcjach pozwalający w zdecydowanym stopniu ograniczyć komunikację pomiędzy PL/SQL, a silnikiem SQL. WyobraĽmy sobie sytuację, kiedy musimy duży blok danych dodać lub pobrać z tabeli. Najprostrzy sposób dodania takiego bloku, to zastosowanie pętli FOR i instrukcji SQL - INSERT. W takim przypadku jednak przy każdym przejściu pętli następuje odwołanie do silnika bazy danych, co w znacznym stopniu wydłuża czas wykonania pętli. Mechanizm wiązań masowych pozwala nam na użycie takiej pętli, przy której następuje tylko jedno połączenie z silnikiem bazy danych. Do jednorazowego wykonania zapytań SQL z wykorzystaniem kolekcji używamy pętli FOR ALL Przykład declare Pętla FORALL, a odwoływanie transakcji W przypadku, kiedy w pętli FOR ALL jakaś instrukcja SQL wygeneruje błąd, który nie jest przechwytywany wszystkie instrukcje w transakcji są cofane. Jeśli natomiast błąd przechwycimy poprzednie instrukcje z pętli FORALL (te przed wygenerowanie wyjątku) nie są cofane. Dodatkowo następuje zatrzymanie pętli FORALL. %BULK_ROWCOUNT Jest to atrybut kursora SQL tworzony przez system podczas wykonywania zapytań SQL. Jest on wykorzystywany w pętli FORALL i określa indeks zapytania aktualnie wykonywanego w pętli. Wykorzystywany tylko i wyłącznie w zapytaniach typu UPDATE i DELETE. W przypadku zapytań typu INSERT wartość ta zawsze wynosi 1. Ograniczenia pętli FORALL Pętli FORALL można używać tylko w programach server-side (nie client-side). W zapytaniach INSERT, UPDATE, DELETE musi nastąpić przynajmniej jedno odwołanie do kolekcji.Wszystkie elementy w kolekcji muszą istnieć.Indeks kolekcji w zapytaniu nie może być wyrażeniem.W zapytaniach SQL i kolekcjach złożonych typów nie możemy się odwoływać do kolekcji, tylko bezpośrednio do danego atrybutu elementu kolekcji. {%1} Klauzula BULK_COLLECT Klauzula BULK_COLLECT jest odpowiednikiem pętli FORALL działającym w odwrotnym kierunku. Pozwala ona na pobranie danych do kolekcji z silnika za pomocą jednej transakcji pomiędzy silnikiem, a blokiem PL/SQL. Przykład 1 declare CREATE TYPE POINT AS OBJECT (x NUMBER, y NUMBER); Przykład 3 declare Klauzula ta jest stosowana w wyrażeniach FETCH z opcją BULK COLLECT i pozwala ona ograniczyć liczbę rekordów pobranych z bazy. Jej składnia wygląda następująco: FETCH ... BULK COLLECT INTO ... LIMIT liczba_rekordów Liczba rekordów może być stała, zmienna, lub wyrażeniem. Zwracanie BULK COLLECT Sentencja BULK COLLECT może być również używana w klauzuli RETURNING INTO zapytań typu INSERT, UPDATE lub DELETE. Przykład declare Ograniczenia klauzuli BULK COLLECT Klauzuli te można używać tylko w programach server-side (nie client-side).Zmienne zapisywane przez tę klauzulę muszą być kolekcją.Nie można używać sentencji FETCH w stosunku do kursora z klauzulą BULK COLLECT i kolekcją rekordów. Przykład declare Przykład ... Atrybuty kursorów Systemowy kursor SQL %FOUNDAtrybut ma wartość NULL po otworzeniu kursora, a przed przeczytaniem jego pierwszego wiersza. , TRUE - jeśli ostatnie pobranie wiersza zakończyło się powodzeniem, FALSE - jeśli nie powiodło się ostatnie pobranie wiersza. Przykład: IF Coursor1%FOUND THEN ... %ISOPENZwraca wartość logiczną określającą, czy kursor jest otwarty. %NOTFOUNDLogiczne przeciwieństwo %FOUND. Przykład:
Określa ilość dotychczas pobranych rekordów poprzez klauzulę FETCH. Jeśli dany kursor nie jest otworzony generuje wyjątek INVALID_COURSOR. Przykład: IF Coursor1%ROWCOUNT>10 THEN ... Kursor ten jest tworzony przez system Oracle do obsługi zapytań wygenerowanych w bloku PL/SQL. %FOUND Atrybut ten określa, czy jakiekolwiek rekordy tabeli wzięły udział w ostatnim zapytaniu SQL typu INSERT, UPDATE lub DELETE. Przykład:
Logiczne przeciwieństwo %FOUND. %ROWCOUNT Określa ile rekordów wzięło udział w ostatnim poleceniu typy INSERT, UPDATE lub DELETE. W bloku PL/SQL aby ułatwić obsługę wyjątkowych sytuacji istnieje podblok EXCEPTION definiujący zachowanie w przypadku wszelkiego rodzaju błędów zaistniałych w bloku PL/SQL. Przykład:
Ważną cechą wyjątków jest to, że nie ma potrzeby sprawdzać, czy nastąpił jakikolwiek błąd po każdym zapytaniu SQL. Możemy wykonać kilka zapytań SQL, a następnie w podbloku EXCEPTION określamy zachowanie w przypadku wystąpienia określonych zdarzeń podczas wykonywania wcześniejszych zapytań. W przypadku obsługi kilku błędów poprzez identyczny kod w podbloku EXCEPTION za słowem kluczowym WHEN nazwy wyjątków możemy łączyć operatorem logicznym OR, np: WHEN exception1 OR exception2 OR exception3 THEN ... Do obsługi błędów nieopisanych z nazwy w bloku EXCEPTION używamy znacznika OTHERS, jak to ma miejsce w przykładzie powyżej, co oznacza obsługę pozostałych wyjątków. Deklarowanie własnych wyjątków SQLCODE i SQLERRM Funkcja raise_application_error Aby zadeklarować i obsłużyć własny wyjątek należy w pierwszej kolejności w bloku DECLARE zadeklarować włąsny wyjątek poprzez jego nazwę: nazwa_wyjątku EXCEPTION; W bloku głównym aby wygenerować wyjątek należy użyć polecenia RAISE, gdzie jako parametr podajemy nazwe zadeklarowanego wyjątku. Następnie w bloku EXCEPTION należy zdefiniowany wyjątek obsłużyć poprzez :WHEN nazwa_wyjątku THEN ...Przykład:
Przy obsłudze błędów w bloku PL/SQL mamy do dyspozycji dwie wbudowane funkcje: SQLCODE i SQLERRM. Funkcja SQLCODE zwraca numer błędu związany z wykonanymi ostatnio operacjami SQL. Wartość 0 oznacza operacje zakończoną sukcesem. Funkcja SQLERRM zwraca opis błędu o numerze podanym jako parametr. Ważne jest, że funkcji tych nie można używać bezpośrednio w wyrażenia SQL. Wartość zwracane przez te funkcje należy przypisać do zmiennych i następnie dopiero te zmienne wykorzystać w wyrażeniach SQL. Przykład:
Przykład:
Aby ułatwić komunikację aplikacji z użytkownikiem system Oracle udostępnia nam funkcję raise_application_error o składni: raise_application_error(error_number, error_message[,TRUE | FALSE]); Funkcja ta może być wygenerowana wyłącznie z podprogramu i zwraca do aplikacji błąd użytkownika z jego opisem. Argumenty tej procedury to : error_number : kod błędu z zakresu -20999 do -20000 error_message : Opis błędu (do 2048 znaków) Trzeci parametr jest opcjonalny. Jeśli przyjmie on wartość TRUE, błąd jest umieszczany w kolejce wraz z poprzednimi błędami. W przypadku wartości FALSE (wartość domyślna) błąd zastępuje poprzednie błędy. Przykład:
Podprogramy są normalnymi blokami PL/SQL, które mogą przyjmować argumenty i zwracać wartości. Struktura podprogramu składa się z takich samych podbloków, jak zwykły blok PL/SQL tj., z części deklaracyjnej, części głównej i części obsługi błędów. Składnia tworząca procedurę ma postać
Składnia tworząca funkcję ma postać
IN argument jest przekazywany do podprogramu i tam jest traktowany jak stała, w związku z czym w bloku podprogramu argumentowi nie możemy przypisać wartości. OUT argument w bloku podprogramu jest traktowany jak zwykła zmienna lokalna, do której można przypisywać wartości. Zmiany wewnątrz podprogramu są widoczne w bloku wywałującym podprogram. Argument typu OUT musi być zmienną, nie może to być stała lub wyrażenie. IN OUT jest to połączenie argumentu IN i OUT NOCOPY jest to rozszerzenie argumentu IN, OUT i IN OUT, określające że argument jest przekazywany przez nazwy, a nie przez wartość. Argument ten jest stosowany do dużych obiektów ponieważ zaoszczędza on czas potrzebny na kopiowanie danych pomiędzy blokiem głównym, a blokiem podprogramu i odwrotnie jak to ma miejsce w przypadku przekazywania argumentu przez wartość Wywoływanie podprogramów: W systemie Oracle mamy dwa możliwe rodzaje wywołań podprogramów różniące się sposobem przekazywania argumentów: Notacja pozycyjna Jest to notacja stosowana w większości języków programowania, gdzie pierwszy argument wywołania jest przyporządkowany do pierwszego argumentu definicji. Kolejne argumenty są przyporządkowane analogicznie. Przykład definicja procedury procedure credit (acct_no INTEGER, amount REAL) IS ... wywołanie credit (acct, amnt);W powyższym wywołaniu zmienna acct przypisana jest argumentowi acct_no, zmienna amnt argumentowi amount. Notacja nazewnicza Przy tej notacji nie jest istotna kolejność argumentów w wywołaniu i definicji, ponieważ w wywołaniu jasno określamy jakiemu argumentowi definicji przypisujemy dany argument wywołania. Przykład definicja procedury procedure credit (acct_no INTEGER, amount REAL) IS ... wywołanie credit (acct_no=>acct, amount=>amnt);wywołanie credit (amount=>amnt, acct_no=>acct,); W powyższym przykładzie widzimy iż nie jest istotna przy wywołaniu kolejność argumentów w procedurze i równie dobrze możemy argumenty w wywołaniu podać w odwrotnej kolejności znając wyłącznie nazwy argumentów z definicji procedury. Wartości domyślne argumentów podprogramu Wartości domyślne argumentów podprogramu pozwala nam określić wartość danego argumentu, w przypadku, gdy spodziewamy się, że dana wartość będzie bardzo często podawana w wywołaniu. W takim przypadku, jeśli nie podamy argumentu w wywołaniu, zostanie mu przypisana wartość domyślna. W przypadku, kiedy w definicji podprogramu istnieją równocześnie argumenty z wartościami domyślnymi. jak również i bez nich, najlepiej wtedy stosować notację przez nazwę. Przykład deklaracja procedury
Możliwe wywołania obu argumentom zostanie przypisana wartość domyślna create_dept; pierwszemu argumentowi zostanie przypisana wartość 'NAME' create_dept('NAME'); argumentowi o nazwie new_loc zostanie przypisana wartość 'LOC' create_dept(new_loc=>'LOC'); Pakiet jest obiektem grupującym logicznie połączone typy PL/SQL, podprogramy tworząc w ten sposób jeden moduł do obiektów którego możemy się dowoływać poprzez notację kropkową: x:=nazwa_pakietu.zmienna_zadeklarowana_w_pakiecie; Pakiet skłąda się z dwóch części. W pierwszej części (część specyfikacji) znajdują się deklaracje dostępne dla użytkownika (publiczne). W części BODY następujepełna definicja obiektów prywatnych. Część BODY nie jest wymagana. Przykład: Create Or Replace PACKAGE users_package AS Ważną cechą pakietu jest ich wydajność. Kiedy następuje wywołanie podprogramu z pakietu po raz pierwszy cały pakiet jest ładowany do pamięci. Przy kolejnych odwołaniach do pakietu dane pobierane są z pamięci, a nie z dysku co redukuje częstotliwość operacji dyskowych. Rekord jest złożonym typem danych, na który składają się podstawowe typy danych unikalnie nazwane w ramach tego rekordu. Składnia wyrażenia definiującego typ rekordowy ma postać TYPE nazwa_typu IS RECORD (pole [,pola2,...]); gdzie pole ma postać: nazwa_pola typ_danych [[NOTNULL]{:= | DEFAULT } wartość] Typem danych w tym przypadku może być dowolny typ danych poza REF CURSOR.W przeciwieństwie do typów VARRAY i tabel zagnieżdzonych typ rekordowy nie może byc tworzony i magazynowany w bazie.Przykładowa definicja typu rekordowego :
tmp_value=rekord.pole To samo się dotyczy jeśli wywołyjemy funkcję zwracającą rekord i chcemy pobrać wraz z z wywołaniem funkcji jedno z pól zwracanego rekordu: tmp_value=nazwa_funkcji().pole; Transakcją w bloku PL/SQL możemy nazwać grupę zmian wygenerowanych podczas sesji użytkownika, które to zmiany możemy w pewnych okolicznościach anulować, bądĽ je potwierdzać. Do obsługi transakcji wykorzystujemy dwa wyrażenia : COMMIT, ROLLBACK, SAVEPOINT oraz ROLLBACK TO. COMMIT Wyrażenie to kończy bieżącą transakcje z potwierdzeniem wszystkich zmian dokonanych podczas jej trwania. ROLLBACK Wyrażenie to kończy bieżącą transakcje odwołując wszystkie zmiany dokonane podczas jej trwania. SAVEPOINT Nazywa i określa charakterystyczny punkt w transakcji umożliwiając w ten sposób wycofanie tylko części transakcji za pomocą wyrażenia ROLLBACK TO. ROLLBACK TO Kończy bieżącą transakcje odwołując wszystkie zmiany dokonane podczas transakcji, które nastąpiły po ustawieniu punktu transakcji podanego jako parametr. Przykład: BEGIN {%1} ustawia punkt transakcji o nazwie mypoint. W kolejnej linii wykonuje polecenie INSERT. W podbloku EXCEPTION określamy, że kiedy zostanie wygenerowany wyjątek DUP_VAL_ON_INDEX (tj. przy próbie dodania do tabeli rekordu o wartości kolumny indeksowanej, która już istnieje w tabeli) następuje wycofanie tylko tych instrukcji, które wystąpiły po ustawieniu punktu transakcji mypoint. W tym przypadku będzie to instrukcja INSERT. Wszystkie instrukcje wykonane przed ustawieniem punktu transakcji będą potwierdzone. Autonomiczna transakcja jest neizależną transakcją uruchomioną wewnątrz innej. W swoim działaniu autonomiczna transakcja zawiesza transakcję, która ja wywołała, wykonuje włąsne instrukcje, następnie je potwierdza lub wycofywuje i zwraca kontrolę do transakcji która ja wywołała. Definicja autonomicznej transakcji następuje poprzez dyrektywę PRAGMA AUTONOMOUS_TRANSACTION dla bloku PL/SQL, funkcji lub procedury.Przykład: create procedure proc1(id INTEGER) as Autonomiczną transakcją nie możemy oznaczyć całego pakietu. Należy to zrobić dla poszczególnych podprogramów pakietu ALL_ALL_TABLES : Wszystkie dostępne tablice ALL_CATALOG : Wszystkie dostępne tablice, perspektywy, synonimy, sekwencje ALL_COL_COMMENTS : Wszystkie dostępne komentarze kolumn Typy obiektowepozwalają na łatwe zarządzanie dużymi systemami informatycznymi dając możliwość tworzenia komponentó w sposób modułowy. Obiekty są złożonymi danymizawierającymi inne typy danych (rónież inne typy obiektowe). W ciele obiektu również mogą się znajdować procedury i funkcje. Struktura typu obiektowego skłąda się z dwóch części tak samo jak to ma miejsce w strukturze pakietów. Pierwsza część zawiera listę publicznych pół i metod dostępnych na zewnątrz obiektu. Druga część zawiera implementację tych metod. Metoda jest zwykłym podprogramem (funkcją lub procedurą) poprzedzonym słowem kluczowym MEMBER lub STATIC. Składnia MEMBER oznacza, że metoda jest związana z obiektem stworzonym na podstawie danego typu obiektowego i wywoływana poprzez nazwę tego obiektu, np.: nazwa_obiektu.nazwametody(); Składnia STATIC oznacza, że jest to metoda statyczną z danym typem obiektowym, co oznacza, że żeby wywołać nie ma potrzeby tworzyć obiektu, żeby taką metodę wykonać. Przykład: nazwa_typu_obiektowego.nazwa_metody(); Przykład obiektu: create TYPE Stack as Object( Do pól, jak i do metod obiektu odwołujemy się poprzez notacje kropkową: ale:=person.age; CREATE TABLE nazwa_tabeli OF typ_obiektu; Tabele te można wykorzystywać w standardowych zapytaniach SQL. Poza tym w zapytaniach SQL można wykorzystywać funkcje VALUE zwracającą zmienną obiektową powstałą z obiektu współzależnego podanego jako parametr. DECLARE Porównywanie obiektów WskaĽniki na obiekty Każdy obiekt posiada konstruktor, czyli metodę (w przypadku Oracle metoda ta jest tworzona przez system), którą tworzymy instancje obiektu. Konstruktor ma taką samą nazwę jak typ obiektu. Parametrami konstruktora są zmienne o takiej samej nazwie i typie jak pola obiektu i ułożone w tej samej kolejności. Przykład: Person person:=Person('Jan','Kowalski','01121971'); Porównywanie obiektów tego samego typu odbywa się za pomocą funkcji MAP i ORDRER. W obiekcie może być tylko jedna metoda oznaczona takimi znacznikami MAP lub ORDER. Nigdy nie mogą występować oba te znaczniki naraz. Metody te są wykorzystywane do sortowania obiektów. Metoda ze znacznikiem MAP w swoim działaniu zwraca wartość, która może charakteryzować położenie obiektu na liście względem innych obiektów. Dla przykładu dla obiektu określającego osobę metoda ta może zwrócić jego pole określające pesel osoby. W ten sposób posortujemy obiekty wg numeru pesel. Przykład: Create TYPE Person AS Object ( ORDER MEMBER function compare (other Person) RETURN INTEGER IS Warto tu zauważyć iż mając na względzie kwestie wydajnościowe lepiej jest używać funkcji porównującej typu MAP. Do zaznaczenia, że dana zmienna jest wskaĽnikiem na obiekt lub pole obiektu jest typu wskaĽnikowego stosujemy słowo kluczowe REF przed nazwa zmiennej lub pola. Przykład: -- Zmienna typu wskaĽnikowego Przykład: -- wskaĽnik na obiekt Person Funkcja mająca odwrotne działanie do funkcji DEREF jest funkcja REF zwracająca referencje obiektu. Poniższy przykład dodaje do tabeli referencji obiektów Person kolejne pozycje: INSERT INTO person_refs Select REF(pers) FROM persons pers; Przykład: DECLARE Przykład: Delete from tab where col_ref IS DANGLING; Pakiet DBMS_STATS Pakiet STATSPACK ?ledzenie zapytań - TKPROF Strojenie operacji wejścia-wyjścia Do generowania statystyk dla optymalizatora kosztowego wykorzystuje się procedury pakietu DBMS_STATS. Optymalizator kosztowy na podstawie bieżących statystyk generuje plan wykonania zapytań SQL. Ważne jest, aby nie zaniedbać generowania tych statystyk,aby danie w nich się znajdujące odzwierciedlały aktualna charakterystykę obiektów bazy danych. Ogólnie pakiet DBMS_STATS służy do zarządzania tymi statystykami. Można powiedzieć, że jest to swego rodzaju alternatywa dla polecenia ANALYZE. Do utworzenia tabeli na potrzeby statystyk można użyć procedury CREATE_STAT_TABLE. Parametry tej procedury, to: ownname : nazwa schematu, w którym będą tworzone statystyki stattab : nazwa tabeli tblspace : nazwa przestrzeni tabel. Jeśli parametr ten nie zostanie podany przyjmuje się tu nazwę domyślnej przestrzeni tabel użytkownika. Przykład: DBMS_STATS.CREATE_STAT_TABLE('scott','stats_table'); Do zbierania statystyk należy użyć procedury GATHER_TABLE_STATS pakietu DBMS_STATS. ownname : nazwa właściciela obiektu tabname : nazwa analizowanego obiektu partname : nazwa partycji analizowanego obiektu estimate_percent : procent rekordów, które maja być odczytane podczas oceniania statystyk block_sample : opcja określająca, czy pobieramy cale bloki zamiast pojedynczych rekordów method_opt : dodatkowe opcje zbierania statystyk degree : stopień zrównoleglenia granularity : stopień dokładności podczas zbierania statystyk cascade : parametr określający, czy zostaną zebrane statystyki indeksów stattab : nazwa tabeli, w której zbierane są statystyki statid : identyfikator statystyk statown : nazwa schematu, w którym są przechowywane statystyki. Przykład: DBMS_STATS.GATHER_TABLE_STATS ('scott','tab1',1, stattab=>'stats_table'); STATSPACK jest jednym z kilku mechanizmów Oracle pozwalający na badanie wydajności bazy danych. Można go uznać za pewnego rodzaju udoskonalenie skryptów UTLBSTAT i UTLESTAT. Poza informacjami generującymi przez te skrypty pakiet STATSPACK dysponuje dodatkowymi informacjami. W celu zainstalowania pakietu należy uruchomić skrypt spcreate.sql znajdujący się w standardowym katalogu ORACLE_HOME/rdbms/admin/. Skrypt ten tworzy specjalnego użytkownika PERFSTAT wraz z obiektami potrzebnymi do badania wydajności i za pomocą tego pakietu. Ze względu na bezpieczeństwo zaraz po uruchomieniu skryptu spcreate.sql należy zmienić hasło użytkownika PERFSTAT, które standardowo ustawiono na PERFSTAT. Wszystkie operacje za pomocą tego pakietu dalej powinny być wykonywane przez użytkownika PERFSTAT. Za pomocą tego pakietu generowane są specjalne migawki zawierające informacje wydajnościowe opisujące stan bazy podczas generowania tej migawki. Możemy generować kilka takich migawek, a następnie za pomocą pakietu STATSPACK możemy porównywać różnice pomiędzy migawkami wydajnościowymi zebranymi w rożnym czasie. Dzieje się tak dlatego, że każda migawka ma swój własny identyfikator, który w tabelach wydajnościowych utworzonych skryptem spcreate.sql grupuje informacje związane z jedna migawka. Aby wygenerować migawkę należy uruchomić procedurę SNAP pakietu STATSPACK: execute STATSPACK.SNAP; Wszystkie parametry związane z generowaniem tej migawki można modyfikować procedura MODIFY_STATSPACK_PARAMETER w przypadku, kiedy nie odpowiadają nam domyślne wartości tych parametrów. Parametrami tymi są: i_snap_level : poziom migawki i_ucomment : komentarz do migawki i_executions_th : próg liczby wykonań i_disc_reads_th : próg operacji odczytu z dysku i_parse_cals_th : próg liczby parsowań i_bufer_gets_th : próg operacji odczytu z bufora i_sharable_mem_th : próg wykorzystania wspólnej pamięci i_version_count_th : próg dla liczby wersji instrukcji SQL i_sesion_id : identyfikator sesji, w przypadku, jeśli statystyki dotyczy konkretnej sesji i_modify_parameter : wartość logiczna TRUE lub FALSE określa, czy zmiana parametru ma dotyczyć również kolejnych migawek. Przykład: execute STATSPACK.MODIFY_STATSPACK_PARAMETER( i_snap_level->4, i_modify_parameter->TRUE); Aby wygenerować raport należy uruchomić skrypt spreport.sql. Ważne jest, aby przed uruchomieniem tego skryptu dokonać analizy schematu STATSPACK za pomocą polecenia: execute DBMS_UTILITY_SCHEMA('PERFSTAT','COMPUTE'); W celu usunięcia starych niepotrzebnych danych statystycznych należy uruchomić skrypt sppurge.sql. Innym sposobem usunięcia niepotrzebnych danych znajdujących się w tabelach wydajnościowych jest obcięcie tabel za pomocą skryptu sptrunc.sql. W celu odinstalowania pakietu STATSPACK należy wykonać skrypt spdrop.sql. Często w celu poprawiania wydajności aplikacji analizuje się jakie zapytania SQL są generowane w systemie i jak one obciążają system. Dokonuje się tego poprzez włączenie monitorowania instancji, czy tez konkretnej sesji. ?ledzenie powoduje zbieranie informacji o liczbie parsowań, wykonań i pobrań. Ze śledzeniem zapytań SQL związane są następujące parametry instancji: TIMED_STATISTICS : jeśli ten parametr ustawimy na TRUE w danych statystycznych znajda się statystyki czasowe. MAX_DUMP_FILE_SIZE : parametr określający maksymalna długość pliku śladu. USER_DUMP_DEST : parametr określający położenie plików śladu. Domyślnie jest położenie zrzutów systemu operacyjnego. {%1} Włączenie śledzenia bieżącej sesji odbywa się poprzez polecenie:ALTER SESSION SET SQL_TRACE = TRUE; lub poprzez procedurę DBMS_SESSION.SET_SQL_TRACE. Aby włączyć śledzenie sesji innej niż bieżąca należy znać informacje o tej zewnętrznej sesji. Mając nazwę użytkownika tej zewnętrznej sesji informacje te możemy pobrać za pomocą polecenia: SELECT sid, serial#, osuser FROM v$session WHERE osuser = 'scott'; Teraz mając te dane możemy włączyć lub wyłączyć śledzenie dla tej sesji za pomocą polecenia: EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION( sid, serial#, TRUE ); Dwa pierwsze parametry są informacjami pobranymi z poprzedniego zapytania, trzeci parametr przyjmuje wartości TRUE lub FALSE i określa, czy włączamy,czy wyłączamy tą sesje. W przypadku włączania śledzenia na poziomie całej instancji należy parametr konfiguracyjny SQL_TRACE ustawić na TRUE. Wartością domyślną jest tutaj FALSE. Włączenie śledzenia dla całej instancji powoduje dość duże obciążenie systemu dlatego raczej zaleca się stosowanie śledzenia sesji, a śledzenie całej instancji stosować w wyjątkowych sytuacjach. Do zapisu danych ze śledzenia w postaci zrozumiałej dla człowieka służy polecenie TKPROF. Składnia tego polecenia ma postać: TKPROF infile outfile [parametry] gdzie: infile : plik wygenerowany w trakcie śledzenia outfile : nazwa pliku wynikowego zawierającego raport śledzenia. {%1} count : liczba wywołań procedur OCI (procedury pozwalające na uzyskanie dostępu do bazy) CPU : czas CPU wykonania w sekundach elapsed : czas oczekiwania na rezultat zapytania w sekundach disk : liczba fizycznych odczytów z dysku query : liczba odczytów bloków utrzymanych w trybie spójnym (z wykorzystaniem segmentu wycofania) current : liczba odczytów bloków utrzymanych w trybie bieżącym (bez wykorzystania segmentu wycofania) rows : liczba przetworzonych rekordów podczas pobierania lub wykonywania. W rozdziale tym zastanowimy się w jaki sposób możemy przyspieszyć operacje wejścia - wyjścia. Warto tu zauważyć iż operacji tych może być bardzo dużo, trwają one zdecydowanie dłużej niż operacje na pamięci i nawet drobne zmiany polepszające wydajność w tym zakresie może skutkować dużą poprawą wydajności. Parametr DB_BLOCK_SIZE Bloki o nie standardowej wielkości Migracja rekordów Łańcuchy bloków Dynamiczne rozszerzanie Parametry PCTFREE oraz PCTUSED Parametr ten określa wielkość bloku danych. Jest on bardzo ważny dla strojenia bazy, ponieważ właściwe jego ustawienie pozwoli ograniczyć liczbę niechcianych zjawisk takich jak migracja oraz łańcuchowanie,czy tez przyspieszyć operacje odczytu rekordów bazy danych. Warto tu zauważyć, ze parametr ten jest wykorzystywany podczas tworzenia bazy danych i nie można go zmienić już w trakcie pracy bazy danych. Wielkość tego parametru zależy od wielkości rekordów znajdujących się w bazie danych oraz sposobu dostępu do tych danych. Jeśli mamy do czynienia z systemem DSS, gdzie często mamy do czynienia z przeszukiwaniami całej tabeli wskazane jest, aby wielkość ta była duża, ponieważ wtedy jednorazowo będziemy odczytywali wiekszą porcję danych. Dla systemów OLTP z kolei zaleca się stosowanie mniejszych bloków danych. Większe bloki danych nie przyniosą ta żadnych korzyści. Wręcz przeciwnie mogą powodować pogorszenie wydajności, ponieważ jeśli blok danych będzie większy niż rekord w tabeli system niepotrzebnie będzie odczytywał te dodatkowe dane i niepotrzebnie te dodatkowe dane będą buforowane niepotrzebnie zajmując miejsce w buforze. Zbyt mała wartość tego parametru może spowodować zwiększenie operacji wejścia - wyjścia, ze względu na pojawienie się takich zjawisk jak migracja i łańcuchowanie. Mogą tu się pojawić problemy z zarządzaniem pamięcią ponieważ więcej czasu zajmuje wypełnienie mniejszych bloków w miarę optymalny sposób. Z kolei zbyt duża wartość tego parametru w odniesieniu do wielkości rekordów w bazie może spowodować zwieszenie operacji wejścia - wyjścia, ze względu na to, że bufor zmieści mniej takich bloków i czeskiej będziemy mieli do czynienia ze zwalnianiem miejsca dla nowych bloków w buforze. Zwalnianie to polega na zapisie najstarszych danych z bufora na dysk. Operacje dyskowe są bardzo czasochłonne, więc wydajność może spaść tu dość znacznie. Dodatkowa jedna operacja wejścia - wyjścia powoduje przetwarzanie większej ilości danych. Byłoby to zaleta, gdy w tej porcji danych znajda się tylko i wyłącznie interesujące nas danych. Jeśli parametr będzie zbyt duży podczas jednej operacji wejścia - wyjścia w odczytanej porcji danych znajda się dodatkowo dane nas nieinteresujące, które niepotrzebnie zostały odczytane. UWAGA: Należy zwrócić uwagę, aby wielkość bloku bazodanowego była wielokrotnością bloku systemu operacyjnego. Zastrzeżenie dotyczy bazy danych Oracle, mówiące o tym, ze wielkość bloku danych ustawiane jest podczas tworzenia bazy i nie można go zmienić już w trakcie pracy bazy danych dotyczy bazy danych w wersji 8i i niższych. W wersji 9i wprowadzono pojęcie bloków nie standardowej wielkości. Istnieje tu możliwość tworzenia przestrzeni tabel o nie standardowej wielkości bloków danych. W takiej sytuacji wszystkie obiekty umieszczone w tej przestrzeni tabel będą miały bloki danych o nie standardowej wielkości. Przykład: CREATE TABLESPACE table_space_dss Migracja rekordów występuje podczas operacji UPDATE. Jeśli aktualizacja rekordu powoduje iż nowy rekord nie mieści się w swoim bloku system musi odnaleĽć nowy blok danych i tam przenieść zaktualizowany rekord. są to operacje, które dodatkowo obciążają procesor i dysk i wskazane jest, aby operacje takie się nie pojawiały lub było ich względnie mało. Łańcuchy bloków występują kiedy rekord bazodanowy znajduje się w więcej niż jednym bloku danych. W takim przypadku odczytanie takiego rekordu zajmuje więcej niż jedna operacje wejścia - wyjścia. Aby sprawdzić, czy w tabeli znajdują się rekordy łańcuchowe wykorzystujemy polecenie ANALIZE. Przed wykonaniem tego polecenia należy stworzyć odpowiednią tabelę, w której znajdą się wyniki polecenia ANALIZE. Dokonujemy tego poleceniem: CREATE TABLE chained_rows ( ANALYZE TABLE tab1 LIST CHAINED ROWS; Po tej operacji tabela chained_rows zawiera listę rekordów łańcuchowych tabeli tab1. Wskazane jest aby tabela ta była pusta bądĽ zawierała niewielką liczbę rekordów. Jeśli tych rekordów jest wiele wskazane jest, aby wyeksportować tabele, jeszcze raz przeanalizować wielkość bloku danych oraz utworzyć bazę od nowa. Warto tu zauważyć, ze są takie tabele, w których z założenia spodziewamy się, że będą zawierały rekordy łańcuchowe. Przykładem będą tu rekordy z kolumnami typu LONG lub długimi kolumnami CHAR lub VARCHAR2. Dynamiczne rozszerzanie jest specjalnym mechanizmem polegającym na automatycznym rozszerzeniu segmentu w przypadku kiedy jego wielkość osiągnie maksimum i należy dodać dodatkowe ekstenty. Operacja ta generuje dodatkowe operacje wejścia - wyjścia oraz dodatkowe systemowe zapytania SQL zwane zapytaniami rekursywnymi, co wpływa na wydajność. Aby odnaleĽć rekursywne zapytania w systemie należy odczytać dynamiczną perspektywę v$sysstat za pomocą polecenia: SELECT name, value FROM v$sysstat WHERE name = 'recursive calls'; Rozmiar ekstentu powinien być ustalony w taki sposób, aby ograniczyć liczbę dynamicznych alokacji celem zwiększenia wydajności aplikacji. Innym rozwiązaniem jest monitorowanie wielkości ekstentów za pomocą perspektywy DBA_EXTENTS i kiedy zauważymy, że zbliża się on do wielkości, kiedy jest wykonywane dynamiczne rozszerzanie, możemy tego dokonać ręcznie, kiedy system będzie mniej obciążony, np. w godzinach nocnych. Bardzo często do zmniejszenia liczby operacji wejścia - wyjścia i tym samym do polepszenia wydajności systemu stosuje się parametry PCTFREE oraz PCTUSED klauzuli STORAGE w poleceniach tworzących obiekty bazodanowe. Parametry te służą do zarządzania blokami danych przez system. Balansując pomiędzy rożnymi wartościami tych parametrów możemy w odniesieniu do danego obiektu kłaść większy nacisk na wydajność odwołań do tego obiektu, czy tez na ilość miejsca zajmowanego przez ten obiekt. Parametr PCTUSED podawany w procentach określa ze do bloku bazodanowego beda dodawane nowe rekordy jesli poziom zajetosci spadnie ponizej tej wartosci. Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości. Parametr PCTFREE okreslany w procentach okresla ze do bloku bazodanowego będą dodawane nowe rekordy dopóki poziom wolnego miejsca w tym bloku będzie powyżej tej wartości. Parametry PCTFREE oraz PCTUSED inaczej są nazywane wskaĽnikami niskiej i wysokiej wody. Suma tych parametrów nie może być większa niż 100, ale nic nie stoi na przeszkodzie, aby suma ta była mniejsza od 100. Warto tu zauważyć, że im suma ta bardziej zbliża się do 100 lub różnica parametrów PCTFREE oraz PCTUSED zbliża się do 0, tym bardziej obciążany jest system, natomiast przestrzeń dyskowa jest bardziej efektywnie wykorzystywane. Aby zachować umiar pomiędzy wydajnością systemu, a zajetością przestrzeni dyskowej wskazane jest aby pomiędzy tymi parametrami zachować różnice jednego rekordu. Jeżeli na danej tabeli dość często są wykonywane operacje UPDATE zwiększające wielkość rekordu wskazane jest aby parametr PCTFREE ustawić na wysoka wartość (np. 25), a PCTUSED na niską wartość (np. 40). W takiej sytuacji zawsze jest pozostawiona odpowiednia ilość miejsc w rekordzie na ewentualne operacje UPDATE zwiększające objętość tych rekordów. W przypadku dużej ilości operacji INSERT i operacji UPDATE, które w małym stopniu zwiększają objętość rekordów wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na wartość względnie neutralną (np. 50). W sytuacji, kiedy najważniejsza jest wydajność systemu, a optymalne wykorzystanie przestrzeni dyskowej jest mało istotne wskazane jest aby obydwa te parametry ustawić na niską wartość (np. 30). Taka konfiguracja tych parametrów powoduje dość istotne marnowanie przestrzeni dyskowej, natomiast w znacznym stopniu ograniczamy tu takie zjawiska jak łańcuchowanie, czy też migracja. W sytuacji, kiedy najważniejsza jest optymalne wykorzystanie przestrzeni dyskowej, a wydajność systemu jest mniej istotne wskazane jest aby parametr PCTFREE ustawić na niską wartość (np. 5), a PCTUSED na dość dużą wartość (np. 90). W takiej sytuacji dbamy o maksymalne wykorzystywanie przestrzeni dyskowej, natomiast musimy zdać sobie sprawę z tego, że będą tu występowały takie zjawiska jak łańcuchowanie czy też migracja. Wyrażenie SET TRANSACTION pozwala nam określić tryb bieżącej transakcji. Najczęściej jest ono wykorzystywane do ustawienia transakcji tylko do odczytu. Wyrażenie to musi być pierwszym w transakcji i może w niej występować tylko raz. Transakcje typu READ ONLY są stosoweane, kiedy wykorzystywane w nich są instrukcje Select INTO, OPEN, FETCH, CLOSE, LOCK TABLE, COMMIT i ROLLBACK, czyli instrukcje, które nie wprowadzają zmian do tabel w bazie. "Poznaj Oracle 8" : David Austin ISBN 83-7158-153-X Mikom 1999 "Oracle8 Server - Księga eksperta" : Joe Greene ISBN 83-7197-099-4 Helion 2000 "Oracle łatwiejszy niż przypuszczasz wydanie II" : Jadwiga Gnybek ISBN 83-7197-350-0 Helion 2000 "PL/SQL User's Guide and Reference" "SQL Reference" "Application Developer's Guide - Fundamentals" |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
linux
eclipse
java
php