PGA Work Area Histogram by Memory Size

From: Sam Bootsma <sbootsma_at_gbrownc.on.ca>
Date: Wed, 15 Feb 2006 08:25:08 -0500
Message-ID: <CC7ECEDD58772D41A44D87EBED4A77A11D433A@TCCEML02.gbrownc.on.ca>

Hello, see questions below ....  

select low_optimal_size/1024 low_kb, (high_optimal_size+1)/1024 high_kb,

optimal_executions, onepass_executions, multipasses_executions

from v$sql_workarea_histogram

where total_executions != 0;    

    LOW_KB HIGH_KB OPTIMAL_EXECUTIONS ONEPASS_EXECUTIONS MULTIPASSES_EXECUTIONS

• ---------- ------------------ ------------------

  ---

         8         16           64621975                  0

0

        16         32             106265                  0

0

        32         64             116750                  0

0

        64        128              52384                 20

0

       128        256               7537                  0

0

       256        512              98963                 30

0

       512       1024              92970                  0

0

      1024       2048               8208                 18

0

      2048       4096               7442                  4

0

      4096       8192               4547                 64

0

      8192      16384               1221                401

2

     16384      32768                 82                214

0

     32768      65536                  0                100

0

     65536     131072                  2                 18

0

    131072     262144                  3                 80

0

    524288    1048576                  0                 66

0

   8388608   16777216                  0                  0

2  

I run the query above (and other PGA related queries) once per day. After the last execution, I noticed two sorts that allocated between 8 GB and 16 GB of memory - but this still was not enough to sort the data in one pass. It required multiple passes over the data to perform the sort. The Oracle documentation makes it clear that a multi-pass sort is very undesirable.  

I remember reading in the Oracle documentation that a 1 GB piece of data can sort in one-pass mode using just 22 MB of memory. Here I have a piece of data (or two pieces) that requires multiple passes and it has between 8 GB and 16 GB of memory at its disposal. This makes me wonder what humungous piece of data needs multi-pass mode when it has between 8 and 16 GB of memory at its disposal! I figure that if this runs during the day, it could kill the system, and it certainly puts a heavy load on it whether it runs during the day or at night.  

A few questions:

1. Do I properly understand this? Is this as serious as I think it is?
2. Is there a way to capture the SQL that is causing a multi-pass sort as it is happening? If so, does anybody have a script to do this (or the performance views to look at).
3. I see a multi-pass sort that used a memory bucket size between 8 MB and 16 MB. I wonder why it did not use more memory and do the sort in one-pass mode. The only thing I can think of is that memory was tight at the time, and Oracle only wanted to allocate a small amount of memory at the time. Does this make sense?

Thanks!  

Sam.    

Sam Bootsma

Oracle DBA

George Brown College

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 15 2006 - 07:25:08 CST