Co konkretnie robi OracleBulkCopy i jak mogę zoptymalizować jego wydajność?

Podsumowując szczegóły: Musimy wprowadzić około 5 milionów wierszy do bazy danych dostawców (Oracle). Wszystko idzie świetnie w przypadku partii 500 tys. Wierszy przy użyciu OracleBulkCopy(ODP.NET), ale gdy próbujemy skalować do 5 mln, wydajność zaczyna zwalniać do indeksowania, gdy osiągnie znak 1 mln, staje się stopniowo wolniejsza w miarę ładowania większej liczby wierszy i ostatecznie upływa po około 3 godzinach.

Podejrzewam, że jest to związane z kluczem podstawowym na stole, ale przeszukiwałem fora Oracle i Stack Overflow w celu uzyskania informacji i wiele tego, co czytam, zaprzecza temu (również wiele postów wydaje się zaprzeczać sobie nawzajem ) . Mam nadzieję, że ktoś może ustalić rekord w kilku ściśle powiązanych pytaniach dotyczących tego procesu:

1. Czy OracleBulkCopyklasa stosuje ładowanie konwencjonalne czy bezpośrednie? Czy jest jakiś sposób na potwierdzenie tego, w ten czy inny sposób?

2. Zakładając, że robi załadunek korzystanie z bezpośrednim ścieżka: Czy to prawda, że Oracle automatycznie ustawia wszystkie indeksy bezużyteczny podczas obciążenia i umieszcza je z powrotem w Internecie potem? Przeczytałem kilka oświadczeń na ten temat, ale znów nie mogę tego potwierdzić.

3. Jeśli # 2 jest prawdą, to czy powinno mieć jakiekolwiek znaczenie, jakie indeksy znajdują się w tabeli przed zainicjowaniem operacji kopiowania zbiorczego? Jeśli tak, to dlaczego?

4. Czy w związku z punktem 3 istnieje jakakolwiek praktyczna różnica między ładowaniem masowym z indeksem bezużytecznym a faktycznym upuszczaniem indeksu przed ładowaniem, a następnie jego odtwarzaniem?

5. Jeśli # 2 jest nie poprawny, lub jeśli istnieją pewne zastrzeżenia nie mam zrozumienia, wtedy byłoby żadnej różnicy do jawnie zrobić indeks bezużyteczny przed obciążeniem luzem, a następnie jawnie odbudować go potem?

6. Czy istnieje coś innego niż kompilacje indeksu, które mogłyby powodować stopniowe spowolnienie operacji kopiowania zbiorczego w miarę dodawania coraz większej liczby rekordów? (Być może ma to coś wspólnego z logowaniem, chociaż spodziewałbym się, że operacje masowe nie są rejestrowane?)

7. Jeśli naprawdę nie ma innego sposobu na podniesienie wydajności do tabaki oprócz upuszczenia najpierw PK / indeksu, jakie kroki mogę podjąć, aby upewnić się, że indeks nie zniknie całkowicie, tj. Jeśli połączenie z bazą danych zostanie utracone w środek procesu?

Jeszcze kilka dni czytania i eksperymentowania i mogłem (głównie) odpowiedzieć na wiele z nich:

1. Znalazłem to zakopane w dokumentacji ODP.NET (jak na ironię nie w OracleBulkCopydokumentach):

   Funkcja ODP.NET Bulk Copy wykorzystuje metodę bezpośredniego ładowania ścieżki, która jest podobna, ale nie taka sama, jak Oracle SQL * Loader. Korzystanie z bezpośredniego ładowania ścieżki jest szybsze niż ładowanie konwencjonalne (przy użyciu konwencjonalnych INSERTinstrukcji SQL ).

   Wygląda więc na to, że ma używać bezpośrednią drogę.

2. Mogłem to zweryfikować, wykonując dużą operację kopiowania zbiorczego i uzyskując właściwości indeksu od SQL Developer. Indeks pojawił się tak, jakby trwałoUNUSABLE kopiowanie zbiorcze. Jednak odkryłem również, że OracleBulkCopy.WriteToServerodmówi uruchomienia, jeśli indeks zaczyna się w UNUSABLEstanie, więc wyraźnie dzieje się tutaj więcej, ponieważ jeśli byłoby to tak proste, jak wyłączenie i przebudowanie indeksu, nie powinno to obchodzić stanu początkowego.

3. To robi różnicę, szczególnie jeśli indeks jest również ograniczeniem . Znalazłem ten mały klejnot w powyższej dokumentacji:

   Włączone ograniczenia
   Podczas kopiowania zbiorczego Oracle domyślnie włączone są automatycznie następujące ograniczenia:

   • NOT NULL
   • UNIQUE
   • PRIMARY KEY (unikalne ograniczenia na niepustych kolumnach)

   NOT NULLograniczenia są sprawdzane w czasie kompilacji tablicy kolumn. Każdy wiersz, który narusza NOT NULLograniczenie, jest odrzucany.

   UNIQUEograniczenia są weryfikowane, gdy indeksy są odbudowywane na końcu ładowania. Indeks jest pozostawiony w stanie Nieużywalny, jeśli narusza UNIQUEograniczenie.

   Dokumentacja jest nieco mglista na temat tego, co dzieje się podczas ładowania, szczególnie w przypadku kluczy podstawowych, ale jedno jest absolutnie pewne - zachowuje się inaczej z kluczem podstawowym niż bez niego . Ponieważ na OracleBulkCopyszczęście pozwoli ci na złamanie ograniczeń indeksu (i doprowadzi indeks do UNUSABLEstanu po zakończeniu ), mam przeczucie, że buduje on indeks PK podczas kopiowania zbiorczego, ale po prostu nie sprawdza go później.

4. Nie jestem pewien, czy zaobserwowana różnica dotyczy samego Oracle, czy tylko dziwactwa OracleBulkCopy. Jury wciąż nie bierze udziału w tym.

5. OracleBulkCopywyrzuci wyjątek, jeśli indeks jest początkowo w UNUSABLEstanie, więc naprawdę jest to kwestia sporna.

6. Jeśli tam są inne czynniki, indeksy i indeksy (zwłaszcza PK) są nadal najważniejszym, jak dowiedziałem się poprzez:

7. Tworzenie globalnej tabeli tymczasowej z tym samym schematem (za pomocą CREATE AS), a następnie kopiowanie zbiorcze do tabeli tymczasowej, a na koniec tworzenie zwykłej starej INSERTtabeli tymczasowej do tabeli rzeczywistej. Ponieważ tabela temp nie ma indeksu, kopiowanie zbiorcze odbywa się bardzo szybko, a wersja ostateczna INSERTjest również szybka, ponieważ dane są już w tabeli (jeszcze nie wypróbowałem wskazówki dołączania, ponieważ 5-wierszowa kopia tabeli do tabeli zajmuje już mniej niż 1 minutę).

   Nie jestem jeszcze pewien potencjalnych konsekwencji (ab) korzystania z tymczasowego obszaru tabel w ten sposób, ale jak dotąd nie przysporzyło mi to żadnych problemów i jest znacznie bezpieczniejsze niż alternatywa, ponieważ zapobiega uszkodzeniu któregoś z wierszy lub indeksy.

   Sukces tego również dość wyraźnie pokazuje, że problem stanowi wskaźnik PK, ponieważ jest to jedyna praktyczna różnica między tabelą tymczasową a tabelą stałą - oba rozpoczęły się od zerowych wierszy podczas testów wydajności.

Wniosek: nie zawracaj sobie głowy próbą kopiowania więcej niż około 100 000 wierszy do indeksowanej tabeli Oracle za pomocą ODP.NET. Upuść indeks (jeśli go nie potrzebujesz) lub „załaduj” wstępnie dane do innej (nieindeksowanej) tabeli.

Oto artykuł Oracle, który wyjaśnia, kiedy korzystne będzie użycie wkładki luzem, a kiedy nie. Ma również wgląd w to, co dzieje się na poziomie bazy danych.

http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_modes.htm