[ORACLE] przyspieszenie selecta z grupowaniem
Maciej Iwanczewski - 11-05-2007 12:31
[ORACLE] przyspieszenie selecta z grupowaniem
Witajcie!
Mam w mojej bazie danych taką tabelę:
CREATE TABLE TEST_PLC
(
WABCO_NR CHAR(10 BYTE),
SYS_NR NUMBER,
STEP NUMBER,
STEP_TIME DATE,
STATUS NUMBER,
TEST_NR NUMBER,
DATA_1 NUMBER,
DATA_2 NUMBER,
DATA_3 NUMBER,
DATA_4 NUMBER,
DATA_5 NUMBER,
DATA_6 NUMBER
);
z takimi indexami:
CREATE INDEX INDX_TEST_PLC_STATUS ON TEST_PLC(STATUS);
CREATE INDEX INDX_TEST_PLC_STEP ON TEST_PLC(STEP);
CREATE INDEX INDX_TEST_PLC_STEP_TIME ON .TEST_PLC(STEP_TIME);
CREATE INDEX INDX_TEST_PLC_SYS_NR ON TEST_PLC(SYS_NR);
W tej tabeli jest około 40 milionów rekordów.
Robię zapytanie:
select max(step_time), sysdate-max(step_time), sys_nr
from test_plc group by sys_nr order by max(step_time) desc
Niestety wykonuje one się dłuugo, około 60 sekund.
Robiłem dla tego zapytania explain plan (w toad):
SELECT STATEMENT ALL_ROWS Cost: 74,523 Bytes: 564 Cardinality: 47
3 SORT ORDER BY Cost: 74,523 Bytes: 564 Cardinality: 47
2 HASH GROUP BY Cost: 74,523 Bytes: 564 Cardinality: 47
1 TABLE ACCESS FULL TABLE WABCO.TEST_PLC Cost: 68,741 Bytes:
475,270,296 Cardinality: 39,605,858
Próbowałem wymuszać użycie indeksów (/*+ index(...) */ ale to nic nie
pomagało - baza to ignorowała.
Mam z tuningowaniem mało doświadczenie, próbowałem szukać w googlach
czegoś na temat tuningowania "group by" ale bez powodzenia.
Dla drugiej, identycznej tabeli (5mln rekordów) i zapytania (30sek)
zakładałem jeszcze index na sys_nr i step_time ale Oracle go ignorował.
Macie jakieś pomysły, hasełko dla googli/dokumentacji oracla? :)
Pozdrawiam,
Maciek
Lucyna Witkowska - 11-05-2007 12:31
Maciej Iwanczewski <nawiiwan@wytnij_togazeta.pl> napisał:
> Dla drugiej, identycznej tabeli (5mln rekordów) i zapytania (30sek)
> zakładałem jeszcze index na sys_nr i step_time ale Oracle go ignorował.
Pojedyncze indeksy w tym przypadku nic nie dadzą. Jedyna szansa to indeks
złożony - trzeba zastanowic sie dlaczego nie zostal użyty.
Brak statystyk? Złe parametry optymalizatora?
Sprobuj wymusić jego użycie.
Pozdrowienia,
Lucyna Witkowska
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 07:57, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> Maciej Iwanczewski <nawiiwan@wytnij_togazeta.pl> napisał:
>
> > Dla drugiej, identycznej tabeli (5mln rekordów) i zapytania (30sek)
> > zakładałem jeszcze index na sys_nr i step_time ale Oracle go ignorował.
>
> Pojedyncze indeksy w tym przypadku nic nie dadzą. Jedyna szansa to indeks
> złożony - trzeba zastanowic sie dlaczego nie zostal użyty.
> Brak statystyk? Złe parametry optymalizatora?
> Sprobuj wymusić jego użycie.
zrobiłem index na step_time i sys_nr:
CREATE INDEX INDX_TEST_PLC_STEP_TIME_SYS_NR ON WABCO.TEST_PLC
(STEP_TIME, SYS_NR);
Oracle go ignorował, próbowałem wymuszać dając select, też index był
ignorowany:
select /*+ index(TEST_PLC INDX_TEST_PLC_STEP_TIME_SYS_NR) */
max(step_time), sysdate-max(step_time), sys_nr from test_plc group by
sys_nr order by max(step_time) desc
Spróbowałem zrobić index bitmapowy:
CREATE BITMAP INDEX INDX_TEST_PLC_STEP_TIME_SYS_NR ON WABCO.TEST_PLC
(STEP_TIME, SYS_NR);
i efekt był pozytywny. Zamiast minuty około 30 sekund ale to dalej
trochę długo dla mnie, liczę na jakieś max. 5 sek.
Co jeszcze mogę zrobić?
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 07:57, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> Maciej Iwanczewski <nawiiwan@wytnij_togazeta.pl> napisał:
>
> > Dla drugiej, identycznej tabeli (5mln rekordów) i zapytania (30sek)
> > zakładałem jeszcze index na sys_nr i step_time ale Oracle go ignorował.
>
> Pojedyncze indeksy w tym przypadku nic nie dadzą. Jedyna szansa to indeks
> złożony - trzeba zastanowic sie dlaczego nie zostal użyty.
> Brak statystyk? Złe parametry optymalizatora?
> Sprobuj wymusić jego użycie.
Szukałem o tych statystykach w googlach. Z tego co wyczytałem to dla
mojego Oracle 10g nie ma potrzeby robienia COMPUTE STATISTICS.
pozdrawiam,
Maciek
Lucyna Witkowska - 11-05-2007 12:31
nawiiwan@gazeta.pl napisał:
> zrobiłem index na step_time i sys_nr:
> CREATE INDEX INDX_TEST_PLC_STEP_TIME_SYS_NR ON WABCO.TEST_PLC
> (STEP_TIME, SYS_NR);
> Oracle go ignorował, próbowałem wymuszać dając select, też index był
> ignorowany:
> select /*+ index(TEST_PLC INDX_TEST_PLC_STEP_TIME_SYS_NR) */
> max(step_time), sysdate-max(step_time), sys_nr from test_plc group by
> sys_nr order by max(step_time) desc
Zmien kolejnosc kolumn w indeksie - szukasz maksymalnej wartosci STEP_TIME
dla kazdej grupy SYS_NR.
Pozdrowienia,
LW
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 09:28, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> nawii...@gazeta.pl napisał:
>
> > zrobiłem index na step_time i sys_nr:
> > CREATE INDEX INDX_TEST_PLC_STEP_TIME_SYS_NR ON WABCO.TEST_PLC
> > (STEP_TIME, SYS_NR);
> > Oracle go ignorował, próbowałem wymuszać dając select, też index był
> > ignorowany:
> > select /*+ index(TEST_PLC INDX_TEST_PLC_STEP_TIME_SYS_NR) */
> > max(step_time), sysdate-max(step_time), sys_nr from test_plc group by
> > sys_nr order by max(step_time) desc
>
> Zmien kolejnosc kolumn w indeksie - szukasz maksymalnej wartosci STEP_TIME
> dla kazdej grupy SYS_NR.
Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
jest bez zmian.
Oracle nie chce indeksu mimo wymuszania.
Dla bitmapowego czas jest podobny jak poprzednio (ok 30s).
Próbowałem:
select max(step_time) from test_plc where sys_nr=1
Użył indeksu bitmapowego ale było wolne, ok 1s.
Pozdrawiam,
Maciek
Marcin 'goral' Goralski - 11-05-2007 12:31
Maciej Iwanczewski wrote:
> select max(step_time), sysdate-max(step_time), sys_nr from test_plc
> group by sys_nr order by max(step_time) desc
>
> Niestety wykonuje one się dłuugo, około 60 sekund.
Mozesz prosze powiedziec, jak dlugo wykona sie :
select sys_nr, max(step_time)
from test_plc
group by sys_nr
order by 2 desc, tak z ciekawosci ?
> Macie jakieś pomysły, hasełko dla googli/dokumentacji oracla? :)
Troche tuningu, jesli mozliwe na poziomie IO ? Rozlozenie plikow z
danymi i reorganizacja storagu ?
marcin
Lucyna Witkowska - 11-05-2007 12:31
nawiiwan@gazeta.pl napisał:
> Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> jest bez zmian.
> Oracle nie chce indeksu mimo wymuszania.
No dobra - ostatnia szansa :-)
Czy SYS_NR, STEP_TIME są NOT NULL?
Pozdrowienia,
LW
dap997 - 11-05-2007 12:31
Zmaterializowane View nie wchodzi w grę?
dap
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
> Zmaterializowane View nie wchodzi w grę?
Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
rozwiązania to bardzo proszę :)
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 11:20, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> nawii...@gazeta.pl napisał:
>
> > Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> > jest bez zmian.
> > Oracle nie chce indeksu mimo wymuszania.
>
> No dobra - ostatnia szansa :-)
> Czy SYS_NR, STEP_TIME są NOT NULL?
Tabela dopuszczała NULL dane były NOT NULL. Mimo to dodałem
constrainta NOT NULL.
Nie spowodowało to żadnych zmian (przyspieszenia).
Pozdrawiam,
Maciek
Lucyna Witkowska - 11-05-2007 12:31
nawiiwan@gazeta.pl napisał:
> > > Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> > > jest bez zmian.
> > > Oracle nie chce indeksu mimo wymuszania.
> >
> > No dobra - ostatnia szansa :-)
> > Czy SYS_NR, STEP_TIME są NOT NULL?
> Tabela dopuszczała NULL dane były NOT NULL. Mimo to dodałem
> constrainta NOT NULL.
> Nie spowodowało to żadnych zmian (przyspieszenia).
Po dodaniu ograniczenia NOT NULL, zwlaszcza na SYS_NR - usun i zbuduj od
nowa indeks (SYS_NR, STEP_TIME).
I oblicz aktualne statystyki dla tej tabeli:
exec dbms_stats.gather_table_stats('WABCO','TEST_PLC');
Pozdrowienia,
LW
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
>> Zmaterializowane View nie wchodzi w grę?
>
> Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
> ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
> eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
> jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
> rozwiązania to bardzo proszę :)
Nie musi, choć zależy co oznacza załadować rekord?
Jeśli oznacza to dodanie rekordu, to przelicz dane do końca poprzedniego
dnia/godziny/minut po czym wykonaj zapytanie tylko na danych dodanych po
ostatnim przeliczeniu. Wyniki zsumuj.
--
P.M.
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 14:10, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> nawii...@gazeta.pl napisał:
>
> > > > Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> > > > jest bez zmian.
> > > > Oracle nie chce indeksu mimo wymuszania.
>
> > > No dobra - ostatnia szansa :-)
> > > Czy SYS_NR, STEP_TIME są NOT NULL?
> > Tabela dopuszczała NULL dane były NOT NULL. Mimo to dodałem
> > constrainta NOT NULL.
> > Nie spowodowało to żadnych zmian (przyspieszenia).
>
> Po dodaniu ograniczenia NOT NULL, zwlaszcza na SYS_NR - usun i zbuduj od
> nowa indeks (SYS_NR, STEP_TIME).
> I oblicz aktualne statystyki dla tej tabeli:
> exec dbms_stats.gather_table_stats('WABCO','TEST_PLC');
Zrobiłem tak jak radzisz, szczególnie z tym ponownym zrobieniem
indeksu i widać zmianę :) Ze zwykłym indeksem jest 40s. (Oracle użył
indeksu) a z bitmapowym jest bez zmian - ok. 30s.
Wniosek z tego, że NOT NULL spowodował, że Oracle użył indeksu.
Obliczenie statystyk nic nie zmienia.
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 11:27, Marcin 'goral' Goralski
<goralski.mar...@BEZ.SMIECI.wp.pl> wrote:
> Maciej Iwanczewski wrote:
> > select max(step_time), sysdate-max(step_time), sys_nr from test_plc
> > group by sys_nr order by max(step_time) desc
>
> > Niestety wykonuje one się dłuugo, około 60 sekund.
>
> Mozesz prosze powiedziec, jak dlugo wykona sie :
> select sys_nr, max(step_time)
> from test_plc
> group by sys_nr
> order by 2 desc, tak z ciekawosci ?
Bez zmian. Probowałem równiez to co proponujesz ale bez sortowania:
select sys_nr, max(step_time)
from test_plc
group by sys_nr
wynik nie jest szybciej. Przypuszczam, że powodem jest ilość
zwracanych wierszy - ok. 50.
> > Macie jakieś pomysły, hasełko dla googli/dokumentacji oracla? :)
>
> Troche tuningu, jesli mozliwe na poziomie IO ? Rozlozenie plikow z
> danymi i reorganizacja storagu ?
Tutaj wymagało by to wiedzy tajemnej której chwilowo nie posiadam :)
Nie moge czytac wszystkiego o ORACLU :)
Miłego dzionka,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 14:35, Paweł Matejski <m...@spam.madej.pl.eu.org> wrote:
> nawii...@gazeta.pl wrote:
> > On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
> >> Zmaterializowane View nie wchodzi w grę?
>
> > Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
> > ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
> > eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
> > jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
> > rozwiązania to bardzo proszę :)
>
> Nie musi, choć zależy co oznacza załadować rekord?
> Jeśli oznacza to dodanie rekordu, to przelicz dane do końca poprzedniego
> dnia/godziny/minut po czym wykonaj zapytanie tylko na danych dodanych po
> ostatnim przeliczeniu. Wyniki zsumuj.
Już referuję.
Załadowanie rekordu oznacza insert do tej tabeli robiony przez
sqlloadera. Idzie to z bat-a co pięć minut. Za każdym razem
paredziesiąt/pareset rekordów (insertów).
Problem też jest taki, że max(step_time) dla jednego sys_nr jest 15
sekund temu a dla drugiego jest to rok temu. A chciałbym aby wszystko
to zostało wybrane (i 15 sekund temu i rok temu).
Nie do końca rozumiem jak przeliczyć dane. Możesz więcej wyjaśnić.
Dzięki,
Maciek
Marcin 'goral' Goralski - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
>>> Macie jakieś pomysły, hasełko dla googli/dokumentacji oracla? :)
>> Troche tuningu, jesli mozliwe na poziomie IO ? Rozlozenie plikow z
>> danymi i reorganizacja storagu ?
>
> Tutaj wymagało by to wiedzy tajemnej której chwilowo nie posiadam :)
> Nie moge czytac wszystkiego o ORACLU :)
Niekoniecznie. Jak wyglada definicja tej przestrzeni tablic jesli idzie
o datafiles ?
Problem bardziej dla admina niz programisty, acz niebanalny przy tej
ilosci rekordow, zakladajac, ze baza jest produkcyjna :-)
marcin
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> On 19 Kwi, 14:35, Paweł Matejski <m...@spam.madej.pl.eu.org> wrote:
>> nawii...@gazeta.pl wrote:
>>> On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
>>>> Zmaterializowane View nie wchodzi w grę?
>>> Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
>>> ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
>>> eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
>>> jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
>>> rozwiązania to bardzo proszę :)
>> Nie musi, choć zależy co oznacza załadować rekord?
>> Jeśli oznacza to dodanie rekordu, to przelicz dane do końca poprzedniego
>> dnia/godziny/minut po czym wykonaj zapytanie tylko na danych dodanych po
>> ostatnim przeliczeniu. Wyniki zsumuj.
>
> Już referuję.
> Załadowanie rekordu oznacza insert do tej tabeli robiony przez
> sqlloadera. Idzie to z bat-a co pięć minut. Za każdym razem
> paredziesiąt/pareset rekordów (insertów).
> Problem też jest taki, że max(step_time) dla jednego sys_nr jest 15
> sekund temu a dla drugiego jest to rok temu. A chciałbym aby wszystko
> to zostało wybrane (i 15 sekund temu i rok temu).
>
> Nie do końca rozumiem jak przeliczyć dane. Możesz więcej wyjaśnić.
Nie wiem, czy masz jakąś kolumnę, która określa kolejność dodawania rekordów.
Jeśli takiej nie ma, to musiałbyś ją dodać. (może być id, może być czas dodania
rekordu).
Załóżmy że bedziesz miał kolumnę czas.
Wynik tego selecta wrzucasz do tabelki przeliczone_test_plc
select max(step_time) as max_step_time, sys_nr
from test_plc
where czas < :wczoraj
group by sys_nr
A Twoje zapytanie zamieniasz na:
select max(step_time), sysdate-max(step_time), sys_nr
from
( select step_time, sys_nr
from test_plc
where czas > :wczoraj
union all
select max_step_time, sys_nr
from przeliczone_test_plc ) sub
group by sys_nr
order by max(step_time) desc
Musisz pamiętać, żeby przechowywać albo jednoznacznie wyliczać wartość :wczoraj.
P.S. Nie jestem biegły w oraclowych mechanizmach, więc przykład jak to robić w
czystym sql'u.
--
P.M.
Marcin 'PIORO' Przepiorowski - 11-05-2007 12:31
> On 19 Kwi, 14:10, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
> wrote:
> > nawii...@gazeta.pl napisał:
> >
> > > > > Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> > > > > jest bez zmian.
Mysle ze warto jeszcze sprawdzic na co Oracle traci czas podczas wykonywanie
takiego selecta. Przed wykonaniem SELECTA wykonaj z tej samej sesji polecenie
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';
a potem popatrz w katalogu udump do pliku sladu.
To powinno rozjasnic nieco obraz - jesli potrzebujesz pomocy odezwij sie na maila.
pozdrawiam,
Marcin 'PIORO' Przepiorowski
--
Wysłano z serwisu OnetNiusy: http://niusy.onet.pl
dap997 - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> On 19 Kwi, 14:10, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
> wrote:
>> nawii...@gazeta.pl napisał:
>>
>>>>> Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
>>>>> jest bez zmian.
>>>>> Oracle nie chce indeksu mimo wymuszania.
>>>> No dobra - ostatnia szansa :-)
>>>> Czy SYS_NR, STEP_TIME są NOT NULL?
>>> Tabela dopuszczała NULL dane były NOT NULL. Mimo to dodałem
>>> constrainta NOT NULL.
>>> Nie spowodowało to żadnych zmian (przyspieszenia).
>> Po dodaniu ograniczenia NOT NULL, zwlaszcza na SYS_NR - usun i zbuduj od
>> nowa indeks (SYS_NR, STEP_TIME).
>> I oblicz aktualne statystyki dla tej tabeli:
>> exec dbms_stats.gather_table_stats('WABCO','TEST_PLC');
>
> Zrobiłem tak jak radzisz, szczególnie z tym ponownym zrobieniem
> indeksu i widać zmianę :) Ze zwykłym indeksem jest 40s. (Oracle użył
> indeksu) a z bitmapowym jest bez zmian - ok. 30s.
> Wniosek z tego, że NOT NULL spowodował, że Oracle użył indeksu.
Wklej proszę plan i statystyki.
dap
dap997 - 11-05-2007 12:31
.... i wersje oraz masz dostęp do partycji?
dap
dap997 - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
>> Zmaterializowane View nie wchodzi w grę?
>
> Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
> ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
> eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
> jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
> rozwiązania to bardzo proszę :)
Da się, ale to może potem.
Możesz zrobić
1) Takie indeksy
drop index test_dd ;
create index test_dd on test_PLC(sys_NR, step_time) compress;
drop index test_ddd ;
create index test_ddd on test_PLC(sys_NR, step_time desc) compress;
drop index test_d ;
create index test_d on test_PLC(step_time, sys_NR) compress;
drop index test_d1 ;
create index test_d1 on test_PLC(step_time desc, sys_NR) compress;
Jak nie masz wersji EE to wytnij opcje compress
2) i taki select? Powinien zwrócić to samo co zapytanie z group by ale
nie mam tego jak prztestować.
select ala, sysdate-ala, sys_nr from (
select step_time, max(step_time) over (partition by sys_nr) as ala,
sys_nr from test_plc order by ala desc);
3) Wklej plan wykonania i statystyki :)
4) Z tego co widac masz tylko 47 numerow systemowych, zgadza sie?
dap
dap
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 16:53, dap997 <news...@gazeta.pl> wrote:
> nawii...@gazeta.pl wrote:
> > On 19 Kwi, 14:10, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
> > wrote:
> >> nawii...@gazeta.pl napisał:
>
> >>>>> Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> >>>>> jest bez zmian.
> >>>>> Oracle nie chce indeksu mimo wymuszania.
> >>>> No dobra - ostatnia szansa :-)
> >>>> Czy SYS_NR, STEP_TIME są NOT NULL?
> >>> Tabela dopuszczała NULL dane były NOT NULL. Mimo to dodałem
> >>> constrainta NOT NULL.
> >>> Nie spowodowało to żadnych zmian (przyspieszenia).
> >> Po dodaniu ograniczenia NOT NULL, zwlaszcza na SYS_NR - usun i zbuduj od
> >> nowa indeks (SYS_NR, STEP_TIME).
> >> I oblicz aktualne statystyki dla tej tabeli:
> >> exec dbms_stats.gather_table_stats('WABCO','TEST_PLC');
>
> > Zrobiłem tak jak radzisz, szczególnie z tym ponownym zrobieniem
> > indeksu i widać zmianę :) Ze zwykłym indeksem jest 40s. (Oracle użył
> > indeksu) a z bitmapowym jest bez zmian - ok. 30s.
> > Wniosek z tego, że NOT NULL spowodował, że Oracle użył indeksu.
>
> Wklej proszę plan i statystyki.
jak wydłubałem w googlach to rozumiem, że chodzi o:
set autot on
set timing on
Wynik dla selecta i indeksu jak ponizej
select max(step_time), sysdate-max(step_time), sys_nr
from test_plc group by sys_nr order by max(step_time) desc;
CREATE INDEX WABCO.INDX_TEST_PLC_SYS_NR_STEP_TIME ON WABCO.TEST_PLC
(SYS_NR, STEP_TIME);
Plan wykonywania
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33023 Card=47
Bytes=564)
1 0 SORT (ORDER BY) (Cost=33023 Card=47 Bytes=564)
2 1 HASH (GROUP BY) (Cost=33023 Card=47 Bytes=564)
3 2 INDEX (FAST FULL SCAN) OF
'INDX_TEST_PLC_SYS_NR_STEP_TIME' (INDEX) (Cost=27203 Card=39851283
Bytes=478215396)
Statystyki
----------------------------------------------------------
2 recursive calls
2 db block gets
123130 consistent gets
122997 physical reads
1164 redo size
3216 bytes sent via SQL*Net to client
602 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
47 rows processed
Jeżeli chodzi o Oracle, to mam 10gR2 (dokładniej 10.2.0.3.0), wersja
Enterprise więc z partycjonowaniem.
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 15:45, Marcin 'goral' Goralski
<goralski.mar...@BEZ.SMIECI.wp.pl> wrote:
> nawii...@gazeta.pl wrote:
> >>> Macie jakieś pomysły, hasełko dla googli/dokumentacji oracla? :)
> >> Troche tuningu, jesli mozliwe na poziomie IO ? Rozlozenie plikow z
> >> danymi i reorganizacja storagu ?
>
> > Tutaj wymagało by to wiedzy tajemnej której chwilowo nie posiadam :)
> > Nie moge czytac wszystkiego o ORACLU :)
>
> Niekoniecznie. Jak wyglada definicja tej przestrzeni tablic jesli idzie
> o datafiles ?
tablespace jest "standardowy", users :) Definicja:
CREATE TABLESPACE USERS DATAFILE
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS01.D BF' SIZE 6980M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS02.D BF' SIZE 10240M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Dyska E, jest to RAID 1 na dwóch dyskach w macierzy FibreChannel DELL
EMC.
File system NTFS pod Windowsem 2003 Enterprise 64bit.
> Problem bardziej dla admina niz programisty, acz niebanalny przy tej
> ilosci rekordow, zakladajac, ze baza jest produkcyjna :-)
Baza jak najbardziej produkcyjna :) Lądują w niej wyniki z maszyn -
praktycznie całą dobę. Jest tam kilkanaście tabel, ta test_plc nie
jest największą :) ale akurat na niej chcę robić takie zapytanie.
Ilość danych dziennie w test_plc to około: 100tys.
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 19 Kwi, 15:59, Paweł Matejski <m...@spam.madej.pl.eu.org> wrote:
> nawii...@gazeta.pl wrote:
> > On 19 Kwi, 14:35, Paweł Matejski <m...@spam.madej.pl.eu.org> wrote:
> >> nawii...@gazeta.pl wrote:
> >>> On 19 Kwi, 12:57, dap997 <news...@gazeta.pl> wrote:
> >>>> Zmaterializowane View nie wchodzi w grę?
> >>> Dane do tej tabeli są ładowane "non stop". I wynik MUSI uwzględnić
> >>> ostatni załadowany rekord (a może to być, np. 10 sek. temu). Chyba to
> >>> eliminuje możliwość stosowania widoków zmaterializowanych, ale jednak
> >>> jak bym mógł prosić o rozszerzenie zaproponowanego przez Ciebie
> >>> rozwiązania to bardzo proszę :)
> >> Nie musi, choć zależy co oznacza załadować rekord?
> >> Jeśli oznacza to dodanie rekordu, to przelicz dane do końca poprzedniego
> >> dnia/godziny/minut po czym wykonaj zapytanie tylko na danych dodanych po
> >> ostatnim przeliczeniu. Wyniki zsumuj.
>
> > Już referuję.
> > Załadowanie rekordu oznacza insert do tej tabeli robiony przez
> > sqlloadera. Idzie to z bat-a co pięć minut. Za każdym razem
> > paredziesiąt/pareset rekordów (insertów).
> > Problem też jest taki, że max(step_time) dla jednego sys_nr jest 15
> > sekund temu a dla drugiego jest to rok temu. A chciałbym aby wszystko
> > to zostało wybrane (i 15 sekund temu i rok temu).
>
> > Nie do końca rozumiem jak przeliczyć dane. Możesz więcej wyjaśnić.
>
> Nie wiem, czy masz jakąś kolumnę, która określa kolejność dodawania rekordów.
> Jeśli takiej nie ma, to musiałbyś ją dodać. (może być id, może być czas dodania
> rekordu).
> Załóżmy że bedziesz miał kolumnę czas.
>
> Wynik tego selecta wrzucasz do tabelki przeliczone_test_plc
>
> select max(step_time) as max_step_time, sys_nr
> from test_plc
> where czas < :wczoraj
> group by sys_nr
>
> A Twoje zapytanie zamieniasz na:
>
> select max(step_time), sysdate-max(step_time), sys_nr
> from
> ( select step_time, sys_nr
> from test_plc
> where czas > :wczoraj
> union all
> select max_step_time, sys_nr
> from przeliczone_test_plc ) sub
> group by sys_nr
> order by max(step_time) desc
Zrobiłem eksperymentalnie:
select max(step_time), sysdate-max(step_time), sys_nr
from ( select step_time, sys_nr
from test_plc where step_time>to_date('2007-04-01','YYYY-MM-DD'))
group by sys_nr order by max(step_time) desc;
jeżeli wezmę dane za cały kwiecień, jak wyżej to jest 17s zamiast30s.
wiec szybciej, ale dalej wolno :(
Jeżeli dane od wczoraj (2007-04-19 00:00:00) do "teraz" to jest bardzo
ładnie, <500ms :)
Więc Twoje rozwiązanie jest niezłe czasowo, ale wymaga dodatkowej
tabeli czego chciałbym uniknąć.
Jeszcze myślałem o zrobieniu triggera na tabeli test_plc który w
dodatkowej tabeli max_time najpierw by usuwał wpis dla insert-owanego
sys_nr i następnie ładował by do niej nowy sys_nr i step_time. mniej
więcej tak:
delete from max_time where sys_nr=xxx
insert into max_time(sys_nr,step_time) values (xxx,zzz)
U mnie jest tak, że każdy następny step_time jest "nowszy" od
poprzedniego ponieważ sa to wyniki z pracy maszyny ściągane z niej po
kolei.
Pozdrawiam,
Maciek
Marcin 'goral' Goralski - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS01.D BF' SIZE 6980M
> AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
> 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS02.D BF' SIZE 10240M
> AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
> Dyska E, jest to RAID 1 na dwóch dyskach w macierzy FibreChannel DELL
> EMC.
> File system NTFS pod Windowsem 2003 Enterprise 64bit.
Krotko mowiac masz dwa data file na jednym fizycznym nosniku, nie wazne,
ze w konfiguracji mirrora. Tak wiec czytasz z jednego urzadzenia calosc
danych. Masz tam moze jakis drugi dysk czy RAID 1, gdzie moglbys
umiescic przeniesc jeden z plikow danych ? Mysle o fizycznym
urzadzeniu, najlepiej nie tym, na ktorym leza redo czy indexy.
> Baza jak najbardziej produkcyjna :)
To problem admina, zeby wobec tego ew. zaplanowal przebudowe systemu ...
goral
dap997 - 11-05-2007 12:31
Hej,
> jak wydłubałem w googlach to rozumiem, że chodzi o:
> set autot on
> set timing on
Tak
> Wynik dla selecta i indeksu jak ponizej
> select max(step_time), sysdate-max(step_time), sys_nr
> from test_plc group by sys_nr order by max(step_time) desc;
>
> CREATE INDEX WABCO.INDX_TEST_PLC_SYS_NR_STEP_TIME ON WABCO.TEST_PLC
> (SYS_NR, STEP_TIME);
>
> Plan wykonywania
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33023 Card=47
> Bytes=564)
> 1 0 SORT (ORDER BY) (Cost=33023 Card=47 Bytes=564)
> 2 1 HASH (GROUP BY) (Cost=33023 Card=47 Bytes=564)
> 3 2 INDEX (FAST FULL SCAN) OF
> 'INDX_TEST_PLC_SYS_NR_STEP_TIME' (INDEX) (Cost=27203 Card=39851283
> Bytes=478215396)
>
> Statystyki
> ----------------------------------------------------------
> 2 recursive calls
> 2 db block gets
> 123130 consistent gets
> 122997 physical reads
Z tego wynika, że prawie wszystko musi być przeczytane z dysku. To
raczej niedobrze :)
Możesz wykonać to zapytanie parokrotnie ze autot on i podesłać wyniki?
Z drugiej strony to jest ciut dziwne, bo:
full table scan przeglada - 475,270,296 bajtów
fast full index scan przeglada - 478 215 396 bajtów,
Zatem index jest tak samo duży jak tabela?
I dodatkowe pytanie o pamięć
Jaki masz db_cache size?
show sga
show parameter size
Jedno pytanie, te 47 wierszy, w wynikach nie zmienia się? Mogą tylko
dość nowe, ale nie ma na tej tabeli żadnych zmian czy kasowań?
dap
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 20 Kwi, 09:20, Marcin 'goral' Goralski
<goralski.mar...@BEZ.SMIECI.wp.pl> wrote:
> nawii...@gazeta.pl wrote:
> > 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS01.D BF' SIZE 6980M
> > AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
> > 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\PRODAW\USERS02.D BF' SIZE 10240M
> > AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
> > Dyska E, jest to RAID 1 na dwóch dyskach w macierzy FibreChannel DELL
> > EMC.
> > File system NTFS pod Windowsem 2003 Enterprise 64bit.
>
> Krotko mowiac masz dwa data file na jednym fizycznym nosniku, nie wazne,
> ze w konfiguracji mirrora. Tak wiec czytasz z jednego urzadzenia calosc
> danych. Masz tam moze jakis drugi dysk czy RAID 1, gdzie moglbys
> umiescic przeniesc jeden z plikow danych ? Mysle o fizycznym
> urzadzeniu, najlepiej nie tym, na ktorym leza redo czy indexy.
>
> > Baza jak najbardziej produkcyjna :)
>
> To problem admina, zeby wobec tego ew. zaplanowal przebudowe systemu ...
To jest tak, że zajmuje się tą bazą w pełni (administruję, programuję
itp), więc mam problem ;)
Te dwa pliki są tylko dlatego, że kiedyś "przez przypadek" stworzyłem
drugi - w pierwszym momencie myślałem, że piererwszy plik się wypełnił
a okazało się, że właściciel schematu nie miał unlimited tablespace
jak dobrze pamiętam :) Więc dwa pliki trzeba traktować jako jeden.
W sumie mam 4 dyski ma macierzy podzielone na 2x2 czyli 2xRAID1 tyle,
że ten "drugi" RAID1 jest do backupu, archive logów a wszystkie pliki
dla tablespace sa na jednym RAID1. Do tego są dyski lokalne w serwerze
ale odpadają ponieważ być może w przyszłości będzie RAC więc dane
muszą być na współdzielonym dysku.
Tutaj zapytał bym sie o polecanę literaturę gdzie poczytam jak tym
zarządzić i dlaczego. Bo temat ba grupy pewnie będzie za szeroki.
Pozdrawiam,
Maciek
Marcin 'goral' Goralski - 11-05-2007 12:31
nawiiwan@gazeta.pl wrote:
> Tutaj zapytał bym sie o polecanę literaturę gdzie poczytam jak tym
> zarządzić i dlaczego. Bo temat ba grupy pewnie będzie za szeroki.
Genralnie Oracle ma dobra literature do tuning bazy, rowniez podreczniki
z Performance and Tuning. Zewnetrznej literatury nie znam ... ale sporo
by Ci pomoglo, gdybys zmienil organizacje storagu od strony fizycznej,
jak sadze. Generalna zasada jest taka, zeby w przypadku danych
najczesciej uzywanych dokonywac maxymalnego podzialu IO, separowac dane
logicznie (datafile z dala od indexow i od redo, archive na bezpiecznych
lokacjach z nadmiarowoscia itd).
Poza tymi ustawieniami - warot zobaczyc co sie dzieje w pamieci, jak to
juz sugerowal dap. Choc tam nie spodziewalbym sie na razie znacznych
zyskow na tuningu, Twoje statystyki jak na razie pokazuja znaczacy
"koszt" na czytaniu danych ...
goral
PS majac 4 dyski ... niewiele mozesz pokombinowac od tej strony ...
chyba, ze te 4 dyski przeznaczone sa tylko i wylacznie na dane, a system
siedzi na lokalnym dysku spoza macierzy.
W taki wypadku, o ile zakup dodatkowych w gre nie wchodzi, lub jest
ograniczony w inny sposob, mozesz sprobowac wrzucic
1. OS + instalacje Oracle na dysk lokalny spoza macierzy
2. zrobic stripe'a (RAID 0) z pierwszych dwoch dyskow macierzy i trzymac
tam dane (bez indexow)
3. na trzecim dysku trzymac indexy
4. na czwartym redo + archive
5. niestety - backup masz wtedy zewnetrzny :-), gdyz nie masz juz dyskow :-)
PPS najlepiej dokup ze 4 dyski do macierzy :-), lub sprawdz wydajnosc
RAID 5 w Twojej konfiguracji 4 dyskow ... przy czytaniu powinno byc
szybciej, ale nie przy zapisie.
Lucyna Witkowska - 11-05-2007 12:31
dap997 <news997@gazeta.pl> napisał:
> Z drugiej strony to jest ciut dziwne, bo:
> full table scan przeglada - 475,270,296 bajtów
> fast full index scan przeglada - 478 215 396 bajtów,
> Zatem index jest tak samo duży jak tabela?
W tym przypadku tak jest - wiersz tabeli jest krotki - liczby i data.
Niestety czytanie takiego indeksu przez FAST FULL INDEX SCAN
wymaga przeczytania wszystkich bloków.
> I dodatkowe pytanie o pamięć
> Jaki masz db_cache size?
Tez stawiam, ze za malo jest rezerwowanej pamieci na SGA.
Ale obiekty sa duze (400MB).
Dlatego raczej w takim wypadku - perspektywa materializowana,
odświeżana po kazdym wczytaniu danych.
Wtedy 40 sek. odświeżania to nie jest duzo. A biezące zapytania
wykonuja sie na tej perspektywie juz w ułamku sekundy.
Pozdrowienia,
LW
nawiiwan@gazeta.pl - 11-05-2007 12:31
On 20 Kwi, 09:34, dap997 <news...@gazeta.pl> wrote:
> > Wynik dla selecta i indeksu jak ponizej
> > select max(step_time), sysdate-max(step_time), sys_nr
> > from test_plc group by sys_nr order by max(step_time) desc;
>
> > CREATE INDEX WABCO.INDX_TEST_PLC_SYS_NR_STEP_TIME ON WABCO.TEST_PLC
> > (SYS_NR, STEP_TIME);
>
> > Plan wykonywania
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33023 Card=47
> > Bytes=564)
> > 1 0 SORT (ORDER BY) (Cost=33023 Card=47 Bytes=564)
> > 2 1 HASH (GROUP BY) (Cost=33023 Card=47 Bytes=564)
> > 3 2 INDEX (FAST FULL SCAN) OF
> > 'INDX_TEST_PLC_SYS_NR_STEP_TIME' (INDEX) (Cost=27203 Card=39851283
> > Bytes=478215396)
>
> > Statystyki
> > ----------------------------------------------------------
> > 2 recursive calls
> > 2 db block gets
> > 123130 consistent gets
> > 122997 physical reads
>
> Z tego wynika, że prawie wszystko musi być przeczytane z dysku. To
> raczej niedobrze :)
> Możesz wykonać to zapytanie parokrotnie ze autot on i podesłać wyniki?
oczywiście :)
zrobiłem 10 razy i wynik plan za każdym razem identyczy (taki jak
powyżej), dla statystyki małe zmiany, dla porównania z poprzednią
jedna poniżej.
0 recursive calls
0 db block gets
123359 consistent gets
123119 physical reads
0 redo size
3218 bytes sent via SQL*Net to client
602 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
47 rows processed
inne są recurcive calls i db block gets, trochę się zmienia consistent
gets, physical reads
> Z drugiej strony to jest ciut dziwne, bo:
> full table scan przeglada - 475,270,296 bajtów
> fast full index scan przeglada - 478 215 396 bajtów,
> Zatem index jest tak samo duży jak tabela?
Nie wiem na ile będzie to ma pływ na powyższe, ale dane zawierają
SYS_NR - numer urządzenia (jest ich 47) a STEP_TIME - czas wykonania
danego kroku. Więc nie jest możliwe w praktyce aby taka dana para
SYS_NR i STEP_TIME wystąpiły więcej niż jeden raz.
> I dodatkowe pytanie o pamięć
> Jaki masz db_cache size?
mam włączone automatyczne zarządzanie pamięcią przez Oracle i pewnie
dlatego mam:
db_cache_size=0,
ale sga_target=3056M
poniżej wszystkie parametry:
Total System Global Area 3204448256 bytes
Fixed Size 2058336 bytes
Variable Size 1996490656 bytes
Database Buffers 1191182336 bytes
Redo Buffers 14716928 bytes
NAME TYPE VALUE
------------------------------------ -------
------------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size unknown 0
db_2k_cache_size unknown 0
db_32k_cache_size unknown 0
db_4k_cache_size unknown 0
db_8k_cache_size unknown 0
db_block_size integer 8192
db_cache_size unknown 0
db_keep_cache_size unknown 0
db_recovery_file_dest_size unknown 0
NAME TYPE VALUE
------------------------------------ -------
------------------------------
db_recycle_cache_size unknown 0
global_context_pool_size string
hash_area_size integer 131072
java_max_sessionspace_size integer 0
java_pool_size unknown 0
large_pool_size unknown 0
max_dump_file_size string UNLIMITED
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
olap_page_pool_size unknown 0
parallel_execution_message_size integer 2152
NAME TYPE VALUE
------------------------------------ -------
------------------------------
sga_max_size unknown 3204448256
shared_pool_reserved_size unknown 95630131
shared_pool_size unknown 0
sort_area_retained_size integer 0
sort_area_size integer 65536
streams_pool_size unknown 50331648
workarea_size_policy string AUTO
> Jedno pytanie, te 47 wierszy, w wynikach nie zmienia się? Mogą tylko
> dość nowe, ale nie ma na tej tabeli żadnych zmian czy kasowań?
Podczas odpowiadania na Wasze pytania i testowania pomysłów cały czas
idą do tabeli nowe dane. Ilość różnych SYS_NR się nie zmienia. Może
będzie ich z czasem kilka więcej.
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 20 Kwi, 11:25, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> dap997 <news...@gazeta.pl> napisał:
>
> > Z drugiej strony to jest ciut dziwne, bo:
> > full table scan przeglada - 475,270,296 bajtów
> > fast full index scan przeglada - 478 215 396 bajtów,
> > Zatem index jest tak samo duży jak tabela?
>
> W tym przypadku tak jest - wiersz tabeli jest krotki - liczby i data.
> Niestety czytanie takiego indeksu przez FAST FULL INDEX SCAN
> wymaga przeczytania wszystkich bloków.
>
> > I dodatkowe pytanie o pamięć
> > Jaki masz db_cache size?
>
> Tez stawiam, ze za malo jest rezerwowanej pamieci na SGA.
> Ale obiekty sa duze (400MB).
Pamięci chyba tak mało nie przydzieliłem. Proszę zobacz odpowiedźna
post dap-a.
> Dlatego raczej w takim wypadku - perspektywa materializowana,
> odświeżana po kazdym wczytaniu danych.
> Wtedy 40 sek. odświeżania to nie jest duzo. A biezące zapytania
> wykonuja sie na tej perspektywie juz w ułamku sekundy.
Skrypt do ładowania idzie co mniej więcej 5 minut. Może to co
proponujesz było by dobrym pomysłem. Musiałbym sprawdzić. Tylko jest w
tym jeden problem taki, że takich tabel jak test_plc mam w sumie 3,
pozostałe sa trochę mniejsze. Ale w wersji z indeksem na sys_nr i
step_time wykonują sie w sumie około minuty. Więc musiałbym co 5 minut
zajmować 1 minutę na wygenerowanie tych danych które będą używane
kilka razy w czasie dnia - chyba nie optymalne? :)
A może da się do tego wykorzystać więcej rdzeni w procesorze? Mam w
serwerze 2 procesory po 4 cory. Może to wykorzystać?
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 19 Kwi, 18:19, dap997 <news...@gazeta.pl> wrote:
> Da się, ale to może potem.
coś group.google.pl nie pokazuje mojej odpowiedzi na twojego post-a
więc jeszcze raz :)
> Możesz zrobić
> 1) Takie indeksy
po zrobieniu indeksu i wygenerowaniu kodu skryptu w TOAD mam "compress
2" czy to prawidłowe?
> drop index test_dd ;
> create index test_dd on test_PLC(sys_NR, step_time) compress;
wynik bez zmian 36s., tak jak z indeksem bez compress..
> drop index test_ddd ;
> create index test_ddd on test_PLC(sys_NR, step_time desc) compress;
tego indeksu oracle nie chce, nawet przy wymuszaniu.
> drop index test_d ;
> create index test_d on test_PLC(step_time, sys_NR) compress;
czas bez zmian 36s.
>
> drop index test_d1 ;
> create index test_d1 on test_PLC(step_time desc, sys_NR) compress;
Oracle nie chciał, po wymuszeniu użył, ale wykonuje się dłuzej niz bez
indeksu
> Jak nie masz wersji EE to wytnij opcje compress
>
> 2) i taki select? Powinien zwrócić to samo co zapytanie z group by ale
> nie mam tego jak prztestować.
>
> select ala, sysdate-ala, sys_nr from (
> select step_time, max(step_time) over (partition by sys_nr) as ala,
> sys_nr from test_plc order by ala desc);
Próbowałem, ale coś nie działa - chyba chce zwrócić bardzo dużo
wyników. Zobaczyłem również:
select step_time, max(step_time) over (partition by sys_nr) as ala,
sys_nr from test_plc order by ala desc
ale wynik taki sam, nie działa :)
Plan
SELECT STATEMENT ALL_ROWSCost: 385,176 Bytes: 876,728,226
Cardinality: 39,851,283 CPU Cost: 101,832,515,203
4 VIEW SYSTEM. Cost: 385,176 Bytes: 876,728,226 Cardinality:
39,851,283 CPU Cost: 101,832,515,203
3 SORT ORDER BY Cost: 385,176 Bytes: 478,215,396 Cardinality:
39,851,283 CPU Cost: 101,832,515,203
2 WINDOW SORT Cost: 385,176 Bytes: 478,215,396 Cardinality:
39,851,283 CPU Cost: 101,832,515,203
1 INDEX FAST FULL SCAN INDEX SYSTEM.TEST_D1 Cost: 17,940 Bytes:
478,215,396 Cardinality: 39,851,283 CPU Cost: 5,369,071,893
> 3) Wklej plan wykonania i statystyki :)
jak rozumiem chodzi o to co powyżej :)
> 4) Z tego co widac masz tylko 47 numerow systemowych, zgadza sie?
W tej chwili tak. Ale ta ilośc może ulegać małym zmianom (tzn może byc
z czasem kilka/kilkanascie) wiecej.
Pozdrawiam,
Maciek
Lucyna Witkowska - 11-05-2007 12:32
nawiiwan@gazeta.pl napisał:
> Pamięci chyba tak mało nie przydzieliłem. Proszę zobacz odpowiedź na
> post dap-a.
Tak, pamieci masz duzo. Problem jest z wielkoscia indeksu.
Znalazlam takie rozwiazanie (J. Lewis):
Zrob pomocnicza tabelkę NUMERY z wszystkimi 50 sys_nr (mozesz dodac te
ktore maja dopiero byc).
I teraz:
SELECT s.sys_nr, (SELECT max(step_time) from test_plc
WHERE test_plc.sys_nr = s.sys_nr) max_step
FROM NUMERY s;
Jesli indeks (sys_nr, step_time) będzie przeszukany po INDEX RANGE SCAN
(MIN/MAX) musi byc dobrze :-)
Pozdrowienia,
Lucyna
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 19 Kwi, 15:59, "Marcin 'PIORO' Przepiorowski"
<pioro1WYTNI...@poczta.fm> wrote:
> > On 19 Kwi, 14:10, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
> > wrote:
> > > nawii...@gazeta.pl napisał:
>
> > > > > > Zrobiłem indeksy z zamienionymi kolumnami (SYS_NR,STEP_TIME) - efekt
> > > > > > jest bez zmian.
>
> Mysle ze warto jeszcze sprawdzic na co Oracle traci czas podczas wykonywanie
> takiego selecta. Przed wykonaniem SELECTA wykonaj z tej samej sesji polecenie
> ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';
zrobiłem:
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
aby nie generować zbędnych plików.
> a potem popatrz w katalogu udump do pliku sladu.
> To powinno rozjasnic nieco obraz - jesli potrzebujesz pomocy odezwij sie na maila.
Wynik jest ogromny. Wrzucę fragmenty:
PARSING IN CURSOR #4 len=113 dep=0 uid=5 oct=3 lid=5 tim=1365678017
hv=2056261356 ad='8bfbec0'
select max(step_time), sysdate-max(step_time), sys_nr
from test_plc group by sys_nr order by max(step_time) desc
END OF STMT
PARSE #4:c=0,e=1442,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,t im=1365678013
EXEC #4:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim =1365678165
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536
#bytes=1 p3=0 obj#=51360 tim=1365678202
WAIT #4: nam='db file scattered read' ela= 10163 file#=5 block#=832005
blocks=4 obj#=70187 tim=1365688529
WAIT #4: nam='db file scattered read' ela= 4344 file#=5 block#=843145
blocks=8 obj#=70187 tim=1365694418
[tutaj szło 7750 linii mniej więcej takich jak powyżej i ponizej.]
WAIT #4: nam='db file scattered read' ela= 1154 file#=5 block#=843154
blocks=7 obj#=70187 tim=1365697798
WAIT #4: nam='db file scattered read' ela= 1244 file#=5 block#=997325
blocks=16 obj#=70187 tim=1410747921
WAIT #4: nam='db file scattered read' ela= 605 file#=5 block#=997341
blocks=6 obj#=70187 tim=1410752695
FETCH
#4:c=34562500,e=45075448,p=123137,cr=123193,cu=0,m is=0,r=1,dep=0,og=1,tim=1410753682
WAIT #4: nam='SQL*Net message from client' ela= 814 driver
id=1413697536 #bytes=1 p3=0 obj#=70187 tim=1410755279
WAIT #4: nam='SQL*Net message to client' ela= 2 driver id=1413697536
#bytes=1 p3=0 obj#=70187 tim=1410755364
FETCH #4:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,ti m=1410755424
*** 2007-04-20 14:40:45.742
WAIT #4: nam='SQL*Net message from client' ela= 7518174 driver
id=1413697536 #bytes=1 p3=0 obj#=70187 tim=1418273674
WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1413697536
#bytes=1 p3=0 obj#=70187 tim=1418273801
FETCH #4:c=0,e=104,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,t im=1418273873
WAIT #4: nam='SQL*Net message from client' ela= 123088 driver
id=1413697536 #bytes=1 p3=0 obj#=70187 tim=1418397013
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=70187 tim=1418397059
FETCH #4:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,ti m=1418397115
WAIT #4: nam='SQL*Net message from client' ela= 115813 driver
id=1413697536 #bytes=1 p3=0 obj#=70187 tim=1418512962
WAIT #4: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=70187 tim=1418513009
FETCH #4:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim =1418513040
WAIT #4: nam='SQL*Net message from client' ela= 727 driver
id=1413697536 #bytes=1 p3=0 obj#=70187 tim=1418513839
*** SESSION ID:(240.4976) 2007-04-20 14:40:45.976
STAT #4 id=1 cnt=47 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=123193
pr=123137 pw=0 time=45075484 us)'
STAT #4 id=2 cnt=47 pid=1 pos=1 obj=0 op='HASH GROUP BY (cr=123193
pr=123137 pw=0 time=45075363 us)'
STAT #4 id=3 cnt=39979175 pid=2 pos=1 obj=70187 op='INDEX FAST FULL
SCAN INDX_TEST_PLC_SYS_NR_STEP_TIME (cr=123193 pr=123137 pw=0
time=10321 us)'
Pozdrawiam,
Maciek
Marcin 'goral' Goralski - 11-05-2007 12:32
[OT] cos mi to przypomina ...
nawiiwan@gazeta.pl wrote:
>> Jedno pytanie, te 47 wierszy, w wynikach nie zmienia się? Mogą tylko
Gdzie rozmawialems z kims na temat monitorowania urzadzen sieciowych
(routerow), co 5 minut , zczytujac ich paramtery i zapisujac na stale
.... cos takiego robisz ? Tak z ciekawosci ...
goral
=?ISO-8859-2?Q?S=B3awomir_Szysz=B3o?= - 11-05-2007 12:32
Dnia 20 Apr 2007 05:47:56 -0700, nawiiwan@gazeta.pl wklepał(-a):
>Wynik jest ogromny. Wrzucę fragmenty:
Przepuść to przez tkprof i wrzuć plik na jakąś stronę.
--
Sławomir Szyszło mailto:slaszysz@poczta.onet.pl
Primus inter FAQires & Grand Inquisitor no.0 of pl.comp.bazy-danych
FAQ pl.comp.bazy-danych http://www.dbf.pl/faq/
Archiwum http://groups.google.com/groups?grou...mp.bazy-danych
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 20 Kwi, 14:53, Marcin 'goral' Goralski
<goralski.mar...@BEZ.SMIECI.wp.pl> wrote:
> Gdzie rozmawialems z kims na temat monitorowania urzadzen sieciowych
> (routerow), co 5 minut , zczytujac ich paramtery i zapisujac na stale
> ... cos takiego robisz ? Tak z ciekawosci ...
Firma w której pracuje, jak pewnie wiele osób zauważyło z wątku,
nazywa sie WABCO. Produkujemy zawory do ciężarówek, autobusów. Każdy
wyprodukowany jest testowany na testerze o numerze SYS_NR :) i dane z
tych testerów lądują w bazie danych :)
Więc nie monitoruję urządzeń sieciowych :) Robi to u nas dział IT.
Miłego dzionka,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 20 Kwi, 12:48, Lucyna Witkowska <ypwit...@nospamcyf-kr.edu.pl>
wrote:
> nawii...@gazeta.pl napisał:
>
> > Pamięci chyba tak mało nie przydzieliłem. Proszę zobacz odpowiedź na
> > post dap-a.
>
> Tak, pamieci masz duzo. Problem jest z wielkoscia indeksu.
>
> Znalazlam takie rozwiazanie (J. Lewis):
> Zrob pomocnicza tabelkę NUMERY z wszystkimi 50 sys_nr (mozesz dodac te
> ktore maja dopiero byc).
Niestety nie wiem jakie będą w przyszłości numery SYS_NR. Zależy mi na
pełnej automatyce na stronie www która będzie korzystać z tych danych.
> I teraz:
> SELECT s.sys_nr, (SELECT max(step_time) from test_plc
> WHERE test_plc.sys_nr = s.sys_nr) max_step
> FROM NUMERY s;
>
> Jesli indeks (sys_nr, step_time) będzie przeszukany po INDEX RANGE SCAN
> (MIN/MAX) musi byc dobrze :-)
Spróbowałem, "w morde kopany" szybciutko to działa :)
Jest dokładnie tak jak napisałaś:
SELECT STATEMENT ALL_ROWSCost: 2 Bytes: 637 Cardinality: 49 CPU
Cost: 307,121
3 SORT AGGREGATE Bytes: 12 Cardinality: 1
2 FIRST ROW Cost: 4 Bytes: 10,174,800 Cardinality: 847,900 CPU
Cost: 28,686
1 INDEX RANGE SCAN (MIN/MAX) INDEX
WABCO.INDX_TEST_PLC_SYS_NR_STEP_TIME Cost: 4 Bytes: 10,174,800
Cardinality: 847,900 CPU Cost: 28,686
4 INDEX FAST FULL SCAN INDEX (UNIQUE) WABCO.NUMERY_PK Cost: 2 Bytes:
637 Cardinality: 49 CPU Cost: 307,121
zmodernizowałem trochę Twoje zapytanie tak aby mieć też (sysdate-
max(step_time) i też szybciutko działa :)
SELECT s.sys_nr, (SELECT max(step_time) from test_plc
WHERE test_plc.sys_nr = s.sys_nr) max_step,
(SELECT sysdate-max(step_time) from test_plc
WHERE test_plc.sys_nr = s.sys_nr) max_step_diff
FROM wabco.NUMERY s
order by max_step desc;
Teraz tylko co zrobic z tymi nie znanymi w przyszłości SYS_NR?
Pozdrawiam,
Maciek
nawiiwan@gazeta.pl - 11-05-2007 12:32
On 20 Kwi, 15:23, nawii...@gazeta.pl wrote:
> Teraz tylko co zrobic z tymi nie znanymi w przyszłości SYS_NR?
Odpowiem wstępnie sam sobie :)
Zrobiłem:
SELECT s.sys_nr, (SELECT max(step_time) from test_plc
WHERE test_plc.sys_nr = s.sys_nr) max_step,
(SELECT sysdate-max(step_time) from test_plc
WHERE test_plc.sys_nr = s.sys_nr) max_step_diff
FROM (select distinct(sys_nr) from test_plc) s
order by max_step desc;
i mam 8s. już nieźle, tylko jak by przyspieszyć to jeszcze trochę :)
Pozdrawiam,
Maciek
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
Strona 1 z 2 • Znaleźliśmy 101 postów • 1, 2
|
Oracle, SQL, PL/SQL. Jak =?ISO-8859-2?Q?napisa=E6_zapytanie=2C?==?ISO-8859-2?Q?_kt=F3re_zwr=F3ci_nazw=EA_atrybutu=2C_kt=F3reg o?==?ISO-8859-2?Q?_warto=B6ci_spe=B3niaj=B1_zadany_warunek?=
Oracle 19g +Insert +Insert +Insert...
[oracle] zapytanie dynamiczne z =?ISO-8859-2?Q?=22dynamiczn=B1_?==?ISO-8859-2?Q?nazw=B1_tabeli=22?=
[Oracle] jak =?ISO-8859-2?Q?ograniczy=E6_pami=EA=E6_dla_se?==?ISO-8859-2?Q?rwera=3F?=
=?ISO-8859-2?Q?=5BOT=5D_Zdany_egzamin_Oracle_1Z0-007_a?==?ISO-8859-2?Q?_brak_informacji_na_stronie_Prometric_-_czy?==?ISO-8859-2?Q?_co=B6_nie_tak=3F?=
[oracle] czy da =?ISO-8859-2?Q?si=EA_z_poziomu_procedury_?==?ISO-8859-2?Q?zrobi=E6_kopi=EA_zapasow=B1=3F?=
[oracle 10g] czy =?ISO-8859-2?Q?mo=BFna_wy=B3=B1czy=E6_wszys?==?ISO-8859-2?Q?tkie_wi=EAzy_w_schemacie=3F?=
MSSQL Express czy Oracle Express
=?iso-8859-2?q?[oracle]_Jak_sprawdzi=E6_wielko=B6=E6_tabeli_=3F=3F?=
=?ISO-8859-2?Q?Poszukjue_ksi=B1=BFki_"Oracle_?= =?ISO-8859-2?Q?optymalizacja_wydajno=B6ci"..?=
zanotowane.pldoc.pisz.plpdf.pisz.pltejsza.htw.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
