Query Processing and Parsing

Queries in the Oracle need to be broken down into its sub-components and various checks needs to be performed on them before it can actually be executed.
The first phase of parsing a query includes Syntax check followed by Semantic check. Syntax check basically deals with the array of the SQL Grammar i.e. whether the statement/query written exists in the SQL Dictionary or is correct according to the grammar of the SQL Language while Semantic check checks for the existence of the object referred in the query i.e. Semantic check is, in short, the object check.

SQL> select * form tab;
select * form tab
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

Here what it returns is the expression saying, "FROM keyword not found " ... That means something is wrong with the syntax of the statement i.e. the structure of the query is not matching with what is expected. So, it failed at the syntax check ...

Now in semantic check, if the syntax is correct and the objects referred in the query are correct; then it generates the output otherwise not.
By the correctness the object, comes various considerations like privileges, existence etc. But the main point remains the same; the object should be visible to the user and the user should have necessary privileges on the object referred.

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

Semantic check failed since table doesn't exist even though the syntax of the query is correct.


After both the checks are done, Oracle checks if the exactly same query has been processed before i.e. if the work has already been done before. Shared pool, component of SGA (Shared/System Global Area) plays an important part here ... The SGA is a chunk of memory that is allocated by an Oracle Instance (during the no mount stage) and is shared among Oracle processes. The shared pool is used for objects that are shared among all users. Shared Pool has two important components: - Library Cache and Data Dictionary Cache. Data Dictionary cache stores part of the data dictionary because Oracle has to query the data dictionary very often while library cache is further divided into Shared SQL Area, PL/SQL Procedures and Control Structures (Latches and Locks) which stores the SQL text and the execution plan for the query.

Now if the query has been processed before and if it is found in library cache of SGA, then area called cursor area is already allocated in the library cache and execution plan exists there and thus we don’t need to perform the steps like Query Transformation, Plan generation again saving us lot of time ... This is what we called as "SOFT PARSE".

But if the query isn't there in library cache, then what we do, we call it as "HARD PARSE". In short, the steps that are there are: -
1. Oracle unpins the library cache latch and pin the shared pool latch.
2. Then it performs the query transformation steps (Query rewrite, bind variables etc)
3. After the query transformation, Oracle estimates the cost of the query in terms of I/O, Memory and CPU
4. And based on the cost, it generates multiple plans to execute the query and picks the most optimized plan and executes the query.

We can check for the execution plan selected for a query by using set autot command .It displays a report on the execution of successful SQL DML statements (such as SELECT, INSERT, UPDATE, DELETE or MERGE). The report can include execution statistics and the query execution path.

Syntax: -
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

OFF does not display a trace report. ON displays a trace report. TRACEONLY displays a trace report, but does not print query data, if any. EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics.

SQL> set autot on exp stat;
SQL> /

A
----------
1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01
|   1 |  TABLE ACCESS FULL| TEST |     1 |    13 |     2   (0)| 00:00:01
------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
3  consistent gets
0  physical reads
0  redo size
415  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

Execution Plan gives us the cost and the rows etc while statistics gives us the more physical reads and blocks information.


There is another type of parsing also, called as "SOFTER SOFT PARSE". It makes use of the initialization parameter session_cached_cursors. If this parameter is set to some value, then in that case; Oracle takes you query, looks into the session cursor cache, picks up the cursor and reuses it. This involves lesser work, even lesser than SOFT Parse.

SQL> show parameter session_cached;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
session_cached_cursors               integer     50

If it is enabled, then two parameters play an important part from v$statname :- 

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              0

So when a query is parsed for three times and caching of cursors is enabled, it increases the cursor cache count and caches the cursor ... And from next time onwards, uses that cursor increasing the cache hits count.

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                              1

SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                              3

( Cache Count increased) 


SQL> select * from test;

A
----------
1

SQL> select a.name, b.value
2  from v$statname a, v$mystat b
3  where a.statistic# = b.statistic#
4  and lower(a.name) like '%cursor ca%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 3
session cursor cache count                                              3

(Cache Hit increasing ... It increased twice, once for test query and another to display the statistics)...



So, in short, it has three types of parsing: -

1. HARD Parse: - First time query is being executed, parse it (syntax check, semantic check), hash it, look in the shared pool, not present, perform transformations, optimize it etc....
2. SOFT Parse: - Executed before, parse it, hash it, look in the shared pool, present, use it.
3. SOFTER soft Parse: - Look into session cursor cache, found, uses it. (But only if session_cached_cursors set to some value).

In database system, the ratio of SOFT Parse/HARD Parse should be very high since it implies the same query was reused again and again and less work was done.

RECYCLE BIN IN DATABASE

Introduction: -

We always hear the word 'Recycle Bin' in Windows but I never knew that such a word exists in database too till the time I first read it .My first question was has this something to do with Windows Recycle Bin! But the answer is no.

Recycle Bin in database is actually a data dictionary table, which contains information about the dropped objects. So if you have ever dropped a table by mistake and wished you could undo that, recycle bin is to your rescue. When you drop a table, the database does not immediately remove the space associated with the table but instead it renames the table and places it and any associated objects in a recycle bin.

We can enable or disable the recycle bin using the initialization parameter recyclebin.

 

SQL> show parameter bin;



NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------

recyclebin                           string      on

We can set/reset it for a particular session/system by using: -

Alter system set recyclebin=on/off
Alter session set recyclebin=on/off


Using Recycle Bin: -

A user can view his objects in the recycle bin by: -

 

SQL> select * from recyclebin;

No rows selected

Currently, the recyclebin for the user is empty.


Now lets create a table: -

 

SQL> create table test (a number);



Table created.



SQL> select * from recyclebin;



no rows selected

Now lets drop the table, which we created just now: -

 

SQL> drop table test;



Table dropped.

Lets peep into the recycle bin now,

 

SQL> select object_name, original_name, operation from recyclebin;



OBJECT_NAME                                   ORIGINAL_NAME               OPERATION

------------------------------ -------------------------------- ---------

BIN$VLKNkYA8S/arE/fy6G6LCg==$0      TEST                             DROP

When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names so as to avoid name conflicts that may arise if multiple tables have the same name. Like if User A drops a table, re-creates it with the same name, then drops it again, there will be naming conflicts in the recyclebin or if two users have tables with the same name, and both users drop their tables.
The renaming convention, which the recyclebin follows, is: -
BIN$unique_id$version
Where:
Unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
Version is a version number assigned by the database


We can query the object in the recycle bin in the same way we query other similar objects.

 

	SQL> select * from BIN$VLKNkYA8S/arE/fy6G6LCg==$0;

	No rows selected.

Removing from RecycleBin: -

This actually deals with two cases: -
1. We want to remove what’s their in recyclebin
2. We don’t want some particular tables to go to recycle bin, instead directly deleted

In the first case, we can use the PURGE command to remove the entries from the recyclebin. If we want to remove a particular table and all its associated entries, we can use: -

 

SQL> purge table test;



Table purged.



SQL> show recyclebin;

Here in purge table, we can specify the original name of the table or system generated names; the effect remains same for both the commands.

If we want to delete all the entries belonging to particular tablespace; we can use: -

 

	SQL> purge tablespace <>;

And if we want to empty the complete recyclebin,

 	

	SQL> purge recyclebin;

You cannot roll back a PURGE statement, nor can you recover an object after it is purged


In other case, while dropping the table; we can use the purge clause so that it is directly deleted and space is immediately free.

 

SQL> show recyclebin;



SQL> drop table test purge;

Table dropped.



SQL> show recyclebin;

Restoring from Recyclebin: -

To reinstate the table, all we have to do is use the “Flashback Table” command.

 

SQL>flashback table test to before drop rename to test;

Flashback complete.



SQL> select * from test;

         A

----------

         1

         2



SQL> show recyclebin;

We can see the table test is back into the original state where it was before drop. But if the table has the indexes built on it, what happens to them? Lets see…

 

SQL> create table test(a number primary key);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> insert into test values(2);

1 row created.

SQL> drop table test;

Table dropped.



SQL> select object_name,original_name,type from recyclebin;



OBJECT_NAME                    ORIGINAL_NAME

------------------------------ --------------------------------

TYPE

-------------------------

BIN$pjJQ48mbQl297TtOUMII6w==$0 SYS_C0016363

INDEX



BIN$a8rd/wnOQZexb+NOndiqjw==$0 TEST

TABLE



SQL> flashback table test to before drop rename to test;

Flashback complete.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                                		   INDEX

DEPT                             			TABLE

PK_DEPT                      			  INDEX

EMP                            			TABLE

BONUS                        			  TABLE

SALGRADE                  		   	  TABLE

TEST                          		    	 TABLE

BIN$pjJQ48mbQl297TtOUMII6w==$0 INDEX



8 rows selected.

The index still has the system-generated name assigned to it. If you want to get the original name back, you need to do it manually using

 

SQL> ALTER INDEX "BIN$pjJQ48mbQl297TtOUMII6w==$0" RENAME TO test_pk;



Index altered.



SQL> select object_name,object_type from user_objects;



OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

PK_EMP                         INDEX

DEPT                           TABLE

PK_DEPT                        INDEX

EMP                            TABLE

BONUS                          TABLE

SALGRADE                       TABLE

TEST                           TABLE

TEST_PK                        INDEX



8 rows selected.

Recyclebin also supports multiple versions of the same table so that we can get back to any older version: -

 

SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (1);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (2);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> CREATE TABLE TEST (a NUMBER);

Table created.

SQL> INSERT INTO TEST VALUES (3);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE TEST;

Table dropped.



SQL> show recyclebin;



ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------



TEST             BIN$JLFMRW9VTRCtxREKNeyDlg==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$nydz2eBWTM+UJKbCyfKGzw==$0 TABLE        2010-04-02:15:07:16



TEST             BIN$sIjIxu4HRVu4gTp/KTKCkg==$0 TABLE        2010-04-02:15:07:16

Now we have all the three versions of test and we can restore to any table using our flashback command and using the recyclebin name.

 

SQL> Flashback table "BIN$sIjIxu4HRVu4gTp/KTKCkg==$0" to before drop rename to test3;



Flashback complete.



SQL> select * from test3;



      COL1

----------

         2