Re: Huge plain inserts response time bad with db file sequential read wait event.

I agree with Harmandeep, the average time for your dB file sequential read is way too high for a modern system. You could be looking at about 1% of that! I would start by looking at what’s going on in your storage system - is it servicing more requests than it has the capacity for? Is it having to do something slower because some of your hardware has gone bad?

Talk to your storage engineers, find out what speed their devices are supposed to be able to random reads at and let them know what you’re getting.

It’s hard to tell if you’re doing more reads than one would expect because this could be an array based insert. It’s GG so I would expect it to be fairly sensible doing that. How many rows are being inserted per execution? You can estimate by checking executions and rows processed in v$sql.

Are you writing to the same storage that you’re replicating from? It would be strange if your source data was being written faster than your replicated data (which should be using effecient batching).

Hope this helps,
Andy

On Fri, 21 Dec 2018 at 07:37, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

>
> On 12/20/2018 12:48 PM, Rakesh Ra wrote:
> >
> >
> > Can someone guide as to,
> >
> > a) Why INSERT is spending more time on db file sequential reads for PK
> > index?
>
> Because insert needs to check whether the row being inserted already
> exists. To do that, Oracle needs to check the primary key index,
> therefore you get db file sequential reads. If the table is large, the
> PK is likely to be large as well and you will need a lot of reads. Root
> block and a couple of branch blocks will likely be cached, but
> eventually you will encounter the situation in which you need to read
> both the branch blocks and leaf blocks to check your data. There are
> various methods to remedy that, one of which is enabling parallel DML.
> Another method of speeding up inserts is to use compress for all
> operations. The third is to use hybrid columnar compression, if
> possible. You may even try using IOT. It's all done using the proven
> trial and error method.
>
> > b) Why CPU costing is showing as off in the execution plan for OGG
> > queries only?
>
> Because you haven't collected system statistics. CPU costing is off when
> optimizer doesn't have an access to system statistics.
>
>
> >
> > Regards,
> > RRA
> --
> Mladen Gogala Database Consultant Tel: (347) 321-1217
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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