Results 1 to 10 of 10

Thread: optimizing tips

  1. #1
    Join Date
    Apr 2004
    Posts
    31

    Unanswered: optimizing tips

    Hi ya!

    Does anyone have any genaral tips on how to improve the peformance of large databases? Perhaps of common problems? improving of snapshots, indexes? Or maybe alink to a helpful site perhaps... Any help would be greatfully appreciated...

    I would also, by the way, appreciate links to a site that would explain snapshots to me... I am as you by now would assume, a newbie on oracle

    thanks guys!

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Aha! Links! I'm good at them!

    This one gives a very brief overview of snaphots:

    http://www.joedunster.net/joeora/snapshots.html

    This link takes you to Oracle's online library:

    http://www.oracle.com/pls/db92/db92.homepage

    Type in some words or phrases & you should get pretty much what you need to know - the docs can be a bit scary, but they are very useful. If they are too 'heavy' then do a search in them for key words (so you can skip the dross).

    Improving performance is an art in itself - there are lots & lots of little tips - search this forum for words like 'performance' etc & you'll see what I mean!

    Good luck, and may the Force go with you!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Follow that second link of cis_groupie's to the Oracle docs, and then go to "list of books". You should then start reading with these:
    - Application Developer's Guide - Fundamentals
    - Concepts

    Print them out, or save the PDF version onto your PC, and study them - or at least, the parts you can understand! Re-read from time to time to pick up more info.

    For performance specifics, of course, go to the "Performance Tuning Guide and Reference". But really as a beginner it is more important to concentrate on understanding the basics first.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I dont have any links as such but here's how I go about tuning a database

    1) Analyze and tune the sql. If youve got access to the code then you can alter the queries themselves. Even if you dont though you can do lots of tuning by looking at v$sqlarea. I suspect 75% of performance improvements come from tuning the sql and knowing when to use indexes, reducing commits etc.

    2) Reducing and balancing the IO across all your disks. Sounds simple but is actually quite tricky. Best done by looking at IO stats through the OS as Oracle doesnt always know what is actually going to the disk and not the OS buffers. Also ensuring tables dont exist in say the same tablespace as their associated indexes etc.

    3) Tuning Database parameters like size of buffer pools, log buffer etc. This usually helps but you dont usually see big improvements.

    4) Tuning low level stuff like waits, enqueues and latches. Usually needed when doing large systems which are under heavy loads.

    5) Tuning the OS and network. Usually doesnt offer much improvement except in rare cases where there is a big bottleneck. For example I had an OLAP server pulling back GBs of data. Once we identified that it was the network which was the bottleneck we altered the OLAP server to only pull back the minimum data required (i.e. without duplicated strings which could be done by lookups) which reduced the time taken from 12 hours to 6 hours.

    Hope this helps, but tuning is probably the hardest part of being a DBA and is a bit of a black art sometimes.

    Alan

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    You should also think about gathering statistics for the database, either using Analyze or Gather Stats. This thread will get you going:

    http://www.dbforums.com/showthread.php?t=1007355

    This thread may also give you some ideas about the SGA:

    http://www.dbforums.com/showthread.php?t=1004215

    With reagrd to the whole Tuning issue, if it all starts to overwhelm you, pick one topic & go through it. Then pick another topic & go through it etc (as opposed to trying to do it all at once).
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    wait!
    there is a much simpler solution!
    don't listen to those guys!

    just change the init.ora file!
    open the file and change the parameter from
    oracle_performance = slow
    change to:
    oracle_performance = fast

    you COULD try 'medium', but I say just go straight to fast instead.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Quote Originally Posted by The_Duck
    wait!
    there is a much simpler solution!
    don't listen to those guys!

    just change the init.ora file!
    open the file and change the parameter from
    oracle_performance = slow
    change to:
    oracle_performance = fast

    you COULD try 'medium', but I say just go straight to fast instead.
    Pssst Duck, You think he will really look 8-)
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by beilstwh
    Pssst Duck, You think he will really look 8-)
    if it is not in your init.ora file then just add the line and bounce the database. now you are running a FAST db!

    enjoy!
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Mine was set as:

    oracle_performance = faster_than_a_speeding_bullet

    but as guns are now banned in this country I had to change it to:

    oracle_performance = fast_as_a_really_good_slingshot
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  10. #10
    Join Date
    Oct 2003
    Posts
    706
    A couple of general suggestions:

    (1) Observe, don't guess. Set up experiments and test them. Examine log-files and actual empirical data. In addition to commercial products you will find many public resources available on the Internet built by DBAs just like you. No one really learns anything by dimming the lights in the machine-room, setting up a crystal ball and saying "Ommmmm...." but plenty of "consultants" make a career of it.

    (2) When a computer process is "human-perceptibly slow," it is already screaming in pain. The most common cause is that the unit of work is vastly more complicated to the computer than the human expected it to be. Many programmers strive to "do it all in one query" and have utterly no idea how the magic-trick is done. (EXPLAIN is usually employed only as a post-mortem step.) The SQL server has no choice but to try to do what it is told to do, and the fact that it is able to succeed as well as it does is a testimony to the cleverness of the server's programmers, not that of the typical application's requests. "If it's slow, don't tweak it and don't throw hardware at it; find a better algorithm."

    (2a) Programmers also like to write these "godawful queries" (maybe they read GQ magazine ...) and wrap them up in transactions! So the poor server builds up this gigantic rollback-entry ... (It's no wonder the computer is howling in pain. The poor thing's constipated!) Again, (gently, politically) educating your application designers might be an ongoing task.

    (2b) Many tasks are easily performed in "chunks," and by running a series of queries in-sequence. But most programmers don't do that.

    (3) If "slowness" is accompanied by a reduction in CPU utilization (which normally should be hovering around 97-99% at all busy times), there is probably contention. If a queue of pending I/O-requests is building up, the problem is most likely insufficient or slow hardware, or poor arrangement of data on the drives.

    (3a) But this can also be caused by thrashing within the operating-system itself. Don't overlook load-throttling. If you measure the total amount of work completed per-second as the number of simultaneous requests goes up, the system reaches an overcommit or "thrash" point. The throughput curve exhibits a "bent-knee" shape, and when you hit the wall you drop dead... almost exponentially. If the system is throttled-back so that it is never able to over-commit itself, the backlog won't appear.

    (4) If "slowness" and a reduction in CPU utilization is not accompanied by the buildup of an I/O queue, then the most likely cause is contention among the requests themselves; locks, transaction isolation-levels and so on.

    It might sound crazy, but sometimes it's a good idea to walk into the machine-room and "watch the engine." Just watch. You can actually get a feel for where the root-cause of slowdowns can be just by "watching the blinking lights." Then test your intuitive ideas by measurement.

    Remember also: most performance-logs are useless, by themselves without further use of statistical analysis, because they tend to "lump all activity together." You need to identify "types of work" (my term...) that are flowing through your system, how often and in what volume they occur, how long they take to complete, what resources they consume and what caused them to wait. These "types of work" are not to be categorized by "what they do to the server," but by "what they mean to the business itself." ("A credit-card authorization," or "The Sales-Report From Hell.") When the data is meaningfully grouped by type-of-work on one axis, time on the other, and total transaction volume in a third, only then does useful information begin to swim out of the murky gloom.
    Last edited by sundialsvcs; 08-26-04 at 11:50.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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