Ask The Real Tom

July 14, 2010

commit rate sampler for oracle

Filed under: Uncategorized — Tags: 11gR2, commit, oracle, pl/sql, rate, sampler — 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; /

Comments (0)

February 24, 2010

Extensive PL/SQL Exception Handling

Filed under: Uncategorized — Tags: error, exception, oracle, pl/sql, trace — 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 ( '----------' );

Comments (0)