Thanks Harmandeeep & Andy. Yes I am already checking with Storage on the
CDOT filer performance and it looks to me like NetApp filer is taking more
load what it can take up.
Thanks Mladen,
These inserts are being fired by Oracle GoldenGate replicat process. So
need to check how we can enable parallel DML's for those and othe provided
suggestions.
Regarding system statistics , does the system statistics affect some
queries only? In my case I observe for all the GoldenGate related SQLID's i
see the CPU costing if OFF. However, the queries coming from JDBC side I do
see the stats.
Regards,
Rakesh RA
On Fri, Dec 21, 2018 at 1:32 PM Andy Sayer <andysayer@xxxxxxxxx> wrote:
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@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
