Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

W mojej pracy magisterskiej wykorzustuję bazę Oracle 11g. Podczas testów wydajnościowych okazało się, że operacje wykonywanie na bazie zajmują więcej czasu niż się tego spodziewałem. Sytuacja jest na tyle specyficzna i dobrze zdefiniowana, że wydaje mi się iż można polepszyć tą wydajność. Proszę o pomoc gdyż skończyły mi się pomysły (ograniczone przez moją wiedzę).

Wykonywane są liczne, bardzo podobne do siebie selecty. Chciałbym zoptymalizować bazę właśnie pod tylko ten typ zapytań. Długość wykonywania jakichkolwiek innych ma tutaj znaczenie marginalne. Zapytanie obejmuje tylko dwie kolumny jednej tabeli. (Właściwie zmaterializowanego widoku. Widok nie jest przebudowany podczas pracy systemu więc to chyba nie ma znaczenia.)

DESCRIBE MVIEW_TRANSACTIONS;

TID                            NOT NULL NUMBER(19)                                                                                                                                                                                    

ITEM                           NOT NULL NUMBER(19)  

na kolumnie ITEM jest założony index bitmapowy:

CREATE BITMAP INDEX MVIEW_TRANSACTIONS_INDEX_ITEM ON MVIEW_TRANSACTIONS;

Pary (tid, item) są unikalne. Zapytania wyglądają w ten sposób:

SELECT count(*)

FROM

(SELECT tid

FROM MVIEW_TRANSACTIONS

WHERE item IN (:1 ,:2 ,:3 ,:4) group by tid having count(*)=:5 ) subtable;

Ostatni parametr jest liczbą elementów w zbiorze. Czyli na przykład:

SELECT count(*)

FROM

(SELECT tid

FROM MVIEW_TRANSACTIONS

WHERE item IN (70 ,2573 ,2430 ,2424) group by tid having count(*)=4 ) subtable;

testowane są zbiory o różnej długości, czyli na przykład też:

SELECT count(*)

FROM

(SELECT tid

FROM MVIEW_TRANSACTIONS

WHERE item IN (71 ,2570) group by tid having count(*)=2 ) subtable;

Plan pierwszego z podanych przykładowych zapytań:

| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                        

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

|   0 | SELECT STATEMENT         |                        |     1 |       |  3439   (3)| 00:00:42 |                                                                                                                                                                                                        

|   1 |  SORT AGGREGATE          |                        |     1 |       |            |          |                                                                                                                                                                                                          

|   2 |   VIEW                   |                        |  3713 |       |  3439   (3)| 00:00:42 |                                                                                                                                                                                                        

|*  3 |    FILTER                |                        |       |       |            |          |                                                                                                                                                                                                          

|   4 |     HASH GROUP BY        |                        |  3713 | 33417 |  3439   (3)| 00:00:42 |                                                                                                                                                                                                        

|*  5 |      MAT_VIEW ACCESS FULL| MVIEW_TRANSACTIONS     |   969K|  8520K|  3411   (3)| 00:00:41 |                                                                                                                                                                                                         

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

                                                                                                                                                                                                                                                                                                           

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                        

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

                                                                                                                                                                                                                                                                                                           

   3 - filter(COUNT(*)=4)                                                                                                                                                                                                                                                                                    

   5 - filter("ITEM"=70 OR "ITEM"=2424 OR "ITEM"=2430 OR "ITEM"=2573)   

Tutaj szczegółowe wyjaśnienie planu z EM: http://zacheusz.eu/various/plan.html (wykonany na większym zbiorze)

W czasie wykonywania tych zapytań nie potrzebuję transakcji. Baza jest uruchomiona w trybie tylko do odczytu (co nie jest konieczne):

startup mount;

alter database open read only;

W ostateczności można nawet założyć, że długość zbioru nie przekracza 10 a w kolumnie ITEM występuje 100 wartości.
Będę niezmiernie wdzięczny za wszelkie profesjonalne rady i pomysły.Zacheusz Siedlecki edytował(a) ten post dnia 16.12.09 o godzinie 09:50

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Krzysztof Pułapa:

Marcin Przepiórowski:
A zebrales statyski po utworzeniu obiektow i zaladowaniu danych ?

Nie :)

no ja też nie ;)

Adam Michalski:
No właśnie, ile masz różnych wartości w kolumnie Item wśród tych 100?

Wierszy jest ponad 50 mln ale różnych wartości jest właśnie 100. Stąd pomysł na index bitmapowy narzuca się samoistnie. Bardzo dziękuję za pomoc. Zaraz przetestuję pomysł Krzysztofa z index organized table.Zacheusz Siedlecki edytował(a) ten post dnia 16.12.09 o godzinie 14:14

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Krzysztof Pułapa:

Zacheusz Siedlecki:
W czasie wykonywania tych zapytań nie potrzebuję transakcji.

A skoro są tylko dwie kolumny i do tego para jest unikalna to spróbuj z index organized table.

Ale ... item daj jako pierwsze w indeksie.

Bardzo dziękuję :D
Czas operacji na bazie zmniejszył się 2 krotnie w stosunku do zmaterializowanego widoku z indeksem bitmapowym i 3 krotnie w stosunku do tabeli z indeksem bitmapowym (btw nie mam pojęcia dlaczego widok był szybszy od tabeli). Na własne oczy się przekonałem, że rzeczywiście istnieją "magicy od Oracle" o których się czasem mówi ;)
Teraz po stworzeniu tabeli z

PRIMARY KEY ("ITEM", "TID") ENABLE

   ) ORGANIZATION

plan zapytania wygląda tak:

| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                 

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

|   0 | SELECT STATEMENT      |                    |     1 |       |    13  (31)| 00:00:01 |                                                                                                                                                                                                                 

|   1 |  SORT AGGREGATE       |                    |     1 |       |            |          |                                                                                                                                                                                                                 

|   2 |   VIEW                |                    |   144K|       |    13  (31)| 00:00:01 |                                                                                                                                                                                                                 

|*  3 |    FILTER             |                    |       |       |            |          |                                                                                                                                                                                                                 

|   4 |     HASH GROUP BY     |                    |   144K|  3670K|    13  (31)| 00:00:01 |                                                                                                                                                                                                                 

|   5 |      INLIST ITERATOR  |                    |       |       |            |          |                                                                                                                                                                                                                 

|*  6 |       INDEX RANGE SCAN| TRANSACTIONS_G2_PK |   144K|  3670K|     9   (0)| 00:00:01 |                                                                                                                                                                                                                 

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

Statystyki
Indeks bitmapowy wybrałem po przeczytaniu tego artykułu: http://www.oracle.com/technology/pub/articles/sharma_i... Jest tam nawet zapytanie podobne w pewnym sensie do mojego zawierające

where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000)

(co sprowadza się do AND)
Zbiór wartości jest ograniczony więc wszystko wskazywało na to, że indeks bitmapowy będzie najlepszym rozwiązaniem. Sugerowałem się również wskazówkami stąd: http://www.dba-oracle.com/oracle_tips_bitmapped_indexe...
Zapytanie służy do obliczania wsparcia zbioru. Oracle Data Mining udostępnia mechanizmy wyznaczające zbiory częste (elementem takiego algorytmu jest obliczanie wsparcia). Tutaj http://www.oracle.com/technology/products/bi/pdf/10gr1...
piszą:

For example, the most efficient mechanism for 'counting' within an Oracle database is by using bitmap indexes, so Oracle will implicitly create a bitmap index structure when computing a frequent itemset.

Sądziłem więc, że będzie to najszybszy z dostępnych mechanizmów choć rzeczywiście specyfika wbudowanej funkcji różni się od systemu analizującego dane ładowane co pewien czas.

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Widzę, że już w tym wątku popełniłem dwa błędy.
1. Zapytania testuję na różnej wielkości danych. Na przykład żeby szybko oszacować różnice w działaniu całego systemu używam zbioru z 6 mln rekordów a różnice w czasach wykonywania pojedynczego zapytania 50 mln. Prawdopodobnie umieściłem też plany dotyczące tych dwóch różnych zbiorów.
2. Sprawdzałem plan przy pomocy

explain plan for

z SQL Developera (to są te wklejone w tym wątku). Plan używany podczas pracy systemu podejrzany w EM różni się od nich - załączyłem do niego linka w pierwszym poście (http://zacheusz.eu/various/plan.html) Tutaj widać użycie indeksu bitmapowego na samym początku.
Przepraszam że zrobiłem takie zamieszanie.

Mariusz Masewicz:

Zbiór wartości jest ograniczony więc wszystko wskazywało na to, że indeks bitmapowy będzie najlepszym rozwiązaniem.

Bedzie DOBRYM - od stwierdzenia "najlepszym" trzymalbym sie z daleka. Zwlaszcza, ze nie policzyles statystyk

Zaraz to zrobię. Znam rozkład i "statystyki" wejściowego zbioru danych - na tej podstawie wybrałem indeks bitmapowy.

Mariusz Masewicz:
A czemu nie pomyslales o MV takim:

SELECT tid, item, count(*)
FROM TRANSACTIONS
group by tid, item;
:-)

Bo potrzebuję sprawdzenia wsparcia konkretnego podzbioru a nie wszystkich. Poza tym w "produkcyjnych" danych unikalnych tid jest około 3200000 a rekordów 50000000. Wydaje mi się nienajlepszym pomysłem ładowanie tego wszystkiego do pamięci. BTW nie do końca rozumiem jak chciałbyś to zapytanie zastosować.

Krzysztof Pułapa:
Więc może jednak spróbuj jeszcze swoje przykłady z hintem /*+ index(MVIEW_TRANSACTIONS MVIEW_TRANSACTIONS_INDEX_ITEM) */

Tabela IOT ma też wady ... np. wysoki koszt insertów.

Koszt insertów może być wysoki - to jest swego rodzaju hurtownia danych, ładowanie może być kosztowne. Zaraz spróbuję z hintem.Zacheusz Siedlecki edytował(a) ten post dnia 16.12.09 o godzinie 22:47

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Różnica w czasach wykonania między zmaterializowanym widokiem z bitmapą a tabelą z bitmapą wynikała właśnie, że przy tabeli baza "nie połapała się" że trzeba użyć indeksu. Po użyciu hinta mimo wszystko IOT zrobiony według pomysłu Krzysztofa działa około dwukrotnie szybciej.
Plan oraz statystyki dla zapytania na tabeli z indeksem bitmapowym:






Oraz dla zapytania z IOT:






(tym razem tabele na pewno zawierają ten sam zestaw danych)Zacheusz Siedlecki edytował(a) ten post dnia 16.12.09 o godzinie 22:39

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Zenbrałem statystyki. Mam nadzieję, że o to chodziło. Wykonałem:

EXEC DBMS_STATS.create_stat_table('GENERATOR','STATS_TABLE');

exec dbms_stats.gather_index_stats('party0', 'TRANSACTIONS2B_INDEX_ITEM', stattab=>'STATS_TABLE', statown=>'GENERATOR');

exec dbms_stats.gather_table_stats('party0', 'TRANSACTIONS_G2_B', stattab=>'STATS_TABLE', statown=>'GENERATOR');

exec dbms_stats.gather_table_stats('party0', 'TRANSACTIONS_G2', stattab=>'STATS_TABLE', statown=>'GENERATOR');

SELECT c5 AS owner,

  c1      AS table_name,

  n1      AS num_rows,

  n2      AS blocks,

  n3      AS avg_row_len,

  n4      AS sample_size

FROM GENERATOR.STATS_TABLE

WHERE type = 'T'

AND c2    IS NULL;

SELECT c1 AS index_name,

  n1      AS num_rows,

  n2      AS leaf_blocks,

  n3      AS distinct_keys,

  n4      AS leaf_blocks_per_key,

  n5      AS data_blocks_per_key,

  n6      AS clustering_factor,

  n7      AS blevel,

  n8      AS sample_size

FROM GENERATOR.STATS_TABLE

WHERE type = 'I'

AND c2    IS NULL;

Otrzymałem:

OWNER                          TABLE_NAME                     NUM_ROWS               BLOCKS                 AVG_ROW_LEN            SAMPLE_SIZE            

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

PARTY0                         TRANSACTIONS_G2_B              774906                 1504                   8                      774906                 

PARTY0                         TRANSACTIONS_G2                774906                 0                      8                      774906                 



2 rows selected



INDEX_NAME                     NUM_ROWS               LEAF_BLOCKS            DISTINCT_KEYS          LEAF_BLOCKS_PER_KEY    DATA_BLOCKS_PER_KEY    CLUSTERING_FACTOR      BLEVEL                 SAMPLE_SIZE            

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

TRANSACTIONS2B_INDEX_ITEM      347                    168                    100                    1                      3                      347                    1                      347                    

TRANSACTIONS_G2B_PK            774906                 3527                   774906                 1                      1                      145206                 2                      774906                 

TRANSACTIONS_G2_PK             774906                 1316                   774906                 1                      1                      0                      2                      774906                 



3 rows selected

tutaj umieszczam dodatkowo w cvs wynik zapytania

SELECT * FROM GENERATOR.STATS_TABLE;

EDIT:
Oprócz tego zapiszę je zaraz do słownika czyli

exec dbms_stats.gather_index_stats('party0', 'TRANSACTIONS2B_INDEX_ITEM');

exec dbms_stats.gather_table_stats('party0', 'TRANSACTIONS_G2_B');

exec dbms_stats.gather_table_stats('party0', 'TRANSACTIONS_G2');

Nie wiedziałem po co są te statystyki :) Dziękuję :)
PS jak widać teraz działam na mniejszym zestawie danych niż 50 mln :]Zacheusz Siedlecki edytował(a) ten post dnia 17.12.09 o godzinie 03:08

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Co ciekawe po zebraniu statystyk OIT nieznacznie zwolnił a bitmapa przyspieszyła (i tak jest wolniejsza niż OIT bez statystyk). Są to różnice rzędu kilku procent.
Plany nie zmieniły się (są takie jak na screenshotach w poście wyżej). Wyczyszczenie statystyk przez

EXEC  DBMS_STATS.delete_database_stats;

przywróciło poprzednią prędkość.Zacheusz Siedlecki edytował(a) ten post dnia 17.12.09 o godzinie 03:54

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Marcin Przepiórowski:
Pozatym 50 mln rekorodow
nie ladujesz do pamieci a do tabeli czyli na dysk ;) przy czym
taka liczba danych do dla Oracla "Pan Pikus" ;)

Dziękuję za wskazówki. Niemniej jednak dalej nie rozumiem jak mogłoby mi tu pomóc zapytanie

SELECT tid, item, count(*)

FROM TRANSACTIONS

group by tid, item;

?Zacheusz Siedlecki edytował(a) ten post dnia 17.12.09 o godzinie 05:31

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Szanowny Mariuszu

Mariusz Masewicz:

Zacheusz Siedlecki:

Marcin Przepiórowski:
Pozatym 50 mln rekorodow
nie ladujesz do pamieci a do tabeli czyli na dysk ;) przy czym
taka liczba danych do dla Oracla "Pan Pikus" ;)

Dziękuję za wskazówki.

Za wskazowki dziekujesz, a statystyki i tak usuwasz, zamiast postarac sie zrozumiec jak one dzialaja...

Postarałem się zrozumieć ale skoro w przypadku mojego systemu i rozwiązania, które obecnie jest najszybsze (pomysł Krzysztofa) powodują jego zwolnienie a ich usunięcie przyspieszenie to chyba logiczne, że je usunąłem? Przypominam, że charakterystyka danych w tym przypadku raczej nie zmieni się - są załadowane na początku a baza jest otwarta read only.

Niemniej jednak dalej nie rozumiem jak mogłoby mi tu pomóc zapytanie

SELECT tid, item, count(*)

FROM TRANSACTIONS

group by tid, item;

Nie zapytanie a MV oparta o to zapytanie. Ale odnosze wrazenie ze swoja wiedze na temat Oracla czerpiesz z wyszukiwanych ad-hoc, wyrwanych z kontekstu kawalkow roznych dziwnych blogow i inszych szemranych "poradnikow". Moze zainwestuj troche czasu w przeczytanie ze zrozumieniem darmowej dokumentacji - na poczatek "tuning guide" dla swojej wersji bazy danych. Do zdobycia jest to na przyklad tu: http://docs.oracle.com

W głównej mierze przeglądałem właśnie dokumentację z oracle.com Niemniej jednak w żadnym stopniu nie jestem specjalistą od Oracle. Najważniejszym elementem mojej pracy są algorytmy kryptograficzne napisane w Javie. A że okazało się, że baza pod spodem zamula to stanąłem przed koniecznością stunningowania jej.
Dokumentację przeglądam ale proszę o pomoc bo przeczytanie dokumentacji nie zastąpi lat doświadczeń i gruntownej wiedzy, którą niektórzy tutaj posiadają.

A po przeczytaniu tej pasjonujacej literatury sam dowiesz sie jak powyzsze MV wraz z mechanizmem Query Rewrite maja sie do Twojego zapytania:

SELECT tid
FROM MVIEW_TRANSACTIONS
WHERE item IN (70 ,2573 ,2430 ,2424) group by tid having count(*)=4

Przy okazji oczywiscie dowiesz sie co to sa statystyki, jakie statystyki nadaja sie do wspierania jakich zapytan (bo tu w gre wchodza i jakies histogramy). Pozniej poznasz wady i zalety indeksow, w tym takze bitmapowych a na koniec dowiesz sie po co w Oracle 9 zmieniono nazwe starego Snapszota na szumne MV :-)

Jeśli (podkreślam - jeśli) chodzi Ci o przerobienie zapytania typu:

SELECT count(*)

FROM

(SELECT tid

FROM party0.TRANSACTIONS_G2

WHERE item IN (70 ,2573 ,2430 ,2424) group by tid having count(*)=4 ) subtable;

na

  CREATE MATERIALIZED VIEW "PARTY0"."MVIEW_TRANSACTIONS_G2_GROUP_BY" ("TID", "ITEM", "C")

  AS SELECT 

    TID, 

    ITEM, 

    COUNT(*) AS c 

FROM 

    TRANSACTIONS_G2 

GROUP BY 

    TID, 

    ITEM;

SELECT COUNT(*)

FROM party0.mview_transactions_g2_group_by

WHERE item IN (70 ,2573 ,2430 ,2424)

AND c = 4;

to po prostu nie zrozumiałeś działania tego pierwszego. A z całym szacunkiem, nie ja tu jestem specjalistą.Zacheusz Siedlecki edytował(a) ten post dnia 17.12.09 o godzinie 18:01

Temat: Optymalizacja 11g pod jeden typ prostych zapytań [?]

Oo dzięki. Zaraz wypróbuję Twoje pomysły. To zapytanie powinno wyglądać tak (drobne poprawki):

select count(*) from (

  select distinct(tid) from party0.transactions_g2 outer

  where exists (select tid from party0.transactions_g2 where item = :1 and tid = outer.tid) 

  and exists (select tid from party0.transactions_g2 where item = :2 and tid = outer.tid) 

  and exists (select tid from party0.transactions_g2 where item = :3 and tid = outer.tid)

  and exists (select tid from party0.transactions_g2 where item = :4 and tid = outer.tid)

);

Na razie działa znacznie wolniej od poprzednich (próbowałem też z hintami dla bitmapy na zagnieżdżonych selectach), ale spróbuję z tym partycjonowaniem i użyję advisora. Wcześniej go próbowałem dla poprzedniego zapytania ale nic nie podpowiedział.
Jestem pod wrażeniem - nawet nie miałem pomysłu na alternatywne zapytanie (a conieco próbowałem myśleć) :)Zacheusz Siedlecki edytował(a) ten post dnia 18.12.09 o godzinie 01:21