Oracle mechanics

17.08.2018

SQL Developer и связанные переменные вида :”1”

Filed under: Oracle,shared pool — Игорь Усольцев @ 00:20
Tags:

Поскольку некоторые инструменты разработки генерируют связанные переменные вида :"1", :"2",.., а некоторые классы запросов могут по факту вытолнятся только на Readonly Standby, не присутствуя на Primary ни в каком виде, для фиксации/оптимизации плана бывает необходимо каким-то образом поместить такой запрос (sql_id) в Primary Shared Pool, что можно сделать стандартным способом через execute immediate:

12.1.0.2.@ SQL> declare
  2  t date;
  3  begin
  4    execute immediate 'SELECT sysdate from dual where 1 = :"1"' into t using 1;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_id, sql_text from v$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------------------------------------------------------
2cg0gucz7scgq SELECT sysdate from dual where 1 = :"1"

, что для громоздких запросов с большим кол-вом bind-ов может потребовать определённых усилий

Прямое выполнение запроса в SQL*Plus для этой цели приспособить затруднительно:

SQL> SELECT sysdate from dual where 1 = :"1";
SP2-0552: Bind variable "1" not declared.
SQL> var "1" char(1)
SP2-0553: Illegal variable name ""1"".

, но даже если получится — инициализация переменных по-прежнему останется нудной задачей

При попытке выполнить запрос PL/SQL developer возвращает

ORA-01008: not all variables bound

, пытаясь распарсить запрос в неизменном виде через OCI:

=====================
PARSING IN CURSOR #140274682451296 len=41 dep=0 uid=0 oct=3 lid=0 tim=9063354981965 hv=1281056484 ad='1366e42c50' sqlid='1fajqcp65qrr4'
SELECT sysdate from dual where 1 = :"1"
END OF STMT

и не оставляя следов в Shared Pool / V$SQL:

SQL> select * from v$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%';

no rows selected

Oracle SQL developer выдаёт формально ту же ошибку:

ORA-01008: не все переменные привязаны
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:

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

SQL> select * from v$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%';
 
SQL_TEXT                                 SQL_ID        SHARABLE_MEM PERSISTENT_MEM EXECUTIONS LOADS FIRST_LOAD_TIME      INVALIDATIONS PARSE_CALLS OPTIMIZER_MODE OPTIMIZER_COST PARSING_SCHEMA_NAME KEPT_VERSIONS ADDRESS          PLAN_HASH_VALUE FULL_PLAN_HASH_VALUE CHILD_NUMBER MODULE          CPU_TIME ELAPSED_TIME CHILD_ADDRESS       SQLTYPE REMOTE OBJECT_STATUS
---------------------------------------- ------------- ------------ -------------- ---------- ----- -------------------- ------------- ----------- -------------- -------------- ------------------- ------------- ---------------- --------------- -------------------- ------------ ------------- ---------- ------------ ---------------- ---------- ------ -------------
SELECT sysdate from dual where 1 = :"1"  2cg0gucz7scgq         7240           1264          0     3 2018-07-31/16:55:31              2           1 ALL_ROWS                    0 SCOTT                           0 00000013EB7C1468               0                    0            0 SQL Developer        861          481 00000013EB32C220          6 N      VALID

с использованием JDBC / PL/SQL API:

=====================
PARSING IN CURSOR #139821740071872 len=782 dep=0 uid=33 oct=47 lid=0 tim=5278989030441 hv=2895811911 ad='268bc53d10' sqlid='98n7q1kq9p5a7'
declare
  l_theCursor     integer default dbms_sql.open_cursor;
  l_status        integer default -1 ;
  insqlsix        VARCHAR2(6);
begin
   insqlsix:=upper(substr(ltrim(:1 ),1,6));
   -- Check to see if the statement is a DML statement. Ignore DDL
   -- (DBMS_SQL.PARSE will execute DDL on the parse)
   if ((insqlsix  in ( 'INSERT', 'UPDATE', 'DELETE', 'SELECT') )
     OR(substr(insqlsix,1,5)='MERGE')
     OR(substr(insqlsix,1,4)='WITH') OR (substr(insqlsix,1,1)='(')) then
     begin
                l_status := -2;
        dbms_sql.parse(  l_theCursor, :2 , dbms_sql.native );
     exception
          when others then
                                l_status := dbms_sql.last_error_position;
          end;
          dbms_sql.close_cursor( l_theCursor );
     end if;
     :3  := l_status;
end;

END OF STMT
PARSE #139821740071872:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=5278989030440
BINDS #139821740071872:
 Bind#0
...
  value="SELECT sysdate from dual where 1 = :"1""
 Bind#1
...
  value="SELECT sysdate from dual where 1 = :"1""
...

Тот же API из SQL*Plus без всякой подготовки / обработки связанных переменных:

SQL> > declare
    2    l_theCursor integer default dbms_sql.open_cursor;
    3    l_status        integer default -1 ;
    4  begin
    5       begin
    6         l_status := -2;
    7         dbms_sql.parse(l_theCursor, 'SELECT sysdate from dual where 1 = :"1"', dbms_sql.native);
    8       exception
    9            when others
   10              then l_status := dbms_sql.last_error_position;
   11            end;
   12       dbms_sql.close_cursor( l_theCursor );
   13  end;
   14  /
 
PL/SQL procedure successfully completed
 
SQL> > select CHILD_ADDRESS, sql_id, PLAN_HASH_VALUE, LOADS, INVALIDATIONS, PARSE_CALLS, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, FETCHES EXECUTIONS, sql_text from gv$sql where sql_text like 'SELECT sysdate from dual where 1 = :"1"%';
 
CHILD_ADDRESS    SQL_ID        PLAN_HASH_VALUE      LOADS INVALIDATIONS PARSE_CALLS LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING EXECUTIONS SQL_TEXT
---------------- ------------- --------------- ---------- ------------- ----------- --------------- ------------- ------------- ---------- ------------------------------------------------------------
00000013147272C8 2cg0gucz7scgq               0          1             0           1               0             0             0          0 SELECT sysdate from dual where 1 = :"1"
 
SQL> select * from gv$sql_shared_cursor where sql_id = '2cg0gucz7scgq';

no rows selected

также даёт загруженный в Shared Pool (LOADS=1) синтаксически разобранный (PARSE_CALLS=1) запрос

Ввиду PLAN_HASH_VALUE=0 и отсутствия в GV$SQL_SHARED_CURSOR — курсор / план выполнения запроса не был скомпилирован / сформирован по причине отсутствия значений связанных переменных, влияющих на выбор плана / PLAN_HASH_VALUE, но место и название уже занимает:

SQL> select kglnaobj, kglhdnsd, kglobtyd, kglobt03 from x$kglob where kglhdadr = '00000013147272C8';
 
KGLNAOBJ                                 KGLHDNSD  KGLOBTYD  KGLOBT03
---------------------------------------- --------- --------- -------------
SELECT sysdate from dual where 1 = :"1"  SQL AREA  CURSOR    2cg0gucz7scgq

Тем не менее для решения задач управления планами важно, что даже такой недокомпилированный курсор уже позволяет прикрепить к запросу (sql_id) SQL Patch:

SQL> @sqlpatch+
&SQL_ID: 2cg0gucz7scgq
&SQL_PATCH_TEXT: OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')
&SQL_PATCH_NAME: OFE_SQL_Patch
 
PL/SQL procedure successfully completed
 
SQL_ID        NAME           CATEGORY  CREATED               STATUS   FMATCH
------------- -------------- --------- --------------------- -------- ------
2cg0gucz7scgq OFE_SQL_Patch  DEFAULT   02.08.2018 00:41:31   ENABLED  NO
 
SQL_PATCH_HINTS
----------------------------------------------------------------------------
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SQL> @spm_check4sql_id 2cg0gucz7scgq
 
SPM_TYPE   SQL_HANDLE            PATCH_NAME     ORIGIN         VERSION    CREATED             LAST_MODIFIED       LAST_EXECUTED ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE SPM_SIGNATURE         SQL_EXACT_SIGNATURE   SQL_FORCE_SIGNATURE
---------- --------------------- -------------- -------------- ---------- ------------------- ------------------- ------------- ------- -------- ----- ---------- --------- --------------------- --------------------- ---------------------
SQL Patch  SQL_16fbca0a2fe4fb18  OFE_SQL_Patch  MANUAL-SQLTUNE 12.1.0.2.0 02.08.2018 00:41:31 02.08.2018 00:41:31               YES     NO       NO    YES        NO          1656139433090808600   1656139433090808600   1656139433090808600

или план другого запроса через создание SQL Profile (скрипт sql_profile_from_sql2.sql), которые через redo transport будут переданы и определят планы выполнения запроса на Read Only Standby

Вариант быстрый, возможно, не лучший — предложения приветствуются

Реклама

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: