Shane
It is 10 on all partitions. Moreover, data was just imported only a month back
and the index was created when inittrans was increased and reorganization was
completed to reflect the inittrans setting. The system was fine after that and
the same amount of DML was processed in all last 4 weeks.
TxSanjay On Saturday, May 18, 2019, 1:59:44 PM EDT, Shane Borden
<sborden76@xxxxxxxxx> wrote:
I would check the PCTFREE setting on the partition. I’ve had instances where
compressing a partition altered the PCTFREE setting to 0.
Shane Bordensborden76@xxxxxxxxx
Sent from my iPhone
On May 18, 2019, at 12:49 PM, Sanjay Mishra (Redacted sender "smishra_97" for
DMARC) <dmarc-noreply@xxxxxxxxxxxxx> wrote:
Rakesh
This table is partitioned with five List Partition and Compression is enabled
as Advanced on the partition level. It was all of sudden started behaving with
no change on App/Db side. Working also with Storage team to see if anything is
changed on Exadata side like patching etc.
TxSanjay
On Friday, May 17, 2019, 11:10:50 PM EDT, Rakesh Ra <rakeshra.tr@xxxxxxxxx>
wrote:
Hi Sanjay,
Is there any compression enabled for the table? What is the value of
compress_fot in dba_tables??
Regards,RRA
On Sat, May 18, 2019, 03:58 Mark W. Farnham <mwf@xxxxxxxx> wrote:
A quick sanity check is if append is “fast” for one row and conventional is
“slow” for one row, then you have one of several possible problems that can
involve the ASSM free bitmap finding a block to shove something into. This is
sometime associated with an “empty front” problem which can be identified by
selecting a non-indexed column from the table where rownum < 2, and observing
that your session stats read many blocks to get the first row.
Your mileage may vary. A really horrible result may indicate that that fastest
path to a solution is a rebuild of a partition or table, but please don’t get
on a treadmill of rebuilding things that don’t need to be rebuilt.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC)
Sent: Friday, May 17, 2019 5:32 PM
To: dmarc-noreply@xxxxxxxxxxxxx; Andy Sayer
Cc: Oracle-L Freelists
Subject: Re: Simple Insert - very slow
Andy
Thanks for the update. Based on the Obj Id with trace on the Insert process, it
is Table itself reported. This table is recently exported and imported and
Index was only created less than a month back. Surprisingly not able to
understand as to why it is doing cell single block physical read to the table.
I will also check your link and do more troubleshooting
Tx
Sanjay
On Friday, May 17, 2019, 10:29:45 AM PDT, Andy Sayer <andysayer@xxxxxxxxx>
wrote:
First step would be to see which object is responsible for all the IO, in my
experience it’s usually down to one index.
Have a look at
https://ctandrewsayer.wordpress.com/2017/02/16/not-all-indexes-are-created-equally/
for how I’ve tackled this before.
It might be you’ve got a “bad index” or it might be that you’ve got an okay
index that turned “bad”.
We had a case where an index exploded and was purely ITL for the first few
thousands leaf blocks, we reset the sequence (told to by support) which meant
that all our inserts had to scan those few thousand leaf blocks to insert one
row.
First step is identifying the object
Andy
On Fri, 17 May 2019 at 16:47, Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx> wrote:
Hi All
We have a table with around a billion rows with one Primary Key. Number of
columns in the table are fewer than 10 and all are numbers data type.
Application is doing few million inserts but running very slow. It is Exadata
with two node and 10CPU and good SGA (bufer Cache 10G). Waits are coming
heavily only for "cell single block physical read" for Insert process. While
doing SQL Monitor shows it is doing high IO like more than 100G for simple
Insert. Oracle version is 12.2.
Any suggestions as what can be the reason for this wait event in this scenario.
TIA
Sanjay
