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

From: Noons <wizofoz2k_at_gmail.com>
Date: Tue, 4 Aug 2009 21:32:35 -0700 (PDT)
Message-ID: <f57e0831-0b4f-4708-872e-9db2360053d2_at_d4g2000prc.googlegroups.com>

On Aug 4, 5:43 pm, ca111026 <ca111..._at_gmail.com> wrote:
> Would you be able to do a simple test? Create average-size table in
> ASSM tablespace, let's say 1,000,000 rows with
> row size approx 100 bytes. Then insert additional 10,000 rows into the
> table using singe-row insert, record how long it takes.
> Then delete all rows from the table, don't commit.
> Then repeat insert of 10,000 rows from another session. Check how long
> it takes.
>
> In my tests second insert takes much, much longer - an hour instead of
> several seconds.

Something is definitely different in your system.

Here are my results:
SQL> select count(1) from zot;

      COUNT(1)

---

       1339456

Elapsed: 00:00:00.24
SQL> desc zot;

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)

(came from dba_tab_columns. zot1 is a subset)

SQL> insert into zot select * from zot1;

13214 rows created.

Elapsed: 00:00:00.21
SQL> delete from zot;

1352670 rows deleted.

Elapsed: 00:01:45.35
SQL> insert into zot select * from zot1;

13214 rows created.

Elapsed: 00:00:00.08
SQL> commit;

Commit complete.

Elapsed: 00:00:00.06
SQL> select count(1) from zot;

      COUNT(1)

---

         13214

Elapsed: 00:00:00.14

In simple terms: bugger all difference between first insert and second insert.
If anything, second was faster. Received on Tue Aug 04 2009 - 23:32:35 CDT