Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
При запуске SQL запроса через execute immediate не действует cursor_sharing=force т.е. константы не преобразуются в псевдо-бинд-переменные. Как заставить оракл все-таки подменить константы на бинды?

Oracle 10.2.0.4 64-бит
31 мар 09, 14:34    [6999345]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
Lecter
Member

Откуда: Киев
Сообщений: 2032
tim128,

Покажите как используете...
31 мар 09, 14:48    [6999465]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tru55
Member

Откуда: СПб
Сообщений: 19790
Application Developer's Guide - Fundamentals


Choosing Between Native Dynamic SQL and the DBMS_SQL Package


DBMS_SQL Lets You Reuse SQL Statements

.......
Native dynamic SQL prepares a SQL statement each time the statement is used, which
typically involves parsing, optimization, and plan generation. Although the extra
prepare operations incur a small performance penalty, the decrease in speed is
typically outweighed by the performance benefits of native dynamic SQL.
31 мар 09, 14:57    [6999569]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
К сожалению dbms_sql не помогает. Я вообще оказался не прав. Дело не в execute immediate. Cursor_sharing=force не влияет на pl/sql блоки :(

SQL> alter system flush shared_pool;
System altered.

SQL> select sql_text from v$sql where sql_text like '%/*TEST*/%';
no rows selected

SQL> begin /*TEST*/ dbms_output.put_line(1);end;
  2  /
PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where sql_text like '%/*TEST*/%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
begin /*TEST*/ dbms_output.put_line(1);end;                                     

SQL> declare
  2  i integer;
  3  sqltxt varchar2(2000);
  4  begin
  5  	 for i in 1..5 loop
  6  	     sqltxt:='begin /*TEST'||'*/ dbms_output.put_line('||i||');end;';
  7  	     execute immediate sqltxt;
  8  	 end loop;
  9  end;
 10  /
PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where sql_text like '%/*TEST*/%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
begin /*TEST*/ dbms_output.put_line(4);end;                                     
begin /*TEST*/ dbms_output.put_line(2);end;                                     
begin /*TEST*/ dbms_output.put_line(5);end;                                     
begin /*TEST*/ dbms_output.put_line(1);end;                                     
begin /*TEST*/ dbms_output.put_line(1);end;                                     
begin /*TEST*/ dbms_output.put_line(3);end;                                     
6 rows selected.

SQL> 
SQL> declare
  2  i integer;
  3  c integer;
  4  r integer;
  5  sqltxt varchar2(2000);
  6  begin
  7  	 for i in 1..10 loop
  8  	     sqltxt:='begin /*TEST'||'*/ dbms_output.put_line('||i||');end;';
  9  	     c:=dbms_sql.open_cursor;
 10  	     dbms_sql.parse(c,sqltxt,dbms_sql.native);
 11  	     r:=dbms_sql.execute(c);
 12  	     dbms_sql.close_cursor(c);
 13  	 end loop;
 14  end;
 15  /
PL/SQL procedure successfully completed.

SQL> select sql_text from v$sql where sql_text like '%/*TEST*/%';

SQL_TEXT                                                                        
--------------------------------------------------------------------------------
begin /*TEST*/ dbms_output.put_line(4);end;                                     
begin /*TEST*/ dbms_output.put_line(2);end;                                     
begin /*TEST*/ dbms_output.put_line(10);end;                                    
begin /*TEST*/ dbms_output.put_line(9);end;                                     
begin /*TEST*/ dbms_output.put_line(6);end;                                     
begin /*TEST*/ dbms_output.put_line(5);end;                                     
begin /*TEST*/ dbms_output.put_line(8);end;                                     
begin /*TEST*/ dbms_output.put_line(1);end;                                     
begin /*TEST*/ dbms_output.put_line(1);end;                                     
begin /*TEST*/ dbms_output.put_line(3);end;                                     
begin /*TEST*/ dbms_output.put_line(7);end;                                     
11 rows selected.
31 мар 09, 15:44    [7000022]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
dbms_dba
Member

Откуда: Минск
Сообщений: 66
tim128
К сожалению dbms_sql не помогает. Я вообще оказался не прав. Дело не в execute mmediate. Cursor_sharing=force не влияет на pl/sql блоки :(


Курсор определяет запросы, а не PL/SQL блоки
31 мар 09, 16:02    [7000204]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
dbms_dba,

Так что делать-то? Конечно разобрать pl/sql блок с целью заменить переменные на бинды как в sql задача мягко говоря сомнительная. Но сейчас тысячи таких одинаковых pl/sql засирают sql cache...
31 мар 09, 16:20    [7000372]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
dbms_dba
Member

Откуда: Минск
Сообщений: 66
tim128
засирают sql cache...


Чтобы развеять сомнения, рекомендую прочитать здесь

SELECT sum(PINHITS)/sum(PINS) FROM V$LIBRARYCACHE

Без пояснений - в большинстве случаев чем больше значение, тем лучше
31 мар 09, 16:59    [7000724]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
dbms_dba,

Это я читал и sum(PINHITS)/sum(PINS)=0,980094400887866

меня беспокоит что library cache hits<50%, parse to execute до 103% доходит, и v$sql весь забит этими одинаковыми pl/sql
31 мар 09, 17:15    [7000908]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
dbms_dba
Member

Откуда: Минск
Сообщений: 66
tim128,

FORCE should be used as a last resort
31 мар 09, 17:28    [7001071]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
dbms_dba
Member

Откуда: Минск
Сообщений: 66
tim128

library cache hits<50%, parse to execute до 103% доходит, и v$sql весь забит этими одинаковыми pl/sql


Этого и следует ожидать.
31 мар 09, 17:32    [7001114]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
dbms_dba
tim128

library cache hits<50%, parse to execute до 103% доходит, и v$sql весь забит этими одинаковыми pl/sql


Этого и следует ожидать.


Почему?

А могу я указать что данный plsql не надо кешировать, поскольку я точно знаю что он уже никогда не повторится?
31 мар 09, 17:34    [7001132]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
Так что, идей ни у кого нет?

Cursor sharing=force снимать нельзя, поскольку тогда пробоемы распространятся также и на обычные sql запросы, а это уже огромные накладные затраты на парс. Сейчас плодятся толко plsql а по ним парса нет да и количество их запусков небольшое. А половину объема кеша жрут

select count(*),sum(executions) from v$sql where sql_text like ('begin %');

  COUNT(*) SUM(EXECUTIONS)
---------- ---------------
      4022           10379
1 row selected.

select count(*),sum(executions) from v$sql where sql_text not like ('begin %');

  COUNT(*) SUM(EXECUTIONS)
---------- ---------------
      4836        30315897
1 row selected.
1 апр 09, 09:54    [7003409]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
1
Guest
declare
  c number;
  sqltxt varchar2(2048):='begin /*+ TEST */ dbms_output.put_line(:b1); end;';
begin
  for c in 1..100 loop
     execute immediate sqltxt using c;
  end loop;
end;
1 апр 09, 17:27    [7007583]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
1,

Да, видимо ничего не поделаешь, только так. Писать свой парсер, подменять параметры на bind и запускать через dbms_sql. execute immediate...using нельзя, потому что количество и типы параметров заранее неизвестны.
1 апр 09, 17:59    [7007830]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
tim128
При запуске SQL запроса через execute immediate не действует cursor_sharing=force т.е. константы не преобразуются в псевдо-бинд-переменные. Как заставить оракл все-таки подменить константы на бинды?

Oracle 10.2.0.4 64-бит

вот я только одного не понимаю - если у вас ..тысячи одинаковых запросов..
и ..огромные накладные затраты на парс.., то почему не написать немного статического pl/sql кода?
1 апр 09, 18:11    [7007907]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
orawish,

не понял что Вы имеете ввиду под статическим кодом. Если парсер, о котором я написал, то я уже решился попробовать. Почему не стал сразу это делать? Надеялся что найдется более изящное решение. Все-таки парсер это довольно громоздкий код, много возможностей для косяков...
1 апр 09, 19:32    [7008223]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
tim128,

я имел ввиду - попробовать отказаться, где только сможете (везде !) от execute immediate в пользу плэскуэльских if/case/.. конструкций ветвления с вариантами статического (компилёного вместе с плэскуэлем) эскуэля внутри
1 апр 09, 19:40    [7008249]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
orawish
tim128,

я имел ввиду - попробовать отказаться, где только сможете (везде !) от execute immediate в пользу плэскуэльских if/case/.. конструкций ветвления с вариантами статического (компилёного вместе с плэскуэлем) эскуэля внутри


Я заранее не знаю что в данном plsql блоке. Можно конечно частично распарсить его, определить какие процедуры вызываются, вычленить параметры и далее поступить по Вашему тексту. Но мне кажется что это сложнее чем заменить параметры в тексте блока на бинды и запустить через dbms_sql подставив вычлененные параметры, как предложил 1. Тем более количество различных вызываемых процедур постоянно растет - разработчики не спят, а дописывать каждый раз код не в кайф - лучше я один раз помучаюсь.
1 апр 09, 19:52    [7008314]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
tim128,

учтите, что кроме проблем производительности увлечение динамикэскуэлем
может обрушить надежность вашего приложения.
сами же говорите - пишут. такого понапишут..
1 апр 09, 19:56    [7008339]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
по прошествии почти четырех лет неожиданно выяснил:

call dbms_output.put_line(1);


при cursor_sharing=force преобразование в псевдо-бинд переменные производит )))
21 янв 13, 16:46    [13804462]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
Кристобаль Хозевич
Member

Откуда: тутошний
Сообщений: 250
tim128
по прошествии почти четырех лет неожиданно выяснил:
Неужели внутри pl/sql-ного блока?
21 янв 13, 17:15    [13804721]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
tim128
call 
call - это SQL
21 янв 13, 18:06    [13805107]     Ответить | Цитировать Сообщить модератору
 Re: execute immediate & cursor_sharing=force  [new]
tim128
Member

Откуда:
Сообщений: 145
Кристобаль Хозевич
tim128
по прошествии почти четырех лет неожиданно выяснил:
Неужели внутри pl/sql-ного блока?


да

xtender
tim128
call 
call - это SQL


видимо поэтому и получается
22 янв 13, 18:29    [13811545]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить