1

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.

closed as unclear what you're asking by ypercubeᵀᴹ, hot2use, LowlyDBA, Erik Darling, Mr.Brownstone Dec 18 '17 at 20:15

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Could that have been a client query (or an ad-hoc query, or whatever it's called in Oracle)? – Andriy M Dec 18 '17 at 8:20
  • One other idea: it could be a query built dynamically. – Andriy M Dec 18 '17 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. – user7009140 Dec 18 '17 at 9:34
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

https://connor-mcdonald.com/2016/01/20/problematic-sql-plsql-is-your-friend/

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