61 lines
2.1 KiB
SQL
61 lines
2.1 KiB
SQL
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
|
|
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
|
|
|
|
--------------------------------------------------------------------------------
|
|
--
|
|
-- File name: nonshared.sql
|
|
-- Purpose: Print reasons for non-shared child cursors from v$sql_shared_cursor
|
|
-- in a readable format
|
|
--
|
|
-- Author: Tanel Poder
|
|
-- Copyright: (c) http://www.tanelpoder.com
|
|
--
|
|
-- Usage: @nonshared <sql_id>
|
|
-- @nonshared 7gf6xg9xfv3vb
|
|
--
|
|
-- Other: Uses modified version of Tom Kyte's printtab code
|
|
-- ( http://asktom.oracle.com )
|
|
--
|
|
--------------------------------------------------------------------------------
|
|
|
|
set serverout on size 1000000
|
|
prompt Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...
|
|
prompt
|
|
|
|
def cmd="select * from v$sql_shared_cursor where sql_id = ''&1''"
|
|
|
|
|
|
declare
|
|
l_theCursor integer default dbms_sql.open_cursor;
|
|
l_columnValue varchar2(4000);
|
|
l_status integer;
|
|
l_descTbl dbms_sql.desc_tab;
|
|
l_colCnt number;
|
|
procedure execute_immediate( p_sql in varchar2 )
|
|
is
|
|
BEGIN
|
|
dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
|
|
l_status := dbms_sql.execute(l_theCursor);
|
|
END;
|
|
begin
|
|
dbms_sql.parse( l_theCursor, replace( '&cmd', '"', ''''), 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);
|
|
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;
|
|
end;
|
|
/
|
|
|
|
set serverout off
|
|
|