This is a brief reminder for those using intensively Oracle dbms_scheduler package to schedule a launch of a stored PL/SQL procedure. Recently I was investigating a wide range performance problem via a 60 minutes AWR snapshot until the following action that appears in the TOP SQL ordered by Gets has captured my attention:
SQL ordered by Gets
Buffer Gets | Executions | Gets per Exec | %Total | Elapsed Time (s) | %CPU | %IO | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|---|---|
858,389,984 | 2 | 429,194,992.00 | 89.58 | 3,606.79 | 97.2 | 1.9 | 6cjn7jnzpc160 | DBMS_SCHEDULER | call xxx_PA_REDPC.P_EXPORT_DA… |
542,731,679 | 32,021 | 16,949.24 | 56.64 | 3,201.40 | 98.4 | .6 | 4bnh2nc4shkz3 | w3wp.exe | SELECT WAGD_ID FROM S1 |
It is not the enormous Logical I/O done by this scheduled stored procedure that has retained my attention but it is the appearance of the call statement in the corresponding SQL Text.
Where does this come from?
Let’s get the DDL of the corresponding program
SELECT dbms_metadata.get_ddl('PROCOBJ','XXX_PARSE_MESSAGE_PRG', 'SXXX') from dual;
Which gives this
BEGIN DBMS_SCHEDULER.CREATE_PROGRAM (program_name => 'XXX_PARSE_MESSAGE_PRG' ,program_type => 'STORED_PROCEDURE' ,program_action => 'XXX_PA_REDPC.P_EXPORT_DA.P_xxxx' ,number_of_arguments => 0 ,enabled => FALSE ,comments => NULL); COMMIT; END; /
I have arranged a little bit the generated DDL script for clarity.
Now things become clear.
When you define your program using
program_type => 'STORED_PROCEDURE'
Then your job will be executed using the SQL command call
call XXX_PA_REDPC.P_EXPORT_DA..'
This is in contrast to when you define your program using
program_type => 'PLSQL_BLOCK'
which has the consequence of making your job being executed using an anonymous PL/SQL block
BEGIN XXX_PA_REDPC.P_EXPORT_DA.. END;
And now the question: how would you prefer your scheduled stored procedure to be executed?
- via the SQL call statement
- via the anonymous PL/SQL block
Well, after a brief research on My Oracle support I found a bug that seems closely related to it
DBMS_SCHEDULER Is Suppressing NO_DATA_FOUND Exceptions for Jobs that Execute Stored Procedures (Doc ID 1331778.1)
In fact, there is one fundamental threat when opting for the call statement. Consider this
SQL> create or replace procedure p1 as begin insert into t values (1,2); raise no_data_found; commit; end; / Procedure created. SQL> select count(1) from t; COUNT(1) ---------- 0
I am going to call this procedure using the call statement which normally will raise a no_data_found exception and the inserted data into table t will be rolled back
SQL> call p1(); Call completed. SQL> select * from t; N1 N2 ---------- ---------- 1 2
Despite the raise of the no_data_found exception inserted data has been committed and the exception ignored. This will not have happened if I have managed to execute the stored procedure using an anonymous PL/SQL block as shown below:
SQL> truncate table t; Table truncated. SQL> begin p1(); end; / begin * ERROR at line 1: ORA-01403: no data found ORA-06512: at "XXX.P1", line 4 ORA-06512: at line 2 SQL> select count(1) from t; COUNT(1) ---------- 0
So, please be informed 🙂
Hi,
Please post db version ?
Kais.
Comment by kais — April 10, 2014 @ 5:23 pm |
It is 11.2.0.3
Best regards
Comment by hourim — April 10, 2014 @ 6:45 pm |
Hi Mohamed. Very interesting & useful observation. The issue looks to be with the “CALL” clause. I wonder why oracle support is considering this to be an issue with DBMS_SCHEDULER.
Comment by rajiviyer — April 14, 2014 @ 7:37 am |