Tuesday 22 May 2012

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.
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.
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.
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://docs.oracle.com/cd/E11882_01/server.112/e16508/sqllangu.htm#CHDFCAGA

Sunday 20 May 2012

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