Re: ramfs or ram disk
From: Alex Fatkulin <afatkulin_at_gmail.com>
Date: Tue, 27 Aug 2013 19:26:29 -0400
Message-ID: <CAMVw97+NoeA5NMdUZJKWANJg52FXsXHqT3g+bDBdwyMCqiP=dQ_at_mail.gmail.com>
There is another problem that a single workarea for a single process can only grow so much. Note 453540.1 refers to internal limitation of 2GB. So once the process has to deal with certain amounts of data further increasing PGA does nothing because workarea can no longer grow. In this case having in-memory TEMP tablespace might be a viable way to get over that limit.
> From: oracle-l-bounce_at_freelists.org <javascript:;> [
> oracle-l-bounce_at_freelists.org <javascript:;>] on behalf of kyle Hailey [
> kylelf_at_gmail.com <javascript:;>]
> Sent: 27 August 2013 21:22
> To: Connor McDonald
> Cc: oracle-l_at_freelists.org <javascript:;>
> Subject: Re: ramfs or ram disk
>
> Awesome response Connor :)
> Has anyone looked into how the writes are done for temporary?
>
> I've noticed on some AWR reports the temp read times at 0ms, for example
>
> Event Waits %Time -outs Total Wait Time
> (s) Avg wait (ms) Waits /txn
> direct path read temp 317,082 0.00 1
> 0 3.80
>
> Over 300K temporary reads and the average time is 0 ms ??
> How would that happen? What if the temporary data was in the Unix file
> system cache?
> If the temp I/O is not DIRECT and/or SYNC then seems reasonable that I'd
> fine most of them in the cache if the cache isn't under pressure.
>
> Anyone look into this?
>
> Thanks
> Kyle
>
>
>
>
>
>
> On Mon, Aug 26, 2013 at 5:58 AM, Connor McDonald
> <mcdonald.connor_at_gmail.com <javascript:;>>wrote:
>
> > *Does anyone here know what he is talking about?*
> > Possibly not your DW architect :-)
> >
> > Oracle has memory allocations for many things, for temporary workareas,
> for
> > commonly accessed data etc...
> >
> > So if you've got
> >
> > a) an abundance of spare RAM floating around, and
> > b) evidence that supports you needing more for Oracle activities
> >
> > you're most probably better off setting the Oracle parameters to consume
> > that RAM rather than something such as ram disk etc...
> >
> > hth
> > Connor
>
>
Date: Tue, 27 Aug 2013 19:26:29 -0400
Message-ID: <CAMVw97+NoeA5NMdUZJKWANJg52FXsXHqT3g+bDBdwyMCqiP=dQ_at_mail.gmail.com>
There is another problem that a single workarea for a single process can only grow so much. Note 453540.1 refers to internal limitation of 2GB. So once the process has to deal with certain amounts of data further increasing PGA does nothing because workarea can no longer grow. In this case having in-memory TEMP tablespace might be a viable way to get over that limit.
> From: oracle-l-bounce_at_freelists.org <javascript:;> [
> oracle-l-bounce_at_freelists.org <javascript:;>] on behalf of kyle Hailey [
> kylelf_at_gmail.com <javascript:;>]
> Sent: 27 August 2013 21:22
> To: Connor McDonald
> Cc: oracle-l_at_freelists.org <javascript:;>
> Subject: Re: ramfs or ram disk
>
> Awesome response Connor :)
> Has anyone looked into how the writes are done for temporary?
>
> I've noticed on some AWR reports the temp read times at 0ms, for example
>
> Event Waits %Time -outs Total Wait Time
> (s) Avg wait (ms) Waits /txn
> direct path read temp 317,082 0.00 1
> 0 3.80
>
> Over 300K temporary reads and the average time is 0 ms ??
> How would that happen? What if the temporary data was in the Unix file
> system cache?
> If the temp I/O is not DIRECT and/or SYNC then seems reasonable that I'd
> fine most of them in the cache if the cache isn't under pressure.
>
> Anyone look into this?
>
> Thanks
> Kyle
>
>
>
>
>
>
> On Mon, Aug 26, 2013 at 5:58 AM, Connor McDonald
> <mcdonald.connor_at_gmail.com <javascript:;>>wrote:
>
> > *Does anyone here know what he is talking about?*
> > Possibly not your DW architect :-)
> >
> > Oracle has memory allocations for many things, for temporary workareas,
> for
> > commonly accessed data etc...
> >
> > So if you've got
> >
> > a) an abundance of spare RAM floating around, and
> > b) evidence that supports you needing more for Oracle activities
> >
> > you're most probably better off setting the Oracle parameters to consume
> > that RAM rather than something such as ram disk etc...
> >
> > hth
> > Connor
>
>
-- Alex Fatkulin, http://afatkulin.blogspot.com Enkitec, http://www.enkitec.com -- http://www.freelists.org/webpage/oracle-lReceived on Wed Aug 28 2013 - 01:26:29 CEST