CftClub.ru
Клуб специалистов ЦФТ-Банк

Очень медленно загружается представление ...
На страницу 1, 2  След.
 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle
Предыдущая тема :: Следующая тема  
Автор Сообщение
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеПн Мар 17, 2014 16:19   Очень медленно загружается представление ... Ответить с цитатой
Полезность: Нет оценки
Доброго времени суток, знатоки ЦФТ.
Мы никак не можем дружит с одной представлении оно у нас очень сильно тормозить (Банковский продукты -> Кредиты часным лицам -> "Список всех кредитов"). После анализа данной представлений выяснил что оно тормозит из-за этой связки:
'{'||(select case when count(1)>0 then '***' else '...' end from z#folder_pay fp where fp.c_prod_fold = to_char(a1_1.id))||'}' C_38
Оказывается у типе "Папки платежей" есть реквизит "PROD_FOLD" типа STRING_16(VARCHAR2(16)).

Никто не знает почему тип этого реквизита VARCHAR2 и как быть в данной ситуации?

Версия 6.0.115.53
Версия ТЯ 7.3.1.2
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
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеВт Мар 18, 2014 08:19   Re: Очень медленно загружается представление ... Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
После анализа данной представлений выяснил что оно тормозит из-за этой связки:
'{'||(select case when count(1)>0 then '***' else '...' end from z#folder_pay fp where fp.c_prod_fold = to_char(a1_1.id))||'}' C_38
Оказывается у типе "Папки платежей" есть реквизит "PROD_FOLD" типа STRING_16(VARCHAR2(16)).

Никто не знает почему тип этого реквизита VARCHAR2 и как быть в данной ситуации?


Тип напрягать не должен.
Попробуйте ограничить подсчёт данных. Ведь все данные, как я понял, в этом запросе нафик не нужны, нужно только знать, есть хоть одна запись, или нет.
Код:
(select case when count(1)>0 then '***' else '...' end from z#folder_pay fp where fp.c_prod_fold = to_char(a1_1.id) and rownum < 2)
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеВт Мар 18, 2014 13:31    Ответить с цитатой
Полезность: Нет оценки
Поставил rownum < 2 не результат выполнения не изменилось.
А Вас join "where varchar = varchar" не смущает?
В таблице Z#PR_CRED около 69545 записи, в z#folder_pay 490478.

Ниже план запроса:
Код:
SQL> explain plan for
  2 
  2  select (select case
  3                   when count(1) > 0 then
  4                    '***'
  5                   else
  6                    '...'
  7                 end
  8            from ibs.z#folder_pay fp
  9           where fp.c_prod_fold = to_char(a1_1.id)
 10             and rownum < 2) papka,a1_1.*
 11    from ibs.z#pr_cred a1_1;
Explained

SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 769389634
--------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              | 68481 |    19M|   899   (2)| 00:00:
|   1 |  SORT AGGREGATE     |              |     1 |     9 |            |
|*  2 |   COUNT STOPKEY     |              |       |       |            |
|*  3 |    TABLE ACCESS FULL| Z#FOLDER_PAY |     8 |    72 |  1275   (2)| 00:00:
|   4 |  TABLE ACCESS FULL  | Z#PR_CRED    | 68481 |    19M|   899   (2)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<2)
   3 - filter("FP"."C_PROD_FOLD"=TO_CHAR(:B1))
17 rows selected


Можно использовать вместо "rownum < 2" хинт FIRST_ROWS но к сожалению время выполнения запроса не изменяется.

Код:
(select /*+FIRST_ROWS(1)*/case when count(1) > 0 then '***' else '...' end from ibs.z#folder_pay fp where fp.c_prod_fold = to_char(a1_1.id))
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3358409332
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |   301 |     2   (0)| 00:00:0
|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| Z#FOLDER_PAY |     8 |    72 |  1275   (2)| 00:00:1
|   3 |  TABLE ACCESS FULL | Z#PR_CRED    |     1 |   301 |     2   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FP"."C_PROD_FOLD"=TO_CHAR(:B1))
15 rows selected
Alexsey
Эксперт


Вступление в Клуб: 06.09.2007
СообщениеВт Мар 18, 2014 15:09    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Поставил rownum < 2 не результат выполнения не изменилось.
А Вас join "where varchar = varchar" не смущает?
В таблице Z#PR_CRED около 69545 записи, в z#folder_pay 490478.

Ниже план запроса:
Код:
SQL> explain plan for
  2 
  2  select (select case
  3                   when count(1) > 0 then
  4                    '***'
  5                   else
  6                    '...'
  7                 end
  8            from ibs.z#folder_pay fp
  9           where fp.c_prod_fold = to_char(a1_1.id)
 10             and rownum < 2) papka,a1_1.*
 11    from ibs.z#pr_cred a1_1;
Explained

SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 769389634
--------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              | 68481 |    19M|   899   (2)| 00:00:
|   1 |  SORT AGGREGATE     |              |     1 |     9 |            |
|*  2 |   COUNT STOPKEY     |              |       |       |            |
|*  3 |   TABLE ACCESS FULL| Z#FOLDER_PAY |     8 |    72 |  1275   (2)| 00:00:
|   4 |  TABLE ACCESS FULL  | Z#PR_CRED    | 68481 |    19M|   899   (2)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<2)
   3 - filter("FP"."C_PROD_FOLD"=TO_CHAR(:B1))
17 rows selected


Можно использовать вместо "rownum < 2" хинт FIRST_ROWS но к сожалению время выполнения запроса не изменяется.

Код:
(select /*+FIRST_ROWS(1)*/case when count(1) > 0 then '***' else '...' end from ibs.z#folder_pay fp where fp.c_prod_fold = to_char(a1_1.id))
SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3358409332
--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     1 |   301 |     2   (0)| 00:00:0
|   1 |  SORT AGGREGATE    |              |     1 |     9 |            |
|*  2 |   TABLE ACCESS FULL| Z#FOLDER_PAY |     8 |    72 |  1275   (2)| 00:00:1
|   3 |  TABLE ACCESS FULL | Z#PR_CRED    |     1 |   301 |     2   (0)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FP"."C_PROD_FOLD"=TO_CHAR(:B1))
15 rows selected


Странно
у меня запрос, приведенный Вами, отрабатывает 4 секунды и отбирает более 300 000 кредитов. Да, кстати, я бы посмотрел почему у вас на индекс не встал запрос по FOLDER_PAY?
Цитата:
Plan hash value: 1257241260

-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 430K| 143M| 7234 (52)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | COUNT STOPKEY | | | | | |
|* 3 | INDEX RANGE SCAN| IDX_Z#FOLDER_PAY_PROD | 26 | 286 | 1 (0) | 00:00:01 |
| 4 | TABLE ACCESS FULL | Z#PR_CRED | 430K| 143M| 7234 (52)| 00:00:02 |
--------------------------------------------------------------------------------------------

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

2 - filter(ROWNUM<2)
3 - access("FP"."C_PROD_FOLD"=TO_CHAR(:B1))

_________________
всегда есть как минимум 2 выхода
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеВт Мар 18, 2014 15:29    Ответить с цитатой
Полезность: Нет оценки
Alexsey, у Вас по реквизиту "C_PROD_FOLD" есть индексы?
Alexsey
Эксперт


Вступление в Клуб: 06.09.2007
СообщениеВт Мар 18, 2014 15:35    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Alexsey, у Вас по реквизиту "C_PROD_FOLD" есть индексы?

Есть.
_________________
всегда есть как минимум 2 выхода
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеВт Мар 18, 2014 15:41    Ответить с цитатой
Полезность: Нет оценки
Alexsey пишет:
Sant пишет:
Alexsey, у Вас по реквизиту "C_PROD_FOLD" есть индексы?

Есть.

Вы сами создали индекс?
Версия ТЯ какая у Вас?
Alexsey
Эксперт


Вступление в Клуб: 06.09.2007
СообщениеВт Мар 18, 2014 15:58    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Alexsey пишет:
Sant пишет:
Alexsey, у Вас по реквизиту "C_PROD_FOLD" есть индексы?

Есть.

Вы сами создали индекс?
Версия ТЯ какая у Вас?

ТЯ 7.3.7.0
версия 14.1
Не помню чтобы, мы сами этот индекс создавали. Скорее всего дистрибутивный.
_________________
всегда есть как минимум 2 выхода
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеВт Мар 18, 2014 16:32    Ответить с цитатой
Полезность: Нет оценки
Понятно, а можно что нибудь делать в данной ситуации? или кроме ЦФТ никто не может помочь?
Еще такой вопрос почему у реквизита "C_PROD_FOLD" тип varchar а не number ?
Alexsey
Эксперт


Вступление в Клуб: 06.09.2007
СообщениеВт Мар 18, 2014 20:29    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Понятно, а можно что нибудь делать в данной ситуации? или кроме ЦФТ никто не может помочь?
Еще такой вопрос почему у реквизита "C_PROD_FOLD" тип varchar а не number ?

Для начала связаться с ЦФТ и уточнить у них про индекс. Как вариант индекс можно создать самим.
Вы сами решите нужно вам быстрое представление быстрое и дистрибутивное или вы будите его делать локально.
_________________
всегда есть как минимум 2 выхода
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеСр Мар 19, 2014 07:04    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Поставил rownum < 2 не результат выполнения не изменилось.
А Вас join "where varchar = varchar" не смущает?

А почему он меня должен смущать? Чем varchar2 такой особенный, что он меня смущать должен?

number = varchar2 или varchar2 = number меня бы смутил, так как тут возможно неявное преобразование типов, что ведёт к слетанию с индекса.

Sant пишет:

В таблице Z#PR_CRED около 69545 записи, в z#folder_pay 490478.

Ниже план запроса:
Код:
SQL> explain plan for
  2 
  2  select (select case
  3                   when count(1) > 0 then
  4                    '***'
  5                   else
  6                    '...'
  7                 end
  8            from ibs.z#folder_pay fp
  9           where fp.c_prod_fold = to_char(a1_1.id)
 10             and rownum < 2) papka,a1_1.*
 11    from ibs.z#pr_cred a1_1;
Explained

SQL> select * from table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 769389634
--------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              | 68481 |    19M|   899   (2)| 00:00:
|   1 |  SORT AGGREGATE     |              |     1 |     9 |            |
|*  2 |   COUNT STOPKEY     |              |       |       |            |
|*  3 |    TABLE ACCESS FULL| Z#FOLDER_PAY |     8 |    72 |  1275   (2)| 00:00:
|   4 |  TABLE ACCESS FULL  | Z#PR_CRED    | 68481 |    19M|   899   (2)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<2)
   3 - filter("FP"."C_PROD_FOLD"=TO_CHAR(:B1))
17 rows selected


План, конечно, безобразный. Как сказал Alexsey, нужен индекс по полю C_PROD_FOLD.
Есть дистрибутивный индекс IDX FOLDER_PAY IDX_Z#FOLDER_PAY_PROD, заведён с версии 8.7. Если у вас в метаданных он есть, а на схеме нет, обратитесь к вашим ДБА, а если нет и в метаданных, то к службе поддержки ЦФТ.

Sant пишет:
Можно использовать вместо "rownum < 2" хинт FIRST_ROWS но к сожалению время выполнения запроса не изменяется.

Вы считаете правильным применять хинт, оптимизирующий план запроса для получения первой записи, когда в запросе подсчитываются все записи? Запрос-то от хинта не изменится. Вы считаете такую замену равнозначной?


Последний раз редактировалось: Random (Ср Мар 19, 2014 07:14), всего редактировалось 1 раз
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеСр Мар 19, 2014 07:11    Ответить с цитатой
Полезность: Нет оценки
Sant пишет:
Понятно, а можно что нибудь делать в данной ситуации? или кроме ЦФТ никто не может помочь?
Еще такой вопрос почему у реквизита "C_PROD_FOLD" тип varchar а не number ?


Видимо, для того, чтобы не создавался foreign key и не мешался. Возможно, в этом поле не только числовые идентификаторы хранятся.
Мало ли для чего разработчик задумал это поле.

Что вас смущает, я не пойму?
Alkov
Профи


Вступление в Клуб: 23.09.2010
СообщениеСр Мар 19, 2014 09:56    Ответить с цитатой
Полезность: Нет оценки
Цитата:
select case when count(1)>0 then '***' else

imho правильней не count(1) , а count(id)

p.s. Почему FULLSCAN то по таблице, ,будет индекс - будет скорость
Sant
Участник со стажем


Вступление в Клуб: 19.08.2013
СообщениеСр Мар 19, 2014 13:08    Ответить с цитатой
Полезность: Нет оценки
Random пишет:

А почему он меня должен смущать? Чем varchar2 такой особенный, что он меня смущать должен?
number = varchar2 или varchar2 = number меня бы смутил, так как тут возможно неявное преобразование типов, что ведёт к слетанию с индекса.

Да, в принципе я с Вами согласен.

Random пишет:

План, конечно, безобразный. Как сказал Alexsey, нужен индекс по полю C_PROD_FOLD.
Есть дистрибутивный индекс IDX FOLDER_PAY IDX_Z#FOLDER_PAY_PROD, заведён с версии 8.7. Если у вас в метаданных он есть, а на схеме нет, обратитесь к вашим ДБА, а если нет и в метаданных, то к службе поддержки ЦФТ.

Нет, в метаданных не нашли, попробуем написать в службу поддержки ЦФТ.

Random пишет:

Вы считаете правильным применять хинт, оптимизирующий план запроса для получения первой записи, когда в запросе подсчитываются все записи? Запрос-то от хинта не изменится. Вы считаете такую замену равнозначной?

Нет, так не считаю и в нашем запросе хинт FIRST_ROWS и rownum<2 не равнозначны.
Random пишет:

Видимо, для того, чтобы не создавался foreign key и не мешался. Возможно, в этом поле не только числовые идентификаторы хранятся.
Мало ли для чего разработчик задумал это поле.
Что вас смущает, я не пойму?

Думаю нашу проблему бы решил простой добавлении индекса, но меня не понятно почему разработчики не добавили индекс в ранних версиях, в потом добавили скорее все это их ошибка.
Random
Эксперт


Вступление в Клуб: 27.06.2011
СообщениеСр Мар 19, 2014 13:57    Ответить с цитатой
Полезность: Нет оценки
Alkov пишет:
Цитата:
select case when count(1)>0 then '***' else

imho правильней не count(1) , а count(id)

p.s. Почему FULLSCAN то по таблице, ,будет индекс - будет скорость


Smile count почти по барабану по чему делать. Просто указать 1 во-первых, короче, во-вторых, никак не связано с запросом.

в pl/+ писать count(*) не получится, писать count(a%id) - нужно помнить об этом a, и если меняешь алиас, нужно менять его и в скобках count. Короче, имхо, для целей разработки, удобнее использовать константу.

И ещё. Попробуй посмотреть и сравнить планы запросов:
Код:

select count(1) from z#ac_fin where c_main_v_id like '%'
select count(distinct c_main_v_id) from z#ac_fin where c_main_v_id like '%'
select count(distinct id) from z#ac_fin where c_main_v_id like '%'


Последний раз редактировалось: Random (Ср Мар 19, 2014 14:36), всего редактировалось 3 раз(а)
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle Часовой пояс: GMT + 3
На страницу 1, 2  След.
Страница 1 из 2

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru