Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
Hi ALL!
Есть ORACLE 10.2.0.4
Вопрос по работе табличной функции
Создана простейшая функция:
1. /CREATE OR REPLACE TYPE RBPAYMENT.TESTPAYDOCROW AS OBJECT
(ref_no NVARCHAR2(16));
/
2. CREATE OR REPLACE TYPE RBPAYMENT.TESTPAYDOC AS TABLE OF TESTPAYDOCROW;
/

3. CREATE OR REPLACE FUNCTION RBPAYMENT.Test1Getlockeddocument (
p_refno IN NVARCHAR2 /* Referent number */
)
RETURN testpaydoc PIPELINED AS

BEGIN
FOR r IN (select REF_NO FROM IS_PAYDOC_RB WHERE ref_no=p_refno ) LOOP
PIPE ROW ( testpaydocrow( r.ref_no) );
END LOOP;
RETURN;
END;
/

Почему она работает намного медленнее , по сравнению с select , на базе которого создана?
Трассировка приводится ниже.В случае функции индекс по полю ref_no похоже не используется.
Можно заставить ее отрабатывать быстрее?

select * from table(rbpayment.test1getlockeddocument('IV1011231760073'))

Fetch 2 0.3500s 0.3708s 0 1,656 0 1
Total 0 4 0.3500s 0.3709s 0 1,656 0 1

1 COLLECTION ITERATOR PICKLER FETCH TEST1GETLOCKEDDOCUMENT (cr=1,656 pr=0 pw=0 time=0.3484s)

select * from rbpayment.is_paydoc_rb where ref_no='IV1011231760073'

Fetch 2 0.0000s 0.0002s 0 4 0 1
Total 0 4 0.0000s 0.0004s 0 4 0 1

1
1 TABLE ACCESS BY INDEX ROWID IS_PAYDOC_RB (cr=4 pr=0 pw=0 time=0.0001s)
INDEX UNIQUE SCAN IS_PAYDOC_RB_U01 (cr=3 pr=0 pw=0 time=0.0000s)
6 дек 10, 14:16    [9892621]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Какой тип поля ref_no в таблице?
6 дек 10, 14:29    [9892744]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Версия oracle? И покажите план для
select REF_NO FROM from rbpayment.is_paydoc_rb where ref_no = :1
6 дек 10, 14:32    [9892761]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
tirleo,

ну, начнём с того, что для каких-либо выводов данных вы привели слишком мало.
что угодно это может быть. (например, любое кеширование)
6 дек 10, 14:38    [9892821]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,

NVARCHAR2(16));
6 дек 10, 14:56    [9892994]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
_Nikotin,

ORACLE 10.2.0.4

SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | 956 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| IS_PAYDOC_RB | 1 | 956 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | IS_PAYDOC_RB_U01 | 1 | | 2 (0)|
6 дек 10, 14:58    [9893011]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Может быть debug ? Попробуйте после
alter function Test1Getlockeddocument compile;
6 дек 10, 15:01    [9893052]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
_Nikotin,

Сделал,скорость работы не изменилась.
Еще 3 другими способами создавал табличную функцию
и получал практически тот же результат.
6 дек 10, 15:13    [9893137]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
попробуйте с хинтом
FOR r IN (select /*+ INDEX(IS_PAYDOC_RB IS_PAYDOC_RB_U01) */ REF_NO FROM IS_PAYDOC_RB WHERE ref_no=p_refno ) LOOP
6 дек 10, 15:17    [9893184]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Если выаолнить в SQL Plus следующий код, индекс используется?
var p_refno NVARCHAR2(16)
exec :p_refno := 'IV1011231760073';
set autotrace traceonly
select * from rbpayment.is_paydoc_rb where ref_no = :p_refno;
6 дек 10, 15:29    [9893278]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
_Nikotin
select REF_NO FROM from rbpayment.is_paydoc_rb where ref_no = :1


tirleo
SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | 956 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| IS_PAYDOC_RB | 1 | 956 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | IS_PAYDOC_RB_U01 | 1 | | 2 (0)|


Кто-то что-то не договаривает, зачем здесь TABLE ACCESS BY INDEX ROWID ?
6 дек 10, 15:42    [9893380]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
_Nikotin,

Попробовал,быстрее не стало
6 дек 10, 15:43    [9893396]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
_Nikotin,


Попробовал.быстрее не стало
6 дек 10, 15:44    [9893404]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,

Это запрос по рабочей таблице IS_PAYDOC_RB с 81 полем
var p_refno NVARCHAR2(16)
exec :p_refno := 'IV1011231760073';
set autotrace traceonly
select * from rbpayment.is_paydoc_rb where ref_no = :p_refno;

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

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 956 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| IS_PAYDOC_RB | 1 | 956 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | IS_PAYDOC_RB_U01 | 1 | | 2 (0)|
-------------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
2224 recursive calls
0 db block gets
67019 consistent gets
3 physical reads
0 redo size
5131 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Это запрос по тестовой таблице IS_PAYDOC_RB_TEST с 1 полем ref_no
var p_refno NVARCHAR2(16)
exec :p_refno := 'IV1011231760073';
set autotrace traceonly
select * from rbpayment.is_paydoc_rb_test where ref_no = :p_refno;

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

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| IS_PAYDOC_RB_TEST | 1 | 28 | 3 (0)|
| 2 | INDEX UNIQUE SCAN | IS_PAYDOC_RB_TEST_INDEX | 1 | | 2 (0)|
--------------------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
64 recursive calls
2 db block gets
1729 consistent gets
0 physical reads
0 redo size
281 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
6 дек 10, 15:59    [9893524]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Раз уже снималась трассировка, в ней кроме запроса с табличной функцией должен быть и запрос к rbpayment.is_paydoc_rb с планом. Если нет плана - закрываем сессию. Как то так:
alter session set events = '10046 trace name context forever, level 8';

Session altered.

select * from table(rbpayment.test1getlockeddocument('IV1011231760073'));


exit
Смотим реальный план, чтоб не гадать, используется или нет индекс.
6 дек 10, 16:16    [9893623]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,
День добрый !

В такой записи индекс не используется
1 TABLE ACCESS FULL IS_PAYDOC_RB (cr=66,694 pr=3,236 pw=0 time=2.3360s)
7 дек 10, 10:22    [9896744]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
tirleo
wurdu,
День добрый !

В такой записи индекс не используется
1 TABLE ACCESS FULL IS_PAYDOC_RB (cr=66,694 pr=3,236 pw=0 time=2.3360s)
Интересно взглянуть на план запроса из shared pool. Из сырого трэйс-файла в строчке PARSING IN CURSOR для этого запроса взять hv=..., по этому значению в v$sql найти запрос и по sql_id посмотреть план

select sql_id from v$sql where hash_value = ...;

select * from table(dbms_xplan.display_cursor('f9vmjnkcj4791', null, 'allstats'));
7 дек 10, 10:45    [9896946]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
Вместо 'f9vmjnkcj4791' поставить свой sql_id
7 дек 10, 11:08    [9897147]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,

var p_refno NVARCHAR2(16)
exec :p_refno := 'IV1011231760073';
set autotrace traceonly
select * from rbpayment.is_paydoc_rb where ref_no = :p_refno;

Ваш совет выдал такой результат:
|* 1 | TABLE ACCESS FULL| IS_PAYDOC_RB | 1 | 3976 | 1 |00:00:02.21 | 66694 |
1 - filter(SYS_OP_C2C("REF_NO")=:P_REFNO)

Похоже при параметрическом запросе не используется индекс и
это проявляется в табличной функции
7 дек 10, 11:46    [9897466]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
tirleo
wurdu,

var p_refno NVARCHAR2(16)
exec :p_refno := 'IV1011231760073';
set autotrace traceonly
select * from rbpayment.is_paydoc_rb where ref_no = :p_refno;

Ваш совет выдал такой результат:
|* 1 | TABLE ACCESS FULL| IS_PAYDOC_RB | 1 | 3976 | 1 |00:00:02.21 | 66694 |
1 - filter(SYS_OP_C2C("REF_NO")=:P_REFNO)

Похоже при параметрическом запросе не используется индекс и
это проявляется в табличной функции
Мы уже тестировали запрос с параметром и он работает. fullscan из-за SYS_OP_C2C, это конвертация одного character set в другой character set. Например из varchar2 в nvarchar2. Поэтому я в первом посте спросил про тип поля. Ты уверяешь что в функции NVARCHAR2. Это 100%? Можно снять trace 10046 с level 12 (с bind переменными), и в сыром трэйсе посмотреть на BINDS # для этого запроса. Там будет dty=..., номер типа, интересно взглянуть на этот номер.
7 дек 10, 12:03    [9897619]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
wurdu
Вместо 'f9vmjnkcj4791' поставить свой sql_id

или буквочку перед кавычкой в литерале

N'ляляля'
7 дек 10, 12:13    [9897719]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
tirleo,

между прочим - попробуйте в запросе не параметр использовать, а локальную переменную
n-типа инициализированную значением параметра
7 дек 10, 12:21    [9897799]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,


Странно , но трассировка показала

select * from rbpayment.is_paydoc_rb where ref_no = :p_refno
p_refno VARCHAR2 ""

BEGIN :p_refno := 'IV1011231760073'; END;
p_refno VARCHAR2 NULL

То есть вместо NVARCHA2 используется тип VARCHAR2
7 дек 10, 12:30    [9897876]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
wurdu
Member

Откуда: Владивосток
Сообщений: 4441
tirleo
wurdu,


Странно , но трассировка показала

select * from rbpayment.is_paydoc_rb where ref_no = :p_refno
p_refno VARCHAR2 ""

BEGIN :p_refno := 'IV1011231760073'; END;
p_refno VARCHAR2 NULL

То есть вместо NVARCHA2 используется тип VARCHAR2
Что это за трассировка? Trace 10046 имеет другой формат.
7 дек 10, 12:47    [9898021]     Ответить | Цитировать Сообщить модератору
 Re: Медленная работа табличной функции  [new]
tirleo
Member

Откуда:
Сообщений: 35
wurdu,

Проблема заключалась по видимому в том ,что
ORACLE в параметрическом запросе вместо NVARCHAR2 использует тип VARCHAR2
и в результате не воспринимает значение для поиска и индекс не используется.
После смены в таблице типа ref_no VARCHAR2,все начало работать,в том
числе и быстро заработала табличная функция

Большое спасибо за помощь
7 дек 10, 13:14    [9898267]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить