Re: Need Help.

Uday,

Your problem may have more than one component. There are three immediate things I'd try/look at, one of which will provide you with information, the other two that may or may not improve your performance. For information on your select statement, start a trace on the session, run the select statement, than analyze the trace file using tkprof with the explain option. If you need specifics on how to do this, check out OTN - http://otn.oracle.com/index.html - search for Explain Plan. This should give you Chapter 13 of the tuning manual, you'll need Chapter 14 as well.

I'd also check with your Systems Administrator to see if you're running Veritas Quick I/O. If not, asynchronous I/O is probably not working on your box. There was a big discussion about this last year on the list, and I ended up changing my init<SID>.ora file, setting disk_asynch_io = false and using multiple DB Writers. Depending on your box, you may want to use I/O slaves instead (either time responses or use a trace and tkprof to determine which works best in your case.).

Also, check to see if your optimizer_mode is set to choose or rule. If choose, analyze your schema/database with compute statistics and see if that doesn't help. You may also want to use hints in your SQL. The cost-based optimizer has a number of other parameters which may help you. You can get these from OTN by searching for Initialization Parameters, reading the summary of what each does, and getting more specific information from the tuning guide.

Also get a book(s). I've got a bunch, but I highly recommend Oracle 8 - The Complete Reference by Kevin Looney and George Koch. Not complete, nothing beats experience, but you will get enough information to guide you towards determining possible solutions. If your app is using java, check out Adrian Cockcroft's Sun Performance and Tuning: Java and the Internet.

Tuning is fun. Have a blast, and oh - don't forget the old standbys iostat and vmstat. You didn't mention anything about your disk configuration. Or your SGA. There's also some v$ views, v$sysstat, v$waitstat, v$buffer_pool_statistics, v$session_wait and a host of others (is timed_statistics set to true in your init.ora?).

So many tools - so little time. Start with the obvious, Explain Plan, Disk I/O and Optimizer to get the biggest bang for your buck, then work your way deeper.

Hope this helps.

David A. Barbour
Oracle DBA

udaycb wrote:
>
> Hi All,
> Recently we moved the database(Oracle 8.0.5) from NT to Sun machine. Now
> this database is going online very soon. When we were testing we observed
> that, the application (select statement) is running very slow. (I don't know
> how fast it was earlier). I have a feeling that it is not using indexes
> properly. I have created 6 indexes on that table on each columns. also
> created a composite indexes. How do I know that the sql is using which index
> for select statement.? Is there any other ways to improve the performance?
>
> Thanks
> Uday
>
> ------------------------------------------------------------------------
> Name: winmail.dat
> winmail.dat Type: ABC DataAnalyzer 1.1 Chart
>(application/x-unknown-content-type-ABCData)
> Encoding: 7bit

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: David A. Barbour
  INET: dbarbour_at_nucentrix.net

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: ListGuru_at_fatcity.com (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).