Re: Simple Insert - very slow

 Mark
I tried to check with a non-index column where rownum < 2 and it came very fast. checking some other details suggested by friends. Somehow also found that insert also on some of the other big tables also showing the same behavior. TxSanjay

    On Friday, May 17, 2019, 6:28:51 PM EDT, Mark W. Farnham <mwf_at_rsiz.com> wrote:  

 #yiv1192446998 #yiv1192446998 -- _filtered #yiv1192446998 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv1192446998 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv1192446998 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv1192446998 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv1192446998 #yiv1192446998 p.yiv1192446998MsoNormal, #yiv1192446998 li.yiv1192446998MsoNormal, #yiv1192446998 div.yiv1192446998MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New;}#yiv1192446998 a:link, #yiv1192446998 span.yiv1192446998MsoHyperlink {color:blue;text-decoration:underline;}#yiv1192446998 a:visited, #yiv1192446998 span.yiv1192446998MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv1192446998 span.yiv1192446998EmailStyle17 {color:#1F497D;}#yiv1192446998 .yiv1192446998MsoChpDefault {font-size:10.0pt;} _filtered #yiv1192446998 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv1192446998 div.yiv1192446998WordSection1 {}#yiv1192446998 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