Zarządzanie obiektami bazy danych Oracle...

Zarządzanie obiektami bazy danych Oracle 11g

Strona: 1 | Administracja bazą Oracle 11g | Studia Podyplomowe SGGW | Laboratorium nr 7 i 8

Zarządzanie obiektami bazy danych Oracle11g

Wstęp Kontynuując ćwiczenia dotyczące obiektów w bazie Oracle, na dzisiejszych zajęciach zajmiemy sie:

Indeksami

Technologią Flashback

Indeksy Indeksy to struktury, które zakłada się na kolumny w tabelach w celu:

przyspieszenia wyszukiwania, a więc operacji SELECT

implementacji więzów integralności typu PRIMARY KEY oraz UNIQUE.

Każde zapytanie do bazy danych w którym uwzględniony jest warunek związany z wartością kolumny

wymaga sprawdzenia tego warunku dla każdego wiersza. Poniższe polecenie:

SELECT * FROM EMPLOYEES WHERE SALARY > 1000 AND SALARY < 50000;

filtruje wiersze po kolumnie SALARY. W przypadku braku indeksu na kolumnie SALARY, baza danych

musi przejrzeć wszystkie wiersze w tabeli (bez względu na ich ilość) aby przygotować wynik. Takie pełne

przejrzenie tabeli jest nazywane FULL TABLE SCAN i jest bardzo nieefektywne. Jeśli tabela będzie miała

miliony rekordów to każde tego typu zapytanie będzie zajmować dużo czasu.

Typowy indeks jest strukturą drzewiastą w postaci B-drzewa (B-Tree) w której znacznie szybciej można

odnaleźć wartości zgodne z warunkiem w klauzuli WHERE.

Innym rodzajem indeksu jest indeks bitmapowy (Bitmap index), który jest używany na kolumnach z niską

kardynalnością wartości, a więc z ich dużą powtarzalnością. Przyjmuje się, że unikalnych wartości w

kolumnie w całej tabeli nie powinno być więcej niż 100. Im mniej unikalnych wartości tym lepsze

wykorzystanie indeksu i większy wpływ na wydajność. Dobrym przykładem zastosowania indeksu

bitmapowego są kolumny przyjmujące z założenie tylko kilka wartości np. Płeć: [K, M].

Oto przykłady tworzenia różnych rodzajów indeksów:

CREATE INDEX EMP_SAL_IDX ON EMPLOYEES (SALARY) TABLESPACE INDEX_TS;

Normalny indeks w postaci B-drzewa na tabeli EMPLOYEES i kolumnie SALARY utworzony w przestrzeni

tabel INDEX_TS

CREATE INDEX EMP_NAME_IDX ON EMPLOYEES (FIRST_NAME, LAST_NAME);



Indeks na tabeli EMPLOYEES założony jednocześnie na dwóch kolumnach

CREATE BITMAP INDEX EMP_SEX_IDX ON EMPLOYEES (SEX);

Indeks bitmapowy założony na kolumnie SEX (Płeć)

Informację o istniejących indeksach można znaleźć w perspektywach

USER_INDEXES – dla zwykłego użytkownika, pokazujące tylko indeksy użytkownika

DBA_INDEXES – dla administratora, pokazujące wszystkie indeksy

Analizowanie i przebudowywanie indeksów Indeksy przebudowuje się od czasu do czasu ponieważ ciągłe usuwanie, aktualizacje i nowe rekordy w

tabeli mogą spowodować, że struktura indeksu będzie nieoptymalna. Może nawet zdarzyć się taka

sytuacja, że indeks będzie zajmował więcej miejsca niż sama tabela. W takiej sytuacji przebudowanie

indeksu wydaje się być najlepszym rozwiązaniem.

Decyzję o przebudowie indeksu można oprzeć o statystyki, które zbiera się poleceniem

ANALYZE INDEX. Są dwie opcje do analizowania indeksu:

Standardowa analiza indeksu poleceniem ANALYZE INDEX COMPUTE STATISTICS, która wpisuje

wyniki analizy w perspektywę DBA_INDEXES

Analiza wewnętrznej struktury i wyliczenie statystyk poleceniem ANALYZE INDEX VALIDATE

STRUCTURE, który generuje statystyki do perspektywy INDEX_STATS. Uwaga! Perspektywa

INDEX_STATS zawiera maksymalnie jeden wiersz wpisywany w wyniku wykonania polecenia

ANALYZE INDEX VALIDATE STRUCTURE. Ponowne wykonanie tego polecenia nadpisuje

poprzedni wpis w INDEX_STATS.

Przykłady:

1. Analiza indeksu i sprawdzenie parametrów w DBA_INDEXES lub USER_INDEXES

ANALYZE INDEX EMP_SAL_IDX COMPUTE STATISTICS;

ANALYZE INDEX EMP_SAL_IDX ESTIMATE STATISTICS;

SELECT

BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY,

AVG_DATA_BLOCKS_PER_KEY

FROM USER_INDEXES

WHERE INDEX_NAME=’EMP_SAL_IDX’;

2. Analiza indeksu i jego wewnętrznej struktury. Podgląd wyników w INDEX_STATS

ANALYZE INDEX EMP_SAL_IDX VALIDATE STRUCTURE;

SELECT HEIGHT, BLOCKS, USED_SPACE, DEL_LF_ROWS FROM INDEX_STATS;

Przyjmuje się zasadę, że jeśli indeks ma wysokość większą niż 4 oraz usuniętych liści jest ponad 10%

(niektóre źródła podają 20%) to należy przebudować indeks.



ALTER INDEX EMP_SAL REBUILD TABLESPACE INDEX_TS;

Powyższe polecenie przebudowuje indeks (usuwa i zakłada nowy) i umieszcza w przestrzeni tabel

INDEX_TS;

Aby usunąć indeks należy posłużyć się poleceniem DROP INDEX np.

DROP INDEX EMP_SAL_IDX;

Informacje o indeksach można znaleźć w perspektywie systemowej ALL_INDEXES lub DBA_INDEXES.

Poniższe zapytanie wyświetla wszystkie indeksy założone na tabeli EMPLOYEES:

SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, BLEVEL FROM ALL_INDEXES

WHERE TABLE_NAME=’EMPLOYEES’;

Technologia Flashback Technologia Flashback w bazie danych Oracle 11g jest jednym z ciekawszych rozwiązań, dość unikalnym

pośród baz relacyjnych dostępnych na rynku. Służy do “zaglądania w przeszłość” czyli odczytywania

danych z przeszłości, a więc nie tych aktualnych, które są w danej chwili zapisane w tabelach.

Dodatkowo, służy również do odtwarzania usuniętych tabel polecenie DROP z kosza (czyli tych

usuniętych poleceniem DROP ale bez słowa kluczowego PURGE). Flashback korzysta z przestrzeni tabel

UNDO w celu odczytania starych danych zmienionych przez transakcje.

Aby zacząć używać technologii Flashback należy sprawdzić i ewentualnie ustawić kilka parametrów

instancji bazy danych oraz przestrzeni tabel UNDO:

Parametry inicjalizacyjne instancji:

o UNDO_MANAGEMENT = AUTO

o UNDO_TABLESPACE=’UNDOTBS1’

o UNDO_RETENTION=900 – wartość wyrażona w sekundach

Parametr przestrzeni tabel UNDO: RETENTION = GUARANTEE

Parametry inicjalizacyjne instancji sprawdza się poleceniem:

SHOW PARAMETER UNDO_MANAGEMENT

SHOW PARAMETER UNDO_TABLESPACE

SHOW PARAMETER UNDO_RETENTION

Parametry inicjalizacyjne zmienia się poleceniem:

ALTER SYSTEM SET UNDO_RETENTION = 1000;

Parametr UNDO_RETENTION wyrażony jest w sekundach oznacza gwarantowany czas operacji flashback

czyli maksymalny czas do jakiego można cofnąć się w czasie.



Dodatkowo, należy również sprawdzić czy przestrzeń tabel UNDOTBS1 rzeczywiście gwarantuje czas

podany w parametrze UNDO_RETENTION:

SELECT RETENTION FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=’UNDOTBS1’

Jeśli wynik powyższego zapytania jest NOGUARANTEE to należy to zmienić poleceniem:

ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

Odtwarzanie tabel z kosza po usunięciu Zanim spróbujemy odtwarzać usunięte tabele z kosza należy sprawdzić czy kosz jest w bazie włączony:

SHOW PARAMETER RECYCLEBIN

Jeśli jest ON to znaczy, że jest włączony. Jeśli nie, to trzeba go włączyć i zrestartować instancję:

ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;

STARTUP;

Po usunięciu tabeli poleceniem DROP:

DROP TABLE EMPLOYEES;

Można ją przywrócić poleceniem:

FLASHBACK TABLE EMPLOYEES TO BEFORE DROP;

Można przejrzeć usunięte obiekty które są w koszu w perspektywie RECYCLEBIN:

SELECT OBJECT_NAME, ORIGINAL_NAME, OPERATION, DROPTIME

FROM RECYCLEBIN;

lub po prostu

SHOW RECYCLEBIN;

Zapytania o dane w przeszłości Aby dowiedzieć się jakie były dane w przeszłości używamy również polecenia SELECT tyle, że z klauzulą

“AS OF TIMESTAMP” lub “AS OF SCN”:

1. SELECT * FROM EMPLOYEES AS OF TIMESTAMP SYSDATE-1/1440 WHERE …;

2. SELECT * FROM EMPLOYEES AS OF SCN 23423423 WHERE …;

Pierwsze z powyższych zapytań wybiera z tabeli EMPLOYEES wiersze sprzed 1 minuty. SYSDATE -1

oznacza dzień wczorajszy, a SYSDATE – 1/1440 oznacza 1440 część dnia czyli dokładnie 1 minutę.



Drugie zapytanie wybiera wiersze z konkretnego punktu w czasie określonego wartością SCN czyli

System Change Number. Każda zatwierdzona transakcja zwiększa SCN tak, żeby baza dokładnie

wiedziała kiedy i co działo się w transakcjach, a także czy pliki bazy są we właściwym stanie podczas

startu instancji. Aktualny SCN można wyciągnąć:

za pomocą funkcji PL/SQL - DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER

z kolumny CURRENT_SCN w perspektywie V$DATABASE

konwertując czas na SCN za pomocą funkcji TIMESTAMP_TO_SCN. Ta metoda zwraca tylko

przybliżoną wartość.

Oto przykłady:

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL

SELECT CURRENT_SCN FROM V$DATABASE;

Zapytania typu flashback umożliwiają również wyświetlenie zmian w zakresie parametru SCN lub czasu

(TIMESTAMP) np:

SELECT VERSIONS_XID, SALARY FROM EMPLOYEES

VERSIONS BETWEEN TIMESTAMP <T1> AND <T2>

WHERE EMPLOYEE_ID = 200;

SELECT VERSIONS_XID, SALARY FROM EMPLOYEES

VERSIONS BETWEEN SCN <SCN1> AND <SCN2>

WHERE EMPLOYEE_ID = 200;



Ćwiczenia

Ćwiczenie 1 – Indeksy 1. Stworzyć tabelę EMPLOYEES uruchamiając skrypy employees_ddl.sql

2. Sprawdzić jakie indeksy istnieją na tabeli HR.EMPLOYEES

3. Które indeksy są:

a. unikalne

b. typu B-TREE (NORMAL)

c. typu BITMAP

4. Założyć indeksy:

a. o nazwie EMP_SAL_IDX na kolumnie SALARY w tabeli EMPLOYEES

b. o nazwie EMP_NAME_IDX na dwóch kolumnach jednocześnie FIRST_NAME oraz

LAST_NAME

5. Uruchomić skrypt employees_dml.sql ,który załaduje dane do tabeli EMPLOYEES

6. Przeanalizować index EMP_SAL_IDX

a. z opcją COMPUTE STATISTICS

b. z opcją VALIDATE STRUCTURE

7. Przebudować index EMP_SAL_IDX i ponownie go przeanalizować. Czy jego rozmiar się zmniejszył

(czyli ilość bloków jaką zajmuje)?

Ćwiczenie 2 – Flashback 1. Sprawdzić czy kosz (RECYCLEBIN) jest włączony w bazie. Jeśli nie jest to go włączyć.

2. Usunąć tabelę EMPLOYEES.

3. Sprawdzić w koszu pod jaką nazwą dostępna jest usunięta tabela EMPLOYEES.

4. Wykonać zapytanie na obiekcie EMPLOYEES będącym w koszu. Czy da się odczytać wiersze?

5. Przywrócić tabelę EMPLOYEES z kosza. Czy się udało? Czy indeksy i więzy integralności też się

odtworzyły?

6. Sprawdzić aktualny SCN i TIMESTAMP

7. Dokonać aktualizacji wierszy i zatwierdzić transakcję

8. Wyświetlić wartości wierszy sprzed transakcji wykonanej w punkcie 7 technologią flashback.

9. Wyświetlić różne wersje zmodyfikowanych wierszy

10. Przywrócić stare wartości do tabeli sprzed aktualizacji w punkcie 7.



Rozwiązania

Ćwiczenie 1 – Indeksy 1. Sprawdzić jakie indeksy istnieją na tabeli HR.EMPLOYEES

a. SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME =

'EMPLOYEES';

2. Które indeksy są:

a. unikalne

b. typu B-TREE (NORMAL)

c. typu BITMAP

d. SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM USER_INDEXES

WHERE TABLE_NAME = 'EMPLOYEES'

3. Założyć indeksy:

a. o nazwie EMP_SAL_IDX na kolumnie SALARY w tabeli EMPLOYEES

i. CREATE INDEX EMP_SAL_IDX ON EMPLOYEES(SALARY); d

b. o nazwie EMP_NAME_IDX na dwóch kolumnach jednocześnie FIRST_NAME oraz

LAST_NAME

i. CREATE INDEX EMP_NAME_IDX ON EMPLOYEES (FIRST_NAME,

LAST_NAME);

4. Uruchomić skrypt employees.sql który załaduje dane do tabeli EMPLOYEES

a. @/mnt/employees.sql

5. Przeanalizować index EMP_SAL_IDX

a. z opcją COMPUTE STATISTICS

i. ANALYZE INDEX EMP_SAL_IDX COMPUTE STATISTICS;

ii. SELECT BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS,

AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY FROM

USER_INDEXES WHERE INDEX_NAME= 'EMP_SAL_IDX';

b. z opcją VALIDATE STRUCTURE

i. ANALYZE INDEX EMP_SAL_IDX VALIDATE STRUCTURE;

ii. SELECT HEIGHT, BLOCKS, USED_SPACE, DEL_LF_ROWS FROM

INDEX_STATS;

6. Przebudować index EMP_SAL_IDX i ponownie go przeanalizować. Czy jego rozmiar się zmniejszył

(czyli ilość bloków jaką zajmuje)?

a. ALTER INDEX EMP_SAL_IDX REBUILD;

b. ANALYZE INDEX EMP_SAL_IDX COMPUTE STATISTICS;

c. SELECT BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS,

AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY FROM

USER_INDEXES WHERE INDEX_NAME= 'EMP_SAL_IDX';

d. ANALYZE INDEX EMP_SAL_IDX VALIDATE STRUCTURE;

e. SELECT HEIGHT, BLOCKS, USED_SPACE, DEL_LF_ROWS FROM

INDEX_STATS;.



Ćwiczenie 2 – Flashback 1. Sprawdzić czy kosz (RECYCLEBIN) jest włączony w bazie. Jeśli nie jest to go włączyć.

a. SHOW PARAMETER RECYCLEBIN

b. Jeśli wynik jest OFF to trzeba zmienić na ON

c. ALTER SYSTEM SET RECYCLEBIN = ON SCOPE=SPFILE;

2. Usunąć tabelę EMPLOYEES.

a. DROP TABLE EMPLOYEES;

3. Sprawdzić w koszu pod jaką nazwą dostępna jest usunięta tabela EMPLOYEES.

a. SHOW RECYCLEBIN

4. Wykonać zapytanie na obiekcie EMPLOYEES będącym w koszu. Czy da się odczytać wiersze?

a. SELECT * FROM “BIN$vydhwMSBPizgQKjAZTh/tA==$0”;

5. Przywrócić tabelę EMPLOYEES z kosza. Czy się udało? Czy indeksy i więzy integralności też się

odtworzyły?

a. FLASHBACK TABLE EMPLOYEES TO BEFORE DROP;

b. SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME =

'EMPLOYEES';

c. SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE

TABLE_NAME= 'EMPLOYEES'

d. Indeksy i więzy integralności są przywracane, ale z nazwami które miały w koszu.

6. Sprawdzić aktualny SCN i TIMESTAMP

A. CONN / AS SYSDBA

B. SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;

c. SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

7. Dokonać aktualizacji wierszy i zatwierdzić transakcję

a. UPDATE EMPLOYEES SET SALARY = SALARY +1000 WHERE

EMPLOYEE_ID=1000;

8. Wyświetlić wartości wierszy sprzed transakcji wykonanej w punkcie 7 technologią flashback.

a. SELECT * FROM EMPLOYEES AS OF SCN 4124141 WHERE EMPLOYEE_ID

= 1000;

9. Wyświetlić różne wersje zmodyfikowanych wierszy

A. SELECT VERSIONS_XID, SALARY FROM EMPLOYEES VERSIONS BETWEEN

TIMESTAMP <T1> AND <T2> WHERE EMPLOYEE_ID = 1000;

10. Przywrócić stare wartości do tabeli sprzed aktualizacji w punkcie 7.

a. UPDATE EMPLOYEES SET SALARY = (SELECT SALARY FROM EMPLOYEES

AS OF SCN 4124141 WHERE EMPLOYEE_ID=1000) WHERE

EMPLOYEE_ID=1000;

b. COMMIT;

Comment [m1]: Nazwa pod jaką odnajdujemy usuniętą tabelę będzie inna w każdej instancji bazy

Comment [m2]: SCN należy pobrać z punktu 6 b

Comment [m3]: Pod <T1> i <T2>

Zarządzanie obiektami bazy danych Oracle...

Documents

Transcript of Zarządzanie obiektami bazy danych Oracle...