Posts Tagged ‘mismatch’
v$sql_shared_cursor
I hit following OTN forum article which was a discussion on typical SQL sharing problem.
There are so many reasons why Oracle does not share the same statement. Oracle records the reason in v$sql_shared_cursor view. In my local 10.2.0.1 database, v$sql_shared_cursor has 53 reasons of unshareability.
But I have a big complaint on v$sql_shared_cursor view. It has 53 columns whose value is ‘Y’ or ‘N’. It’s very annoying to display these values and find the columns whose value is ‘Y’. It’s eye-consuming job.
So I built up my own version of v$sql_shared_cursor, like following:
ed shared_cursor
declare
c number;
col_cnt number;
col_rec dbms_sql.desc_tab;
col_value varchar2(4000);
ret_val number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c,
'select q.sql_text, s.*
from v$sql_shared_cursor s, v$sql q
where s.sql_id = q.sql_id
and s.child_number = q.child_number
and q.sql_text like ''&1''',
dbms_sql.native);
dbms_sql.describe_columns(c, col_cnt, col_rec);
for idx in 1 .. col_cnt loop
dbms_sql.define_column(c, idx, col_value, 4000);
end loop;
ret_val := dbms_sql.execute(c);
while(dbms_sql.fetch_rows(c) > 0) loop
for idx in 1 .. col_cnt loop
dbms_sql.column_value(c, idx, col_value);
if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
'CHILD_NUMBER', 'SQL_TEXT') then
dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
' = ' || col_value);
elsif col_value = 'Y' then
dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
' = ' || col_value);
end if;
end loop;
dbms_output.put_line('--------------------------------------------------');
end loop;
dbms_sql.close_cursor(c);
end;
/
The powerful dbms_sql package enables us to display only the columns whose value is ‘Y’. The result is like following.
@shared_cursor 'select /* share_test */%'
SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID = 9wbx9pbrmhsxm
ADDRESS = 2BB29C74
CHILD_ADDRESS = 2BC811D4
CHILD_NUMBER = 0
--------------------------------------------------
SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID = 9wbx9pbrmhsxm
ADDRESS = 2BB29C74
CHILD_ADDRESS = 2AB8DA4C
CHILD_NUMBER = 1
OPTIMIZER_MODE_MISMATCH = Y
--------------------------------------------------
SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2
SQL_ID = 9wbx9pbrmhsxm
ADDRESS = 2BB29C74
CHILD_ADDRESS = 2AAB1798
CHILD_NUMBER = 2
AUTH_CHECK_MISMATCH = Y
LANGUAGE_MISMATCH = Y
OPTIMIZER_MODE_MISMATCH = Y
--------------------------------------------------
Very intuitive. Following is a demonstration to detect cursor sharing problem using this script.
UKJA@ukja102> create table t1(c1 int, c2 varchar2(100)); Table created. UKJA@ukja102> alter system flush shared_pool; System altered. UKJA@ukja102> var b1 number; UKJA@ukja102> var b2 varchar2(10); UKJA@ukja102> UKJA@ukja102> exec :b1 := 1; PL/SQL procedure successfully completed. UKJA@ukja102> exec :b2 := '0'; PL/SQL procedure successfully completed. UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2; no rows selected UKJA@ukja102> --This is my SQL UKJA@ukja102> @shared_cursor 'select /* share_test */%' SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2BC811D4 CHILD_NUMBER = 0 -------------------------------------------------- PL/SQL procedure successfully completed. UKJA@ukja102> --Change the optimizer mode from all_rows to first_rows UKJA@ukja102> alter session set optimizer_mode = first_rows; Session altered. UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2; no rows selected UKJA@ukja102> -- Yes, mismatch in optimizer mode UKJA@ukja102> @shared_cursor 'select /* share_test */%' SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2BC811D4 CHILD_NUMBER = 0 -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2AB8DA4C CHILD_NUMBER = 1 OPTIMIZER_MODE_MISMATCH = Y -------------------------------------------------- PL/SQL procedure successfully completed. UKJA@ukja102> --Change NLS setting UKJA@ukja102> alter session set nls_sort = 'KOREAN_M'; Session altered. UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2; no rows selected UKJA@ukja102> -- Mismatch in language mode and authentication UKJA@ukja102> @shared_cursor 'select /* share_test */%' SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2BC811D4 CHILD_NUMBER = 0 -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2AB8DA4C CHILD_NUMBER = 1 OPTIMIZER_MODE_MISMATCH = Y -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2AAB1798 CHILD_NUMBER = 2 AUTH_CHECK_MISMATCH = Y LANGUAGE_MISMATCH = Y OPTIMIZER_MODE_MISMATCH = Y -------------------------------------------------- PL/SQL procedure successfully completed. UKJA@ukja102> -- Now I change the length of the bind variable from 10 to 2000!!! UKJA@ukja102> var b2 varchar2(2000); UKJA@ukja102> exec :b2 := '0'; PL/SQL procedure successfully completed. UKJA@ukja102> select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2; no rows selected UKJA@ukja102> -- Mismatch in bind variable UKJA@ukja102> @shared_cursor 'select /* share_test */%' SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2BC811D4 CHILD_NUMBER = 0 -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2AB8DA4C CHILD_NUMBER = 1 OPTIMIZER_MODE_MISMATCH = Y -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2AAB1798 CHILD_NUMBER = 2 AUTH_CHECK_MISMATCH = Y LANGUAGE_MISMATCH = Y OPTIMIZER_MODE_MISMATCH = Y -------------------------------------------------- SQL_TEXT = select /* share_test */ * from t1 where c1 = :b1 and c2 = :b2 SQL_ID = 9wbx9pbrmhsxm ADDRESS = 2BB29C74 CHILD_ADDRESS = 2B10FC70 CHILD_NUMBER = 3 BIND_MISMATCH = Y --------------------------------------------------
The last example clearly shows that the length of bind variable of varchar2 type can be a source of cursor unshareability. Julian Dyke provides a wonderful explanation on this problem.
Unshareable SQL statements have some well-known problems.
- Library cache contention due to increased scan depth
- Execution plan change due to new optimization
- Waste of system resource due to increased hard parse count
Don’t forget v$sql_shared_cursor. It has the answer why your statements are not shared!
Dion Cho - Oracle Performance Storyteller 