en_manual_03_03

Pic. 23 Report “Examine”

File info >>>

folder: C:\LightProfiler\trc\samples

file: count_one_ora9.trc

created: 12.01.2012 15:30:44

modified: 05.01.2012 00:19:16

size: 52400 bytes

Main info >>>

oracle version: 9.2.0.8.0

trace level: 12

session(s):

*** SESSION ID:(9.3) 2010-02-11 00:04:24.321

available "dep": 0, 1, 2

contain RPC events: False

contain LOB events: False

Preamble >>>

Dump file c:\oracle\admin\test9\udump\test9_ora_2816.trc

Thu Feb 11 00:04:24 2010

ORACLE V9.2.0.8.0 - Production vsnsta=0

vsnsql=12 vsnxtr=3

Windows 2000 Version 5.2 Service Pack 2, CPU type 586

Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.8.0 - Production

Windows 2000 Version 5.2 Service Pack 2, CPU type 586

Instance name: test9

Redo thread mounted by this instance: 1

Oracle process number: 12

Windows thread id: 2816, image: ORACLE.EXE

Generating a report of this type can be done with any profiler's status, and does not take much time or resources.

To generate a report, select "Reports" - "Examine" via application menu or press the corresponding button on the profiler's toolbar.

Resource profile

Report “Resource profile” is a detailed description of trace-file contents, which includes information about the database wait events¹, SQL statements, runtime or parse errors and other useful information, needed to solve optimization problems, or to debug your code.

The report consists of several html-files that are generated in directory specified by the option "Output folder" (by default - <application folder> / output). The file structure of this report, is schematically represented below:

• <trace-file name>.files – folder for additional data
  • stmtN.html – report for SQL statements with sequence number N². Description of this report can be found in the section Sections for statement.
  • stmtN_<tim>_<nn> - report for SQL statement's snapshot, where N – serial number for SQL statement, <tim> - time of snapshot hold (based on trace-file data), <nn> - serial number of snapshot for SQL statement. Description of this report can be found in the section Sections for snapshot.
• <trace-file name>.html – the main report, represents a summary data about resource profile. Description of this report can be found in the section Sections for profile.

Note:

1. Report “Resource profile” can be generated only for profiler having status - “Parsed”.

Generation of such report may take a long time (depending on size of source trace-file and the profiler options), so, when generating report for a file having a size, greater than 10MB, you will be asked to renounce the use of resource-intensive options:

Pic. 24 Resource-intensive options

To generate a report - select the menu item “Report” (“Active” / “All”) - “Profile” or use application's toolbar or active profiler's toolbar.³. Also, you can use the operation “P&P”, that doing parse and report generation as one operation.

Sections for profile

The current version of application can generate the following sections of report "Resource profile":

• Parameters — this section contains four blocks to show the following information:

Pic. 25 Section “Parameters”

• Profile parameters — session IDs and calculated values for duration (CPU) and elapsed time (including waits)
• SQL statements stat. – number of different types of SQL statements and their levels
• Options — options and their values, used on report's generation
• Trace file info — location, size, main dates of the source trace-file (create, modify)
• Preamble — the trace-file preamble (see Glossary)

Pic. 26 Section “Preamble”

• Response time by events — the distribution of response time for session (see Glossary) by database events, their characteristics (duration time - c or ela, elapsed time - e, the number of occurrences, etc.).

Pic. 27 Section “Response time by events”

• Response time by SQL statements – the distribution of response time for session by SQL statements (see Glossary), time characteristics, the number of occurrences.

Pic. 28 Section “Response time by SQL statements”

• Response time by SQL cursors – the distribution of response time for session by SQL cursors (see Glossary), time characteristics, the number of occurrences.

Pic. 29 Section “Response time by SQL cursors”

• Events — this section contains description for events, that was recorded in the trace-file and their distribution by SQL statements.

Pic. 30 Section “Events”

• Events for SQL statements (recursive/ non recursive) — structure of these sections are similar with structure of the section “Response time by events”, but contains data only for SQL statements with corresponding type (see Glossary).
• Events graph – this session presents the response time as histogram. Shows events are including in each interval, number of occurrences, duration time and etc.

Pic. 31 Section “Events graph”

• SQL statements (overview) — this is a list of the generated SQL statements in order of appearance, and their time characteristics, statistics of database calls and etc. Example of the section illustrated below:

Pic. 32 Section “SQL statements (Overview)”

• SQL statements (brief) — this is a list of the SQL statements in order of descending their response time, contains brief statistics and statement's body. Example of the section illustrated below:

Pic. 33 Section “SQL statements (brief)”

• Datafiles usage — this section contains statistics of datafile's usage for the specified events and for the whole resource profile. This section will be generated during processing events of sequential read or similar, for example. If trace-file does not include such events, then this section will be skipped in the report. In common case, this section contains four blocks: events that sorted by speed, by elapsed time, by raised, by blocks (if the session was worked only with one datafile – then will be generated only first block of section).

Pic. 34 Section “Datafiles usage”

• Snapshots — this section contains list of the SQL statements, that contain cursors with errors or for these cursors was triggered “catcher” (mechanism for recording snapshots of SQL cursor) during trace-file parsing. In the current version of application, “catcher” can record snapshots when following conditions:
  • cursor contains parse error (sentence PARSE ERROR)
  • cursor contains execution or fetching error (sentence ERROR)
  • cursor contains events, which may witnessing about occurred error
  • bind values contains sentence “ora-”
  • bind values contains user-defined values
  • Example of the section illustrated below:

Pic. 35 Section “Snapshots”

Sections for statement

For specific SQL statements, during resource profile creation, may be generated detailed descriptions – the reports “Statement detail”⁴. You can go to this reports, by clicking link “Detail” from section “SQL statements (brief). The current version of application can generate following sections for this report:

• Brief info — section for brief information, it duplicated data from section “SQL statements (brief)”.
• Stats — this section contains summarized data of time characteristics for statement:

Pic. 36 Section “Stats”

• Events — this section presents SQL statement's response time in the context of database's events.

Pic. 37 Section “Events”

• Database calls — this section presents SQL statement's response time in the context of database's calls, adding some specific information.

Pic. 38 Section “Database calls”

• Bind-set(s) — This section contains parameters and values for bind variables, which was used for cursor, included in SQL statement. Also, for each bind variables set, displaying information about accompanying database calls. When using the library ZeroClipboard (see Extensions), each set of variables will have a button "Assemble a SQL statement», when clicked, in the clipboard will be placed command text with the values of bind variables. Below is an example of the section:

Pic. 39 Section “Bind-set(s)”

• Data histograms — this section contains information about ratio of calls number and duration / elapsed time for SQL statement and database calls.

Pic. 40 Section “Data histograms”

• Execution plans — this section contains definition of actual execution plans for the cursors, their structures, number of occurrences and others available parameters.

Pic. 41 Section “Execution plans”

• Datafiles usage — this section is similar with section in report “Resource profile”.
• Thresholds — this is section contains description of structure and some parameters for SQL statement's cursors, that have largest and smallest duration time. Cursors, described in this section, may have the following sub-sections: "Accumulated events", "Database actions", "Binds", "Events graph".
• Events graph — this section is similar with section in report “Resource profile”.
• Snapshots — this section contains list of snapshots that available for SQL statement, which contains: time of snapshot's generation (from trace-file), cause of catcher firing, data of error (if occurred). There is a opportunity for go to the detailed description of snapshot – report “Snapshot”. Below is an example of the section:

Pic. 42 Section “Snapshots”

Sections for snapshot

For the report “Snapshot”, can be generated following sections:

• Description — this is a corresponding row from the section “Snapshots” of detailed description for SQL statement.
• SQL statement — contains identifier and text for SQL statement.
• Events – this section is similar to section “Events” of detailed description for SQL statement.
• Stack – in this section are listed identifiers and body fragments for 5 previous SQL statements.

Optimization

The current version of application provides the following types of optimization's reports:

• Resource profile optimization for session — a comparison of resource profiles by response time
• Resource profile optimization for SQL statement — a comparison of resource profiles by number of occurrences

The optimization's reports are based on two or more analyzed trace-files, and represent a comparison of general characteristics: duration time, elapsed time (in general for profile or for statement), characteristics of events. In the current version of application, comparison is occurring by the pairs (the order of comparison is user-defined on stage before report generation) - the stages. If for comparisons was selected only two trace-files, then the report will contain only one stage, if there are N files, then the report will contain the N-1 stages and the one final stage - the comparison of first and last trace-files.

In the optimization reports are used color highlighting, for results of comparing characteristics of trace-files:

• green – characteristic was decreased > 10 %
• yellow — characteristic was changed up to 10%
• red — characteristic was increased > 10%

Resource profile optimization for session

In the current version of application, each stage of this report contains following sections:

• Files info — brief information from preamble of the trace-files, which form the current stage

Pic. 43 Section “Files info”

• Total stats — this section contains information about comparison of duration time/ elapsed time for trace-files

Pic. 44 Section “Total stats”

• Events (LHS file) / (RHS file) – this section contains simplified variant of the section “Events” of resource profile
• Events (impr.(+)/degr.(-)) - this section illustrate a comparison of characteristics for available events

Pic. 45 Section “Events (impr.(+)/degr.(-))”

Generation of the report "Resource profile optimization for session" consists from the following steps:

1. Select the menu item “Report” - “Optimization”
2. In the dialog box do following:
   1. Select report type - “Session”
   2. If necessary, you can fill an input field “Comment”. The text you type will be included in generated report.
   3. Select at least two profilers, which will be used for report. Also, using controls at the bottom of dialog box (buttons "Up", "Down"), you can sort profilers in order to analyze (downward).

Pic. 46 Generate a report for session

1. Click the button “Continue”, after what, will be executed trace-file's parsing and report's generation.

Note:

1. To generate an optimization report, all profilers that are including in report, should be analyzed with same options. In case, if the profilers options has differences, or parsing for profiler has not yet been fulfilled, then for these profilers will be executed operations "Reset" and "Parse" with same options as for first profiler in list.

Resource profile optimization for SQL statement

In the current version of application, each stage of this report contains following sections:

• Files info — brief information from preamble of the trace-files, which form the current stage
• Brief info (LHS file)/(RHS file) — contains data from the section “Brief info” of SQL statement details
• Stats (impr.(+)/degr.(-)) - comparison characteristics of SQL statements such as duration time, elapsed time and duration for events between database calls
• Events (LHS file) / (RHS file) – this section contains simplified variant of the section “Events” of the resource profile
• Events (impr.(+)/degr.(-)) - this section illustrate a comparison of characteristics for events

Process of the report generating "Resource profile optimization for SQL statement" consists from the following steps:

1. Select the menu item “Report” - “Optimization”
2. In the dialog box do following:
   1. Select report type - “SQL statement”
   2. If necessary, you can fill an input field “Comment”. The text you type will be included in the generated report.
   3. Select at least two profilers, which will be used for report. Also, using controls at the bottom of dialog box (buttons "Up", "Down"), you can sort profilers in order to analyze (downward).

Pic. 47 Generate a report for SQL statement

1. Click the button “Continue”, after what, all selected profiles will be parsed.

Note:

1. To generate an optimization report, all profilers that are including in report, should be analyzed with same options. In case, if the profilers options has differences, or parsing for profiler has not yet been fulfilled, then for these profilers will be executed operations "Reset" and "Parse" with same options as for first profiler in list.
2. After that, you should select some SQL statements, which characteristics will be included in the report. Do this, you can in the appeared dialog box:

1. Pic. 48 Selection of the SQL statements
2. you should select some SQL statement for each trace-file (upper part of the dialog). To select the SQL statement, just double-click on the appropriate row, after what, identifier of selected statement will be added in upper part of the dialog:

1. Pic. 49 Selecting SQL statement by double-click

Note:

1. You must select SQL statement for each trace-file.
2. Press the button “Continue”, after what, will be generated the report.

1Depends on the trace level

2The serial number has assigned from application depending on statement’s occurrences in the source trace-file

3For parsed profilers

4By default, a report "Statement detail" will be created only for statements which was highlighted in any part of the report "Resource profile" or having error/ warnings. This behavior is governed by the profiler options.

< Previous section Content Next section >