It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

SQL Processing Flow in Oracle

About SQL Processing

SQL processing is the parsing, optimization, row source generation, and execution of a SQL statement. Depending on the statement, the database may omit some of these stages. Figure 1 depicts the general stages of SQL processing
Figure 1 Stages of SQL Processing
Description of Figure 3-1 follows
Description of "Figure 1 Stages of SQL Processing"

1.1 SQL Parsing

As shown in Figure 1, the first stage of SQL processing is parsing. This 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 means 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 prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in 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 that can be found before statement execution. Some errors cannot be caught by parsing. For example, the database can encounter deadlocks or errors in data conversion only during statement execution.

1.1.1 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 example, 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

1.1.2 Semantic Check

The semantics of a statement are its meaning. Thus, a semantic check determines whether a statement is meaningful, for example, whether the objects and columns in the statement exist. A syntactically correct statement can fail a semantic check, as shown in 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

1.1.3 Shared Pool Check

During the parse, the database performs a shared pool check to determine whether it can skip resource-intensive steps of statement processing. To this end, the database uses a hashing algorithm to generate a hash value for every SQL statement. The statement hash value is the SQL ID shown inV$SQL.SQL_ID. This hash value is deterministic within a version of Oracle Database, so the same statement in a single instance or in different instances has the same SQL ID.

When a user submits a SQL statement, the database searches the shared SQL area to see if an existing parsed statement has the same hash value. The hash value of a SQL statement is distinct from the following values:
  • Memory address for the statementOracle Database uses the SQL ID to perform a keyed read in a lookup table. In this way, the database obtains possible memory addresses of the statement.
  • Hash value of an execution plan for the statementA SQL statement can have multiple plans in the shared pool. Typically, each plan has a different hash value. If the same SQL ID has multiple plan hash values, then the database knows that multiple plans exist for this 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 existing code, then it must build a new executable version of the application code. This operation is known as a hard parse, or a library cache miss.

Note:
The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary. When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.

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

Soft parses can vary in how much work they perform. For example, configuring the session shared SQL area can sometimes reduce the amount of latching in the soft parses, making them "softer."

In general, a soft parse is preferable to a hard parse because the database skips the optimization and row source generation steps, proceeding straight to execution.

Figure 2 is a simplified representation of a shared pool check of an UPDATE statement in a dedicated server architecture.
Figure 2 Shared Pool Check
Description of Figure 3-2 follows
Description of "Figure 2 Shared Pool Check"

If a check determines that a statement in the shared pool has the same hash value, then the database performs semantic and environment checks to determine whether the statements have the same meaning. Identical syntax is not sufficient. For example, suppose two different users log in to the database and issue the following SQL statements:
CREATE TABLE my_table ( some_col INTEGER );
SELECT * FROM my_table;
The SELECT statements for the two users are syntactically identical, but two separate schema objects are named my_table. This semantic difference means that the second statement cannot reuse the code for the first statement.
Even if two statements are semantically identical, an environmental difference can force a hard parse. In this context, the optimizer environment is the totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode). Consider the following series of SQL statements executed by a single user:
ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
ALTER SYSTEM FLUSH SHARED_POOL;               # optimizer environment 1
SELECT * FROM sh.sales;

ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;  # optimizer environment 2
SELECT * FROM sh.sales;

ALTER SESSION SET SQL_TRACE=true;             # optimizer enviornment 3
SELECT * FROM sh.sales;

In the preceding example, the same SELECT statement is executed in three different optimizer environments. Consequently, the database creates three separate shared SQL areas for these statements and forces a hard parse of each statement.

2 How Oracle Database Processes DDL
Oracle Database processes DDL differently from DML. For example, when you create a table, the database does not optimize the CREATE TABLEstatement. Instead, Oracle Database parses the DDL statement and carries out the command.
The database processes DDL differently because it is a means of defining an object in the data dictionary. Typically, Oracle Database must parse and execute many recursive SQL statements to execute a DDL statement. Suppose you create a table as follows:
CREATE TABLE mytable (mycolumn INTEGER);

Typically, the database would run dozens of recursive statements to execute the preceding statement. The recursive SQL would perform actions such as the following:
  • Issue a COMMIT before executing the CREATE TABLE statement
  • Verify that user privileges are sufficient to create the table
  • Determine which tablespace the table should reside in
  • Ensure that the tablespace quota has not been exceeded
  • Ensure that no object in the schema has the same name
  • Insert rows that define the table into the data dictionary
  • Issue a COMMIT if the DDL statement succeeded or a ROLLBACK if it did not


3 Shared Pool  Memory Allocation and Reuse

In general, any item (shared SQL area or dictionary row) in the shared pool remains until it is flushed according to a modified LRU algorithm. The memory for items that are not being used regularly is freed if space is required for new items that must be allocated some space in the shared pool. A modified LRU algorithm allows shared pool items that are used by many sessions to remain in memory as long as they are useful, even if the process that originally created the item terminates. As a result, the overhead and processing of SQL statements associated with a multiuser Oracle system is minimized.

When a SQL statement is submitted to Oracle for execution, Oracle automatically performs the following memory allocation steps:

    1. Oracle checks the shared pool to see if a shared SQL area already exists for an identical statement. If so, that shared SQL area is used for the execution of the subsequent new instances of the statement.Alternatively, if there is no shared SQL area for a statement Oracle allocates a new shared SQL area in the shared pool. In either case, the users private SQL area is associated with the shared SQL area that contains the statement.
    2. Oracle allocates a private SQL area on behalf of the session. Thelocation of the private SQL area depends on the type of connection established for the session.

Oracle also flushes a shared SQL area from the shared pool in these circumstances:

  • When the DBMS_STATS statement is used to update or delete the statistics of a table, cluster, or index, all shared SQL areas that contain statements referencing the analyzed schema object are flushed from the shared pool. The next time a flushed statement is run, the statement is parsed in a new shared SQL area to reflect the new statistics for the schema object.
  • If a schema object is referenced in a SQL statement and that object is later modified in any way, the shared SQL area is invalidated (marked invalid), and the statement must be reparsed the next time it is run.
  • If you change a databases global database name, all information is flushed from the shared pool.
  • The administrator can manually flush all information in the shared pool to assess the performance (with respect to the shared pool, not the data buffer cache) that can be expected after instance startup without shutting down the current instance. The statement ALTER SYSTEM FLUSH SHARED_POOL is used to do this.
Note: A shared SQL area can be flushed from the shared pool, even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to run its statement, Oracle reparses the statement, and a new shared SQL area is allocated in the shared pool.

You Might Also Like

Related Posts with Thumbnails

Pages