Looking for assistance on a performance issue in Oracle 12c R2 - on prem installation. I have a simple CSV file ( id, name ) with 4 rows of data. I created an external table based on this file. I use the external table to load another simple table. When I run the insert statement as an anonymous SQL block, it executes in less than a second. When I run the exact same code as a store procedure, it executes in 20 seconds. I traced both scenarios and compared the results and the difference is in the wait event: Px Deq: Execute Reply. My understanding is that this event is waiting for slave processes to respond.
I've tried using a 'noparallel' hint in the insert - select statement and altered the external table to be 'noparallel'. Performance improved marginally but still takes 10 seconds to load 4 rows. My question is, why would executing the same code within a stored procedure use parallelism when the anonymous PL/SQL block does not? My guess is that it may be related to an initialization parameter but I'm not sure which one.
Any help would be greatly appreciated.
Thanks.