| Trying to tune a query. [message #266444] |
Mon, 10 September 2007 23:08  |
galan_josel
Messages: 7
Registered: September 2007
|
Junior Member |
|
|
I'm using 11.5.9 e-suite. 9i DB.
I'm using statspack as primary tool to tune my dbs. I have been working for a while in my top 10 queries.
I have been unable to find the source of one of the queries running in the application.
1)I have been looking in the PACKAGES,FUNCTIONS and PROCEDURES.
2)Forms
3)Oracle Reports.
4)Discoverer.
5)Applications Alerts.
6)Concurrent Programs, shell scripts etc.
I'm not familiar with WORKFLOW. Is the only place that I have not be able to see.
I'm cut and paste the query in question form the spreport:
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
3,629,304 4,012 904.6 6.6 533.33 545.62 710680279
SELECT MC.SEGMENT1,MC.DESCRIPTION
FROM MTL_SYSTEM_ITEMS MSI,
MTL_CATEGORIES MC,
MTL_CATEGORY_SETS MCS,
MTL_ITEM_CATEGORIES MIC
WHERE MCS.CATEGORY_SET_NAME = :b1
AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
AND MC.CATEGORY_ID = MIC.CATEGORY_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SEGMENT1 = :b2
MTL_CATEGORIES ALIAS MC IS A VIEW 
I will like to create a trace of the sql, I don't know when is executed and is executed by the same user (apps).
Excluding to AUTOTRACE all sessions.
How I can trace this or find more information about the query.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Trying to tune a query. [message #266865 is a reply to message #266662] |
Tue, 11 September 2007 19:50  |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello galan_josel,
Do you have any reason to use abbreviations?
I tried to understand your environment... also I googled to search for "e-suite"... with no much information.
The information recorded in this forum should be helpful to the others in the future. But with your abbreviations... I am sure that even you will not be able to find your own thread after 6 months.
| Quote: | Application release: 11.5.9
Database: 9.2.0.4
OS: 5.9
|
The OraFAQ Forum Guide should be the Law. Otherwise the OraFAQ content will be unsearchable for everyone further. The quality of information here recorded depends directly on the quality of words used in the posts/threads.
Let's improve the quality of search ability of the OraFAQ forum now.
Thank you,
mson77
[Updated on: Tue, 11 September 2007 19:51] Report message to a moderator |
|
|
|
| Re: Trying to tune a query. [message #266867 is a reply to message #266865] |
Tue, 11 September 2007 21:41 |
galan_josel
Messages: 7
Registered: September 2007
|
Junior Member |
|
|
I know, The information was not organize properlly. Sorry.
But I said in the begining that it was "e-suite". Just check.
What I was looking for was a way to trace the statement inquestion or any other when:
1)- No access to the source of the statement.
2)- No creating traces for the entire db.
3)- No knowing when is going to execute the next time.
The only way that I know is base on knowing the SID when is executed.
DBMS_SYSTEM.SET_EV -> I will like to have the sid. That is my problem.
ORADEBUG also is the same problem. I need the SID.
Thank you in advance.
Jose Galan.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
