In general, there are many reasons you'll see more than one cursor in V$SQL for a given SQL statement.
rajesh@10GR2> create table t(
2 x number,
3 y varchar2(10),
4 constraint t_pk primary key(x)
5 );
Table created.
Elapsed: 00:00:00.21
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.set_table_stats(ownname=>user,tabname=>'T',numrows=>1000000,numblks=>100000);
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
rajesh@10GR2>
So we have a table, and the optimizer is told that there are 1,000,000 rows in it. Now we'll ensure that the shared pool has no cached copies of SQL against this table.
rajesh@10GR2> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.49
rajesh@10GR2> select sql_id,upper(sql_text) as sql_text
2 from v$sql
3 where sql_text like 'SELECT%FROM%LOOK%ME%';
no rows selected
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> declare
2 l_x_num number;
3 l_x_str varchar2(20);
4 begin
5 execute immediate ' alter session set optimizer_mode = all_rows ';
6 for x in (select * from t look_me where y = l_x_num)
7 loop
8 null;
9 end loop;
10
11 for x in (select * from t look_me where y = l_x_str)
12 loop
13 null;
14 end loop;
15 execute immediate ' alter session set optimizer_mode = first_rows ';
16 for x in (select * from t look_me where y = l_x_num)
17 loop
18 null;
19 end loop;
20
21 for x in (select * from t look_me where y = l_x_str)
22 loop
23 null;
24 end loop;
25 end;
26 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> select sql_id,upper(sql_text) as sql_text
2 from v$sql
3 where sql_text like 'SELECT%FROM%LOOK%ME%';
SQL_ID SQL_TEXT
------------- ----------------------------------------
cjhyuxhug0ws2 SELECT * FROM T LOOK_ME WHERE Y = :B1
cjhyuxhug0ws2 SELECT * FROM T LOOK_ME WHERE Y = :B1
cjhyuxhug0ws2 SELECT * FROM T LOOK_ME WHERE Y = :B1
cjhyuxhug0ws2 SELECT * FROM T LOOK_ME WHERE Y = :B1
Elapsed: 00:00:00.06
rajesh@10GR2>
Those four copies may appear identical, but they are different
Cursor 1 used ALL_ROWS and bound a NUMBER datatype
Cursor 2 used ALL_ROWS and bound a STRING datatype
Cursor 3 used FIRST_ROWS and bound a NUMBER datatype
Cursor 4 used FIRST_ROWS and bound a STRING datatype
The differences between cursor pair 1 and 2 and cursor pair 3 and 4 are in the optimizer settings.
rajesh@10GR2> select * from table(dbms_xplan.display_cursor('cjhyuxhug0ws2',0));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
SQL_ID cjhyuxhug0ws2, child number 0
-------------------------------------
SELECT * FROM T LOOK_ME WHERE Y = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21953 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 195K| 21953 (1)| 00:04:24 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("Y")=:B1)
rajesh@10GR2> select * from table(dbms_xplan.display_cursor('cjhyuxhug0ws2',1));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
SQL_ID cjhyuxhug0ws2, child number 1
-------------------------------------
SELECT * FROM T LOOK_ME WHERE Y = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21945 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 195K| 21945 (1)| 00:04:24 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=:B1)
The plans for cursors 1 and 2. Those two plans are very (may be) different—all because of the binds. When you compare a string with a number, an implicit to_number() is placed on the string.
rajesh@10GR2> select * from table(dbms_xplan.display_cursor('cjhyuxhug0ws2',2));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
SQL_ID cjhyuxhug0ws2, child number 2
-------------------------------------
SELECT * FROM T LOOK_ME WHERE Y = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21953 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 195K| 21953 (1)| 00:04:24 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("Y")=:B1)
rajesh@10GR2> select * from table(dbms_xplan.display_cursor('cjhyuxhug0ws2',3));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------
SQL_ID cjhyuxhug0ws2, child number 3
-------------------------------------
SELECT * FROM T LOOK_ME WHERE Y = :B1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21945 (100)| |
|* 1 | TABLE ACCESS FULL| T | 10000 | 195K| 21945 (1)| 00:04:24 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=:B1)
It shows that child cursors 0 and 1 (the first two cursors) differed in a bind mismatch (column B) and the next two cursors differed from the previous ones because of optimizer mode mismatches. This is clearly available in V$sql_shared_cursor.
rajesh@10GR2> SELECT sql_id,child_number,bind_mismatch,optimizer_mode_mismatch
2 FROM v$sql_shared_cursor
3 where sql_id ='cjhyuxhug0ws2'
4 order by child_number;
SQL_ID CHILD_NUMBER B O
------------- ------------ - -
cjhyuxhug0ws2 0 N N
cjhyuxhug0ws2 1 Y N
cjhyuxhug0ws2 2 N Y
cjhyuxhug0ws2 3 Y Y
Elapsed: 00:00:00.35
rajesh@10GR2>
No comments:
Post a Comment