Re: Do you use ASSM (Automatic Segment Space Management)?
Date: Fri, 7 Aug 2009 06:56:13 -0700 (PDT)
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Aug 07 2009 - 08:56:13 CDT