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@xxxxxxxxx>
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.
@
0x14cfb24d<-ksdxcb()+2254<-sspuser()+213<-__sighandler()<-write()+16<-sdbgrfuw
@
f_write_file()+59<-sdbgrfwf_write_file()+66<-dbgtfdFileWrite()+714<-dbgtfdFile
@
AccessCbk()+848<-dbgtfPutStr()+576<-dbktPri()+144<-ksdwrf()+640<-ktsp_dl_auto_
@
align()+1382<-ktspfsrch_array()+3301<-ktspscan_bmb()+3004<-ktspgsp_main()+1060
@
6<-kdzh_setup_blkarray()+455<-kdzhailseb_dml()+1904<-kdzhcl_dml()+206<-kdt_hcc
@
_flush()+258<-kdtFlushBuf()+20786<-qerltcFlushKdtBuffer()+452<-qerltcBufRow()+
@ 87<-qerltcKdtBufferedInsRowCBK()+283<-qerltcLoadSta
On Sun, 30 Oct, 2022, 3:31 am Mladen Gogala, <gogala.mladen@xxxxxxxxx>
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-1217https://dbwhisperer.wordpress.com
