GoldenLine
Zaloguj się

konto usunięte

Temat: XMLTYPE

Bry

Jest sobie tabela z kolumną XMLTYPE. Trafiłem na zapytanie, które trwa dość długo z powodu full scana i doszedłem do tego, że usunięcie tej kolumny z SELECT powoduje, że baza zaczyna używać indeksu PK.

Wygląda to mniej więcej tak:

SELECT ID, DOC "XMLTYPE", inne kolumny ... FROM TABELA WHERE ..... - tu mamy full scan na TABELA

SELECT ID, inne kolumny ... FROM TABELA WHERE ..... - tu mamy oczekiwany skan po indeksie PK na ID

Szukając przyczyn takiego zachowania trafiłem na to, że dla tej kolumny nie są liczone statystyki, aczkolwiek dla wszystkich pozostałych kolumn z tej tabeli są aktualne.

Szukając po necie trafiłem tylko na statystyki dla XMLIndeks. Jak sobie z tym problemem poradzić ? Nie mogę zmienić treści zapytania oraz struktury tabel, ale mogę modyfikować parametry bazy lub dodawać/modyfikować indeksy.Ten post został edytowany przez Autora dnia 07.10.13 o godzinie 14:46
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Cześć,

Master Note for Oracle XML Database (XDB) Performance (Doc ID 1407946.1)
podpunkt Function Based Indexes.

Bartek.
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Bartosz Z.:
Cześć,

Master Note for Oracle XML Database (XDB) Performance (Doc ID 1407946.1)
podpunkt Function Based Indexes.

Dzięki, ale to chyba nie rozwiąże jednak mojego problemu. Tutaj chodzi bardziej o to, że optymalizator głupieje, gdy wyspecyfikuje się xmltype JAWNIE na liście kolumn. Wystarczy, że tą kolumnę się usunie lub co więcej, zastąpi się listę kolumn gwiazdką (!) i plan zapytania wraca do normy. Ta kolumna nie jest nigdzie używana w warunku where.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:
Bartosz Z.:
Cześć,

Master Note for Oracle XML Database (XDB) Performance (Doc ID 1407946.1)
podpunkt Function Based Indexes.

Dzięki, ale to chyba nie rozwiąże jednak mojego problemu. Tutaj chodzi bardziej o to, że optymalizator głupieje, gdy wyspecyfikuje się xmltype JAWNIE na liście kolumn. Wystarczy, że tą kolumnę się usunie lub co więcej, zastąpi się listę kolumn gwiazdką (!) i plan zapytania wraca do normy. Ta kolumna nie jest nigdzie używana w warunku where.

Krzysiek, a patrzyłeś może na trace'a 10053 na sekcję "Final query after transformation"? Może zobaczysz tam jakiś ciekawy format zapytania, który by powodował taki stan rzeczy. Może będzie można na tej podstawie jakoś je przepisać... A nawet jak to nie transformator, to może będzie tam jakaś inna wskazówka na temat powodu doboru planu.
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
Krzysiek, a patrzyłeś może na trace'a 10053 na sekcję "Final query after transformation"? Może zobaczysz tam jakiś ciekawy format zapytania, który by powodował taki stan rzeczy. Może będzie można na tej podstawie jakoś je przepisać... A nawet jak to nie transformator, to może będzie tam jakaś inna wskazówka na temat powodu doboru planu.

Dzięki, sprawdzę. Mam "z tym czymś" teraz kłopot, bo poruszam się trochę po nieznanym obszarze, tj. do tej pory albo pisałem PL/SQL albo po prostu grałem w drużynie "sysdba". Takie niespodzianki powodują, że ciężko mi nawet sformułować zapytanie do google :(

Po przejrzeniu "%nastu" stron nawet nie dało się potwierdzić, że statystyki dla XMLType nie są w ogóle liczone ....
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
jak to nie transformator, to może będzie tam jakaś inna wskazówka na temat powodu doboru planu.

No więc jest tak:

Zapytanie strukturalnie wygląda tak:

select id, col1, col2, col3 .... from tabela where exists (select null from tabela2 where tabela2.tabela_id = tabela.id and .... )

więc VW_SQ_1 to pewnie alias podzapytania.

Tak wygląda początek decyzji o full scanie (select z kolumną XMLTYPE):



Access path analysis for TABELA_X

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for TABELA_X[TABELA_X]

  Column (#1): ID(

    AvgLen: 33 NDV: 37379527 Nulls: 0 Density: 0.000000

  Table: TABELA_X  Alias: TABELA_X

    Card: Original: 37406610.000000  Rounded: 37406610  Computed: 37406610.00  Non Adjusted: 37406610.00

  Access Path: TableScan

    Cost:  408372.27  Resp: 408372.27  Degree: 0

      Cost_io: 406819.00  Cost_cpu: 37808410990

      Resp_io: 406819.00  Resp_cpu: 37808410990

  Best:: AccessPath: TableScan

         Cost: 408372.27  Degree: 1  Resp: 408372.27  Card: 37406610.00  Bytes: 0



***************************************





OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]:  TABELA_X[TABELA_X]#0


Tak wygląda początek decyzji o złączeniu po indeksie i skanie po ROWID (select bez XMLTYPE):



Access path analysis for TABELA_X

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for TABELA_X[TABELA_X]

  Table: TABELA_X  Alias: TABELA_X

    Card: Original: 37406610.000000  Rounded: 37406610  Computed: 37406610.00  Non Adjusted: 37406610.00

  Access Path: TableScan

    Cost:  408710.36  Resp: 408710.36  Degree: 0

      Cost_io: 406819.00  Cost_cpu: 46037865190

      Resp_io: 406819.00  Resp_cpu: 46037865190

  Best:: AccessPath: TableScan

         Cost: 408710.36  Degree: 1  Resp: 408710.36  Card: 37406610.00  Bytes: 0



***************************************





OPTIMIZER STATISTICS AND COMPUTATIONS

***************************************

GENERAL PLANS

***************************************

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]:  TABELA_X[TABELA_X]#0  VW_SQ_1[VW_SQ_1]#1


W tym drugim przypadku znajduje indeks PK i go używa. Pierwsza różnica jaka daje się zauważyć to "Column (#1): ID". Nie wiem skąd ta różnica, bo zapytanie zmieniło się tylko o kolumnę XMLTYPE.

A potem wygląda na to, że kolumna XMLTYPE powoduje wzrost kosztu przeglądania tabeli i optymalizator stwierdza, że full scan będzie lepszy. Niestety jest to totalnie zła decyzja i trzeba go jakoś nakłonić do wyprostowania tego.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:

A potem wygląda na to, że kolumna XMLTYPE powoduje wzrost kosztu przeglądania tabeli i optymalizator stwierdza, że full scan będzie lepszy. Niestety jest to totalnie zła decyzja i trzeba go jakoś nakłonić do wyprostowania tego.

To jest EE? Bo jeśli tak, to może by spróbować obniżyć koszt potencjalnego użycia indeks przez np. optimizer_index_cost_adj na jakąś śmiesznie niską wartoć w sesji i jak już wybierze plan z indeksem, użyć pakietu DBMS_SPM do permanentnego zastabilizowania planu?

Jeśli to nie jest EE, to wymusiłbym skanowanie indeksem przez hinta a potem z końcówki trace'a 10053 użył sekscji OUTLINE DATA żeby zastabilizować plan tym outlinem.

Ewentualnie jeżeli to EE a żadne machlojki parametrami sesji (ukrytymi bądź jawnymi) nie podziałają, to również popróbowałbym hinta, zgarnął z 10053 OUTLINE DATA i wykorzystał do stabilizacji planu. A żeby było koszernie, zmigrowałbym outline do baseline pakietem DBMS_SPM.

Co o tym myślisz?
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
To jest EE? Bo jeśli tak, to może by spróbować obniżyć koszt potencjalnego użycia indeks przez np. optimizer_index_cost_adj na jakąś śmiesznie niską wartoć w sesji i jak już wybierze plan z indeksem, użyć pakietu DBMS_SPM do permanentnego zastabilizowania planu?

To jest EE i już ustawiałem optimizer_index_cost_adj - tak nawet na ślepo/eksperymentalnie ale niczego to nie dało. Mam wrażenie, że przez jawne wyspecyfikowanie w SELECT kolumny typu XMLTYPE optymalizator w ogóle nie zauważa, że na kolumnie ID istnieje indeks PK. Niestety w tabeli nie ma innego BLOB'a czy CLOB'a więc nie mam na czym eksperymentować.
Ewentualnie jeżeli to EE a żadne machlojki parametrami sesji (ukrytymi bądź jawnymi) nie podziałają, to również popróbowałbym hinta, zgarnął z 10053 OUTLINE DATA i wykorzystał do stabilizacji planu. A żeby było koszernie, zmigrowałbym outline do baseline pakietem DBMS_SPM.

Co o tym myślisz?

Staramy się jak możemy, żeby nie robić takiego druta :) Jest możliwe, że ktoś o tym kiedyś zapomni, a ktoś inny zrobi eksport/import do innej bazy (test/dev, itp) i będzie dociekał tak jak ja teraz o co chodzi, że to co działało już nie działa.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:
Staramy się jak możemy, żeby nie robić takiego druta :) Jest możliwe, że ktoś o tym kiedyś zapomni, a ktoś inny zrobi eksport/import do innej bazy (test/dev, itp) i będzie dociekał tak jak ja teraz o co chodzi, że to co działało już nie działa.

No to, Panie, chyba trzeba będzie SR'a założyć. Ale z drugiej strony jakby się to udało baselinem ustabilizować, to one się ładnie przenoszą między bazami. A stuprocentowej przenoszalności bez problemów też uniknąć ciężko...

P.S.
A taz z ciekawości - jak wygląda faza "Final query after transformation" dla obu zapytań? Różnią się czymś?

I czy zapytanko reaguje na hinta?Ten post został edytowany przez Autora dnia 08.10.13 o godzinie 23:37
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
P.S.
A taz z ciekawości - jak wygląda faza "Final query after transformation" dla obu zapytań? Różnią się czymś?

Różnią się.

W przypadku użycia XMLTYPE wygląda tak:

select id, col1, col2, col3 .... from tabela   where exists 

(select 0 from tabela2 where tabela2.tabela_id = tabela.id and .... )

Gdy się wykasuje XMLTYPE to wynosi złączenie poza podzapytanie:
select id, col1, col2, col3 .... from tabela, 

            (select tabela2.tabela_id from tabela2 where .... ) vm_sql_1 

              where tabela.id = vm_sql_1.tabela_id


Wówczas idzie po indeksie (TABLE ACCESS BY INDEX ROWID). No i tu się zapatrzyłem w wyniki obu wersji, ponieważ są zupełnie inne :) Tj. w drugiej wersji wyświetla więcej rekordów.

I czy zapytanko reaguje na hinta?

Reaguje, ale robi FULL SCAN po indeksie PK. No i złączenia też są inne niż w wersji bez XMLTYPE. Koszt kosmiczny a wydajność bez zmian.
Link do wypowiedziLink
Paweł Grzegorz Kwiatkowski

Paweł Grzegorz Kwiatkowski Architekt
oprogramowania,
Ericsson

Temat: XMLTYPE

Krzysztof P.:

Ciekawy problem ;-)

Czy w trace dotyczącym zapytania z XMLType w ogóle pojawia się query unnesting albo jakieś informacje o transformacjach XML?
Wówczas idzie po indeksie (TABLE ACCESS BY INDEX ROWID). No i tu się zapatrzyłem w wyniki obu wersji, ponieważ są zupełnie inne :) Tj. w drugiej wersji wyświetla więcej rekordów.

Bug 12363485 - Wrong results / dump from subquery unnesting (Doc ID 12363485.8) ?
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Przepraszam, trochę naściemniałem jednak :), ponieważ zapytaniu występuje col.getclobval()

Dopiero użycie tej funkcji powoduje full scana. Sam select na kolumnie XMLTYPE jest OK.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

A jak często źródłowe tabele się zmieniają? Może walnąć MVIEW z query rewrite + jakieś sensowne odświeżanie?
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
A jak często źródłowe tabele się zmieniają? Może walnąć MVIEW z query rewrite + jakieś sensowne odświeżanie?

Nie można, bo wprowadza to zmianę w pobieraniu danych. Możemy robić wszystko, byle zachowanie bazy było niezależne od czasu.

Czyli możemy dodawać indeksy, zmieniać parametry instancji, ale nie możemy tego tak zmienić, że zapytanie wykonane w w tym samym momencie na bazie przed i po zmianie zwróci inne wyniki.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:
Czyli możemy dodawać indeksy, zmieniać parametry instancji, ale nie możemy tego tak zmienić, że zapytanie wykonane w w tym samym momencie na bazie przed i po zmianie zwróci inne wyniki.

To powiem Ci, Krzysztof, że mi się pomysły pomału kończą :) Jeszcze z jeden czy dwa miałbym odnośnie zmiany struktury zapytania ale piszesz, że to nie wchodzi w grę :/

Może jeszcze spróbować podrobić statystykę clustering_factor dla indeksu, żeby stał się bardziej atrakcyjny...
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Kamil S.:
Może jeszcze spróbować podrobić statystykę clustering_factor dla indeksu, żeby stał się bardziej atrakcyjny...

Taki photoshop ? :)

Niestety chyba dostępne dopiero od wersji 11.2.0.4 z tego co widzę (Doc ID 13262857.8). A ja nie mam takiej.
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:
Kamil S.:
Może jeszcze spróbować podrobić statystykę clustering_factor dla indeksu, żeby stał się bardziej atrakcyjny...

Taki photoshop ? :)

Niestety chyba dostępne dopiero od wersji 11.2.0.4 z tego co widzę (Doc ID 13262857.8). A ja nie mam takiej.

Dokładnie :) Taki photoshop. Ta notka, którą wkleiłeś dotyczy możliwości rozszerzenia ilości bloków, które są próbkowane przy liczeniu clustering_factor (jeśli dobrze zrozumiałem).

Ale można użyć zwykłego DBMS_STATS.SET_INDEX_STATS, żeby sztucznie obniżyć tę statystykę do wymyślonej przez siebie wartości (najlepiej do takiej, żeby ta liczba odpowiadała ilości bloków w tabeli). Wtedy indeks byłby chętniej wybierany, bo Oracle myślałby, że to niższy koszt.
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Ciekawy problem. Sam właśnie zaczynam rozpoznawać temat SQL Plan Management przez co nasuwa mi się pytanie czy parametr optimizer_capture_sql_plan_baselines jest ustawiony na TRUE ? Jeśli tak to być może w SQL Plan Management zatwierdzonym SPM baseline jest ten nie używający indeksu ?
Link do wypowiedziLink

konto usunięte

Temat: XMLTYPE

Bartosz Z.:
Cześć,

Master Note for Oracle XML Database (XDB) Performance (Doc ID 1407946.1)
podpunkt Function Based Indexes.

Bartek.

A jednak zapędziłem się za daleko usiłując na siłę leczyć pacjenta na inną chorobę.

Przez wskazanie podpunktu umknęło mi z tej notki jedno ważne zdanie: "It is recommended to XMLSerialize since its optimized by the XDB and thus performs better rather than using getclobval(). In addition from 11.2.x onwards xmltype.getclobval function is deprecated anyway"

Doc ID 1072039.1
Link do wypowiedziLink
Kamil Stawiarski

Kamil Stawiarski Oracle Certified
Master | Oracle ACE

Temat: XMLTYPE

Krzysztof P.:

A jednak zapędziłem się za daleko usiłując na siłę leczyć pacjenta na inną chorobę.

Przez wskazanie podpunktu umknęło mi z tej notki jedno ważne zdanie: "It is recommended to XMLSerialize since its optimized by the XDB and thus performs better rather than using getclobval(). In addition from 11.2.x onwards xmltype.getclobval function is deprecated anyway"

Doc ID 1072039.1

Ha! Cenna informacja :)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj