Skip to content

Commit f106f8f

Browse files
author
Dan McGhan
committed
Merge branch 'master' of github.com:oracle/oracle-db-examples
2 parents ee573d1 + 2f51ea5 commit f106f8f

File tree

13 files changed

+899
-0
lines changed

13 files changed

+899
-0
lines changed

‎optimizer/upgrading_to_12c/dpump_copy/README.md

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,8 @@ Example *lst* and *log* files are included so you can see the expected results.
2828

2929
Notice how all of the statistics and metadata is copied across from S1 to S2.
3030

31+
See directory "table_to_table" for copying statistics metadata from one table to another.
32+
3133
### DISCLAIMER
3234

3335
* These scripts are provided for educational purposes only.

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/README.md

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
# Copying Statistics
2+
3+
When you copy statistics from one database schema to another, you need to consider:
4+
5+
- Base statistics information for tables, columns and indexes (e.g. NUM_ROWS)
6+
- Extended statistics
7+
- Histograms
8+
- Column usage information
9+
- Individual table DBMS_STATS preferences
10+
- SQL plan directives
11+
12+
In this directory is a self-contained example to create a schema "S1" with a full compliment of statistics on table "T1" and stale statistics on "T2". The Data Pump example copies statistical metadata from T1 to T2.
13+
14+
To run the example:
15+
16+
```
17+
$ sqlplus / as sysdba
18+
SQL> @user
19+
SQL> @look -- This lists statistics information and metadata before it has been copied to T2
20+
SQL> exit
21+
$ ./dp_copy -- Using Data Pump to copy metadata from T1 to T2
22+
$ sqlplus / as sysdba
23+
SQL> @look -- This lists statistics information and metadata after it has been copied to T2 - note index does not have copied stats
24+
SQL> @gather_t2 -- Gather statistics on T2 - BUT NOTE that stats are locked!
25+
SQL> @unlock
26+
SQL> gather_t2 -- Gather now works
27+
SQL> @look -- T2 stats are now fresh and notice how column usage metadata has kept the histogram
28+
```
29+
30+
Example *lst* and *log* files are included so you can see the expected results.
31+
32+
The script outputs where generated using Oracle Database 19c
33+
34+
LIMITATIONS
35+
36+
- Note that INDEX statistics are not remapped with "remap_table" in datapump. This means that index stats will not be copied from T1I to T2I.
37+
- Datapump remap_table does not reliably transport base table and column stats from one table to another (and the results are verion dependent). Hence, it is recommended that you gather statistics on the target table after the metadata is copied (T2 in this example)
38+
39+
40+
### DISCLAIMER
41+
42+
* These scripts are provided for educational purposes only.
43+
* They are NOT supported by Oracle World Wide Technical Support.
44+
* The scripts have been tested and they appear to work as intended.
45+
* You should always run scripts on a test instance.
46+
47+
### WARNING
48+
49+
* These scripts drop and create user accounts. For use on test databases. DBA access is required.

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/dp_copy

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
#!/bin/sh
2+
#
3+
expdp s1/s1 dumpfile=expdat.dmp content=metadata_only include=statistics
4+
if [ $? -eq 0 ]
5+
then
6+
impdp s1/s1 dumpfile=expdat.dmp table_exists_action=skip include=statistics remap_table=t1:t2
7+
else
8+
echo "Error detected. Import skipped."
9+
fi

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/dp_copy.log

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
2+
Export: Release 19.0.0.0.0 - Production on Mon Jun 17 05:35:58 2019
3+
Version 19.3.0.0.0
4+
5+
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
6+
7+
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
8+
Starting "S1"."SYS_EXPORT_SCHEMA_01": s1/******** dumpfile=expdat.dmp content=metadata_only include=statistics
9+
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
10+
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11+
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
12+
Master table "S1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
13+
******************************************************************************
14+
Dump file set for S1.SYS_EXPORT_SCHEMA_01 is:
15+
/ade/b/2729850815/oracle/rdbms/log/expdat.dmp
16+
Job "S1"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 17 05:36:12 2019 elapsed 0 00:00:13
17+
18+
19+
Import: Release 19.0.0.0.0 - Production on Mon Jun 17 05:36:15 2019
20+
Version 19.3.0.0.0
21+
22+
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
23+
24+
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
25+
Master table "S1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
26+
Starting "S1"."SYS_IMPORT_FULL_01": s1/******** dumpfile=expdat.dmp table_exists_action=skip include=statistics remap_table=t1:t2
27+
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
28+
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
29+
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
30+
Job "S1"."SYS_IMPORT_FULL_01" successfully completed at Mon Jun 17 05:36:35 2019 elapsed 0 00:00:19
31+

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/expimp.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
exec dbms_stats.drop_stat_table('s1','my_stat_tab')
2+
exec dbms_stats.create_stat_table('s1','my_stat_tab')
3+
4+
exec dbms_stats.export_table_stats('s1','t1',stattab=>'my_stat_tab')
5+
exec dbms_stats.export_index_stats('s1','t1i',stattab=>'my_stat_tab')
6+
7+
exec dbms_stats.import_table_stats('s1','t2',stattab=>'my_stat_tab')
8+
exec dbms_stats.import_index_stats('s1','t2i',stattab=>'my_stat_tab')
9+
10+

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/gather_index.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
exec dbms_stats.gather_index_stats(user,'T2I')

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/gather_t2.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
exec dbms_stats.delete_table_stats(ownname=>'s1',tabname=>'t2')
2+
exec dbms_stats.gather_table_stats(ownname=>'s1',tabname=>'t2',method_opt=>'for all columns size auto');

‎optimizer/upgrading_to_12c/dpump_copy/table_to_table/look.sql

Lines changed: 82 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,82 @@
1+
set pagesize 1000
2+
set linesize 200
3+
column table_name format a30
4+
column index_name format a30
5+
column owner format a30
6+
column column_name format a30
7+
column tcb_pref format a10
8+
column cuse format a100 wrapped
9+
column analyzed format a19
10+
column extension format a20
11+
break on table_name
12+
13+
prompt =======================================================================
14+
prompt This script displays the various metadata associated with optimizer
15+
prompt statistics and adaptive features.
16+
prompt Specifically:
17+
prompt Stats informaiton in DBA_TABLES
18+
prompt Stats information in DBA_INDEXES
19+
prompt Extended statistics
20+
prompt Histograms
21+
prompt Column usage information
22+
prompt Table preferences
23+
prompt =======================================================================
24+
25+
prompt Compare analyzed dates between T1 and T2
26+
prompt S1.T1 and S1.T2 have slightly different row counts. The difference will
27+
prompt be apparent in the statistics until the statistics are copied from T1 to T2.
28+
prompt ============================================================================
29+
select table_name,to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS') analyzed, num_rows
30+
from dba_tables
31+
where owner in ('S1') and table_name in ('T1','T2') order by 1;
32+
33+
select table_name,column_name,num_distinct
34+
from dba_tab_col_statistics
35+
where owner in ('S1') and table_name in ('T1','T2')
36+
order by 1,2;
37+
38+
prompt Compare analyzed dates between T1 and T2 for indexes
39+
prompt and notice that the index statistics are not copied
40+
prompt because we don't have a remap_index in DP
41+
prompt ====================================================
42+
select table_name,index_name,num_rows,to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS') analyzed
43+
from dba_indexes
44+
where owner in ('S1') and table_name in ('T1','T2') order by 1;
45+
46+
prompt Extended stats. Compare user S1 with S2...
47+
prompt ==========================================
48+
select table_name,extension
49+
from dba_stat_extensions
50+
where owner in ('S1')
51+
order by owner,table_name;
52+
53+
prompt Histograms. Compare user S1 with S2...
54+
prompt ======================================
55+
select table_name,column_name,histogram
56+
from dba_tab_col_statistics
57+
where owner in ('S1')
58+
order by owner,table_name,column_name;
59+
60+
prompt Number of COL_USAGE$ entries for T1
61+
prompt ===================================
62+
select count(*) from sys.col_usage$
63+
where obj# = (select object_id from dba_objects where object_name = 'T1' and owner = 'S1');
64+
prompt Number of COL_USAGE$ entries for T2
65+
prompt ===================================
66+
select count(*) from sys.col_usage$
67+
where obj# = (select object_id from dba_objects where object_name = 'T2' and owner = 'S1');
68+
69+
set long 1000000
70+
prompt Column usage report for S1.T1
71+
prompt =============================
72+
select dbms_stats.report_col_usage(ownname=>'s1',tabname=>'t1') cuse from dual;
73+
prompt Column usage report for S1.T2
74+
prompt =============================
75+
select dbms_stats.report_col_usage(ownname=>'s1',tabname=>'t2') cuse from dual;
76+
77+
prompt S1.T1 TABLE_CACHED_BLOCKS preference
78+
prompt ====================================
79+
select dbms_stats.get_prefs ('TABLE_CACHED_BLOCKS','s1','t1') tcb_pref from dual;
80+
prompt S1.T2 TABLE_CACHED_BLOCKS preference (should match S1.T1 once copied)
81+
prompt =====================================================================
82+
select dbms_stats.get_prefs ('TABLE_CACHED_BLOCKS','s1','t2') tcb_pref from dual;

0 commit comments

Comments
 (0)