ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / FBI, непонятка с планом.
14 сообщений из 14, страница 1 из 1
FBI, непонятка с планом.
    #39325634
favt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
favt
Участник
Доброго всем.
Суть задачи: есть таблица, в которой хранятся логи входа в систему.
В ней есть строки вида:"Регистрация пользователя %USER% в системе".
Таблица в одной из прод.базе занимает 5Гб. (это минимум, на других больше)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SQL> select bytes from dba_segments where segment_name='EVENTCOMMIT';
     BYTES                                                                      
----------                                                                      
4847566848                                                                      
SQL> desc eventcommit;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EVENTID                                   NOT NULL NUMBER(15)
 EVENTMSEC                                          NUMBER
 EVENTTIME                                 NOT NULL DATE
 EVENTSESSION                              NOT NULL NUMBER(15)
 EVENTTYPE                                 NOT NULL NUMBER(15)
 LABEL                                     NOT NULL VARCHAR2(254)
 DESCRIPTION                                        VARCHAR2(2000)


"Нужных" индексов на таблице нет.
Помимо регистрации пользователя - есть и другие события в таблице, но их меньше примерно в 2 раза (65% - Регистрации пользователя / 35% прочее). При этом есть еще служебные пользователи, их тоже большинство.
Решил создать FBI (откинем прочие события и служебных пользователей)
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SQL> create or replace function x_eventtype_func (eventtype in number, label in varchar2) return varchar2 deterministic
  2  as
  3  begin
  4  if eventtype=10015 --это событие Регистрации пользователя
  5    and substr(label,37,instr(label,' ',1,4)-instr(label,' ',1,3)-1) not in ('SUPER','DAEMON') then
  6  return substr(label,37,instr(label,' ',1,4)-instr(label,' ',1,3)-1); -- из строки вытаскиваем Пользователя!
  7  else
  8  return null;
  9  end if;
 10  end;
 11  /
Function created.

SQL> create index ix_eventcommit_et1 on eventcommit (substr(x_eventtype_func(eventtype,label),1,254)) online
  2  /
Index created.
SQL> begin
  2  dbms_stats.gather_table_stats('OD','EVENTCOMMIT',cascade => true);
  3  end;
  4  /
PL/SQL procedure successfully completed.
Elapsed: 00:21:37.51



Теперь если мы выбираем по FBI должно выбираться по индексу, НО: если я выбираю само значение функции. то все в порядке:
Код: 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.
SQL> select substr(x_eventtype_func(eventtype,label),1,254)                                     
  2  from eventcommit                                                                           
  3  where substr(x_eventtype_func(eventtype,label),1,254) is not null                          
  4  and rownum<6;                                                                              
                                                                                                
SUBSTR(X_EVENTTYPE_FUNC(EVENTTYPE,LABEL),1,254)                                                 
------------------------------------------------------------------------------------------------
AACHIBURKINA                                                                                    
AACHIBURKINA                                                                                    
AACHIBURKINA                                                                                    
AACHIBURKINA                                                                                    
AACHIBURKINA                                                                                    
                                                                                                
Elapsed: 00:00:00.13                                                                            
                                                                                                
Execution Plan                                                                                  
----------------------------------------------------------                                      
Plan hash value: 2723101260                                                                     
                                                                                                
---------------------------------------------------------------------------------------         
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |         
---------------------------------------------------------------------------------------         
|   0 | SELECT STATEMENT |                    |     5 |    10 |     1   (0)| 00:00:01 |         
|*  1 |  COUNT STOPKEY   |                    |       |       |            |          |         
|*  2 |   INDEX FULL SCAN| IX_EVENTCOMMIT_ET1 |     5 |    10 |     1   (0)| 00:00:01 |         
---------------------------------------------------------------------------------------         
                                                                                                
Predicate Information (identified by operation id):                                             
---------------------------------------------------                                             
                                                                                                
   1 - filter(ROWNUM<6)                                                                         
   2 - filter(SUBSTR("OD"."X_EVENTTYPE_FUNC"("EVENTTYPE","LABEL"),1,254) IS                     
              NOT NULL)                                                                         
                                                                                                
                                                                                                
Statistics                                                                                      
----------------------------------------------------------                                      
         28  recursive calls                                                                    
          0  db block gets                                                                      
          8  consistent gets                                                                    
          0  physical reads                                                                     
          0  redo size                                                                          
        296  bytes sent via SQL*Net to client                                                   
        252  bytes received via SQL*Net from client                                             
          2  SQL*Net roundtrips to/from client                                                  
          0  sorts (memory)                                                                     
          0  sorts (disk)                                                                       
          5  rows processed


а если я в выборку добавлю поле из таблицы, то он уже не хочет строить по FBI :
Код: 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.
SQL> select substr(x_eventtype_func(eventtype,label),1,254), label                                    
  2  from eventcommit                                                                                       
  3  where substr(x_eventtype_func(eventtype,label),1,254) is not null                                      
  4  and rownum<6;                                                                                          
                                                                                                            
SUBSTR(X_EVENTTYPE_FUNC(EVENTTYPE,LABEL),1,254)       LABEL                                                 
------------------------------------------------------------------------------------------------------------
F4_USERV1S                                        Регистрация соединения пользователя F4_USERV1S в системе  
DSS4                                              Регистрация соединения пользователя DSS4 в системе        
SIMAKHIN                                          Регистрация соединения пользователя SIMAKHIN в системе    
SIMAKHIN                                          Регистрация соединения пользователя SIMAKHIN в системе    
BAUTDINOV                                         Регистрация соединения пользователя BAUTDINOV в системе   
                                                                                                            
                                                                                                            
Elapsed: 00:00:24.97                                                                                        
                                                                                                            
Execution Plan                                                                                              
----------------------------------------------------------                                                  
Plan hash value: 1199804628                                                                                 
                                                                                                            
----------------------------------------------------------------------------------                          
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                          
----------------------------------------------------------------------------------                          
|   0 | SELECT STATEMENT   |             |     5 |   320 |     2   (0)| 00:00:01 |                          
|*  1 |  COUNT STOPKEY     |             |       |       |            |          |                          
|*  2 |   TABLE ACCESS FULL| EVENTCOMMIT |     5 |   320 |     2   (0)| 00:00:01 |                          
----------------------------------------------------------------------------------                          
                                                                                                            
Predicate Information (identified by operation id):                                                         
---------------------------------------------------                                                         
                                                                                                            
   1 - filter(ROWNUM<6)                                                                                     
   2 - filter(SUBSTR("OD"."X_EVENTTYPE_FUNC"("EVENTTYPE","LABEL"),1,254)                                    
              IS NOT NULL)                                                                                  
                                                                                                            
                                                                                                            
Statistics                                                                                                  
----------------------------------------------------------                                                  
         28  recursive calls                                                                                
          0  db block gets                                                                                  
     107416  consistent gets                                                                                
     107445  physical reads                                                                                 
          0  redo size                                                                                      
        598  bytes sent via SQL*Net to client                                                               
        252  bytes received via SQL*Net from client                                                         
          2  SQL*Net roundtrips to/from client                                                              
          0  sorts (memory)                                                                                 
          0  sorts (disk)                                                                                   
          5  rows processed


Почему так? Я понимаю если бы я менял where условие, но в выборке?!
...
Рейтинг: 0 / 0
12.10.2016, 19:15
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325639
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Я так и не понял, какой же процент строк попал в индекс.
...
Рейтинг: 0 / 0
12.10.2016, 19:27
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325660
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
favt,

Привет, Костя :) а зачем такой индекс без даты-то? Добавь в индекс первым полем дату и в запрос ограничение по дате
...
Рейтинг: 0 / 0
12.10.2016, 20:20
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325753
favt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
favt
Участник
xtender,
Спалился я, похоже.
Так в том то и дело, что ограничивать по дате мне и не надо.Точнее мне надо будет искать: когда пользователь последний раз входил под РМ. (group by description). Да и добавление в индекс поля даты сводит на нет его селективность и размер. Я хотел чтобы он хранил только соединения обычных пользователей.
Elic.
Пока мне сложно судить, постараюсь завтра дождаться выводов. Любой fullscan таблицы выполняется более часа.
По моим "предположительным" подсчетам в индекс должно попасть не более 10% строк от всей таблицы!
...
Рейтинг: 0 / 0
12.10.2016, 23:59
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325801
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Поведение оптимизатора понятно.
Пока нет необходимости лезть в таблицу - он согласен фуллсканить индекс, поскольку его сегмент меньше по размерам.
Но если требуются отсутствующие в индексе поля - то, увы и ах, лазить за ними в таблицу по rowid при том, что проиндексировано до 65% строк - изврат.
Задача поиска "последний раз заходил" в данном случае может быть решена скорее не функциональным, а доменным индексом.
Заодно и на размере можно конкретно выиграть.
Ключевое слово для гугла - ODCIIndex
...
Рейтинг: 0 / 0
13.10.2016, 02:13
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325807
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
favt,

Fbi добавляет столбец в таблицу, оно тебе надо? Не проще ли триггер повесить с when и мерджить в новую таблицу нужное?
...
Рейтинг: 0 / 0
13.10.2016, 03:13
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325808
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
В новой таблице только поля имени пользователя, раб.места и дата, ну и уникальный индекс на пользователя и раб.место
...
Рейтинг: 0 / 0
13.10.2016, 03:15
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325809
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
xtenderFbi добавляет столбец в таблицуОн добавляет псевдо -столбец, реально данные (выражения) хранятся только в индексе
...
Рейтинг: 0 / 0
13.10.2016, 03:34
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325946
favt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
favt
Участник
У меня больше даже вопрос: Почему?
В доках читаю (видать невнимательно?!) что select * from table where FBI(col)=aval должен идти по индексу,
Так почему при добавлении поля в Select (Когда даже ВСЕ столбцы должны) план не хочет подхватывать индекс?
Индекс же содержит rowid нужных записей? Он по ним должен вытаскивать строки таблицы?
И да, если поставить вместо
Код: sql
1.
substr(x_eventtype_func(EVENTTYPE,label),1,254) is not null

Код: sql
1.
substr(x_eventtype_func(EVENTTYPE,label),1,254)=:b1

Тогда в плане уже идет поиск по Index Range Scan
Саян,
Тут триггером даже нагружать и не хочу, мне надо за все время отчет. Так, в целом я могу ограничиться EVENTTIME, на нем есть индекс.
...
Рейтинг: 0 / 0
13.10.2016, 10:00
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39325953
Фотография AlexFF__|
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexFF__|
Участник
favtУ меня больше даже вопрос: Почему?
В доках читаю (видать невнимательно?!) что select * from table where FBI(col)=aval должен идти по индексу...
Никто
Никому
Ничего
Не должен
...
Рейтинг: 0 / 0
13.10.2016, 10:03
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39326098
wurdu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
wurdu
Участник
favtУ меня больше даже вопрос: Почему?
В доках читаю (видать невнимательно?!) что select * from table where FBI(col)=aval должен идти по индексу,
Так почему при добавлении поля в Select (Когда даже ВСЕ столбцы должны) план не хочет подхватывать индекс?
Индекс же содержит rowid нужных записей? Он по ним должен вытаскивать строки таблицы?
И да, если поставить вместо
Код: sql
1.
substr(x_eventtype_func(EVENTTYPE,label),1,254) is not null

Код: sql
1.
substr(x_eventtype_func(EVENTTYPE,label),1,254)=:b1

Тогда в плане уже идет поиск по Index Range Scan
Саян,
Тут триггером даже нагружать и не хочу, мне надо за все время отчет. Так, в целом я могу ограничиться EVENTTIME, на нем есть индекс.Он не должен как минимум потому что это может быть очень неэффективно медленными одноблочными чтениями тащить данные из индекса, а потом медленными одноблочными из таблицы по сравнению с быстрыми многоблочными full scan / fast full scan. В твоем изначальном примере INDEX RANGE SCAN существует только из-за предиката по ROWNUM.
Поэтому надо определиться, либо оптимизировать под ROWNUM, и тогда индекс вполне оправдан, либо не пользовать ROWNUM в тестах вообще.
Если FULLSCAN таблицы на 5GB выполняется больше часа, то желательно понять с чем связана скорость в 1 MB/s. Мы же понимаем, что современные скорости в 100-1000 раз быстрее?
Также надо собирать статистику на hidden column, по-моему она по дефолту не собирается, хотя могу ошибаться.
...
Рейтинг: 0 / 0
13.10.2016, 11:50
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39326135
Фотография AlexFF__|
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AlexFF__|
Участник
wurduТакже надо собирать статистику на hidden column, по-моему она по дефолту не собирается, хотя могу ошибаться.
Собирается.
Тут ТС все верно сделал, создал FBI - обязательно собери статистику таблицы.
...
Рейтинг: 0 / 0
13.10.2016, 12:12
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39326152
ORA__SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORA__SQL
Участник
AlexFF__|wurduТакже надо собирать статистику на hidden column, по-моему она по дефолту не собирается, хотя могу ошибаться.
Собирается.
Тут ТС все верно сделал, создал FBI - обязательно собери статистику таблицы.
Ага) и не забыть про прикол
Код: plsql
1.
dbms_stats.gather_table_stats(method_opt => 'for all hidden columns size 1')
...
Рейтинг: 0 / 0
13.10.2016, 12:27
| Ответить | Цитировать | Написать  
FBI, непонятка с планом.
    #39326388
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
favt,

Триггер будет легче fbi, т.к. Будет вызываться реже - а функция на каждую вставку
...
Рейтинг: 0 / 0
13.10.2016, 15:23
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / FBI, непонятка с планом.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39325809&tid=1887241]:
 0ms
get settings:
 9ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 161ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 69ms
get tp. blocked users:
 1ms
others: 248ms
total:  526ms
созд. / загр.: 526ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]