Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 186 lines (145 sloc) 7.311 kb
8f332853 » Yury Velikanov
2012-11-20 Cool procedure to move SQL plan statistics
1 ##### On the source
2
3 EXPLAIN PLAN
4 SET STATEMENT_ID = 'SQL_BAD_PLAN_01'
5 INTO apps.plan_table
6 FOR SELECT
7 ....
8
9 12:30:50 APPS@PRDB:1>
10
11 Explained.
12
13
14 select count(*) from apps.plan_table where STATEMENT_ID = 'SQL_BAD_PLAN_01';
15
16 12:32:20 APPS@PRDB:1> select count(*) from apps.plan_table where STATEMENT_ID = 'SQL_BAD_PLAN_01';
17
18 COUNT(*)
19 ---------------
20 87
21
22 1 row selected.
23
24 12:32:38 APPS@PRDB:1>
25
26
27 begin sys.dbms_stats.create_stat_table(ownname=>'APPS',stattab=>'phy_pln_obj_01',tblspace=>'perfstat'); end;
28 /
29
30 select count(*) from apps.plan_table where STATEMENT_ID = 'SQL_BAD_PLAN_01';
31 -- Should be 0
32
33 -- The following code export statistics for all objects involved in the execution plan to phy_pln_obj_01 table.
34 declare
35 v_ownname varchar2(30):='APPS';
36 v_stattab varchar2(30):='phy_pln_obj_01';
37 cursor c_all_pln_obj is
38 --#C#B#########################################
39 with all_pln_obj as (select
40 pt.object_owner,
41 pt.object_name,
42 pt.object_type
43 from
44 apps.plan_table pt
45 where 1=1
46 and pt.STATEMENT_ID = 'SQL_BAD_PLAN_01'
47 and pt.object_name is not null
48 and pt.object_type!='VIEW')
49 --==============================================
50 select distinct owner, table_name from
51 (
52 -- All tables used in the SQL plan
53 select object_owner owner, object_name table_name from all_pln_obj
54 where object_type='TABLE'
55 union all
56 -- All tabkles used in the SQL plan via indexes
57 select table_owner, table_name from dba_indexes di, all_pln_obj ao
58 where 1=1
59 and di.owner=ao.object_owner
60 and di.index_name=ao.object_name
61 and ao.object_type like '%INDEX%'
62 ) order by owner, table_name;
63 --#C#E#########################################
64 begin
65 for r_stat in c_all_pln_obj
66 loop
67 begin
68 dbms_output.put_line('exec dbms_stats.delete_table_stats(ownname=>'''||r_stat.owner||''',tabname=>'''||r_stat.table_name||''',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
69
70 DBMS_STATS.EXPORT_TABLE_STATS ( ownname=>r_stat.owner,
71 tabname=>r_stat.table_name,
72 stattab=>v_stattab,
73 cascade=>TRUE,
74 statown=>v_ownname);
75 exception
76 when others then
77 dbms_output.put_line('Fail to export table: '||r_stat.owner||'.'||r_stat.table_name);
78 end;
79 end loop;
80 end;
81 /
82
83
84 ##### On the destination
85 # I leave it to you to backup stats from dev tables
86
87 exec dbms_stats.delete_table_stats(ownname=>'APPLSYS',tabname=>'FND_FLEX_VALUES',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
88 exec dbms_stats.delete_table_stats(ownname=>'APPLSYS',tabname=>'FND_ID_FLEX_STRUCTURES',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
89 exec dbms_stats.delete_table_stats(ownname=>'APPLSYS',tabname=>'FND_ID_FLEX_STRUCTURES_TL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
90 exec dbms_stats.delete_table_stats(ownname=>'AR',tabname=>'RA_CUSTOMER_TRX_ALL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
91 exec dbms_stats.delete_table_stats(ownname=>'AR',tabname=>'RA_CUSTOMER_TRX_LINES_ALL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
92 exec dbms_stats.delete_table_stats(ownname=>'BOM',tabname=>'BOM_COMPONENTS_B',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
93 exec dbms_stats.delete_table_stats(ownname=>'BOM',tabname=>'BOM_STRUCTURES_B',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
94 exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_CATEGORIES_B',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
95 exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_CATEGORIES_TL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
96 exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_ITEM_CATEGORIES',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
97 exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_PARAMETERS',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
98 exec dbms_stats.delete_table_stats(ownname=>'INV',tabname=>'MTL_SYSTEM_ITEMS_B',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
99 exec dbms_stats.delete_table_stats(ownname=>'ONT',tabname=>'OE_ORDER_LINES_ALL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
100 exec dbms_stats.delete_table_stats(ownname=>'ONT',tabname=>'OE_TRANSACTION_TYPES_TL',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
5c23676a » Yury Velikanov
2012-11-20 security fix
101 exec dbms_stats.delete_table_stats(ownname=>'XXCUST',tabname=>'CUST_CONV_CUM_PROD_SALES',CASCADE_PARTS=>TRUE, CASCADE_COLUMNS=> TRUE, CASCADE_INDEXES=> TRUE);
8f332853 » Yury Velikanov
2012-11-20 Cool procedure to move SQL plan statistics
102
103 begin sys.dbms_stats.create_stat_table(ownname=>'APPS',stattab=>'phy_pln_obj_01',tblspace=>'perfstat'); end;
104 /
105
106
107 ##### On the source
108 CREATE DATABASE LINK FROM_PRDB_TO_DVDB_01
109 CONNECT TO APPS
110 IDENTIFIED BY password
111 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dev host)(PORT=dev port))(CONNECT_DATA=(SERVICE_NAME=DVDB)))';
112
113 insert into apps.phy_pln_obj_01@FROM_PRDB_TO_DVDB_01 select * from apps.phy_pln_obj_01;
114
115
116 14:24:40 APPS@PRDB:1> insert into apps.phy_pln_obj_01@FROM_PRDB_TO_DVDB_01 select * from apps.phy_pln_obj_01;
117
118 1782 rows created.
119
120 14:26:18 APPS@PRDB:1>
121
122
123 ##### On the destination
124
125 14:25:27 APPS@DVDB:1> select count(*) from apps.phy_pln_obj_01;
126
127 COUNT(*)
128 ---------------
129 1782
130
131 14:26:58 APPS@DVDB:1>
132
133
134 -- Time to delete stats and import the stats from EBSP
135
136 -- Executed all DELETE commands in EBSD
137
138
139 declare
140 cursor c_stat is select distinct c5 owner, c1 table_name, STATID from
141 apps.phy_pln_obj_01 where TYPE = 'T';
142 begin
143 for r_stat in c_stat
144 loop
145 begin
146 dbms_output.put_line('Importing stats for '||r_stat.owner||'.'||r_stat.table_name);
147 DBMS_STATS.IMPORT_TABLE_STATS (ownname=>r_stat.owner,
148 tabname=>r_stat.table_name,
149 statid=>r_stat.STATID,
150 stattab=>'phy_pln_obj_01',
151 cascade=>TRUE,
152 statown=>'apps');
153 end;
154 end loop;
155 end;
156 /
157
158
159
160 14:31:15 APPS@DVDB:1> declare
161 14:32:26 APPS@DVDB:1> cursor c_stat is select distinct c5 owner, c1 table_name, STATID from
162 14:32:26 APPS@DVDB:1> apps.phy_pln_obj_01 where TYPE = 'T';
163 14:32:26 APPS@DVDB:1> begin
164 14:32:26 APPS@DVDB:1> for r_stat in c_stat
165 14:32:26 APPS@DVDB:1> loop
166 14:32:26 APPS@DVDB:1> begin
167 14:32:26 APPS@DVDB:1> dbms_output.put_line('Importing stats for '||r_stat.owner||'.'||r_stat.table_name);
168 14:32:26 APPS@DVDB:1> DBMS_STATS.IMPORT_TABLE_STATS (ownname=>r_stat.owner,
169 14:32:26 APPS@DVDB:1> tabname=>r_stat.table_name,
170 14:32:26 APPS@DVDB:1> statid=>r_stat.STATID,
171 14:32:26 APPS@DVDB:1> stattab=>'phy_pln_obj_01',
172 14:32:26 APPS@DVDB:1> cascade=>TRUE,
173 14:32:26 APPS@DVDB:1> statown=>'apps');
174 14:32:26 APPS@DVDB:1> end;
175 14:32:26 APPS@DVDB:1> end loop;
176 14:32:26 APPS@DVDB:1> end;
177 14:32:26 APPS@DVDB:1> /
178 Importing stats for YO.SUPER_COOL_TAB
179 Importing stats for YO2.SUPER_COOL_TAB2
180 ....
181
182 PL/SQL procedure successfully completed.
183
184 14:32:29 APPS@DVDB:1>
185
186 ## Check the execution plan
Something went wrong with that request. Please try again.