查看v$sql_shared_cursor视图获取sql语句为什么不能共享?

[20111213]查看v$sql_shared_cursor视图获取sql语句为什么不能共享?

查询v$sql_shared_cursor视图获取sql语句为什么不能共享，在GUI界面下，由于不能共享的原因很多，视图字段N多，大部分都是'N'的数值，检查这个是一个很费时间的操作。

想到了TOM大师的print_table过程，应该修改一下很容易实现这个功能。

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

修改一下，很容易实现这个功能：

CREATE OR REPLACE PROCEDURE print_shared_cursor (p_query IN VARCHAR2)
AUTHID CURRENT_USER
IS
   l_thecursor     INTEGER           DEFAULT DBMS_SQL.open_cursor;
   l_columnvalue   VARCHAR2 (4000);
   l_query         VARCHAR2 (4000);
   l_status        INTEGER;
   l_desctbl       DBMS_SQL.desc_tab;
   l_colcnt        NUMBER;
BEGIN
   --EXECUTE IMMEDIATE 'alter session set  nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';

   l_query := 'select * from v$sql_shared_cursor where sql_id=' || CHR (39) || p_query || CHR (39);
   DBMS_SQL.parse (l_thecursor, l_query, DBMS_SQL.native);
   DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);

   FOR i IN 1 .. l_colcnt
   LOOP
      DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
   END LOOP;

   l_status := DBMS_SQL.EXECUTE (l_thecursor);
   DBMS_OUTPUT.put_line ('-------------------------------------------------------');

   WHILE (DBMS_SQL.fetch_rows (l_thecursor) > 0)
   LOOP
      FOR i IN 1 .. l_colcnt
      LOOP
         DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);

         IF (l_columnvalue 'N')
         THEN
            DBMS_OUTPUT.put_line (RPAD (l_desctbl (i).col_name, 30) || ': ' || l_columnvalue);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('-------------------------------------------------------');
   END LOOP;
--  execute immediate  'alter session set nls_date_format=''dd-MON-rr'' ';
EXCEPTION
   WHEN OTHERS
   THEN
      NULL;
--  execute immediate  'alter session set nls_date_format=''dd-MON-rr'' ';
      RAISE;
END;
/


实际上我还发现别人已经写了一个sql语句实现一样的操作，只不过他查询的是sql语句，很容易修改为查询sql_id的语句，我修改如下：

http://dioncho.wordpress.com/2009/03/05/vsql_shared_cursor/

$ cat shared_cursor.sql
SET  serveroutput on size 100000;

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_id 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;
/

SET serveroutput off;


做一个简单测试：

SQL> var a number;
SQL> exec :a := 10;

PL/SQL procedure successfully completed.

SQL> select * from dept where deptno=:a;

查询获得sql_id='6a2y9jbwu5fz9'.

SQL> alter session set optimizer_mode =first_rows;
SQL> select * from dept where deptno=:a;
SQL> set serveroutput on
SQL> exec print_shared_cursor('6a2y9jbwu5fz9');
-------------------------------------------------------
SQL_ID                        : 6a2y9jbwu5fz9
ADDRESS                       : 00000000BF56C328
CHILD_ADDRESS                 : 00000000BF56BFC8
CHILD_NUMBER                  : 0
-------------------------------------------------------
SQL_ID                        : 6a2y9jbwu5fz9
ADDRESS                       : 00000000BF56C328
CHILD_ADDRESS                 : 00000000B5ED9E80
CHILD_NUMBER                  : 1
OPTIMIZER_MODE_MISMATCH       : Y
-------------------------------------------------------

PL/SQL procedure successfully completed.

@shared_cursor.sql 6a2y9jbwu5fz9
old  15:           and q.sql_id like ''&1''',
new  15:           and q.sql_id like ''6a2y9jbwu5fz9''',
SQL_TEXT                       = select * from dept where deptno=:a
SQL_ID                         = 6a2y9jbwu5fz9
ADDRESS                        = 00000000BF56C328
CHILD_ADDRESS                  = 00000000BF56BFC8
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select * from dept where deptno=:a
SQL_ID                         = 6a2y9jbwu5fz9
ADDRESS                        = 00000000BF56C328
CHILD_ADDRESS                  = 00000000B5ED9E80
CHILD_NUMBER                   = 1
OPTIMIZER_MODE_MISMATCH        = Y
--------------------------------------------------

PL/SQL procedure successfully completed.