Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to find procedure/package by SQL statement(SQL_TEXT) for tuning

Posted on 2004-10-21
4
Medium Priority
?
1,047 Views
Last Modified: 2008-01-09
Hi,

Our systems are running well-known ERP and Oracle 8i or above.
We have performance issues.
We can find problematic SQL statements (SQL full text)by monitoring, but we don't know
which procedure/package the SQL statements were executed from.

Could anyone please tell us how to find procedure/packages by SQL text?

Assumption: we did not set module, action, client_info, etc by using
DBMS_MONITOR and DBMS_APPLICATION_INFO packages.

Thank you in advance.
0
Comment
Question by:drcrazy44
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 300 total points
ID: 12371843
0
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 450 total points
ID: 12372105
Views USER_SOURCE and DBA_SOURCE hold the code. Something like this would be helpful :

SELECT name
FROM user_source
WHERE text = <problematic_SQL>;
0
 
LVL 9

Expert Comment

by:konektor
ID: 12378352
use statspack. documentation : ORACLE_HOME/rdbms/admin/spdoc.txt
0
 

Author Comment

by:drcrazy44
ID: 12383052
Thank you very much for all.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question