Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
explain plan looks ok but the statement is still taking a bit of time

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