Databases Reference
In-Depth Information
In cases where the preceding query returns more than a single row, the V$ view
V$SQL_
SHARED_CURSOR
shows what type of mismatch is responsible for the creation of multiple child
cursors. Nevertheless, statements with identical SQL identifiers (
sqlid
) and plan hash values
are processed using the same execution plan. Following is an example that selects only three
out of 61 columns describing potential reasons for a mismatch:
SQL> SELECT child_number, user_bind_peek_mismatch, optimizer_mode_mismatch,
bind_mismatch
FROM v$sql_shared_cursor
WHERE sql_id='9w4xfcb47qfdn';
CHILD_NUMBER USER_BIND_PEEK_MISMATCH OPTIMIZER_MODE_MISMATCH BIND_MISMATCH
------------ ------------------------- ------------------------- -------------
0 N N N
1 N Y N
At this stage both the SQL identifier and the child cursor number are known, such that
DBMS_XPLAN
may be called. The format option ALLSTATS is a shortcut for IOSTATS (column
Buffers
in the plan) combined with MEMSTATS (automatic PGA memory management statistics).
The option LAST requests execution statistics for merely the last as opposed to all executions of a
statement. To improve readability, the following plan table has been split in two parts and the
column
Id
is repeated for each:
SQL> SELECT *
FROM TABLE (dbms_xplan.display_cursor('9w4xfcb47qfdn', 1,
'ADVANCED ALLSTATS LAST +PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 9w4xfcb47qfdn, child number 1
-------------------------------------
SELECT e.last_name, e.first_name, d.department_name FROM hr.employees
e, hr.departments d WHERE e.department_id=d.department_id AND
d.department_id=:dept_id AND e.employee_id=:emp_id AND first_name=:fn
Plan hash value: 4225575861
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | NESTED LOOPS | | 1 | 1 | 38 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 16 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 22 |
|* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | |
-------------------------------------------------------------------------------
