Re: weird insert behaviour
- From: Lei Zeng <leizeng2003@xxxxxxxxx>
- To: "remigiusz.sokolowski@xxxxxxxxxx" <remigiusz.sokolowski@xxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 7 Feb 2012 16:11:25 -0800 (PST)
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@xxxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxx>
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--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
