How select statement works:Parsing SQL Statements in Oracle

Parsing SQL Statements in Oracle

How Oracle Database processes SQL statements ?

For performance tuning basic knowledge a developer or tuning expert needs to know the basic execution of the select statement works.

What happens when a select statement get issues in the DB  (select * from emp) 

 This explains the way in which the database processes DDL statements to create objects, DML to modify data, and queries to retrieve data.

SQL Parsing

 First stage of SQL processing is PARSING

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 PGA.

PGA :  Program Global Area (PGA) is a memory buffer that contains data and control information for a server process.

             Every session will have its own PGA

Cursors: The Cursor is a handle (name or a pointer) for the memory associated with a specific statement. 

                A cursor is basically an Area allocated by Oracle for executing the SQL statements. 

Steps in Parsing

·          Syntax Check

·         Semantic Check

·         Shared Pool Check

Syntax Check

A statement that breaks a rule for well-formed SQL syntax fails the check.  

syntactic checking verifies that keywords,

·          object names

·          operators

·          delimiters

 and so on are placed correctly in your SQL statement.

For example, the following embedded SQL statements contain syntax errors: 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

Semantic Check


Meaning. Thus, a semantic check determines whether a statement is meaningful , Do the tables and
columns referenced in the SQL statement actually exist in the database? Does the user executing the
statement have access to the objects and are the proper privileges in place? Are there ambiguities in the
statement? 

  SQL> select * from table_doesnt_exist;
  select * from table_doesnt_exist
                *
  ERROR at line 1:
  ORA-00942: table or view does not exist




Shared Pool Check(Technical Aspects )





 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 in V$SQL.SQL_ID.


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. 


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.


Latch :A
latch is a low-level internal lock used by Oracle to protect memory structures
(for example if you are issuing a statement at time table            structure should not be modified
like drop column).A low-level serialization control mechanism used to protect
shared data structures in the SGA from simultaneous access.


library
cache :.
The Library Cache is a piece of memory within the SGA that Oracle uses in order
to store SQL StatementsAn area of memory in the shared pool.
This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQLprocedures
and packages, and control structures such as locks and library cache handles.


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 the amount of
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.


Hard Parse vs. Soft Parse


The Oracle database now needs to check in the Shared Pool to
determine if the current SQL statement being parsed has already been processed
by any other sessions.


If the current statement has already been
processed, the parse operation can skip the next two functions in the process: Optimization and Row
Source Generation. If the parse phase does, in fact, skip these two functions,
it is called a soft parse. A soft parse will save a considerable amount of
CPU cycles when running your query. On the other hand, if the current SQL
statement has never been parsed by another session, the parse phase must
execute ALL of the parsing steps. This type of parse is called a hard
parse. It is especially important that developers write and design queries that
take advantage of soft parses so that parsing phase can skip the optimization
and row source generation functions, which are very CPU intensive and a point
of contention (serialization). If a high percentage of your queries are being hard-parsed, your system
will function slowly, and in some cases, not at all.


Oracle uses a piece of memory called the
Shared Pool to enable sharing of SQL statements. The Shared Pool is a piece of
memory in the System Global Area (SGA) and is maintained by
the Oracle database. After Oracle completes the first two functions of the
parse phase (Syntax and Semantic Checks), it looks in the Shared Pool to see if
that same exact query has already been processed by another session. Since
Oracle has already performed the semantic check, it has already determined:


Exactly what table(s) are involved


That the user has access privileges to the
tables.


Oracle will now look at all of the queries in
the Shared Pool that have already been parsed, optimized, and generated to see
if the hard-parse portion of the current SQL statement has already been done.





Why not Check the Shared Pool First?


Oracle will always
keep an unparsed representation of the SQL code in the Shared Pool, and that
the database will perform a hashing algorithm to quickly located the SQL code.
OK, so why doesn't Oracle make this step (checking the Shared Pool for a
matching statement) the first step in its parsing phase, before making any
other checks. Even when soft parsing,
Oracle needs to parse the statement before it goes looking in the Shared Pool,One
of the big reason's

select * from emp;
Assume that this query was first
submitted by user "SCOTT" and that the "emp" table in the
FROM clause is a table owned by SCOTT. You then submit the same exact query (as
a user other than SCOTT) to Oracle. The database has no idea what
"emp" is a reference to. Is it a synonym to another table? Is it a
view in your schema that references another table? For this reason, Oracle
needs to perform a Semantic Check on the SQL statement to ensure that the code
you are submitting is going to reference the same exact objects you are
requesting in your query





SQL Optimization



Query optimization is the process of choosing the most efficient means of executing a SQL statement.
The database optimizes queries based on statistics
collected about the actual data being accessed. The optimizer uses the number
of rows, the size of the data set, and other factors to generate possible
execution plans, assigning a numeric cost to each plan. The database uses the
plan with the lowest cost.



The database must perform
a hard parse at least once for every unique DML statement and performs
optimization during this parse. DDL is never optimized unless it includes a DML
component such as a subquery that requires optimization.



SQL Row Source Generation


The row source generator is software that receives the optimal execution plan from
the optimizer and produces an iterative plan, called the query plan,
that is usable by the rest of the database. The iterative plan is a binary
program that, when executed by the SQL virtual machine, produces theresult set. 





A row source is
an iterative control structure. It processes a set of rows, one row at a time,
in an iterated manner. A row source produces a row set.The row source
can be a table, view, or result of a join or grouping operation. Each row source produced by the row source generator is executed by the SQL execution engine.






SQL Statement Processing




SQL Statements are processed differently depending on whether the statement is a query, data manipulation language (DML) to update, insert, or delete a row, or data definition language (DDL) to write information to the data dictionary. 









Processing a query:

·        Parse:

o   Search for identical statement in the Shared SQL Area.

o   Check syntax, object names, and privileges.

o   Lock objects used during parse.

o   Create and store execution plan.

·        Bind: Obtains values for variables.

·        Execute: Process statement.

·        Fetch: Return rows to user process.




Processing a DML statement:

·        Parse: Same as the parse phase used for processing a query.

·        Bind: Same as the bind phase used for processing a query.

·        Execute:

o   If the data and undo blocks are not already in the Database Buffer Cache, the server process reads them from the datafiles into the Database Buffer Cache.

o   The server process places locks on the rows that are to be modified. The undo block is used to store the before image of the data, so that the DML statements can be rolled back if necessary.

o   The data blocks record the new values of the data.

o   The server process records the before image to the undo block and updates the data block.  Both of these changes are made in the Database Buffer Cache.  Any changed blocks in the Database Buffer Cache are marked as dirty buffers.  That is, buffers that are not the same as the corresponding blocks on the disk.

o   The processing of a DELETE or INSERT command uses similar steps.  The before image for a DELETE contains the column values in the deleted row, and the before image of an INSERT contains the row location information.




Processing a DDL statement:

·        The execution of DDL (Data Definition Language) statements differs from the execution of DML (Data Manipulation Language) statements and queries, because the success of a DDL statement requires write access to the data dictionary.

·        For these statements, parsing actually includes parsing, data dictionary lookup, and execution.  Transaction management, session management, and system management SQL statements are processed using the parse and execute stages.  To re-execute them, simply perform another execute.





Reference

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_10.shtml

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76992/optimops.htm#29149
http://docs.oracle.com/cd/E11882_01/server.112/e16508/sqllangu.htm#CHDFCAGA

Rollback after commit:Flashback Architecture

 FlashBack  Architecture

There are a number of flashback levels

row level	flashback query, flashback versions query, flashback transaction query
table level	flashback table, flashback drop
database level	flashback database

What is FLASHBACK ?

past experience

1.) Why we need Flashback query?

Oracle introduced flashback features in Oracle 9i and 10g to address simple data recovery needs .Retrieves data from a past point in time Backup and recovery capabilities are provided by the database management systems which ensure the safety and protection of valuable enterprise data in case of data loss however, not all data-loss situations call for a complete and tedious recovery exercise from the backup.

2.) What needs to be done for the flash back query enable ?

Configuration before using Flashback Queries

In order to use this feature, the database instance has to be configured. 

SQL> show parameter UNDO;

NAME                    TYPE            VALUE
----------------------- --------------  -----------    
undo_management         string           AUTO
undo_retention          integer          900
undo_suppress_errors    boolean          TRUE
undo_tablespace         string           UNDO_TBSPC

What Is Undo?

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

·         Roll back transactions when a ROLLBACK statement is issued

·         Recover the database

·         Provide read consistency

·         Analyze data as of an earlier point in time by using Oracle Flashback Query

·         Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.

What DBA has to do ?

To be able to use flashback query, we require the following system elements:

• undo_management=auto (set in pfile/spfile);
• undo_retention=n (set in pfile/spfile, where n is a positive number of seconds);
• undo_tablespace=[undo tablespace name] (set in pfile/spfile);
• FLASHBACK or FLASHBACK ANY system privilege; and
• EXECUTE on DBMS_FLASHBACK.

(Show Parameter UNDO)

This command displays all the necessary parameters for using the Flashback Queries

undo_retention 

Property	Description
Parameter type	String
Syntax	UNDO_MANAGEMENT = { MANUAL | AUTO }
Default value	MANUAL
Modifiable	No
Basic	Yes
Real Application Clusters	Multiple instances must have the same value.

After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace. If retention guarantee is not enabled, the database can overwrite unexpired undo when space is low, thus lowering the undo retention for the system. This option is disabled by default.

WARNING:

Enabling retention guarantee can cause multiple DML operations to fail. Use with caution.

You enable retention guarantee by specifying the RETENTION GUARANTEE clause for the undo tablespace when you create it with either the CREATE DATABASEor CREATE UNDO TABLESPACE statement. Or, you can later specify this clause in an ALTER TABLESPACE statement. You disable retention guarantee with theRETENTION NOGUARANTEE clause.

You can use the DBA_TABLESPACES view to determine the retention guarantee setting for the undo tablespace. A column named RETENTION contains a value ofGUARANTEE, NOGUARANTEE, or NOT APPLY (used for tablespaces other than the undo tablespace).

Setting the Undo Retention Period

Do one of the following:

• Set UNDO_RETENTION in the initialization parameter file.

·         UNDO_RETENTION = 1800

·          

• Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

·         ALTER SYSTEM SET UNDO_RETENTION = 2400;

The effect of an UNDO_RETENTION parameter change is immediate, but it can only be honored if the current undo tablespace has enough space.

undo_management

Property	Description
Parameter type	String
Syntax	UNDO_MANAGEMENT = { MANUAL | AUTO }
Default value	MANUAL
Modifiable	No
Basic	Yes
Real Application Clusters	Multiple instances must have the same value.

UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

undo_suppress_errors   

Parameter type	Boolean
Default value	False
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

UNDO_SUPPRESS_ERRORS enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true" to the tool to suppress the ORA-30019 error.

If you want to run in automatic undo management mode, ensure that your tools or applications are updated to run in automatic undo management mode.

undo_tablespace        

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace. A default undo tablespace is then created at database creation. An undo tablespace can also be created explicitly,referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

When the instance starts, the database automatically selects the first available undo tablespace.If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace.

If the database contains multiple undo tablespaces, you can optionally specify at startup that you want to use a specific undo tablespace. This is done by setting the UNDO_TABLESPACE initialization parameter, as shown in this example:

UNDO_TABLESPACE = undotbs_01

UNDO_TABLESPACE

An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Managing Undo Tablespaces

This section describes the various steps involved in undo tablespace management and contains the following sections:

·         Creating an Undo Tablespace

·         Altering an Undo Tablespace

·         Dropping an Undo Tablespace

·         Switching Undo Tablespaces

·         Establishing User Quotas for Undo Space

In addition to the above your DBA will have to grant:

1.    FLASHBACK privilege to the user for all or a subset of objects

2.    Execute privileges on the dbms_flashback package

How it will work ? HANDS ON.

Using the Flashback Query with AS OF clause:

Using the AS OF clause with time

--1.Create table the employee table.

CREATE TABLE EMPLOYEE(EMP_ID  NUMBER,

NAME    VARCHAR2 (30)                ,         

AGE     NUMBER

)

--2.Have some records entered into the employee table.

INSERT INTO EMPLOYEE VALUES(1,'AAAAA',11);

INSERT INTO EMPLOYEE VALUES(2,'AAAAA',22);

INSERT INTO EMPLOYEE VALUES(3,'AAAAA',33);

INSERT INTO EMPLOYEE VALUES(4,'AAAAA',44);

INSERT INTO EMPLOYEE VALUES(5,'AAAAA',55);

COMMIT;

SELECT * FROM EMPLOYEE;

--3.Delete or update some or all of the records from the employee table and commit the transaction.

DELETE EMPLOYEE WHERE EMP_ID = 4;

COMMIT;

UPDATE EMPLOYEE SET EMP_ID = 100 WHERE EMP_ID = 3;

COMMIT;

SELECT * FROM EMPLOYEE;

--4 Select using Flashback query

  SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:38:58','DD-MON-YY HH24: MI: SS');

--My table created at time '14-MAY-12 10:38:58'

    

 5)

 SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:39:58','DD-MON-YY HH24: MI: SS'); 

PREVIOUS DATA RECOVERED '14-MAY-12 10:39:58'

  SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:40:58','DD-MON-YY HH24: MI: SS');

SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:42:48','DD-MON-YY HH24: MI: SS');

SELECT * FROM EMPLOYEE  AS OF TIMESTAMP TO_TIMESTAMP ('14-MAY-12 10:42:58','DD-MON-YY HH24: MI: SS');

Using the AS OF clause - with SCN

(SELECT * FROM EMPLOYEE AS OF SCN 203836360);

Systems Change Number (“SCN”)

The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.

Restrictions

the server must be configured to use Automatic Undo Management.

• No DDL or DML can be issued when using Flashback Query.
• Flashback Query does not reverse DDL operations such as DROP, only DML alterations to the data.
• Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.

Oracle Flashback Version Query

 Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval.

How Row Version will be created ?

A row version is created whenever a COMMIT statement is executed.

Pseudocolumn Name	Description
VERSIONS_STARTSCN VERSIONS_STARTTIME	Starting System Change Number (SCN) or TIMESTAMP when the row version was created. This pseudocolumn identifies the time when the data first had the values reflected in the row version. Use this pseudocolumn to identify the past target time for Oracle Flashback Table or Oracle Flashback Query. If this pseudocolumn is NULL, then the row version was created before start.
VERSIONS_ENDSCN VERSIONS_ENDTIME	SCN or TIMESTAMP when the row version expired. If this pseudocolumn is NULL, then either the row version was current at the time of the query or the row corresponds to a DELETE operation.
VERSIONS_XID	Identifier of the transaction that created the row version.
VERSIONS_OPERATION	Operation performed by the transaction: I for insertion, D for deletion, or U for update. The version is that of the row that was inserted, deleted, or updated; that is, the row after an INSERT operation, the row before a DELETE operation, or the row affected by an UPDATE operation. For user updates of an index key, Oracle Flashback Version Query might treat an UPDATE operation as two operations, DELETE plus INSERT, represented as two version rows with a D followed by an I VERSIONS_OPERATION.

HANDS ON

Step 1.    Create Table

 Step 2 insert values

 Step 3 Update the existing table

Step 4) After updating select from CHILD_LOCATOR

Step 5)Version Query  when we run we will get Previous  all version(all changes in the table)

Flashback Transaction query

Oracle has provided a new view, FLASHBACK_TRANSACTION_QUERY, to provide more
information about the data versions. This includes the SQL required to reverse each change. Queries
against this view are documented as "flashback transaction queries" and require the
SELECT ANY TRANSACTION system privilege. The view definition is as follows.

Row selection