oracle Database session tracking

Hello everyone,

Today I would like to describe the DBMS_APPLICATION_INFO package in the oracle database.

here are some of the procedures it has:

SET_ACTION Procedure	Sets the name of the current action within the current module
SET_CLIENT_INFO Procedure	Sets the client_info field of the session
SET_MODULE Procedure	Sets the name of the module that is currently running to a new module

for the full description refer to the link:

DBMS_APPLICATION_INFO

with this you can set some information in the current session, which can be queried from the v$session from the sys schema:

select module,action,client_info from v$session

this can be helpful to identify and trace database sessions, specially if you are using connections pools.

after getting the connection from the pool, the first thing to do is to set the client_info ,module or action fields  using  this package for example:set the module to the application name and set the client_info to user name. this way it will be easier for you or the dba to identify which session you want to trace.

here is an example:

recently we had a certain page in our production environment that was slow to load, the problem could not be reproduced in test environment, after some poking around we suspected that the issue might be caused by database. but since the application uses a connection pool ,we had several sessions connected to the database.and did not know which session to trace,so by using this package we set the value of the client_info to be the user name. after that we managed to trace the session ,used TKPROF on the output file and by inspecting the resulting file found that the issue was caused by loading an entire table(40,000+ record) into memory then searching them. after fixing the issue the page loaded fast.

Oracle DB XML generation

As you can tell from the title this is article is not related to ADF, but to the oracle database.

the use case: 

    generate xml document based on a certain requirement in oracle database.

for example :using the HR schema, list all department id, name and all of it's employees as well as listing each employee name and job.

something like :   1     dept1

                                     10    emp1 clerk

                                     11    emp2  it

                           2    dept2

the solution:

there are several xml functions in the oracle database that can be used to generate and format xml documents. I will use them to generate the needed xml.

there are two solutions we found that could solve the issue:

1) simple :

SELECT DBMS_XMLGEN.getxml ('select d.department_name,d.department_id,cursor(select first_name ||'' ''||last_name name,job_id from employees e where E.DEPARTMENT_ID=D.DEPARTMENT_ID) employees from departments d ') FROM DUAL;

will  return something like:

<?xml version="1.0"?>

<ROWSET>

 <ROW>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Jennifer Whalen</NAME>

    <JOB_ID>AD_ASST</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

 <ROW>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <EMPLOYEES>

   <EMPLOYEES_ROW>

    <NAME>Michael Hartstein</NAME>

    <JOB_ID>MK_MAN</JOB_ID>

   </EMPLOYEES_ROW>

   <EMPLOYEES_ROW>

    <NAME>Pat Fay</NAME>

    <JOB_ID>MK_REP</JOB_ID>

   </EMPLOYEES_ROW>

  </EMPLOYEES>

 </ROW>

.

.

.

.

.

</ROWSET>

as you can see the  DBMS_XMLGEN.getxml generate the output based on whatever sql query is passed into it as input and the generated output tags are based on the column names.

still there are tags that are named row,rowset that you cannot control their names.

of course ,you can use the replace function to change those tags.

but what if the data returned from the query contains data that have strings similar to these tags?

this led me to look for another solution.

2) more complex:

DBMS_XMLGEN function is not the only xml function provided by oracle. There are handful of other functions, which we will use in the following solution:

SELECT SYS_XMLAGG (

          SYS_XMLGEN (

             XMLFOREST (

                D.DEPARTMENT_NAME,

                D.DEPARTMENT_ID,

                (SELECT SYS_XMLAGG (

                           SYS_XMLGEN (XMLFOREST (E.FIRST_NAME, E.JOB_ID),

                                       XMLFormat ('Employee')),

                           XMLFormat ('List'))

                   FROM employees e

                  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) "Employees"),

             XMLFormat ('Department')),

          XMLFormat ('Departments'))

          "xml query"

  FROM departments d;

which will produce :

<?xml version="1.0"?>

<Departments>

<Department>

  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>

  <DEPARTMENT_ID>10</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Jennifer</FIRST_NAME>

        <JOB_ID>AD_ASST</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

<Department>

  <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>

  <DEPARTMENT_ID>20</DEPARTMENT_ID>

  <Employees>

    <List>

      <Employee>

        <FIRST_NAME>Michael</FIRST_NAME>

        <JOB_ID>MK_MAN</JOB_ID>

      </Employee>

      <Employee>

        <FIRST_NAME>Pat</FIRST_NAME>

        <JOB_ID>MK_REP</JOB_ID>

      </Employee>

    </List>

  </Employees>

</Department>

.

.

.

</Departments>

of course you can always write a lot of pl/sql code to read the result and concatenate the result to create the desired output.

DBLink odd behavior in Oracle database

Hello all,

recently I came across an odd behavior in the oracle database in the production environment. I am not sure if this is the correct behavior.

I managed to reproduce the behavior in test environment.

my scenario:

1)I have a local database, a remote database and a DBLink in the local database pointing to the remote one.

2) read a value from the remote database.

3) insert or update values in a table in the local database.

4)disconnect the local database from the remote database(either a-unplug the network cable from the remote database b- unplug the network cable from the local database).

5) commit the changes on the local database.

for whatever strange reason it's the database decided to start a 2PC commit.

what I don't get is that I only read from the remote database, so why is the database trying to start a 2PC commit.

I could not find a viable solution. so had to implement the following workaround:

1) change the read operation into calling a procedure.

2) have the procedure call the remote database.

3) defined the procedure as having an autonomous transaction.

4) after reading the value , rollback the autonomous transaction.

it seems since the read from the remote database was not in the main transaction, when I try to commit it , it is not changed into a 2PC commit.