Insert Into queries take too much time in Apache spark

javaoracleoracle-12c

I am using Oracle Database with Apache spark 2.3.0

In my code I am writing final output (after all the processing) in three tables using below spark command.

billableChargeKDF.write().mode("append").format("jdbc").option("url", connection).option("dbtable", "CI_BILL_CHG_K").save();
billableChargeDF.write().mode("append").format("jdbc").option("url", connection).option("dbtable", "CI_BILL_CHG").save();
billableChargeSqDF.write().mode("append").format("jdbc").option("url", connection).option("dbtable", "CI_BCHG_SQ").save();

Each of the three Dataframes has 130 Partions. Each dataframe is writing 30 Million rows.

I am using 130 Cores, 26 Executors (Each executor is having 30GB Memory), 2 Worker machines(each having 88 virtual cores and 502.GB Memory)

After analyzing AWR report I found that above INSERT INTO queries takes most per Execution time.

Is there anyway I can reduce same?

Is there anyway I can improve performance?

EDIT

Also High Watermark contention is 30% of DB time.

DB machine has 16 Cores and 264GB.

AWR REPORT DATA

AWR image 1

AWR image 2

EDIT:

I have ran this query 

SELECT tablespace_name, extent_id, blocks, bytes,segment_name FROM user_extents WHERE segment_name = 'CI_BILL_CHG';

Below is the result if it can help.

CISTS_01    0   128 1048576 CI_BILL_CHG
CISTS_01    1   128 1048576 CI_BILL_CHG
CISTS_01    2   128 1048576 CI_BILL_CHG
CISTS_01    3   128 1048576 CI_BILL_CHG
CISTS_01    4   128 1048576 CI_BILL_CHG
CISTS_01    5   128 1048576 CI_BILL_CHG
CISTS_01    6   128 1048576 CI_BILL_CHG
CISTS_01    7   128 1048576 CI_BILL_CHG
CISTS_01    8   128 1048576 CI_BILL_CHG
CISTS_01    9   128 1048576 CI_BILL_CHG
CISTS_01    10  128 1048576 CI_BILL_CHG
CISTS_01    11  128 1048576 CI_BILL_CHG
CISTS_01    12  128 1048576 CI_BILL_CHG
CISTS_01    13  128 1048576 CI_BILL_CHG
CISTS_01    14  128 1048576 CI_BILL_CHG
CISTS_01    15  128 1048576 CI_BILL_CHG
CISTS_01    16  128 1048576 CI_BILL_CHG
CISTS_01    17  128 1048576 CI_BILL_CHG
CISTS_01    18  128 1048576 CI_BILL_CHG
CISTS_01    19  128 1048576 CI_BILL_CHG
CISTS_01    20  128 1048576 CI_BILL_CHG
CISTS_01    21  128 1048576 CI_BILL_CHG
CISTS_01    22  128 1048576 CI_BILL_CHG
CISTS_01    23  128 1048576 CI_BILL_CHG
CISTS_01    24  128 1048576 CI_BILL_CHG
CISTS_01    25  128 1048576 CI_BILL_CHG
CISTS_01    26  128 1048576 CI_BILL_CHG
CISTS_01    27  128 1048576 CI_BILL_CHG
CISTS_01    28  128 1048576 CI_BILL_CHG
CISTS_01    29  128 1048576 CI_BILL_CHG
CISTS_01    30  128 1048576 CI_BILL_CHG
CISTS_01    31  128 1048576 CI_BILL_CHG
CISTS_01    32  128 1048576 CI_BILL_CHG
CISTS_01    33  128 1048576 CI_BILL_CHG
CISTS_01    34  128 1048576 CI_BILL_CHG
CISTS_01    35  128 1048576 CI_BILL_CHG
CISTS_01    36  128 1048576 CI_BILL_CHG
CISTS_01    37  128 1048576 CI_BILL_CHG
CISTS_01    38  128 1048576 CI_BILL_CHG
CISTS_01    39  128 1048576 CI_BILL_CHG
CISTS_01    40  128 1048576 CI_BILL_CHG
CISTS_01    41  128 1048576 CI_BILL_CHG
CISTS_01    42  128 1048576 CI_BILL_CHG
CISTS_01    43  128 1048576 CI_BILL_CHG
CISTS_01    44  128 1048576 CI_BILL_CHG
CISTS_01    45  128 1048576 CI_BILL_CHG
CISTS_01    46  128 1048576 CI_BILL_CHG
CISTS_01    47  128 1048576 CI_BILL_CHG
CISTS_01    48  128 1048576 CI_BILL_CHG
CISTS_01    49  128 1048576 CI_BILL_CHG
CISTS_01    50  128 1048576 CI_BILL_CHG
CISTS_01    51  128 1048576 CI_BILL_CHG
CISTS_01    52  128 1048576 CI_BILL_CHG
CISTS_01    53  128 1048576 CI_BILL_CHG
CISTS_01    54  128 1048576 CI_BILL_CHG
CISTS_01    55  128 1048576 CI_BILL_CHG
CISTS_01    56  128 1048576 CI_BILL_CHG
CISTS_01    57  128 1048576 CI_BILL_CHG
CISTS_01    58  128 1048576 CI_BILL_CHG
CISTS_01    59  128 1048576 CI_BILL_CHG
CISTS_01    60  128 1048576 CI_BILL_CHG
CISTS_01    61  128 1048576 CI_BILL_CHG
CISTS_01    62  128 1048576 CI_BILL_CHG
CISTS_01    63  128 1048576 CI_BILL_CHG
CISTS_01    64  1024    8388608 CI_BILL_CHG
CISTS_01    65  1024    8388608 CI_BILL_CHG
CISTS_01    66  1024    8388608 CI_BILL_CHG
CISTS_01    67  1024    8388608 CI_BILL_CHG
CISTS_01    68  1024    8388608 CI_BILL_CHG
CISTS_01    69  1024    8388608 CI_BILL_CHG
CISTS_01    70  1024    8388608 CI_BILL_CHG
CISTS_01    71  1024    8388608 CI_BILL_CHG
CISTS_01    72  1024    8388608 CI_BILL_CHG
CISTS_01    73  1024    8388608 CI_BILL_CHG
CISTS_01    74  1024    8388608 CI_BILL_CHG
CISTS_01    75  1024    8388608 CI_BILL_CHG
CISTS_01    76  1024    8388608 CI_BILL_CHG
CISTS_01    77  1024    8388608 CI_BILL_CHG
CISTS_01    78  1024    8388608 CI_BILL_CHG
CISTS_01    79  1024    8388608 CI_BILL_CHG
CISTS_01    80  1024    8388608 CI_BILL_CHG
CISTS_01    81  1024    8388608 CI_BILL_CHG
CISTS_01    82  1024    8388608 CI_BILL_CHG
CISTS_01    83  1024    8388608 CI_BILL_CHG
CISTS_01    84  1024    8388608 CI_BILL_CHG
CISTS_01    85  1024    8388608 CI_BILL_CHG
CISTS_01    86  1024    8388608 CI_BILL_CHG
CISTS_01    87  1024    8388608 CI_BILL_CHG
CISTS_01    88  1024    8388608 CI_BILL_CHG
CISTS_01    89  1024    8388608 CI_BILL_CHG
CISTS_01    90  1024    8388608 CI_BILL_CHG
CISTS_01    91  1024    8388608 CI_BILL_CHG
CISTS_01    92  1024    8388608 CI_BILL_CHG
CISTS_01    93  1024    8388608 CI_BILL_CHG
CISTS_01    94  1024    8388608 CI_BILL_CHG
CISTS_01    95  1024    8388608 CI_BILL_CHG
CISTS_01    96  1024    8388608 CI_BILL_CHG
CISTS_01    97  1024    8388608 CI_BILL_CHG
CISTS_01    98  1024    8388608 CI_BILL_CHG
CISTS_01    99  1024    8388608 CI_BILL_CHG
CISTS_01    100 1024    8388608 CI_BILL_CHG
CISTS_01    101 1024    8388608 CI_BILL_CHG
CISTS_01    102 1024    8388608 CI_BILL_CHG
CISTS_01    103 1024    8388608 CI_BILL_CHG
CISTS_01    104 1024    8388608 CI_BILL_CHG
CISTS_01    105 1024    8388608 CI_BILL_CHG
CISTS_01    106 1024    8388608 CI_BILL_CHG
CISTS_01    107 1024    8388608 CI_BILL_CHG
CISTS_01    108 1024    8388608 CI_BILL_CHG
CISTS_01    109 1024    8388608 CI_BILL_CHG
CISTS_01    110 1024    8388608 CI_BILL_CHG
CISTS_01    111 1024    8388608 CI_BILL_CHG
CISTS_01    112 1024    8388608 CI_BILL_CHG
CISTS_01    113 1024    8388608 CI_BILL_CHG
CISTS_01    114 1024    8388608 CI_BILL_CHG
CISTS_01    115 1024    8388608 CI_BILL_CHG
CISTS_01    116 1024    8388608 CI_BILL_CHG
CISTS_01    117 1024    8388608 CI_BILL_CHG
CISTS_01    118 1024    8388608 CI_BILL_CHG
CISTS_01    119 1024    8388608 CI_BILL_CHG
CISTS_01    120 1024    8388608 CI_BILL_CHG
CISTS_01    121 1024    8388608 CI_BILL_CHG
CISTS_01    122 1024    8388608 CI_BILL_CHG
CISTS_01    123 1024    8388608 CI_BILL_CHG
CISTS_01    124 1024    8388608 CI_BILL_CHG
CISTS_01    125 1024    8388608 CI_BILL_CHG
CISTS_01    126 1024    8388608 CI_BILL_CHG
CISTS_01    127 1024    8388608 CI_BILL_CHG
CISTS_01    128 1024    8388608 CI_BILL_CHG
CISTS_01    129 1024    8388608 CI_BILL_CHG
CISTS_01    130 1024    8388608 CI_BILL_CHG
CISTS_01    131 1024    8388608 CI_BILL_CHG
CISTS_01    132 1024    8388608 CI_BILL_CHG
CISTS_01    133 1024    8388608 CI_BILL_CHG
CISTS_01    134 1024    8388608 CI_BILL_CHG
CISTS_01    135 1024    8388608 CI_BILL_CHG
CISTS_01    136 1024    8388608 CI_BILL_CHG
CISTS_01    137 1024    8388608 CI_BILL_CHG
CISTS_01    138 1024    8388608 CI_BILL_CHG
CISTS_01    139 1024    8388608 CI_BILL_CHG
CISTS_01    140 1024    8388608 CI_BILL_CHG
CISTS_01    141 1024    8388608 CI_BILL_CHG
CISTS_01    142 1024    8388608 CI_BILL_CHG
CISTS_01    143 1024    8388608 CI_BILL_CHG
CISTS_01    144 1024    8388608 CI_BILL_CHG
CISTS_01    145 1024    8388608 CI_BILL_CHG
CISTS_01    146 1024    8388608 CI_BILL_CHG
CISTS_01    147 1024    8388608 CI_BILL_CHG
CISTS_01    148 1024    8388608 CI_BILL_CHG
CISTS_01    149 1024    8388608 CI_BILL_CHG
CISTS_01    150 1024    8388608 CI_BILL_CHG
CISTS_01    151 1024    8388608 CI_BILL_CHG
CISTS_01    152 1024    8388608 CI_BILL_CHG
CISTS_01    153 1024    8388608 CI_BILL_CHG
CISTS_01    154 1024    8388608 CI_BILL_CHG
CISTS_01    155 1024    8388608 CI_BILL_CHG
CISTS_01    156 1024    8388608 CI_BILL_CHG
CISTS_01    157 1024    8388608 CI_BILL_CHG
CISTS_01    158 1024    8388608 CI_BILL_CHG
CISTS_01    159 1024    8388608 CI_BILL_CHG
CISTS_01    160 1024    8388608 CI_BILL_CHG
CISTS_01    161 1024    8388608 CI_BILL_CHG
CISTS_01    162 1024    8388608 CI_BILL_CHG
CISTS_01    163 1024    8388608 CI_BILL_CHG
CISTS_01    164 1024    8388608 CI_BILL_CHG
CISTS_01    165 1024    8388608 CI_BILL_CHG
CISTS_01    166 1024    8388608 CI_BILL_CHG
CISTS_01    167 1024    8388608 CI_BILL_CHG
CISTS_01    168 1024    8388608 CI_BILL_CHG
CISTS_01    169 1024    8388608 CI_BILL_CHG
CISTS_01    170 1024    8388608 CI_BILL_CHG
CISTS_01    171 1024    8388608 CI_BILL_CHG
CISTS_01    172 1024    8388608 CI_BILL_CHG
CISTS_01    173 1024    8388608 CI_BILL_CHG
CISTS_01    174 1024    8388608 CI_BILL_CHG
CISTS_01    175 1024    8388608 CI_BILL_CHG
CISTS_01    176 1024    8388608 CI_BILL_CHG
CISTS_01    177 1024    8388608 CI_BILL_CHG
CISTS_01    178 1024    8388608 CI_BILL_CHG
CISTS_01    179 1024    8388608 CI_BILL_CHG
CISTS_01    180 1024    8388608 CI_BILL_CHG
CISTS_01    181 1024    8388608 CI_BILL_CHG
CISTS_01    182 1024    8388608 CI_BILL_CHG
CISTS_01    183 1024    8388608 CI_BILL_CHG
CISTS_01    184 8192    67108864    CI_BILL_CHG
CISTS_01    185 8192    67108864    CI_BILL_CHG
CISTS_01    186 8192    67108864    CI_BILL_CHG
CISTS_01    187 8192    67108864    CI_BILL_CHG
CISTS_01    188 8192    67108864    CI_BILL_CHG
CISTS_01    189 8192    67108864    CI_BILL_CHG
CISTS_01    190 8192    67108864    CI_BILL_CHG
CISTS_01    191 8192    67108864    CI_BILL_CHG
CISTS_01    192 8192    67108864    CI_BILL_CHG
CISTS_01    193 8192    67108864    CI_BILL_CHG
CISTS_01    194 8192    67108864    CI_BILL_CHG
CISTS_01    195 8192    67108864    CI_BILL_CHG
CISTS_01    196 8192    67108864    CI_BILL_CHG
CISTS_01    197 8192    67108864    CI_BILL_CHG
CISTS_01    198 8192    67108864    CI_BILL_CHG
CISTS_01    199 8192    67108864    CI_BILL_CHG
CISTS_01    200 8192    67108864    CI_BILL_CHG
CISTS_01    201 8192    67108864    CI_BILL_CHG
CISTS_01    202 8192    67108864    CI_BILL_CHG
CISTS_01    203 8192    67108864    CI_BILL_CHG
CISTS_01    204 8192    67108864    CI_BILL_CHG
CISTS_01    205 8192    67108864    CI_BILL_CHG
CISTS_01    206 8192    67108864    CI_BILL_CHG
CISTS_01    207 8192    67108864    CI_BILL_CHG
CISTS_01    208 8192    67108864    CI_BILL_CHG
CISTS_01    209 8192    67108864    CI_BILL_CHG
CISTS_01    210 8192    67108864    CI_BILL_CHG
CISTS_01    211 8192    67108864    CI_BILL_CHG
CISTS_01    212 8192    67108864    CI_BILL_CHG
CISTS_01    213 8192    67108864    CI_BILL_CHG
CISTS_01    214 8192    67108864    CI_BILL_CHG
CISTS_01    215 8192    67108864    CI_BILL_CHG
CISTS_01    216 8192    67108864    CI_BILL_CHG
CISTS_01    217 8192    67108864    CI_BILL_CHG
CISTS_01    218 8192    67108864    CI_BILL_CHG
CISTS_01    219 8192    67108864    CI_BILL_CHG
CISTS_01    220 8192    67108864    CI_BILL_CHG
CISTS_01    221 8192    67108864    CI_BILL_CHG
CISTS_01    222 8192    67108864    CI_BILL_CHG
CISTS_01    223 8192    67108864    CI_BILL_CHG
CISTS_01    224 8192    67108864    CI_BILL_CHG
CISTS_01    225 8192    67108864    CI_BILL_CHG
CISTS_01    226 8192    67108864    CI_BILL_CHG
CISTS_01    227 8192    67108864    CI_BILL_CHG
CISTS_01    228 8192    67108864    CI_BILL_CHG
CISTS_01    229 8192    67108864    CI_BILL_CHG
CISTS_01    230 8192    67108864    CI_BILL_CHG
CISTS_01    231 8192    67108864    CI_BILL_CHG
CISTS_01    232 8192    67108864    CI_BILL_CHG
CISTS_01    233 8192    67108864    CI_BILL_CHG
CISTS_01    234 8192    67108864    CI_BILL_CHG
CISTS_01    235 8192    67108864    CI_BILL_CHG
CISTS_01    236 8192    67108864    CI_BILL_CHG
CISTS_01    237 8192    67108864    CI_BILL_CHG
CISTS_01    238 8192    67108864    CI_BILL_CHG
CISTS_01    239 8192    67108864    CI_BILL_CHG
CISTS_01    240 8192    67108864    CI_BILL_CHG
CISTS_01    241 8192    67108864    CI_BILL_CHG
CISTS_01    242 8192    67108864    CI_BILL_CHG
CISTS_01    243 8192    67108864    CI_BILL_CHG
CISTS_01    244 8192    67108864    CI_BILL_CHG
CISTS_01    245 8192    67108864    CI_BILL_CHG
CISTS_01    246 8192    67108864    CI_BILL_CHG
CISTS_01    247 8192    67108864    CI_BILL_CHG
CISTS_01    248 8192    67108864    CI_BILL_CHG
CISTS_01    249 8192    67108864    CI_BILL_CHG
CISTS_01    250 8192    67108864    CI_BILL_CHG
CISTS_01    251 8192    67108864    CI_BILL_CHG
CISTS_01    252 8192    67108864    CI_BILL_CHG
CISTS_01    253 8192    67108864    CI_BILL_CHG
CISTS_01    254 8192    67108864    CI_BILL_CHG
CISTS_01    255 8192    67108864    CI_BILL_CHG
CISTS_01    256 8192    67108864    CI_BILL_CHG
CISTS_01    257 8192    67108864    CI_BILL_CHG
CISTS_01    258 8192    67108864    CI_BILL_CHG
CISTS_01    259 8192    67108864    CI_BILL_CHG
CISTS_01    260 8192    67108864    CI_BILL_CHG
CISTS_01    261 8192    67108864    CI_BILL_CHG
CISTS_01    262 8192    67108864    CI_BILL_CHG
CISTS_01    263 8192    67108864    CI_BILL_CHG
CISTS_01    264 8192    67108864    CI_BILL_CHG
CISTS_01    265 8192    67108864    CI_BILL_CHG
CISTS_01    266 8192    67108864    CI_BILL_CHG
CISTS_01    267 8192    67108864    CI_BILL_CHG
CISTS_01    268 8192    67108864    CI_BILL_CHG
CISTS_01    269 8192    67108864    CI_BILL_CHG
CISTS_01    270 8192    67108864    CI_BILL_CHG
CISTS_01    271 8192    67108864    CI_BILL_CHG
CISTS_01    272 8192    67108864    CI_BILL_CHG
CISTS_01    273 8192    67108864    CI_BILL_CHG
CISTS_01    274 8192    67108864    CI_BILL_CHG
CISTS_01    275 8192    67108864    CI_BILL_CHG
CISTS_01    276 8192    67108864    CI_BILL_CHG
CISTS_01    277 8192    67108864    CI_BILL_CHG
CISTS_01    278 8192    67108864    CI_BILL_CHG
CISTS_01    279 8192    67108864    CI_BILL_CHG
CISTS_01    280 8192    67108864    CI_BILL_CHG
CISTS_01    281 8192    67108864    CI_BILL_CHG
CISTS_01    282 8192    67108864    CI_BILL_CHG
CISTS_01    283 8192    67108864    CI_BILL_CHG
CISTS_01    284 8192    67108864    CI_BILL_CHG
CISTS_01    285 8192    67108864    CI_BILL_CHG
CISTS_01    286 8192    67108864    CI_BILL_CHG
CISTS_01    287 8192    67108864    CI_BILL_CHG
CISTS_01    288 8192    67108864    CI_BILL_CHG
CISTS_01    289 8192    67108864    CI_BILL_CHG
CISTS_01    290 8192    67108864    CI_BILL_CHG
CISTS_01    291 8192    67108864    CI_BILL_CHG
CISTS_01    292 8192    67108864    CI_BILL_CHG
CISTS_01    293 8192    67108864    CI_BILL_CHG
CISTS_01    294 8192    67108864    CI_BILL_CHG
CISTS_01    295 8192    67108864    CI_BILL_CHG
CISTS_01    296 8192    67108864    CI_BILL_CHG
CISTS_01    297 8192    67108864    CI_BILL_CHG
CISTS_01    298 8192    67108864    CI_BILL_CHG
CISTS_01    299 8192    67108864    CI_BILL_CHG
CISTS_01    300 8192    67108864    CI_BILL_CHG
CISTS_01    301 8192    67108864    CI_BILL_CHG
CISTS_01    302 8192    67108864    CI_BILL_CHG
CISTS_01    303 8192    67108864    CI_BILL_CHG
CISTS_01    304 8192    67108864    CI_BILL_CHG
CISTS_01    305 8192    67108864    CI_BILL_CHG
CISTS_01    306 8192    67108864    CI_BILL_CHG
CISTS_01    307 8192    67108864    CI_BILL_CHG
CISTS_01    308 8192    67108864    CI_BILL_CHG
CISTS_01    309 8192    67108864    CI_BILL_CHG
CISTS_01    310 8192    67108864    CI_BILL_CHG
CISTS_01    311 8192    67108864    CI_BILL_CHG
CISTS_01    312 8192    67108864    CI_BILL_CHG
CISTS_01    313 8192    67108864    CI_BILL_CHG
CISTS_01    314 8192    67108864    CI_BILL_CHG
CISTS_01    315 8192    67108864    CI_BILL_CHG
CISTS_01    316 8192    67108864    CI_BILL_CHG
CISTS_01    317 8192    67108864    CI_BILL_CHG
CISTS_01    318 8192    67108864    CI_BILL_CHG
CISTS_01    319 8192    67108864    CI_BILL_CHG
CISTS_01    320 8192    67108864    CI_BILL_CHG
CISTS_01    321 8192    67108864    CI_BILL_CHG
CISTS_01    322 8192    67108864    CI_BILL_CHG
CISTS_01    323 8192    67108864    CI_BILL_CHG
CISTS_01    324 8192    67108864    CI_BILL_CHG
CISTS_01    325 8192    67108864    CI_BILL_CHG
CISTS_01    326 8192    67108864    CI_BILL_CHG
CISTS_01    327 8192    67108864    CI_BILL_CHG
CISTS_01    328 8192    67108864    CI_BILL_CHG
CISTS_01    329 8192    67108864    CI_BILL_CHG
CISTS_01    330 8192    67108864    CI_BILL_CHG

Best Answer

The time distribution on your inserts:

25q1.... : 3.1% CPU,  0% I/O
dtff.... : 2.01% CPU, 0% I/O
3qf7.... : 8.31% CPU, 0% I/O

This is not normal, and the rest of the time is spent waiting on something else. This could be caused by simple transactional locking (TX contention, TM contention) but there is nothing that indicates this in your top 10 events. But there are other waits.

You have a high number of HW contention waits. HW contention waits occur when data is concurrently inserted into the same table and the database needs to allocate new extents for the segment. This can be be mitigated by increasing the extent size for the table. The other waits can be simply the consequences of this.

You did not say anything about the table. HW contention is usually not a problem on tables with basic data types. But it is a significant problem on tables with Basicfile type LOB columns. Oracle 11g, by default, creates Basicfile LOBs, unless you explicitly specify Securefile LOB type on table creation or change the db_securefile parameter before creating the table. Securefile LOBs are designed to perform much better, including concurrency situation like the above.

If these tables have LOB columns, and they are Basicfile LOBs, it is recommended to migrate them to Securefile LOBs. Then perform your test again to see if it helped.

If these tables do not have LOB columns, then you could try this with bigger extent sizes. For a quick test without any big change, you could try manually preallocating extents and see if it improves performance. For example: alter table ci_bill_chg allocate extent (size 10G). If that helps, then you can go in this direction.

Related Question