Query Processing and Optimization in Oracle

Query Processing

SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit a number of these stages.

The following figure depicts the general stages of SQL processing.

SQL Parsing

The first stage of SQL processing is parsing.

The parsing stage involves separating the pieces of a SQL statement into a data structure that other routines can process. The database parses a statement when instructed by the application, which suggests that only the application­, and not the database itself, can reduce the number of parses.

When an application issues a SQL statement, the application makes a parse call to the database to organize the statement for execution. The parse call opens or creates a cursor, which may be a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and personal SQL area are within the program global area (PGA).

During the parse call, the database performs the following checks:

Syntax Check

Semantic Check

Shared Pool Check

The preceding checks identify the errors which will be found before statement execution. Some errors can’t be caught by parsing. For example, the database can encounter deadlocks or errors in conversion only during statement execution.

Syntax Check

Oracle Database must check each SQL statement for syntactic validity.

A statement that breaks a rule for well-formed SQL syntax fails the check. for instance, the following statement fails because the keyword FROM is misspelled as FORM:

SQL> SELECT * FORM employees; SELECT * FORM employees * ERROR at line 1: ORA-00923: FROM keyword not found where expected

Semantic Check

The semantics of a statement is its meaning. A semantic check determines whether a statement is meaningful, for instance, whether the objects and columns within the statement exist.

A syntactically correct statement can fail a semantic check, as shown within the following example of a query of a nonexistent table:

SQL> SELECT * FROM nonexistent_table; SELECT * FROM nonexistent_table * ERROR at line 1: ORA-00942: table or view does not exist

Shared pool check

During the parse, the database performs a shared pool check to work out whether it can skip resource-intensive steps of statement processing.

To this end, the database uses a hashing algorithm to get a hash value for each SQL statement. The statement hash value is the SQL ID shown in V$SQL.SQL_ID. This hash value is deterministic within a version of Oracle Database, therefore the same statement during a single instance or in several instances has an equivalent SQL ID.

Parse operations fall into the following categories, depending on the type of statement submitted and the result of the hash check:

Hard parse

If Oracle Database cannot reuse the existing code, then it must build a replacement executable version of the appliance code. This operation is known as a hard parse or a library cache miss.

During the hard parse, the info base accesses the library cache and data dictionary cache numerous times to see the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their order doesn’t change. Latch contention increases statement execution time and reduces concurrency.

Soft parse

A soft parse is any parse that’s not a hard parse. If the submitted statement is that the same as a reusable SQL statement within the shared pool, then Oracle Database reuses the existing code. This reuse of code is called a library cache hit.

Soft parses can vary in what proportion work they perform. For example, configuring the session shared SQL area can sometimes reduce the quantity of latching within the soft parses, making them softer.

Query Optimization

Oracle is one of the foremost used databases where Query optimization plays a crucial role within the performance tuning of the database. Before we get into the details of Query optimization, let us first understand what does Query Optimization means.

What is Query Optimization in Oracle?

Query optimization is the process of selecting the foremost efficient means of executing a SQL statement. Since SQL is non-procedural in nature, hence the optimizer is free to merge, reorganize, and process the SQL statements in any order for the utmost efficiency.

The database optimizes each SQL statement based on statistics collected about the accessed data. The optimizer determines the optimal plan for a SQL statement.

This is done by examining multiple access methods, such as full table scan or index scans, different join methods such as nested loops and hash joins, different join orders, and possible transformations.

How to Optimize SQL query in Oracle?

Here are the query optimization techniques in oracle which you’ll use to performance tune your Oracle database:

1. Start with the System Level SQL tuning

To get started with the SQL query optimization it’s important that you simply perform SQL tuning at the System level first else other changes might get undo automatically resulting in rework in performance tuning.

2. Rewrite complex subqueries with temporary tables

You can easily rewrite complex subqueries in Oracle SQL with Global Temporary Table (GTT) and SQL WITH operator. This is helpful in complex subqueries such as WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views.

Upon tuning with the queries with temporary tables and WITH clause, the performance of the SQL query in Oracle increases by many folds.

3. Index all predicates

For high performance and scalability, Index all predicates of JOIN, WHERE ORDER BY and GROUP BY clauses in SQL queries. In absence of the right indexing, it causes table scanning of SQL queries which consequently leads to performance or locking issues.

Thus, it is good to have all predicates indexed except where the column data has low cardinality.

4. Use Inner Joins instead of Outer Joins

Use outer join only if it is very necessary, otherwise, try replacing it with inner joins for a better SQL query performance. As outer joins slower the SQL execution, thereby affecting optimization, thus using inner joins is highly advisable for better performance.

5. Use CLOB/BLOB columns at end statements

Oracle 10g and Oracle 11g require CLOB/BLOB columns at the end statements, else it might result in an execution failure when the input value size is more than 1000 characters.

Conclusion
In this blog post, we have discussed the concept of Query processing and optimization in oracle.