-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathlocktemprecstats_pkg.sql
137 lines (132 loc) · 6.41 KB
/
locktemprecstats_pkg.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
133
134
135
136
137
REM locktemprecstats_pkg.sql
REM (c)David Kurtz, Go-Faster Consultancy 2009-24
set serveroutput on timi on pages 999 timi on
spool locktemprecstats_pkg
--------------------------------------------------------------------------------------------------------------
-- object definition required by package
--------------------------------------------------------------------------------------------------------------
DROP TYPE SYSADM.t_lock_tab;
DROP TYPE SYSADM.t_lock_row;
CREATE TYPE SYSADM.t_lock_row AS OBJECT
(recname VARCHAR2(18 CHAR)
,rectype INTEGER
,table_name VARCHAR2(30 CHAR)
,last_analyzed DATE
,num_rows INTEGER
,stattype_locked VARCHAR2(5 CHAR)
,lock_stats VARCHAR2(1 CHAR)
)
/
CREATE TYPE t_lock_tab IS TABLE OF SYSADM.t_lock_row
/
--------------------------------------------------------------------------------------------------------------
-- Package Header
--------------------------------------------------------------------------------------------------------------
create or replace package SYSADM.locktemprecstats as
function unlockedtables RETURN t_lock_tab PIPELINED;
procedure locktables;
end locktemprecstats;
/
show errors
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
-- Package Body
--------------------------------------------------------------------------------------------------------------
create or replace package body SYSADM.locktemprecstats as
--------------------------------------------------------------------------------------------------------------
--Constants that should not be changed
--------------------------------------------------------------------------------------------------------------
k_module CONSTANT VARCHAR2(64 CHAR) := $$PLSQL_UNIT; --name of package for instrumentation
--------------------------------------------------------------------------------------------------------------
-- unlockedtables returns query of tables whose statistics should be locked and deleted
--------------------------------------------------------------------------------------------------------------
function unlockedtables RETURN t_lock_tab PIPELINED IS
l_module VARCHAR2(64);
l_action VARCHAR2(64);
BEGIN
dbms_application_info.read_module(l_module, l_action);
dbms_application_info.set_module(k_module, 'unlockedtables');
FOR i IN (
WITH v AS (SELECT rownum row_number FROM dual CONNECT BY LEVEL <= 100)
SELECT /*+LEADING(g r)*/ DISTINCT r.recname, r.rectype, t.table_name, t.last_analyzed, t.num_rows, s.stattype_locked, g.lock_stats
FROM psrecdefn r
, ps_gfc_stats_ovrd g
, user_tables t
LEFT OUTER JOIN user_tab_statistics s ON s.table_name = t.table_name AND s.partition_name IS NULL
WHERE r.rectype = 0
AND g.recname = r.recname
AND t.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)
AND t.temporary = 'N'
AND ( (g.lock_stats = 'Y' AND s.stattype_locked IS NULL) --stats not locked
OR (g.lock_stats = 'N' AND s.stattype_locked IS NOT NULL))
UNION
SELECT /*+LEADING(o r i v)*/ DISTINCT r.recname, r.rectype, t.table_name, t.last_analyzed, t.num_rows, s.stattype_locked, g.lock_stats
FROM psrecdefn r
LEFT OUTER JOIN ps_gfc_stats_ovrd g ON g.recname = r.recname
, pstemptblcntvw i
, psoptions o
, v
, user_tables t
LEFT OUTER JOIN user_tab_statistics s ON s.table_name = t.table_name AND s.partition_name IS NULL
WHERE r.rectype = 7
AND r.recname = i.recname
AND v.row_number <= i.temptblinstances + o.temptblinstances
AND t.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename)||DECODE(v.row_number*r.rectype,100,'',LTRIM(TO_NUMBER(v.row_number)))
AND t.temporary = 'N'
AND ((NVL(g.lock_stats,' ') IN('Y',' ') AND s.stattype_locked IS NULL) --stats not locked
OR (g.lock_stats = 'N' AND s.stattype_locked IS NOT NULL))
ORDER BY 1,3
--FETCH FIRST 100 ROWS ONLY
) LOOP
PIPE ROW(t_lock_row(i.recname, i.rectype, i.table_name, i.last_analyzed, i.num_rows, i.stattype_locked, i.lock_stats));
END LOOP;
dbms_application_info.set_module(l_module, l_action);
RETURN;
END unlockedtables;
--------------------------------------------------------------------------------------------------------------
-- Using the rowset pipeline function above create DB jobs to lock and delete stats
--------------------------------------------------------------------------------------------------------------
PROCEDURE locktables IS
l_sql CLOB;
l_job_name VARCHAR2(30);
l_module VARCHAR2(64);
l_action VARCHAR2(64);
BEGIN
dbms_application_info.read_module(l_module, l_action);
dbms_application_info.set_module(k_module, 'locktables');
FOR i IN (select * from table(locktemprecstats.unlockedtables())) LOOP
dbms_application_info.set_action('locktables.'||i.table_name);
l_sql := '';
l_job_name := '';
IF i.lock_stats = 'N' THEN
l_sql := l_sql || 'dbms_stats.unlock_table_stats(ownname=>'''||user||''',tabname=>'''||i.table_name||');';
l_job_name := l_job_name||'UNLOCK_';
ELSE
IF i.stattype_locked IS NULL THEN --lock stats
l_job_name := l_job_name||'LOCK_';
l_sql := l_sql || 'dbms_stats.lock_table_stats(ownname=>'''||user||''',tabname=>'''||i.table_name||''');';
END IF;
IF i.last_analyzed IS NOT NULL THEN --delete stats
l_job_name := l_job_name||'DELETE_';
l_sql := l_sql || 'dbms_stats.delete_table_stats(ownname=>'''||user||''',tabname=>'''||i.table_name||''',force=>TRUE);';
END IF;
END IF;
IF l_sql IS NOT NULL THEN
--dbms_output.put_line(l_sql);
DBMS_SCHEDULER.create_job(
job_name => l_job_name||i.table_name,
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN '||l_sql||' END;',
start_date => SYSTIMESTAMP,
auto_drop => TRUE,
enabled => TRUE);
END IF;
END LOOP;
dbms_application_info.set_module(l_module, l_action);
END locktables;
--------------------------------------------------------------------------------------------------------------
end locktemprecstats;
/
show errors
spool off