XIV Konferencja PLOUG Szczyrk Październik 2008

Oracle 11g – nowe cechy w strojeniu wydajności i tworzeniu aplikacji dla bazy

danych Kamil Stawiarski

Royal Software Sp. z o.o.

Abstrakt. Baza danych Oracle 11g dostarcza nie tylko wielu nowych funkcjonalności lecz także rozwiązuje problemy, które do tej pory stanowiły zmartwienie deweloperów. Dzięki nowym opcjom obsługi triggerów, tabelom read-only, ulepszonej obsłudze wyrażeń regularnych oraz kilku kosmetycznym poprawkom zaimplementowanym w nowej wersji bazy danych deweloperzy odczują poprawę komfortu programowania. Wygodne są nowe możliwości optymalizacji wydajności kodu, testowania aplikacji, „niewidzialne” indeksy, ulepszona natywna kompilacja PL/SQL, czy bufor Result Cache. Informacja o autorze. Kamil Stawiarski zajmuje stanowisko dyrektora technologicznego w firmie Royal Software. Od kilku lat zajmu-je się bazą Oracle, zarówno od strony deweloperskiej jak również administratorskiej. Dzięki zdobytej wiedzy oraz doświadczeniom prowadzi konsultacje oraz wykłady na szkoleniach autoryzowanych oraz konsultacjach dla Partnerów Oracle.

Baza danych Oracle 11g dostarcza wielu nowych funkcjonalności oraz rozwiązań problemów,

które do tej pory stanowiły zmorę deweloperów. Dzięki nowym opcjom obsługi triggerów, tabelom

read-only, ulepszoną obsługą wyrażeń regularnych oraz kilku kosmetycznym poprawkom

zaimplementowanym w nowej wersji bazy danych odczujemy poprawę komfortu programowania.

Wygodą stały się też nowe możliwości optymalizacji wydajności kodu, oraz testowania

aplikacji. „Niewidzialne” indexy, ulepszona natywna kompilacja PL/SQL, czy bufor Result Cache

powinny pozytywnie wpłynąć na nasz codzienny kontakt z bazą Oracle.

Przyjrzyjmy się zatem kilku ciekawszym właściwościom najnowszej bazy danych firmy Oracle.

SQL*Plus„Najprostszym i najprzyjemniejszym” narzędziem do obsługi bazy danych Oracle zawsze był

SQL*Plus – narzędzie tekstowe o przyjaźnie migającym kursorku. Szczerze mówiąc myślałem, że

w Oracle 11g ktoś wpadnie na pomysł, żeby dostarczać to narzędzie z jakże prostą, acz przyjemną

opcją historii poleceń, czy dopełniania składni, jednak moje nadzieje okazały się płonne. Zamiast

tego otrzymaliśmy produkt wzbogacony o kilka zacnych funkcjonalności, które w ostatecznym

rozrachunku mogą być nam wielce przydatne.

● set errorlogging on – całkiem pożyteczna opcja, która upraszcza wykrywanie błędów w

skryptach; wiele razy się zdarzało, że pisałem skrypt administracyjny, który powstawał w

celu wykonania określonych zadań migracyjnych, aplikacyjnych lub po prostu dla symulacji

obciążenia testowej bazy danych. Wiele razy zdarzyło się, że skrypt generował serię błędów,

które łatwo było przeoczyć, jeśli nie napisało się do nich własnej obsługi. Dzięki temu

nowemu poleceniu, wszystkie błędy powstałe w wyniku poleceń wydawanych w SQL*Plus

zostają zapisane do tabeli o nazwie „SPERRORLOG”. Znajdziemy tam informacje o

użytkowniku, poleceniu oraz komunikacie błędu zwróconego przez Oracle. Tabela jest

zwykłą tabelą, podlegającą normalnym zasadom tranzakcyjności – naturalnie oznacza to, że

jeżeli po serii powstałych błędów nasza sesja ulegnie brutalnemu zamknięciu, nie

zobaczymy cennych informacji, mogących naprowadzić nas na stosowny trop. Nie mniej

jednak jest to opcja, która może w szczególnych przypadkach znacznie ułatwić proces

poszukiwania błędów w skryptach.

Przyk ad:ł

SQL> set errorlogging on

SQL> desc sperrorlog Nazwa Wartosc NULL? Typ ----------------------------- -------- -------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB

SQL> l 1 select username, timestamp, statement, message 2 from sperrorlog 3* where rownum=1SQL> /

US TIMESTAMP STATEMENT MESSAGE-- ------------------------------ -------------------- ------------------------------------------------------------------------------------------HR 08/09/20 13:49:52,000000 delete employees ORA-12081: operacja aktualizacji nie jest dozwolona dla

PL/SQL Podstawowym językiem tworzenia oprogramowania na bazie danych Oracle od zawsze był

PL/SQL. Oracle 11g dostarcza kilka ciekawych rozwiązań, którymi warto się przyjrzeć rozważając

migracje dotychczasowych aplikacji.

● trigger following clause – wiele razy w swojej karierze zmuszony byłem do aktualizacji

bieżących rozwiązań opartych na triggerach ze względu na ewolucję procesów

biznesowych aplikacji. Pociągało to za sobą konieczność modyfikacji kodu konkretnego

triggera dla zachowania sekwencyjności wykonywanych operacji. Z punktu widzenia

dbania o kod i utrzymywania go w należytym porządku oraz zgodnie ze wszelkimi dobrymi

praktykami programowania należałoby wówczas stworzyć kolejny trigger przeznaczony do

obsługi nowej funkcjonalności. W poprzednich wersjach bazy tego typu rozwiązanie było

wykluczone ze względu na nieprzewidywalność kolejności uruchamiania dwóch takich

samych triggerów. Baza danych Oracle 11g pozwala nam na wyspecyfikowanie klauzuli, w

której możemy określić kolejność uruchamiania stworzonych triggerów.

Przyk ad:łCREATE OR REPLACETRIGGER TRIG_GEN_PASSBEFORE INSERT ON EMPLOYEESFOR EACH ROW BEGIN :new.default_pass := to_char(:new.hire_date, 'YYYYMMDY') || upper(substr(:new.first_name, 1, 2)) || initcap(substr(:new.last_name, 1, 2));END;/

CREATE OR REPLACE

TRIGGER TRIG_HASH_PASSBEFORE INSERT ON EMPLOYEESFOR EACH ROW follows trig_gen_passBEGIN :new.default_pass := DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING=>:new.default_pass);END;/

W pierwszym triggerze zostało wygenerowane standardowe hasło nowo

dodawanego pracownika. Trigger nr 2 natomiast zawiera prosty kod hashujący

wygenerowane wcześniej hasło. W powyższym przykładzie moglibyśmy dodać

między tymi dwoma triggerami jeszcze jeden (w środku), którego funkcjonalność

polegałaby na przesłaniu e-mailem wygenerowanego hasła. Dzięki takiemu rozbiciu

kodu mogę łatwiej zarządzać poszczególnymi etapami obsługi dodania nowego

pracownika. Mogę również wyłączać poszczególne etapy („alter trigger disable

clause”), dla celów deweloperskich, lub testowych.

● Compound trigger – jest to nowość w Oracle 11g, dla której bez problemu znajdzie

zastosowanie każdy deweloper PL/SQL. Wiele razy, aby zaimplementować jakąś

funkcjonalność była potrzeba stworzenia kilku triggerów – „before statement”, „after each

row” i „after statement”. W dodatku, każda z faz zależała od poprzedniej w bezpośredni

sposób. Jeżeli był wymagany przepływ danych między triggerami, musieliśmy

kombinować ze zmiennymi pakietowymi lub innymi sposobami zachowywania spójności

między poszczególnymi wywołaniami. Obecnie możemy stworzyć jeden trigger, w którym

da się wyszczególnić określone sekcje: przed wykonaniem polecenia, przed wykonaniem

poszczególnych wierszy, po wykonaniu poszczególnych wierszy, po wykonaniu polecenia

no i sekcja deklaracyjna triggera. Tego typu triggery mogą działać oczywiście tylko przy

poleceniach DML. Na pierwszy rzut oka widać jednak ich przydatność – konstrukcja

pozwala poprawić wydajność implementacji określonych funkcjonalności, jak również

usprawnia zarządzanie i obsługę kodu.

Przyk ad:łCREATE OR REPLACE TRIGGER Check_Employee_Salary_Raise FOR UPDATE OF Salary ON EmployeesCOMPOUND TRIGGER Ten_Percent CONSTANT NUMBER := 0.1; TYPE Salaries_t IS TABLE OF Employees.Salary%TYPE; Avg_Salaries Salaries_t; TYPE Department_IDs_t IS TABLE OF Employees.Department_ID%TYPE; Department_IDs Department_IDs_t;

TYPE Department_Salaries_t IS TABLE OF Employees.Salary%TYPE INDEX BY VARCHAR2(80);

Department_Avg_Salaries Department_Salaries_t;

BEFORE STATEMENT IS BEGIN SELECT AVG(e.Salary), NVL(e.Department_ID, -1) BULK COLLECT INTO Avg_Salaries, Department_IDs FROM Employees e GROUP BY e.Department_ID; FOR j IN 1..Department_IDs.COUNT() LOOP Department_Avg_Salaries(Department_IDs(j)) := Avg_Salaries(j); END LOOP; END BEFORE STATEMENT;

AFTER EACH ROW IS BEGIN IF :NEW.Salary - :Old.Salary > Ten_Percent*Department_Avg_Salaries(:NEW.Department_ID) THEN Raise_Application_Error(-20000, 'Raise too big'); END IF; END AFTER EACH ROW;END Check_Employee_Salary_Raise;

Powyższy przykład został zaczerpnięty z dokumentacji do bazy Oracle11g. Widać

na nim użycie compound trigger w celu uniknięcia mutowania tabeli – nie możemy

dać podwyżki pracownikowi, jeśli przekracza ona 10% wartości średnich zarobków

w departamencie, w którym pracuje.

● Function Result Cache – funkcjonalność ta jest przydatna, gdy odwołujemy się często do

funkcji, która zwraca zazwyczaj ten sam wynik, dla tych samych parametrów wejściowych.

Możemy określić, żeby wynik funkcji został zapisany w buforze, dzięki temu każde kolejne

wywołanie funkcji z określonymi parametrami będzie pobierało wynik z bufora. Parametr

„relies_on” określa tabele, z których korzysta funkcja w swoim ciele – jeżeli zawartość

tabeli ulegnie zmianie, zbuforowana zawartość ulegnie inwalidacji. Dokładniejszy opis

działania bufora „Result Cache” zostanie opisana w późniejszej części tego dokumentu.

Jeżeli chcemy użyć w funkcji opcji „function result cache”, musimy trzymać się

określonych restrykcji:

○ Funkcja nie może być wywoływana z prawami wywołującego

○ Funkcja nie może być użyta w bloku anonimowym

○ Funkcja nie może posiadać parametrów OUT

○ Funkcja nie może przyjmować parametrów złożonych

○ Funkcja nie może zwracać złożonego typu danych

Przyk ad:ł

CREATE OR REPLACEFUNCTION F_GET_STATUS(p_dept_name departments.department_name%TYPE) RETURN VARCHAR2

result_cache relies_on(employees, departments)as v_sal employees.salary%TYPE; v_ret VARCHAR2(3);BEGIN select avg(salary) into v_sal from employees e, departments d where e.department_id= d.department_id and d.department_name= p_dept_name; if v_sal > 8600 then v_ret := 'YES'; elsif v_sal is null then v_ret := 'NOT'; raise no_data_found; else v_ret := 'NO'; end if; return v_ret; exception when no_data_found then dbms_output.put_line('Nie ma takiego departmentu jak ' || p_dept_name); return v_ret; END F_GET_STATUS;/

SQL> select f_get_status('IT') from dual; 2 F_GET_STATUS('IT')--------------------------NO

SQL> select status 2 from v$result_cache_objects 3 where name like '%F_GET_STATUS%' 4 and TYPE='Result' 5 /

STATUS---------Published

SQL> update departments 2 set department_name=initcap(department_name);

27 wierszy zostalo zmodyfikowanych.

SQL> commit;

Zatwierdzanie zostalo ukonczone.

SQL> select status 2 from v$result_cache_objects 3 where name like '%F_GET_STATUS%' 4 and TYPE='Result' 5 /

STATUS---------Invalid

Jak widać w powyższym przykładzie, zaraz po odwołaniu się do funkcji

otrzymaliśmy opublikowany wynik w naszym buforze, z którego będzie korzystało

każde następne wywołanie funkcji z takim samym parametrem wejściowym. Jednak

po modyfikacji tabeli, na której oparta była funkcja (wskazane przez relies_on), nasz

zbuforowany wynik uległ inwalidacji – dzięki temu mamy pewność otrzymywania

wiarygodnych danych.

Zupełnie inaczej ma się sprawa, gdy nie użyjemy klauzuli relies_on – w takim

przypadku możemy otrzymać nieprawdziwy wynik, ponieważ Oracle nie będzie

miał informacji o zależności zbuforowanego wyniku funkcji od zawartości tabel, na

których opierają się nasze wyliczenia.

● Wyrażenia regularne – wyrażenia regularne stanowią bardzo potężne narzędzie w rękach

programisty. Szczególnie istotne jest ich zastosowanie podczas wyrafinowanych opcji

przeszukiwania ciągów znakowych lub walidacji danych. Obsługa wyrażań regularnych

została dodana do bazy Oracle w wersji 10g. W wersji 11g została ona rozszerzona o nowe

funkcjonalności – do funkcji REGEXP_INSTR została dodana możliwość znalezienia N-

tego sub wyrażenia. Znajdziemy też zupełnie nową funkcję REGEXP_COUNT, która

oczywiście jak nie trudno się domyśleć, pozwala na zliczenie trafień na podstawie zadanego

wzorca.

● „Continue” oraz „simple_integer” - jest to jedna z opcji, która została dodana do składni

PL/SQL po wielu latach... Zdecydowana większość języków programowania posiada tę

klauzulę, która pozawala na kontynuowanie pętli nadrzędnej przy spełnieniu określonych

warunków.

Przyk ad:łCREATE OR REPLACEPROCEDURE P_CONT_EXAMPLE AS v_sum simple_integer := 0;

BEGIN <<BEFORE_LOOP>> for i in (select department_id, department_name from departments) loop v_sum := 0; for j in (select salary from employees where department_id=i.department_id) loop v_sum := v_sum + j.salary; if v_sum>19000 then dbms_output.put_line(i.department_name || ' ' || v_sum); continue BEFORE_LOOP; end if; end loop; end loop;

END P_CONT_EXAMPLE;

Przy okazji trywialnego przykładu użycia klauzuli continue zdecydowałem się

pokazać również nowy typ danych dodany do języka PL/SQL. Simple_integer jest

pod typem, typu pls_integer – ma dokładnie taki sam zakres jak pls_integer, różnica

polega jednak na tym, że gdy dojdziemy do końca zakresu typu simple_integer

nastąpi „przekręcenie” wartości zmiennej do najmniejszej wartości zakresu.

Simple_integer jest również zadeklarowany jako not null, wymaga więc

zainicjalizowania wartością, już na etapie deklaracji w naszym programie.

● Dynamiczny SQL – Oracle ulepszył obsługę dynamicznego SQL zarówno za pomocą

pakietu DBMS_SQL, jak również natywnego dynamicznego SQL (execute immediate). Oto

kilka rozszerzeń dodanych do obsługi tej przydatnej funkcjonalności:

○ Natywny dynamiczny SQL może przyjmować teraz 32kb tekstu.

○ DBMS_SQL.PARSE zostało przeciążone dla używania CLOB.

○ DBMS_SQL wspiera teraz abstrakcyjne typy danych, takie jak kolekcje.

○ DBMS_SQL pozwala używać mechanizmu BULK BINDING używając typów

zdefiniowanych przez użytkownika.

● Natywna kompilacja – możliwość natywnej kompilacji procedur PL/SQL, była moim

zdaniem zawsze jednym z największych plusów bazy Oracle. Możliwość przechowywania

procedur w postaci bibliotek języka C dawała przecież zawsze nadzieję, na znacznie

szybsze wykonanie kodu, zwłaszcza opartego na dużej liczbie obliczeń. Jednak

wielokrotnie korzystanie z tego mechanizmu było ograniczone na wielu środowiskach.

Wynikało to z faktu, że do tej pory skompilowany kod PL/SQL do postaci biblioteki języka

C przechowywany był na serwerze, a co za tym szło wymagany był kompilator C dostępny

dla właściciela bazy danych. Takie rozwiązanie z punktu widzenia bezpieczeństwa było

niedopuszczalne na pewnych środowiskach. Tak więc funkcjonalność bardzo często

pozostawała niewykorzystana.

Oracle 11g ulepszył obsługę natywnej kompilacji PL/SQL – począwszy od tej wersji

bazy danych biblioteki C nie są przetrzymywane na serwerze lecz bezpośrednio w bazie

danych, co likwiduje konieczność dostępu do kompilatora C.

Przyk ad:łSQL> alter session set plsql_code_type=NATIVE;

Sesja zostala zmieniona.

SQL> alter function f_get_status compile;

Funkcja zostala zmieniona.

SQL> select plsql_code_type 2 from DBA_PLSQL_OBJECT_SETTINGS 3 where name='F_GET_STATUS';

PLSQL_CODE_TYPE--------------------------------------------------------------------------------NATIVE

Jak widać na powyższym przykładzie, aby skompilować procedurę w trybie

NATIVE, wystarczy ustawić jeden parametr sesji, oraz wykonać kompilację.

Powyższa procedura, nie powoduje powstania żadnych plików na serwerze.

Wszystko pozostaje wewnątrz bazy danych.

● Kosmetyka – w PL/SQL zobaczymy również kilka poprawek kosmetycznych, które może

nie wpływają jakoś znacznie na poprawę wydajności lub funkcjonalności systemu, ale na

pewno są miłym ukłonem w stronę użytkownika. Tego typu poprawkami jest np. możliwość

używania notacji mieszanej podczas wywoływania funkcji w wyrażeniu SQL, czy też opcja

zastosowania przypisania „wprost” wartości sekwencji.

SQL SQL jest językiem dostępu do danych, którego znajomość jest podstawą umiejętnością, zarówno

dla dewelopera, jak i administratora. Oracle w najnowszej wersji swojej bazy danych dostarcza nam

kilku ulepszeń, które zostaną opisane poniżej.

● Read-only tables – komenda „alter table” dostarcza nam teraz możliwości uczynienia tabeli

dostępną tylko do odczytu dla całej bazy danych (w tym również dla właściciela tabeli).

Pojawiła się również nowa kolumna w tabeli słownika danych

[DBA/ALL/USER]_TABLES, która określa stan tabeli – READ_ONLY, której właściwymi

wartościami są YES, lub NO.

Przyk ad:ł

SQL> alter table employees read only;

Tabela zostala zmieniona.

SQL> select read_only 2 from user_tables 3 where table_name='EMPLOYEES';

REA---YES

SQL> delete employees;delete employees *BLAD w linii 1:ORA-12081: operacja aktualizacji nie jest dozwolona dla tabeli "HR"."EMPLOYEES"

● Niewidzialne indeksy – czy kiedykolwiek zdarzyło się Wam stworzyć indeks w celach

testowych, tylko po to żeby okazało się, że niektóre plany wykonania „zwariowały” a

wydajność wcale się nie poprawiła? A może usunęliście jakiś indeks, bo przeszkadzał

podczas aktualizacji tabel a potem musieliście go odtwarzać, co trwało wiekami i

przysparzało o nerwicę? W swej najnowszej wersji bazy danych, Oracle przedstawia nową

funkcjonalność, która po zastosowaniu sprawia, że wskazany indeks staje się niewidoczny

dla optymalizatora.

Przyk ad:ł

SQL> select first_name, last_name, department_name 2 from employees e, departments d 3 where e.department_id=d.department_id;

106 wierszy zostalo wybranych.

Plan wykonywania----------------------------------------------------------Plan hash value: 1343509718

--------------------------------------------------------------------------------------------| Id | Operation | Name --------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | 1 | MERGE JOIN | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS || 3 | INDEX FULL SCAN | DEPT_ID_PK ||* 4 | SORT JOIN| 5 | TABLE ACCESS FULL

SQL> alter index DEPT_ID_PK invisible;

Indeks zostal zmieniony.

SQL> select first_name, last_name, department_name 2 from employees e, departments d 3 where e.department_id=d.department_id;

106 wierszy zostalo wybranych.

Plan wykonywania----------------------------------------------------------Plan hash value: 2052257371

----------------------------------------------------------------------------------| Id | Operation | Name ----------------------------------------------------------------------------------| 0 | SELECT STATEMENT |* 1 | HASH JOIN| 2 | TABLE ACCESS FULL | DEPARTMENTS | | 3 | TABLE ACCESS FULL | EMPLOYEES |

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

Można nadpisać ustawienia niewidzialnych indeksów ustawiając parametr

optimizer_use_invisible_indexes na wartość TRUE – można skorzystać z

ustawienia parametru na poziomie sesji, lub systemu co spowoduje, że pomimo

włączenia „niewidzialności”, optymalizator będzie brał pod uwagę wszystkie

indeksy.

● SQL Query Result Cache – mechanizm ten jest przydatny, gdy mamy duże ilość danych,

które przeważnie pozostają statyczne, a do których odwołujemy się często w zapytaniach

(głównie funkcje agregujące). Dzięki tej funkcjonalności wyniki zapytań przechowywane są

w obszarze współdzielonym (SHARED POOL), który począwszy od wersji 11g dzieli się na

trzy główne obszary – poza LIBRARY CACHE oraz DICTIONARY CACHE występuje

RESULT CACHE, w którym właśnie przechowywane są zbuforowane wyniki naszych

zapytań (w praktyce mamy więc kolejne miejsce, w którym mogą występować latche).

Parametr result_cache_max_size może być ustawiony w celu manualnego określenia

rozmiaru SQL Query Result Cache. Standardowo parametr ten ustawiany jest dynamicznie

podczas startu instancji. Jeżeli parametr zostanie ustawiony na 0, wyłączona zostanie

funkcjonalność buforowania wyników zapytań. (PL/SQL Function Result Cache omawiany

wcześniej, również korzysta z tego obszaru).

Rozważając wielkość bufora, Oracle będzie brał pod uwagę takie parametry jak

memory_target (0,25% dla Result Cache), sga_target (0,50% dla Result Cache),

shared_pool_size (1% dla Result Cache) – Oracle nie powinien pozwolić na ustawienie

parametru result_cache_max_size powyżej 75% shared pool.

Kolejnymi istotnymi parametrami, które należy wziąć pod uwagę chcąc korzystać z

omawianej funkcjonalności są: result_cache_mode oraz result_cache_max_result.

Pierwszy z parametrów może przyjmować wartości MANUAL (ustawienie standardowe) –

aby skorzystać z buforowania, należy użyć w zapytaniu hinta, który wymusi takie działanie,

lub FORCE – Oracle będzie korzystał dla każdego zapytania z omawianego mechanizmu,

jeżeli to tylko możliwe. Parametr ten może być modyfikowany zarówno na poziomie sesji

jak i systemu.

Drugi z wymienionych parametrów określa maksymalną procentową wielkość, jaką może

zająć jeden zbuforowany wynik w result cache.

Aby skorzystać z SQL Query Result Cache przy standardowych ustawieniach bazy danych

należy do zapytania dodać hint: result_cache. Kiedy parametr result_cache_mode ma

ustawioną wartość FORCE, sytuacja wygląda dokładnie odwrotnie – każdy SQL będzie

korzystał z bufora i aby to uniemożliwić, należy zastosować hint: no_result_cache.

Typowym rezultatem poprawnego działania mechanizmu jest zredukowanie liczby

odczytów z bufora danych do zera. Należy jednak pamiętać, że w przypadku częstych

operacji DML, omamiana funkcjonalność może doprowadzić do de optymalizacji, ponieważ

będziemy mieli do czynienia z faktem częstych inwalidacji obiektów w result cache.

Przyk ad:ł

SQL> select /*+ result_cache */ max(salary) 2 from employees;

Plan wykonywania----------------------------------------------------------Plan hash value: 265868411

----------------------------------------------------------------------------------------------------------| Id | Operation | Name ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT || 1 | RESULT CACHE | f4cg36a3s8cm34yrg5nwtxug1h | 2 | SORT AGGREGATE || 3 | INDEX FULL SCAN (MIN/MAX)| SALARY_IDX |

SQL> select name, status, row_count 2 from v$result_cache_objects 3 where cache_id='f4cg36a3s8cm34yrg5nwtxug1h';

NAME STATUS ROW_COUNT------------------------------------------------------------ --------- ----------select /*+ result_cache */ max(salary) Published 1from employees

Jak widać w powyższym przykładzie, na podstawie informacji dostarczonej

przez plan wykonania zapytania, możemy odpytać perspektywę

v$result_cache_objects (przedstawianą przy okazji omawiania PL/SQL Function

Result Cache), aby sprawdzić statystyki dotyczące naszych zbuforowanych

wartości.

Ograniczenia SQL Query Result Cache są następujące:

● Brak obsługi zapytań do słownika danych.

● Brak obsługi zapytań do tabel tymczasowych

● Nie buforowane są wartości sekwencji

● Z bufora nie mogą korzystań niedeterministyczne funkcje

● Funkcjonalność nie obsługuje zapytań zwierających następujące funkcje

SQL: current_date, current_timestamp, userenv/sys_context,

sys_guid, sysdate, sys_timestamp

● Client-Side SQL Query Result Cache – buforowanie wyników zapytań w SGA, nie

zredukuje ilości przepływu danych przez sieć. Jedną z tajemnic strojenia wydajności baz

danych jest zdanie: „Najszybszy dostęp do bazy danych to brak dostępu do bazy

danych”. Postępując zgodnie z tą zasadą powstały takie produkty, jak Oracle Coherence.

Z myślą o tej zasadzie powstał też mechanizm, pozwalający na buforowanie wyników

zapytań po stronie klienta. Jak na razie funkcjonalność dostępna jest wyłącznie dla

sterowników opartych na OCI, takich jak: PHP, JDBC-OCI, OCCI, ODP.Net,

Pro*C/C++, Pro*COBOL oraz ODBC.

PerformanceOdkąd baza Oracle opiera swoją potęgę wydajnościową na optymalizatorze kosztowym,

poprawne zbieranie i przechowywanie statystyk stało się jednym z głównych obszarów tematu

strojenia bazy danych. Przyjrzyjmy się niektórym nowym cechom statystyk, które dostarcza nam

baza Oracle 11g.

● „Wisz ce” statystyki –ą baza Oracle 11g daje nam opcję wyboru, czy zebrane statystyki

zostaną opublikowane po zebraniu, czy otrzymają status „wiszącej” statystyki.

Żeby określić, czy statystyki zostaną opublikowane bezpośrednio po zebraniu, czy też

otrzymają status „wiszących”, możemy użyć funkcji GET_PREFS('PUBLISH') z pakietu

DBMS_STATS. Jeżeli funkcja zwróci wartość TRUE, będzie to oznaczało, że statystyki

zostaną opublikowane bezpośrednio po zebraniu. Aby zmienić ten parametr możemy

skorzystać z funkcji SET_SCHEMA_PREFS (lub SET_TABLE_PREFS gdy chcemy

wskazać konkretną tabelę), również znajdującej się w pakiecie DBMS_STATS.

Gdy statystyki zostaną zebrane jako „wiszące”, możemy podejrzeć wygenerowane wartości

w odpowiednich tabelach słownika danych: USER_TAB_PENDING_STATISTICS,

USER_COL_PENDING_STATISTICS, USER_IND_PENDING_STATISTICS.

Następnie w zależności od tego, czy uzyskamy zadowalający nas efekt, możemy statystyki

opublikować (DBMS_STATS.PUBLISH_PENDING_STATS), bądź skasować

(DBMS_STATS_DELETE_PEDING_STATS).

Przyk ad:ł

SQL> select dbms_stats.get_prefs('PUBLISH','HR','EMPLOYEES2')

2 from dual;

DBMS_STATS.GET_PREFS('PUBLISH','HR','EMPLOYEES2')--------------------------------------------------------------------------------TRUE

SQL> begin 2 dbms_stats.set_table_prefs('HR','EMPLOYEES2','PUBLISH','FALSE'); 3 end; 4 /

Procedura PL/SQL zostala zakonczona pomyslnie.

SQL> delete employees2;

110 wierszy zostalo usunietych.

SQL> commit;

Zatwierdzanie zostalo ukonczone.

SQL> begin 2 dbms_stats.gather_table_stats('HR','EMPLOYEES2'); 3 end; 4 /

Procedura PL/SQL zostala zakonczona pomyslnie.

SQL> select 'OPUBPLIKOWANE :' || num_rows as pub 2 from user_tables 3 where table_name='EMPLOYEES2';

PUB-------------------------------------------------------OPUBPLIKOWANE :110

SQL> select 'WISZACE :' || num_rows as pend 2 from user_tab_pending_stats 3 where table_name='EMPLOYEES2';

PEND-------------------------------------------------WISZACE :0

SQL> begin 2 dbms_stats.publish_pending_stats('HR','EMPLOYEES2'); 3 end; 4 /

Procedura PL/SQL zostala zakonczona pomyslnie.

SQL> select 'OPUBPLIKOWANE :' || num_rows as pend 2 from user_tables 3 where table_name='EMPLOYEES2';

PEND-------------------------------------------------------OPUBPLIKOWANE :0

Statystyki, które nie są opublikowane, nie mają wpływu na zmianę planu

wykonania zapytania, wyjątkiem jest sytuacja, gdy wartość parametru

optimizer_use_pending_statistics jest ustawiona na TRUE.

● Statystyki wielokolumnowe i oparte na funkcjach – Oracle 11g przedstawia nowe

możliwości w generowaniu histogramów. Począwszy od tej wersji bazy danych mamy

możliwość zbierania statystyk opartych na grupie kolumn oraz na funkcjach. Dzięki tej

możliwości otwierają się zupełnie nowe możliwości wpływania na optymalizator kosztowy

w celu wygenerowania najbardziej właściwego planu wykonania zapytania.

Aby skorzystać z możliwości generowania statystyk wielokolumnowych należy uprzednio

stworzyć grupę kolumn, do której będziemy się odwoływać.

Przyk ad:ł

SQL> declare 2 v_name varchar2(30); 3 begin 4 v_name := dbms_stats.create_extended_stats(null,'EMPLOYEES','(DEPARTMENT_ID,JOB_ID)'); 5 end; 6 /

Procedura PL/SQL zostala zakonczona pomyslnie.

SQL> begin 2 dbms_stats.gather_table_stats(null,'EMPLOYEES', method_opt=>'for all columns size auto for columns (DEPARTMENT_ID,JOB_ID)'); 3 end; 4 /

Procedura PL/SQL zostala zakonczona pomyslnie.

SQL> begin 2 dbms_stats.gather_table_stats(null,'EMPLOYEES', method_opt=>'for all columns size auto for columns (lower(FIRST_NAME))');3 end;4 /

SQL> select extension_name, extension 2 from user_stat_extensions 3 where table_name='EMPLOYEES';

EXTENSION_NAME EXTENSION------------------------------ ------------------------------------------------------------SYS_STUCCK0PNI_XUVBAGRNRRUF29S ("DEPARTMENT_ID","JOB_ID")SYS_STUKTFUBS35V1$LRO53L2Z$F8R (LOWER("FIRST_NAME"))

Jakkolwiek funkcjonalność wydaje się być ciekawa oraz obiecująca,

przeprowadzone testy wskazują, że mechanizm jest jeszcze niestabilny a zachowanie

optymalizatora trudne do przewidzenia.

● Bind-Aware Peeking – do tej pory używanie zmiennych wiązanych mogło nieść ze sobą

ryzyko zmniejszenia wydajności zapytań, gdy w kolumnach znajdowały się dane o

nierównomiernym rozkładzie, nadające się do zakładania histogramów. Oracle 11g

proponuje nam rozwiązanie mające uwolnić nas od problemu wybierania, kiedy używać

zmiennych wiązanych a kiedy jednak lepiej jest użyć literałów.

Dzięki nowemu mechanizmowi Bind-Aware Peeking, SQL przy pierwszym wykonaniu

przejdzie normalne, twarde parsowanie. Następne wykonanie spowoduje znalezienie

zbuforowanego planu wykonania zapytania, unikając twardego parsowania. Po wykonaniu

SQL, jego statystyki dotyczące wykonania zostaną porównane ze statystykami z

poprzednich wykonań. Jeżeli Oracle zauważy, że porównanie wypada na niekorzyść,

oznaczy nasz kursor jako bind-aware. Dzięki temu przy kolejnych wykonaniach będzie

sprawdzana selektywność predykatów i, jeśli trzeba, wygenerowany nowy plan wykonania

zapytania. Efektem takiego działania jest powstanie kilku różnych planów wykonania

zapytania dla tego samego kursora, zawierającego zmienne wiązane. Odpowiedni plan

będzie wybierany na podstawie wartości przypisanych do zmiennych wiązanych.