How To Catch Running Sqls Obiee 11g

Question

Hello everybody,

I want to see running sql codes on obiee, if its running time passes 1000 seconds. (to take an action otherwise, database is locked and no sql is run) When I see the S_NQ_ACCT table, it shows only ending sqls. Can I see still running ones via a metadata table or a way to catch the case?

Can you help me please.

Regards,

Dilek

rmoff · Answer

Just look in your database, e.g. Real time SQL monitoring https://oracle-base.com/articles/11g/real-time-sql-monitoring-11gr1

If you configure your OBIEE RPD you can see in Oracle which OBIEE users and dashboards account for what SQL being run on the database: http://rittmanmead.com/blog/2015/03/instrumenting-obiee-database-connections-for-improved-performance-diagnostics/

dilek · Answer

Hi, my goal is to catch running sqls (if take take hours to end, sometimes does not end until use all resource and lock the database). The way to look "Manage Sessions" is not efficient because we don't know when it will happen. There is no way?

Gianni Ceresa · Answer

Hi,

The currently running queries can be viewed in the "Administration" page when you look at "Manage sessions".

S_NQ_ACCT is for usage tracking (so stats) and data is inserted not only at the end of queries but in an asynchronous way, so a line can be inserted after some time the query was over.