Re: Troublesome Batched Insert

It would be easy to believe that someone just made a small coding mistake when selecting which counter to update.

Do you see any "HCC" statistics in the snapshot, in particular was there a non-zero value for "HCC load conventional CUs query low" but generally any "HCC load conventional ..." stats that might suggest that your "HSC OLTP Space Saving" fills a gap left by a missing "HCC load conventional ..." statistics.

Regards
Jonathan Lewis

On Tue, 28 Feb 2023 at 20:24, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> Hi,
>
> 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