SQL & PL/SQL

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.

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"

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

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..

Refer to https://www.youtube.com/watch?v=KNaonxlMVOo

- Pavan Kumar N

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

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.

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.

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

Hi,

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

KR

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

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

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.