RE: New to RAC and need some ideas/suggestions
Date: Wed, 22 Aug 2012 22:39:33 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4E3D0E396_at_LITIGMBCRP02.Corp.Acxiom.net>
I realize this is a day late and possibly session history is lost due to memory space pressures, but one of the first things I do when I believe ASH may help is snapshot GV$ACTIVE_SESSION_HISTORY doing a CTAS. That way I won't loose any data before I have a chance to analyze it.
If you know the SQL_IDs then you can obviously lookup in your CTAS on those to check ranges of times and concurrency. Or you could get the OBJECT_ID for the object in question and check the CTAS ASH table by CURRENT_OBJ#. To me ASH data is the best way to go for getting at the session info in this case.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chris Taylor Sent: Tuesday, August 21, 2012 4:17 PM
To: Bobak, Mark
Cc: oracle-l_at_freelists.org
Subject: Re: New to RAC and need some ideas/suggestions
Mark,
What's a good way to determine what 'other' sessions would have been inserting into the same table? Is there way to track this back through history when the sessions are no longer connected? I'm looking at V$ACTIVE_SESSION_HISTORY where event like '%FB%' and I see several SESSION_IDS that had this particular wait but none of them seem to be connected at the moment.
I've got my investigator hat on at the moment, but I'm not sure where to begin (or how to begin) looking for culprits. Two of the SESSION IDS had the same SQL_ID but on different INSTANCES - but I'm not sure they were running CONCURRENTLY - is there some way I can determine that?
(ugh - I just realized it looks like GMAIL screwed up my formatted table I sent in my original message - my apologies for that)
Chris
On Tue, Aug 21, 2012 at 4:07 PM, Bobak, Mark <Mark.Bobak_at_proquest.com>wrote:
> Hi Chris,
>
> The 'gc' waits are an indicator of traffic going across the interconnect.
> This is generally to be avoided, as much as possible.
>
> If you have other sessions inserting into the same table or set of
> tables, from a different node, you're likely to see this, and see
> performance suffer. Try to isolate the workload of this type to a particular node.
>
> Also, consider hash partitioning heavily used indexes (number of
> partitions equal to smallest power of 2 greater than or equal to the
> number of nodes in the cluster). Remember, you can hash partition
> indexes, even for columns of tables that are not partitioned at all.
>
> Welcome to the wonderful world of RAC.
>
> Hope that helps,
>
> -Mark
-- http://www.freelists.org/webpage/oracle-l *************************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. **************************************************************************** -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 22 2012 - 17:39:33 CDT