How to trace a SQL query back to BO user who issued it

[Former Member]

Hello,

I am new to this forum. We are trying to find a solution for the following problem.

We have a Oracle 11g database and BO user use this for reporting.
We have identified queries that takes longest time to execute using Oracle DBA_HIST tables and found that theses are the queries issued by Web Intelligence or Crystal reports. Now we want to trace these queries back to the BO user who issued them. Oracle only gives the schema name which issued the query. I know we can use BO audit tables to track the user within BO but how to match Oracle DBA_HIST data to BO audit tables data. Or is there an another way of doing this?

Most of the crystal reports user query the database directly with their own queries without any universe. So use of END_SQL function to add the BO user name as a comment to all SQL statements is not possible.

[DellSC]

There is an option to include the SQL in the audit details that you can turn on under Auditing. This would give you the information you need. However, turning on this option will greatly increase the amount of data that is added to the auditing database. It will also significantly impact how long it takes the auditing APS to load log files into the database.

I generally recommend only turning this option on for short periods of time to diagnose specific issues - it's NOT something you want to leave turned on all the time.

-Dell