TKPROF output can be generated from a raw SQL Trace. It formats and summarizes the diagnostic information from the raw SQL Trace.TKPROF allows you to analyse a trace file in easy way to determine where time is being spent and what query plans are being used on SQL statements.
This post is part 2 of the below posts on trace and tkprof. Please refer other posts to read related information.
This post will cover Part 2: Generating a tkprof file from trace file.
WHAT DOES A TKPROF FILE CONTAINS:
SQL Text that were executed
Timing information: The execution count, elapsed time, CPU time, physical reads (disk), logical reads (query/current) and the number of rows returned for each SQL. This is further broken down into Parse, Execute and Fetch stages.
Wait information: The times waited, maximum wait and total waited for each database wait event, both for each SQL and for the whole period of the trace. It only does this if the level is 8 (with waits) or 12 (with waits and binds). At least level 8 is recommend because it gives this extra information.
Execution Plan: The runtime execution plan for each SQL with the actual row source operation counts for each execution plan line. It only does this if the cursor has been closed for the SQL (in 10g and before) or the row source statistics for the SQL have been written to the trace. The execution plan will also contain the actual tables accessed if the SQL uses views or synonyms.
It also contains information like Library cache misses (hard parse) and total SQL and wait statistics for Recursive and Non-recursive SQL Statements
Tracing a SQL statement, generating TKPROF of the trace and understanding it carefully are some key steps that you will have to do when faced with a tough to break performance issue. This post will describe the methodology of this process.
Tkprof is very useful for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis. The DBA can then identify and resolve performance issues such as poor SQL, indexing, and wait events.
Bind variables are often known as one of the key feature for better SQL query performance. Bind variables as per Oracle documentation is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.
You can think of SQL query as a kind of “function” in any programming language and bind variables as “values” that you pass to the function.
Select * from EMP where EMP_ID=1;
Select * from EMP where EMP_ID=:a;
First statement uses a literal value (1) to run the query while the second SQL statement uses bind variable (:a) to run the SQL statement. The value of (:a) will be provide to Oracle at run time.
A “cursor” is a memory area in the library cache that is allocated to the SQL statement which users execute. This memory area stores key information about the SQL statement like SQL text, SQL execution plan, statistics etc.
Each SQL statement has one Parent cursor and one or more child cursors.Let us understand what a parent and child cursors are.
There are a number of factors that we need to keep in mind to be able to successfully tune a GoldenGate Setup. If you carefully design your system keeping in mind the end goal and apply some proven performance tuning measures you should be able to get real time replication from your GoldenGate setup.
The application connects and disconnects for each database interaction. This problem is common with stateless middleware in application servers. It has over two orders of magnitude impact on performance, and is totally unscalable.
When you are faced with any database related performance issue before you begin troubleshooting it is important to first have some basic ground work done. Below are some key points which should be thought upon before doing actual troubleshooting