Re: Insert query slowness

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