Ask The Real Tom

July 14, 2010



commit rate sampler for oracle

Filed under: Uncategorized — Tags: , , , , , — admin @ 11:26 am

Today a wrote a simple commit sampler for oracle databases. With this simple tool it possible to see the commit rate on the database.

It can be usefull to compre the application perfomance on batch processing system and analyze commit related waits.

@snapcommit

Example
@snapcommit 1 20

snapcommit.sql

set tab off verify off linesize 299
set termout on serverout on size 1000000 format wrapped

define snap_interval="&1"
define snap_limit="&2"

declare
cmt_start number;
cmt_last number;
cmt_curr number;
cmt_diff number;

procedure oprint(p_var varchar2) as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
dbms_output.put_line(p_var);
end oprint;
begin

for x in 1..&snap_limit+1
loop
cmt_last := cmt_curr;

select value into cmt_curr from v$sysstat where name='user commits';
if(x = 1) then
cmt_start:= cmt_curr;
else
cmt_diff := cmt_curr - cmt_last;
oprint('commits: '||cmt_diff||' in '||&snap_interval||'s' );

end if;

dbms_lock.sleep(&snap_interval);
end loop;
cmt_diff := cmt_curr - cmt_start;
dbms_output.put_line('total commits: '||cmt_diff );
end;
/

February 24, 2010



Extensive PL/SQL Exception Handling

Filed under: Uncategorized — Tags: , , , , — admin @ 12:09 pm

If pl/sql exception get catched, it can be difficult to get the cause of the error.

To work around this the exception handler should log what was going wrong.

Suggestion

DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_CALL_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_CALL_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_STACK:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_STACK() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );
DBMS_OUTPUT.PUT_LINE ( 'FORMAT_ERROR_BACKTRACE:' );
DBMS_OUTPUT.PUT_LINE( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() );
--
DBMS_OUTPUT.PUT_LINE ( '----------' );

Powered by WordPress