1 2 Previous Next 16 Replies Latest reply: Nov 22, 2013 10:25 AM by Rajesh Hariharan RSS

    Merge query running for days

    Rajesh Hariharan

      Friends,

       

      I have a merge query running for more than 2 days in Oracle Exadata environment,this merge query is being run by the ETL tool, we have indexes in place,parallelism enabled .Table T has more than 100 million rows  and S has 40 million rows.

       

      Below is the query for your reference.Please let me know your thoughts on this.

       

      create or replace procedure proc_update as

       

      merge /*+ PARALLEL */ into schema.table1 T

      using(

      select /*+ PARALLEL */ s.c1,s.c2, 'N' flag,(s.from_date-1/86400) to_date,s.insrt_dt,s.re_id from schema.table2 s

      ) S

      on (s.c1=T.c1 and s.c2=t.c2+1)

      when matched

      then update set

      T.flag=S.flag,

      T.to_date=S.to_date,

      T.upd_dt=s.insrt_dt,

      T.re_id=s.re_id

      where T.flag='Y';

      commit;

       

       

      end;

        • 1. Re: Merge query running for days
          Nikolay Savvinov

          Hi,

           

          1) what's your Oracle version

          2) do you have the Diagnostic and Tuning Pack license?

           

          Best regards,

            Nikolay

          • 2. Re: Merge query running for days
            Rajesh Hariharan

            Hi Nikolay ,

             

            1 ) The version is :  Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit .

            2) What do mean by diagnostic pack ,I ran the ADDM report on the database and found nothing significant ,no waiting or blocking sessions.

             

            Please let me know if you need any more info.

            • 3. Re: Merge query running for days
              Nikolay Savvinov

              Hi,

               

              1) the Tuning and Diagnostic Pack license is what you need to have in order to run ADDM reports and other things like AWR, ASH, SQL real-time monitoring etc.

              2) post SQL real time monitor:

               

              select dbms_sqltune.report_sql_monitor(sql_id=>:sql_id_of_your_merge_statement) from dual

               

              or

               

              select dbms_sqltune.report_sql_monitor(sid=>:sid_of_the_session_running_the_merge_statement) from dual;

               

              Best regards,

                Nikolay

              • 4. Re: Merge query running for days
                Rajesh Hariharan

                Here is the report i got out of your query,

                 

                "(CLOB) SQL Monitoring Report

                 

                 

                SQL Text

                ------------------------------

                merge /*+ PARALLEL */ into schema.table1 T using(select /*+ PARALLEL */ s.c1,s.c2, 'N' flag,(s.from_date-1/86400) to_date,s.insrt_dt,s.re_id from schema.table2 s

                ) S on (s.c1=T.c1 and s.c2=t.c2+1) when matched then update set T.flag=S.flag, T.to_date=S.to_date,T.upd_dt=s.insrt_dt,T.re_id=s.re_id where T.flag='Y';

                 

                 

                Global Information

                ------------------------------

                Status                                 :  EXECUTING           

                Instance ID                            :  2                   

                Session                                :  Schema1 (982:1227)      

                SQL ID                                 :  id       

                SQL Execution ID                 :  33554432            

                Execution Started                :  11/19/2013 21:24:26 

                First Refresh Time               :  11/19/2013 21:24:33 

                Last Refresh Time               :  11/22/2013 00:08:10 

                Duration                             :  182624s             

                Module/Action                                              :  JDBC Thin Client/-  

                Service                                                         :  db.domain.net

                Program                                                        :  JDBC Thin Client    

                PLSQL Entry Ids (Object/Subprogram)          :  537255,1            

                PLSQL Current Ids (Object/Subprogram)       :  537255,1            

                 

                 

                Global Stats

                ====================================================================================================================================

                | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |  Cell   |

                | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |

                ====================================================================================================================================

                |  190269 |   67376 |   119320 |        0.00 |         207 |     3313 |       52 |     7G | 187M |   1TB |  141K |  33GB |  -2.04% |

                ====================================================================================================================================

                 

                 

                Parallel Execution Details (DOP=48 , Servers Allocated=96)

                =================================================================================================================================================================================================================

                |      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |  Cell   |               Wait Events               |

                |                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |               (sample #)                |

                =================================================================================================================================================================================================================

                | PX Coordinator | QC    |         |  188555 |   66113 |   118922 |        0.00 |         207 |     3313 |          |     7G | 187M |   1TB |  3083 | 617MB |         | gc cr block 2-way (1)                   |

                |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr block congested (1)               |

                |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr disk read (3)                     |

                |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr grant 2-way (5)                 ..."

                • 5. Re: Merge query running for days
                  Nikolay Savvinov


                  Hi,

                   

                  can't you see the report truncated? Post the full version, it shouldn't be too big.

                  Use "Courier New" font (in Advanced Editor mode) to make it readable.

                   

                  Best regards,

                    Nikolay

                  • 6. Re: Merge query running for days
                    Rajesh Hariharan

                    Nikolay,

                     

                    This is the full report i am getting .. i am trying to export it to txt and this is all i am getting.

                    • 7. Re: Merge query running for days
                      Rajesh Hariharan

                      Hi Nikolay ,

                       

                      Here is the full report ...

                       

                      SQL Monitoring Report

                       

                       

                      SQL Text

                      ------------------------------

                      merge /*+ PARALLEL */ into schema.table1 T using(select /*+ PARALLEL */ s.c1,s.c2, 'N' flag,(s.from_date-1/86400) to_date,s.insrt_dt,s.re_id from schema.table2 s

                      ) S on (s.c1=T.c1 and s.c2=t.c2+1) when matched then update set T.flag=S.flag, T.to_date=S.to_date,T.upd_dt=s.insrt_dt,T.re_id=s.re_id where T.flag='Y';

                       

                       

                      Global Information

                      ------------------------------

                      Status                                 :  EXECUTING

                      Instance ID                            :  2

                      Session                                :  schema (982:1227)

                      SQL ID                                 :  id

                      SQL Execution ID                       :  33554432

                      Execution Started                      :  11/19/2013 21:24:26

                      First Refresh Time                     :  11/19/2013 21:24:33

                      Last Refresh Time                      :  11/22/2013 02:46:38

                      Duration                               :  192134s

                      Module/Action                          :  JDBC Thin Client/-

                      Service                                :  domain.net

                      Program                                :  JDBC Thin Client

                      PLSQL Entry Ids (Object/Subprogram)    :  537255,1

                      PLSQL Current Ids (Object/Subprogram)  :  537255,1

                       

                       

                      Global Stats

                      ====================================================================================================================================

                      | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |  Cell   |

                      | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |

                      ====================================================================================================================================

                      |  200193 |   70070 |   126376 |        0.00 |         208 |     3488 |       52 |     7G | 198M |   1TB |  141K |  33GB |  -2.04% |

                      ====================================================================================================================================

                       

                       

                      Parallel Execution Details (DOP=48 , Servers Allocated=96)

                      =================================================================================================================================================================================================================

                      |      Name      | Type  | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  |  Other   | Buffer | Read | Read  | Write | Write |  Cell   |               Wait Events    |

                      |                |       |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |               (sample #)     |

                      =================================================================================================================================================================================================================

                      | PX Coordinator | QC    |         |  198427 |   68786 |   125946 |        0.00 |         208 |     3488 |          |     7G | 198M |   1TB |  3151 | 630MB |         | gc cr block 2-way (1)        |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr block congested (1)    |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr disk read (3)          |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr grant 2-way (5)        |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc cr request (2)            |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc current grant 2-way (745) |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc current grant congested (7)|

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc current multi block request (2)    |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | gc current request (24)      |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | buffer busy waits (154)      |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | log file switch completion (5)|

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | enq: FB - contention (5)     |

                      |                |       |         |         |         |          |             |             |          |          |        |      |       |       |       |         | cell single block physical read (35298) |

                      | p000           | Set 1 |       1 |      37 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2868 | 695MB | -72.41% | direct path read temp (4)    |

                      | p001           | Set 1 |       2 |      36 |      27 |     9.37 |             |             |          |          |        | 1146 | 278MB |  2873 | 696MB | -72.41% | direct path read temp (3)    |

                      | p002           | Set 1 |       3 |      36 |      27 |     9.30 |             |             |          |          |        | 1147 | 278MB |  2869 | 695MB | -72.41% | direct path read temp (3)    |

                      | p003           | Set 1 |       4 |      36 |      27 |     9.32 |             |             |          |          |        | 1146 | 278MB |  2863 | 693MB | -72.41% | direct path read temp (2)    |

                      | p004           | Set 1 |       5 |      35 |      27 |     8.46 |             |             |          |          |        | 1146 | 278MB |  2876 | 697MB | -72.41% | direct path read temp (1)    |

                      | p005           | Set 1 |       6 |      35 |      27 |     8.15 |             |             |          |          |        | 1147 | 278MB |  2869 | 695MB | -72.41% |                              |

                      | p006           | Set 1 |       7 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2878 | 697MB | -72.41% | direct path read temp (4)    |

                      | p007           | Set 1 |       8 |      38 |      27 |       11 |             |             |          |          |        | 1145 | 277MB |  2877 | 697MB | -72.41% |                              |

                      | p008           | Set 1 |       9 |      35 |      27 |     8.61 |             |             |          |          |        | 1148 | 278MB |  2870 | 695MB | -72.41% |                              |

                      | p009           | Set 1 |      10 |      35 |      27 |     8.54 |             |             |          |          |        | 1147 | 278MB |  2868 | 695MB | -72.41% |                              |

                      | p010           | Set 1 |      11 |      34 |      27 |     7.07 |             |             |          |          |        | 1147 | 278MB |  2867 | 694MB | -72.41% | direct path read temp (3)    |

                      | p011           | Set 1 |      12 |      36 |      27 |     8.97 |             |             |          |          |        | 1147 | 278MB |  2873 | 696MB | -72.41% | direct path read temp (1)    |

                      | p012           | Set 1 |      13 |      37 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2877 | 697MB | -72.41% | direct path read temp (4)    |

                      | p013           | Set 1 |      14 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2875 | 696MB | -72.41% | direct path read temp (2)    |

                      | p014           | Set 1 |      15 |      38 |      27 |       11 |             |             |          |          |        | 1148 | 278MB |  2869 | 695MB | -72.41% | direct path read temp (1)    |

                      | p015           | Set 1 |      16 |      35 |      27 |     8.10 |             |             |          |          |        | 1146 | 278MB |  2873 | 696MB | -72.41% | direct path read temp (1)    |

                      | p016           | Set 1 |      17 |      34 |      27 |     7.15 |             |             |          |          |        | 1146 | 278MB |  2872 | 696MB | -72.41% | direct path read temp (2)    |

                      | p017           | Set 1 |      18 |      36 |      27 |       10 |             |             |          |          |        | 1146 | 278MB |  2874 | 696MB | -72.41% | direct path read temp (1)    |

                      | p018           | Set 1 |      19 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2874 | 696MB | -72.41% | direct path read temp (2)    |

                      | p019           | Set 1 |      20 |      36 |      27 |     9.12 |             |             |          |          |        | 1147 | 278MB |  2872 | 696MB | -72.41% | direct path read temp (2)    |

                      | p020           | Set 1 |      21 |      36 |      27 |     9.48 |             |             |          |          |        | 1146 | 278MB |  2870 | 695MB | -72.41% | direct path read temp (1)    |

                      | p021           | Set 1 |      22 |      36 |      27 |       10 |             |        0.00 |          |          |        | 1146 | 278MB |  2873 | 696MB | -72.41% | direct path read temp (3)    |

                      | p022           | Set 1 |      23 |      38 |      27 |       11 |             |             |          |          |        | 1147 | 278MB |  2872 | 696MB | -72.41% | direct path read temp (1)    |

                      | p023           | Set 1 |      24 |      35 |      27 |     7.78 |             |             |          |          |        | 1147 | 278MB |  2871 | 695MB | -72.41% | direct path read temp (3)    |

                      | p024           | Set 1 |      25 |      34 |      27 |     7.51 |             |             |          |          |        | 1147 | 278MB |  2869 | 695MB | -72.41% | direct path read temp (2)    |

                      | p025           | Set 1 |      26 |      38 |      27 |       11 |             |             |          |          |        | 1146 | 278MB |  2874 | 696MB | -72.41% | direct path read temp (1)    |

                      | p026           | Set 1 |      27 |      34 |      27 |     7.41 |             |             |          |          |        | 1147 | 278MB |  2873 | 696MB | -72.41% |                              |

                      | p027           | Set 1 |      28 |      35 |      27 |     8.11 |             |             |          |          |        | 1147 | 278MB |  2870 | 695MB | -72.41% | direct path read temp (3)    |

                      | p028           | Set 1 |      29 |      34 |      27 |     7.51 |             |             |          |          |        | 1147 | 278MB |  2875 | 696MB | -72.41% | direct path read temp (3)    |

                      | p029           | Set 1 |      30 |      36 |      27 |     9.46 |             |             |          |          |        | 1147 | 278MB |  2868 | 695MB | -72.41% | direct path read temp (1)    |

                      | p030           | Set 1 |      31 |      35 |      27 |     8.01 |             |             |          |          |        | 1146 | 278MB |  2869 | 695MB | -72.41% |                              |

                      | p031           | Set 1 |      32 |      36 |      27 |       10 |             |             |          |          |        | 1146 | 278MB |  2864 | 694MB | -72.41% | direct path read temp (3)    |

                      | p032           | Set 1 |      33 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2874 | 696MB | -72.41% | direct path read temp (1)    |

                      | p033           | Set 1 |      34 |      35 |      27 |     8.43 |             |             |          |          |        | 1147 | 278MB |  2871 | 695MB | -72.41% | direct path read temp (1)    |

                      | p034           | Set 1 |      35 |      36 |      27 |     9.18 |             |             |          |          |        | 1145 | 277MB |  2873 | 696MB | -72.41% | direct path read temp (2)    |

                      | p035           | Set 1 |      36 |      35 |      27 |     8.44 |             |             |          |          |        | 1146 | 278MB |  2876 | 697MB | -72.41% | direct path read temp (2)    |

                      | p036           | Set 1 |      37 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2873 | 696MB | -72.41% | direct path read temp (3)    |

                      | p037           | Set 1 |      38 |      35 |      27 |     8.45 |             |             |          |          |        | 1145 | 277MB |  2872 | 696MB | -72.41% | direct path read temp (3)    |

                      | p038           | Set 1 |      39 |      34 |      27 |     7.66 |             |             |          |          |        | 1146 | 278MB |  2871 | 695MB | -72.41% | direct path read temp (1)    |

                      | p039           | Set 1 |      40 |      37 |      27 |       11 |             |             |          |          |        | 1146 | 278MB |  2874 | 696MB | -72.41% | direct path read temp (2)    |

                      | p040           | Set 1 |      41 |      88 |      26 |     9.13 |             |             |          |       52 |        | 1146 | 278MB |  2869 | 695MB | -72.41% | direct path read temp (1)    |

                      | p041           | Set 1 |      42 |      36 |      27 |     8.95 |             |             |          |          |        | 1145 | 277MB |  2872 | 696MB | -72.41% | direct path read temp (1)    |

                      | p042           | Set 1 |      43 |      36 |      27 |       10 |             |             |          |          |        | 1147 | 278MB |  2871 | 695MB | -72.41% |                              |

                      | p043           | Set 1 |      44 |      35 |      27 |     8.05 |             |             |          |          |        | 1147 | 278MB |  2872 | 696MB | -72.41% | direct path read temp (1)    |

                      | p044           | Set 1 |      45 |      36 |      27 |     8.87 |             |             |          |          |        | 1145 | 277MB |  2865 | 694MB | -72.41% | direct path read temp (1)    |

                      | p045           | Set 1 |      46 |      36 |      27 |     9.04 |             |             |          |          |        | 1147 | 278MB |  2871 | 695MB | -72.41% | direct path read temp (1)    |

                      | p046           | Set 1 |      47 |      35 |      27 |     8.76 |             |             |          |          |        | 1146 | 278MB |  2868 | 695MB | -72.41% |                              |

                      | p047           | Set 1 |      48 |      33 |      27 |     6.73 |             |             |          |          |        | 1147 | 278MB |  2876 | 697MB | -72.41% | direct path read temp (3)    |

                      =================================================================================================================================================================================================================

                       

                       

                      SQL Plan Monitoring Details (Plan Hash Value=1646525903)

                      =====================================================================================================================================================================================================================================

                      | Id   |              Operation              |    Name     |  Rows   | Cost  |   Time    |  Start  | Execs |   Rows   | Read  | Read  | Write | Write | Mem  | Temp | Activity |             Activity Detail           | Progress |

                      |      |                                     |             | (Estim) |       | Active(s) | Active  |       | (Actual) | Reqs  | Bytes | Reqs  | Bytes |      |      |   (%)    |               (# samples)             |          |

                      =====================================================================================================================================================================================================================================

                      |    0 | MERGE STATEMENT                     |             |         |       |       155 | +178025 |     1 |        0 |       |       |       |       |      |      |     0.32 | buffer busy waits (154)               |          |

                      |    1 |   MERGE                             | T   |         |       |    192037 |     +98 |     1 |        0 |  198M |   1TB |  3151 | 630MB |      |      |    99.43 | gc cr block 2-way (1) |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc cr block congested (1)             |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc cr disk read (3)  |           |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc cr grant 2-way (5) |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc cr request (2)    |           |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc current grant 2-way (745)          |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc current grant congested (7)        |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc current multi block request (2)    |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | gc current request (24)               |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | log file switch completion (5)        |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | Cpu (12141)          |           |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | enq: FB - contention (5)              |          |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | cell single block physical read (35298) |        |

                      |    2 |    PX COORDINATOR                   |             |         |       |    192037 |     +98 |    49 |      16M |       |       |       |       |      |      |     0.04 | Cpu (18)             |           |

                      | -> 3 |     PX SEND QC (RANDOM)             | :TQ10002    |      4M | 95693 |    192037 |     +99 |    48 |      16M |       |       |       |       |      |      |     0.01 | Cpu (6)              |           |

                      | -> 4 |      VIEW                           |             |         |       |    192037 |     +99 |    48 |      16M |       |       |       |       |      |      |          |                      |           |

                      | -> 5 |       HASH JOIN BUFFERED            |             |      4M | 95693 |    192127 |      +9 |    48 |      16M | 55032 |  13GB |  138K |  33GB | 283M |  36G |     0.21 | Cpu (20)             |       50% |

                      |      |                                     |             |         |       |           |         |       |          |       |       |       |       |      |      |          | direct path read temp (80)            |          |

                      |    6 |        PX RECEIVE                   |             |     43M | 22599 |        13 |      +9 |    48 |      43M |       |       |       |       |      |      |          |                      |           |

                      |    7 |         PX SEND HASH                | :TQ10000    |     43M | 22599 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      |    8 |          PX BLOCK ITERATOR          |             |     43M | 22599 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      |    9 |           TABLE ACCESS STORAGE FULL | S   |     43M | 22599 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      |   10 |        PX RECEIVE                   |             |    119M |  8388 |        90 |     +21 |    48 |     119M |       |       |       |       |      |      |          |                      |           |

                      |   11 |         PX SEND HASH                | :TQ10001    |    119M |  8388 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      |   12 |          PX BLOCK ITERATOR          |             |    119M |  8388 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      |   13 |           TABLE ACCESS STORAGE FULL | T           |    119M |  8388 |           |         |       |          |       |       |       |       |      |      |          |                      |           |

                      =====================================================================================================================================================================================

                      • 8. Re: Merge query running for days
                        Nikolay Savvinov


                        Hi,

                         

                        interesting. The time is spent mostly on single-block cell reads -- probably either from UNDO tablespace to maintain read-consistency, or from updating the indexes. Check V$ACTIVE_SESSION_HISTORY to find which it is (or maybe you'll find something unexpected).

                         

                        Best regards,

                          Nikolay

                        • 9. Re: Merge query running for days
                          Rajesh Hariharan

                          Hi,

                           

                          I checked the session history table , there is an entry in this table for every 1 sec for this sql_id ,a sample row from the table looks like this,

                           

                          sql_opname       : UPSERT

                          EVENT              : cell single block physical read (for all the rows)

                          Blocking session status : NO HOLDER

                           

                          Please let me know if you can infer anything from this.

                          • 10. Re: Merge query running for days
                            Nikolay Savvinov


                            Hi,

                             

                            find CURRENT_OBJ# and then find the object_name from DBA_OBJECTS.

                             

                            Best regards,

                              Nikolay

                            • 11. Re: Merge query running for days
                              Rajesh Hariharan

                              Hello,

                               

                              I found the current object is pointing to some foreign key,I read in some blogs that there is no need to fk indexes in exadata.

                               

                              Please let me know if it a crime to use fk indexes on exadata.

                              • 12. Re: Merge query running for days
                                Nikolay Savvinov

                                Hi,

                                 

                                I've never heard anything about indexes on FK being "a crime" in Exadata. Some expert said once that you can drop all indexes on Exadata -- but I think it was meant as a joke, and I've never heard anyone single out FK indexes.

                                Can you provide a link? Was there any explanation provided? Did you make a test before taking this advice?

                                 

                                 

                                Best regards,

                                  Nikolay

                                • 13. Re: Merge query running for days
                                  Rajesh Hariharan

                                  This thread(Why cell single block physical read) here emphasizes the fact the we need to use no index to make it a smart scan rather than cell single block physical read.

                                   

                                  If its not a crime to use indexes ,might be the the it affects the performance as the table size is huge and the exadata decides to use indexes on its own.I was just trying to drill down this issue as this merge statement is taking hell- lotta time ,its been more than 2 days now and still completed only half of its activity when i checked the gv$session_longops.

                                   

                                  Please let me know your thoughts.

                                  • 14. Re: Merge query running for days
                                    Nikolay Savvinov

                                    Hi,

                                     

                                    you have to choose between the advantages offered by Exadata smart scan and the advantages offered by traditional high-precision index probes -- that's what the thread was about. How you got from this to "it's a crime to have indexes on FK columns in Exadata", I don't understand.

                                     

                                    In any case -- it looks like the MERGE statement is slow because it takes long to update indexes on the target table. Dropping and rebuilding them after the merge might be faster.

                                     

                                    Best regards,

                                      Nikolay

                                    1 2 Previous Next