Re: Do you use ASSM (Automatic Segment Space Management)?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 7 Aug 2009 06:56:13 -0700 (PDT)
Message-ID: <547e7c5a-8026-4063-88f5-a647b63b2760_at_g6g2000vbr.googlegroups.com>

On Aug 7, 9:03 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 69,802 physical block reads, 69,793 consistent gets, 83,979 current
> mode gets, 18.03 seconds spent performing single block reads.  This
> seems to be behaving similar to the bug that Jonathan Lewis found with
> 16KB block sizes last year when column values in existing rows were
> changed from NULL to a value.  In that case, the current mode gets
> were the tipoff that there was a problem.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I repeated the test with an ASSM tablespace with 1MB uniform extents. The first insert performed 71,250 physical block reads, 71,206 consistent gets, 85,473 current mode gets, 18.85 seconds performing single block reads with an elapsed time of 21.53 and for some reason 0 CPU seconds (the next insert reported 3.59 CPU seconds).

I also repeated the test with a locally managed table with with 1MB uniform extents without ASSM: "SIZE 2G REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT MANUAL". The results in the final test were a little disappointing. The *totals* from the script execution for all of the inserts:
Total for Trace File:

|PARSEs    1003|CPU S    0.234002|CLOCK S    0.312034|ROWs        0|
PHY RD BLKs         0|CON RD BLKs (Mem)         0|CUR RD BLKs
(Mem)         0|
|EXECs     1003|CPU S    0.031200|CLOCK S    0.062434|ROWs     1002|
PHY RD BLKs         0|CON RD BLKs (Mem)      1051|CUR RD BLKs
(Mem)      1343|
|FETCHs       2|CPU S    0.000000|CLOCK S    0.000000|ROWs        1|
PHY RD BLKs         0|CON RD BLKs (Mem)         3|CUR RD BLKs
(Mem)         0|

Wait Event Summary:
SQL*Net message to client           0.001472  On Client/Network   Min
Wait:     0.000001  Avg Wait:     0.000001  Max Wait:     0.000076
SQL*Net message from client         0.683966  On Client/Network   Min
Wait:     0.000402  Avg Wait:     0.000684  Max Wait:     0.001799

The totals for all of the inserts performed 0 physical block reads, 1,051 consistent gets, 1,343 current mode gets, 0 seconds performing single block reads with an elapsed time of 0.374468 seconds (0.312034 of that was for parsing) and 0.265202 CPU seconds (0.234002 for parsing).

Looks like a problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Aug 07 2009 - 08:56:13 CDT