So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday.
I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep.
Setup
The table holding my SQL statements
CREATE TABLE batchsql
( batchid VARCHAR2(64) NOT NULL
, sqlid INTEGER NOT NULL UNIQUE
, sqltext varchar2(4000) NOT NULL
, starttime DATE
, endtime DATE
, sqlcode INTEGER
, sqlerrm VARCHAR2(256)
, error_stack VARCHAR2(4000)
, error_backtrace VARCHAR2(4000)
);
would be nicer to make the ‘sqlid’ column a compisone unique key with the batchid (harmless off course).
The anonymous PL/SQL block that will loop and execute these statements
DECLARE
v_batchid batchsql.batchid%TYPE;
i_sqlid batchsql.sqlid%TYPE;
v_sqltext batchsql.sqltext%TYPE;
i_sqlcode PLS_INTEGER;
v_sqlerrm VARCHAR2(256);
i PLS_INTEGER;
BEGIN
v_batchid := 'DB01_ANALYZE_20080118';
i := 0;
LOOP
-- get statement that nobody is working on
SELECT sqlid,
sqltext
INTO i_sqlid,
v_sqltext
FROM batchsql
WHERE batchid = v_batchid
AND starttime IS NULL
AND rownum = 1;
i := i +1;
-- open new block for 'sqltext' statement
BEGIN
-- first set the starttime, (lock statement) to make sure
-- no other session will pick up the same statement
UPDATE batchsql
SET starttime = SYSDATE
WHERE sqlid = i_sqlid;
COMMIT;
-- execute the statement
EXECUTE IMMEDIATE v_sqltext;
-- everything went well, register endtime and commit
UPDATE batchsql
SET endtime = SYSDATE
WHERE sqlid = i_sqlid;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
-- error occured, register endtime and errors
i_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
UPDATE batchsql
SET endtime = SYSDATE
, sqlcode = i_sqlcode
, sqlerrm = v_sqlerrm
, error_stack = DBMS_UTILITY.FORMAT_ERROR_STACK()
-- Uncomment 'FORMAT_ERROR_BACKTRACE()' for pre 10G
-- , error_backtrace = DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
WHERE sqlid = i_sqlid;
COMMIT;
END;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
IF i = 0 THEN
DBMS_OUTPUT.PUT_LINE ( 'No SQL Statements found for batch ''' || v_batchid || '''');
ELSE
DBMS_OUTPUT.PUT_LINE ( 'Carried out ' || TO_CHAR(i) || ' SQL statements');
END IF;
DBMS_OUTPUT.PUT_LINE ( 'Time is ''' || to_CHAR(SYSDATE, 'DD-MON-YY hh24:mi:ss') || '''');
END;
/
Example
Suppose I have to analyze a bunch of tables, I will first have to fill my ‘batchsql’ table with all the SQL Statements that belong to this batch, I identify the batch to ‘DB01_ANALYZE_20080118′.
Generate some SQL statements and insert them in the batchsql table
Start a session that will start executing statements found in the batchsql table for batch ‘DB01_ANALYZE_20080118′:
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 v_batchid batchsql.batchid%TYPE;
3 i_sqlid batchsql.sqlid%TYPE;
4 v_sqltext batchsql.sqltext%TYPE;
5 i_sqlcode PLS_INTEGER;
6 v_sqlerrm VARCHAR2(256);
7 i PLS_INTEGER;
8 BEGIN
9 v_batchid := 'DB01_ANALYZE_20080118';
10 i := 0;
11 LOOP
12 -- get statement that nobody is working on
13 SELECT sqlid,
14 sqltext
15 INTO i_sqlid,
16 v_sqltext
17 FROM batchsql
18 WHERE batchid = v_batchid
19 AND starttime IS NULL
20 AND rownum = 1;
21 i := i +1;
22 -- open new block for 'sqltext' statement
23 BEGIN
24 -- first set the starttime, (lock statement) to make sure
25 -- no other session will pick up the same statement
26 UPDATE batchsql
27 SET starttime = SYSDATE
28 WHERE sqlid = i_sqlid;
29 COMMIT;
30 -- execute the statement
31 EXECUTE IMMEDIATE v_sqltext;
32 -- everything went well, register endtime and commit
33 UPDATE batchsql
34 SET endtime = SYSDATE
35 WHERE sqlid = i_sqlid;
36 COMMIT;
37 EXCEPTION
38 WHEN OTHERS THEN
39 -- error occured, register endtime and errors
40 i_sqlcode := SQLCODE;
41 v_sqlerrm := SQLERRM;
42 UPDATE batchsql
43 SET endtime = SYSDATE
44 , sqlcode = i_sqlcode
45 , sqlerrm = v_sqlerrm
46 , error_stack = DBMS_UTILITY.FORMAT_ERROR_STACK()
47 -- Uncomment 'FORMAT_ERROR_BACKTRACE()' for pre 10G
48 -- , error_backtrace = DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()
49 WHERE sqlid = i_sqlid;
50 COMMIT;
51 END;
52 END LOOP;
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 IF i = 0 THEN
56 DBMS_OUTPUT.PUT_LINE ( 'No SQL Statements found for batch ''' || v_batchid || '''');
57 ELSE
58 DBMS_OUTPUT.PUT_LINE ( 'Carried out ' || TO_CHAR(i) || ' SQL statements');
59 END IF;
60 DBMS_OUTPUT.PUT_LINE ( 'Time is ''' || to_CHAR(SYSDATE, 'DD-MON-YY hh24:mi:ss') || '''');
61* END;
62 /
Carried out 3 SQL statements
Time is '18-JAN-08 18:37:01'
PL/SQL procedure successfully completed.
SQL> REM and again
SQL> /
No SQL Statements found for batch 'DB01_ANALYZE_20080118'
Time is '18-JAN-08 18:37:20'
PL/SQL procedure successfully completed.
SQL>
SQL>
Pretty straigh forward, not?
The first run the starttime column is NULL (for every row),
The statements are executed, both the starttime and the endtime are updated at every single execution
The second run find no rows where starttime IS NULL and will not execute any statement at all.
Of course, you can add extra functionalities as many as you would like, store the sessionid for every SQL statement, have multiple runs for a batch to reuse statements, etc etc. Oh and of course I should rewrite it to a SQL Procedure or Package.
The babysitting is now minimized to running this query:
col batch for 9999
col completed for 9999
col todo for 9999
col activesessions for 9999999 head "ACTIVE|SESSIONS"
col errors for 99999
col elapsed_seconds for 9999999 head "ELAPSED|SECONDS"
col avg_execution_time for 9999999 head "AVG|EXECUTION|TIME"
col percentage_complete for 9999999 head "PERCENTAGE|COMPLETE"
-- as I only have one batch loaded I don't need to filter on the batchid
select batch.c batch,
completed.c completed,
batch.c - completed.c todo,
to_char( times.started, 'DD-MM-HH hh24:mi') started,
to_char( times.lastcompleted, 'DD-MM-HH hh24:mi') lastcompleted,
round( completed.c / batch.c * 100, 1) percentage_complete,
activesessions.c activesessions,
errors.c errors,
( sysdate - times.started) *24*60*60 elapsed_seconds,
trunc( ( sysdate - times.started)*24*60*60 / completed.c) avg_execution_time,
to_char( sysdate +
( batch.c - completed.c) *
( (sysdate - times.started) / completed.c)
, 'DD-MM-HH hh24:mi') est_end_time
from ( select count(*) as c from batchsql ) batch
, ( select count(*) as c from batchsql
where endtime is not null) completed
, ( select count(*) as c
from batchsql
where starttime is not null
and endtime is null) activesessions
, ( select count(*) as c
from batchsql
where sqlcode is not null ) errors
, ( select min(starttime) started,
max(starttime) lastcompleted
from batchsql ) times
/
AVG
PERCENTAGE ACTIVE ELAPSED EXECUTION
BATCH COMPLETED TODO STARTED LASTCOMPLETED COMPLETE SESSIONS ERRORS SECONDS TIME EST_END_TIME
----- --------- ----- -------------- -------------- ---------- -------- ------ -------- --------- --------------
1524 492 1032 18-01-06 18:45 18-01-10 22:01 32 6 1 11756 23 19-01-04 04:52
SQL>
SQL>
SQL> select*from batchsql where sqlcode is not null;
BATCHID SQLID
---------------------------------------------------------------- ----------
SQLTEXT
-----------------------------------------------------------------------------------------------------------------------
STARTTIME ENDTIME SQLCODE SQLERRM
---------- ---------- ---------- --------------------------------------------------------------------------------------
ERROR_STACK
-----------------------------------------------------------------------------------------------------------------------
ERROR_BACKTRACE
-----------------------------------------------------------------------------------------------------------------------
DB01_ANALYZE_20080118 84
ANALYZE TABLE ********
18-01-2008 18-01-2008 -1555 ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small
ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small
SQL>
UPDATE 2010-05-10T11:04:21+00:00
Make sqlid non unique and add trigger to generate sqlid’s.
CREATE TABLE batchsql
( batchid VARCHAR2(64) NOT NULL
, sqlid INTEGER NOT NULL
, sqltext varchar2(4000) NOT NULL
, starttime DATE
, endtime DATE
, sqlcode INTEGER
, sqlerrm VARCHAR2(256)
, error_stack VARCHAR2(4000)
, error_backtrace VARCHAR2(4000)
);
CREATE SEQUENCE s_batchsql;
CREATE OR REPLACE TRIGGER t_batchsql
BEFORE INSERT ON batchsql
FOR EACH ROW
WHEN (new.sqlid IS NULL)
BEGIN
SELECT s_batchsql.NEXTVAL
INTO :new.sqlid
FROM dual;
END;
/
Create script to execute statements from batch table
export ORAENV_ASK=NO;
export ORACLE_SID=sfsse001;
/usr/local/bin/oraenv
sqlplus -silent '/ as sysdba' <<EOF
column d new_value d
select to_char( sysdate, 'YYYYMMDD-HH24MISS') d from dual;
set serveroutput on
spool batch_&d.log
@r.sql
spool off
EOF
Batch SQL statements in parallel (multiple) sessions
So it was decided ‘this batch’ had to be done ‘this weekend’. Thousands of SQL statements some of them would take minutes others hours… And MAKE SURE it’s finished monday.
I hate babysitting multiple sessions having to create multiple SQL Scripts to spread the workload (it’s on a 20+ CPUS’s database server) So I wrote this PL/SQL ‘application’ in a hurry to enable starting multiple databases sessions in
nohup, each session picks up the next SQL statement ‘available’ (WHERE endtime IS NULL). It will save me some sleep.Setup
The table holding my SQL statements
would be nicer to make the ‘sqlid’ column a compisone unique key with the batchid (harmless off course).
The anonymous PL/SQL block that will loop and execute these statements
Example
Suppose I have to analyze a bunch of tables, I will first have to fill my ‘batchsql’ table with all the SQL Statements that belong to this batch, I identify the batch to ‘DB01_ANALYZE_20080118′.
Generate some SQL statements and insert them in the batchsql table
SQL> DEFINE BATCHID = "''DB01_ANALYZE_20080118''" SQL> DEFINE QUOTE = "''" SQL> SELECT 'INSERT INTO batchsql 2 ( batchid, sqlid, sqltext) 3 VALUES 4 ( &BATCHID,' || ROWNUM || ', "E.ANALYZE TABLE ' || table_name || ' COMPUTE STATISTICS"E);' 5 FROM user_tables 6 WHERE rownum < 5 7 / 'INSERTINTOBATCHSQL(BATCHID,SQLID,SQLTEXT)VALUES(''DB01_ANALYZE_20080118'','||ROWNUM||',''ANALYZETABLE'||TABLE_ --------------------------------------------------------------------------------------------------------------- INSERT INTO batchsql ( batchid, sqlid, sqltext) VALUES ( 'DB01_ANALYZE_20080118',1, 'ANALYZE TABLE BATCHSQL COMPUTE STATISTICS'); INSERT INTO batchsql ( batchid, sqlid, sqltext) VALUES ( 'DB01_ANALYZE_20080118',2, 'ANALYZE TABLE DEMO COMPUTE STATISTICS'); SQL>Do test the error handling I insert one bogus statement that will generate an error.
Now look at the contents of the batchsql table, we see three statements for the ‘DB01_ANALYZE_20080118′ batch.
Start a session that will start executing statements found in the batchsql table for batch ‘DB01_ANALYZE_20080118′:
Pretty straigh forward, not?
See the log results.
Of course, you can add extra functionalities as many as you would like, store the sessionid for every SQL statement, have multiple runs for a batch to reuse statements, etc etc. Oh and of course I should rewrite it to a SQL Procedure or Package.
The babysitting is now minimized to running this query:
UPDATE 2010-05-10T11:04:21+00:00
Make sqlid non unique and add trigger to generate sqlid’s.
Create script to execute statements from batch table