Results 1 to 14 of 14

Thread: Defrag a DB

  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Red face Unanswered: Defrag a DB

    Hi PPL

    I am quite new to this whole Oracle thing...

    We are having a problem with a Slow DB but there are quite a few records in it and I am not sure if that is the actual problem, we have created indexes on the tables which helped with the processing of records by it seems that the speed keeps dropping over time...

    We want to Defrag the DB cause we have 2 tables which has about 1gig empty but we have no idea how to. Can someone please explain it to me, in simple steps...

    Any help will be really appreciated...

  2. #2
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Just a little more information if needed

    The DB is running on Windows 2000 Server and its Oracle 8i Ver 8.1.7.

    We intially thought that it could be a Network problem but we took the Server on the net work and run the application on it and it was still slow..

    Can someone also recommend any other ways to enhance the speed of the database?

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Queries

    Does ur application use lots of SQLs.

    If you dont mind, could u post some of the queries u r running.
    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    A very quick method would be to Analyze all the tables in your schema and compute statistics on them.
    (you can create a script of download one very easily).

    That would be where I would look first. If you have many inserts/deletions of data then analyzation is needed to recalibrate your indexes.

    If still slow, work on query optimization and creating additional indexes.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Yes, the Application uses a lot of SQL because it does payroll calculations, the problem is that I dont have any of the code but this is what I got from a trace. Its does a few thousand selects, updates, and inserts in like a minute on our 1 DB but on the other one it takes about 10 minutes. It uses an ODBC Connection.

    Also can you recommed a script that I can use to Analyze all the tables ?

    "SELECT PNR, EFTERNAMN, FOERNAMN, SZINITIAL, ANKN, ANONYM, ANSTDATA, CLEARNR, DISPKOD, LUTBFORM, PERSORG, PERSSOEK, RAEKNR, RUMSNR, POKOD, FKKONTOR, SEKEL, LONTYP, ANSTSTAT, KORKORTSKOD, KORKORTANM, UPPBORDSKOD, UPPBORDSTYP, BIRTHDATE, TITLE, SEX, NINUMBER, ROLLNO, SORTCODE, ACCOUNTNO, MARITAL, ETHNIC, REGDAT, REGTID, REGSIGN FROM sysadm.OA0P0100 WHERE PNR = 'TP5468'\ 0"

    "SELECT AVLFORM, ARTAL, PERIOD, GFOM, GTOM, NARVARFOM, NARVARTOM, UTBETDATUM, EXTRA, KONTROLLUPPGIFT, KONTROLLAAR, KUPPGIFTSDATUM, SPCTEXT, OID FROM sysadm.OA0P0020 WHERE OID = '*' AND UTBETDATUM = 20030425 ORDER BY OID, UTBETDATUM DESC, EXTRA, AVLFORM\ 0"

    "SELECT B.FIELDNAME, B.LENGTH, B.DATATYPE, B.FIELDNAMESYSTEM, B.LENGTHSYSTEM, B.DATATYPESYSTEM, B.ID, A.OBLIGATORY, B.DECIMALER, B.DBMODIFIED, A.TABNR, A.IDSYSTEM, A.DBMODIFIED, A.FNR, A.POS, A.SUPRESSZERO, A.SELTYP, A.LOG, A.LOGKEY, A.LOGGROUP, A.LOGSPECIAL, A.TABTEST, A.TEXTALIGN, A.RELTESTTYP, A.RELFLTID, A.NO_CHECK, A.LEDTEXT FROM sysadm.OA0P0202 A, sysadm.OA0P0203 B WHER

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    To defrag the tables use alter table move (see the oracle sql manual on technet) then do an alter index rebuild (as all the table indexes become invalid). Then analyze the tables using BMS_UTILITY.ANALYZE_SCHEMA (see Oracle supplied PLSQL packages ref.).

    Alan

  7. #7
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Sorry... Can you explain it in a little more simplier terms, I really dont know much about Oracle

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Do you have an Oracle DBA book?
    This would come in very handy and only costs around $50 USD

    I don't think you need to move the tables, but I think you should rebuild the indexes and analyze the tables.

    Attached is a simple script to rebuild your indexes. You need to augment the script a tad for your schema names and such.

    the following code will work to analyze your tables. Run this as the schema owner.

    Code:
    set linesize 500
    set pagesize 0
    set echo off
    
    spool analyze.sql
    
    select 'analyze table '|| table_name ||' compute statistics;'
    from user_tables;
    
    @analyze.sql
    If you do not see better performance after doing these two things, then I would move the tables and then tune the SQL statements.
    Attached Files Attached Files
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jul 2003
    Posts
    4
    I doubt that rebuilding your indexes is going to do anything for you.

    Code:
    alter table my_table move
    should remove much of the empty space inside the table, which will speed up full table scans by lowering the high water mark, but wouldn't do anything for index-based access.

    It could be that you have some kind of instance-level problem, suchas nisufficient memeory allocated.

    I would really advise that you go to http://tahiti.oracle.com, and download the concepts manual and the performance guide, read them, and apply what you learn.

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Three steps, mostly what previous posters have said.....

    1: REBUILD TABLE
    When you do the "alter table mytable move" you are rebuilding the table into the same/original tablespace - the command is usefull not just to actually move the table but to force a rebuild in the same location. This will get rid of free space left by deleted rows. Free space (deleted rows *can* be a performance killer depending on the quantity/ratio).

    2: REBUILD INDEXES
    Doing (1) above will make any indexes on those tables invalid so you will have to rebuild the indexes for the table (which has the same effect of releasing free space in the index as per (1) above for tables).

    3: GATHER STATISTICS
    Now, the table has been rebuilt and so have the indexes... you should analyze the tables and indexes....

    analyze table MyTable compute statistics;
    analyze table MyTable compute statistics for all indexes;
    --------------------------

    So now you are in an optimum position to start considering where your performance is being hit. Slimdave is right to consider the instance level problem, but I would say only after you have done the above and tuned the queries running on the instance.

    EXAMPLE
    ======
    Table 1 - 1 million rows, 1% contain a certain key
    Table 2 - contains a referential keys to table 1

    No matter whether your tables and indexes are rebuilt, a where clause on the 1% on table 1 with a join to table 2 will almost always result in a full table scan of table 1 (and possibly table 2). This means you are relying on disk performance/throughput - and that isn't why we run Oracle.

    In this situation it wouldn't matter how much you tune your instance, the full table scans above will (usually) be constrained by disk performance/throughput.

    In Summary:

    1: Follow steps 1-3 above (all suggested by previous posters).
    2: Read a SQL tuning doc (including identifying current bottlenecks)
    3: Tune the SQL for best performance
    4: When you can't do any more on (3), then tune the instance.
    5: When (4) can't help anymore - consider logical redesign or if not appropriate, faster hardware/more memory etc

    There are plenty of links to help you (2) above, even a doc of my own :-)

    Hth
    Bill
    Last edited by billm; 07-22-03 at 22:08.

  11. #11
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Smile

    We want to Defrag the DB cause we have 2 tables which has about 1gig empty but we have no idea how to. Can someone please explain it to me, in simple steps...
    You have to export your data out of the fragmented tablespace, coalesce the tablespace, then import the data backup into. In 9i you can do tablespace export very easily.


    Hope that helps,

    clio_usa
    OCP - DBA
    Visit our Web Site

  12. #12
    Join Date
    Jul 2003
    Posts
    4
    Nope, that's a very very bad way of doing it.

    you should be using locally managed tablespaces, for which the coalesce step is irrelevant, because LMT's do not fragment like Dictionary managed TS's do.

    And I don't understand your comment on 9i tablespace export -- that's a way of copying the data by moving the data files themselves -- it is irrelevant to this issue.

    I think that the wrd "defrag" is misplaced in this thread -- what is actually wanted seems to be space reclamation, for which an "ALTER TABLE MY_TABLE MOVE" followed by index rebuilds would be the most appropriate methodology

  13. #13
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Okay...

    Just want to say Thanks for all the all the help..

    The route below is what really helped me, I used TOAD to view the fragments and there was almost nothing... I just dont understand Number 3, how does it work?


    Three steps, mostly what previous posters have said.....

    1: REBUILD TABLE
    When you do the "alter table mytable move" you are rebuilding the table into the same/original tablespace - the command is usefull not just to actually move the table but to force a rebuild in the same location. This will get rid of free space left by deleted rows. Free space (deleted rows *can* be a performance killer depending on the quantity/ratio).

    2: REBUILD INDEXES
    Doing (1) above will make any indexes on those tables invalid so you will have to rebuild the indexes for the table (which has the same effect of releasing free space in the index as per (1) above for tables).

    3: GATHER STATISTICS
    Now, the table has been rebuilt and so have the indexes... you should analyze the tables and indexes....

    analyze table MyTable compute statistics;
    analyze table MyTable compute statistics for all indexes;

  14. #14
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    This doc explains statistics, particularly in relation to Sql tuning. Maybe it will help.

    http://www.billmagee.co.uk/oracle/sqltune/index.html

    Cheers
    Bill

Posting Permissions

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