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:
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:
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:
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 1
from 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
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.
