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

From: kevin <majunyue_at_hotmail.com>
Date: Wed, 5 Aug 2009 06:19:59 -0700 (PDT)
Message-ID: <9260ad16-05b7-4c26-888e-3e7ebd777149_at_13g2000prl.googlegroups.com>

On Aug 5, 12:32 pm, Noons <wizofo..._at_gmail.com> wrote:
> 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.

Let me show you something:

---

9iSQL> create table t as select * from fb6 where rownum < 100000;

Table created.

Elapsed: 00:00:00.03
9iSQL> insert into t select * from t;

99999 rows created.

Elapsed: 00:00:01.00

Execution Plan

---

   0 INSERT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'T' Statistics

---

        182  recursive calls
      11122  db block gets
       3136  consistent gets
       1104  physical reads
    9007392  redo size
        793  bytes sent via SQL*Net to client
        802  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      99999  rows processed

9iSQL> delete t;

199998 rows deleted.

Elapsed: 00:00:05.03

Execution Plan

---

   0 DELETE STATEMENT Optimizer=CHOOSE    1 0 DELETE OF 'T'
   2 1 TABLE ACCESS (FULL) OF 'T' Statistics

---

        305  recursive calls
     219083  db block gets
       2422  consistent gets
       1104  physical reads
   63639616  redo size
        793  bytes sent via SQL*Net to client
        781  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     199998  rows processed
------------------------------------------------------------------------------------------

when you delete rows before commit, the redo size is very big. So the problem is not regarding ASSM, it's redo size. Received on Wed Aug 05 2009 - 08:19:59 CDT