Re: New to RAC and need some ideas/suggestions
From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Thu, 23 Aug 2012 08:12:35 -0500
Message-ID: <50362C43.1080105_at_ardentperf.com>
If you're querying the active_session_history view, then you must have the diagnostics license. Grabbing a snapshot of active_session_history across the whole cluster doesn't hurt, but for historical info you can always go into dba_hist_active_session_history as well. Even though AWR only captures snapshots of the ASH at larger intervals, I've generally found no gaps in ASH coverage. (Depending on the average number of active sessions, The ASH buffer is often large enough to hold even 1 hr of snapshots with the default AWR snapshot interval! And many people even decrease AWR snapshots to 15 or 30 minute intervals.)
Date: Thu, 23 Aug 2012 08:12:35 -0500
Message-ID: <50362C43.1080105_at_ardentperf.com>
If you're querying the active_session_history view, then you must have the diagnostics license. Grabbing a snapshot of active_session_history across the whole cluster doesn't hurt, but for historical info you can always go into dba_hist_active_session_history as well. Even though AWR only captures snapshots of the ASH at larger intervals, I've generally found no gaps in ASH coverage. (Depending on the average number of active sessions, The ASH buffer is often large enough to hold even 1 hr of snapshots with the default AWR snapshot interval! And many people even decrease AWR snapshots to 15 or 30 minute intervals.)
It's also worth pointing out that even without the diagnostics pack license, you can download SASH from http://ashmasters.com/simulation which will provide similar functionality. I think that anyone would find great value from having sample data for all of their important databases. Just yesterday I was troubleshooting a long-running job and pulled ASH/sample from a week prior in order to get a break-through in our analysis.
-Jeremy
-- Jeremy Schneider Chicago +1 312-725-9249 http://www.ardentperf.com On 08/22/2012 05:39 PM, Herring Dave - dherri wrote:Received on Thu Aug 23 2012 - 08:12:35 CDT
> 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-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l