Embedded Files
Frequently Asked Questions
What is — extended SQL trace file ?
Extended SQL trace file – the file containing sequentially written down database actions, caused by requests to DB. This file will by generate after setting of pseudo-event 10046 for session.
What is — session response time ?
The session response time — this is an interval of time between the beginning of sending by the user of request or requests to a DB, and obtaining a results of requests. Thus it is possible to consider "response time" as elapsed time between the click on button «Generate the report» and the moment when report displayed on the screen.
What is - resource profile of session ?
The resource profile of session represents the table containing elements (e.g. events) which makes up response time for session. For example, for the session requesting data from DB, the typical resource profile will contain events of parse, execution, fetch and event of physical reading database files. Following characteristics also are included in this table: total/max./min./average waiting time, an amount of calls to an element. Elements of a resource profile are sorted in reduction of the session response time contribution. This is an example of a session resource profile decomposed by events:
More detailed info about resource profiles you can found in outstanding book «Optimizing Oracle Performance» by Cary Millsap with Jeff Holt.
Where I can find description of extended SQL trace files ?
The main document describing contents of a extended SQL trace file is note ID 39817.1 which you can download from Oracle Metalink (http://metalink.oracle.com).
Where I can find the description of wait events and the recommendation about their adjustment ?
Very good description you can find in the book «Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning» by Richmond Shee, Kirtikumar Deshpande and K Gopalakrishnan or address to the official documentation of Oracle: chapter «Instance Tuning» in «Database Performance Tuning Guide and Reference» (for Oracle9i), chapter «Instance Tuning Using Performance Views» in «Oracle® Database Performance Tuning Guide» (for Oracle 10g/11g). The official documentation is free downloadable on a site http://www.oracle.com.
How do I enable / disable tracing session in Oracle ?
Tracing a session can be enabled using the following commands
1) ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
2) EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,12,'');
Turning off the trace is made in a similar way:
1) ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
2) EXEC SYS.DBMS_SYSTEM.SET_EV(SID,SERIAL#,10046,0,'');
Preferable to use the first option, that is to include a trace within a session, thus avoiding the deficiency or redundancy of data in the extended SQL trace file (A description of the problems with enabling / disabling tracing, see in the book«Optimizing Oracle Per formance» by Cary Millsap with Jeff Holt).
What is tracing level is preferable to use ?
There are several levels of trace pseudo-events 10046:
0 - Data are not displayed
1 - output database calls, execution plans, errors, transaction markers
4 - added output bind variables
8 - added output of the wait events
12 - added output bind variables and wait events
Using a certain level depends on the task at hand. It is recommended to use level 12 for determining the error SQL / PLSQL and 8 for the definition of non-optimal SQL.
Where to find the extended SQL trace files ?
Extended SQL trace files generated on the database server. Their location is determined by the parameter USER_DUMP_DEST. Usually, the server files accessible only for the DBA, so that you can always turn to them for help with the inclusion of trace and obtain the trace file :-).
Note that the trace file may contain sensitive data such as account numbers, pans, etc.
How much time to collect trace data ?
The answer is simple - as much as necessary for the manifestation of an error or run through the loop. For example a user error occurs when performing the calculations in this case, the best will collect data in the interval between when the user of calculation and an error occurs on the screen. If you do not determine the optimal SQL for shortchanging the data set, we can start by gathering a trace on a single loop iteration. In the case of the definition is not optimal SQL for a single calculation - it is better to collect data from task start to get its results.
What are the recommendations are available to work with LightProfiler ?
1) Version of the interpreter. LightProfiler scripts are tested on the following versions of Python: 2.6.4, 2.7, 3.1. If you use an application in the form of scripts Python, use a newer version of the interpreter, this provides an increase in the speed of the application.
2) The size of trace files. There are no restrictions, just be aware that the file size affects the time it is processed and the size of the constructed profile. In my job to deal with enough files to 100-150 MB. If you plan to handle huge files, consider whether all the data you need. Also, to speed up and reduce the size of the resulting profile, you can disable options such as the generation of extended data for event collection and distribution map of events.
What LightProfiler differs from tkprof and trace analyzer ?
I do not understand the resulting profile. What should I do ?
If you have an any question related to the received profile, or an error, please contact us. To send us a message please use page "Contact".
I modified the source code or fixed a bug in it. What's next ?
You can share your refinement with other people. Send us a description of the changes via page "Contact".
When you make the localization of the ________ language ?
Are you ready to take on the translation and have experience of similar work? Send your suggestions via page "Contact".
Page updated
Google Sites
Report abuse