Here is a problem: I need to track all PL/SQL code that application of interest (Oracle Retail Price Management in this case) is executing, where I need to find out not only all anonymous code along with stored procedures/packages/functions that have been executed, but also the sequence of execution (time of execution).

In this case, you first need to know the application architecture as this is important to setup PL/SQL code tracking.

Oracle Retail Price Management is part of Oracle Retail Suite with the main purpose to manage price changes (promotions, clearance…).

It is Java apps that runs on top of Web Logic application server. As RPM is using connection pool, it’s critical to setup database services for each application, and create data source on the Web Logic side that will use previously created Db service.

During the RPM tracing we can observe that connection pool is using different sessions from pool to complete the task. Without using of dedicated Db service for RPM application and Web Logic DS that is using created service, we can only partly trace code execution.

Using different connections inside connection pool is expected behavior.

After Db service and Web Logic DS setup has been completed, you can start tracing the execution of RPM code.

Here I’ll explain case where nobody is using RPM application during the test. In real world scenario, this is usually not possible, so you’ll need to add additional criteria (like module/action/client id…) in the code.


exec dbms_monitor.serv_mod_act_trace_enable(service_name => 'rpm',waits => true, binds => true);

After you complete the testing, you need to execute the following:


exec dbms_monitor.serv_mod_act_trace_disable(service_name => 'rpm');

Now you need to collect the trace files.

Due to the connection pool usage, several trace files will be created.

This is the second reason why it is so important to setup dedicated Db service for each application running on Db server, as Oracle has trcsess utility that can create one trace file based on, in this case, service name from all generated trace file .

This will make tracking PL/SQL code execution by time line very easy.

Just to complete discussion, there are two additional reasons for using Db services in all cases.

One is to make very easy to monitor resource consumption by application (by using Oracle Enterprise Manager or similar tool).

The last reason is to be able to allocate resources and setup priorities and limitations by application importance. This can be done by using resource manager.

Once you have one, filtered by service name, trace file, it’s easy to get the desired result:

You just need to execute the following script (assuming you have Linux/Unix server or client).

In trace file you can easily found the pattern for finding anonymous PL/SQL code as well as stored procedures/functions/packages


awk '/BEGIN/{flag=1}/END OF STMT/{flag=0}flag' rpm_trace.trc

/BEGIN – matches lines having this text

/BEGIN/{flag=1} – sets the flag when awk fing line with BEGIN

/END OF STMT/{flag=0} – unsets the flag when END OF STMT is found

This is the sample output:

BEGIN
IF USER = ‘BMF_USER’ THEN
PLATFORM.PLATFORM_CORE.set_context(‘kws’,’session.username’,’jure.tomic’);
execute immediate ‘alter session set current_schema=kws’;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END tgalon$_bmf_user;

BEGIN
select username, osuser, module, sid, serial#
into v_username, v_os_user, v_module, v_sid, v_serial#
from v$session where username = ‘RMS’;
IF
v_username = ‘RMS’
— AND v_os_user = ‘oracle’
— AND v_machine = ‘rtl-wl-std-1325-td1.konzum.hr’
THEN
execute immediate ‘alter session set TRACEFILE_IDENTIFIER = “reim”‘;
sys.DBMS_MONITOR.session_trace_enable (v_sid, v_serial#, waits => TRUE, binds => TRUE);
–sys.dbms_system.set_ev(v_sid,v_serial#,10046,8,”);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END on_logon_reim;
BEGIN :return := RPM_CC_PROMO_CONSTRAINT.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_NEG_RETAIL.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_CLEAR_LT_REG.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_CLEAR_MKDN.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_CLEARUOM_SELLUOM.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_FIXED_CLR_PROM_OVER.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_PROM_LT_CLEAR_REG.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_AMOUNT_OFF_UOM.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_MULTI_UNIT_UOM.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_PC_PROM_OV.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_CL_PROM_OV.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_PROM_COMP_CNT.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := RPM_CC_PROMO_FIXED_PRICE.VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := Z_CUSTOM_RULE.CC_ZONE_VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := Z_CUSTOM_RULE.CC_ITEM_ZONE_DATE_VALIDATE(:error_tbl, :price_event_type); END;
BEGIN :return := Z_CUSTOM_RULE.CC_PROMO_VS_MP_CARD_VALIDATE(:error_tbl, :price_event_type); END;
….


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.