Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Unanswered: Creating Tables - Size and Extents

    Hi

    I have a problem, on my Live DB, my program is rather fast and on my Test DB its slow. My Test DB has been imported from my Live DB (.dmp)

    There is 1 table that I am not sure is configured correctly and was hoping that some one could help me out here...

    The table is used about once a month and about 15 000 records are inserted into it at a time. The Indexes and all that stuff is fine and the table is not fragmented.

    There is 2 tables in specific (TableEmp)

    Live DB TableEmp:
    Size : 68.64 mb
    Initial Ext : 71,680
    Next Ext : 71,680
    Num Ext : 189
    Max Ext : 2,147,483,645

    Test DB TableEmp:
    Size : 46.13 mb
    Initial Ext : 48,365,568
    Next Ext : 696,320
    Num Ext : 1
    Max Ext : 2,147,483,645

    These are all my columns in the Table


    KONTO VARCHAR2 (54) NOT NULL,
    KORR NUMBER (38) NOT NULL,
    KORRDATUM NUMBER (13) NOT NULL,
    LATTHELG NUMBER (38) NOT NULL,
    OID VARCHAR2 (12) NOT NULL,
    ORSAK VARCHAR2 (8) NOT NULL,
    OMF FLOAT NOT NULL,
    PRIO NUMBER (38) NOT NULL,
    PROCENT NUMBER (5,2) NOT NULL,
    NORMALTECKEN NUMBER (38) NOT NULL,
    TRANSATTRIBUT NUMBER (38) NOT NULL,
    TJLF FLOAT NOT NULL,
    TIMTYP NUMBER (38) NOT NULL,
    TECKEN NUMBER (38) NOT NULL,
    SPECSORT NUMBER (38) NOT NULL,
    SPECANTAL FLOAT NOT NULL,
    SEMF FLOAT NOT NULL,
    RAPPNR NUMBER (38) NOT NULL,
    APRIS NUMBER (14,2) NOT NULL,
    ANTAL FLOAT NOT NULL,
    ANSTNR NUMBER (38) NOT NULL,
    ANST3 VARCHAR2 (12) NOT NULL,
    ANST2 VARCHAR2 (12) NOT NULL,
    ANST1 VARCHAR2 (12) NOT NULL,
    AJOURF NUMBER (38) NOT NULL,
    UTBDAT NUMBER (13) NOT NULL,
    ARBTIMMAR FLOAT NOT NULL,
    KALDGR FLOAT NOT NULL,
    ID4 VARCHAR2 (8) NOT NULL,
    ID3 VARCHAR2 (8) NOT NULL,
    ID2 VARCHAR2 (8) NOT NULL,
    ID1 VARCHAR2 (8) NOT NULL,
    GTOM NUMBER (13) NOT NULL,
    GFOM NUMBER (13) NOT NULL,
    FRPAVERK NUMBER (38) NOT NULL,
    FOERBEL NUMBER (14,2) NOT NULL,
    BETMOTT VARCHAR2 (5) NOT NULL,
    BELOPP FLOAT NOT NULL,
    AVVSGR FLOAT NOT NULL,
    ARBDGR FLOAT NOT NULL,
    TRANSTYP NUMBER (38) NOT NULL,
    LART VARCHAR2 (5) NOT NULL,
    PNR VARCHAR2 (10) NOT NULL,
    PRISSATT NUMBER (38) NOT NULL,
    REGDAT NUMBER (13),
    AARTAL NUMBER (38) NOT NULL,
    ACKKORR NUMBER (38) NOT NULL,
    ACKKOD VARCHAR2 (4) NOT NULL,
    KATKOD VARCHAR2 (12) NOT NULL,
    GRENS VARCHAR2 (8) NOT NULL,
    GENSYSS FLOAT NOT NULL,
    FORMEL VARCHAR2 (4) NOT NULL,
    EXTRA NUMBER (38) NOT NULL,
    DIVISOR NUMBER (38) NOT NULL,
    AVVSKATT NUMBER (38) NOT NULL,
    AVLFORM VARCHAR2 (2) NOT NULL,
    REGSIGN VARCHAR2 (10),
    REGTID NUMBER (13),
    TRANSNR NUMBER (38) NOT NULL,
    PRIVKOD NUMBER (38) NOT NULL,
    PERIOD NUMBER (38) NOT NULL,
    MAXBELOPP FLOAT NOT NULL)

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The reason test might be slower is

    1) PROD has a bigger faster server
    2) network issues if your app doesnt run on the database server like say a firewall reducing bandwidth
    3) A different execution plan is being chosen on TEST which is less efficient, may be indicative that PROD tables have been analyzed but TEST hasnt.

    Alan

  3. #3
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Originally posted by AlanP
    The reason test might be slower is

    1) PROD has a bigger faster server
    2) network issues if your app doesnt run on the database server like say a firewall reducing bandwidth
    3) A different execution plan is being chosen on TEST which is less efficient, may be indicative that PROD tables have been analyzed but TEST hasnt.

    Alan
    Thats what I figure but my Manager is insist that its the way that the extents are specified in the Table and the TableSpace it self...

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    what exactly is your problem now? Do u want an answer why the testdb is slow or do you want to change the extents sizes or do u want to know how this happens ???
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    TEST is actually better organised in terms of extents in that it has less (for the table anyway). If anything performance should be better on TEST (though the diff would be too small to make any real difference).

    Alan

  6. #6
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Originally posted by evanhattem
    hi,

    what exactly is your problem now? Do u want an answer why the testdb is slow or do you want to change the extents sizes or do u want to know how this happens ???
    I would like to know why this happens and how to change in extents in a script as well

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Talking

    Originally posted by Deshin13
    I would like to know why this happens and how to change in extents in a script as well
    The only way to change extents on tables is either by
    Export -> Drop -> Import (EDI)
    or
    Create NewTable -> Insert from OldTable -> Rename/Drop OldTable -> Rename NewTable to OldTable.

    The extents problem is one of how you size your tables when they are built. Most delivered Oracle DBs from vendors are not tuned when they are created. And even in-house built ones can sometimes go askew.

    The problem is that the db is designed originally to work whatever front-end package, but your company then actually puts it into production in a slightly different way than the original software is used by other companies. For example in an acctg database, the typical company has 6 journal lines for each journal header, but your company does 30 lines for each header. That causes the journal line table to increase 5 times faster than expected. Because the db is sized to expect a 6 rows, it blows through the pre-built dataspace and needs more dataspace sooner, causing it grab another extent.

    Another one is that Company A has 500 fixed assets because it only tracks items worth more than $500. Your company set the limit at $50 so you end up with 3000 fixed assets. That changes the amount of data that you have to maintain.

    Trust me, just about every DBA faces this problem. You just need to know the quick (tables/dataspace/tablespaces) run out of extents and need to be set unlimited) and long-term (monitor extents and clean them up on an occasional basis) fixes. How and what to analyze, monitor memory, partition tables, etc. It took me ovar year and multiple tweaks, touches and rebuilds to get my database to the 90% tuned level. At this point, any further changes would barely gain me anything.

    Just plug along, get training and post here for more advice. Many of us have been where you are and have slugged out these nightmares.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    alter table ... move also allows you to resize extents and other storage parameters, though it does have a couple of restrictions and is also very fast.

    Alan

  9. #9
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    This is the way that I am doing it...


    ALTER TABLE tablename
    storage
    (
    INITIAL 5120 K
    NEXT 5120 K
    MINEXTENTS 1
    MAXEXTENTS 70
    PCTINCREASE 0
    );

    Although the INITIAL EXTENT Size can not be changed.

  10. #10
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by Deshin13
    This is the way that I am doing it...


    ALTER TABLE tablename
    storage
    (
    INITIAL 5120 K
    NEXT 5120 K
    MINEXTENTS 1
    MAXEXTENTS 70
    PCTINCREASE 0
    );

    Although the INITIAL EXTENT Size can not be changed.
    You are never able to change the Initial Extent unless you drop and recreate the table. Your best case scenario doing it this way is 2 extents. And then you would have to do your NEXT large enough to hold all the rest of the data. That really doesn't help. That is why the EDI approach generally works best, unless your database is fanatically tied with referential integrity.

    I would also suggest that get a copy of TOAD. Helps tremendously in tuning and maintaining your DB.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  11. #11
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The initial extent can be changed using alter table move. If your using LMT then the initial extent will ofcourse be fixed).

    Anyway I tried this test and the initial extent (extent 0) does get resized.

    The interesting thing is the move doesnt always seem to resize all the segments (locking or tablespace coalesce issues?) in that there would sometimes be an extent in the middle of the table which would be 2-3x larger than the others. If the alter table move was repeated it would be OK.

    Alan



    CREATE TABLESPACE TEST
    DATAFILE '/dbs1/oradata/DEV/test01.dbf' SIZE 20M reuse
    DEFAULT STORAGE (INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 999 PCTINCREASE 0)
    ONLINE;

    create table x (a varchar2(4000)) tablespace test storage (initial 16K next 16K pctincrease 0)

    insert into x select table_name from dba_tables

    select * from dba_extents where segment_name='X'

    alter table x move tablespace test storage (initial 32K next 32K)

    select * from dba_extents where segment_name='X'

    alter table x move tablespace test storage (initial 128K next 128K)

    select * from dba_extents where segment_name='X'

    alter table x move tablespace test storage (initial 32K next 32K)

    select * from dba_extents where segment_name='X'

  12. #12
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by AlanP
    The initial extent can be changed using alter table move. If your using LMT then the initial extent will ofcourse be fixed).

    Anyway I tried this test and the initial extent (extent 0) does get resized.

    The interesting thing is the move doesnt always seem to resize all the segments (locking or tablespace coalesce issues?) in that there would sometimes be an extent in the middle of the table which would be 2-3x larger than the others. If the alter table move was repeated it would be OK.

    Alan
    Good Catch! I forgot about moving to another tablespace and back. As I said I just usually do EDI.

    Thanks for reminding me. I've hit the end of serious tuning about 10 months ago and it is just the monthly check to make sure everthing else is still good.

    Other points to check:
    Is your tablespace fragmented?
    Is your actual datafile on disk fragmented?

    For the datafile fragmentation you need a defragmentation utility like Diskkeeper on WinXX boxes. You also need to have your db shutdown for it to work. If you are on a UNIX box I don't know how fragmentation is handled.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •