Troublesome Batched Insert

Sorry – this email has evolved whilst typing it...

Trying to troubleshoot a batched conventional INSERT VALUES statement where performance has suddenly degraded significantly – from a single session inserting peak 3.5 million rows per hour to 750,000 per hour.

Batch insert is fired from a Java/Scala app.

All the additional time is spent on IO and DBAs and Oracle Support are engaged, etc. Their investigation is ongoing but they will probably point at the lack of flash cache on our quarter rack which we already knew but same applies to recent weeks where this has not been a problem

The insert statement in question has the /*+ ignore_row_on_dupkey_index */ hint in it And I’ve had some food for thought from here: https://jonathanlewis.wordpress.com/2017/03/07/guesswork/

Almost 100% sure that there are no duplicates involved.

There were some failed partition maintenance operations (PMO) over the weekend and it has been noted that both CLEANUP_ONLINE_PMO and PMO_DEFERRRED_GIDX_MAINT_JOB have been busy coalescing the two indexes on the table which is the target of the INSERT. These have been stopped for a number of hours in case they were contributing but of course they could have been working towards fixing the problem.

All the above is a lot of blurb when really I want to ask about an observation from Tanel’s snapper. I’ve been snapping the two session involved in the INSERT.

I happened to notice a stat for “HSC OLTP Space Saving” and there is no OLTP compression in use. The default partition compression is (currently) COMPRESS FOR QUERY LOW which is actually an anomaly because the conventional insert means that this should be uncompressed doesn’t it?

Then whilst composing this email, I read this: https://mikedietrichde.com/2018/11/28/direct-inserts-into-hcc-tables-may-be-slower-since-oracle-12-2/ https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-16B7E3B2-DBDC-4547-8D7B-858065AD3D71 Inserts on HCC Compressed Tables Can Be Significantly Slower in 12.2 Compared to 11.2 (Doc ID 2469912.1)

We haven’t suddenly gone to 19.16 but that’s interesting at least.

Still, why did I start writing thhis this, oh yes – is the stat “HSC OLTP Space Saving” not unique to OLTP compression? And if so, why might we see it when there is no OLTP compression anywhere?

So I wanted to see the compression type of a few rows via DBMS_COMPRESSION.GET_COMPRESSION_TYPE and it returns 64 which is

COMP_BLOCK NUMBER 64

Compressed row

What does 64 mean exactly?

If I check last week’s partitions they all say 1 which is what I would have expected.

So something has changed somewhere...

It’s not the insert – that code is exactly the same

It’s not the partition metadata or default compress as per DBA_PART_TABLES (def_compress_for and def_compression) – these are what they’ve always been.

Could the CLEANUP_ONLINE_PMO have done it?

Or something else? Apparently the server was patched over the weekend but only a GI patch.

Any thoughts on this rambling ?

Thanks

Cheers.
Dominic

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