This file will contain the formatted trace output. All rights reserved. Register or Login. Welcome back! Sign in with Email. Reset Your Password We'll send an email with a link to reset your password. Stay ahead! Get the latest news, expert insights and market research, tailored to your interests.
Sign up with Email. Sign in with email Enter the email address associated with your account. You auth link is expired or incorrect, please try again. PARSE : Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.
Section 3: Wait Event Information We know if there is problem with a hint about the problem area from tabular statistics and the most costly operation from row source operation, now we can get the details where this query is waiting from wait event information. Elapsed times include waiting on following events: Event waited on Times Max. Share this: WhatsApp Tweet.
Like this: Like Loading Related posts. Leave a Reply Cancel reply Enter your comment here Fill in your details below or click an icon to log in:. Email required Address never made public. Name required. Follow Following. Oracle Database Internal Mechanism Join other followers. Sign me up. Already have a WordPress. Log in now. Statistics about the processed rows appear in the ROWS column. Total number of rows processed by the SQL statement.
This total does not include rows processed by subqueries of the SQL statement. The row source counts are displayed when a cursor is closed.
Exiting or reconnecting causes the counts to be displayed. Timing statistics have a resolution of one hundredth of a second; therefore, any operation on a cursor that takes a hundredth of a second or less might not be timed accurately. Keep this in mind when interpreting statistics. In particular, be careful when interpreting the results from simple queries that execute very quickly.
Sometimes, in order to execute a SQL statement issued by a user, Oracle must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, then Oracle makes recursive calls to allocate the space dynamically.
Recursive calls are also generated when data dictionary information is not available in the data dictionary cache and must be retrieved from disk. You can suppress the listing of Oracle internal recursive calls for example, space management in the output file by setting the SYS command-line parameter to NO.
The statistics for a recursive SQL statement are included in the listing for that statement, not in the listing for the SQL statement that caused the recursive call. So, when you are calculating the total resources required to process a SQL statement, consider the statistics for that statement as well as those for recursive calls caused by that statement. For more information, see "Avoiding the Trigger Trap". These statistics appear on separate lines following the tabular statistics.
Trace files generated immediately after instance startup contain data that reflects the activity of the startup process. For the purposes of tuning, ignore such trace files. The key is the number of block visits, both query that is, subject to read consistency and current that is, not subject to read consistency.
Segment headers and blocks that are going to be updated are acquired in current mode, but all query and subquery processing requests the data in query mode. You can find high disk activity in the disk column. If it is acceptable to have 7.
You can also see that 10 unnecessary parse call were made because there were 11 parse calls for this one statement and that array fetch operations were performed.
You know this because more rows were fetched than there were fetches performed. You might want to keep a history of the statistics generated by the SQL Trace facility for an application, and compare them over time. This script contains:. The script then inserts the new rows into the existing table. Most output table columns correspond directly to the statistics that appear in the formatted output file. The columns in Table help you identify a row of statistics.
This is the date and time when the row was inserted into the table. This value is not exactly the same as the time the statistics were collected by the SQL Trace facility. This indicates the level of recursion at which the SQL statement was issued. For example, a value of 0 indicates that a user issued the statement. A value of 1 indicates that Oracle generated the statement as a recursive call to process a statement with a value of 0 a statement issued by a user.
A value of n indicates that Oracle generated the statement as a recursive call to process a statement with a value of n- 1. This identifies the user issuing the statement. This value also appears in the formatted output file. Oracle uses this column value to keep track of the cursor to which each SQL statement was assigned.
The output table does not store the statement's execution plan. The following query returns the statistics from the output table. If you are not aware of the values being bound at run time, then it is possible to fall into the argument trap.
If the bind variable is actually a number or a date, then TKPROF can cause implicit data conversions, which can cause inefficient plans to be executed. To avoid this, experiment with different data types in the query. The next example illustrates the read consistency trap. Without knowing that an uncommitted transaction had made a series of updates to the NAME column, it is very difficult to see why so many block visits would be incurred.
Cases like this are not normally repeatable: if the process were run again, it is unlikely that another transaction would interact with it in the same way. This example shows an extreme and thus easily detected example of the schema trap.
At first, it is difficult to see why such an apparently straightforward indexed query needs to look at so many database blocks, or why it should access any blocks at all in current mode.
0コメント