Life Cycle of SQL Query in Oracle -I

When the user runs a SQL statement /query it go through different phases before getting the results to the end user. The phases are termed as life cycle of SQL statement.

The life cycle of SQL statements is shown below:

OPEN  à  PARSE  à  BIND  à  EXECUTE  à FETCH

When user starts a session an area in the memory called “UGA” is created and when a SQL statement (Any DML i.e. select/update/insert/delete or DDL i.e. create/alter) is run, A small portion of memory with the UGA allocated called “SQLAREA” or “Cursor” also known as opening of the cursor (OPEN phase in life cycle).  

Every SQL statement will have two cursors a parent and child cursor to store various information such SQL text, execution plan, statistics etc.

A Parent cursor holds the SQL text and a pointer to the child cursor, when the same SQL is run they share the parent cursor. V$SQLAREA view has one row for each parent cursor.

A Child cursor holds the other required information for SQL execution like Object references, Names, types, execution plan, statistics, bind variables etc. V$SQL view has one row for each child cursor.

SQL> select sql_id , to_char(last_active_time, 'dd-MON HH24:MI:SS'),

                     address "parent", child_address "child", child_number

           from v$sql

        where sql_id='d8cfscpcrx26b'

        order by 2;

SQL_ID                 LAST_ACTIVE              PARENT                          CHILD             CNUMBER

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

d8cfscpcrx26b 16-DEC 06:40:18          00000001DB60AC10 00000001DB60A9C0            0

d8cfscpcrx26b 16-DEC 06:44:44          00000001DB60AC10 00000001DB60A050            1

d8cfscpcrx26b 16-DEC 15:30:04          00000001DB60AC10 00000001DB580278            2

d8cfscpcrx26b 16-DEC 15:30:04          00000001DB60AC10 00000001DB2AB630            3

SQL> select * from table( dbms_xplan.display_cursor('d8cfscpcrx26b', 0 ) );

PLAN_TABLE_OUTPUT

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

SQL_ID  d8cfscpcrx26b, child number 0

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

Plan hash value: 3593060818

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

| Id  | Operation                                      | Name                                  | Rows     | Bytes     | Cost (%CPU)       | Time     |

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

|   0 | SELECT STATEMENT                     |                                             |              |               |    19 (100)           |          |

|   1 |  SORT AGGREGATE                       |                                             |     1      |    35       |                             |          |

|   2 |   TABLE ACCESS STORAGE SAMPLE| XTBL_DOMAIN_DECODES |   924   | 32340    |    19   (0)             | 00:00:01 |

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

SQL> select * from table( dbms_xplan.display_cursor('d8cfscpcrx26b', 1) );

PLAN_TABLE_OUTPUT

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

SQL_ID  d8cfscpcrx26b, child number 1

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

Plan hash value: 3593060818

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

| Id  | Operation                                      | Name                                  | Rows     | Bytes     | Cost (%CPU)       | Time     |

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

|   0 | SELECT STATEMENT                     |                                             |              |               |    19 (100)           |          |

|   1 |  SORT AGGREGATE                       |                                             |     1      |    35       |                             |          |

|   2 |   TABLE ACCESS STORAGE SAMPLE| XTBL_DOMAIN_DECODES |   924   | 32340    |    19   (0)             | 00:00:01 |

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

SQL> select * from table( dbms_xplan.display_cursor('d8cfscpcrx26b', 2) );

PLAN_TABLE_OUTPUT

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

SQL_ID  d8cfscpcrx26b, child number 2

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

Plan hash value: 3593060818

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

| Id  | Operation                                      | Name                                  | Rows     | Bytes     | Cost (%CPU)       | Time     |

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

|   0 | SELECT STATEMENT                     |                                             |              |               |    19 (100)           |          |

|   1 |  SORT AGGREGATE                       |                                             |     1      |    35       |                             |          |

|   2 |   TABLE ACCESS STORAGE SAMPLE| XTBL_DOMAIN_DECODES |   924   | 32340    |    19   (0)             | 00:00:01 |

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

The same SQL statement can have more than one child cursor because there are number of factors that decide the cursor sharing foremost is the initialization parameter “cursor_sharing” which has three possible values: EXACT, SIMILAR, and FORCE.

EXACT à Each Statement has its own parent cursor, generates own plan based on the literal value.

FORCE à Only one parent and child cursor for each distinct statement, only one plan is used for all occurrences of that statement.

SIMILAR à Only one parent but different child cursors, generated own plan based on literals.

The initialization parameter “cursor_bind_capture_destination” determines the location at which bind variables that are captured from SQL cursors.

The initialization parameter “cursor_space_for_time” determines de-allocation of SQL area from shared pool, possible values are:

TRUE à Shared SQL areas are not aged out of the pool as long as an open cursor references them.

FALSE à Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.

The initialization parameter “open_cursors” determines the limit that number of cursors/SQL areas can be open in shared pool.

The initialization parameter “session_cached_cursors ” determines number of cursor to cached in library. The repeated parse calls of same SQL statement cause the session cursor to be cached reduces the query response time.

A cursor normally consists of 2 or more heaps, which is a small uniform portion of UGA memory usually in 1K or 4K chunks.

For the above example it’s has shown the SQL has one parent and different child cursors. Since the SQL statement is identical but the SQL has different bind variable value causing to have many child cursors with the same execution plan.

We can query v$sql_shared_cursor to identify why the query is having different child cursors this could be cause of any of the following case mismatch like optimizer_mode, bind_value, literal string etc.

  

SQL> show parameter cursor

NAME                                                             TYPE                      VALUE

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

cursor_bind_capture_destination                       string                     memory+disk

cursor_sharing                                                  string                     EXACT

cursor_space_for_time                                      boolean                FALSE

open_cursors                                                    integer                  1000

session_cached_cursors                                    integer                  50

SQL> select child_number, bind_mismatch B,

                      optimizer_mode_mismatch M, literal_mismatch L

            from v$sql_shared_cursor

          where sql_id = 'd8cfscpcrx26b';

CHILD_NUMBER                  B             M            L

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

           0                                   N            N             N

           1                                   Y            N             N

           2                                   Y            N             N

           3                                   Y            N             N

4 rows selected.

An SQL statement must always be parsed. Then, it can be executed any number of times. If it is a select statement, the result-data needs be fetched for each execution. 

As this is growing bigger 'n' bigger I will post the rest of the life cycle in my next blog title "Life Cycle of SQL Query in Oracle -II". 

Any suggestions/corrections are welcomed. Thanks.

SHARED POOL

The Shared Pool is a memory structure that is shared by all system users.  It consists of both fixed and variable structures.  The variable component grows and shrinks depending on the demands placed on memory size by system users and application programs.

Memory can be allocated to the Shared Pool by the parameter SHARED_POOL_SIZE in PFILE.  The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms. Increasing the value of this parameter increases the amount of memory reserved for the shared pool.You can alter the size of the shared pool dynamically with the ALTER SYSTEM SET command.  

Shared Pool size cannot be > SGA_MAX_SIZE or SGA_TARGET. It is recommended to let Oracle optimize/manage the Shared Pool size by implementing AMM or ASMM to manage memory.

Shared Pool stores the most recently executed SQL statements and used data definitions.  This is because some system users and application programs will tend to execute the same SQL statements often having this information in memory improves system performance. Shared Pool includes Library Cache and Data Dictionary Cache.

SQL> show parameter shared_pool

NAME                                                  TYPE               VALUE

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

shared_pool_reserved_size             big integer      95630131

shared_pool_size                              big integer       0

                                                                                                                                 

SQL> Select sum(bytes/1024/1024) "Size (MB)"  

                FROM V$SGASTAT

                where POOL = 'shared pool';

 Size (MB)

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

      1888 

SQL> SELECT POOL, NAME, BYTES/1024/1024 "Size (MB)"

                  FROM V$SGASTAT 

               WHERE NAME = 'free memory' 

                      AND POOL = 'shared pool';

POOL              Option                                                               Size (MB)

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

shared pool     free memory                                                     376.965912

The Oracle shared pool contains Oracle's library cache, which is responsible for collecting, parsing, interpreting, and executing all of the SQL statements that go against the Oracle database. Hence, the shared pool is a key component, so it's necessary for the Oracle database administrator to check for shared pool contention.

The amount of memory available for the library cache can drastically affect the parse rate of an Oracle database instance. The shared pool advisory statistics provides information about library cache memory to database administrators (DBA’s), allowing them to predict how changes in the size of the shared pool can affect aging out of objects in the shared pool.


The shared pool advisory statistics track the library cache's use of shared pool memory and predict how the library cache will behave in different sizes of shared pools. Determines how much memory the library cache is using, how much is currently pinned, how much is on the shared pool's LRU list, and how much time might be lost or gained by changing the size of the shared pool.

set lines 200

--

COL shared_pool_size_for_estimate     FOR 9999999                    HEA sp_size

COL shared_pool_size_factor                FOR 9999999.9999           HEA sp_size_factor

COL estd_lc_size                                   FOR 999999                      HEA lc_size

COL estd_lc_memory_objects               FOR 99999999                  HEA lc_mem_objs

COL estd_lc_time_saved                       FOR 9999999999              HEA lc_time_saved

COL estd_lc_time_saved_factor            FOR 999999.99999           HEA lc_time_saved_factor

COL estd_lc_load_time                          FOR 999999999                HEA lc_load_time

COL estd_lc_load_time_factor               FOR 9999999.99999         HEA lc_load_time_factor

COL estd_lc_memory_object_hits         FOR 99999999999999      HEA lc_memory_object_hits

--

select * from v$shared_pool_advice;

This above query displays information about estimated parse time in the shared pool for different sizes ranges from 10% of the current shared pool size or the amount of pinned library cache memory, whichever is higher, to 200% of the current shared pool size in equal intervals. The value of the interval depends on the current size of the shared pool.

Shared Pool Components/Contents:

There are many components in shared pool that occupy memory and are shared globally. A number of components in shared pool are fixed in size are allocated at the instance start-up.

The below query shows the contents of shared pool at any given time via a fixed view, The data displayed is sum of all allocation of each component.

SQL>select * from

(select name, round(bytes/1024/1024,4) MB from v$sgastat

 where pool = 'shared pool'

 order by 2 desc)            

where rownum <=10;

NAME                                         MB

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

sql area                                   1522.3323

free memory                           1257.9943

KGH: NO ACCESS                 758.4828

CCursor                                   297.9297

PCursor                                   165.5545

library cache                           118.9883

kglsim object batch                  66.1936

Cursor Stats                            64.0249

kglsim heap                             45.2159

db_block_hash_buckets         45

Some of the Components that are found in shared pool are:

•         SQL (cursors) à SQL statements that gets called repeatedly by many sessions.
•          PL/SQL à Packages and Procedures that gets called repeatedly by many sessions.
•          Library Cache à Metadata for tables, indexes, views, synonyms, dependencies, constraints and others.
•         Row Cache à Cached rows from data dictionary used to build complex SQL and Library Cache objetcs.

The background process MMAN (memory manager) manages the shared pool automatically by allocating memory dynamically on demand. When the server process send a memory request MMAN allocates memory in chunks of (4k or less) depends on the size required. When the shared pools has no space to allocates the memory, MMAN will try to free the space by flushing the aged out objects from the shared pool and if all the objects in the shared pool are pinned (the objects are being currently used in SQL or PL/SQL query execution) then the MMAN throw the error ORA-04301.

There are no. of fixed view to look at the shared memory used by each instance of the SQL or PL/SQL via the views V$SQL, V$SQLAREA, V$SQLSTATS. Its recommended to use v$sqlstats view as it faster and has greater data retention.

SQL>select * from

(select sql_id,plan_hash_value,

       to_char(last_Active_time, 'DD-MON-YYYY HH24:MI') last_active, 

  parse_calls,rows_processed,executions,fetches,loads,

       round(cpu_time/1000/1000, 2) "CPU Time Sec's",

       round(elapsed_time/1000/1000,2) "Elapsed Sec's", 

       round(sharable_mem/1024) "MemKB",

       sorts

from v$sqlstats

order by 10 desc)

where rownum < 30;

To look up the no. of objects by namespace in the library cache of shared pool run the following query:

SQL>select namespace, pins, pinhits, reloads, invalidations

  from v$librarycache

 order by namespace;

To look up the no. of times different type of objects in the shared pool been referred can be seen by the following views:

SQL>COL hit_ratio FOR 999.99

SQL>COL "DML's"   FOR 999999999

--

SQL>SELECT parameter, sum(gets) hits, sum(getmisses) missed,

       100*sum(gets - getmisses) / sum(gets) hit_ratio,

       sum(modifications)  "DML's", sum(dlm_requests) requests,

       sum(dlm_conflicts) conflists, sum(dlm_releases) releases,

       sum(flushes) "FlushToDisk"

 FROM v$rowcache

 WHERE gets > 0

 GROUP BY parameter;