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.

Here’s a demo, first the setup:

create table as_dpe_number
(pk_col number primary key
,data_col varchar2(100)
);
create sequence as_dpe_number_seq;
declare
 sString varchar2(100) := rpad('x',100,'x');
begin
 for i in 1..100
 loop
 insert into as_dpe_number
 select as_dpe_number_seq.nextval, sString from dual connect by rownum <=10000;
 commit;
 end loop;
end;
/
exec dbms_parallel_execute.create_task('AS_TEST_NUMBER')

I have a table with 1,000,000 rows (which isn’t that much, but this is a demo), and I have setup my parallel execute task. Now I call the chunking procedure:

set timing on
ANDY@pdb1>exec dbms_parallel_execute.create_chunks_by_number_col('AS_TEST_NUMBER',user,'AS_DPE_NUMBER','PK_COL',1000)

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.47

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)

select ash.sql_id, substr(s.sql_text,1,52) sql_text, count(*)
from v$active_session_history ash
left join v$sql s
 on ash.sql_id = s.sql_id
 and ash.sql_child_number = s.child_number
where ash.session_id = sys_context('userenv','sid')
and ash.sample_time >= sysdate-interval'10'second
group by ash.sql_id, substr(s.sql_text,1,52)
order by count(*);
SQL_ID         SQL_TEXT                                             COUNT(*)
------------- ----------------------------------------------------- ----------
83b0ca0j15319 INSERT INTO DBMS_PARALLEL_EXECUTE_CHUNKS$( CHUNK_ID,          1
8mdxrp0r8dhx2 select min("PK_COL"), max("PK_COL")                           4

You might automatically recognize the problem immediately if you’ve run into it before. But if we were to look at the plan for that SQL that’s taking the time:

ANDY@pdb1>select * from dbms_xplan.display_cursor(sql_id=>'8mdxrp0r8dhx2');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID 8mdxrp0r8dhx2, child number 0
-------------------------------------
select min("PK_COL"), max("PK_COL") from
"ANDY"."AS_DPE_NUMBER"

Plan hash value: 2623793653

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 546 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C0013861 | 860K| 10M| 546 (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Note
-----
 - dynamic statistics used: dynamic sampling (level=2)

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

select (select min(pk_col) from as_dpe_number) , (select max(pk_col) from as_dpe_number) from dual;

Instead? Well, we get a plan like:

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 298820326

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| SYS_C0013861 | 1 | 13 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 13 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| SYS_C0013861 | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Note
-----
 - dynamic statistics used: dynamic sampling (level=2)

Those MIN/MAX scans mean that we are jumping straight to the beginning and straight to the end of the index, easy!

So how do we get dbms_parallel_execute to use this? We can’t change the SQL it runs, but we can change the table it runs against:

exec dbms_parallel_execute.drop_task('AS_TEST_NUMBER')
create table as_dpe_number_minmax2
as
select min(pk_col) pk_col from as_dpe_number
union all
select max(pk_col) from as_dpe_number
/

Table created.

Elapsed: 00:00:00.05

exec dbms_parallel_execute.create_task('AS_TEST_NUMBER')
exec dbms_parallel_execute.create_chunks_by_number_col('AS_TEST_NUMBER',user,'AS_DPE_NUMBER_MINMAX','PK_COL',1000)

Elapsed: 00:00:00.85

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.

 

Advertisements