I want to know the name of database object which is running the part of my query which is causing problem

From the awr report, I found one query which is making some performance issue, but I am not able to find that query anywhere in the database. Is there any way to find where that is being used in the DB in Oracle 10g? Like in which view or package or procedure.

  • Could that have been a client query (or an ad-hoc query, or whatever it's called in Oracle)?
    – Andriy M
    Dec 18, 2017 at 8:20
  • One other idea: it could be a query built dynamically.
    – Andriy M
    Dec 18, 2017 at 8:22
  • yes i believe now its being written and called at the application end , its a inline subquery , so the developer wrote in application itself instead of writing a procedure and calling it. Dec 18, 2017 at 9:34

1 Answer 1


The AWR report will contain the SQL_ID. You can query V$SQL with this to locate additional metadata such as the MODULE, ACTION, PARSING_USER_ID. Also, if the SQL came from a stored procedure, the PROGRAM_ID, PROGAM_LINE# columns point to the (stored procedure) object concerned.

I've got an example of the latter on my blog


Not the answer you're looking for? Browse other questions tagged or ask your own question.