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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
