Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
  SQL Server Tips by Burleson

Viewing Trace Information

Once these commands complete, the trace can be viewed through SQL Profiler by using the Open Trace File option in the tool and selecting the trace file created for this purpose.

Another option is to use the fn_trace_gettable function to view trace results. Once the trace file has been created, a SQL like the following can be issued to view the output, substituting the trace file name for the example one shown:

select
*
from
::fn_trace_gettable
('c:\dev\trace\tracetest.trc',default)


The nice thing about trace data is it is granular in that each execution of a SQL statement is seen as its own atomic unit. The bad thing about trace data is it is granular in that each execution of a SQL statement is seen as its own atomic unit.

Contradictory? Not really. There are some analysis situations in which the DBA needs to see each individual run of a SQL statement even if it is executed 1,000 times. There are other times, however, when the DBA wants an aggregation of a trace file so it can be seen, for example, that a particular SQL statement was run 1,000 times. This helps troubleshoot situations in which a SQL statement runs very fast, but runs much more often than it should and is therefore eating away at resources.

Unfortunately, the DBA cannot use the fn_trace_gettable function and perform aggregations on the TextData column, which contains the actual SQL text because the column type is a text datatype. However, a table from the function where the TextData column is altered to be a long varchar column such as 3000 or so can be created. The DBA can then find out execution counts with a simple query like the following:

select
TextData,
count(*)
from
tracetab
group by
TextData
order by
2 desc


The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks
 

Hit Counter