| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Can someone explain this scenario?
This looks like a read-consistency issue.
When you select from the base table to insert into the history table, Oracle may be spending a lot of effort (and wait time) building the correct read-consistent view of the block you are trying to copy from.
Strangely the update and 'select for update' commands work differently in this respect.
Two checks:
1)
For a brief period, run with 10046 set to level 8
on one of the processes - I think you will find
that the trace file shows the number of CR gets
for each row steadily increasing as more and more
work is needed to get back to the correct SCN,
and you may also see lots of buffer busy waits
and latch free waits as Oracle tracks through
undo blocks.
2)
select dbarfil, dbablk, count(*) from x$bh -- as sys
group by dbarfil, dbablk
having count(*) > 5;
(Check the column names. If you have more than 1023 tablespaces you will also need the tablespace number)
I suspect you will find that there are some blocks with large numbers of copies in the buffer. (It isn't really supposed to be possible, but it happens. The situation can be aggravated by having an unnecessarily large buffer - but it's an awkward thing to balance. )
Your solution is the appropriate one.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Denmark__May 21-23rd ____Sweden___June ____Finland__September ____Norway___September
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2 ____Estonia___June 4th - 6th ____Australia_June 18th - 20th (Perth) ____Australia_June 23rd - 25th (t.b.a) ____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> Hi List,
>
> I have encountered a performance problem (and since solved it) but I
was
> hoping someone could explain exactly why it happened. Let me
explain:
>
> Effectively, insert 1 row into a table and then insert that same row
into a
> "history" table. This process was completed as a single transaction
(along
> with some other irrelevant statements) and then the results were
commited
> before looping around and doing the process for the next item of
data. The
> two key steps were:
> 1) Insert a row into a ~1million row table using "insert into
table1...
> values..."
> 2) Insert a rows into a ~100million row table using "insert into
table2...
> select... from table1 where col1 = constant", col1 has a unique
index
> defined.
>
> The tables and indexes were all analyzed and accurate. The second
insert
> was taking around ~15 seconds to complete, where in a smaller
environment
> the performance was fine. I added a hint to the select component
and this
> reduced execution to ~3seconds. Eventually I duplicated the first
> statement and reduced execution time to a fraction of a second -
what I was
> originally expecting.
>
> We often perform this type of task, however the first statement is
usually
> an update of an existing row. The performance has never been
noticed as a
> problem before - we typically run in the "hundreds or thousands of
records
> per seconds" range performing these updates depending on the
complexity.
>
> My best guess is that I was getting some kind of wait, perhaps the
indexes
> of table1 were still being updated when I was attempting the scan
for the
> second insert? This would explain why having the first statement as
an
> update doesn't cause the problem - the unique index columns are
never
> updated.
>
> Unfortunately I don't have any time currently to investigate the
problem
> further - I simply changed the script as mentioned, but I would love
to
> understand if this is expected behaviour or not. Do any guru's out
there
> have some insight? For the record, we are running 8.1.7.4 on a Sun
E4500.
>
> Thanks in advance,
>
> Mark
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon May 12 2003 - 01:26:38 CDT
 |
 |