11 Replies Latest reply: Jan 15, 2010 5:36 PM by 746513 RSS

    MERGE statement and DB performance

    746513
      Hi,

      I am having issues with server performance after MERGE statement issued. Merge is conducted on the table with 500M rows. The tables that are merging contain from few hundreds to few thousands rows (the biggest one about 25k).
      Server: Red Hat
      DB: 10g Enterprise

      ********************
      MERGE /*+ USE_HASH(veh, tmp) */ INTO vehicle_trans_tb veh
      USING (SELECT *
      FROM tmp_vs_veh_trans_83469
      WHERE bu_unit_rnk = 1
      ) tmp
      ON (veh.rowid = tmp.v_veh_trans_rowid)
      WHEN MATCHED THEN UPDATE
      SET veh.vin = tmp.vin
      , veh.data_src_id = 'DDRVS'
      , veh.trans_vehicle_new_used_cd = DECODE(tmp.trans_vehicle_new_used_cd, 'N', 'N', 'U')
      , veh.trans_event_dt = tmp.trans_delivery_dt
      , veh.trans_delivery_dt = tmp.trans_delivery_dt
      , veh.bus_associate_cd = tmp.bus_assoc_cd
      , veh.cust_nbr = tmp.cust_nbr
      , veh.last_fileno = tmp.fileno
      , veh.last_recno = tmp.recno
      , veh.last_updt_dt = SYSDATE
      , veh.last_updt_process_id = 'DDRVS'
      WHEN NOT MATCHED THEN
      INSERT
      (
      acct_id
      , vin
      , vehicle_trans_nbr
      , data_src_id
      , trans_vehicle_new_used_cd
      , trans_disposed_retained_cd
      , trans_type_cd
      , trans_event_dt
      , trans_delivery_dt
      , bus_associate_cd
      , cust_nbr
      , insert_dt
      , last_fileno
      , last_recno
      , last_updt_dt
      , last_updt_process_id
      )
      VALUES
      (
      tmp.acct_id
      , tmp.vin
      , tmp.vehicle_trans_nbr
      , 'DDRVS'
      , DECODE(tmp.trans_vehicle_new_used_cd, 'N', 'N', 'U')
      , 'SR'
      , 'ACQ'
      , tmp.trans_delivery_dt
      , tmp.trans_delivery_dt
      , tmp.bus_assoc_cd
      , tmp.cust_nbr
      , SYSDATE
      , fileno
      , recno
      , SYSDATE
      , 'DDRVS'
      )
      **********************

      Merge table and merge index are partitioned. Merge works in parallel mode.

      The problem is, after issuing such statement the DB performance goes down rapidly. I/O rate increases, CPU load goes up to 200, execution itself takes about 2hrs.
      Do you guys know what could be wrong and are there any limitations to the MERGE statement? What could be improved or changed?
      Thanks
        • 1. Re: MERGE statement and DB performance
          sb92075
          Do you guys know what could be wrong and are there any limitations to the MERGE statement?
          Are there any bit mapped indexes involved?

          Post Operating System (OS) name & version for DB server system.
          Post results of
          SELECT * from v$version
          • 2. Re: MERGE statement and DB performance
            Nicolas.Gasparotto
            What about the same without the HINT ?

            Nicolas.
            • 3. Re: MERGE statement and DB performance
              746513
              No, there are no bitmap indexes at all and USE_HASH hint cannot be removed as this is production environment.


              SQL> select * from v$version;

              BANNER
              ----------------------------------------------------------------
              Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
              PL/SQL Release 10.2.0.4.0 - Production
              CORE 10.2.0.4.0 Production
              TNS for Linux: Version 10.2.0.4.0 - Production
              NLSRTL Version 10.2.0.4.0 - Production
              • 4. Re: MERGE statement and DB performance
                624104
                No, there are no bitmap indexes at all and USE_HASH hint cannot be removed as this is production environment.
                That description sounds exactly like a non-production env....

                Bitmap indexes are great for production if you have low cardinality and I'm pretty sure all my teachers/professors/mentors told me never to use hints in production env...
                • 5. Re: MERGE statement and DB performance
                  746513
                  I know, but the thing is, there is no low-cardinality in neither columns indexed. Also hint usage is justified. USE_HASH may not work well on large tables, but it works far better than nested loops given the volumes we process.
                  Any suggestions?

                  Forgot to add that I have 900 parallel servers setup to run at maximum. Does this consume such high server and DB resources? Or is it the hint thing ?
                  • 6. Re: MERGE statement and DB performance
                    sb92075
                    FROM tmp_vs_veh_trans_83469
                    The general rule for Oracle is that "temp" tables are not needed.
                    Are statistics refreshed before & after MERGE executes?

                    In round number, what % of table gets changed?

                    Edited by: sb92075 on Jan 14, 2010 10:00 AM
                    • 7. Re: MERGE statement and DB performance
                      Nicolas.Gasparotto
                      Forgot to add that I have 900 parallel servers setup to run at maximum
                      What do you mean ? Is it PARALLEL_MAX_SERVERS=900 ? Looks very high too me. What about your explain plan ?
                      Stats up-to-date ? Number of rows involved ? Indexes ?

                      Nicolas.
                      • 8. Re: MERGE statement and DB performance
                        riedelme
                        I haven't been impressed by MERGE performance, although its possible I haven't found the precise circumstances where it is most effective. In particular I've found that merge can be slow when rows are mostly being inserted (performing lookups for rows that are not there).

                        How is performance on the lookup query? It it properly lindexed for the volume of data it retrieves?

                        PQO helps with sorts and full table scans (which are often used by hash joins). It can help or hurt performance. Are you sure it is helping you?

                        How much data are you processing with the MERGE (bytes and rows)?
                        • 9. Re: MERGE statement and DB performance
                          746513
                          --Are statistics refreshed before & after MERGE executes?
                          Unfortunately, not. But they're not getting too stale as statistics gets refreshed every night anyway.

                          --In round number, what % of table gets chang
                          Very little, if we compare 20.000 records to 500.000.000

                          --The general rule for Oracle is that "temp" tables are not needed.
                          What do you mean? There are not tem tables. Just the "temp" name
                          • 10. Re: MERGE statement and DB performance
                            Tubby
                            I'm curious about
                            ON (veh.rowid = tmp.v_veh_trans_rowid)
                            What have you done / are you doing to be able to map a rowid? Or do you actually have a custom column named 'rowid' in your table?

                            Can you provide us an explain plan for the current statement?
                            • 11. Re: MERGE statement and DB performance
                              746513
                              Thanks everybody for your responses so far.
                              I would provide the execution plan for that statement if the merging table existed. Unfortunately it gets deleted straight after the merges is finished.
                              I can tell you that it does 2 full joins on the merged and merging tables. I have also run the SQL optimizer in order to prefrom better execution plan. The thing is it cannot be implemented as the second table is always different. Therefore optimizer cannot work out same plan for very similar merge statements.

                              Anyway, it all worked in acceptable way. Things got worsen few weeks ago. Why? No idea. No parameters change, no infrastructure change. Only the main table grew.

                              I agree, the merge statement is not perfect, with the use_hash hint, and fact the statistics do not get collected before the executions. But it really worked in accpetable way. Now, is just terrible.

                              I would suspect the max_servers allowance is the problem. PX send blk event is pretty high for DB not only when merge runs. Most of the performance activity is the user I/O. I also checked the server and quite often there are 400-900 ora_p0* processes running. No wonder it takes that long. What does it mean? Processes cannot get get access to the disk fast enough? Do processes wait for each other to finish? Is infrastructure not powerful enough (8 processors on board) ?

                              Ideas ?