Re: Tuning Self-referencing Inserts

I think we need to take a few steps back.

It’s hard to explain why such a statement would cause noticeable issues if the table is really not that large. A simple insert into <target> statement using a select from <target table>, won’t take a silly amount of time - it’s just the time to read the table blocks using a full tablescan then the time to update the indexes which might be some overhead but no different to any other insert statement of the same volume.

Perhaps this this insert is within some loop? Perhaps the self-reference is written so that a silly execution plan is being used. It would be great if you could share the execution plan you are seeing and the SQL being executed. Is there PL/SQL involved?

One other thing that springs to mind that could have an impact is DML error logging (it sounds like a situation where unique keys could give you errors?)

As for library cache pins, this is unusual for an insert/select statement. Things like running a PL/SQL procedure which another session is trying to recompile while another session is executing it will bring around this behaviour. Perhaps there is a function being called by your statement. Again, what exactly are you seeing? This is a block so you should be able to investigate what the blocking session is doing by checking v$session.

Hope this helps,
Andy

On Fri, 30 Nov 2018 at 18:32, MacGregor, Ian A. <dmarc-noreply_at_freelists.org> wrote:

> The problem is with a PeopleSoft statement which is based on a select
> statement which references the table being inserted. The problem stems
> from having to read and build the indexes of the table which is not that
> large. The buffer gets are extremely high.
>
>
> Is there generic answer to this problem. Would it help to rebuild with
> a higher percent free in an attempt to have few rows per block so as to
> lessen contention.
>
>
> Also for library cache pins I don't understand how to reads the p3raw
> value. I think the problem primarily lies with the maintenance m of the
> unique index bit I am. not 100% confident.
>
>
> Ian A. MacGregor
> SLAC National Accelerator Laboratory
> Computing Division
> To offer the best IT service at the lab and be the IT provider of choice.
>

--
http://www.freelists.org/webpage/oracle-l