Еще хочу обратить внимание на несколько моментов.
1) Весь функционал из моего предыдущего сообщения
будет работать и на 9iR1. Но очень мало кто о нем знает.
Просто Оракл как-то слабо его пиарит, поэтому все направо и налево используют hint cardinality... лучше б уж юзали opt_estimate. Последний по крайней мере, opt_estimate активно используется в tuning advisor.
Вряд ли Оракл сделает так, что в будущих релизах перестанут работать существующие SQL Profiles.
2) Достаточно
хороший пример по созданию своего картрижда для оценки стоимости:
Power Demand Cartridge ExampleОн же целиком может быть найдет в примерах к базе либо же в и-нете:
extdemo1.sql3) Есть особенность, что
не возникает ошибка, если при привязке указываешь неправильный тип объекта! Например, в моем случае код:
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. |
Надо поисследовать вызовами чего это достигается.
Вроде все, что вспомнил по этой тематике интересное написал.
Спрашивай если что.