Hello, 'a reader' - let me give to you 2 obs :
a. there is no such thing as 'sql_id of the entire pl/sql block' : each distinct SQL present in the PL/SQL block will receive a distinct sql_id... Of course, non-distinct SQLs already present in SQL cache will share one sql_id
b. I recommend that you use the basic instrumentation technique provided by Connor (I prefer to use the CLIENT_INFO column due to the larger size) , so your PL/SQL block will turn into :
BEGIN
--
dbms_application_info.set_client_info('starting setarraysize @' || to_char(sysdate, 'HH24:MI:SS'));
CNV_PKG.SetArrayFetchSize(2000);
--
dbms_application_info.set_client_info('starting trunc#1 @' || to_char(sysdate, 'HH24:MI:SS'));
truncutil.truncate_table(<tablename>);
--
dbms_application_info.set_client_info('starting trunc#2 @' || to_char(sysdate, 'HH24:MI:SS'));
truncutil.truncate_table(<table2>);
--
dbms_application_info.set_client_info('starting proc1 @' || to_char(sysdate, 'HH24:MI:SS'));
CNV_PKG.proc1;
--
dbms_application_info.set_client_info('starting proc2 @' || to_char(sysdate, 'HH24:MI:SS'));
proc2;
END;
And in another session connected to the database with any client tool, repeteadly query :
select client_info, otherusefulcolumnsinv$session FROM v$session where client_info is not null;
(replace V$ with GV$ if using RAC...
)
With this you will discover the main culprit : after that, do the same instrumentation inside the slow procedure, until you find the culprit command... It can be, maybe, the TRUNCATE command in the truncate_table procedure, or any other SQL command in proc1 OR proc2....
Regards,
J. Laurindo Chiappa