Solved

10046 trace

Posted on 2014-04-29
11
398 Views
Last Modified: 2014-05-20
How we will find the 10046 trace file?
0
Comment
Question by:vangogpeter
  • 6
  • 3
  • 2
11 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40030847
In 10g and earlier, they should be in the location specified by the init parameter user_dump_dest.

In 11g and up, they should be in the location specified by the init parameter diagnostic_dest (and then you go down the tree from there into instance/instance/trace I believe.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40030854
What is your Oracle version?

In 11g it should be in the trace folder under diagnostic_dest spfile parameter.

try:
SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

In previous versions I believe it is under the folder pointed to by the user_dump_dest spfile parameter.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40030876
I should also point out that only in very specific instances should you ever need to trace 10046 for performance tuning.

I have only used this in very extreme circumstances.

For 99% of my SQL tuning I use explain plan.  Once If get it close, I switch to the autotrace option in sqlplus for more detail if necessary.

For example in sqlplus:
set autotrace on

select 'hello' from dual;

set autotrace off

Open in new window


If you don't actually want to see the output from the select then:
set autotrace traceonly

The difference between explain plan and autotrace:
explain plan doesn't actually execute the statement.

using autotrace will execute the statement.
explain plan is very useful for statements that take a lot of time to execute.

When BOTH of these methods don't achieve the tuning goal, then any only then will I actually perform a 10046 trace.
0
 

Author Comment

by:vangogpeter
ID: 40030886
Sir,

from OS LEVEL OR DB LEVEL

Please help me to get exactly 10046 or 10053

just that one.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40030895
So the question is really how to perform a 10046 trace?

http://www.dba-oracle.com/oracle_news/2004_7_9_rittman.htm

Activating event 10046 is actually quite straightforward for your current session, and takes the form of

    alter session set events '10046 trace name context forever, level num';

Where num is either 1,4,8 or 12, with 1 being the same as a normal set sql_trace=true, 4 including values of bind variables, 12 including wait events, and 12 including both bind variables and wait events.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40030900
As far as the location of the files, that has already been answered.

The file name should be the one with the process or session id of the session or process that created it.  I forget which.

I typically look for the one with the latest date/time stamp.  I open it and if it looks like the trace file I'm after, I use that one.

Less work involved that way.
0
 

Author Comment

by:vangogpeter
ID: 40030910
We want to spool the output of 10046 and 10053 in  a file or get the just to display anyu clue ..
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40031030
You don't 'spool' the output.  spool is a sqlplus command to capture the output of executed statements.

You alter the session to enable the trace.  The trace file is generated in the directories mentioned above.

You find the trace file then perform whatever actions you wish on it.  Typically this is a tkprof to make it somewhat more human readable (but barely human readable).

I'm not sure what else you are missing.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40031040
I guess a key point here is what is it that you're trying to achieve with the trace?  What are you hoping to gain?

If you are trying to performance tune, slightwv's comment above about using autotrace / explain and trying to hit the large things up front (missing indexes, etc) can get you large gains before trying to hit the trace (which not only shows accesses to user tables but also all the Oracle system tables as well).

It's a horrible file to read at the best of times.
0
 

Author Comment

by:vangogpeter
ID: 40032037
I want to go the os level and view. Please help. Which location is or else how we will capture.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40032049
>>Please help. Which location is or else how we will capture.

We have already provided this information in previous posts.

After you trace the SQL statements the resulting trace file is either in the folder pointed to by the user_dump_dest or diagnostic_dest spfile parameters depending on your Oracle version.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now