Oracle Tips and Tricks — David Fitzjarrell

May 21, 2018

Export – Import Business, Again

Filed under: General — dfitzjarrell @ 19:05

"At first it was difficult to tell just what had changed — it all looked the same
and it all smelled the same — but, for some reason, nothing sounded the same."
-- Norton Juster, The Phantom Tollbooth

Doing things just because “they’ve always been done that way” may not be the best reason to stick with old and accepted processes and utilities. Oracle has changed significantly over the years and those improvements shouldn’t be ignored simply because “that’s always worked before.” Export and import have dramatically improved over the years, and not simply in how the user interface is called. New database constructs, new histograms, new index types are all thoughtfully covered with the Data Pump utiliies. Not so with the historical exp and imp tools; these have not been updated since Oracle 9i, when Data Pump was introduced. Because of this using conventional exp and imp in a modern version of Oracle can create some unpleasant surprises.

Histograms are important to performance as they inform the optimizer of data skew and other data patterns that can provide a better ‘picture’ to work with when creating an execution plan. Top-n and hybrid histograms can’t be handled with the exp and imp utilities, so using them to transfer data between two 12.1.0.2 databases will cause those histograms to be lost since exp and imp can’t know of such histograms. Let’s use an example from Jonathan Lewis to illustrate what happens when Data Pump isn’t used on databases newer than 9.x.

Let’s build a table (called T1), load it with data and create some histograms; the data loaded will create top-frequency and hybrid histograms. Looking at the results:



COLUMN_NAME                         NUM_DISTINCT HISTOGRAM       NUM_BUCKETS
----------------------------------- ------------ --------------- -----------
FREQUENCY                                    100 FREQUENCY               100

TOP_N                                        100 TOP-FREQUENCY            95

HYBRID                                       100 HYBRID                   50


COLUMN_NAME                         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE  ENDPOINT_REPEAT_COUNT
----------------------------------- --------------- -------------- ---------------------- ---------------------
FREQUENCY                                         3              1 1                                          0
                                                  8              2 2                                          0
                                                 15              3 3                                          0
                                                 24              4 4                                          0
                                                 35              5 5                                          0
                                                 48              6 6                                          0
                                                 63              7 7                                          0
                                                 80              8 8                                          0
                                                 99              9 9                                          0
                                                120             10 10                                         0
                                                143             11 11                                         0
                                                168             12 12                                         0
                                                195             13 13                                         0
                                                224             14 14                                         0
                                                255             15 15                                         0
                                                288             16 16                                         0
                                                323             17 17                                         0
                                                360             18 18                                         0
                                                399             19 19                                         0
                                                440             20 20                                         0
                                                483             21 21                                         0
                                                528             22 22                                         0
                                                575             23 23                                         0
                                                624             24 24                                         0
                                                675             25 25                                         0
                                                728             26 26                                         0
                                                783             27 27                                         0
                                                840             28 28                                         0
                                                899             29 29                                         0
                                                960             30 30                                         0
                                               1023             31 31                                         0
                                               1088             32 32                                         0
                                               1155             33 33                                         0
                                               1224             34 34                                         0
                                               1295             35 35                                         0
                                               1368             36 36                                         0
                                               1443             37 37                                         0
                                               1520             38 38                                         0
                                               1599             39 39                                         0
                                               1680             40 40                                         0
                                               1763             41 41                                         0
                                               1848             42 42                                         0
                                               1935             43 43                                         0
                                               2024             44 44                                         0
                                               2115             45 45                                         0
                                               2208             46 46                                         0
                                               2303             47 47                                         0
                                               2400             48 48                                         0
                                               2499             49 49                                         0
                                               2600             50 50                                         0
                                               2703             51 51                                         0
                                               2808             52 52                                         0
                                               2915             53 53                                         0
                                               3024             54 54                                         0
                                               3135             55 55                                         0
                                               3248             56 56                                         0
                                               3363             57 57                                         0
                                               3480             58 58                                         0
                                               3599             59 59                                         0
                                               3720             60 60                                         0
                                               3843             61 61                                         0
                                               3968             62 62                                         0
                                               4095             63 63                                         0
                                               4224             64 64                                         0
                                               4355             65 65                                         0
                                               4488             66 66                                         0
                                               4623             67 67                                         0
                                               4760             68 68                                         0
                                               4899             69 69                                         0
                                               5040             70 70                                         0
                                               5183             71 71                                         0
                                               5328             72 72                                         0
                                               5475             73 73                                         0
                                               5624             74 74                                         0
                                               5775             75 75                                         0
                                               5928             76 76                                         0
                                               6083             77 77                                         0
                                               6240             78 78                                         0
                                               6399             79 79                                         0
                                               6560             80 80                                         0
                                               6723             81 81                                         0
                                               6888             82 82                                         0
                                               7055             83 83                                         0
                                               7224             84 84                                         0
                                               7395             85 85                                         0
                                               7568             86 86                                         0
                                               7743             87 87                                         0
                                               7920             88 88                                         0
                                               8099             89 89                                         0
                                               8280             90 90                                         0
                                               8463             91 91                                         0
                                               8648             92 92                                         0
                                               8835             93 93                                         0
                                               9024             94 94                                         0
                                               9215             95 95                                         0
                                               9408             96 96                                         0
                                               9603             97 97                                         0
                                               9800             98 98                                         0
                                               9999             99 99                                         0
                                              10000            100 100                                        0

HYBRID                                            3              1 1                                          3
                                                224             14 14                                        29
                                                440             20 20                                        41
                                                675             25 25                                        51
                                                899             29 29                                        59
                                               1155             33 33                                        67
                                               1368             36 36                                        73
                                               1599             39 39                                        79
                                               1848             42 42                                        85
                                               2115             45 45                                        91
                                               2400             48 48                                        97
                                               2703             51 51                                       103
                                               2915             53 53                                       107
                                               3135             55 55                                       111
                                               3363             57 57                                       115
                                               3599             59 59                                       119
                                               3843             61 61                                       123
                                               4095             63 63                                       127
                                               4355             65 65                                       131
                                               4623             67 67                                       135
                                               4899             69 69                                       139
                                               5183             71 71                                       143
                                               5328             72 72                                       145
                                               5475             73 73                                       147
                                               5624             74 74                                       149
                                               5775             75 75                                       151
                                               5928             76 76                                       153
                                               6083             77 77                                       155
                                               6240             78 78                                       157
                                               6399             79 79                                       159
                                               6560             80 80                                       161
                                               6723             81 81                                       163
                                               6888             82 82                                       165
                                               7055             83 83                                       167
                                               7224             84 84                                       169
                                               7395             85 85                                       171
                                               7568             86 86                                       173
                                               7743             87 87                                       175
                                               7920             88 88                                       177
                                               8099             89 89                                       179
                                               8280             90 90                                       181
                                               8463             91 91                                       183
                                               8648             92 92                                       185
                                               8835             93 93                                       187
                                               9024             94 94                                       189
                                               9215             95 95                                       191
                                               9408             96 96                                       193
                                               9603             97 97                                       195
                                               9800             98 98                                       197
                                              10000            100 100                                        1

TOP_N                                             1              1 1                                          0
                                                 16              7 7                                          0
                                                 33              8 8                                          0
                                                 52              9 9                                          0
                                                 73             10 10                                         0
                                                 96             11 11                                         0
                                                121             12 12                                         0
                                                148             13 13                                         0
                                                177             14 14                                         0
                                                208             15 15                                         0
                                                241             16 16                                         0
                                                276             17 17                                         0
                                                313             18 18                                         0
                                                352             19 19                                         0
                                                393             20 20                                         0
                                                436             21 21                                         0
                                                481             22 22                                         0
                                                528             23 23                                         0
                                                577             24 24                                         0
                                                628             25 25                                         0
                                                681             26 26                                         0
                                                736             27 27                                         0
                                                793             28 28                                         0
                                                852             29 29                                         0
                                                913             30 30                                         0
                                                976             31 31                                         0
                                               1041             32 32                                         0
                                               1108             33 33                                         0
                                               1177             34 34                                         0
                                               1248             35 35                                         0
                                               1321             36 36                                         0
                                               1396             37 37                                         0
                                               1473             38 38                                         0
                                               1552             39 39                                         0
                                               1633             40 40                                         0
                                               1716             41 41                                         0
                                               1801             42 42                                         0
                                               1888             43 43                                         0
                                               1977             44 44                                         0
                                               2068             45 45                                         0
                                               2161             46 46                                         0
                                               2256             47 47                                         0
                                               2353             48 48                                         0
                                               2452             49 49                                         0
                                               2553             50 50                                         0
                                               2656             51 51                                         0
                                               2761             52 52                                         0
                                               2868             53 53                                         0
                                               2977             54 54                                         0
                                               3088             55 55                                         0
                                               3201             56 56                                         0
                                               3316             57 57                                         0
                                               3433             58 58                                         0
                                               3552             59 59                                         0
                                               3673             60 60                                         0
                                               3796             61 61                                         0
                                               3921             62 62                                         0
                                               4048             63 63                                         0
                                               4177             64 64                                         0
                                               4308             65 65                                         0
                                               4441             66 66                                         0
                                               4576             67 67                                         0
                                               4713             68 68                                         0
                                               4852             69 69                                         0
                                               4993             70 70                                         0
                                               5136             71 71                                         0
                                               5281             72 72                                         0
                                               5428             73 73                                         0
                                               5577             74 74                                         0
                                               5728             75 75                                         0
                                               5881             76 76                                         0
                                               6036             77 77                                         0
                                               6193             78 78                                         0
                                               6352             79 79                                         0
                                               6513             80 80                                         0
                                               6676             81 81                                         0
                                               6841             82 82                                         0
                                               7008             83 83                                         0
                                               7177             84 84                                         0
                                               7348             85 85                                         0
                                               7521             86 86                                         0
                                               7696             87 87                                         0
                                               7873             88 88                                         0
                                               8052             89 89                                         0
                                               8233             90 90                                         0
                                               8416             91 91                                         0
                                               8601             92 92                                         0
                                               8788             93 93                                         0
                                               8977             94 94                                         0
                                               9168             95 95                                         0
                                               9361             96 96                                         0
                                               9556             97 97                                         0
                                               9753             98 98                                         0
                                               9952             99 99                                         0
                                               9953            100 100                                        0


245 rows selected.

After exporting the table with conventional exp the original table is dropped:

Table dropped.

Import the table with conventional import (imp) and look at the results; notice that all of the specialty histograms are now converted to frequency histograms:




COLUMN_NAME                         NUM_DISTINCT HISTOGRAM       NUM_BUCKETS
----------------------------------- ------------ --------------- -----------
FREQUENCY                                    100 FREQUENCY               100

TOP_N                                        100 FREQUENCY                95

HYBRID                                       100 FREQUENCY                50



COLUMN_NAME                         ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE  ENDPOINT_REPEAT_COUNT
----------------------------------- --------------- -------------- ---------------------- ---------------------
FREQUENCY                                         3              1                                            0
                                                  8              2                                            0
                                                 15              3                                            0
                                                 24              4                                            0
                                                 35              5                                            0
                                                 48              6                                            0
                                                 63              7                                            0
                                                 80              8                                            0
                                                 99              9                                            0
                                                120             10                                            0
                                                143             11                                            0
                                                168             12                                            0
                                                195             13                                            0
                                                224             14                                            0
                                                255             15                                            0
                                                288             16                                            0
                                                323             17                                            0
                                                360             18                                            0
                                                399             19                                            0
                                                440             20                                            0
                                                483             21                                            0
                                                528             22                                            0
                                                575             23                                            0
                                                624             24                                            0
                                                675             25                                            0
                                                728             26                                            0
                                                783             27                                            0
                                                840             28                                            0
                                                899             29                                            0
                                                960             30                                            0
                                               1023             31                                            0
                                               1088             32                                            0
                                               1155             33                                            0
                                               1224             34                                            0
                                               1295             35                                            0
                                               1368             36                                            0
                                               1443             37                                            0
                                               1520             38                                            0
                                               1599             39                                            0
                                               1680             40                                            0
                                               1763             41                                            0
                                               1848             42                                            0
                                               1935             43                                            0
                                               2024             44                                            0
                                               2115             45                                            0
                                               2208             46                                            0
                                               2303             47                                            0
                                               2400             48                                            0
                                               2499             49                                            0
                                               2600             50                                            0
                                               2703             51                                            0
                                               2808             52                                            0
                                               2915             53                                            0
                                               3024             54                                            0
                                               3135             55                                            0
                                               3248             56                                            0
                                               3363             57                                            0
                                               3480             58                                            0
                                               3599             59                                            0
                                               3720             60                                            0
                                               3843             61                                            0
                                               3968             62                                            0
                                               4095             63                                            0
                                               4224             64                                            0
                                               4355             65                                            0
                                               4488             66                                            0
                                               4623             67                                            0
                                               4760             68                                            0
                                               4899             69                                            0
                                               5040             70                                            0
                                               5183             71                                            0
                                               5328             72                                            0
                                               5475             73                                            0
                                               5624             74                                            0
                                               5775             75                                            0
                                               5928             76                                            0
                                               6083             77                                            0
                                               6240             78                                            0
                                               6399             79                                            0
                                               6560             80                                            0
                                               6723             81                                            0
                                               6888             82                                            0
                                               7055             83                                            0
                                               7224             84                                            0
                                               7395             85                                            0
                                               7568             86                                            0
                                               7743             87                                            0
                                               7920             88                                            0
                                               8099             89                                            0
                                               8280             90                                            0
                                               8463             91                                            0
                                               8648             92                                            0
                                               8835             93                                            0
                                               9024             94                                            0
                                               9215             95                                            0
                                               9408             96                                            0
                                               9603             97                                            0
                                               9800             98                                            0
                                               9999             99                                            0
                                              10000            100                                            0

HYBRID                                            3              1                                            0
                                                224             14                                            0
                                                440             20                                            0
                                                675             25                                            0
                                                899             29                                            0
                                               1155             33                                            0
                                               1368             36                                            0
                                               1599             39                                            0
                                               1848             42                                            0
                                               2115             45                                            0
                                               2400             48                                            0
                                               2703             51                                            0
                                               2915             53                                            0
                                               3135             55                                            0
                                               3363             57                                            0
                                               3599             59                                            0
                                               3843             61                                            0
                                               4095             63                                            0
                                               4355             65                                            0
                                               4623             67                                            0
                                               4899             69                                            0
                                               5183             71                                            0
                                               5328             72                                            0
                                               5475             73                                            0
                                               5624             74                                            0
                                               5775             75                                            0
                                               5928             76                                            0
                                               6083             77                                            0
                                               6240             78                                            0
                                               6399             79                                            0
                                               6560             80                                            0
                                               6723             81                                            0
                                               6888             82                                            0
                                               7055             83                                            0
                                               7224             84                                            0
                                               7395             85                                            0
                                               7568             86                                            0
                                               7743             87                                            0
                                               7920             88                                            0
                                               8099             89                                            0
                                               8280             90                                            0
                                               8463             91                                            0
                                               8648             92                                            0
                                               8835             93                                            0
                                               9024             94                                            0
                                               9215             95                                            0
                                               9408             96                                            0
                                               9603             97                                            0
                                               9800             98                                            0
                                              10000            100                                            0

TOP_N                                             1              1                                            0
                                                 16              7                                            0
                                                 33              8                                            0
                                                 52              9                                            0
                                                 73             10                                            0
                                                 96             11                                            0
                                                121             12                                            0
                                                148             13                                            0
                                                177             14                                            0
                                                208             15                                            0
                                                241             16                                            0
                                                276             17                                            0
                                                313             18                                            0
                                                352             19                                            0
                                                393             20                                            0
                                                436             21                                            0
                                                481             22                                            0
                                                528             23                                            0
                                                577             24                                            0
                                                628             25                                            0
                                                681             26                                            0
                                                736             27                                            0
                                                793             28                                            0
                                                852             29                                            0
                                                913             30                                            0
                                                976             31                                            0
                                               1041             32                                            0
                                               1108             33                                            0
                                               1177             34                                            0
                                               1248             35                                            0
                                               1321             36                                            0
                                               1396             37                                            0
                                               1473             38                                            0
                                               1552             39                                            0
                                               1633             40                                            0
                                               1716             41                                            0
                                               1801             42                                            0
                                               1888             43                                            0
                                               1977             44                                            0
                                               2068             45                                            0
                                               2161             46                                            0
                                               2256             47                                            0
                                               2353             48                                            0
                                               2452             49                                            0
                                               2553             50                                            0
                                               2656             51                                            0
                                               2761             52                                            0
                                               2868             53                                            0
                                               2977             54                                            0
                                               3088             55                                            0
                                               3201             56                                            0
                                               3316             57                                            0
                                               3433             58                                            0
                                               3552             59                                            0
                                               3673             60                                            0
                                               3796             61                                            0
                                               3921             62                                            0
                                               4048             63                                            0
                                               4177             64                                            0
                                               4308             65                                            0
                                               4441             66                                            0
                                               4576             67                                            0
                                               4713             68                                            0
                                               4852             69                                            0
                                               4993             70                                            0
                                               5136             71                                            0
                                               5281             72                                            0
                                               5428             73                                            0
                                               5577             74                                            0
                                               5728             75                                            0
                                               5881             76                                            0
                                               6036             77                                            0
                                               6193             78                                            0
                                               6352             79                                            0
                                               6513             80                                            0
                                               6676             81                                            0
                                               6841             82                                            0
                                               7008             83                                            0
                                               7177             84                                            0
                                               7348             85                                            0
                                               7521             86                                            0
                                               7696             87                                            0
                                               7873             88                                            0
                                               8052             89                                            0
                                               8233             90                                            0
                                               8416             91                                            0
                                               8601             92                                            0
                                               8788             93                                            0
                                               8977             94                                            0
                                               9168             95                                            0
                                               9361             96                                            0
                                               9556             97                                            0
                                               9753             98                                            0
                                               9952             99                                            0
                                               9953            100                                            0


245 rows selected.

Even though the query results label these values according to the histogram type that they used to be notice that they all are Frequency histograms, with no emdpoint_repeat_count other than 0.

Since this process might be used to populate dev and test databases with production data (that has been obfuscated before the developers and testers can use it) this would produce some possibly serious performance issues which might otherwise remain unexplained. Using the proper utilities really is required in such situations.

Because it can be difficult to tell just what has changed.

Advertisements

Blog at WordPress.com.