Re: Simple Insert - very slow

From: Sanjay Mishra <"Sanjay>
Date: Sun, 19 May 2019 20:44:09 +0000 (UTC)
Message-ID: <>


Here is the detailsSQL> select * from table(dbms_xplan.display_cursor('xby5opfkfllchy',1,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  xby5opfkfllchy, child number 1-------------------------------------INSERT  INTO"SYSADMIN"."TXN_CUSTOM_PROV_DTL" ("TXN_ID","PROV_ID","SEQID","PORTABLE_ID","SOR_CODE","STATE_ID","FACT_TXN_DTL_ID","FOR_VAL_NO","SPARE_ID1","SPARE_ID2","SPARE_ID3","VERSION","STATUS_FLAG","TXN_UPD_DT","TXN_CRT_DT") VALUES (:1   ,:2   ,:3   ,:4   ,:5   ,:6  ,:7   ,:8   ,:9   ,:10  ,:11  ,:12  ,:13  ,:14  ,:15  )
--------------------------------------------------------| Id  | Operation                | Name                |--------------------------------------------------------|   0 | INSERT STATEMENT         |                     ||   1 |  LOAD TABLE CONVENTIONAL | TXN_CUSTOM_PROV_DTL |--------------------------------------------------------

-----   - cpu costing is off (consider enabling it)   - Warning: basic plan statistics not available. These are only collected when:       * hint 'gather_plan_statistics' is used for the statement or       * parameter 'statistics_level' is set to 'ALL', at session or system level

TxSanjay On Sunday, May 19, 2019, 1:37:27 PM EDT, Rajeev Prabhakar <> wrote:  

Could you pls share the output from the following : select * from table (dbms_xplan.display_cursor()); Rajeev 

On May 19, 2019 at 12:35 PM, <Sanjay Mishra> wrote:

Thanks for the update. ==> Simple Insert means conventional load using Insert into table values(bind values) and so neither Append or direct method is in use==> Table is partitioned with 5 partitions and data is going on in one partition==> Table is Partition with Advance compression and the only primary key is global partitioned and not compressed.==> Data is loaded with Informatica ETL load and Parallel DML is not enabled==> Tablespace is Bigfile tablespace with 12.2 and Autoextend on and no max size limited to grow to allowed max size. TxSanjay

    On Sunday, May 19, 2019, 12:20:52 PM EDT, Matt Shelton <> wrote:  

 #yiv8839266832 -- filtered {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;}#yiv8839266832 filtered {panose-1:2 4 5 3 5 4 6 3 2 4;}#yiv8839266832 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv8839266832 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv8839266832 p.yiv8839266832MsoNormal, #yiv8839266832 li.yiv8839266832MsoNormal, #yiv8839266832 div.yiv8839266832MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New;}#yiv8839266832 a:link, #yiv8839266832 span.yiv8839266832MsoHyperlink {color:blue;text-decoration:underline;}#yiv8839266832 a:visited, #yiv8839266832 span.yiv8839266832MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv8839266832 p.yiv8839266832msonormal, #yiv8839266832 li.yiv8839266832msonormal, #yiv8839266832 div.yiv8839266832msonormal {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:New;}#yiv8839266832 span.yiv8839266832EmailStyle18 {color:#1F497D;}#yiv8839266832 .yiv8839266832MsoChpDefault {font-size:10.0pt;}#yiv8839266832 filtered {margin:1.0in 1.0in 1.0in 1.0in;}#yiv8839266832 div.yiv8839266832WordSection1 {}#yiv8839266832 Please explain what you mean by simple insert you are running?  What type of compression is it HCC? Is it a direct load or conventional load?  What tool is being used to run the insert? Are you loading into one tablespace or multiple?  What type of autoextend on the segments is happening?


Have you tried a simple insert /* append* /  as select * from table?


My recommendation on Exadata hardware most the of problems is not understanding the development side or how Exadata features work.


Thank You



From: [] On Behalf Of Shane Borden Sent: Saturday, May 18, 2019 1:59 PM
Cc: Mark W. Farnham; Rakesh Ra;; Oracle-L Freelists Subject: Re: Simple Insert - very slow


I would check the PCTFREE setting on the partition.   I’ve had instances where compressing a partition altered the PCTFREE setting to 0.  

Shane Borden

Sent from my iPhone

On May 18, 2019, at 12:49 PM, Sanjay Mishra (Redacted sender "smishra_97" for DMARC) <> wrote:



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.





On Friday, May 17, 2019, 11:10:50 PM EDT, Rakesh Ra <> wrote:



Hi Sanjay,


Is there any compression enabled for the table? What is the value of compress_fot in dba_tables??




On Sat, May 18, 2019, 03:58 Mark W. Farnham <> 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.




From: [] On Behalf Of Sanjay Mishra (Redacted sender "smishra_97" for DMARC) Sent: Friday, May 17, 2019 5:32 PM
To:; Andy Sayer Cc: Oracle-L Freelists
Subject: Re: Simple Insert - very slow




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 





On Friday, May 17, 2019, 10:29:45 AM PDT, Andy Sayer <> 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 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




On Fri, 17 May 2019 at 16:47, Sanjay Mishra <> 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      

Received on Sun May 19 2019 - 22:44:09 CEST

Original text of this message