-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathget_lob_compression_d_invoice_docs.sql
207 lines (188 loc) · 7.46 KB
/
get_lob_compression_d_invoice_docs.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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
SET LINES 400 PAGES 1000
set serveroutput on
/*
BLOB SIZE Histogram
SELECT size_range, COUNT(*) AS num_rows
FROM (
SELECT
CASE
WHEN LENGTH(BLOB_DATA) = 0 THEN '0KB'
WHEN LENGTH(BLOB_DATA) > 0 AND LENGTH(BLOB_DATA) <= 16556 THEN '1KB-16KB'
WHEN LENGTH(BLOB_DATA) > 16556 AND LENGTH(BLOB_DATA) <= 32768 THEN '16KB-32KB'
WHEN LENGTH(BLOB_DATA) > 32768 AND LENGTH(BLOB_DATA) <= 65536 THEN '32KB-64KB'
WHEN LENGTH(BLOB_DATA) > 65536 AND LENGTH(BLOB_DATA) <= 131072 THEN '64KB-128KB'
WHEN LENGTH(BLOB_DATA) > 131072 AND LENGTH(BLOB_DATA) <= 1048576 THEN '128KB-1MB'
ELSE 'Greater than 1MB'
END AS size_range
FROM AAX2DMSSW.D_INVOICE_DOCS SUBPARTITION (SP_SF_20210824)
)
GROUP BY size_range
ORDER BY
CASE size_range
WHEN '0KB' THEN 1
WHEN '1-1KB' THEN 2
WHEN '16KB-32KB' THEN 3
WHEN '32KB-64KB' THEN 4
WHEN '64KB-128KB' THEN 5
WHEN '128KB-1MB' THEN 6
ELSE 7
END;
*/
DECLARE
l_blkcnt_cmp PLS_INTEGER;
l_blkcnt_uncmp PLS_INTEGER;
l_row_cmp PLS_INTEGER;
l_lobcnt PLS_INTEGER;
l_cmp_ratio NUMBER;
l_comptype_str VARCHAR2(32767);
l_scratchtbsname varchar2(256) := 'USERS';
l_tabowner varchar2(256) := 'AAX2DMSSW';
l_tabname varchar2(256) := 'D_INVOICE_DOCS';
l_lobname varchar2(256) := 'BLOB_DATA';
l_numbers CONSTANT SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(
DBMS_COMPRESSION.COMP_LOB_LOW,
DBMS_COMPRESSION.COMP_LOB_MEDIUM,
DBMS_COMPRESSION.COMP_LOB_HIGH
);
/* To avoid: ORA-20000: Compression Advisor sample size must be at least 0.1 percent of the total lobs */
l_sample_size PLS_INTEGER;
l_subpartition_name VARCHAR2(256);
BEGIN
--there are 24 different document types, each stored in their own partition, and sub-partitioned by date range
/*
PARTITION_NAME
------------------------------
P_INV_DOC_APPBRS
P_INV_DOC_APPFLN
P_INV_DOC_APPSFC
P_INV_DOC_CPO
P_INV_DOC_DTCSV
P_INV_DOC_DTS
P_INV_DOC_DTSCUST
P_INV_DOC_EFNBAG
P_INV_DOC_EFNDE
P_INV_DOC_FIBRS
P_INV_DOC_FIFLN
P_INV_DOC_FILHT
P_INV_DOC_FISFC
P_INV_DOC_MABAG
P_INV_DOC_MADE
P_INV_DOC_RS
P_INV_DOC_SB
P_INV_DOC_SF
P_INV_DOC_SW
P_INV_DOC_TDPOL
P_INV_DOC_TFCSV
P_INV_DOC_TSPOL
P_INV_DOC_VSLHT
P_INV_DOC_WDCSV
24 rows selected.
*/
FOR x IN (
SELECT DISTINCT partition_name
FROM dba_tab_partitions
WHERE TABLE_OWNER = l_tabowner
AND table_name = l_tabname
AND REGEXP_LIKE(partition_name,'FIBRS|FIFLN|FISFC|DOC_RS|DOC_SB|DOC_SF|DOC_SW')
ORDER BY 1
) LOOP
BEGIN
/* get the largest subpartition for the give partition_name */
SELECT partition_name INTO l_subpartition_name
FROM (
SELECT a.owner,
b.table_name,
a.partition_name,
a.tablespace_name,
c.DEF_TAB_COMPRESSION,
c.COMPRESS_FOR,
c.BIGFILE,
c.STATUS,
a.segment_name,
a.segment_type,
ROW_NUMBER() OVER (PARTITION BY b.table_name ORDER BY SUM(a.bytes) / (1024 * 1024 * 1024) DESC) AS rn
FROM dba_segments a, dba_tab_subpartitions b, dba_tablespaces c
WHERE a.segment_type = 'TABLE SUBPARTITION'
AND c.tablespace_name = a.tablespace_name
AND a.owner = b.table_owner
AND b.table_name = l_tabname
AND a.segment_name = b.table_name
AND a.partition_name = b.subpartition_name
AND b.PARTITION_NAME = x.partition_name /* limit query of range based subpartitions to the given doc type partition */
and a.owner IN (SELECT username FROM dba_users where oracle_maintained = 'N')
GROUP BY a.owner, b.table_name, a.partition_name, a.tablespace_name, c.DEF_TAB_COMPRESSION, c.COMPRESS_FOR, c.BIGFILE, c.STATUS, a.segment_name, a.segment_type
)
WHERE rn = 1;
EXCEPTION
WHEN OTHERS THEN
-- Handling exceptions
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'Unable to find subpartition for object = ' || l_tabowner || '.' || l_tabname || '.' || x.partition_name ||'.' || l_lobname);
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Message: ' || SQLERRM);
CONTINUE; /* skip it */
END;
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'Object = ' || l_tabowner || '.' || l_tabname || '.' || x.partition_name ||'.' || l_subpartition_name ||'.' || l_lobname);
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(1)*0.11 FROM '||l_tabowner||'.'||l_tabname||' SUBPARTITION ('||l_subpartition_name||') WHERE '||l_lobname||' IS NOT NULL'
INTO l_sample_size;
EXCEPTION
WHEN OTHERS THEN
-- Handling exceptions
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Message: ' || SQLERRM);
END;
SELECT GREATEST(NVL(l_sample_size,0), DBMS_COMPRESSION.COMP_RATIO_LOB_MAXROWS) INTO l_sample_size
FROM DUAL;
FOR i IN 1..l_numbers.COUNT LOOP
BEGIN
-- Loop through different compression types
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => l_scratchtbsname,
tabowner => l_tabowner,
tabname => l_tabname,
lobname => l_lobname,
partname => l_subpartition_name,
comptype => l_numbers(i),
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
lobcnt => l_lobcnt,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => l_sample_size
);
EXCEPTION
WHEN OTHERS THEN
/* Handle this SQL Error Message: ORA-20000: Compression Advisor sample size exceeds the total number of non-null lobs: */
DBMS_COMPRESSION.GET_COMPRESSION_RATIO (
scratchtbsname => l_scratchtbsname,
tabowner => l_tabowner,
tabname => l_tabname,
lobname => l_lobname,
partname => l_subpartition_name,
comptype => l_numbers(i),
blkcnt_cmp => l_blkcnt_cmp,
blkcnt_uncmp => l_blkcnt_uncmp,
lobcnt => l_lobcnt,
cmp_ratio => l_cmp_ratio,
comptype_str => l_comptype_str,
subset_numrows => DBMS_COMPRESSION.COMP_RATIO_LOB_MINROWS
);
END;
-- Display compression information for each compression type
DBMS_OUTPUT.PUT_LINE('Compression Type : ' || l_comptype_str);
DBMS_OUTPUT.PUT_LINE('Estimated Compression Ratio of Sample : ' || l_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression Ratio : ' || LTRIM(TO_CHAR(l_blkcnt_uncmp/l_blkcnt_cmp,'999,999,999.00'))||' to 1');
DBMS_OUTPUT.PUT_LINE('Number of blocks used by the compressed sample of the object : ' || l_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Number of blocks used by the uncompressed sample of the object : ' || l_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Number of LOBs actually sampled : ' || l_lobcnt);
EXCEPTION
WHEN OTHERS THEN
-- Handling exceptions
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE(chr(13)||chr(10)||'SQL Error Message: ' || SQLERRM);
END;
END LOOP;
END LOOP;
END;
/