-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathxplan_test_ass_stats.sql
132 lines (108 loc) · 4.13 KB
/
xplan_test_ass_stats.sql
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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
--------------------------------------------------------------------------------
-- Author: Alberto Dell'Era
-- Copyright: (c) 2008-2021 Alberto Dell'Era http://www.adellera.it
--------------------------------------------------------------------------------
create or replace type tttt as object (
dummy_attribute number,
static function f (p varchar2) return varchar2
);
/
show errors
create or replace type body tttt as
static function f (p varchar2)
return varchar2
is
begin
return p;
end f;
end;
/
show errors
disassociate statistics from types tttt;
associate statistics with types tttt default selectivity 0.003, default cost (300,30,3);
-- following are dummified from http://www.oracle-developer.net/display.php?id=426#
create or replace type stats_ot as object (
dummy_attribute number,
static function odcigetinterfaces (
p_interfaces out sys.odciobjectlist
) return number,
static function odcistatsselectivity (
p_pred_info in sys.odcipredinfo,
p_selectivity out number,
p_args in sys.odciargdesclist,
p_start in varchar2,
p_stop in varchar2,
p_promo_category in varchar2,
p_env in sys.odcienv
) return number,
static function odcistatsfunctioncost (
p_func_info in sys.odcifuncinfo,
p_cost out sys.odcicost,
p_args in sys.odciargdesclist,
p_promo_category in varchar2,
p_env in sys.odcienv
) return number
);
/
show errors
create or replace type body stats_ot 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 odcistatsselectivity (
p_pred_info in sys.odcipredinfo,
p_selectivity out number,
p_args in sys.odciargdesclist,
p_start in varchar2,
p_stop in varchar2,
p_promo_category in varchar2,
p_env in sys.odcienv
) return number is
begin
p_selectivity := 0.1;
return odciconst.success;
end odcistatsselectivity;
static function odcistatsfunctioncost (
p_func_info in sys.odcifuncinfo,
p_cost out sys.odcicost,
p_args in sys.odciargdesclist,
p_promo_category in varchar2,
p_env in sys.odcienv
) return number is
begin
p_cost := sys.odcicost(null, null, null, null);
p_cost.cpucost := 100;
p_cost.iocost := 10;
p_cost.networkcost := 0;
return odciconst.success;
end odcistatsfunctioncost;
end;
/
show errors
create or replace function plsql_func (p varchar2)
return varchar2
is
begin
return p;
end plsql_func;
/
disassociate statistics from functions plsql_func;
associate statistics with functions plsql_func using stats_ot;
drop table t;
create table t (x varchar2(100));
insert into t (x) values ('giulio cesare');
insert into t (x) values ('marco antonio');
create index t_ind_ctx on t(x) indextype is ctxsys.context;
select * from t where contains( x, 'giulio' ) > 0;
alter system flush shared_pool;
define SQL_TEST="select /*+ xplan_test_marker */ tttt.f(x) from t where plsql_func('X') = 'X' and contains(x, 'giulio') > 0"
declare l_x number := 0; l_y number := 0;
begin /* xplan_exec_marker sga_xplan_exec */ for r in (&SQL_TEST.) loop null; end loop; end;
/
@xplan "%xplan_test_marker%" "plan_stats= last ,access_predicates=Y,lines=150,module=,action=,dbms_xplan=n,plan_details=n,plan_env=y,tabinfos=y"