weird insert behaviour
Date: Tue, 7 Feb 2012 10:57:19 +0100
Message-ID: <4F30F57F.7020806_at_nordea.com>
Hi,
db 11.2.0.2.4 on aix
through the code we make a few hundreds thousands of inserts into a
large partitioned table.
It takes some time, but usually it is a stable process (ie. time is more
or less predictable).
Today the situation was as follows:
- process run as expected till around 7:20 - probably at that time it
reached around ~350.000 inserted rows (whole number for today was around
700.000, commit every 100.000)
- and then it looked like inserting stopped - the process performed
thousands of sequential reads on pk to that table. Monitoring showed
still ~350.000 executions of that insert query, the session still read
block by block from pk, and so through ~3h
- suddenly this "pause" for reading pk stoppped and the whole process
finished successfully, rows were inserted, everything fine again
On weekend we made a move of historical monthly partitions (ie. up to Dec 2010) to new tablespaces (with something like 'ALTER TABLE tbl1 move subpartition DATA200811_X TABLESPACE TS_ARCH_2008 UPDATE indexes;' clause) in order to restore some considerable amount of space after compression those historical partitions. On Monday there is no load to that table, so this administrative action may be of some meaning here.
The problem is we have no idea, no clue what happened during that time. When tried to enable 10046 there was even no attempt to create trace file, so obviously the code execution was within some "atomic" piece of performance.
Any idea, what could happen? where to look in similar case?
Regards
Remigiusz
-
--
Pole nakazi
Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>
pos : DBA at DIiUSI addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland phone : +48 58 667 17 43 mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedzibą w Gdyni, ul. Kielecka 2, 81-303 Gdynia, wpisaną do Rejestru Przedsiębiorców Krajowego Rejestru Sądowego pod numerem: 0000021828, dla której dokumentację przechowuje Sąd Rejonowy Gdańsk - Północ w Gdańsku, VIII Wydział Gospodarczy Krajowego Rejestru Sądowego, o kapitale zakładowym i wpłaconym w wysokości: 277.493.500,00 złotych, NIP: 586-000-78-20, REGON: 190024711--
http://www.freelists.org/webpage/oracle-l Received on Tue Feb 07 2012 - 03:57:19 CST