1 package dbms_heat_map AUTHID CURRENT_USER as
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides heatmap information at block/extent/segment
6 -- object and tablespace levels. It contains the definitions for
7 -- processing heatmap for top N objects and tablespaces.
8
9 -- SECURITY
10 --
11 -- The execution privilege is granted to PUBLIC. Procedures in this
12 -- package run under the caller security. The user must have ANALYZE
13 -- privilege on the object.
14
15 type hm_bls_record is record
16 (
17 owner VARCHAR2(128),
18 segment_name VARCHAR2(128),
19 partition_name VARCHAR2(128),
20 tablespace_name VARCHAR2(30),
21 file_id NUMBER,
22 relative_fno NUMBER,
23 block_id NUMBER,
24 writetime date
25 );
26 type hm_bls_row is table of hm_bls_record;
27
28 /*
29 * BLOCK_HEAT_MAP- Table function returns the block level
30 * ILM statistics for a table
31 * segment. It returns no information for segment
32 * types that are not
33 * data. The stat returned today is the latest
34 * modification time of the block.
35 * Input Parameters
36 * owner : Owner of the segment
37 * segment_name : Table name of a non partitioned table or
38 * (sub)partition of partitioned table.
39 * Returns no rows when table name is specified
40 * for a partitioned table.
41 * partition_name : Defaults to NULL. For a partitioned table,
42 * specify the partition or subpartition segment name.
43 * sort_columnid : ID of the column to sort the output on.
44 * Valid values 1..9. Invalid values are ignored.
45 * No errors are raised.
46 * sort_order : Defaults to NULL. Possible values: ASC, DESC
47 *
48 * Output Parameters
49 * owner : owner of the segment
50 * segment_name : segment name of the non partitioned table
51 * partition_name : partition or subpartition name
52 * tablespace_name : tablespace containing the segment
53 * file_id : absolute file number of the block in the segment
54 * relative_fno : relative file number of the block in the segment
55 * block_id : block number of the block
56 * writetime : last modification time of the block
57 *
58 */
59 function BLOCK_HEAT_MAP (owner in varchar2,
60 segment_name in varchar2,
61 partition_name in varchar2 DEFAULT NULL,
62 sort_columnid in number DEFAULT NULL,
63 sort_order in varchar2 DEFAULT NULL
64 ) return hm_bls_row pipelined;
65
66 type hm_els_record is record
67 (
68 owner VARCHAR2(128),
69 segment_name VARCHAR2(128),
70 partition_name VARCHAR2(128),
71 tablespace_name VARCHAR2(30),
72 file_id NUMBER,
73 relative_fno NUMBER,
74 block_id NUMBER,
75 blocks NUMBER,
76 bytes NUMBER,
77 min_writetime date,
78 max_writetime date,
79 avg_writetime date
80 );
81 type hm_els_row is table of hm_els_record;
82
83 /*
84 * EXTENT_HEAT_MAP -Table function returns the extent level ILM
85 * statistics for a table segment. It returns no
86 * information for segment types that are not
87 * data. Aggregates at extent level including minimum
88 * modification time and maximum modification time are
89 * returned
90 * Input Parameters
91 * owner : Owner of the segment
92 * segment_name : Table name of a non partitioned table or
93 * (sub)partition of partitioned table.
94 * Returns no rows when table name is specified for a
95 * partitioned table.
96 * partition_name : Defaults to NULL. For a partitioned table,
97 * specify the partition or subpartition segment name.
98 *
99 * Output Parameters
100 * owner : owner of the segment
101 * segment_name : segment name of the non partitioned table
102 * partition_name : partition or subpartition name
103 * tablespace_name : tablespace containing the segment
104 * file_id : absolute file number of the block in the segment
105 * relative_fno : relative file number of the block in the segment
106 * block_id : start block number of the extent
107 * blocks : number of blocks in the extent
108 * bytes : number of bytes in the extent
109 * min_writetime : minimum of last modification time of the block
110 * max_writetime : maximum of last modification time of the block
111 * avg_writetime : average of last modification time of the block
112 *
113 */
114 function EXTENT_HEAT_MAP(owner in varchar2,
115 segment_name in varchar2,
116 partition_name in varchar2 DEFAULT NULL
117 ) return hm_els_row pipelined;
118
119 /*
120 * Segment Level Heat Map.
121 *
122 * Description :
123 * The procedure returns the heatmap attributes
124 * for the give segment.
125 *
126 * Input Parameters:
127 * tablespace_id : tablespace containing the segment
128 * header_file : segment header relative file number
129 * header_block : segment header block number
130 * segment_objd : dataobj of the segment
131 *
132 * Output Parameters :
133 * min_writetime : Oldest writetime for the segment
134 * max_writetime : Latest writetime for the segment
135 * avg_writetime : Average writetime for the segment
136 * avg_readtime : Average readtime for the segment
137 * min_readtime : Oldest readtime for the segment
138 * max_readtime : Latest readtime for the segment
139 * min_ftstime : Oldest ftstime for the segment
140 * max_ftstime : Latest ftstime for the segment
141 * avg_ftstime : Average ftstime for the segment
142 * min_lookuptime : Oldest lookuptime for the segment
143 * max_lookuptime : Latest lookuptime for the segment
144 * avg_lookuptime : Average lookuptime for the segment
145 *
146 */
147 PROCEDURE SEGMENT_HEAT_MAP(
148 tablespace_id in number,
149 header_file in number,
150 header_block in number,
151 segment_objd in number,
152 min_writetime out date,
153 max_writetime out date,
154 avg_writetime out date,
155 min_readtime out date,
156 max_readtime out date,
157 avg_readtime out date,
158 min_ftstime out date,
159 max_ftstime out date,
160 avg_ftstime out date,
161 min_lookuptime out date,
162 max_lookuptime out date,
163 avg_lookuptime out date);
164
165 -- content of one row in object_heat_map table function.
166 type hm_object_row is record (
167 owner varchar2(100),
168 segment_name varchar2(100),
169 partition_name varchar2(100),
170 tablespace_name varchar2(100),
171 segment_type varchar2(20),
172 segment_size number,
173 min_writetime date,
174 max_writetime date,
175 avg_writetime date,
176 min_readtime date,
177 max_readtime date,
178 avg_readtime date,
179 min_ftstime date,
180 max_ftstime date,
181 avg_ftstime date,
182 min_lookuptime date,
183 max_lookuptime date,
184 avg_lookuptime date
185 );
186
187 type hm_object_table is table of hm_object_row;
188
189
190 /*
191 * Object Level Heat Map.
192 *
193 * Description :
194 * The table function returns the minimum, maximum and average access
195 * times for all the segments belonging to the object. The object
196 * must be a table. The table function raises an error if called on
197 * object tables other than table.
198 *
199 * Input Parameters:
200 * object_owner : object owner
201 * object_name : object name
202 *
203 * Output Parameters :
204 * segment_name : Name of the top level segment
205 * partition_name : Name of the partition
206 * tablespace_name : Name of the tablespace
207 * segment_type : Type of segment as in dba_segments.segment_type
208 * segment_size : Segment size in bytes
209 * min_writetime : Oldest writetime for the segment
210 * max_writetime : Latest writetime for the segment
211 * avg_writetime : Average writetime for the segment
212 * avg_readtime : Average readtime for the segment
213 * min_readtime : Oldest readtime for the segment
214 * max_readtime : Latest readtime for the segment
215 * min_ftstime : Oldest ftstime for the segment
216 * max_ftstime : Latest ftstime for the segment
217 * avg_ftstime : Average ftstime for the segment
218 * min_lookuptime : Oldest lookuptime for the segment
219 * max_lookuptime : Latest lookuptime for the segment
220 * avg_lookuptime : Average lookuptime for the segment
221 *
222 */
223
224 function object_heat_map(
225 object_owner in varchar2,
226 object_name in varchar2)
227 return hm_object_table pipelined;
228
229 -- content of one row in tablespace_heat_map table function.
230 type hm_tablespace_row is record (
231 tablespace_name varchar2(30),
232 segment_count number,
233 allocated_bytes number,
234 min_writetime date,
235 max_writetime date,
236 avg_writetime date,
237 min_readtime date,
238 max_readtime date,
239 avg_readtime date,
240 min_ftstime date,
241 max_ftstime date,
242 avg_ftstime date,
243 min_lookuptime date,
244 max_lookuptime date,
245 avg_lookuptime date
246 );
247
248 type hm_tablespace_table is table of hm_tablespace_row;
249
250 /*
251 * Tablespace Level Heat Map.
252 *
253 * Description :
254 * The table function returns the minimum, maximum and average access
255 * times for all the segments in the tablespace.
256 *
257 * Input Parameters:
258 * tablespace_name : Name of the tablespace
259 *
260 * Output Parameters :
261 * segment_count : Total number of segments in the tablespace
262 * allocated_bytes : Space used by the segments in the tablespace
263 * min_writetime : Oldest writetime for the tablespace
264 * max_writetime : Latest writetime for the tablespace
265 * avg_writetime : Average writetime for the tablespace
266 * avg_readtime : Average readtime for the tablespace
267 * min_readtime : Oldest readtime for the tablespace
268 * max_readtime : Latest readtime for the tablespace
269 * min_ftstime : Oldest ftstime for the tablespace
270 * max_ftstime : Latest ftstime for the tablespace
271 * avg_ftstime : Average ftstime for the tablespace
272 * min_lookuptime : Oldest lookuptime for the tablespace
273 * max_lookuptime : Latest lookuptime for the tablespace
274 * avg_lookuptime : Average lookuptime for the tablespace
275 *
276 */
277
278 function tablespace_heat_map(
279 tablespace_name in varchar2)
280 return hm_tablespace_table pipelined;
281
282 -- Auto Advisor job to materialize heat maps
283 procedure auto_advisor_heatmap_job(topn in number default 100);
284
285 end;
[Home] [Help]