Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle Performance Tuning steps

Re: Oracle Performance Tuning steps

From: Gavin D'Mello <>
Date: Mon, 01 Jul 2002 00:03:21 -0800
Message-ID: <>


        I'm working on a pidley sized db ( about 100mb) but sometimes when I try compiling a decent sized package i get a time out error saying the object is locked, while at other times the package compiles on the fly. 
        I'm trying to figure out why i get this error randomly. Some suggestions maybe that the compiler is going into a loop in my code which i seriously doubt. Is there any way I can find out what is causing the compile to hang up .... yes i know it says the object is locked but when no one is using the db ( that's when I update) why should the object (i.e. my package) lock ?
any help would be really appreciated,

thank you,


  Thnx Mike ,

  U saved at least 50-60 bucks of mine. Nyways can anyone tell me few links where I can find good documentation on Oracle Performance Tuning ? (Or books if they are worth byuing .............)

  Thnx in advance


   "Johnson, Michael " wrote:

    I have seen no other books that deal with Oracle waits the     way Gaja and Kirti's book does. After all, the waits are     what you are trying to diagnose in a slow system.

    There are several web sites floating around that have     some good technical papers on diagnosing slow systems.

    IMHO, I find that Oracle Books are like Stock Trading books. There     are only a few good ones floating around, but alot of them     offer advice that is down right dangerous, so choose wisely.

    FWIW.     Mike

      -----Original Message-----
      From: Chetan []
      Sent: Friday, June 28, 2002 4:13 AM
      To: Multiple recipients of list ORACLE-L
      Subject: Re: Oracle Performance Tuning steps

      Thanks , 

      I looked into the database. There r some waits happening on the undo blocks (non-system) but could not figure out whether this could possibly cause such a slowdown of the system. Also there were some indexes newly created on some of the tables which are causing problems. 

      What's the best approch anyways to hunt down the problem in a situation like this ? 

      - Chetan 

       BigP wrote: 

        cheten ,
        find processid of the application , look into database waits , that will give u some hint .Also look into db buffers to find if there are full table scans flushing db buffer . btw Did u ran statistics  ? 

          ----- Original Message ----- 
          From: Chetan 
          To: Multiple recipients of list ORACLE-L 
          Sent: Wednesday, June 26, 2002 3:38 AM
          Subject: Oracle Performance Tuning steps

          Hi guys ,

          Need some help. Actually we are looking here at a Oracle 8.1.7 db on HP-UNIX. The application was running fine uptil yesterday. Suddenly a part of the appln is running extremely slow. I can not figure what might be the problem. Wanted to track this down asap.

          Here is some information about the db.

          Database size - 20GB

          Optimizer - CHOOSE

          Disk Structure - RAID 1+0

          No. of processors - 4

          Block Size - 8K

          Archivelog mode : ARCHIVELOG

          Please tell me what should be the ideal way I should try to trace the problem. I thought of running UTLBSTAT/UTLESTAT or STATSPACK and asked the user to run that part of the appln. Has anybody worked with STATSPACK before ?

          Can anybody tell me what should accurate and fastest way to hunt down the problem ? I think its something to do with indexes or changes in the queries.

          Also can someone tell me the ideal backup strategy for this database considering the fact that it's a 24x7 system.

          Thanks in advance .

          Chetan Chindarkar

          Do You Yahoo!?
          Sign-up for Video Highlights of 2002 FIFA World Cup

      Do You Yahoo!?
      Sign-up for Video Highlights of 2002 FIFA World Cup

  Do You Yahoo!?
  Sign-up for Video Highlights of 2002 FIFA World Cup
Please see the official ORACLE-L FAQ:
Author: Gavin D'Mello

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Jul 01 2002 - 03:03:21 CDT

Original text of this message