Home » RDBMS Server » Performance Tuning » Trying to tune a query.
Trying to tune a query. [message #266444] Mon, 10 September 2007 23:08 Go to next message
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 Mad

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.
Embarassed



Re: Trying to tune a query. [message #266506 is a reply to message #266444] Tue, 11 September 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Trying to tune a query. [message #266659 is a reply to message #266444] Tue, 11 September 2007 05:51 Go to previous messageGo to next message
galan_josel
Messages: 7
Registered: September 2007
Junior Member
Application release: 11.5.9
Database: 9.2.0.4
OS: 5.9

Question: Is a way to create a TRACE when the statemnt in question is executed?

Thank you.
Jose.
Re: Trying to tune a query. [message #266660 is a reply to message #266659] Tue, 11 September 2007 05:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, with dbms_system.set_ev or oradebug but you will only get partial data.

Regards
Michel
Re: Trying to tune a query. [message #266662 is a reply to message #266660] Tue, 11 September 2007 05:58 Go to previous messageGo to next message
galan_josel
Messages: 7
Registered: September 2007
Junior Member
Thank you.
Jose.
Re: Trying to tune a query. [message #266865 is a reply to message #266662] Tue, 11 September 2007 19:50 Go to previous messageGo to next message
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 Go to previous message
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.

Previous Topic: Error when I try to connect to a database using sqlplus
Next Topic: What is the different between Oracle parse and application parse?
Goto Forum:
  


Current Time: Sat Dec 10 20:38:05 CST 2016

Total time taken to generate the page: 0.12296 seconds