Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / FBI, непонятка с планом. / 14 сообщений из 14, страница 1 из 1
12.10.2016, 19:15
    #39325634
favt
favt
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
Доброго всем.
Суть задачи: есть таблица, в которой хранятся логи входа в систему.
В ней есть строки вида:"Регистрация пользователя %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:27
    #39325639
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
Я так и не понял, какой же процент строк попал в индекс.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.10.2016, 20:20
    #39325660
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
favt,

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

Fbi добавляет столбец в таблицу, оно тебе надо? Не проще ли триггер повесить с when и мерджить в новую таблицу нужное?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 03:15
    #39325808
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
В новой таблице только поля имени пользователя, раб.места и дата, ну и уникальный индекс на пользователя и раб.место
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 03:34
    #39325809
Вячеслав Любомудров
Вячеслав Любомудров
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
xtenderFbi добавляет столбец в таблицуОн добавляет псевдо -столбец, реально данные (выражения) хранятся только в индексе
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 10:00
    #39325946
favt
favt
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
У меня больше даже вопрос: Почему?
В доках читаю (видать невнимательно?!) что 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:03
    #39325953
AlexFF__|
AlexFF__|
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
favtУ меня больше даже вопрос: Почему?
В доках читаю (видать невнимательно?!) что select * from table where FBI(col)=aval должен идти по индексу...
Никто
Никому
Ничего
Не должен
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 11:50
    #39326098
wurdu
wurdu
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
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, 12:12
    #39326135
AlexFF__|
AlexFF__|
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
wurduТакже надо собирать статистику на hidden column, по-моему она по дефолту не собирается, хотя могу ошибаться.
Собирается.
Тут ТС все верно сделал, создал FBI - обязательно собери статистику таблицы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2016, 12:27
    #39326152
ORA__SQL
ORA__SQL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
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, 15:23
    #39326388
Sayan Malakshinov
Sayan Malakshinov
Модератор форума
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
FBI, непонятка с планом.
favt,

Триггер будет легче fbi, т.к. Будет вызываться реже - а функция на каждую вставку
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / FBI, непонятка с планом. / 14 сообщений из 14, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 514ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887241]:
 0ms
get settings:
 11ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 167ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 255ms
total:  514ms
x
x
Закрыть


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