Re: weird insert behaviour
Date: Tue, 7 Feb 2012 16:11:25 -0800 (PST)
Message-ID: <1328659885.11022.YahooMailNeo_at_web114714.mail.gq1.yahoo.com>
Hi:
“- 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” Do you know the SID, or username, or module of your insert query session? If yes, can you use it to query ASH data and see all the sql id being kept tracked of during the insert. I bet you will see some other sql statements against tabpart$ generated by Oracle (use 10046 trace will be more accurate). ASH could be able to catch it if you insert session ran over 3 h. If you find it, check the TOP_LEVEL_SQL_ID in the ASH table and the top sql should be your insert query sql_id. Please feel free to try DBspeed http://www.dbspeed.com/product.htmland it will help your search in ASH.
I have seen this type of situation after we upgraded a database from 10g to 11g. We opened SR with Oracle asking what that sql on tabpart$ came from. It is pretty much related to the maintenance work on the partitions. “On weekend we made a move of historical monthly partitions…”
Basically, Oracle needs to do some background checking for all the partitions and the sql against tabpart$ could be a time-consuming task if the number of partitions is big. Collect stats on the data dictionary after partition maintenance will help because it ensures Oracle is using right execution plan on tabpart$.
Lei
http://www.dbspeed.com/index.html
From: Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Tuesday, February 7, 2012 1:57 AM
Subject: weird insert behaviour
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
--
http://www.freelists.org/webpage/oracle-l Received on Tue Feb 07 2012 - 18:11:25 CST