The SQL trace files produced by the methods discussed previously can be read in their raw form, or they can be translated by the tkprof utility into a more human readable form. The output below lists the usage notes from the tkprof utility in Oracle 10g. The waits parameter was only added in Oracle 9i, so prior to this version wait information had to be read from the raw trace file.
The values of bind variables must be read from the raw files as they are not displayed in the tkprof output. The following section shows an example of gathering SQL trace for a session to give you an idea of the whole process. The following script creates a test table and a procedure that populates it. This procedure will be the subject of our example trace. Gathering the statistics on the empty table may seem odd, but this prevents any dynamic sampling being added to the trace file contents, which would only serve to complicate the file.
Now we know the name of the trace file we can enable tracing, execute the procedure and disable tracing. First you need to ensure that Timed Statistics are enabled in your session, or the trace file will be missing vital information:. Next, for a very large SQL statement, there is a chance the default size limit of the trace file will be exceeded, and vital information about the query will be lost, so set the maximum size to unlimited:. Next, if you are on a busy system, it can be useful to add a unique identifier to your trace file to help find it later:.
At this point, tracing is enabled for your session, so any SQL statements that you run will be traced. Try running any SQL query:. When running this query, you may notice that there is no change, the query runs just the same as it always did and there is no indication tracing is enabled. However, be assured that the trace information is being recorded and written to a file - you just need to know where to look. Sometimes you want to trace a session that is not your own, or that you cannot easily add the code above to enable trace.
If you have sysdba access to the database, the easiest way to enable trace in another session is to use the oradebug command. First you need to identify the SPID of the process you want to trace:. Identifying the session you want may take some work, which is more than I want to talk about here.
Once you have the SPID you want to trace, run the following three commands:. OK, so we have traced the SQL statement above, but where is the trace file? Well, it goes into user dump dest of course.
Our next step is to find the trace file and run tkprof on it. SPID value. So running a simple ls -lta head to list the last 10 modified files will most likely put it at the top of the list. Lets CTL-C out of the tail -f and run the tkprof to generate the human readable report which should give us what we are after the top consuming SQL statements for this session. And believe it or not — to answer this question using TKPROF all you need is two parameters: trace file and output file:.
The value we are after is listed in the 4th column — see below note : I shortened the number of columns so they fit on this screen :. And in this case the highest elapsed time is Finally, there is one last thing left to do — turn off the SQL Trace for the session — otherwise you are running a risk of consuming all space on the database server it if continues to run. I'd appreciate feed back from anyone who has tried this implementation. Thanks once again.
Hello Tom, Which. Please do reply. November 03, - am UTC. Hi Tom, Thanks for your response. Oracle doc. What are those tables? How to get a meaningful output? November 04, - am UTC. A reader, January 09, - pm UTC. Tom, Can you please tell something about this query? Can you tell how it can be corrected? January 10, - am UTC. I can say, I would probably not RUN it at all. Tom, This looks like an expensive query too.
Can you please suggest an alternative to this? Hi Tom Does "alter session set events ' trace name context off'" turn the tracing off and flush the contents? Or do we have to exit the session? This is not an issue if you tune using sql plus but if you are trying to turn on and off the tracing for the same connection without disconnecting e. January 28, - pm UTC. Menon, January 28, - pm UTC. Did not understand why you repeated the "alter session..
Just checking if I missed something. Also, if you are using statement caching, the cursors may not actually get closed. So in effect if you just execute "alter session set events ' trace name context off'", it does not guarantee that the trace file generated will have everything, correct? Thanks veeresh, February 03, - am UTC.
Hi Tom, My doubts in explain plan for report queries. How i can explain plan for those reports queries where datalink exists for more then one query group? February 03, - pm UTC. Datalink veeresh, February 04, - am UTC. How oracle joins internally these two queries?
If I will come to know about that then I can explain plan as a single statement not individually. February 04, - am UTC. February 16, - pm UTC. A reader, February 16, - pm UTC.
Or is that only obtainable through explain plan. I know tkprof shows you reality but does it also show what the estimated cardinality was assumed by the plan with which it actually executed the query? February 17, - am UTC. Then I look for serious mismatches between the guess and reality -- those bad cardinalities lead to bad plans being selected.
Then I look for "why" stale stats, missing stats, incomplete stats HI Tom, We are having major problems on an environment where there are two concurrent data load runnings with large volumes. I've set up the following test case to run when the server is under heavy usuage and low usuage. Do you think this is a suitable test? A reader, February 17, - am UTC. Tom, Thanks for the feedback. We've set up a meeting with the hardware boys.
From what I can gather through research the wait time is unacceptable for a SAN. This is a good example of where a scientific approach can yield divendends.
The undeniable stats provided to the Ops team were the catalyst that led to an investigation. Be scientific in your approach. May 08, - pm UTC. I'll file a bug against 10g. Hi Tom, Would be happy,if you give the sql statement. May 09, - am UTC. Which one to use? Reader, June 08, - am UTC. How can we stop this one? If we want to trace other session how can we do that? Could you please help me in understand this Many Many Thanks. June 08, - am UTC. I always read your suggestion from asktom when ever I want to do something but this time i was really confused and was not able to find the difference.
I hope you will really help me out. Thank you in advance for your time. June 16, - am UTC. You have the real plan that was used at runtime.
Hi I was wondering how can we fix sql text length problem when using tkprof insert option. Is it a bug? July 19, - pm UTC. I see, it's a bug. That is really a pain. I am migrating databases from 8i to 9i and would like to store query statistics in the table to compare afterwards. Do you know any other way or I have to write a parser to parse the tkprof output?
July 22, - pm UTC. Hi I have statspack as well however I would like to store single execution statistics that is why I have used tkprof. I wrote a parser with awk 2 days ago, works well. Now you please tell me how to delete or purge those trace files NOT tkprof output files otherwise my trace files will eat up space in the server.
So through SQL can I remove that? September 11, - am UTC. But you would then have special privileges Hi Tom, i am confused about something, i am working with 2 sessions on the same table. October 08, - am UTC. You are doing work to undo their changes, but for 3.
Hi Tom, yes they aren't much , i am just surprised , i guess that undo work are done using the undo segment and there should be no redo logs needed will you please give us more lite on this issue? October 11, - am UTC. October 11, - pm UTC. We are running third part application on our database servers. One of their module has some issues. And vendors want from us to set up trace file. Any feed back will be appreciated. November 04, - pm UTC.
Tom, How to check which session has trace on? November 20, - am UTC. A reader, December 08, - am UTC. I traced the session that executed this package, but am unable to pinpoint the source of the problem. From the trace file. The tkprof from the trace too doesn't suggest any information pertaining to the error.
Thanks Sanji. February 14, - pm UTC. FieldName chrEq tabFields i. February 15, - am UTC. February 16, - am UTC. I was trying to show what was going wrong in the code above - they are building a string like I was - and the string they built - didn't fit into the string they tried to put it into.
I got it. Sorry for the stupid question. Tom, I have been trying to trace a job. Also I wanted it to be enabled and disabled from within the job; not sure if setting and resetting the Client Identifier in code logic can do the trick.
I could not find any relevant note on metalink. Below is the test script: It requires the EMP table in addition to the script. Is there any setting that you think I am missing here? Or could this be a bug? But for the test given, I can see j has created the session with service and client ID properly set. But no trace file for j too. July 18, - pm UTC. My question is - In Linux nodes whenever i am backing up the alertlog by using mv or cp the OS processes associated i.
Thanks Mahalingesh. April 13, - pm UTC. You'll see the deleted status go away as the backgrounds write to the new one. Is that the elapsed time since the Database had been up and running or since some arbitary time Julian.
0コメント