Temat: statystyki systemowe i... wydajność spada na łeb.

Cześć,
mam taką dziwną zagwozdkę, po zebraniu statystyk systemowych wydajność bazy poleciała okrutnie na łeb na szyję (koszty zapytań poszybowały w górę). Fakt, że statystyki były zebrane w godzinach porannych, gdzie ruch jeszcze na bazie nie był zbyt duży, ale dlaczego tak radykalnie spada wydajność jak zaczyna się naprawdę duże obciążenie? Po skasowaniu statystyk od razu baza "wraca do życia".
...i tak przy okazji nasuwa mi się pytanie, dlaczego jak puścimy sobie jakieś dłuższe zapytanie na bazie na której nikogo poza nami nie ma, i to zapytanie wisi na sequential read. Wykresiki w EM pokazują nam zużycie 20%. Dodanie odpowiednio dużego parallel przyspiesza wykonanie tego zapytania (zyżycie w EM 80%). No i tu jest klucz, dlaczego jeżeli jesteśmy totalnie sami na bazie, to cała para z procków i macierzy nie idzie na wykonanie tego jednego zapytania, tylko 80% zasobów jest na wczasach?

Temat: statystyki systemowe i... wydajność spada na łeb.

Musisz podać więcej detali:
1) Wersja bazy
2) System operacyjny

Piszesz:
3) po zebraniu statystyk systemowych wydajność bazy poleciała okrutnie na łeb na szyję (koszty zapytań poszybowały w górę)
Czy plany zapytań uległy zmianie? 10g jest podatne na bind variable peeking. Kiedyś miałem taką sytuację, że zapytanie miało 2 różne plany wykonań, pomogło skasowanie statystyk na tabeli i zamrożenie ich. NUM_ROWS było null i optymalizotor wybierał najlepszy plan wykonania

4) ale dlaczego tak radykalnie spada wydajność jak zaczyna się naprawdę duże obciążenie.

Co to znaczy radykalnie spada wydajność? Gdzie jest wąskie gardło? CPU, memory, I/O ?

5) dlaczego jak puścimy sobie jakieś dłuższe zapytanie na bazie na której nikogo poza nami nie ma, i to zapytanie wisi na sequential read. Wykresiki w EM pokazują nam zużycie 20%

20% czego? CPU (zielone) ? To normalne jeśli Twój proces czeka na "sequential read" czyli, np czyta po indeksie, to w tym czasie procesor czeka na I/O. Jak sobie w tym czasie sprawdzisz poleceniem top/sar/mpstat to zobacz co statystyki wątka CPU.

6) Dodanie odpowiednio dużego parallel przyspiesza wykonanie tego zapytania (zyżycie w EM 80%)

To raczej normalne.

7) dlaczego jeżeli jesteśmy totalnie sami na bazie, to cała para z procków i macierzy nie idzie na wykonanie tego jednego zapytania, tylko 80% zasobów jest na wczasach?

jeśli nie zrównoleglasz zapytania, to jest ono obsługiwane przez jeden wątek.

Zrób trace za pomocą

alter session set timed_statistics = true;
alter session set statistics_level=ALL;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='asynch';
alter session set events = '10046 trace name context forever, level 12 : 10053 trace name context forever, level 1';

---> Twoje zapytanie <---

select * from dual;
alter session set events = '10046 trace name context off : 10053 trace name context off ';
exit;

Performance tuning to dluga i ciezka droga, a na koncu drogi nie zawsze jest sukces. :)

Temat: statystyki systemowe i... wydajność spada na łeb.

Aleksander Mathias:
Cześć,
mam taką dziwną zagwozdkę, po zebraniu statystyk systemowych wydajność bazy poleciała okrutnie na łeb na szyję (koszty zapytań poszybowały w górę).

Jaka jest wartość parametru optimizer_mode ?

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOr...

Co do nudzenia się systemu ... to można jeszcze pokombinować z tym:

http://www.ploug.org.pl/plougtki.php?action=read&p=32&a=5

Temat: statystyki systemowe i... wydajność spada na łeb.

1) Wersja bazy

11.2.0.2

2) System operacyjny

RHEL

Piszesz:
3) po zebraniu statystyk systemowych wydajność bazy poleciała okrutnie na łeb na szyję (koszty zapytań poszybowały w górę)
Czy plany zapytań uległy zmianie? 10g jest podatne na bind variable peeking. Kiedyś miałem taką sytuację, że zapytanie miało 2 różne plany wykonań, pomogło skasowanie statystyk na tabeli i zamrożenie ich. NUM_ROWS było null i optymalizotor wybierał najlepszy plan wykonania

3) To nie 10g. Tak jak napisałem, plany zapytań uległy zmianie (dramatycznie duże koszty przy zebranych statystykach systemowych (koszty pociągnęły za sobą dłuższe czasy wykonania)) ale niestety nie mam przykładu tych zapytań - mea culpa.

4) ale dlaczego tak radykalnie spada wydajność jak zaczyna się naprawdę duże obciążenie.

Co to znaczy radykalnie spada wydajność? Gdzie jest wąskie gardło? CPU, memory, I/O ?

4) W większości systemów które widziałem, to wąskim gardłem jest I/O, tak samo jest tutaj.

5) dlaczego jak puścimy sobie jakieś dłuższe zapytanie na bazie na której nikogo poza nami nie ma, i to zapytanie wisi na sequential read. Wykresiki w EM pokazują nam zużycie 20%

20% czego? CPU (zielone) ? To normalne jeśli Twój proces czeka na "sequential read" czyli, np czyta po indeksie, to w tym czasie procesor czeka na I/O. Jak sobie w tym czasie sprawdzisz poleceniem top/sar/mpstat to zobacz co statystyki wątka CPU.

Tak, ale jeżeli jest oczekiwanie na I/O znaczy procesor się nudzi a macierz w tym czasie powinna czytać wszystkimi siłami, a że jestem sam na bazie to 100% odczytów powinno iść dla mnie, nie (nie ważne czy single czy w parallel - czas powinien być ten sam)? Tak rozumując, to w parallel powinno zejść nawet trochę dłużej, bo tworzonych jest kilka procesów i może się zdarzyć, że jeden proces prosi o dane z początku dysku, a drugi o dane z końca dysku. Dysk zamiast czytać po kolei, to przestawia głowicę raz na początek, raz na koniec dysku. W macierzach to pewnie sporadycznie, ale jednak.
Chyba, że ma to związek z multiblock read?!

6) Dodanie odpowiednio dużego parallel przyspiesza wykonanie tego zapytania (zyżycie w EM 80%)

To raczej normalne.

No z punktu 5 wynika, że nie zawsze - jeżeli nie wyrabia procesor, to się zgadzam. Dwa procesy działają na dwóch różnych procesorach, ale przy odczytach z dysku? Przecież pod spodem jest jedna macierz.

7) dlaczego jeżeli jesteśmy totalnie sami na bazie, to cała para z procków i macierzy nie idzie na wykonanie tego jednego zapytania, tylko 80% zasobów jest na wczasach?

jeśli nie zrównoleglasz zapytania, to jest ono obsługiwane przez jeden wątek.

Możesz to rozwinąć? Jak się ma odczyt z dysku przy jednym wątku i przy dwóch wątkach?

Zrób trace za pomocą

alter session set timed_statistics = true;
alter session set statistics_level=ALL;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='asynch';
alter session set events = '10046 trace name context forever, level 12 : 10053 trace name context forever, level 1';

---> Twoje zapytanie <---

>

select * from dual;
alter session set events = '10046 trace name context off : 10053 trace name context off ';
exit;

Performance tuning to dluga i ciezka droga, a na koncu drogi nie zawsze jest sukces. :)

...no właśnie, czy zawsze? ;)

@Krzysztof, poczytałem, ale niestety nie wyjaśnia to dlaczego tak kiepsko się dzieje po zebraniu statystyk. Podejrzewam, że kluczowym może być czas zbierania statystyk - czyt. za mały workload.Aleksander Mathias edytował(a) ten post dnia 02.02.13 o godzinie 11:58

Temat: statystyki systemowe i... wydajność spada na łeb.

Witam!
Zgodnie z tym co sugerował Bartek, zacząłbym od trace'ów 10046 i 10053 - szczególnie ten drugi może być przydatny, bo może się okazać, że po zebraniu statystyk, transformator SQL przerabia Twoje zapytania do drastycznie innych postaci.

Twoje zapytanie wisi na db file sequential read po zebraniu statystyk?
A w planie jest odczyt indeksu czy pełne skanowanie tabeli? Jeśli to full table scan, i tabela jest "duża", to 11g może wybrać skanowanie pojedynczym procesem ale przy użyciu direct path read - jeśli tabela składa się z ekstentów o nierównomiernej wielkości to pojedyncze bloki ekstentów będą doczytywane przez db file sequential read, co faktycznie może stanowić problem wydajnościowy.

Jaka jest wielkość tej tabeli (w blokach)?
Jaki plan wykonania?
Jaka wielkość db cache?
Jaka wartość parametru _small_table_treshold?

Pozdrawiam!
Kamil.

Temat: statystyki systemowe i... wydajność spada na łeb.

Wątek pewnie już nieaktualny...
Ale co tam ;)
W przypadku problemów wydajnościowych po zebraniu statystyk systemowych, przede wszystkim należałoby sprawdzić jakie mamy wartości statystyk systemowych.
Czyli
select * from aux_stats$;
i przede wszystkim sprawdzić wartości SREADTIM i MREADTIM.
W 11.2 wartości potrafiły być całkowicie z kosmosu,
jako "workaround" - dbms_stats.set_system_stats
i ustawienie wartości dla single i multiblock read zbliżonych do tego co faktycznie potrafi nasz sprzęt.

Pozdrawiam,
T.K.