Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 разные планы выполнения при вызове из пакета и в sql*plus  [new]
-error
Member

Откуда: moscow
Сообщений: 162
пытаюсь разобраться с одним "долгоиграющим" процессом. включил его трассировку.
после обработки файла трассировки с сортировкой по fchela,exeela,prsela самый первый оператор:
********************************************************************************

SELECT B.*
FROM
 MC_BIN_TAB B WHERE :B1 BETWEEN B.PAN_LOW AND B.PAN_HIGH ORDER BY B.PRIORITY
^@
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    56564      2.01       2.03          0          0          0           0
Execute  56564      5.96       5.72          0          0          0           0
Fetch    77386   3206.22    3128.00          0   19118632          0       26630
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   190514   3214.19    3135.76          0   19118632          0       26630

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 21     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY
      0   TABLE ACCESS FULL MC_BIN_TAB

********************************************************************************
а вот, что я вижу в sql*plus:
SQL> truncate table plan_table;

Table truncated.

SQL> explain plan for
  2  SELECT B.* FROM MC_BIN_TAB B WHERE :B1 BETWEEN B.PAN_LOW AND B.PAN_HIGH ORDER BY B.PRIORITY
  3  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------
| Id  | Operation                    |  Name                 | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |   205 | 20705 |     7  (29)|
|   1 |  SORT ORDER BY               |                       |   205 | 20705 |     7  (29)|
|   2 |   TABLE ACCESS BY INDEX ROWID| MC_BIN_TAB            |   205 | 20705 |     6  (17)|
|*  3 |    INDEX RANGE SCAN          | MC_BIN_PAN_RANGE_IDX  |    37 |       |     4  (25)|
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   3 - access("B"."PAN_HIGH">=:Z AND "B"."PAN_LOW"<=:Z)
       filter("B"."PAN_LOW"<=:Z AND "B"."PAN_HIGH">=:Z)

15 rows selected.
переменная :B1 и поля PAN_HIGH и PAN_LOW - varchar2(19)
если вместо :B1 вписать значение переменной, выдернутое из трейса, то оптимизатор выбирает full scan и выполняет 338 логических чтений.
если в этот же оператор хинтом прописать индекс, то получается ~143 логических чтения.
статистика по таблице и её индексам собрана и пересобрана.
получается, оптимизатор ошибается?
13 мар 07, 14:09    [3892181]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
-error
переменная :B1 и поля PAN_HIGH и PAN_LOW - varchar2(19)
если вместо :B1 вписать значение переменной, выдернутое из трейса, то оптимизатор выбирает full scan и выполняет 338 логических чтений.
если в этот же оператор хинтом прописать индекс, то получается ~143 логических чтения.
статистика по таблице и её индексам собрана и пересобрана.
получается, оптимизатор ошибается?

Версия оракла?
Какой процент записей в результате выбирается запросом?
Статистику как собирали?
Системную статистику собирали?
optimizer_index_caching, optimizer_index_cost_adj, db_file_multiblock_read_count чему равны?
13 мар 07, 14:24    [3892277]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
Andrew Max
Member

Откуда:
Сообщений: 1045
Вполне возможно, план отличается из-за bind variable peeking.
В Вашем втором примере (SQL*Plus) запрос на самом деле не выполняется: Вы видите всего лишь результат EXPLAIN PLAN, которому, разумеется, ничего не известно о реальных значениях bind-переменных.

-error
... если вместо :B1 вписать значение переменной, выдернутое из трейса, то оптимизатор выбирает full scan и выполняет 338 логических чтений.
если в этот же оператор хинтом прописать индекс, то получается ~143 логических чтения.
статистика по таблице и её индексам собрана и пересобрана.
получается, оптимизатор ошибается?

Возможно, статистика собрана неудачно. Возможно, и CBO себя нехорошо повел. По информации в Вашем сообщении точный вывод сделать трудно.

Кроме сказанного Вами, настораживают высокие значения EXECUTE и, особенно, PARSE:
-error
SELECT B.*
FROM
 MC_BIN_TAB B WHERE :B1 BETWEEN B.PAN_LOW AND B.PAN_HIGH ORDER BY B.PRIORITY
^@
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    56564      2.01       2.03          0          0          0           0
Execute  56564      5.96       5.72          0          0          0           0
Fetch    77386   3206.22    3128.00          0   19118632          0       26630
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   190514   3214.19    3135.76          0   19118632          0       26630

Как вы используете запрос в своем пакете?
13 мар 07, 14:35    [3892333]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
-error
Member

Откуда: moscow
Сообщений: 162
oracle 9.2.0.6
строк запросом выбирается очень мало. больше одной в трейсе не видел (хотя он большой ~3GB. может где-то есть и больше). всего в таблице ~80K строк.
статистику собирал примерно так:
exec dbms_stats.gather_table_stats(<USER>,'MC_BIN_TAB',cascade => true, method_opt => 'FOR ALL INDEXED COLUMNS')
далее
SQL> show parameter optimizer_index

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
SQL> show parameter multiblock

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
системная статистика вроде бы есть, но очень старая. датируется ~04-08-2006.

этот курсор используется разработчиками где-то глубоко в коде и судя по значениям parse постоянно открывается/закрывается. кроме этого, временные затраты на это невелики и сессия в этот момент использует сервер практически монопольно. так что на эти мягкие разборы, в общем-то, пока можно закрыть глаза.
13 мар 07, 15:17    [3892643]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
evostr
Member

Откуда: Екатеринбург
Сообщений: 1278
-error
строк запросом выбирается очень мало. больше одной в трейсе не видел

Не понял тогда, почему вы говорите про 143 логических чтения?
-error
статистику собирал примерно так:
exec dbms_stats.gather_table_stats(<USER>,'MC_BIN_TAB',cascade => true, method_opt => 'FOR ALL INDEXED COLUMNS')

Попробуйте собрать без гистограмм, посмотрите, что с планом будет
13 мар 07, 15:33    [3892773]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
_AndreyP
Member

Откуда: Krsk-->SPb
Сообщений: 429
покажите значения переменных из таблицы, и из статистики:
:B1
PAN_HIGH
PAN_LOW
13 мар 07, 16:21    [3893134]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
Andrew Max
Member

Откуда:
Сообщений: 1045
-error
... этот курсор используется разработчиками где-то глубоко в коде ...

Иными словами, исходный код пакета Вам недоступен и хинт INDEX добавить не получится? Или Вы хотите докопаться до причин FULL SCAN-а?

Если желательно "докопаться" - то, думаю, правильнее всего получить трейс с установленным events 10053 и в трассируемом сеансе выполнить два EXPLAIN PLAN-а: один - для запроса, в котором значение bind-переменной заменено на "ту самую константу", а второй - для точно такого же запроса, но с хинтом INDEX. Если возникнут затруднения в интерпретации результата - его можно запостить сюда (лучше вложением), попробуем разобраться вместе.

Кроме того, хотелось бы взглянуть на:

select i.blevel, i.leaf_blocks, i.clustering_factor 
  from user_indexes i 
 where i.index_name = 'MC_BIN_PAN_RANGE_IDX';
 
select t.blocks 
  from user_tables t 
 where t.table_name  = 'MC_BIN_TAB';

В принципе, может помочь предложение evostr... а может и не помочь - тут дело случая. :)
13 мар 07, 16:57    [3893411]     Ответить | Цитировать Сообщить модератору
 Re: разные планы выполнения при вызове из пакета и в sql*plus  [new]
-error
Member

Откуда: moscow
Сообщений: 162
логические чтения при добавлении хинта, например, здесь:
=====================
PARSING IN CURSOR #34 len=127 dep=1 uid=21 oct=3 lid=21 tim=630696966374 hv=480400056 ad='a0fa7d40'
SELECT /*+ index(b, MC_PAN_LOW_HIGH_NDX)*/ B.* FROM MC_BIN_TAB B WHERE :B1 BETWEEN B.PAN_LOW AND B.P
AN_HIGH ORDER BY B.PRIORITY
END OF STMT
PARSE #34:c=0,e=408,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=630696966366
BINDS #34:
 bind 0: dty=1 mxl=32(16) mal=00 scl=00 pre=00 oacflg=13 oacfl2=8000000100000001 size=32 offset=0
   bfp=800003f9401e1c58 bln=32 avl=16 flg=05
   value="xxxxxxxxxxxxxxxx"
EXEC #34:c=0,e=2186,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=1,tim=630696968636
WAIT #34: nam='db file sequential read' ela= 12374 p1=89 p2=5758 p3=1
WAIT #34: nam='db file sequential read' ela= 444 p1=89 p2=5759 p3=1
WAIT #34: nam='db file sequential read' ela= 6510 p1=89 p2=5760 p3=1
[... еще куча ожиданий ...]
WAIT #34: nam='db file sequential read' ela= 484 p1=89 p2=6419 p3=1
WAIT #34: nam='db file sequential read' ela= 477 p1=89 p2=6420 p3=1
FETCH #34:c=30000,e=162292,p=141,cr=142,cu=0,mis=0,r=1,dep=1,og=1,tim=630697131020
FETCH #34:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,tim=630697131146
FETCH #34:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=630697131224
STAT #34 id=1 cnt=2 pid=0 pos=1 obj=0 op='SORT ORDER BY '
STAT #34 id=2 cnt=2 pid=1 pos=1 obj=6545 op='TABLE ACCESS BY INDEX ROWID MC_BIN_TAB '
STAT #34 id=3 cnt=2 pid=2 pos=1 obj=272602 op='INDEX RANGE SCAN MC_PAN_LOW_HIGH_NDX '
выбрано две строки, а прочитано 142 блока.

данные из таблицы и значения переменных, к сожалению, показать не могу. их характер - строки из цифр.

хинт удалось все-таки добавить, в результате чего время выполнения сократилось примерно на 50 минут. Но хотелось бы выяснить, почему такое происходит, т.к. в отчете tkprof'a видно еще несколько длительных операторов, которые не используют индексы.

трассировку hard parse'а постараюсь сделать.

  1  select i.blevel, i.leaf_blocks, i.clustering_factor
  2    from dba_indexes i
  3*  where i.index_name = 'MC_PAN_LOW_HIGH_NDX'
SQL> /

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1         142              3054

  1  select t.blocks
  2    from dba_tables t
  3*  where t.table_name  = 'MC_BIN_TAB'
SQL> /

    BLOCKS
----------
       334

по таблице зачем-то сделано несколько индексов, в которых участвуют поля PAN_LOW и PAN_HIGH.
для хинта выбрал меньший по количеству блоков.
14 мар 07, 16:38    [3898487]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить