Re: Simple Insert - very slow

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_at_rsiz.com> 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_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sanjay Mishra (Redacted
> sender "smishra_97" for DMARC)
> *Sent:* Friday, May 17, 2019 5:32 PM
> *To:* dmarc-noreply_at_freelists.org; 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_at_gmail.com>
> 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_at_freelists.org>
> 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
>
>

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