Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 долго работает функция  [new]
Пгуые123
Guest
День добрый

Вот нужен совет.
Встал вопрос аудита. Все сделали через dbms_fga.

И вот пришло время разбирать эти запросы. Делаю следующую функцию:
unction get_clients(sql_text in varchar2) return varchar2
as
  Result varchar2(32767);
begin
 if(regexp_instr(sql_text,'.*client_id in \(([0-9\,\ ]{1,})\).*') >0) then
 for x in (select c.name, c.client_id
           from t_client c 
           where c.client_id in (select column_value
                                 from table(str2tbl(regexp_replace(sql_text,'.*client_id in \(([0-9\,\ ]{1,})\).*','\1')))
                                 )
           order by c.name)
 loop
   Result := Result || x.name ||  chr(13); 
 end loop;
 end if;
 return trim(chr(13) from Result);
end;
str2tbl - пайп функция которая принимает строку разделенную запятыми и возвращает табличный тип.

функция в принципе работает, но вот на 500 записей думает 400 секунд. Причем, только 1/5 часть записей имеют нужную подстроку.

Что можно с этим сделать?
Можно ли тоже самое перефигачить без использования функции? у меня получилось только используя select cursor() from
9 июн 10, 09:37    [8912754]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Elic
Member

Откуда:
Сообщений: 29979
Пгуые123
from t_client
План уже изучен?
9 июн 10, 09:42    [8912777]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
выбрал самую длинную строку.
Тут посмотрел, что regexp_replace сама по себе не быстрая...
может стоит все-таки использовать простой replace?
select * from t_client where client_id in (select column_value from table(str2tbl('56586, 54427, 55920, 53033, 50800, 50554, 50555, 50550, 50549, 55266, 50548, 52381, 54197, 53211, 53528, 50634, 50552, 53208, 56484, 50632, 50551, 50556, 50126, 53793, 50633, 50909, 53210, 50631, 50558, 50557, 50553, 50907, 53209, 50908, 53531, 50616, 56198, 56274, 56276, 56275, 53578, 53214, 53174, 53855, 56970, 50615, 50614, 56626, 50613, 50559, 56181, 53977')))

Execution Plan
-------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |   255 | 74460 |  1281 |
|   1 |  NESTED LOOPS                       |               |   255 | 74460 |  1281 |
|   2 |   SORT UNIQUE                       |               |       |       |    |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL       |       |       |    |
|   4 |   TABLE ACCESS BY INDEX ROWID       | T_CLIENT      |     1 |   290 |  1 |
|   5 |    INDEX UNIQUE SCAN                | IDX_CLIENT_PK |     1 |       |  0 |
-------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
         33  recursive calls
          0  db block gets
        158  consistent gets
          0  physical reads
          0  redo size
      13512  bytes sent via SQL*Net to client
        406  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         52  rows processed


9 июн 10, 10:05    [8912897]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Пгуые123
select * from t_client where client_id in (select column_value from table(str2tbl('56586, 54427, 55920, 53033, 50800, 50554, 50555, 50550, 50549, 55266, 50548, 52381, 54197, 53211, 53528, 50634, 50552, 53208, 56484, 50632, 50551, 50556, 50126, 53793, 50633, 50909, 53210, 50631, 50558, 50557, 50553, 50907, 53209, 50908, 53531, 50616, 56198, 56274, 56276, 56275, 53578, 53214, 53174, 53855, 56970, 50615, 50614, 56626, 50613, 50559, 56181, 53977')))
Можно реальный план реального запроса?
9 июн 10, 10:46    [8913231]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
JaRo,
это реальный план реального запроса.
SQL> set autotrace trace
SQL> select * from t_client where client_id in (select column_value from table(s
tr2tbl('56586, 54427, 55920, 53033, 50800, 50554, 50555, 50550, 50549, 55266, 50
548, 52381, 54197, 53211, 53528, 50634, 50552, 53208, 56484, 50632, 50551, 50556
, 50126, 53793, 50633, 50909, 53210, 50631, 50558, 50557, 50553, 50907, 53209, 5
0908, 53531, 50616, 56198, 56274, 56276, 56275, 53578, 53214, 53174, 53855, 5697
0, 50615, 50614, 56626, 50613, 50559, 56181, 53977')));

52 rows selected.


Execution Plan
----------------------------------------------------------

-------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |   255 | 74460 |  1281 |
|   1 |  NESTED LOOPS                       |               |   255 | 74460 |  1281 |
|   2 |   SORT UNIQUE                       |               |       |       |    |
|   3 |    COLLECTION ITERATOR PICKLER FETCH| STR2TBL       |       |       |    |
|   4 |   TABLE ACCESS BY INDEX ROWID       | T_CLIENT      |     1 |   290 |  1 |
|   5 |    INDEX UNIQUE SCAN                | IDX_CLIENT_PK |     1 |       |  0 |
-------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        158  consistent gets
          0  physical reads
          0  redo size
      13512  bytes sent via SQL*Net to client
        406  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
         52  rows processed

SQL>

что вас смутило?
9 июн 10, 10:53    [8913298]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
JaRo
Member

Откуда:
Сообщений: 1659
Пгуые123
JaRo,
это реальный план реального запроса.
что вас смутило?
1) То, что он не такой, о каком говорили изначально. Запрос в таком виде тормозит?
2) Хочется увидеть план, снятый именно в том окружении, в котором работает тормозящий запрос (например, у Вас он в хр. процедуре, что очень влияет на выполнение)

ЗЫ: А причем тут fga?
9 июн 10, 11:23    [8913649]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
JaRo,

с планом понял. сейчас сделаю.
fga практически не причем, необходим разбор сохраненного им запроса для построения отчета
9 июн 10, 11:26    [8913682]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
JaRo,

не уверен, что поможет, но вот план
SQL> select sqlps.get_clients(sql_text) from dba_fga_audit_trail;

210 rows selected.

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  1542 |   131K|  2363 |
|   1 |  TABLE ACCESS FULL| FGA_LOG$ |  1542 |   131K|  2363 |
--------------------------------------------------------------
Statistics
----------------------------------------------------------
       1963  recursive calls
       2694  db block gets
       4703  consistent gets
          8  physical reads
          0  redo size
      21289  bytes sent via SQL*Net to client
        527  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
        239  sorts (memory)
          0  sorts (disk)
        210  rows processed
9 июн 10, 11:45    [8913911]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
ладно...
переделал все с regexp_replace на substr...

скорость выполнения стала приемлемая: 1,5 сек все те же строки.

пошел переделывать все остальное...
9 июн 10, 11:52    [8914018]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
Пгуые123
выбрал самую длинную строку.
Тут посмотрел, что regexp_replace сама по себе не быстрая...
может стоит все-таки использовать простой replace?
..

кстати! не regexp_replace не быстрая, а маски у вас садистские.
1) нет никакой необходимости, чтобы упоминать в regexp_instr ни одной из звездей.
2) regexp_replace замените на regexp_substr, опять же - избавив маску от того же звездастого зла
9 июн 10, 12:09    [8914256]     Ответить | Цитировать Сообщить модератору
 Re: долго работает функция  [new]
Пгуые123
Guest
orawish,

Спасибо за советы!
Обязательно сейчас попробую!
9 июн 10, 14:21    [8915805]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить