Skip to Main Content
  • Questions
  • Fluctuating counts on ROWID splits using DIY parallelism.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Russell.

Asked: April 28, 2021 - 12:32 pm UTC

Last updated: May 24, 2021 - 5:05 am UTC

Version: Various but confirmed 11.2.0.4

Viewed 100+ times

You Asked

Hi Tom, Chris, Connor and all,

I've been a user of your DIY Parallel solution for many years now as a way to pull data out of large unpartitioned tables in parallel to send to other non-Oracle databases or for file system archiving. I've ran into a situation recently at my last company and now my new company where the solution is acting different. I first noticed the change at my old company when the data warehouse I was supporting was moved to an Exadata system in OCI. The version of the database stayed the same, 11.2.0.4, making the only change being the hardware/datacenter move. What happened was during a row count validation of a table export based upon rowid splits the counts didn't match what was exported. Upon further investigation I found that the row count for a given rowid split was flutuating. Upon doing one count it would return a value and then the value would change upon subsequent counts. The count didn't just go up, it would go up and down between a set of three or four different values, making getting an accurate count imposssible.

The SQL I'd used to do this was of the formats:

SELECT COUNT(*) FROM X.XXX WHERE ROWID BETWEEN 'AAC2GBAFRAAD+c4AAP' AND 'AAC2GBAGIAAJ97wABX';

or
SELECT COUNT(*) FROM X.XXX WHERE ROWID > 'AAC2GBAFRAAD+c4AAP' AND ROWID <= 'AAC2GBAGIAAJ97wABX';


I can see where the counts could increment up if data is being added to the table but these were static tables and the count bounced back and forth between a few different sets of numbers. I'm now seeing this happen on other databases at my new job and I'm not sure what the cause of it is. I can't pin it down to a type of table or version or whether it's Exadata related or maybe something related to background work ASM is doing. I did a search to see if anyone else is having this occur to them without any luck. I'm seeing where lots of folks have implemented it but not where the row counts for a given split fluctuates. Do you have any idea what could be causing this and how to make it stop? It doesn't happen on all rowid splits for a table and it doesn't happen for all tables in a given database, it appears to be very random.

Thanks,
Russ

and we said...

There have been bugs in the past with Exadata and rowid ranges due to smart scan geting involved. (eg Bug 18684802)

I'd log a call with Support to cover off any thing like that first.

You could also try (not as a permanent fix but a diagnostic one)

alter session set cell_offload_processing=FALSE;

and see if it still repeats.

Rating

  (1 rating)

Comments

Workaround is successful!

Russ M, May 19, 2021 - 6:42 pm UTC

Hi Folks,

This is the original poster and I wanted to let you know that I tried setting CELL_OFFLOAD_PROCESSING to FALSE and that appears to have fixed the problem. I've been letting the various teams who admin these systems know so they can research.

Thanks a lot for the help!

Russ
Connor McDonald
May 24, 2021 - 5:05 am UTC

Thanks for getting back to us