Re: Insert query slowness

You should increase the batch size, 500 is the rule of thumb, but I would test higher numbers still. It should be possible to increase the batch size.  I never did configure Informatica myself, but I heard it is difficult to configure. Do also check your database driver, this might limit you on the batch size.
You could use the APPEND_VALUES hint, if in Informatica or via a sql_patch. There should be a direct database mode for informatica, but it comes with an extra charge.

Is there any trigger on one of the tables? I might help to have a trace.

Thanks

Lothar

Am 01.11.2022 um 20:35 schrieb yudhi s:
> Trying to see what all can be done to improve this batch insert
> performance. Apart from making the index unusable and loading the
> table. Is there any other option? Currently we have ofe 11.2, will it
> be making it 19.0 will help it anyway?
>
> On Mon, 31 Oct, 2022, 12:05 pm yudhi s, <learnerdatabase99_at_gmail.com>
> wrote:
>
> Thank you Andy. Actually the inserts are currently happening in
> batch only from ETL tool informatica , with a batch size of 60 to 
> 70. But as I mentioned each if those batch used to take <5milli
> sec which is now taking double the time and the dba_hist_sql_stat
> showing it's mostly the cpu time component which has been
> increased for each of those batches. Do you mean by increasing the
> batch size further we may see benefits? But yes, app development
> team stating they don't have control the batch size but
> informatica does it's own.
>
> On Sun, 30 Oct, 2022, 10:24 pm Andy Sayer, <andysayer_at_gmail.com>
> wrote:
>
> You will potentially make a lot of that time back by
> converting to using array binds for your large inserts. Once
> you do that, make sure you compare rows processed to cpu time
> rather than executions to cpu time. Your legitimate single
> value processes won’t change but they’re only taking 10ms,
> which isn’t going to be noticeable.
>
> Thanks,
> Andy
>
> On Sun, Oct 30, 2022 at 2:50 AM, yudhi s
> <learnerdatabase99_at_gmail.com> wrote:
>
> Thank you so much Dominic and Malden.
>
> We raised a case with Oracle. It appears post 19.15 db
> patch and the grid patch, some additional functions get
> executed behind the scene as below and thus overall cpu
> times becomes higher for the data load queries.
>
> Also there appears to be a hardware issue with one cell
> server in this full rack system and also it's a X3 machine
> with image 19.2.9.0 with write-though flash cache mode, so
> we may have to change it to write back to help the write
> queries/DML performance.
>
> _at_
> 0x14cfb24d<-ksdxcb()+2254<-sspuser()+213<-__sighandler()<-write()+16<-sdbgrfuw
> _at_
> f_write_file()+59<-sdbgrfwf_write_file()+66<-dbgtfdFileWrite()+714<-dbgtfdFile
> _at_
> AccessCbk()+848<-dbgtfPutStr()+576<-dbktPri()+144<-ksdwrf()+640<-ktsp_dl_auto_
> _at_
> align()+1382<-ktspfsrch_array()+3301<-ktspscan_bmb()+3004<-ktspgsp_main()+1060
> _at_
> 6<-kdzh_setup_blkarray()+455<-kdzhailseb_dml()+1904<-kdzhcl_dml()+206<-kdt_hcc
> _at_
> _flush()+258<-kdtFlushBuf()+20786<-qerltcFlushKdtBuffer()+452<-qerltcBufRow()+
> _at_ 87<-qerltcKdtBufferedInsRowCBK()+283<-qerltcLoadSta
>
> On Sun, 30 Oct, 2022, 3:31 am Mladen Gogala,
> <gogala.mladen_at_gmail.com> wrote:
>
> On 10/28/22 18:34, yudhi s wrote:
>>
>> Hello Experts, It's version 19.15 of the oracle. But
>> the OFE has still been set as 11.2.0.4. We are seeing
>> after our infra team applied april 2022 database and
>> grid patch(below is the exact detail), almost all of
>> the conventional batch insert queries are running
>> slower(e.g. ~5ms/execution before patch vs
>> 10ms/execution now). The main time component for this
>> query is shown to be "CPU time" in dba_hist_sqlstat,
>> which has been increased. However, when checking the
>> wait event pattern from dba_hist_system_event, the
>> response time , we see for 'cell single block
>> physical read' wait event the response time , has
>> been increased up from <1ms before patch to ~10ms+
>> now after patch. Also we are seeing a new wait event
>> "cell single block physical read: flash cache" which
>> was not there before the patch. Want to know if
>> anyone has encountered such an issue? And this
>> symptom we are seeing across multiple databases in
>> the same cluster.There is nothing much in the insert
>> queries. The insert queries, looks like "Insert into
>> tab1(c1,c2,....).. values(c1,c2,....); "
>>
>> 33815596;OCW RELEASE UPDATE 19.15.0.0.0 (33815596)
>> 33806152;Database Release Update : 19.15.0.0.220419
>> (33806152) 33803476 - Grid patch
>>
>> Regards
>>
>> Yudhi
>>
> Hi Yudhi,
>
> If the duration of 'cell single block physical read'
> has gone up 10 times on your Exadata machine after
> upgrade, you should open a case with Oracle Support.
> Did you only patch Oracle RDBMS or did you also apply
> Exadata firmware patch? You can trace the particular
> insert that got slower by executing someting like
> "ALTER SYSTEM SET EVENTS="sql_trace[sql: <SQL_ID>]
> level=12'". That should produce a trace file for every
> process executing the given SQL_ID. You can combine
> those trace files by trcsess and analyze by using
> orasrp (http://oracledba.ru/orasrp/). That should give
> you an idea of what exactly are your inserts waiting for.
>
> However, to reiterate, what you described warrants
> opening a case with Oracle Support. Out of curiosity,
> what version of Exadata do you have? Do you have one
> of those new monsters with Mellanox (Nvidia) 100Gb/sec
> adapters, RDMA and Optane cache? I wonder what effect
> will Intel killing off Optane have on the Exadata
> platform? The "persistent cache" was actually Intel
> Optane. Of course, those last few sentences were just
> me thinking aloud, that doesn't have much to do with
> your problem.
>
> Regards
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
> https://dbwhisperer.wordpress.com
>

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