forked from utPLSQL/utPLSQL
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathproftab.sql
More file actions
106 lines (102 loc) · 4.59 KB
/
proftab.sql
File metadata and controls
106 lines (102 loc) · 4.59 KB
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
declare
l_tab_exist number;
begin
select count(*) into l_tab_exist from
(select table_name from all_tables where table_name = 'PLSQL_PROFILER_RUNS' and owner = sys_context('USERENV','CURRENT_SCHEMA')
union all
select synonym_name from all_synonyms where synonym_name = 'PLSQL_PROFILER_RUNS' and owner = sys_context('USERENV','CURRENT_SCHEMA'));
if l_tab_exist = 0 then
execute immediate q'[create table plsql_profiler_runs
(
runid number primary key, -- unique run identifier,
-- from plsql_profiler_runnumber
related_run number, -- runid of related run (for client/
-- server correlation)
run_owner varchar2(128), -- user who started run
run_date date, -- start time of run
run_comment varchar2(2047), -- user provided comment for this run
run_total_time number, -- elapsed time for this run
run_system_info varchar2(2047), -- currently unused
run_comment1 varchar2(2047), -- additional comment
spare1 varchar2(256) -- unused
)]';
execute immediate q'[comment on table plsql_profiler_runs is
'Run-specific information for the PL/SQL profiler']';
dbms_output.put_line('PLSQL_PROFILER_RUNS table created');
end if;
end;
/
declare
l_tab_exist number;
begin
select count(*) into l_tab_exist from
(select table_name from all_tables where table_name = 'PLSQL_PROFILER_UNITS' and owner = sys_context('USERENV','CURRENT_SCHEMA')
union all
select synonym_name from all_synonyms where synonym_name = 'PLSQL_PROFILER_UNITS' and owner = sys_context('USERENV','CURRENT_SCHEMA'));
if l_tab_exist = 0 then
execute immediate q'[create table plsql_profiler_units
(
runid number references plsql_profiler_runs,
unit_number number, -- internally generated library unit #
unit_type varchar2(128), -- library unit type
unit_owner varchar2(128), -- library unit owner name
unit_name varchar2(128), -- library unit name
-- timestamp on library unit, can be used to detect changes to
-- unit between runs
unit_timestamp date,
total_time number DEFAULT 0 NOT NULL,
spare1 number, -- unused
spare2 number, -- unused
--
primary key (runid, unit_number)
)]';
execute immediate q'[comment on table plsql_profiler_units is
'Information about each library unit in a run']';
dbms_output.put_line('PLSQL_PROFILER_UNITS table created');
end if;
end;
/
declare
l_tab_exist number;
begin
select count(*) into l_tab_exist from
(select table_name from all_tables where table_name = 'PLSQL_PROFILER_DATA' and owner = sys_context('USERENV','CURRENT_SCHEMA')
union all
select synonym_name from all_synonyms where synonym_name = 'PLSQL_PROFILER_DATA' and owner = sys_context('USERENV','CURRENT_SCHEMA'));
if l_tab_exist = 0 then
execute immediate q'[create table plsql_profiler_data
(
runid number, -- unique (generated) run identifier
unit_number number, -- internally generated library unit #
line# number not null, -- line number in unit
total_occur number, -- number of times line was executed
total_time number, -- total time spent executing line
min_time number, -- minimum execution time for this line
max_time number, -- maximum execution time for this line
spare1 number, -- unused
spare2 number, -- unused
spare3 number, -- unused
spare4 number, -- unused
--
primary key (runid, unit_number, line#),
foreign key (runid, unit_number) references plsql_profiler_units
)]';
execute immediate q'[comment on table plsql_profiler_data is
'Accumulated data from all profiler runs']';
dbms_output.put_line('PLSQL_PROFILER_DATA table created');
end if;
end;
/
declare
l_seq_exist number;
begin
select count(*) into l_seq_exist from
(select sequence_name from all_sequences where sequence_name = 'PLSQL_PROFILER_RUNNUMBER' and sequence_owner = sys_context('USERENV','CURRENT_SCHEMA')
union all
select synonym_name from all_synonyms where synonym_name = 'PLSQL_PROFILER_RUNNUMBER' and owner = sys_context('USERENV','CURRENT_SCHEMA'));
if l_seq_exist = 0 then
execute immediate q'[create sequence plsql_profiler_runnumber start with 1 nocache]';
dbms_output.put_line('Sequence PLSQL_PROFILER_RUNNUMBER created');
end if;
end;
/