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.
No comments:
Post a Comment