vangogpeter
asked on
10046 trace
How we will find the 10046 trace file?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sir,
from OS LEVEL OR DB LEVEL
Please help me to get exactly 10046 or 10053
just that one.
from OS LEVEL OR DB LEVEL
Please help me to get exactly 10046 or 10053
just that one.
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.
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.
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.
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.
ASKER
We want to spool the output of 10046 and 10053 in a file or get the just to display anyu clue ..
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.
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.
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.
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.
ASKER
I want to go the os level and view. Please help. Which location is or else how we will capture.
>>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.
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.
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.