This discussion is archived
5 Replies Latest reply: Mar 7, 2013 10:23 PM by 429356 RSS

Procedure usage logging asynchronously

429356 Newbie
Currently Being Moderated
Hi,

Our database is a central data hub and data from our database tables are retrieved using stored procedures. These SPS are inside a package and application calls these sps through other sps written in application schema(within the same database)

We've now a requirement to track usage patterns of these sps when called to retrieve the data. The requirement is to dynamically know:-

1. Which sp was called by which program
2. What are the parameters that the program has passed to call the sp
3. More fine grained to know what key data the program has accessed

A very simple example is like a procedure pkg1.pr1(in_emp_id IN in_emp_id_tbl, out_ref_emp_details OUT sys_refcursor) - in_emp_id_tbl is a nested table collection to pass list of emp ids adnt ehref cursor will return the details of all the emp id requested.

Say this procedure is called by any application from another schema to display the details in their screen by a particular user then want to log the schema, the program, the user and the parameters(IN) they have passed to get the details of the employees.

This can be done easily by writing a logging script and calling it inside the procedure pkg1.pr1 and that logging script will insert the details in a generic log table ; but we want to avoid too much logging along with the data retrieval process as this may hit the performance of the sp called and in our application scenario during pick hours multiple users will call multiple procedures(like the above example but much complex) at the same time to retrieve the data and display it in the screen.

So we are thinking of doing this logging asynchronously. Can anybody tell how

- We can do this logging asynchronously. Is there any oracle utility that gives me ready made information .

- Will DBMS_SCHEDULER a good option to trigger 100 or more jobs at the same time or any other way.

- DBMS_PIPE can also be another option but ours is a RAC environment so can we use that.

Any help in the regard is highly appreciated.

Regards,
Sudipta
  • 1. Re: Procedure usage logging asynchronously
    rp0428 Guru
    Currently Being Moderated
    >
    - We can do this logging asynchronously. Is there any oracle utility that gives me ready made information .
    >
    Code run in PL/SQL will be synchronous, even if you call a logging procedure that is an AUTONOMOUS_TRANSACTION.

    For asynchronous processing you can use streams/advanced queing. For minimal impact you would write a new LOG package and make calls to it as you suggest in your post.

    Then the log package would create messages and send them to a queue for asynchronous processing.
    >
    - Will DBMS_SCHEDULER a good option to trigger 100 or more jobs at the same time or any other way.
    >
    I would not think so. You would still have to package the information and store it somewhere for the scheduler job to be able to access it.
    >
    - DBMS_PIPE can also be another option but ours is a RAC environment so can we use that.
    >
    That option would work but is not nearly as well known as using streams. Streams is the forward-looking functionality that is being integrated into almost all aspects of Oracle's own processes.

    Stick with the better known stream processing. The LOG interface to your application/procedures should be simple and generic and the log procedures themselves should package the information, add it to a queue and return as quickly as possible.

    A Log package will let you create overloaded procedures that all use a common API so you can expand as needed.
  • 2. Re: Procedure usage logging asynchronously
    sb92075 Guru
    Currently Being Moderated
    su**** wrote:
    Hi,

    Our database is a central data hub and data from our database tables are retrieved using stored procedures. These SPS are inside a package and application calls these sps through other sps written in application schema(within the same database)

    We've now a requirement to track usage patterns of these sps when called to retrieve the data. The requirement is to dynamically know:-

    1. Which sp was called by which program
    2. What are the parameters that the program has passed to call the sp
    3. More fine grained to know what key data the program has accessed
    WHY?
    Has anyone done a cost/benefit analysis regarding the cost to collect these details vs. the benefit from having these details?
  • 3. Re: Procedure usage logging asynchronously
    429356 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your qucik response.

    With AQ also I think it'll insert into a table first(aq table) so there will be some I/O that we want to avoid(not logging into table directly along with the procedure call for data retreival). Do you think with AQ buffered messaging this can be avoided but I think it'll make the code more complex.

    On DBMS_SCHEDULER or JOB the packaging will be inside the logging framework procedure without logging it in actual table at that point in time which will happen when the job will start(independent of the procedure called for data retreival) but my question is if we trigger multiple jobs at the same time that logs on the same table then will there be any overall impact on the database performance.

    Regards,
    Sudipta
  • 4. Re: Procedure usage logging asynchronously
    rp0428 Guru
    Currently Being Moderated
    >
    With AQ also I think it'll insert into a table first(aq table) so there will be some I/O that we want to avoid(not logging into table directly along with the procedure call for data retreival). Do you think with AQ buffered messaging this can be avoided but I think it'll make the code more complex.
    >
    AQ is written, tested and supported by very experienced and knowledgeable people. It is expressly designed to be asynchronous and used by the largest databases and systems that Oracle supports.

    Use of buffering might be more complex initially but once your framework is done you never need to touch it again.

    I would put my money on Oracle's functionality rather than hope to best it by anything written by others. Your choice though.

    Remember - Oracle developers have access to a LOT more inside information than anyone else.
    >
    On DBMS_SCHEDULER or JOB the packaging will be inside the logging framework procedure without logging it in actual table at that point in time which will happen when the job will start(independent of the procedure called for data retreival) but my question is if we trigger multiple jobs at the same time that logs on the same table then will there be any overall impact on the database performance.
    >
    How do expect to pass the information to the job without storing it somewhere and committing it first. A job executes in its own session and will not have access to any uncommitted data from the initiating session.
  • 5. Re: Procedure usage logging asynchronously
    429356 Newbie
    Currently Being Moderated
    Hi sb92075,

    Since there are multiple applications that will retreive data using procedures from this central data hub it is very creitcal to know which user has called what and based upon that reports will be geenrated. There are license agreements where in certain cases users are not supposed to retreive certain information that also we want to track. For example if there are 1000 employee details in emp table then some users are supposed to see only say 10 specific employee details and for some it may be some other set of emps. So the list of empid they will send as input parameter should be accordingly(this we are handling in a different way but just in case if somebody is requesting for which he is not authorised we want to track it).

    Regards,
    Sudipta

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points