ассоциация статистики с пакетной pipelined-функцией

bdsm_sql
Дата: 28.03.2011 11:44:54
возможно ли это? или необходимо, чтобы ф-ция была отдельным объектом в базе?

в доке ничего внятного не нашел..
ассоциировал целый пакет с объектом - в итоге объект игнорируется, ни один метод его не вызывается, однако Cardinality в плане становится "1".

---------------------------------------
мистер Петя
dbms_photoshop
Дата: 28.03.2011 18:57:35
bdsm_sql
возможно ли это?
Нет.
bdsm_sql
в доке ничего внятного не нашел..
ASSOCIATE STATISTICS
+
10420685
bdsm_sql
Дата: 28.03.2011 19:55:52
ага, я так и понял уже, поковыряв там и сям.
просто в доке не написано конкретно, что этого нельзя (ткните носом если не прав)
ASSOCIATE STATISTICS работает для пакета, и я думал что может как-то можно заставить работать ODCIStatsTableFunction для каждой ф-ции из пакета (передавать инфу через аргумент типа SYS.ODCIFuncInfo)
и в доке нет ничего по этому поводу..
а в таблице functions и packages вообще соответствуют одинаковым методам (и там и там кстати нет ODCIStatsTableFunction)

короче, ИМХО, бардак

и кстати, наблюдается та же проблема, что и с anydata-pipelined. если повесить статистику на pipelined ф-цию без аргументов, селект из нее валится на ora-7445. приходится добавлять 1 левый.
dbms_photoshop
Дата: 29.03.2011 03:21:04
dbms_photoshop
Нет.
Сорри, меня глюкнуло. Все возможно!
Берешь из статьи "Table Expressions, Cardinality, SYS_OP_ATG and KOKBF$" тип:
create or replace type demo_func_stats_type as object
(
  dummy_attribute number,
  static function odcigetinterfaces(p_interfaces out SYS.odciobjectlist)
    return number,
  static function odcistatstablefunction(p_function in SYS.odcifuncinfo,
                                         p_stats    out SYS.odcitabfuncstats,
                                         p_args     in SYS.odciargdesclist,
                                         p_num_rows in number)
    return number
)
/
create or replace type body demo_func_stats_type as
  static function odcigetinterfaces(p_interfaces out SYS.odciobjectlist)
    return number is
  begin
    p_interfaces := SYS.odciobjectlist(SYS.odciobject('SYS', 'ODCISTATS2'));
    return odciconst.success;
  end odcigetinterfaces;
  static function odcistatstablefunction(p_function in SYS.odcifuncinfo,
                                         p_stats    out SYS.odcitabfuncstats,
                                         p_args     in SYS.odciargdesclist,
                                         p_num_rows in number) return number is
  begin
    dbms_output.put_line(p_function.ObjectName || ' ' ||
                         p_function.MethodName);
    p_stats := SYS.odcitabfuncstats(p_num_rows);
--    raise_application_error(-20002, 'Hello world');
    return odciconst.success;
  end odcistatstablefunction;
end;
/
Единственное что - я в него добавил dbms_output.put_line, чтоб видеть когда функция вызывается.
Создаешь пакет:
create or replace package pkg_card is

  function f1(p in number) return sys.odcinumberlist;

  function f2(p in number) return sys.odcinumberlist
    pipelined;

end pkg_card;
/
create or replace package body pkg_card is

  function f1(p in number) return sys.odcinumberlist is
    result sys.odcinumberlist;
  begin
    select rownum bulk collect into result from dual connect by level <= p;
    return result;
  end;

  function f2(p in number) return sys.odcinumberlist
    pipelined as
  begin
    for i in (select rownum from dual connect by level <= p) loop
      pipe row(i.rownum);
    end loop;
  end;

end pkg_card;
/
Связываешь:
associate statistics with packages pkg_card using demo_func_stats_type;
Проверка:
SQL> explain plan for
  2  select *
  3  from table(pkg_card.f1(3));
PKG_CARD F1

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 738145910

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |     3 |     6 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F1   |       |       |            |          |
------------------------------------------------------------------------------------------

8 rows selected.

SQL>
SQL> explain plan for
  2  select *
  3  from table(pkg_card.f2(7));
PKG_CARD F2

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3211130993

------------------------------------------------------------------------------------------
| Id  | Operation                         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |     7 |    14 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F2   |       |       |            |          |
------------------------------------------------------------------------------------------

8 rows selected.
Обрати внимание, что в output вывелись названия пакета и оцениваемой функции. Если не выводятся - значит метод не вызывается.
Также проверь получает ли параметры, описанные после p_args, то есть параметры оцениваемой функции.
bdsm_sql
однако Cardinality в плане становится "1".
Это признак того, что при вызове метода типа возникает исключение. Оракл исключение незаметно проглатывает и проставляет в этом случае 1. Если в моем примере раскоментируешь raise_application_error - получишь Rows = 1.
dbms_photoshop
Дата: 29.03.2011 05:47:02
Еще хочу обратить внимание на несколько моментов.

1) Весь функционал из моего предыдущего сообщения будет работать и на 9iR1. Но очень мало кто о нем знает.
Просто Оракл как-то слабо его пиарит, поэтому все направо и налево используют hint cardinality... лучше б уж юзали opt_estimate. Последний по крайней мере, opt_estimate активно используется в tuning advisor.
Вряд ли Оракл сделает так, что в будущих релизах перестанут работать существующие SQL Profiles.

2) Достаточно хороший пример по созданию своего картрижда для оценки стоимости: Power Demand Cartridge Example
Он же целиком может быть найдет в примерах к базе либо же в и-нете: extdemo1.sql

3) Есть особенность, что не возникает ошибка, если при привязке указываешь неправильный тип объекта! Например, в моем случае код:
disassociate statistics FROM packages pkg_card;
associate statistics with functions pkg_card using demo_func_stats_type;

explain plan for
select *
from table(pkg_card.f1(5));
select * from table(dbms_xplan.display);

disassociate statistics FROM functions pkg_card;
associate statistics with packages pkg_card using demo_func_stats_type;

explain plan for
select *
from table(pkg_card.f1(5));
select * from table(dbms_xplan.display);
отработает корректно.
Однако в первом случае метод не будет вызван и Rows будет равен 8168. Во-втором все будет ок и Rows = 5.

4) Если возникнет желание написать оберточную функцию, то могут возникнуть проблемы.
То есть, есть к примеру, функция:
create or replace function f_pivot(p in number) return sys.odcinumberlist is
  result sys.odcinumberlist;
begin
  select rownum bulk collect into result from dual connect by level <= p;
  return(result);
end f_pivot;
и обертка
create or replace function f_wrap(p in sys.odcinumberlist)
  return sys.odcinumberlist is
begin
  return p;
end f_wrap;
Допустим есть желание сделать так, чтоб оптимизатор мог оценить кардинальность в случае:
SQL> select * from table(f_wrap(f_pivot(3)));

COLUMN_VALUE
------------
           1
           2
           3
+ пишем тип
create or replace type demo_func_stats_type0 as object
(
  dummy_attribute number,
  static function odcigetinterfaces(p_interfaces out SYS.odciobjectlist)
    return number,
  static function odcistatstablefunction(p_function in SYS.odcifuncinfo,
                                         p_stats    out SYS.odcitabfuncstats,
                                         p_args     in SYS.odciargdesclist,
                                         p_num_rows in sys.odcinumberlist)
    return number
)
/
create or replace type body demo_func_stats_type0 as
  static function odcigetinterfaces(p_interfaces out SYS.odciobjectlist)
    return number is
  begin
    p_interfaces := SYS.odciobjectlist(SYS.odciobject('SYS', 'ODCISTATS2'));
    return odciconst.success;
  end odcigetinterfaces;
  static function odcistatstablefunction(p_function in SYS.odcifuncinfo,
                                         p_stats    out SYS.odcitabfuncstats,
                                         p_args     in SYS.odciargdesclist,
                                         p_num_rows in sys.odcinumberlist)
    return number is
  begin
    dbms_output.put_line(p_function.ObjectName || ' ' ||
                         p_function.MethodName || ' num_rows: ' ||
                         p_num_rows.count);
    p_stats := SYS.odcitabfuncstats(p_num_rows.count);
    return odciconst.success;
  end odcistatstablefunction;
end;
/
Проверка:
SQL> explain plan for
  2  select *
  3  from table(f_wrap(f_pivot(3)));
F_WRAP  num_rows: 0

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 526212252

--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |        |     1 |     2 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F_WRAP |       |       |            |          |
--------------------------------------------------------------------------------------------

8 rows selected.

SQL>
SQL> explain plan for
  2  select *
  3  from table(f_wrap(sys.odcinumberlist(1,10,100)));
F_WRAP  num_rows: 3

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 526212252

--------------------------------------------------------------------------------------------
| Id  | Operation                         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |        |     3 |     6 |    24   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F_WRAP |       |       |            |          |
--------------------------------------------------------------------------------------------

8 rows selected.
Как видно, когда передается не коллекция, а функция возвращающая коллекцию - оптимизатор не может получить число элементов.

5) В 11G dynamic sampling стал таким умным, что умеет оценивать кардинальности коллекций.
Но само задействование этого механизма на продакшене мне совсем не по душе.
Поскольку в 10-ке dynamic sampling порождал рекурсивные sql для оценки кардинальности, которые далеко не всегда были нужны.
В 11-ке для коллекций я глубоко еще не копал в мехинизм работы, но тем не менее
+ эффект есть
SQL> explain plan for
  2  select * from table(f_pivot(3));

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2378332590

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F_PIVOT |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

8 rows selected.

SQL>
SQL> explain plan for
  2  select /*+ dynamic_sampling(2) */* from table(f_pivot(3));

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2378332590

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |     3 |     6 |    11   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| F_PIVOT |     3 |     6 |    11   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

12 rows selected.
Надо поисследовать вызовами чего это достигается.

Вроде все, что вспомнил по этой тематике интересное написал.
Спрашивай если что.
bdsm_sql
Дата: 29.03.2011 08:11:14
о, спасибо огромное, кой-чего действительно не знал
так и предполагал (надеялся на) работу с пакетными ф-циями..

автор
Это признак того, что при вызове метода типа возникает исключение. Оракл исключение незаметно проглатывает и проставляет в этом случае 1. Если в моем примере раскоментируешь raise_application_error - получишь Rows = 1.


вот этого как раз и не хватало. то, что исключениях проглатываются, я заметил, а вот что кардинальность "1" с этим связана, как-то и не доперил :)

еще раз спасибо!
Splain
Дата: 29.03.2011 15:37:34
dbms_photoshop
Еще хочу обратить внимание на несколько моментов.

5) В 11G dynamic sampling стал таким умным, что умеет оценивать кардинальности коллекций.
Но само задействование этого механизма на продакшене мне совсем не по душе.
Поскольку в 10-ке dynamic sampling порождал рекурсивные sql для оценки кардинальности, которые далеко не всегда были нужны.
В 11-ке для коллекций я глубоко еще не копал в мехинизм работы, но тем не менее
Надо поисследовать вызовами чего это достигается.


Трассировка показывает, что в этом случае рекурсивно выполняется запрос вида:

SELECT NVL(SUM(C1),0), NVL(SUM(C2),0) 
FROM (SELECT  1 AS C1, 1 AS C2 FROM TABLE("HR"."GEN_NUMBERS"(100)))

+ полный вариант совместной трассировки 10053 и 10046


*** 2011-03-29 15:05:57.094
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 2).
** Dynamic sampling updated table stats.: blocks=33

*** 2011-03-29 15:05:57.094
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("KOKBF$") FULL("KOKBF$") NO_PARALLEL_INDEX("KOKBF$") */ 1 AS C1, 1 AS C2 FROM TABLE("HR"."GEN_NUMBERS"(100)) "KOKBF$") SAMPLESUB
=====================
PARSING IN CURSOR #6 len=363 dep=1 uid=119 oct=3 lid=0 tim=3995965005888 hv=3757285791 ad='2957c5a0' sqlid='1yhtsrggz78cz'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("KOKBF$") FULL("KOKBF$") NO_PARALLEL_INDEX("KOKBF$") */ 1 AS C1, 1 AS C2 FROM TABLE("HR"."GEN_NUMBERS"(100)) "KOKBF$") SAMPLESUB
END OF STMT
PARSE #6:c=0,e=9150,p=0,cr=49,cu=0,mis=1,r=0,dep=1,og=1,plh=3061000774,tim=3995965005886
EXEC #6:c=0,e=51,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3061000774,tim=3995965006024
FETCH #6:c=0,e=5883,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=3061000774,tim=3995965011940
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)'
STAT #6 id=2 cnt=100 pid=1 pos=1 obj=0 op='COLLECTION ITERATOR PICKLER FETCH GEN_NUMBERS (cr=0 pr=0 pw=0 time=99 us cost=103 size=0 card=8168)'

*** 2011-03-29 15:05:57.109
** Executed dynamic sampling query:
level : 2
sample pct. : 100.000000
actual sample size : 100
filtered sample card. : 100
orig. card. : 8168
block cnt. table stat. : 33
block cnt. for sampling: 33
max. sample block cnt. : 64
sample block cnt. : 33
min. sel. est. : -1.00000000
CLOSE #6:c=0,e=11,dep=1,type=0,tim=3995965012218
** Using dynamic sampling card. : 100
** Dynamic sampling updated table card.
Table: KOKBF$ Alias: KOKBF$
Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00
Access Path: TableScan
Cost: 35.08 Resp: 35.08 Degree: 0
Cost_io: 35.00 Cost_cpu: 250008
Resp_io: 35.00 Resp_cpu: 250008
Best:: AccessPath: TableScan
Cost: 35.08 Degree: 1 Resp: 35.08 Card: 100.00 Bytes: 0

***************************************


Естественно, ни о каком Sample block в случае табличной функции речи идти не может, так что уровни 2-10 оценки идентичны.

На уровне 1 в запросе на оценку селективности появляется фраза

SAMPLE BLOCK (93.939394 , 1) SEED (1)

Что приводит к ошибке на этапе парсинга и, соответственно, берется константное значение селектиности.

Динамических оценок селективности табличных функций не происходит с изменением параметра optimizer_dynamic_sampling

Конечно, двойное выполнение не очень хорошо, но здесь стоит рассчитывать на то, что мы знаем, что делаем, когда пишем в запросе хинт DYNAMIC_SAMPLING