This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Nov 22, 2013 8:25 AM by Rajesh Hariharan RSS

Merge query running for days

Rajesh Hariharan Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points