Home » RDBMS Server » Performance Tuning » Query in active session for more than 2 hours (11.2.0.4)
Query in active session for more than 2 hours [message #638279] Mon, 08 June 2015 15:31 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
Following query has been running for more than 2 hours.
UPDATE acm_audits
   SET note = comments
 WHERE     audit_internal_id IN (SELECT audit_internal_id
                                   FROM acm_alert_audits)
       AND comments IS NOT NULL

When I look at session longops, it says 45% complete on Hash Join: : 513329 out of 1148116 Blocks done.
Please suggest if Is there anything I could do differently to make this query run faster?

Below is the explain plan':-
Plan hash value: 2755850470
 
--------------------------------------------------------------------------------------------------
| Id  | Operation             | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                  |    20M|  1230M|       |   231K  (1)| 00:46:13 |
|   1 |  UPDATE               | ACM_AUDITS       |       |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|                  |    20M|  1230M|   557M|   231K  (1)| 00:46:13 |
|   3 |    TABLE ACCESS FULL  | ACM_ALERT_AUDITS |    32M|   185M|       | 20858   (1)| 00:04:11 |
|*  4 |    TABLE ACCESS FULL  | ACM_AUDITS       |    20M|  1115M|       |   115K  (1)| 00:23:07 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("AUDIT_INTERNAL_ID"="AUDIT_INTERNAL_ID")
   4 - filter("COMMENTS" IS NOT NULL)
Re: Query in active session for more than 2 hours [message #638281 is a reply to message #638279] Mon, 08 June 2015 18:06 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
how many rows in acm_audits?
how many rows in acm_audits get UPDATED?
how many rows where comments IS NOT NULL?
how many rows WHERE audit_internal_id IN (SELECT audit_internal_id FROM acm_alert_audits)?
Re: Query in active session for more than 2 hours [message #638344 is a reply to message #638281] Tue, 09 June 2015 15:30 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Blackswan,
Please find the following numbers:-

select count(*) from ctr_acm.acm_audits; 89557810
how many rows in acm_audits get UPDATED? 57293024
how many rows where comments IS NOT NULL? 57303816
SELECT count(audit_internal_id) FROM acm_alert_audits 89531369

Thanks,

Varun
Re: Query in active session for more than 2 hours [message #642140 is a reply to message #638344] Thu, 03 September 2015 09:21 Go to previous messageGo to next message
shirleyhu
Messages: 4
Registered: August 2015
Location: AUSTRALIA
Junior Member
I reckon the problem is with "HASH JOIN RIGHT SEMI" which require 557M tempspace and it takes 19 mins .Please check the possibility to adjust pga to allow sort in memory instead of tempspace which is very costly .
"TABLE ACCESS FULL | ACM_AUDITS " tooks 23 mins , please check related wait event.
Re: Query in active session for more than 2 hours [message #642142 is a reply to message #642140] Thu, 03 September 2015 12:19 Go to previous messageGo to next message
John Watson
Messages: 7622
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum, Shirley. It is very nice to see a new member who answers questions rather than only ever asking them! Please keep it up.

And, just to do te moderator bit, please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Re: Query in active session for more than 2 hours [message #642283 is a reply to message #642142] Sun, 06 September 2015 10:14 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Hi Shirley, glad to have you join the club.

To the problem at hand,

The basic query plan (hash join) looks good for what it is you are trying to do. You are in fact updating 60 million rows out of a 90 million row table. And given the row counts you have provided, the hash join clearly is the way to go. I would suggest the following in order of precedence. There is a change you can make to the query to try and do less work, and there are a series of strategies for making a hash join go faster:

Changing the query.

1. add AND NOTE != COMMENT to the end of the query.  This way you will not update any rows where the comment is the same.  I assume there may be some.  If there are none due to the nature of the problem, then don't bother.  The hope would be that a large percentage of the updates need not be done, and thus you will skip the overhead of redo/undo etc.

UPDATE acm_audits
   SET note = comments
 WHERE  audit_internal_id IN (
                               SELECT audit_internal_id
                               FROM acm_alert_audits
                             )
   AND comments IS NOT NULL
   AND note != comments
/


Strategies for making the hash join go faster.

1. don't do the join by simply removing it.  This refers to removal of DUNSEL JOINS or what Oracle calls JOIN ELIMINATION.  However, this does not apply to your case.  You need all the parts you have exposed.

2. reduce the amount of memory needed by the join by removing unnecessary columns from the query.  This is about not selecting columns ultimately not needed by a query because doing so can cause intermediary row sets to grow in size due to useless data.  It is mostly an issue with SELECT statements that have a long series of joins.  This too does not apply to your case.  You have no columns referenced that you do not need and you have to fetch the entire row to update it anyway.

3. increase the amount of memory available to the join.  This is what Shirley suggested, and she is spot on.  You can increase your PGA in an effort to try and get more memory to the hash join.  You can also go old school and use manual memory management and specify the maximum size HASH_AREA_SIZE.  Do this by setting a series of session parameters.  The below parameter settings will give up to 2GB for your work area to do the hash join.  But, you should do it only on a test box to see how it behaves, and then discuss your results with your DBA.  You may find the improvement is good, but not as dramatic as you would have hoped.  You may also find considerable resistance to using manual memory management in today's Oracle environments.  This is because the normal strategy for Oracle on  10g/11g/12c is to share memory, and the strategy of manual memory management is all about being greedy and not sharing.  So it conflicts with the other work done on your server.  This makes it a bit of a charged topic.

ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET HASH_AREA_SIZE=2147483647;

4. Use PARALLEL DML to throw more resources at it.  Parallel DML of course brings with it its own set of problems, requirements, and limitations.  You should familiarize yourself with parallel DML by reading about it.  Your goal would be to do say 4 or 6 or 8 parallel update streams at the same time.  You would need additional session parameters set, and you can combine this with the above settings for manual work area management if you like.  Thus you do what you see below (starting with a DOP=2).  You can scale up the parallelism as needed, but remember that for each parallel stream, you will get another work area which could grow to the full 2GB in size.  Thus if you do say DOP of 8, you can end up with 16BG of memory used for the hash join.  This is of course what you want if your hash join can benefit from it.  But it does not play well on a system that needs to share a limited amount of memory between sessions, as in for example an environment where databases are virtualized and usually resource constrained as a result.  So again you will want to discuss this with your DBA after you have tested in a DEV environment.  Notice also that a COMMIT is required after the update if you want to use this table after you have updated it, and thus you have to consider the ramifications of using parallel DML to your transaction design for it changes ATOMIC.

ALTER SESSION SET WORKAREA_SIZE_POLICY=MANUAL;
ALTER SESSION SET HASH_AREA_SIZE=2147483647;
ALTER SESSION EMABLE PARALLEL DML;

UPDATE /*+ parallel (acm_audits,2) */ acm_audits
   SET note = comments
 WHERE  audit_internal_id IN (
                               SELECT /*+ parallel (acm_alert_audits, 2) */ audit_internal_id
                               FROM acm_alert_audits
                             )
   AND comments IS NOT NULL
   AND note != comments
/

commit
/

5. use HASH PARTITIONING to aid #4 above.  Hash partitioning will provide affinity of a partition to a parallel stream.  Thus if you hash partition both tables involved on AUDIT_INTERNAL_ID with the same number of partitions, you can take advantage of FULL PARTITION WISE HASH JOIN which is highly scalable due to the elimination of the need to pass rows between parallel streams which comes of course from providing affinity of a partition pair created by the associated equi-partitioning on the tables.

6. since you are updating more than 20% of your table (a rough guide at best), you might be much better served by doing a CREATE TABLE AS to build a new version of the table and then substitute it for the old one using either partition swapping or full replacement.  This too has its issues.  All advanced strategies have special needs.  Creating a table and its indexes etc. rather than updating in place can be up to 10 times faster.  But you have to manage it.  It is not a simple inline piece of code like a basic update is.  And just like the parallel DML option, it breaks up your transaction semantics, so you have to know that this is OK.  Indeed, you can do the table create and index creates in parallel which makes the process blazingly fast.


You may want to read about parallel DML here. Pay particular attention to the section about updating in parallel using updatable join views.

OK so some of this is pretty advanced stuff. You will want to discuss your options with your DBA at some point. Your DBA may have other ideas too.

If you want to learn more about SQL Tuning, I humbly refer you to my book. Here is the first chapter, the scripts from the book, and a new tool of a sort that helps me to organize tuning sessions. Read the first chapter and play with the scripts to decide if the book is something you are interested in before you buy it. If you do decide to buy, check to see if your company has an expense reimbursement program. Most do, and most cover books related to your field. This means you can order it from Amazon, then immediately fill out an expense form so that your company pays you back. No need to spend your own money when you can spend someone else's instead. If you get an error when trying to download any of these attachments, just click retry which always works. Beyond that, send me a PM and I'll forward you the materials directly.

Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities

Good luck, Kevin.

[Updated on: Sun, 06 September 2015 10:28]

Report message to a moderator

Previous Topic: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME
Next Topic: cost is more after releasing FTS on big table (partition table)
Goto Forum:
  


Current Time: Mon Oct 22 12:10:26 CDT 2018