Re: Troublesome Batched Insert

I can't get my hands on an Exadata to test it, but I think that's just internal code that's updating the wrong counters. It's the sort of thing that has happened in the past when counters that could only be used for one purpose are hi-jacked by a mechanism doing something new.

HSC OLTP Space Saving 1729736

HSC OLTP Non Compressible Blocks 334
HCC block compressions completed 335
HSC OLTP Compressed Blocks 336

HSC Compressed Segment Block Changes 11539 HSC Heap Segment Block Changes 11538

HCC DML Conventional 10521
HCC block compressions attempted 1012
HSC OLTP partial compression 4

• 10521 + 1012 + 4 is pretty close to 11538/11539

HSC OLTP positive compression 1005
HSC OLTP inline compression 1005

• "double counting"

The coincidence make it look like a classic - call a new routine and count the calls, but the new routine calls an old routine that counts how many time it has been called but using a different counter.

I think I'd just create take the little script I had in that blog note you reference, change the table to be compressed for query high, and see what the stats said when you did the 50,000 error update.

Maybe I should download the ZFS Pillar OVM when I get a bit of time.

Regards
Jonathan Lewis

On Wed, 1 Mar 2023 at 14:25, Dominic Brooks <dombrooks_at_hotmail.com> wrote:

> Hi Jonathan,
>
> Thanks.
> Non-zero HSC/HCC related stats:
>
> HSC OLTP Space Saving 1729736
> HSC OLTP Compressed Blocks 336
> HSC Compressed Segment Block Changes 11539
> HSC Heap Segment Block Changes 11538
> HSC OLTP Non Compressible Blocks 334
> HSC OLTP positive compression 1005
> HSC OLTP inline compression 1005
> HSC OLTP partial compression 4
> HCC DML Conventional 10521
> HCC block compressions completed 335
> HCC block compressions attempted 1012
>
>
>
> Cheers,
>
> Dominic
>
>
>
> *From: *Jonathan Lewis <jlewisoracle_at_gmail.com>
> *Sent: *01 March 2023 14:18
> *To: *ORACLE-L <oracle-l_at_freelists.org>
> *Subject: *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/
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fjonathanlewis.wordpress.com%2F2017%2F03%2F07%2Fguesswork%2F&data=05%7C01%7C%7C1405a0643e8845a84c2f08db1a5fc896%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638132770968538437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=%2FK6PWZz9tGDhiA5oW0eh66iYHU0KXaXCdseQr%2FJNs2w%3D&reserved=0>
>
>
>
> 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://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fmikedietrichde.com%2F2018%2F11%2F28%2Fdirect-inserts-into-hcc-tables-may-be-slower-since-oracle-12-2%2F&data=05%7C01%7C%7C1405a0643e8845a84c2f08db1a5fc896%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638132770968538437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=rOOUwMF0Mfj0ZSb77M5qxfRYOto9eVN4K2hMwKmQEVw%3D&reserved=0>
>
>
> https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-16B7E3B2-DBDC-4547-8D7B-858065AD3D71
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.oracle.com%2Fen%2Fdatabase%2Foracle%2Foracle-database%2F12.2%2Fnewft%2Fnew-features.html%23GUID-16B7E3B2-DBDC-4547-8D7B-858065AD3D71&data=05%7C01%7C%7C1405a0643e8845a84c2f08db1a5fc896%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638132770968538437%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=kC%2FTFDibHM7DdphRQW5QdtA4n1%2F%2F3MIL3P%2FesOkOfxs%3D&reserved=0>
>
> *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