Forum Stats

  • 3,728,222 Users
  • 2,245,576 Discussions
  • 7,853,386 Comments

Discussions

explain plan looks ok but the statement is still taking a bit of time

user13328581
user13328581 Member Posts: 1,300 Bronze Badge
edited June 2016 in SQL & PL/SQL

see result below.

Plan hash value: 2863189736

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

| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |

|   1 |  UPDATE                   | GS_TABLE         |       |       |            |          |

|*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |

|*  3 |    TABLE ACCESS FULL      | GS_TABLE         |     5 |   895 |   123   (0)| 00:00:02 |

|*  4 |    TABLE ACCESS FULL      | UPDATEDPROGRAMCODE |   850 | 44200 |     7   (0)| 00:00:01 |

|*  5 |   VIEW                    |                    |    11 |  2024 |     8  (13)| 00:00:01 |

|*  6 |    WINDOW SORT PUSHED RANK|                    |    11 |   440 |     8  (13)| 00:00:01 |

|*  7 |     FILTER                |                    |       |       |            |          |

|*  8 |      TABLE ACCESS FULL    | UPDATEDPROGRAMCODE |    11 |   440 |     7   (0)| 00:00:01 |

|   9 |   VIEW                    |                    |   850 |  1138K|     9  (23)| 00:00:01 |

|  10 |    SORT ORDER BY          |                    |   850 |   685K|     9  (23)| 00:00:01 |

|* 11 |     VIEW                  |                    |   850 |   685K|     8  (13)| 00:00:01 |

|  12 |      WINDOW SORT          |                    |   850 | 47600 |     8  (13)| 00:00:01 |

|* 13 |       FILTER              |                    |       |       |            |          |

|* 14 |        TABLE ACCESS FULL  | UPDATEDPROGRAMCODE |   850 | 47600 |     7   (0)| 00:00:01 |

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

AndrewSayer

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2016

    What do you mean it looks okay?

    Explain plan doesn't tell you the real plan that will be used, you should always querythe execution plan directly from the cursor cache (use dbms_xplan.display_cursor).

    You are doing several full table scans and sorting the results. How long did you think it would take? How long does it take? What is the session actually doing, is it waiting on locks, is it doing work? Check event from v$session.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited June 2016
    user13328581 wrote:
    
    see result below.
    
    Plan hash value: 2863189736
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |
    |   1 |  UPDATE                   | GS_TABLE         |       |       |            |          |
    |*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |
    |*  3 |    TABLE ACCESS FULL      | GS_TABLE         |     5 |   895 |   123   (0)| 00:00:02 |
    |*  4 |    TABLE ACCESS FULL      | UPDATEDPROGRAMCODE |   850 | 44200 |     7   (0)| 00:00:01 |
    |*  5 |   VIEW                    |                    |    11 |  2024 |     8  (13)| 00:00:01 |
    |*  6 |    WINDOW SORT PUSHED RANK|                    |    11 |   440 |     8  (13)| 00:00:01 |
    |*  7 |     FILTER                |                    |       |       |            |          |
    |*  8 |      TABLE ACCESS FULL    | UPDATEDPROGRAMCODE |    11 |   440 |     7   (0)| 00:00:01 |
    |   9 |   VIEW                    |                    |   850 |  1138K|     9  (23)| 00:00:01 |
    |  10 |    SORT ORDER BY          |                    |   850 |   685K|     9  (23)| 00:00:01 |
    |* 11 |     VIEW                  |                    |   850 |   685K|     8  (13)| 00:00:01 |
    |  12 |      WINDOW SORT          |                    |   850 | 47600 |     8  (13)| 00:00:01 |
    |* 13 |       FILTER              |                    |       |       |            |          |
    |* 14 |        TABLE ACCESS FULL  | UPDATEDPROGRAMCODE |   850 | 47600 |     7   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    

    According to the post PLAN, the query should take 2 seconds to complete & accessed fewer than 1000 rows total.

    How do these values compare with reality?

    please quantify "a bit of time"

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited June 2016

    It looks like your update is using an existence subquery to identify rows, then updating two columns with individual subqueries that need to do a tablescan to find a few hundred rows and do some sorting for each row updated.

    You don't show the predicate section of the plan so we have no idea how many things in the predicate section may be making the optimizer come up with very bad estimates, so:

      The plan thinks that there will be only one row to update - check a select with existence, is this close ?

       How much work does Oracle do for a typical example value for each of the subqueries - Oracle's estimate is that it will take in the order of a couple of seconds per row updated (so if you're updating a couple of thousand rows you could be waiting for 20 minutes) if Oracle is close to correct in its subquery prediction.

    Regards

    Jonathan Lewis

  • user13328581
    user13328581 Member Posts: 1,300 Bronze Badge
    edited June 2016

    In reality, it is taking more than 1 hour...

    Jonathan: I could try converting it with a select with existence,,,also how do i show the predicate section of the plan in sql developer..

  • Pavan Kumar
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited June 2016
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,903 Gold Crown
    edited June 2016

    Hi Jonathan,

    ------------------------------------------------------------------------------------------------
    
    | Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    
    ------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT          |                        |     1 |   231 |   160   (6)| 00:00:02 |
    |   1 |  UPDATE                   | GS_TABLE             |       |       |            |          |
    |*  2 |   HASH JOIN SEMI          |                        |     1 |   231 |   130   (0)| 00:00:02 |
    |*  3 |    TABLE ACCESS FULL      | GS_TABLE             |     5 |   895 |   123   (0)| 00:00:02 |
    |*  4 |    TABLE ACCESS FULL      | UPDATEDPROGRAMCODE     |   850 | 44200 |     7   (0)| 00:00:01 |
    

    Hope fully the update gs_table statement is joined with UPDATEDPROGRAMCODE (850 rows) - trying to fetch topped rank with range/window ..performing the hash semi (probably correlated query to get lookup or summed value) with below (self referential for same table)

    
    
    |*  5 |   VIEW                
    |               
    
     |
    11 |  2024 | 
    8  (13)| 00:00:01 |
    
    
    |*  6 |
    WINDOW SORT PUSHED RANK|               
    
     |
    11 |   440 | 
    8  (13)| 00:00:01 |
    
    
    |*  7 | 
    FILTER            
    |               
    
     |   
    |   
    |        
    |      
    |
    
    
    |*  8 |  
    TABLE ACCESS FULL
    | UPDATEDPROGRAMCODE     |
    11 |   440 | 
    7   (0)| 00:00:01 |
    
    
    
    
    

    and finally making sure hash join semi (not exists with some sort of business exclusion condition (not exists)

    
    
    |   9 |   VIEW                
    |               
    
     |   850 |  1138K| 
    9  (23)| 00:00:01 |
    
    
    |  10 |
    SORT ORDER BY      
    |               
    
     |   850 |   685K| 
    9  (23)| 00:00:01 |
    
    
    |* 11 | 
    VIEW              
    |               
    
     |   850 |   685K| 
    8  (13)| 00:00:01 |
    
    
    |  12 |  
    WINDOW SORT      
    |               
    
     |   850 | 47600 | 
    8  (13)| 00:00:01 |
    
    
    |* 13 |   
    FILTER          
    |               
    
     |   
    |   
    |        
    |      
    |
    
    
    |* 14 |    
    TABLE ACCESS FULL  | UPDATEDPROGRAMCODE     |   850 | 47600 | 
    7   (0)| 00:00:01 |
    
    
    
    

    How about rewriting as,

    1. Initial elimination the rows which aren;t needed (if we have index joning condition only index lookup will happen), so we have subset of require data

    2. Update the statement with index - resultset which would be small fit into to PGA, then getting summary of group of required value

    Please correct me

    - Pavan Kumar N

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited June 2016
    user13328581 wrote:
    
    In reality, it is taking more than 1 hour...
    Jonathan: I could try converting it with a select with existence,,,also how do i show the predicate section of the plan in sql developer..
    

    Use SQL*Plus to display the execution plan in a postable format:

    Do:

    explain plan for <update statement>;

    select * from table(dbms_xplan.display);

    Post the update statement if you want help rewriting it. It can almost definitely be rewritten so that UPDATEDPROGRAMCODE and GS_TABLE are scanned only once with the result used to update your table. You'll probably find it helpful to write a query that will generate the new versions of the rows for the table and use a MERGE statement.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited June 2016

    Pavan Kumar N,

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

    | Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

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

    |   0 | UPDATE STATEMENT          |                    |     1 |   231 |   160   (6)| 00:00:02 |

    |   1 |  UPDATE                   | GS_TABLE           |       |       |            |          |

    |*  2 |   HASH JOIN SEMI          |                    |     1 |   231 |   130   (0)| 00:00:02 |

    |*  3 |    TABLE ACCESS FULL      | GS_TABLE           |     5 |   895 |   123   (0)| 00:00:02 |

    |*  4 |    TABLE ACCESS FULL      | UPDATEDPROGRAMCODE |   850 | 44200 |     7   (0)| 00:00:01 |

    |*  5 |   VIEW                    |                    |    11 |  2024 |     8  (13)| 00:00:01 |

    |*  6 |    WINDOW SORT PUSHED RANK|                    |    11 |   440 |     8  (13)| 00:00:01 |

    |*  7 |     FILTER                |                    |       |       |            |          |

    |*  8 |      TABLE ACCESS FULL    | UPDATEDPROGRAMCODE |    11 |   440 |     7   (0)| 00:00:01 |

    |   9 |   VIEW                    |                    |   850 |  1138K|     9  (23)| 00:00:01 |

    |  10 |    SORT ORDER BY          |                    |   850 |   685K|     9  (23)| 00:00:01 |

    |* 11 |     VIEW                  |                    |   850 |   685K|     8  (13)| 00:00:01 |

    |  12 |      WINDOW SORT          |                    |   850 | 47600 |     8  (13)| 00:00:01 |

    |* 13 |       FILTER              |                    |       |       |            |          |

    |* 14 |        TABLE ACCESS FULL  | UPDATEDPROGRAMCODE |   850 | 47600 |     7   (0)| 00:00:01 |

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


    When formatted in fixed font the plan LOOKS LIKE the plan for a query of the form:

    update gs_table

    set

            column1 = (correlated subquery against updatedprogramcode including analytic functions),

            column2 = (correlated subquery againsts updatedprogramcode including analytic functions)

    where

            {some predicates}

    and exists (select correlated from updatedprogramcode)

    However, the OP's reply to my posting suggests that my guess was wrong since (s)he has said they "could try converting it with a select with existenc" and hasn't answered any of the questions I asked about where the work happens and how many rows are involved. Since I've had no response to my requests for information I'm not going to speculate on where the time goes or how the performance could be improved.

    Regards

    Jonathan Lewis

    P.S.  I suspect the plan also suggests that the version is 12c, but I haven't thought too carefully about that.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited June 2016

    How to get the plan with predicates probably depends on the version of SQL*Developer - it's not a tool I use regularly. Whatever else you do the two key points that benefit the forum (and you) are to use dbms_xplan and to get a text output.

    For basic usage you should be able to execute two queries from the SQL screen:

    explain plan for

    {your SQL statement}

    select * from table(dbms_xplan.display);

    If the last couple of lines of the output are a note about "using old version of PLAN_TABLE" you will need to "drop table PLAN_TABLE;" (but make sure you get permission from the owner of the database (or schema) before you do that.

    As an alternative you could run the query, then do:

    select sql_id, child_number, sql_text from V$sql where sql_text like '%{some identifiable bit of your SQL}%';

    When you can identify the row in v$sql that is your query you can do:

    select * from table(dbms_xplan.display_cursor('{the sql_id reported}', {the child_number reported}));

    If you want to see where the work goes and how much data is involved you can use the SQL_Developer autotrace feature, but at present this will only give you a graphic display which doesn't display well in the forum - however the STARTS column and the A-Rows column tell you about the workload line by line, and will allow you to answer my question about how many rows identified as in need of update.

    Regards

    Jonathan Lewis

  • kulikouski
    kulikouski Member Posts: 56
    edited June 2016

    Hi,

    You could try rewrite UPDATE to MERGE, I believe it will faster.

    KR

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,581 Gold Crown
    edited June 2016

    It might be faster - but you don't know whether the time is spent on identifying the data to be updated or on performing the subqueries that calculate new values; and since it appears that there are two (or more) columns being updated by two significantly differing subqueries that involve analytic functions it might not be possible to create an efficient USING subquery for a merge; so it might be better to wait to see what the statement looks like before guessing how best to change it.

    Regards

    Jonathan Lewis

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited June 2016

    Can you move your monitor closer to the window? I'm having difficulty seeing your update statement.

    AndrewSayer
  • user13328581
    user13328581 Member Posts: 1,300 Bronze Badge
    edited June 2016

    Hi the version is 12c. After further digging, I realized the reason why the update statement is slow is due to the number of triggers created on the required updated table.

This discussion has been closed.