sql_shared_cursor (转）

ORACLE使用子游标去区分一个不能被共享的SQL，因为虽然SQL相同，但是SQL所指向的对象使不同的。也就是说，这些SQL的父游标都是一样的，HASH_VALUE值都相同。例如，数据库有三个表T，有这样一个语句，select *from T,由于每个T都被不同的对象使用或是用户使用，而在数据库级别，这些语句都是一样的，HASH_VALUE都相同，但是他们的子游标就不同了，这就会产生High Version Counts，由于HASH_VALUE相同，持有LATCH会不放，所以当PARSE的时候就会产生LATCH FREE。这是产生High Version的一个方面。另外正如大家前面所说的，数据库的BUG也会引发这个问题。至于第一个问题，可以通过查询V$SQL_SHARED_CURSOR 得到详细关于子游标不能共享的原因，具体不再描述[@more@]

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: http://groups.google.com/group/comp.databases.oracle.server/msg/03a3b1fed9a16999)

* 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 
www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html
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).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/1933/viewspace-907536/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/1933/viewspace-907536/