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
Oracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.
To import a set of SQL plan baselines from one system to another: