Oracle SQL/PL/SQL performance & goodies

Slow dbms_parallel_execute.create_chunks_by_number_col ?

I have to apologise for the lack of content in the past few months, I’ve been stretching myself a little too thin. Some of you may know I’m trying to sell my apartment and move to the West Midlands, that’s turned out to be very stressful too! Anyway, I’ve found some time for this quick post so let’s get on with it.

I was using the dbms_parallel_execute package today to chunk up a reconciliation job (comparing the existence of primary key values between two tables in a DW). The only way I could think of comparing the columns without running out of temp (the tables are Tbs in size) was to batch up the comparison with value ranges. The easy way to chunk up my number column was using this dbms_parallel_execute procedure, but I ran into a performance problem pretty quickly.

So that took 5.47 seconds to split my 1,000,000 rows into 1,000 batches of 1,000 rows. What’s the harm? Well if we look at where that 5 seconds went, using a quick n’ dirty query against v$active_session_history (and you must have paid for the diagnostics pack licence if you want to use it)

We’re reading the entire index even though we only need to know the first and last values, this is because the min and the max are being attempted in the same subquery. You can appreciate how long that might take with a table that’s 1 Tb! Let’s see what happens if we do

I should note that this only works because instead of the package going through all the values in your table, it just goes to the minimum and maximum values and creates uniform width chunks. I.e. if you have values 1, 5,6,7,8,9,10 and wanted two chunks, you’d get a chunk with 1-5 and a much more dense chunk of 6 to 10.

Hopefully this comes in handy when you need it, as is a nice reminder about the problem with select min & max queries.