1 package body BIS_BIA_STATS AS
2 /*$Header: BISGTSPB.pls 120.2 2006/09/07 14:34:46 aguwalan noship $*/
3
4 /**
5 * This is the wrapper around FND_STATS.gather_table_stats
6 * Object name will be the table name or MV name seeded in RSG,
7 * while object type is either 'MV' or 'TABLE'
8 * We will derive the object schema name , then
9 * call FND_STATS.gather_table_stats to analyze the object
10 **/
11
12 /**Changes for Enhancement 4378448. We have to review/rollback
13 the changes after ST fix the issue as mentioned in the enhancement.
14 Changes made:
15 Call FND_STATS.SET_TABLE_STATS for the MV log if num_rows in all_tables
16 for this log <>0. Otherwise, not to call FND_STATS.SET_TABLE_STATS for the MV log.
17 **/
18
19 procedure GATHER_TABLE_STATS(errbuf out NOCOPY varchar2,
20 retcode out NOCOPY varchar2,
21 objecttype in varchar2,
22 objectname in varchar2,
23 percent in number default null,
24 degree in number default null,
25 partname in varchar2 default null,
26 backup_flag in varchar2 default 'NOBACKUP',
27 granularity in varchar2 default 'DEFAULT',
28 hmode in varchar2 default 'LASTRUN',
29 invalidate in varchar2 default 'Y'
30 ) is
31 l_object_owner varchar2(30);
32 l_object_log varchar2(30);
33 l_request_id number;
34 l_root_request_id number;
35 l_stage_request_id number;
36 l_num_rows number;
37 begin
38 l_request_id:=fnd_global.CONC_REQUEST_ID ;
39 l_root_request_id:=FND_GLOBAL.CONC_PRIORITY_REQUEST;
40
41 bis_collection_utilities.put_line('request id: '||l_request_id);
42
43 begin
44 l_object_owner:=bis_create_requestset.get_object_owner(objectname,objecttype);
45 l_object_log:=bis_create_requestset.get_mv_log(objectname,l_object_owner);
46 bis_collection_utilities.put_line('schema for '||objectname||':'||l_object_owner);
47 bis_collection_utilities.put_line('MV log for '||objectname||':'||l_object_log );
48 exception
49 when others then
50 bis_collection_utilities.put_line('Error happened when get object schema and object mv log'||sqlcode||sqlerrm);
51 raise;
52 end;
53
54
55 if nvl(l_object_owner,'NOTFOUND')<>'NOTFOUND' then
56 begin
57 bis_collection_utilities.put_line('Calling FND_STATS.GATHER_TABLE_STATS for object '||l_object_owner||','||objectname);
58 FND_STATS.GATHER_TABLE_STATS(errbuf =>errbuf,
59 retcode =>retcode,
60 ownname =>l_object_owner,
61 tabname =>objectname,
62 percent =>percent,
63 degree =>degree,
64 partname =>partname,
65 backup_flag =>backup_flag,
66 granularity =>granularity,
67 hmode =>hmode,
68 invalidate=>invalidate
69 );
70 bis_collection_utilities.put_line(l_object_owner||','||objectname||' has been analyzed successfully');
71 exception
72 when others then
73 bis_collection_utilities.put_line('Error happened inside FND_STATS.GATHER_TABLE_STATS '||sqlcode||sqlerrm);
74 raise;
75 end;
76 end if;
77
78 if l_object_log is not null then
79 begin
80 select num_rows
81 into l_num_rows
82 from all_tables
83 where owner=l_object_owner
84 and table_name=l_object_log;
85 exception
86 when no_data_found then
87 l_num_rows:=0;
88 when others then
89 raise;
90 end;
91 end if;
92
93 if l_object_log is not null and l_num_rows=0 then
94 bis_collection_utilities.put_line('Not to call FND_STATS.SET_TABLE_STATS for '||l_object_owner||'.'||l_object_log||' because num_rows in all_tables for this MV log is already 0');
95 end if;
96
97 if l_object_log is not null and l_num_rows<>0 then
98 begin
99 bis_collection_utilities.put_line('Calling FND_STATS.SET_TABLE_STATS for object log '||l_object_owner||','||l_object_log);
100 FND_STATS.SET_TABLE_STATS(OWNNAME=>l_object_owner,
101 TABNAME=>l_object_log,
102 NUMROWS=>0,
103 NUMBLKS=>0,
104 AVGRLEN=>0);
105 bis_collection_utilities.put_line(l_object_owner||','||l_object_log||' statistics has been set successfully');
106 exception
107 when others then
108 bis_collection_utilities.put_line('Error happened inside FND_STATS.SET_TABLE_STATS '||sqlcode||sqlerrm);
109 raise;
110 end;
111 end if;
112 --Enh#4418520-aguwalan
113 IF(BIS_CREATE_REQUESTSET.is_history_collect_on(l_root_request_id)) THEN
114 begin
118 (x_request_id => l_request_id,
115 bis_collection_utilities.put_line('Calling BIS_COLL_RS_HISTORY.insert_program_object_data ');
116 if nvl(l_object_owner,'NOTFOUND')<>'NOTFOUND' then
117 BIS_COLL_RS_HISTORY.insert_program_object_data
119 x_stage_req_id =>null,
120 x_object_name =>objectname,
121 x_object_type =>objecttype,
122 x_refresh_type =>'ANALYZED',
123 x_set_request_id =>l_root_request_id);
124 end if;
125
126 if l_object_log is not null and l_num_rows<>0 then
127 BIS_COLL_RS_HISTORY.insert_program_object_data
128 (x_request_id => l_request_id,
129 x_stage_req_id =>null,
130 x_object_name =>l_object_log,
131 x_object_type =>'MV_LOG',
132 x_refresh_type =>'ANALYZED',
133 x_set_request_id =>l_root_request_id);
134 end if;
135
136 bis_collection_utilities.put_line('Called BIS_COLL_RS_HISTORY.insert_program_object_data');
137 exception
138 when others then
139 bis_collection_utilities.put_line('Error happened in BIS_COLL_RS_HISTORY.insert_program_object_data'||sqlcode||sqlerrm);
140 raise;
141 end;
142 ELSE
143 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
144 BIS_COLLECTION_UTILITIES.put_line('Request Set History Collection Option is off for this Request Set.');
145 BIS_COLLECTION_UTILITIES.put_line('No History Collection will happen for this request set.');
146 BIS_COLLECTION_UTILITIES.put_line('------------------------------------------------------------------');
147 END IF;
148
149 exception
150 when others then
151 raise;
152
153 end;
154 END BIS_BIA_STATS;
155
[Home] [Help]