Ask The Real Tom

July 8, 2010



Oracle Web Application Trace – Tuning Approach

Filed under: Uncategorized — Tags: , , , , , , — admin @ 10:07 am

Situation:

The user claims that a oracle web application is slow. The web application is unsing in database java.

Test 1: Determine the slow Part

The webapplication can be run in sqlplus with following pl/sql

set serveroutput on
set timing on
declare
name owa.vc_arr;
val owa.vc_arr;
res owa.vc_arr;
simple_list__ owa_util.vc_arr;
complex_list__ owa_util.vc_arr;
c clob;
begin
name(1) := '1';
val(1) := '1';
OWA.init_cgi_env (0, name, val);
HTP.init;
-- if ((owa_match.match_pattern('jdb', simple_list__, complex_list__, true))) then
jdb(1, name, val, res);
-- end if;
wpg_docload.get_download_file(:doc_info);
OWA_UTIL.showpage;
end;
/

The result showed that the application is using 4 frames. This means that 1 web request is followed by 4 subsequend requests.
We see as well that the owa functions are not the problem.

Test 2: Trace the application
To just trace the relevant part, I traced by server and modul name


select username,client_identifier,process,module from v$session where module like 'httpd%';

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SYS$USERS','httpd@svzpca2 (TNS V1-V3)', DBMS_MONITOR.ALL_ACTIONS,TRUE,TRUE,NULL);

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE ('SYS$USERS','httpd@svzpca2 (TNS V1-V3)', DBMS_MONITOR.ALL_ACTIONS,NULL);

cd udump
tkprof

The trace showed one query taken about 10 to 20 seconds per executionn

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 15 0.00 0.00 0 0 0 0
Execute 15 155.99 152.95 1 4147 8870 0
Fetch 24 0.35 0.38 1 3632 0 134
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 54 156.34 153.34 2 7779 8870 134

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 192 (recursive depth: 1)

Now its a simple sql tuning task to solve the problem

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