cursor_sharing=force не работает

Фролов

Дата: 28.02.2007 18:52:03

Видел у Кайта в процедуре такое:

execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';

У меня это не дает никакого эффекта, потому что в sqlarea я вижу запрос с литеральной константой.

PS: Сервер 10g.

Фролов

Дата: 28.02.2007 19:57:54

Я несу чушь или этого никто не знает?
Может мне кто-нибудь кинуть вывод, что у него байнд в sqlarea таблице?

RA\/EN

Дата: 28.02.2007 20:29:18

Фролов

Я несу чушь или этого никто не знает?
Может мне кто-нибудь кинуть вывод, что у него байнд в sqlarea таблице?

А новый-то курсор, с байндом, появляется?
Не вижу повода выкидывать из SQLAREA старый курсор...

Andrew Max

Дата: 28.02.2007 20:49:32

Фролов

Видел у Кайта в процедуре такое:

execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';

У меня это не дает никакого эффекта, потому что в sqlarea я вижу запрос с литеральной константой.

PS: Сервер 10g.

Если после установки CURSOR_SHARING Вы пытаетесь выполнять запрос с теми же значениями bind-переменных, с которыми он уже выполнялся ранее, не стоит ожидать появления новой записи в V$SQLAREA. Oracle найдет уже разобранный запрос в shared pool-e и просто выполнит его повторно.

Однако как только Вы измените хотя бы один литерал в запросе - Oracle будет строить новый план, заменив литералы на bind-переменные:

SQL> select sql_text from v$sqlarea where sql_text like 'select /* tag */%';

строки не выбраны

SQL> select /* tag */ 'X' A from dual;

A
-
X

SQL> select sql_text from v$sqlarea where sql_text like 'select /* tag */%';

SQL_TEXT
-----------------------------------------------------------------------------

select /* tag */ 'X' A from dual

SQL> alter session set CURSOR_SHARING = force;

Сеанс изменен.

SQL> select /* tag */ 'X' A from dual;

A
-
X

SQL> select sql_text from v$sqlarea where sql_text like 'select /* tag */%';

SQL_TEXT
-----------------------------------------------------------------------------

select /* tag */ 'X' A from dual

SQL> select /* tag */ 'Y' A from dual;

A
--------------------------------
Y

SQL> select sql_text from v$sqlarea where sql_text like 'select /* tag */%';

SQL_TEXT
-----------------------------------------------------------------------------

select /* tag */ 'X' A from dual
select /* tag */ :"SYS_B_0" A from dual

SQL>

Вы это хотели спросить?

Andrew Max

Дата: 28.02.2007 20:54:39

Фролов

...
У меня это не дает никакого эффекта, потому что в sqlarea я вижу запрос с литеральной константой.
...

Еще посмотрите документацию по хинту CURSOR_SHARING_EXACT, который может принудительно включить режим EXACT:

SQL> alter session set CURSOR_SHARING = force;

Сеанс изменен.

SQL> select /*+ CURSOR_SHARING_EXACT */ 'Some literal' A from dual;

A
------------
Some literal

SQL> select sql_text from v$sqlarea where sql_text like 'select /*+ CURSOR_SHARING_EXACT */ ''Some%';

SQL_TEXT
------------------------------------------------------------------------------------------------------

select /*+ CURSOR_SHARING_EXACT */ 'Some literal' A from dual

Фролов

Дата: 01.03.2007 12:45:51

Andrew Max

Спасибо, но у меня задача выполнить с открытием курсора, а не просто запрос. Вто что у меня

select * from v$sqlarea where sql_text like 'select id from ort.wip%';

no rows selected

declare
 c sys_refcursor;
begin
 execute immediate 'alter session set cursor_sharing=force';
 open c for 'select id from ort.wip where name=''name''';
end; 
/

PL/SQL procedure successfully completed.

select * from v$sqlarea where sql_text like 'select id from ort.wip%';

SQL_TEXT
----------------------------------------
select id from ort.wip where name='name'


declare
 c sys_refcursor;
begin
 execute immediate 'alter session set cursor_sharing=force';
 open c for 'select id from ort.wip where name=''new''';
end; 
/

PL/SQL procedure successfully completed.

select * from v$sqlarea where sql_text like 'select id from ort.wip%';

SQL_TEXT
----------------------------------------
select id from ort.wip where name='new'

Фролов

Дата: 01.03.2007 12:50:54

Что интересно, выставив хинт /*+ CURSOR_SHARING_EXACT */, изменений после открытия курсора в sqlarea не нахожу

Фролов

Дата: 01.03.2007 13:10:45

Фролов

Что интересно, выставив хинт /*+ CURSOR_SHARING_EXACT */, изменений после открытия курсора в sqlarea не нахожу

Здесь я протупил, условия для like поменялись :)

Andrew Max

Дата: 01.03.2007 13:43:09

Фролов

Вот что у меня

select * from v$sqlarea where sql_text like 'select id from ort.wip%';

no rows selected

declare
 c sys_refcursor;
begin
 execute immediate 'alter session set cursor_sharing=force';
 open c for 'select id from ort.wip where name=''name''';
end; 
/

PL/SQL procedure successfully completed.

select * from v$sqlarea where sql_text like 'select id from ort.wip%';

SQL_TEXT
----------------------------------------
select id from ort.wip where name='name'
...
...

Хм...
Версию сервера точнее назовите, пожалуйста.

У меня в 9.2.0.6.0 все как и должно быть:

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production

SQL> select sql_text from v$sqlarea
  2   where sql_text like 'select dummy from sys.dual%';

строки не выбраны

SQL> declare
  2   c sys_refcursor;
  3  begin
  4   execute immediate 'alter session set cursor_sharing=force';
  5   open c for 'select dummy from sys.dual where dummy=''X''';
  6  end;
  7  /

Процедура PL/SQL успешно завершена.

SQL> select sql_text from v$sqlarea
  2   where sql_text like 'select dummy from sys.dual%';

SQL_TEXT
-----------------------------------------------------------------------

select dummy from sys.dual where dummy=:"SYS_B_0"

И кстати: ORT.WIP в Вашем запросе - это, случайно, не view?
Если да - думаю, стоит посмотреть на его текст. Например, может оказаться, что:

SQL> create or replace view mydual as
  2  select /*+ CURSOR_SHARING_EXACT */ * from sys.dual;

Представление создано.

SQL> select sql_text from v$sqlarea
  2   where sql_text like 'select dummy from mydual%';

строки не выбраны

SQL> declare
  2   c sys_refcursor;
  3  begin
  4   execute immediate 'alter session set cursor_sharing=force';
  5   open c for 'select dummy from mydual where dummy=''X''';
  6  end;
  7  /

Процедура PL/SQL успешно завершена.

SQL> select sql_text from v$sqlarea
  2   where sql_text like 'select dummy from mydual%';

SQL_TEXT
-----------------------------------------------------------------------

select dummy from mydual where dummy='X'

С другой стороны, в десятке все намного занятнее:

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod

SQL> create or replace view mydual as
  2  select /*+ CURSOR_SHARING_EXACT */ * from sys.dual;

Представление создано.

SQL> select sql_text from v$sqlarea
  2   where sql_text like 'select dummy from mydual%';

строки не выбраны

SQL> declare
  2   c sys_refcursor;
  3  begin
  4   execute immediate 'alter session set cursor_sharing=force';
  5   open c for 'select dummy from mydual where dummy=''X''';
  6  end;
  7  /
declare
*
ошибка в строке 1:
ORA-32550: Замена выполнена несмотря на подсказку об обратном
ORA-06512: на  line 5


SQL>

В документации по поводу ORA-32550 говорится:

Oracle® Database Error Messages 10g Release 1

ORA-32550: Replacement occured despite hint to the contrary
Cause: This should never be signalled; it's internal.
Action: Report to Oracle support.

Странно все это...
Может, гуру чего скажут?

Фролов

Дата: 01.03.2007 15:41:43

10G 10.1.0.4.0, ORT.WIP - синоним для таблицы из другой схемы, проверка осуществляется из под sys.
Так у вас заменяет литерал в 10-ке?