Timing Sections of PLSQL Code

Your PLSQL routine is running like a dog with three legs? Don't know where to start looking as it could be any of a number of issues? Then adapt this timing code and drop it in for testing. Wrap the start and end calls around suspected sections of code to find where the hold up is occuring.

DECLARE ----------------------------- -- Timing Declaration Start TYPE tt_timingStart IS TABLE OF TIMESTAMP(9) INDEX BY VARCHAR2(30); l_timingStart tt_timingStart; TYPE tt_timingTotal IS TABLE OF NUMBER INDEX BY VARCHAR2(30); l_timingCount tt_timingTotal; l_timingTotal tt_timingTotal; -- PROCEDURE timingStart( p_timingIndex IN VARCHAR2 ) IS BEGIN BEGIN IF l_timingTotal(p_timingIndex) IS NULL THEN NULL; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN l_timingCount(p_timingIndex) := 0; l_timingTotal(p_timingIndex) := 0; END; l_timingStart(p_timingIndex) := SYSTIMESTAMP; END timingStart; -- PROCEDURE timingEnd( p_timingIndex IN VARCHAR2 ) IS l_duration INTERVAL DAY(9) TO SECOND(5) := SYSTIMESTAMP - l_timingStart(p_timingIndex); BEGIN l_timingCount(p_timingIndex) := l_timingCount(p_timingIndex) + 1; l_timingTotal(p_timingIndex) := l_timingTotal(p_timingIndex) + (EXTRACT(SECOND FROM l_duration)*1000) + (EXTRACT(MINUTE FROM l_duration)*60000) + (EXTRACT(HOUR FROM l_duration)*3600000) + (EXTRACT(DAY FROM l_duration)*86400000); END timingEnd; -- PROCEDURE timingOutput IS l_file UTL_FILE.FILE_TYPE; l_index VARCHAR2(30); BEGIN l_file := UTL_FILE.FOPEN('SCRATCH_DIR','TimingOutput.txt','W'); l_index := l_timingTotal.FIRST; WHILE l_index IS NOT NULL LOOP UTL_FILE.PUT_LINE(l_file,LPAD(l_index,20,'_')||': ' ||LPAD(ROUND(l_timingCount(l_index)),10)||'iterations' ||LPAD(ROUND(l_timingTotal(l_index)),10)||'ms' ||LPAD(ROUND(l_timingTotal(l_index)/1000),10)||'s' ||LPAD(ROUND(l_timingTotal(l_index)/60000),10)||'m'); l_index := l_timingTotal.NEXT(l_index); END LOOP; UTL_FILE.FCLOSE(l_file); END timingOutput; -- Timing Declaration End ----------------------------- BEGIN timingStart('Procedure'); FOR i IN 1..20 LOOP timingStart('First Sleep'); DBMS_LOCK.SLEEP(0.3); timingEnd('First Sleep'); timingStart('Second Sleep'); DBMS_LOCK.SLEEP(0.5); timingEnd('Second Sleep'); timingStart('Third Sleep'); DBMS_LOCK.SLEEP(0.1); timingEnd('Third Sleep'); END LOOP; timingEnd('Procedure'); timingOutput; END; /

This does assume there is a SCRATCH directory to output the file to. If you haven't got one it's easily modified to DBMS_OUTPUT.PUT_LINE.

Timed

Oracle Processes CPU Usage

Is that long running job doing anything? This SQL query (Oracle 11) will list user processes and the amount of CPU usage the current job has used so far. Re-query to see how the CPU usage progesses.

SELECT vs.username, vs.sid, vs.serial#, vp.spid, vs.machine, vs.module, vs.program, vs.action, vs.status, vs.last_call_et, ROUND((vss_sess.value-vss_call.value)/100) "CPU Usage" FROM gv$session vs, gv$process vp, v$sesstat vss_sess, v$sesstat vss_call WHERE vs.paddr = vp.addr(+) AND vs.inst_id = vp.inst_id(+) AND vs.sid = vss_sess.sid AND vs.sid = vss_call.sid AND vss_call.statistic# = 13 -- CPU used when call started AND vss_sess.statistic# = 14 -- CPU used by this session AND vs.username IS NOT NULL ORDER BY ( CASE vs.status WHEN 'INACTIVE' THEN 0 ELSE vss_sess.value-vss_call.value END ) DESC, vs.status, vs.last_call_et;

Maybe useful here...

Several ways to kill a process.

The kill session syntax. The immediate option will not kill the session faster, however it will return the prompt immediately after the statement has been issued.

ALTER SYSTEM KILL SESSION 'sid,serial#'; ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

The kill session statement asks the session to end, whereas the disconnect session statement cuts the session process.

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The post transaction option is equivilent to kill session.

For those even more stubborn processes, you need to move to the operating system, using the spid value from the query above.

In Windows:

orakill ORACLE_SID spid

In UNIX:

kill -9 spid

Done and Killed (hopefully).

Solving ORA-28001 Without Changing the Password

Unexpectedly, several months after upgrading to Oracle 11g, our SYSMAN and DBSNMP account passwords expired, creating lots of errors in our audit logs. We never had this with 10g, they never expired.

Not wanting to dive into lots of unfamilar config files, we looked at unexpiring the accounts, keeping the existing passwords.

A little googling and tweaking later, the following SQL command outputs alter user statements to "unexpire" the accounts.

I'm not sure if this method is supported by Oracle or not but it worked for us.

SELECT 'ALTER USER '||u.name||' IDENTIFIED BY VALUES '''||u.spare4||';'||u.password||''';' cmd FROM sys.user$ u WHERE u.name IN ('SYSMAN','DBSNMP') ;

User altered

Oracle Function to Split String into an Array

This deterministic function creates a general function for converting a comma seperated string into an array.

First of all you need to create a varchar2 array type. I've found it very useful to create a generic varchar2 array I can use anywhere.

CREATE TYPE "VARCHAR2_ARRAY" AS TABLE OF VARCHAR2(32767)

Now the function...

CREATE OR REPLACE FUNCTION string_to_array ( p_string IN VARCHAR2, p_seperator IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2_ARRAY DETERMINISTIC IS l_string VARCHAR2(4000) := p_seperator||p_string||p_seperator; l_array VARCHAR2_ARRAY := VARCHAR2_ARRAY(); BEGIN IF LENGTH(p_seperator) != 1 THEN RAISE_APPLICATION_ERROR(-20101,'Seperator should only be one character'); END IF; SELECT SUBSTR(l_string, INSTR(l_string,p_seperator,1,LEVEL)+1, INSTR(l_string,p_seperator,1,LEVEL+1) - INSTR(l_string,p_seperator,1,LEVEL)-1 ) token BULK COLLECT INTO l_array FROM DUAL CONNECT BY LEVEL < LENGTH(l_string)-LENGTH(REPLACE(l_string,p_seperator,'')); RETURN(l_array); END; /

String Split...Done.

PLS-00201: identifier 'NVL2' must be declared

Having just tried to use NVL2 inside PLSQL I have discovered it isn't there! This is one of those functions that only exist for SQL not PLSQL. Like DECODE.

NVL2 is quite simple and easily rewritten with IF or CASE statements but these can sometimes make code messy. NVL2 in SQL can just be concatinated within a string and is one command. (Not always a good reason for using it but in some cases...)

Anyway, to get around the problem in these few circumstances, you can create your own function.

CREATE OR REPLACE FUNCTION NVL2 ( p_value IN VARCHAR2, p_newValueIfNotNull IN VARCHAR2, p_newValueIfNull IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN IF p_value IS NOT NULL THEN RETURN(p_newValueIfNotNull); ELSE RETURN(p_newValueIfNull); END IF; END; /

The function will be used when called from PLSQL, the built-in will be used when called from SQL.

Sorted

Diagnose ORA-02292 Errors When Deleting Records in Oracle

Working on an undocumented system and got the error “ORA-02292: integrity constraint (…) violated - child record found” when trying to delete records? This solution provides an anonymous procedure for running in SQL*Plus, Developer, etc, to get the child relationships for a specific table, so you can easily understand the constraints preventing the record being deleted.

The procedure needs two parameters, the start table and the rowid of the record. The following code can be prefixed to the procedure in the script to set up these variables.

VAR vRowId VARCHAR2(20) VAR vSchema VARCHAR2(30) VAR vStartTable VARCHAR2(30) BEGIN :vSchema := USER; :vStartTable := 'MY_TABLE'; SELECT ROWID INTO :vRowId FROM my_table WHERE my_table_id = 5333; END; /

Adjust the query or just add the rowid reference directly, as required. The procedure then uses these values to produce the output, which will be specifically for the record with the rowid selected. Relationships with no data will not be displayed, so you can track down the cause of the ORA-02292 error.

DECLARE --//------------------------------ c_schema CONSTANT VARCHAR2(30) := :vSchema; c_startTable CONSTANT VARCHAR2(30) := :vStartTable; c_startRowId CONSTANT VARCHAR2(20) := :vRowId; --//------------------------------ TYPE t_consColumns IS TABLE OF user_cons_columns.column_name%TYPE; TYPE t_results IS TABLE OF ROWID; TYPE t_processed IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(50); g_processed t_processed; --//------------------------------ FUNCTION getChildRowids ( p_parentTable IN VARCHAR2, p_childTable IN VARCHAR2, p_parentColumns IN t_consColumns, p_childColumns IN t_consColumns, p_rowid IN ROWID ) RETURN t_results IS l_query VARCHAR2(4000); l_results t_results; BEGIN l_query := 'SELECT c.ROWID' || ' FROM '||c_schema||'.'||p_parentTable||' p,' || c_schema||'.'||p_childTable ||' c' ||' WHERE p.ROWID = '''||p_rowid||''''; FOR i IN 1..p_parentColumns.COUNT LOOP l_query := l_query||' AND p.'||p_parentColumns(i)||' = c.'||p_childColumns(i); END LOOP; EXECUTE IMMEDIATE l_query BULK COLLECT INTO l_results; RETURN(l_results); END getChildRowids; --//------------------------------ FUNCTION getConstraintColumns ( p_constraintName IN VARCHAR2 ) RETURN t_consColumns IS l_columnNames t_consColumns; BEGIN SELECT column_name BULK COLLECT INTO l_columnNames FROM dba_cons_columns WHERE owner = c_schema AND constraint_name = p_constraintName; RETURN(l_columnNames); END getConstraintColumns; --//------------------------------ FUNCTION alreadyProcessed ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) RETURN BOOLEAN IS l_return BOOLEAN := FALSE; BEGIN IF g_processed(p_tableName||'.'||p_rowid)=1 THEN NULL; END IF; RETURN(TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN g_processed(p_tableName||'.'||p_rowid) := 1; RETURN(FALSE); END; --//------------------------------ PROCEDURE processTable ( p_tableName IN VARCHAR2, p_rowid IN ROWID ) IS l_parentColumns t_consColumns; l_childColumns t_consColumns; l_results t_results; BEGIN IF NOT alreadyProcessed(p_tableName,p_rowid) THEN FOR c IN ( SELECT parent.constraint_name parentConstraint, child.constraint_name childConstraint, child.table_name childTable FROM dba_constraints parent, dba_constraints child WHERE parent.table_name = p_tableName AND parent.constraint_name = child.r_constraint_name AND child.r_owner = c_schema AND parent.owner = c_schema ) LOOP l_parentColumns := getConstraintColumns(c.parentConstraint); l_childColumns := getConstraintColumns(c.childConstraint); l_results := getChildRowids(p_tableName, c.childTable, l_parentColumns, l_childColumns, p_rowid); FOR r IN 1..l_results.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Found child records'); DBMS_OUTPUT.PUT_LINE('- for: '||p_tableName||' ['||p_rowid||']'); DBMS_OUTPUT.PUT_LINE('- at: '||c.childTable||' ['||l_results(r)||']'); DBMS_OUTPUT.PUT_LINE('- via: '||c.childConstraint||' -> '||c.parentConstraint); processTable(c.childTable, l_results(r)); END LOOP; END LOOP; END IF; END processTable; --//------------------------------ BEGIN DBMS_OUTPUT.PUT_LINE('Processing '||c_startTable||' ['||c_startRowId||']'); processTable(c_startTable, c_startRowId); END; /

The main sources of data are the views dba_constraints and dba_cons_columns. The procedure uses these to build a dynamic query to check for related data in the child tables. If data is found it outputs the result, then checks down that branch for further child tables.

The procedure only checks one schema, so relationships between schemas will not be included in the search.

Child Records Found

Oracle Procedure to Write a CLOB to a File

I've seen several ways to do this but this way works nicely for me. (Using Oracle 11g)

CREATE OR REPLACE PROCEDURE clob_to_file ( p_directory IN VARCHAR2, p_filename IN VARCHAR2, p_clob IN CLOB ) IS c_chunk CONSTANT PLS_INTEGER := 32767; l_fHandler UTL_FILE.FILE_TYPE; l_pos PLS_INTEGER := 1; BEGIN l_fHandler := UTL_FILE.FOPEN(p_directory, p_filename, 'W', c_chunk); WHILE l_pos < DBMS_LOB.GETLENGTH(p_clob) LOOP UTL_FILE.PUT(l_fHandler, DBMS_LOB.SUBSTR(p_clob, c_chunk, l_pos)); l_pos := l_pos + c_chunk; UTL_FILE.FFLUSH(l_fHandler); END LOOP; UTL_FILE.FCLOSE(l_fHandler); END; /

The UTL_FILE package writes the clob in 32767 byte chunks. This has a limitation that each 32K chunk has to have a carriage return (I've not come across an output without one, so not captured and processed the error here but you could easily), otherwise you get the following error:

ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 183 ORA-06512: at "SYS.UTL_FILE", line 1169 ORA-06512: at "MYSCHEMA.CLOB_TO_FILE", line 16 ORA-06512: at line 8

Written.