RE: Can someone explain this scenario?

It's really hard to tell without much more data, but it can be any number of issues. It can be, for instance, waiting for a transaction slot in the block you are trying to insert into or, possibly, for a free list. It can even be a delayed cleanout issue. If the event is repeatable, go in with 10046, level 12 and do TRCA or, even better, send the data to Hotsos. You can also monitor gv$session_wait while doing that. It's 8.1.7.4 so you don't have "SEGMENT MANAGMENT AUTO" option. Are things like PCTFREE, PCTUSED, FREELISTS, INITTRANS and alike set properly? If the table is being inserted into, I imagine thay you left enough
room for an additional row or two in the block (PCTFREE)?

Mladen Gogala
Oracle DBA
Phone:(203) 459-6855
Email:mgogala_at_oxhp.com

-----Original Message-----
Sent: Sunday, May 11, 2003 10:07 PM
To: Multiple recipients of list ORACLE-L

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

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

   Privileged/Confidential information may be contained in this message.

          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.com

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).