Re: Oracle 8 Tuning Question

Hi,
If you want exactly know whats happening with Oracle when your query is running, turn sql-trace on (SQL_TRACE=TRUE in init<sid>.ora or alter system ...) and use tkprof and explain plan to tune your sql statement first. After, use utlbstat, run your query, and utlestat. Use report.txt to tune your instance (you will find these scripts in '?/rdbms/admin'). It's possible that CPU usage goes 100% with one query. It's not rare. For example if you have large full scan, large aggregate etc. Of course, if you have 4 CPU and you don't use OPQ then 25 % total CPU = (meaby) 100 % of one CPU.

Good luck

Douglas Peel a écrit dans le message
<7j0s72$ghr$1_at_nntp9.atl.mindspring.net>...
>Hey all,
>
>I am fairly new to the Oracle game and I'm having a tuning problem. I am
>running a query on a fairly large table (1M records) and it seems to be
>taking much longer than it should. I'm running Oracle 8 on and NT4.0
Server
>box. What I've noticed is when the query is being executed, the total CPU
>usage rarely (if ever) goes above around 16-18% and never above 25%, but,
>the query can take several minutes (3-10 minutes depending on result set)
to
>execute. Why would the CPU usage remain so low? Why not use all of the
CPU
>time? The box has no other tasks running simultaneously that should
prevent
>it from taking what it needs.
>
>I believe that I have all of the tables indexed properly and the query is
>optimized, but, like I said, I'm new enough to not be 100% sure.
>
>Any suggestions on this would be greatly appreciated.
>
>Doug
>
>dpeel_at_mindspring.com
>
>
Received on Tue Jun 01 1999 - 10:33:30 CDT