檢視v$sql_shared_cursor檢視獲取sql語句為什麼不能共享?
[20111213]檢視v$sql_shared_cursor檢視獲取sql語句為什麼不能共享?
查詢v$sql_shared_cursor檢視獲取sql語句為什麼不能共享,在GUI介面下,由於不能共享的原因很多,檢視欄位N多,大部分都是'N'的數值,檢查這個是一個很費時間的操作。
想到了TOM大師的print_table過程,應該修改一下很容易實現這個功能。
修改一下,很容易實現這個功能:
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.
查詢v$sql_shared_cursor檢視獲取sql語句為什麼不能共享,在GUI介面下,由於不能共享的原因很多,檢視欄位N多,大部分都是'N'的數值,檢查這個是一個很費時間的操作。
想到了TOM大師的print_table過程,應該修改一下很容易實現這個功能。
修改一下,很容易實現這個功能:
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.
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-713219/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- V$SQL_SHARED_CURSOR檢視硬解析的原因SQL
- 檢視低效的SQL語句SQL
- [ZT]v$sqlarea,v$sql,v$sqltext這三個檢視提供的sql語句有什麼區別SQL
- v$sql_shared_cursor檢視記錄多版本的資訊SQL
- v$sql檢視和v$sqlarea檢視的構建SQL
- 檢視mysql正在執行的SQL語句MySql
- SQL語句大全—檢視錶空間(二)SQL
- SQL語句大全—檢視錶空間(一)SQL
- 檢視包正在被哪個程式使用,檢視包含SQL語句的PACKAGESQLPackage
- SQL語句為什麼不會共享(上)SQL
- SQL語句為什麼不會共享(中)SQL
- SQL語句為什麼不會共享(下)SQL
- Sql Server 獲取指定表、檢視結構SQLServer
- Oracle 檢視佔用undo大的sql語句OracleSQL
- sqlserver 檢視和sql語句的效率對比SQLServer
- 檢視造成等待事件的具體SQL語句事件SQL
- sql 語句網路除錯和 sql 語句低層傳輸檢視SQL除錯
- MySQL檢視建表語句MySql
- oracle 檢視死鎖語句Oracle
- 使用V$SQL_PLAN檢視SQL
- V$SQLAREA 檢視TOP_SQLSQL
- v$sql_plan 檢視解析SQL
- MySQL_通過binlog檢視原始SQL語句MySql
- Oracle日常效能檢視常用語句Oracle
- GDB為什麼檢視不了原始碼?原始碼
- 用LinqPad檢視Nhibernate生成的sql語句SQL
- sql server中如何檢視執行效率不高的語句SQLServer
- 檢視mysql連線數 sql語句執行時間MySql
- 檢視當前oracle中正在執行的sql語句OracleSQL
- SQL檢視SQL
- 【實驗】【檢視】使用v$bgprocess檢視獲得所有後臺程式資訊
- MySql 什麼是檢視MySql
- v$session 檢視Session
- 檢視 Laravel 查詢資料語句Laravel
- oracle檢視建立物件的DDL語句Oracle物件
- 檢視語句執行的時間
- 通過日誌檢視mysql正在執行的SQL語句MySql