Почему висит запрос? План запроса внутри

@140907 · Регистрация: 19.02.2016

Author24 — интернет-сервис помощи студентам

Добрый день.
Прошу помочь разобраться с планом выполнения запроса.

SQL

SELECT * FROM ( SELECT /*+ FIRST_ROWS(50) */ rownum AS row_num, t.* FROM ( 
 
SELECT t1.partition_num AS t1partition_num, 
t2.name AS t2id, 
t1.var_id AS t1var_id, 
t1.user_status AS t1user_status, 
t1.host AS t1host, 
t1.status_id AS t1status_id, 
t1.sys_date_update AS t1sys_date_update, 
t1.date_main AS t1date_main, 
t1.name AS t1name, 
t1.id AS t1id 
FROM z_letters_public t1 -- Это вью (смотрит в таб. Z_LETTERS, Z_VAR, Z).
LEFT JOIN z_letter_status t2 ON (t1.status_id=t2.id) 
WHERE 1 = 1  AND t1.post_id=702 
ORDER BY t1id, t1id ) t 
 
WHERE rownum < 51) WHERE row_num >= 1

Смотрю план выполнения.

SQL

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id_этого запроса',0))
 
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                     | ROWS  | Bytes | Cost (%CPU)| TIME     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |       |       |   288 (100)|          |
|*  1 |  VIEW                               |                          |    50 |   210K|   288   (0)| 00:00:04 |
|*  2 |   COUNT STOPKEY                     |                          |       |       |            |          |
|   3 |    VIEW                             |                          |    52 |   217K|   288   (0)| 00:00:04 |
|*  4 |     FILTER                          |                          |       |       |            |          |
|   5 |      NESTED LOOPS SEMI              |                          |    52 |  5356 |   236   (0)| 00:00:03 |
|   6 |       NESTED LOOPS OUTER            |                          |    52 |  5252 |    80   (0)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID  |       Z_LETTERS          |    25M|  1684M|    28   (0)| 00:00:01 |
|   8 |         INDEX FULL SCAN             |       Z_LETTERS_ID_PK    |    52 |       |     3   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID  |       Z_LETTER_STATUS    |     1 |    33 |     1   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN           |       Z_LETTER_STATUS_PK |     1 |       |     0   (0)|          |
|  11 |       VIEW PUSHED PREDICATE         | VW_SQ_1                  |     1 |     2 |     3   (0)| 00:00:01 |
|  12 |        NESTED LOOPS                 |                          |     1 |    25 |     3   (0)| 00:00:01 |
|  13 |         NESTED LOOPS                |                          |     1 |    21 |     3   (0)| 00:00:01 |
|  14 |          TABLE ACCESS BY INDEX ROWID|       Z_VAR              |     1 |     8 |     2   (0)| 00:00:01 |
|* 15 |           INDEX UNIQUE SCAN         |       Z_VAR_PK           |     1 |       |     1   (0)| 00:00:01 |
|* 16 |          TABLE ACCESS BY INDEX ROWID|       Z                  |     3 |    39 |     1   (0)| 00:00:01 |
|* 17 |           INDEX UNIQUE SCAN         |       Z_PK               |     1 |       |     0   (0)|          |
|* 18 |         INDEX UNIQUE SCAN           |    Z_LAYER_PK            |   845 |  3380 |     0   (0)|          |
|  19 |      TABLE ACCESS BY INDEX ROWID    |       Z_VAR              |     1 |     8 |     2   (0)| 00:00:01 |
|* 20 |       INDEX UNIQUE SCAN             |       Z_VAR_PK           |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - FILTER("ROW_NUM">=1)
   2 - FILTER(ROWNUM<51)
   4 - FILTER(=702)
  10 - access("STATUS_ID"="t2"."ID")
  15 - access("B"."ID"="A"."VAR_ID")
  16 - FILTER(("VIRTUAL_PORTAL_ID"="пакет для вычисления id портала"(USER@!) AND 
              NVL("P"."HIDDEN_FLAG",0)<>1))
  17 - access("P"."ID"="B"."POST_ID")
  18 - access("P"."LAYER_ID"="C"."ID")
  20 - access("B"."ID"=:B1)

И план как будто бы нормальный - запрос должен выполняться быстро.
Но он висит минутами!
И как строка 7 может занимать одну секунду, если просматривается 25 млн строк?
Может быть, я неправильно читаю план?

Добавлено через 3 минуты
Таблица Z_LETTERS - непартиционированная, 25 000 000 строк.
На ней есть индексы (созданы в 2013 году, анализируются регулярно (90%), но не ребилдились ни разу).
Z_LETTERS_ID_PK
Z_LETTERS_VAR_ID
Z_LETTERS_DATE_MAIN

В таблицу круглосуточно вставляются строки (примерно 300 000 в день). Ничего не апдейтится.
Ночью строки старее 90 дней удаляются.

@AGK · 20.02.2016, 09:29

Сообщение от 140907

Эта вьюха не все запросы показывает.

Она показывает только те запросы, в которых есть длинные операции. А Oracle сам решает, какую операцию назначить длинной. Как этим управлять (и можно ли), я не знаю.

Сообщение от 140907

Можно ли заставить оракл использовать INDEX FAST FULL SCAN?

Хинт есть, но Oracle сам принимает решенние. Есть вариант принудительно заставить запрос работать по заранее сохраненному плану, но это очень большая головная боль и там надо соблюсти кучу условий.

Сообщение от 140907

Но запрос из первого поста дольше 6 секунд 100%

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

Сообщение от 140907

Но пролистовка нужна

Это те самые rownum ? С ним надо осторожно. Может Вы его неправильно используете, поэтому запрос долго работает. Расскажите про пролистовку подробнее. Что такое, с чем едят, как организуется, кто придумал, работала ли когда-нибудь и почему перестала работать. Возможно, имеет смысл организовать по-другому (если базируется на rownum)

Добавлено через 30 минут

Сообщение от 140907

нет в v$session_longops ничего похожего на LETTERS.

Сообщение от 140907

INDEX FULL SCAN | Z_LETTERS_ID_PK

В плане показано сканирование индекса. Если оно долгая операция, то должна появиться в longops. Если не появляется, значит либо очень быстро проскакивает, либо идет долго, но Oracle ее не относит к классу длинных операций. Это, кстати, самый противный случай, когда непонятно, что делать.

@140907 · 20.02.2016, 12:33 **[ТС]**

Сообщение от AGK

Хинт есть, но Oracle сам принимает решенние.

Вы использовали такой хинт? Какой он?

Сообщение от AGK

Вы хотите сказать, что запрос работает быстро? Или у него нет длинных операций, хотя он работает долго?

Запрос точно не работает быстро. Наверно, у него нет длинных операций, хотя он работает долго.
Вот я только что запустил первый запрос. В v$session_longops ничего похожего на LETTER нет (фильтрую по id сессии - ни одной строки). Если же выполняю select count(distinct id) - появляется запись с Index Fast Full Scan и верным id сессии.

Сообщение от AGK

Это те самые rownum ? С ним надо осторожно. Может Вы его неправильно используете, поэтому запрос долго работает. Расскажите про пролистовку подробнее. Что такое, с чем едят, как организуется, кто придумал, работала ли когда-нибудь и почему перестала работать. Возможно, имеет смысл организовать по-другому (если базируется на rownum)

Приложение позволяет работать с БД через браузер. На странице можно задать условия поиска (т.н. "фильтр") и получить результат (т.н. "список"). Кол-во строк в списке надо ограничить, т.е. надо сделать пролистовку. Она сделана путем обертывания основного запроса запросами с rownum (в первом запросе - это строки 1 и 18). Она работает везде. Другое дело, с какой скоростью. Но как иначе организовать - не знаю. Пользователю надо предоставить возможность не только фильтровать, но и сортировать. Так что как иначе формировать результат для пользователя - ума не приложу.

Сообщение от AGK

В плане показано сканирование индекса. Если оно долгая операция, то должна появиться в longops. Если не появляется, значит либо очень быстро проскакивает, либо идет долго, но Oracle ее не относит к классу длинных операций. Это, кстати, самый противный случай, когда непонятно, что делать.

А если быстро проскакивает, тогда в каком месте первый запрос выполняется быстро?

Добавлено через 2 минуты

Сообщение от 140907

А если быстро проскакивает, тогда в каком месте первый запрос выполняется быстро?

Надо читать как: А если быстро проскакивает, тогда в каком месте первый запрос выполняется медленно?
Ох, замаялся

@AGK · 20.02.2016, 13:58

Насколько я понимаю, можно в хинте указать только имя индекса, а Oracle сам, в зависимости от ситуации, выбирает метод доступа к индексу. Посмотрите https://habrahabr.ru/post/189574/
и https://docs.oracle.com/cd/E11... m#PFGRF005

Попробуйте запустить такой селект (для начала без хинтов) и посмотрите на план. Там у меня есть комментарии, и вообще, селект можно упростить. Сортировку лучше выносить в самый конец. С nn можно задавать диапазон. C rownum диапазон задавать нельзя, он всегда начинается с 1. Попробуйте селект без JOIN с оракловыми (+). Иногда так лучше план получается (или быстрее выполняется), несмотря на все уверения в эквивалентности.

SQL

SELECT * FROM (        -- можно (закомментировать) убрать вместе с последней скобкой
SELECT t.* 
FROM ( 
SELECT ROW_NUMBER() OVER (ORDER BY  t1.id, t1.id) nn, -- зачем в сортировке два раза одно и то же, так в исходном было
t1.partition_num AS t1partition_num, 
t2.name AS t2id, 
t1.var_id AS t1var_id, 
t1.user_status AS t1user_status, 
t1.host AS t1host, 
t1.status_id AS t1status_id, 
t1.sys_date_update AS t1sys_date_update, 
t1.date_main AS t1date_main, 
t1.name AS t1name, 
t1.id AS t1id 
FROM z_letters_public t1 -- Это вью (смотрит в таб. Z_LETTERS, Z_VAR, Z).
    LEFT JOIN z_letter_status t2 ON (t1.status_id=t2.id) 
WHERE 1 = 1  AND t1.post_id=702 
) t 
WHERE nn < 51 
)      --  можно (закомментировать)  убрать вместе с первым селектом
-- WHERE nn >= 1 -- бессмысленное условие, выполняется тождественно
ORDER BY nn

Добавлено через 24 минуты

Сообщение от AGK

ON (t1.status_id=t2.id)

Правильно ли я понимаю, что в таблице t2 может не оказаться id, которое удовлетворяет условию t1.status_id=t2.id ?

Добавлено через 18 минут

Сообщение от 140907

тогда в каком месте первый запрос выполняется медленно?

Для начала я попробовал бы запустить

SQL

1	SELECT * FROM v$session WHERE sid=my_sid

и понаблюдать за полями PROGRAM, MODULE, ACTION и EVENT. Возможно, ваше приложение что-то пишет в первые три поля, и вы сможете что-то понять, а наблюдение за событиями может, например, показать, что запрос ждет какой-то ресурс или еще что-нибудь.

@140907 · 20.02.2016, 14:09 **[ТС]**

Выполнил ваш вариант первого запроса.
Что-то меня пугает план

Особенно буква G

SQL

19  ----------------------------------------------------------------------------------------------------------------------------
20  | Id  | Operation                             | Name                       | ROWS  | Bytes |TempSpc| Cost (%CPU)| TIME     |
21  ----------------------------------------------------------------------------------------------------------------------------
22  |   0 | SELECT STATEMENT                      |                            |       |       |       |  1371K(100)|          |
23  |   1 |  SORT ORDER BY                        |                            |  1508K|  6188M|    11G|  1371K  (1)| 04:34:20 |
24  |*  2 |   VIEW                                |                            |  1508K|  6188M|       | 18627   (1)| 00:03:44 |
25  |*  3 |    WINDOW SORT PUSHED RANK            |                            |  1508K|   163M|  1872K| 18627   (1)| 00:03:44 |
26  |*  4 |     HASH JOIN RIGHT OUTER             |                            | 15084 |  1679K|       | 17624   (1)| 00:03:32 |
27  |   5 |      TABLE ACCESS FULL                |       Z_LETTER_STATUS      |     3 |    99 |       |     3   (0)| 00:00:01 |
28  |   6 |      NESTED LOOPS                     |                            |       |       |       |            |          |
29  |   7 |       NESTED LOOPS                    |                            | 15084 |  1193K|       | 17620   (1)| 00:03:32 |
30  |   8 |        VIEW                           | VW_SQ_1                    |   305 |  3965 |       |    17   (6)| 00:00:01 |
31  |   9 |         HASH UNIQUE                   |                            |   305 |  7625 |       |            |          |
32  |* 10 |          HASH JOIN                    |                            |   305 |  7625 |       |    17   (6)| 00:00:01 |
33  |  11 |           NESTED LOOPS                |                            |     3 |    51 |       |     3   (0)| 00:00:01 |
34  |* 12 |            TABLE ACCESS BY INDEX ROWID|       Z                    |     3 |    39 |       |     3   (0)| 00:00:01 |
35  |* 13 |             INDEX RANGE SCAN          |       Z__VIRTUAL_PORTAL_ID |     3 |       |       |     1   (0)| 00:00:01 |
36  |* 14 |            INDEX UNIQUE SCAN          |    Z_LAYER_PK              |     1 |     4 |       |     0   (0)|          |
37  |  15 |           TABLE ACCESS FULL           |       Z_VAR                |  7189 | 57512 |       |    13   (0)| 00:00:01 |
38  |* 16 |        INDEX RANGE SCAN               |       Z_LETTERS__VAR_ID    |    49 |       |       |    40   (0)| 00:00:01 |
39  |  17 |         TABLE ACCESS BY INDEX ROWID   |       Z_VAR                |     1 |     8 |       |     2   (0)| 00:00:01 |
40  |* 18 |          INDEX UNIQUE SCAN            |       Z_VAR_PK             |     1 |       |       |     1   (0)| 00:00:01 |
41  |  19 |       TABLE ACCESS BY INDEX ROWID     |       Z_LETTERS            |    49 |  3332 |       |    64   (0)| 00:00:01 |
42  ----------------------------------------------------------------------------------------------------------------------------
43   
44  Predicate Information (IDENTIFIED BY operation id):
45  ---------------------------------------------------
46   
47     2 - FILTER("NN"<51)
48     3 - FILTER(ROW_NUMBER() OVER ( ORDER BY "ID","ID")<51)
49     4 - access("STATUS_ID"="T2"."ID")
50    10 - access("P"."ID"="B"."POST_ID")
51    12 - FILTER(NVL("P"."HIDDEN_FLAG",0)<>1)
52    13 - access("VIRTUAL_PORTAL_ID"="VIRTUAL_PORTAL_PACK"."GET_VP_ID"(USER@!))
53    14 - access("P"."LAYER_ID"="C"."ID")
54    16 - access("ITEM_1"="A"."VAR_ID")
55         FILTER(=702)
56    18 - access("B"."ID"=:B1)

@AGK · 20.02.2016, 14:36

Снова по поводу

Сообщение от AGK

z_letter_status t2

Является ли поле t2.id первичным ключем или, хотя бы, уникальным полем? Если не знаете наверняка, проверить на уникальность можно, например, так

SQL

1 2	SELECT id, COUNT() FROM z_letter_status GROUP BY id HAVING COUNT()>1

Добавлено через 8 минут
А если попытаться выполнить, выполняется или как? На G пока не смотрите, не до грибов.

Обратите внимание, план на другой индекс перескочил.

Сообщение от 140907

INDEX RANGE SCAN | Z_LETTERS__VAR_ID

А сколько записей-то в z_letter_status - три или больше? И если больше, попробуйте на другие вопросы по z_letter_status ответить

Добавлено через 3 минуты
Кстати t1.id - это NUMBER или что-то другое? Если не NUMBER, то что именно?

Добавлено через 10 минут
У вас есть поле partition_num. На какой таблице партиции и по какому параметру? Почему в запросе нет никаких ограничений по партициям? Какие индексы, партиционированные или нет?

Добавлено через 3 минуты
Если, как Вы писали,

Сообщение от 140907

В таблицу круглосуточно вставляются строки (примерно 300 000 в день). Ничего не апдейтится. Ночью строки старее 90 дней удаляются

, то партиции должны быть на Z_LETTERS и по датам. Почему в запросе нет никаких дат?

@140907 · 20.02.2016, 14:59 **[ТС]**

Запустил ваш запрос из 23 сообщения - за минуту не уложился.
В v$session_longops ничего нет.

В z_letter_status три строки.
В обеих таблицах id - это уникальный pk, number.
Таблицы не партиционированы. Просто колонка так называется. Почему - не знаю.

Добавлено через 24 секунды
Спасибо за помощь и внимание к моей проблеме.

Добавлено через 1 минуту
В запросе нет дат, т.к. может быть ситуация, когда хочется увидеть все без дат.
Но один из обсуждаемых планов - партиционировать таблицу z_letters по дате и заставлять пользователя указывать дату.

Добавлено через 54 секунды
Все запросы я выполняю вручную через подобие sql plus.

Добавлено через 56 секунд

Сообщение от AGK

Правильно ли я понимаю, что в таблице t2 может не оказаться id, которое удовлетворяет условию t1.status_id=t2.id

Думаю, это почти невозможно.

@AGK · 20.02.2016, 15:00

Мне как раз план нравится

Сообщение от 140907

SELECT STATEMENT
SORT ORDER BY 11G |1371K (1)|04:34:20

Он лишний раз показывает, что если из запроса убрать всякую искажающую суть лабуду типа rownum, то запрос хочет выполняться 4 с половиной часа. Признайтесь, первый запрос выполнялся хоть когда-нибудь? Или это какой-то новодел?
Если так, то тогда становится понятно, в частности, отсутствие longops. Вы просто не даете запросу дойти до того момента, когда они появляются и сбрасываете его выполнение раньше.

@140907 · 20.02.2016, 15:04 **[ТС]**

Сообщение от AGK

-- WHERE nn >= 1 -- бессмысленное условие, выполняется тождественно

Для первой страницы - да. Но ведь надо сформировать другие страницы, на которых показать, например, с 51 по 100 строки.

Добавлено через 2 минуты

Сообщение от AGK

Он лишний раз показывает, что если из запроса убрать всякую искажающую суть лабуду типа rownum, то запрос хочет выполняться 4 с половиной часа. Признайтесь, первый запрос выполнялся хоть когда-нибудь? Или это какой-то новодел?

Когда в таблице было не 25 млн - выполнялся всегда.
Даже сейчас он выполняется иногда, но условия успешного выполнения я не определил.

@AGK · 20.02.2016, 15:22

Сообщение от 140907

Запустил ваш запрос из 23 сообщения - за минуту не уложился.

А за сколько уложился? Или сбросили, не дождавшись?

Сообщение от 140907

В z_letter_status три строки.

Сообщение от 140907

Думаю, это почти невозможно.

Зачем Вам внешняя связь, если невозможно. Зачем вообще связь, если так мало записей. Гораздо лучше сработает подзапрос.

Я сейчас нарисую еще один запрос, только не удивляйтесь и не падайте в обморок.
Очень желательно хотя бы один раз довести запрос до конца (даже если это долго) и посмотреть на длинные операции.

SQL

-- SELECT * FROM (        -- можно (закомментировать) убрать вместе с последней скобкой
SELECT t.* 
FROM ( 
SELECT ROW_NUMBER() OVER (ORDER BY  t1.id, t1.id) nn, -- зачем в сортировке два раза одно и то же, так в исходном было
t1.partition_num AS t1partition_num, 
(SELECT t2.name FROM z_letter_status t2 WHERE t2.id=t1.status_id) AS t2id,  
t1.var_id AS t1var_id, 
t1.user_status AS t1user_status, 
t1.host AS t1host, 
t1.status_id AS t1status_id, 
t1.sys_date_update AS t1sys_date_update, 
t1.date_main AS t1date_main, 
t1.name AS t1name, 
t1.id AS t1id 
FROM z_letters_public t1 -- Это вью (смотрит в таб. Z_LETTERS, Z_VAR, Z).
    ---   LEFT JOIN z_letter_status t2 ON (t1.status_id=t2.id) 
WHERE 1 = 1  AND t1.post_id=702 
AND t1.id = to_number(t1.id)   -- не удалять!!!
) t 
WHERE nn < 51 
-- )      --  можно (закомментировать)  убрать вместе с первым селектом
-- WHERE nn >= 1 -- бессмысленное условие, выполняется тождественно
ORDER BY nn

Добавлено через 7 минут

Сообщение от 140907

Для первой страницы - да. Но ведь надо сформировать другие страницы, на которых показать, например, с 51 по 100 строки

Вот тогда и поставите условие nn between 51 and 100

Сообщение от 140907

Когда в таблице было не 25 млн - выполнялся всегда

А сколько было записей в таблице в "тестовом" варианте, когда запрос всегда выполнялся? И сколько времени он выполнялся?

@140907 · 20.02.2016, 15:32 **[ТС]**

Сообщение от AGK

AND t1.id = to_number(t1.id)

К чему это приведет?

Добавлено через 1 минуту
Просто если я подвешу всю базу (100% заполнение памяти или еще что), мне что-нибудь могут сделать.

Добавлено через 8 минут

Сообщение от AGK

А за сколько уложился? Или сбросили, не дождавшись?

Происходит автоматический сброс. Но можно попробовать это обойти. Подумаю как.

Сообщение от AGK

Зачем Вам внешняя связь, если невозможно. Зачем вообще связь, если так мало записей. Гораздо лучше сработает подзапрос.

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

Сообщение от AGK

Вот тогда и поставите условие nn between 51 and 100

Битвин работает быстрее?

Сообщение от AGK

А сколько было записей в таблице в "тестовом" варианте, когда запрос всегда выполнялся? И сколько времени он выполнялся?

Не могу сказать. Но сейчас он выполняется быстро, если: указать date_main, post_id и var_id.

@AGK · 20.02.2016, 15:49

Сообщение от 140907

быстро, если: указать date_main, post_id и var_id

То есть быстро, если сильно ограничить число записей. Поскольку есть дата, то подозреваю, что вместо 25млн играют 300тыс. Или еще меньше.

Сообщение от 140907

К чему это приведет?

Раз так страшно, не запускайте, а, для начала, посмотрите (покажите) план. Это - некая недокументированная особенность, которую мы случайно обнаружили и проверили на двух совершенно разных базах в совершенно разных конторах. Должно привести к переключению на другой индекс и, возможно к уменьшению коста. Если действительно так, попробуйте запустить на 1мин., вдруг проскочит.

Сообщение от 140907

ораклу ничего не стоит вычитать их себе в кэш

Но при этом план дурной будет. Именно из-за наличия внешней связи. А с подзапросом он будет другой, надеюсь.

Сообщение от 140907

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

Вообще он должен перекодироваться в <= and >= . Но суть не в этом. Суть в том, что с rownum таких вещей делать нельзя. А со значение функции row_number() - можно.

@140907 · 20.02.2016, 15:51 **[ТС]**

Если указать только date_main и post_id, то с момента запуска примерно минуту ничего не происходит, через минуту приложение говорит "извините", и в это же время в v$session_longops появляется запись Index Fast Full Scan: Z_LETTERS - выполняется в течение 90 секунд.

Добавлено через 52 секунды
Получается, что первый запрос (если в него добавить дату) выполняется (вероятно) 150 секунд.

Добавлено через 33 секунды

Сообщение от AGK

То есть быстро, если сильно ограничить число записей. Поскольку есть дата, то подозреваю, что вместо 25млн играют 300тыс. Или еще меньше.

Я брал с 1 по 20 февраля.

@AGK · 20.02.2016, 16:05

Сообщение от AGK

А с подзапросом он будет другой, надеюсь.

Иногда даже стоит создать функцию типа

SQL

CREATE OR REPLACE FUNCTION ret_name(pstatus_id IN NUMBER) RETURN varchar2 DETERMINISTIC  IS
  vname    varchar2(1000);
BEGIN 
    SELECT name INTO vname
    FROM z_letter_status WHERE id=pstatus_id;
    RETURN vname;
exception
    WHEN others RETURN NULL;
END;

и вызывать ее, а не подзапрос, только ради изменения плана

Добавлено через 7 минут

Сообщение от 140907

запрос (если в него добавить дату) выполняется (вероятно) 150 секунд

И судя по Вашему диапазону дат он лопатит порядка 5млн записей. Ну это не 25млн., все-таки. Интересно, а на каких индексах скорость выполнения выше, не пробовали оценить?

@140907 · 21.02.2016, 17:05 **[ТС]**

Извините, но я должен взять паузу, т.к. я на работе, и надо решать другие задачи. Я попробую сделать вечером или в выходные.

Добавлено через 9 минут
Еще раз спасибо за помощь!

Добавлено через 22 часа 42 минуты
Добавил ваше новое условие (t1.id = to_number(t1.id)) в самый первый запрос темы.
За минуту результат не получен.
После этого долго смотрел в v$session_longops - ничего похожего.
Если правильно понимаю план, долго чтение индекса заменилось на многочисленные сравнения.

SQL

21  --------------------------------------------------------------------------------------------------------------------
22  | Id  | Operation                             | Name                       | ROWS  | Bytes | Cost (%CPU)| TIME     |
23  --------------------------------------------------------------------------------------------------------------------
24  |   0 | SELECT STATEMENT                      |                            |       |       | 18495 (100)|          |
25  |*  1 |  VIEW                                 |                            |     1 |  4302 | 18495   (1)| 00:03:42 |
26  |*  2 |   COUNT STOPKEY                       |                            |       |       |            |          |
27  |   3 |    VIEW                               |                            |     1 |  4289 | 18495   (1)| 00:03:42 |
28  |*  4 |     SORT ORDER BY STOPKEY             |                            |     1 |   105 | 18495   (1)| 00:03:42 |
29  |   5 |      NESTED LOOPS OUTER               |                            |     1 |   105 | 18492   (1)| 00:03:42 |
30  |   6 |       NESTED LOOPS                    |                            |     1 |    72 | 18491   (1)| 00:03:42 |
31  |   7 |        VIEW                           | VW_SQ_1                    |   310 |  1240 |    17   (6)| 00:00:01 |
32  |   8 |         HASH UNIQUE                   |                            |   310 |  7750 |            |          |
33  |*  9 |          HASH JOIN                    |                            |   310 |  7750 |    17   (6)| 00:00:01 |
34  |  10 |           NESTED LOOPS                |                            |     3 |    51 |     3   (0)| 00:00:01 |
35  |* 11 |            TABLE ACCESS BY INDEX ROWID|       Z                    |     3 |    39 |     3   (0)| 00:00:01 |
36  |* 12 |             INDEX RANGE SCAN          |       Z__VIRTUAL_PORTAL_ID |     3 |       |     1   (0)| 00:00:01 |
37  |* 13 |            INDEX UNIQUE SCAN          |    Z_LAYER_PK              |     1 |     4 |     0   (0)|          |
38  |  14 |           TABLE ACCESS FULL           |       Z_VAR                |  7284 | 58272 |    13   (0)| 00:00:01 |
39  |* 15 |        TABLE ACCESS BY INDEX ROWID    |       Z_LETTERS            |     1 |    68 |    66   (0)| 00:00:01 |
40  |* 16 |         INDEX RANGE SCAN              |       Z_LETTERS__VAR_ID    |    49 |       |    41   (0)| 00:00:01 |
41  |  17 |          TABLE ACCESS BY INDEX ROWID  |       Z_VAR                |     1 |     8 |     2   (0)| 00:00:01 |
42  |* 18 |           INDEX UNIQUE SCAN           |       Z_VAR_PK             |     1 |       |     1   (0)| 00:00:01 |
43  |  19 |       TABLE ACCESS BY INDEX ROWID     |       Z_LETTER_STATUS      |     1 |    33 |     1   (0)| 00:00:01 |
44  |* 20 |        INDEX UNIQUE SCAN              |       Z_LETTER_STATUS_PK   |     1 |       |     0   (0)|          |
45  --------------------------------------------------------------------------------------------------------------------
46   
47  Predicate Information (IDENTIFIED BY operation id):
48  ---------------------------------------------------
49   
50     1 - FILTER("ROW_NUM">=1)
51     2 - FILTER(ROWNUM<51)
52     4 - FILTER(ROWNUM<51)
53     9 - access("P"."ID"="B"."POST_ID")
54    11 - FILTER(NVL("P"."HIDDEN_FLAG",0)<>1)
55    12 - access("VIRTUAL_PORTAL_ID"="пакет"."процедура"(USER@!))
56    13 - access("P"."LAYER_ID"="C"."ID")
57    15 - FILTER("ID"=TO_NUMBER(TO_CHAR("ID")))
58    16 - access("ITEM_1"="A"."VAR_ID")
59         FILTER(=702)
60    18 - access("B"."ID"=:B1)
61    20 - access("STATUS_ID"="T2135064386"."ID")

Сейчас попробую выполнить ваш последний вариант запроса.

Добавлено через 57 минут
Выполнил ваш запрос из 29 комментария.
За минуту не уложился.
В долгих операциях - пусто.
Вот план.

SQL

19  -------------------------------------------------------------------------------------------------------------------
20  | Id  | Operation                            | Name                       | ROWS  | Bytes | Cost (%CPU)| TIME     |
21  -------------------------------------------------------------------------------------------------------------------
22  |   0 | SELECT STATEMENT                     |                            |       |       | 18495 (100)|          |
23  |   1 |  TABLE ACCESS BY INDEX ROWID         |       Z_LETTER_STATUS      |     1 |    33 |     1   (0)| 00:00:01 |
24  |*  2 |   INDEX UNIQUE SCAN                  |       Z_LETTER_STATUS_PK   |     1 |       |     0   (0)|          |
25  |   3 |  SORT ORDER BY                       |                            |     1 |  4302 | 18495   (1)| 00:03:42 |
26  |*  4 |   VIEW                               |                            |     1 |  4302 | 18494   (1)| 00:03:42 |
27  |*  5 |    WINDOW SORT PUSHED RANK           |                            |     1 |    81 | 18494   (1)| 00:03:42 |
28  |   6 |     NESTED LOOPS                     |                            |       |       |            |          |
29  |   7 |      NESTED LOOPS                    |                            |     1 |    81 | 18491   (1)| 00:03:42 |
30  |   8 |       VIEW                           | VW_SQ_1                    |   310 |  4030 |    17   (6)| 00:00:01 |
31  |   9 |        HASH UNIQUE                   |                            |   310 |  7750 |            |          |
32  |* 10 |         HASH JOIN                    |                            |   310 |  7750 |    17   (6)| 00:00:01 |
33  |  11 |          NESTED LOOPS                |                            |     3 |    51 |     3   (0)| 00:00:01 |
34  |* 12 |           TABLE ACCESS BY INDEX ROWID|       Z                    |     3 |    39 |     3   (0)| 00:00:01 |
35  |* 13 |            INDEX RANGE SCAN          |       Z__VIRTUAL_PORTAL_ID |     3 |       |     1   (0)| 00:00:01 |
36  |* 14 |           INDEX UNIQUE SCAN          |    Z_LAYER_PK              |     1 |     4 |     0   (0)|          |
37  |  15 |          TABLE ACCESS FULL           |       Z_VAR                |  7284 | 58272 |    13   (0)| 00:00:01 |
38  |* 16 |       INDEX RANGE SCAN               |       Z_LETTERS__VAR_ID    |    49 |       |    41   (0)| 00:00:01 |
39  |  17 |        TABLE ACCESS BY INDEX ROWID   |       Z_VAR                |     1 |     8 |     2   (0)| 00:00:01 |
40  |* 18 |         INDEX UNIQUE SCAN            |       Z_VAR_PK             |     1 |       |     1   (0)| 00:00:01 |
41  |* 19 |      TABLE ACCESS BY INDEX ROWID     |       Z_LETTERS            |     1 |    68 |    66   (0)| 00:00:01 |
42  -------------------------------------------------------------------------------------------------------------------
43   
44  Predicate Information (IDENTIFIED BY operation id):
45  ---------------------------------------------------
46   
47     2 - access("T2"."ID"=:B1)
48     4 - FILTER("NN"<51)
49     5 - FILTER(ROW_NUMBER() OVER ( ORDER BY "ID","ID")<51)
50    10 - access("P"."ID"="B"."POST_ID")
51    12 - FILTER(NVL("P"."HIDDEN_FLAG",0)<>1)
52    13 - access("VIRTUAL_PORTAL_ID"="пакет"."процедура"(USER@!))
53    14 - access("P"."LAYER_ID"="C"."ID")
54    16 - access("ITEM_1"="A"."VAR_ID")
55         FILTER(=702)
56    18 - access("B"."ID"=:B1)
57    19 - FILTER("ID"=TO_NUMBER(TO_CHAR("ID")))
58

@AGK · 22.02.2016, 17:19

Сообщение от 140907

Выполнил ваш запрос из 29 комментария.

К сожалению, без непосредственного доступа к вашей базе, тяжело что-либо советовать дальше. Пока можно сказать только одно: несмотря на уменьшившийся кост, запрос считается долго. Поэтому надо попытаться вернуться к тем запросам, которые считаются быстро. Но я бы, все-таки, убрал rownum и заменил его на row_number(). Уберите id=to_number(id) и в условия WHERE добавьте одну дату. Активируются другие индексы. Судя по всему, надо вызывать именно их. Короче говоря, надо как-то повторить все ваши таблицы (хотя бы на 10%) и их индексы, а также используемые вьюхи и поиграть с ними. Но я не представляю, как это сделать. Например, может быть, нужны другие индексы. Например, id вместе с датой. И row_number() надо сортировать по дате и id. Но это надо делать самому, так как обратная связь через форум - это слишком долго

@AGK 763 / 664 / 194 Регистрация: 24.11.2015 Сообщений: 2,158
	20.02.2016, 09:29	21
	Сообщение от 140907 Эта вьюха не все запросы показывает. Она показывает только те запросы, в которых есть длинные операции. А Oracle сам решает, какую операцию назначить длинной. Как этим управлять (и можно ли), я не знаю. Сообщение от 140907 Можно ли заставить оракл использовать INDEX FAST FULL SCAN? Хинт есть, но Oracle сам принимает решенние. Есть вариант принудительно заставить запрос работать по заранее сохраненному плану, но это очень большая головная боль и там надо соблюсти кучу условий. Сообщение от 140907 Но запрос из первого поста дольше 6 секунд 100% Вы хотите сказать, что запрос работает быстро? Или у него нет длинных операций, хотя он работает долго? Проясните пожалуйста, что имеется в виду. Сообщение от 140907 Но пролистовка нужна Это те самые rownum ? С ним надо осторожно. Может Вы его неправильно используете, поэтому запрос долго работает. Расскажите про пролистовку подробнее. Что такое, с чем едят, как организуется, кто придумал, работала ли когда-нибудь и почему перестала работать. Возможно, имеет смысл организовать по-другому (если базируется на rownum) Добавлено через 30 минут Сообщение от 140907 нет в v$session_longops ничего похожего на LETTERS. Сообщение от 140907 INDEX FULL SCAN \| Z_LETTERS_ID_PK В плане показано сканирование индекса. Если оно долгая операция, то должна появиться в longops. Если не появляется, значит либо очень быстро проскакивает, либо идет долго, но Oracle ее не относит к классу длинных операций. Это, кстати, самый противный случай, когда непонятно, что делать. 0

@140907 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 17
	20.02.2016, 12:33 [ТС]	22
	Сообщение от AGK Хинт есть, но Oracle сам принимает решенние. Вы использовали такой хинт? Какой он? Сообщение от AGK Вы хотите сказать, что запрос работает быстро? Или у него нет длинных операций, хотя он работает долго? Запрос точно не работает быстро. Наверно, у него нет длинных операций, хотя он работает долго. Вот я только что запустил первый запрос. В v$session_longops ничего похожего на LETTER нет (фильтрую по id сессии - ни одной строки). Если же выполняю select count(distinct id) - появляется запись с Index Fast Full Scan и верным id сессии. Сообщение от AGK Это те самые rownum ? С ним надо осторожно. Может Вы его неправильно используете, поэтому запрос долго работает. Расскажите про пролистовку подробнее. Что такое, с чем едят, как организуется, кто придумал, работала ли когда-нибудь и почему перестала работать. Возможно, имеет смысл организовать по-другому (если базируется на rownum) Приложение позволяет работать с БД через браузер. На странице можно задать условия поиска (т.н. "фильтр") и получить результат (т.н. "список"). Кол-во строк в списке надо ограничить, т.е. надо сделать пролистовку. Она сделана путем обертывания основного запроса запросами с rownum (в первом запросе - это строки 1 и 18). Она работает везде. Другое дело, с какой скоростью. Но как иначе организовать - не знаю. Пользователю надо предоставить возможность не только фильтровать, но и сортировать. Так что как иначе формировать результат для пользователя - ума не приложу. Сообщение от AGK В плане показано сканирование индекса. Если оно долгая операция, то должна появиться в longops. Если не появляется, значит либо очень быстро проскакивает, либо идет долго, но Oracle ее не относит к классу длинных операций. Это, кстати, самый противный случай, когда непонятно, что делать. А если быстро проскакивает, тогда в каком месте первый запрос выполняется быстро? Добавлено через 2 минуты Сообщение от 140907 А если быстро проскакивает, тогда в каком месте первый запрос выполняется быстро? Надо читать как: А если быстро проскакивает, тогда в каком месте первый запрос выполняется медленно? Ох, замаялся 0

@140907 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 17
	20.02.2016, 14:59 [ТС]	26
	Запустил ваш запрос из 23 сообщения - за минуту не уложился. В v$session_longops ничего нет. В z_letter_status три строки. В обеих таблицах id - это уникальный pk, number. Таблицы не партиционированы. Просто колонка так называется. Почему - не знаю. Добавлено через 24 секунды Спасибо за помощь и внимание к моей проблеме. Добавлено через 1 минуту В запросе нет дат, т.к. может быть ситуация, когда хочется увидеть все без дат. Но один из обсуждаемых планов - партиционировать таблицу z_letters по дате и заставлять пользователя указывать дату. Добавлено через 54 секунды Все запросы я выполняю вручную через подобие sql plus. Добавлено через 56 секунд Сообщение от AGK Правильно ли я понимаю, что в таблице t2 может не оказаться id, которое удовлетворяет условию t1.status_id=t2.id Думаю, это почти невозможно. 0

@AGK 763 / 664 / 194 Регистрация: 24.11.2015 Сообщений: 2,158
	20.02.2016, 15:00	27
	Мне как раз план нравится Сообщение от 140907 SELECT STATEMENT SORT ORDER BY 11G \|1371K (1)\|04:34:20 Он лишний раз показывает, что если из запроса убрать всякую искажающую суть лабуду типа rownum, то запрос хочет выполняться 4 с половиной часа. Признайтесь, первый запрос выполнялся хоть когда-нибудь? Или это какой-то новодел? Если так, то тогда становится понятно, в частности, отсутствие longops. Вы просто не даете запросу дойти до того момента, когда они появляются и сбрасываете его выполнение раньше. 0

@140907 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 17
	20.02.2016, 15:04 [ТС]	28
	Сообщение от AGK -- WHERE nn >= 1 -- бессмысленное условие, выполняется тождественно Для первой страницы - да. Но ведь надо сформировать другие страницы, на которых показать, например, с 51 по 100 строки. Добавлено через 2 минуты Сообщение от AGK Он лишний раз показывает, что если из запроса убрать всякую искажающую суть лабуду типа rownum, то запрос хочет выполняться 4 с половиной часа. Признайтесь, первый запрос выполнялся хоть когда-нибудь? Или это какой-то новодел? Когда в таблице было не 25 млн - выполнялся всегда. Даже сейчас он выполняется иногда, но условия успешного выполнения я не определил. 0

@140907 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 17
	20.02.2016, 15:32 [ТС]	30
	Сообщение от AGK AND t1.id = to_number(t1.id) К чему это приведет? Добавлено через 1 минуту Просто если я подвешу всю базу (100% заполнение памяти или еще что), мне что-нибудь могут сделать. Добавлено через 8 минут Сообщение от AGK А за сколько уложился? Или сбросили, не дождавшись? Происходит автоматический сброс. Но можно попробовать это обойти. Подумаю как. Сообщение от AGK Зачем Вам внешняя связь, если невозможно. Зачем вообще связь, если так мало записей. Гораздо лучше сработает подзапрос. Так ведь если мало записей, то ораклу ничего не стоит вычитать их себе в кэш и обращаться туда. Просто менять логику приложения я бы не хотел. Сообщение от AGK Вот тогда и поставите условие nn between 51 and 100 Битвин работает быстрее? Сообщение от AGK А сколько было записей в таблице в "тестовом" варианте, когда запрос всегда выполнялся? И сколько времени он выполнялся? Не могу сказать. Но сейчас он выполняется быстро, если: указать date_main, post_id и var_id. 0

@AGK 763 / 664 / 194 Регистрация: 24.11.2015 Сообщений: 2,158
	20.02.2016, 15:49	31
	Сообщение от 140907 быстро, если: указать date_main, post_id и var_id То есть быстро, если сильно ограничить число записей. Поскольку есть дата, то подозреваю, что вместо 25млн играют 300тыс. Или еще меньше. Сообщение от 140907 К чему это приведет? Раз так страшно, не запускайте, а, для начала, посмотрите (покажите) план. Это - некая недокументированная особенность, которую мы случайно обнаружили и проверили на двух совершенно разных базах в совершенно разных конторах. Должно привести к переключению на другой индекс и, возможно к уменьшению коста. Если действительно так, попробуйте запустить на 1мин., вдруг проскочит. Сообщение от 140907 ораклу ничего не стоит вычитать их себе в кэш Но при этом план дурной будет. Именно из-за наличия внешней связи. А с подзапросом он будет другой, надеюсь. Сообщение от 140907 Битвин работает быстрее Вообще он должен перекодироваться в <= and >= . Но суть не в этом. Суть в том, что с rownum таких вещей делать нельзя. А со значение функции row_number() - можно. 0

@140907 0 / 0 / 0 Регистрация: 19.02.2016 Сообщений: 17
	20.02.2016, 15:51 [ТС]	32
	Если указать только date_main и post_id, то с момента запуска примерно минуту ничего не происходит, через минуту приложение говорит "извините", и в это же время в v$session_longops появляется запись Index Fast Full Scan: Z_LETTERS - выполняется в течение 90 секунд. Добавлено через 52 секунды Получается, что первый запрос (если в него добавить дату) выполняется (вероятно) 150 секунд. Добавлено через 33 секунды Сообщение от AGK То есть быстро, если сильно ограничить число записей. Поскольку есть дата, то подозреваю, что вместо 25млн играют 300тыс. Или еще меньше. Я брал с 1 по 20 февраля. 0

@AGK 763 / 664 / 194 Регистрация: 24.11.2015 Сообщений: 2,158
	22.02.2016, 17:19	35
	Сообщение от 140907 Выполнил ваш запрос из 29 комментария. К сожалению, без непосредственного доступа к вашей базе, тяжело что-либо советовать дальше. Пока можно сказать только одно: несмотря на уменьшившийся кост, запрос считается долго. Поэтому надо попытаться вернуться к тем запросам, которые считаются быстро. Но я бы, все-таки, убрал rownum и заменил его на row_number(). Уберите id=to_number(id) и в условия WHERE добавьте одну дату. Активируются другие индексы. Судя по всему, надо вызывать именно их. Короче говоря, надо как-то повторить все ваши таблицы (хотя бы на 10%) и их индексы, а также используемые вьюхи и поиграть с ними. Но я не представляю, как это сделать. Например, может быть, нужны другие индексы. Например, id вместе с датой. И row_number() надо сортировать по дате и id. Но это надо делать самому, так как обратная связь через форум - это слишком долго 0