Индекс работает в SQL, но не работает в PL/SQL / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Индекс работает в SQL, но не работает в PL/SQL

24 сообщений из 24, страница 1 из 1

Индекс работает в SQL, но не работает в PL/SQL

#40079249

A_-_ND

Гость

Здравствуйте!

Помогите, пожалуйста, разобраться в ситуации.

Код: plaintext

1.
2.
3.
4.
5.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE	11.2.0.4.0	Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Есть самый простой запрос:

Код: plsql

select max(DT) from T_LOG where CODE = :p_code;

У таблицы есть индекс

Код: plsql

index T_LOG_I1 on T_LOG (CODE)

Таблица T_LOG - таблица логирования действий пользователей. Т.е. днём данные в ней интенсивно накапливаются, а ночью старые (больше месяца) данные удаляются.

Ситуация.
Данный запрос прекрасно работает в SQL - оптимизатор индекс автоматом определяет.
А вот в PL/SQL - TABLE ACCESS FULL. Явно хинтом указал индекс - а оптимизатору по барабану.

Пересобрал статистику, пересоздал индексы - ситуация та же.
И только после пересоздания самой таблицы T_LOG - всё заработало.

Вопрос: какого фига? Что нужно делать, чтобы избежать такой ситуации в будущем?

...

Рейтинг:

0 / 0

21.06.2021, 23:50

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079251

Правильный Вася

Участник

Сообщения: 475

Рейтинг: 0 / 0

Думаю, эффективней было бы сделать индекс по (code,dt)

...

Рейтинг:

0 / 0

22.06.2021, 01:51

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079268

PuM256

Гость

A_-_ND

Ситуация.
Данный запрос прекрасно работает в SQL - оптимизатор индекс автоматом определяет.
А вот в PL/SQL - TABLE ACCESS FULL. Явно хинтом указал индекс - а оптимизатору по барабану.

Это вы как определили? В любом случае, планы в студию. И код на SQL и PL/SQL.

...

Рейтинг:

0 / 0

22.06.2021, 09:10

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079283

A_-_ND

Гость

PuM256,

С трассировкой и с запросами всё нормально, всё перепроверял несколько раз - проблема не в этом. Запрос самый простой(при исследовании довёл до указанного выше запроса) и планы соответственно, тоже.

Меня пугает, что после пересоздания таблицы(сбор статистики, пересоздание индексов не помогало), без изменения кода, всё заработало корректно. План в PL/SQL стал с индексом ( как и в SQL)

Т.е. с таблицей что-то случилось. Но что? (Коль в SQL всё нормально работало)

Как перестраховаться чтобы в дальнейшем не попасть на эту ситуацию?

...

Рейтинг:

0 / 0

22.06.2021, 10:39

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079303

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

A_-_ND

Как перестраховаться чтобы в дальнейшем не попасть на эту ситуацию?

Пересоздавать таблицу перед каждым запросом.

...

Рейтинг:

0 / 0

22.06.2021, 12:07

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079334

PuM256

Гость

A_-_ND,

Повторю вопрос - каким способом вы смотрите план в SQL и в PL/SQL?

...

Рейтинг:

0 / 0

22.06.2021, 13:28

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079341

A_-_ND

Гость

PuM256,

PL/SQL снимал трассировку
SQL строил план в девелопере.

Да и без трассировки в девелопере явно ощущалось.

SQL отрабатывает моментально.
Тот же запрос, вызываемый в скрипте

Код: plsql

1.
2.
3.

begin 
select max(DT) into :result from T_LOG where CODE = :p_code; 
end;

- зависал на минуты

...

Рейтинг:

0 / 0

22.06.2021, 13:58

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079342

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

A_-_ND

Тот же запрос, вызываемый в скрипте

Код: plsql

where CODE = :p_code;

Конвертации типов нет?

...

Рейтинг:

0 / 0

22.06.2021, 14:09

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079343

A_-_ND

Гость

AmKad,

нет.
Подставлял явно значение - не помогало.

И опять-таки, после пересоздания таблицы всё заработало правильно.

...

Рейтинг:

0 / 0

22.06.2021, 14:15

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079345

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

A_-_ND

И опять-таки, после пересоздания таблицы всё заработало правильно.

Тест с execution plan в студию.

SY.

...

Рейтинг:

0 / 0

22.06.2021, 14:26

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079349

A_-_ND

Гость

Текущий план скрипта PL/SQL

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.

SELECT MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N) 
FROM
 T_LOG B WHERE B.CODE = PCODE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     11      0.00       0.00          0          0          0           0
Fetch       11      0.06       0.07          0      10032          0          11
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23      0.07       0.08          0      10032          0          11

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 87     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=10032 pr=0 pw=0 time=77847 us)
  24448   TABLE ACCESS BY INDEX ROWID T_LOG (cr=10032 pr=0 pw=0 time=79138 us cost=0 size=39 card=1)
  24448    INDEX FULL SCAN T_LOG_I1 (cr=127 pr=0 pw=0 time=7837 us cost=0 size=0 card=1)(object id 105033)

...

Рейтинг:

0 / 0

22.06.2021, 14:34

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079356

A_-_ND

Гость

План до пересоздания таблицы

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

SELECT MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N) 
FROM
 T_LOG B WHERE B.CODE = PCODE


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      3.14      24.31     370433     370449          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      3.14      24.31     370433     370449          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87     (recursive depth: 4)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=370449 pr=370433 pw=0 time=24314912 us)
1173705   TABLE ACCESS FULL T_LOG (cr=370449 pr=370433 pw=0 time=19292802 us cost=100524 size=18589776 card=1161861)

Ещё раз обращу внимание что явно хинтом подсовывал индекс

Код: plsql

--+ index(b T_LOG_I1)

- оптимизатор его не брал

...

Рейтинг:

0 / 0

22.06.2021, 14:42

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079357

AmKad

Участник

Сообщения: 5 151

Рейтинг: 0 / 0

А access/filter predicates?

...

Рейтинг:

0 / 0

22.06.2021, 14:44

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079358

PuM256

Гость

A_-_ND,

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

...

Рейтинг:

0 / 0

22.06.2021, 14:49

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079368

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

A_-_ND

План до пересоздания таблицы

Код: plsql

total        3      3.14      24.31     370433     370449          0           1

Текущий план скрипта PL/SQL

Код: plsql

total       23      0.07       0.08          0      10032          0          11

1 строка - чтение индекса + 1 чтение 1 блока таблицы.
11 строк - чтение индекса + до 11 чтений по 1 блоку таблицы. Оптимизатор решил full scan который читает не по 1 блоку таблицы а по multiblock_read_count блоков дешевле. Правильно решил или нет - смотреть трейс + статистика.

SY.

...

Рейтинг:

0 / 0

22.06.2021, 15:17

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079377

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

A_-_ND,

Не сходится:
A_-_NDУ таблицы есть индекс
index T_LOG_I1 on T_LOG (CODE)

И:
A_-_NDINDEX FULL SCAN T_LOG_I1

С чего-бы оптимизатор читал весь индекс если имеем WHERE B.CODE = PCODE?

Ну и:
A_-_NDЕсть самый простой запрос:
select max(DT) from T_LOG where CODE = :p_code;

A_-_NDТекущий план скрипта PL/SQL

Код: plsql

1.
2.
3.

SELECT MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N)
FROM
 T_LOG B WHERE B.CODE = PCODE

SY.

...

Рейтинг:

0 / 0

22.06.2021, 15:42

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079422

A_-_ND

Гость

Не сходится:

Да, есть такое.
В конце привёл планы боевых запросов.
А в начале привёл упрощённые запросы к таблице, с которыми работал и пытался разобраться откуда тормоза идут.
Почему запрос из PL/SQL тормозит, а из SQL работает прекрасно?

...

Рейтинг:

0 / 0

22.06.2021, 18:55

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079423

A_-_ND

Гость

PuM256

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

Увы, этих планов нет

...

Рейтинг:

0 / 0

22.06.2021, 18:56

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079466

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

A_-_ND

Почему запрос из PL/SQL тормозит, а из SQL работает прекрасно?

Потому что пока ты преуспел в невольном сокрытии/подтасовке. Для начала приведи DDL создания таблицы и ее всех индексов, констрейнтов, триггеров итд. Приведи точный SQL и тип данных PCODE ибо это первый подозреваемый:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

DROP TABLE T_LOG PURGE
/
CREATE TABLE T_LOG(
                   CODE VARCHAR2(10),
                   DT   DATE,
                   N    NUMBER
                  )
/
CREATE INDEX T_LOG_I1 ON T_LOG(CODE)
/

А теперь сравни:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.

VARIABLE PCODE VARCHAR2(10)
  SELECT  MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N) 
    FROM  T_LOG B
    WHERE B.CODE = :PCODE
/
SELECT  *
  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR())
/

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  4frpng6t03xdn, child number 1
-------------------------------------
  SELECT  MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N)     FROM  T_LOG
B     WHERE B.CODE = :PCODE

Plan hash value: 1316027242

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE              |          |     1 |    29 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_LOG    |     1 |    29 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_LOG_I1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."CODE"=:PCODE)

Note
-----
   - dynamic sampling used for this statement (level=2)


25 rows selected.

VARIABLE PCODE NUMBER
  SELECT  MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N) 
    FROM  T_LOG B
    WHERE B.CODE = :PCODE
/
SELECT  *
  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR())
/


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  4frpng6t03xdn, child number 0
-------------------------------------
  SELECT  MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N)     FROM  T_LOG
B     WHERE B.CODE = :PCODE

Plan hash value: 597331626

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE    |       |     1 |    29 |            |          |
|*  2 |   TABLE ACCESS FULL| T_LOG |     1 |    29 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("B"."CODE")=:PCODE)

Note
-----
   - dynamic sampling used for this statement (level=2)


24 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

22.06.2021, 21:00

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079474

Участник

Откуда: Middlebury, CT USA

Сообщения: 10 588

Рейтинг: 0 / 0

A_-_ND,

Ну и то что INDEX FULL SCAN T_LOG_I1 настойчиво намекает что индекс T_LOG_I1 на более чем CODE и CODE не первая колонка:

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.

DROP INDEX T_LOG_I1
/
CREATE INDEX T_LOG_I1 ON T_LOG(N,CODE,DT)
/
VARIABLE PCODE VARCHAR2(10)
EXPLAIN PLAN FOR
  SELECT  MAX(B.DT) KEEP (DENSE_RANK LAST ORDER BY B.N) 
    FROM  T_LOG B
    WHERE B.CODE = :PCODE
/
SELECT  *
  FROM  TABLE(DBMS_XPLAN.DISPLAY)
/


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 3403113949

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    29 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |          |     1 |    29 |            |          |
|*  2 |   INDEX FULL SCAN| T_LOG_I1 |     1 |    29 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."CODE"=:PCODE)
       filter("B"."CODE"=:PCODE)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL>

SY.

...

Рейтинг:

0 / 0

22.06.2021, 21:14

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079477

A_-_ND

Гость

SY,

Уверен, что проблема не в параметре. После пересоздания таблицы(тем же скриптом), без изменения кода, запрос стал работать корректно.

Ладно, таблицу уже пересоздал. Проблемы сейчас нет.
Буду мониторить.

Надеюсь больше не стрельнёт.

Всем спасибо!

...

Рейтинг:

0 / 0

22.06.2021, 21:23

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079493

delphinotes

Гость

Я иногда сталкиваюсь с подобной ситуацией, когда план запроса "ломается".
При этом, если взять SQL-текст и выполнить отдельно - план нормальный, но при запуске этого же текста из приложения/отчёта - запрос дико тормозит. Если его отловить среди запущенных и посмотреть план - то видно, что "кривой".
Помогает удаление "кривого" плана из кэша запросов

...

Рейтинг:

0 / 0

23.06.2021, 01:21

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079503

PuM256

Гость

A_-_ND

SY,
После пересоздания таблицы(тем же скриптом), без изменения кода, запрос стал работать корректно.

Я бы так однозначно не утверждал. INDEX FULL SCAN - не самый лучший метод, как правило.

...

Рейтинг:

0 / 0

23.06.2021, 09:32

| Ответить | Цитировать | Написать

Индекс работает в SQL, но не работает в PL/SQL

#40079592

SQL*Plus

Участник

Откуда: Россия, Москва

Сообщения: 8 665

Рейтинг: 0 / 0

A_-_ND

Научитесь, плииз, грамотно и точно задавать вопросы
и давать точные исчерпывающие ответы на вопросы, которые задают вам.

Но это только в том случае, если вы хотите решить проблему, а не просто поговорить ни о чём. :-)

...

Рейтинг:

0 / 0

23.06.2021, 14:38

| Ответить | Цитировать | Написать

24 сообщений из 24, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Индекс работает в SQL, но не работает в PL/SQL

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=17&tid=1880091]:	0ms
get settings:	10ms
get forum list:	13ms
check forum access:	3ms
check topic access:	3ms
track hit:	43ms
get topic data:	12ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	2ms
others:	235ms

total:	378ms