Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Exclamation Unanswered: DB Consulting Question

    Hi all

    I have a running system using oracle 9i since one year, and durinng this year we changed alot of the structure of the DB adding fields,constraints,tables...etc

    the database became slow
    do u think if we export the structure,data,constraints...etc and drop the DB then recreate it from scratch and then import the structure,data,constraints...etc to the DB, do u think this will make it faster?

    What do u recommed, and how I can do it

    thanx

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    It would be much easier to do a search through this forum (or a google search) for performance tuning. Recreating a Db from scratch may well resolve the problem but what happens in another year's time? Far better to read up a bit on performance tuning & then try out your new-found knowledge - people will be more impressed with you!

    Also, you need to be able to define 'slow', by which I mean 'Do you have benchmark figures against which to compare the Db's performance?'. I could hazard a guess & say that you don't, in which case you need to, once you've sorted the Db out to your satisfaction (hint: statspack might be of use). If you have benchmarks then when users complain that the Db is slow you can compare performance figures & see whether or not there really is a problem.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    My production DB isn't large (full export is about 2.5 GB in size and grows about 250 MB a year) and I found useful to recreate it once a year. It is Oracle 7 DB, though, not 9i as yours. I can afford myself to export DB at night, drop and create DB next day (in a matter of hour or so) and import it in next several hours.

    If nothing else, such a process performs defragmentation. Although, as I've read somewhere, fragmentation doesn't necessarily mean worse performances if datafiles are spread over multiple disks.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    just export and drop the TABLES (or the USER CASCADE).
    then recreate user/schema and import

    there is no need to drop the whole database and recreate it.

    also, are your statistics stale? have you been running statspack to look for
    poor performing code? have you been tracing sessions of the application to
    see what has been dragging the apllication?

    you are not really SOLVING the problem since you don't really know what your problem is. All you know is that the application is running unacceptably slow.

    tune the sql
    create proper indexes
    tune the db
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I agree with the_duck Oracle 9 runs cost based and if you don't have your statistics up to date, your database will run slower and slower as the stored statistics get out of date. The good thing is that it is easy to get them up to date. On our Oracle 9I database, I run a cron job every night at 3am that updates the statistics for the entire production schema and my database is very responsive.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    beilstwh, I update stats for our main schema once a month - I know that scheduled jobs 'cost' nothing to run, but is there a point at which no benefit is gained from updating stats? I am considering changing the job to run once a week instead of once a month, but don't know how to predict the tangible benefits to be gained from doing this.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by cis_groupie
    beilstwh, I update stats for our main schema once a month - I know that scheduled jobs 'cost' nothing to run, but is there a point at which no benefit is gained from updating stats? I am considering changing the job to run once a week instead of once a month, but don't know how to predict the tangible benefits to be gained from doing this.
    Depends on how 'active' your tables are.
    I was refreshing statistics nightly just so I could evaluate
    production and deveopment apples-to-apples.

    Can't tell you how frustrating it is to see different plans on dev vs.
    production solely because their statistics were not both up to date.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This is why I like being a DBA so much - so much of it is 'wet your finger & stick it in the air'! Based on what you've said, The_Duck, I'll change to weekly for now & review in due course. (We don't have 'production' & 'development', just 'test' & 'live' environments.)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

Posting Permissions

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