Database Performance Tuning

Oracle terms and Ideas you need to know before beginning

Just to refresh your Oracle skills, here is a short go-through as a starter.

Oracle Parser

It performs syntax analysis as well as semantic analysis of SQL statements for execution, expands views referenced in the query into separate query blocks, optimizing it and building (or locating) an executable form of that statement.

Hard Parse

A hard parse occurs when a SQL statement is executed, and the SQL statement is either not in the <b>shared pool</b>, or it is in the shared pool but it cannot be shared. A SQL statement is not shared if the metadata for the two SQL statements is different i.e. a SQL statement textually identical to a preexisting SQL statement, but the tables referenced in the two statements are different, or if the optimizer environment is different.

Soft Parse

A soft parse occurs when a session attempts to execute a SQL statement, and the statement is already in the shared pool, and it can be used ( that is, shared ). For a statement to be shared, all data, ( including metadata, such as the optimizer execution plan ) of the existing SQL statement must be equal to the current statement being issued.

Cost Based Optimizer

It generates a set of potential execution plans for SQL statements, estimates the cost of each plan, calls the plan generator to generate the plan, compares the costs, and then chooses the plan with the lowest cost. This approach is used when the data dictionary has <b>statistics</b> for at least one of the tables accessed by the SQL statements. The CBO is made up of the query transformer, the estimator and the plan generator.

EXPLAIN PLAN

A SQL statement that enables examination of the execution plan chosen by the optimizer for DML statements. EXPLAIN PLAN makes the optimizer to choose an execution plan and then to put data describing the plan into a database table. The combination of the steps Oracle uses to execute a DML statement is called an execution plan. An execution plan includes an access path for each table that the statement accesses and an ordering of the tables i.e. the join order with the appropriate join method.

Oracle Trace

Oracle utility used by Oracle Server to collect performance and resource utilization data, such as SQL parse, execute, fetch statistics, and wait statistics. Oracle Trace provides several SQL scripts that can be used to access server event tables, collects server event data and stores it in memory, and allows data to be formatted while a collection is occurring.

SQL Trace

It is a basic performance diagnostic tool to monitor and tune applications running against the Oracle server. SQL Trace helps to understand the efficiency of the SQL statements an application runs and generates statistics for each statement. The trace files produced by this tool are used as input for TKPROF.

TKPROF

It is also a diagnostic tool to monitor and tune applications running against the Oracle Server. TKPROF primarily processes SQL trace output files and translates them into readable output files, providing a summary of user-level statements and recursive SQL calls for the trace files. It also shows the efficiency of SQL statements, generate execution plans, and create SQL scripts to store statistics in the database.

To be continued...