sql_shared_cursor (轉）

In addition to bind size change, data type change also causes Bind Mismatch, 
not Type Check Mismatch, at least according to v$sql_shared_cursor. (for more 
info about this view, see Note:296377.1)

SQL> create table t (s varchar2(10));

Table created.

SQL> declare
  2    s_var varchar2(10) := 'a string';
  3    n_var number := 123;
  4  begin
  5    insert into t (s) values (s_var);
  6    insert into t (s) values (n_var);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> col sql_text for a50
SQL> select address, child_address, sql_text from v$sql where lower(sql_text) like 'insert into t (s) %';

ADDRESS  CHILD_AD SQL_TEXT
-------- -------- --------------------------------------------------
7A3B1920 7A38F464 INSERT into t (s) values (:b1)
7A3B1920 7A38ECB8 INSERT into t (s) values (:b1)

-- The above two SQLs have the same sql_text but are not shared. Hence two 
-- entries in v$sql with the same address and hash_value, but different 
-- child_address, due to bind value data type difference (note the column 
-- Bind_Mismatch). Replace kglhdpar with address in 10g.

SQL> select * from v$sql_shared_cursor where kglhdpar = '7A3B1920';

ADDRESS  KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
7A38F464 7A3B1920 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7A38ECB8 7A3B1920 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
                                              ^
                                  That B is Bind_Mismatch.

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

> Questions about translating v$sql_shared_cursor.
> 
> First, for my particular SQL, I found there are 1026 child cursors.
> But when I plug in the address to v$sql_shared_cursor, it only
> returned 5. So my question is why not 1026?
> 
> Here is SQL's with excessive child cursors:
> 
> HASH_VALUE ADDRESS            COUNT(*)
> ---------- ---------------- ----------
>   10714010 0000000906F1FA70       1026
>   10714010 000000092AEF20E0        438
> 2786767810 00000008D7FEC5C8       1026  2786767810 000000091FBECF20        791
> 3584469599 0000000906F25A48       1026
> 3584469599 000000092AC78870        796
> 
> I am interested in the one pointed to above.
> 
> SQL> select * from v$sql_shared_cursor where KGLHDPAR = hextoraw('00000008D7FEC5C8');
> 
> ADDRESS          KGLHDPAR         U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
> ---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
> 0000000911FE1910 00000008D7FEC5C8 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
> 000000090B258D10 00000008D7FEC5C8 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
> 0000000903A45028 00000008D7FEC5C8 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
> 00000009193B26B0 00000008D7FEC5C8 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
> 0000000919AA9558 00000008D7FEC5C8 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N
> 
> So why does it only return 5? What about the rest 1021 cursors?

I don't know the exact conditions for SQL statements to appear in v$sql 
but not in v$sql_shared_cursor. Possible conditions are

* They're DDLs.

* Part of the cursor optimization info of the SQLs is flushed from library 
cache, causing v$sql.optimizer_mode='NONE'
(Ref: )

* They're recursive SQLs.

In your case, that specific SQL may have optmizer_mode='NONE' in v$sql
for most of the 1021 children. If not, see if other columns of v$sql can give a
clue (i.e. compare other columns between the 1021 rows and the 5 rows)

Yong

> You are right. All the remaining optimizer_mode is "NONE".
> 
> So does that mean all these cursors' "heap 6" (no clue what that means)
> are flushed?
> 
> The question is why so many of them and why isn't the entire child
> cursor aged out?

If optimizer_mode='NONE', then yes their heap 6, which stores execution plans,
is flushed. The reason why not the entire child cursor is flushed may be 
related to cursor invalidations; e.g. DDL was run on base objects. (If 
somebody flushed shared pool, the entire cursor would be out.)

Can you check those NONE optimizer_mode cursors to see if their last_load_time
differs from first_load_time? It should. What time is it? Does it correspond to
the time of some DDL or ANALYZE job?

Yong

> All the last_load_time are blank and we have no analysis of any kind.
> We still run rule mode. The first load time is when the box was started
> midnight 2 weeks ago.

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

Commonly used queries against v$sql_shared_cursor when you don't know the address of a specific cursor

/***** 9i version *****/

select a.* from v$sql_shared_cursor a, v$sql b
where a.address = b.child_address
and b.child_number >= 100;

select * from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%';

--Matching on parent address
select * from v$sql where address in
(select kglhdpar from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%'
);

--Matching on child address
select * from v$sql where child_address in
(select address from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor like '%Y%'
);

/***** end for 9i version *****/

/***** 10gR2 version *****/
Differences from 9i:
 kglhdpar in 9i changed to address       in 10g
 address  in 9i changed to child_address in 10g
 plus much more columns in v$sql_shared_cursor
Note: 10gR1 has more columns than 9i but less than 10gR2

select a.* from v$sql_shared_cursor a, v$sql b
where a.child_address = b.child_address
and b.child_number >= 100;

select * from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor ||
anydata_transformation || incomplete_cursor || top_level_rpi_cursor ||
different_long_length || logical_standby_apply || diff_call_durn ||
bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch ||
stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch ||
top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch ||
mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch ||
px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch ||
litrep_comp_mismatch like '%Y%';

--Matching on parent address
select * from v$sql where address in
(select address from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor ||
anydata_transformation || incomplete_cursor || top_level_rpi_cursor ||
different_long_length || logical_standby_apply || diff_call_durn ||
bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch ||
stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch ||
top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch ||
mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch ||
px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch ||
litrep_comp_mismatch like '%Y%'
);

--Matching on child address
select * from v$sql where child_address in
(select child_address from v$sql_shared_cursor where
unbound_cursor || sql_type_mismatch || optimizer_mismatch || outline_mismatch ||
stats_row_mismatch || literal_mismatch || sec_depth_mismatch ||
explain_plan_cursor || buffered_dml_mismatch || pdml_env_mismatch ||
inst_drtld_mismatch || slave_qc_mismatch || typecheck_mismatch ||
auth_check_mismatch || bind_mismatch || describe_mismatch ||
language_mismatch || translation_mismatch || row_level_sec_mismatch ||
insuff_privs || insuff_privs_rem || remote_trans_mismatch ||
logminer_session_mismatch || incomp_ltrl_mismatch || overlap_time_mismatch ||
sql_redirect_mismatch || mv_query_gen_mismatch || user_bind_peek_mismatch ||
typchk_dep_mismatch || no_trigger_mismatch || flashback_cursor ||
anydata_transformation || incomplete_cursor || top_level_rpi_cursor ||
different_long_length || logical_standby_apply || diff_call_durn ||
bind_uacs_diff || plsql_cmp_switchs_diff || cursor_parts_mismatch ||
stb_object_mismatch || row_ship_mismatch || pq_slave_mismatch ||
top_level_ddl_mismatch || multi_px_mismatch || bind_peeked_pq_mismatch ||
mv_rewrite_mismatch || roll_invalid_mismatch || optimizer_mode_mismatch ||
px_mismatch || mv_staleobj_mismatch || flashback_table_mismatch ||
litrep_comp_mismatch like '%Y%'
);

/***** end for 10gR2 version *****/

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

Sometimes v$sql_shared_cursor fails to reveal why cursors are not shared. It's 
known that when cursor_sharing=similar, a histogram on a column causes hard 
parses (even when the data is not skewed). Test code is from 

except for the first SQL (create table). Test is done on 9.2.0.1.0 and 
reproduced in 10gR1, 10gR2.

create table t as select rownum id from all_objects;
create index t_idx on t (id);
begin
        dbms_stats.gather_table_stats
        ( ownname    => USER,
          tabname     => 'T',
          method_opt => 'for all indexed columns size 254',
          cascade      => TRUE
        );
end;
/
alter session set cursor_sharing=similar;
select * from t CS_SIMILAR where id = 1;
select * from t CS_SIMILAR where id = 50;
select * from t CS_SIMILAR where id = 99;
select * from t CS_SIMILAR where id = 1;
select * from t CS_SIMILAR where id = 50;
select * from t CS_SIMILAR where id = 99;
select sql_text from v$sql where sql_text like 'select * from t CS% where id = %' order by sql_text;

The last query does show 3 rows:
SQL_TEXT
------------------------------------------------
select * from t CS_SIMILAR where id = :"SYS_B_0"
select * from t CS_SIMILAR where id = :"SYS_B_0"
select * from t CS_SIMILAR where id = :"SYS_B_0"

As usual, I want to see why the same SQL is not shared (change kglhdpar to address in 10g; my address '7911C6A0' is from v$sql.address):

SQL> select * from v$sql_shared_cursor where kglhdpar = '7911C6A0';

ADDRESS  KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
-------- -------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
7911C184 7911C6A0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7910BF7C 7911C6A0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7910BA00 7911C6A0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

You see, none of the "reason for not sharing" columns is 'Y'. If I look at the plan (select * from v$sql_plan where address = '7911C6A0'), I see 3 rows with no difference except, of course, under child_number.

This means it doesn't matter whether the data is skewed or not. If you have a histogram, a different SQL child cursor is created (i.e. a hard parse happens). Unfortunately, v$sql_shared_cursor fails to tell us why the sibling cursors are not one single cursor.

I realized that I actually read the same conclusion in Jonathan Lewis's "Cost-Based Oracle" earlier. On p.159 of his book, he says "two things will trigger this reoptimization [when cursor_sharing=similar]: first, if any of the predicates involves a range scan, and second, even on a simple equality, if there is histogram on a column that appears in a predicate, the query will be reoptimized." In my test case, both conditions are met, although just the second condition is enough (as in biti's test at blog.csdn.net/biti_rainy/archive/2004/07/12/39466.aspx).