>>
Every now and then a user generate a table scan (one way or another).
This table scan tends to "hang" the database for a while. The database
process uses ~100% CPU, and nothing else is allowed to happen on the
database during the table scan.
<<
If you are still having a problem after reviewing the advise already
posted may I suggest the following.
- Check that the combination of the large SGA and the number of Oracle
processes are not causing the OS to swap or do excessive paging. In UNIX
you can use sar -u, sar -w, and sar -p to see this with your admin.
- When this process is running how much free space is there in your
temporary tablespace? If the query sorts large amounts of data it may
well take all available sort area and cause everyone else to wait. You
can also have a shortage of temporary table locks which you set in your
init.ora. See item 3 to determine this.
- Look at the v$lock table to see if system resources or particuliar
table rows are being locked. You can use the sys.dba_ddl_locks and
sys.dba_dml_locks but these views are slow. You may also want to check
out v$latch and V$session_wait and v$rollstat.
- Check to see if you have rollback segment header contention.
- Check on the utilization of your buffer pool, ie, hit ratio and number
of read, dirty, and in use blocks.
- Go back to the OS and make sure you do not have a hot pack, ie, too
much data on one disk. Also verify that the tables and indexes for the
problem application are on different disks not just in different
tablespace.
- Check the number of extents the tables and indexes are in and if the
number is not near the optimal for your system then re-org them and
regenerate the statistics for these tables (if running cost based) and do
this even if the tables do not need re-org. Rule out a possible cause.
You should know or be able to figure out how to do the above. I will
admit that if I did not have scripts to do most of this I would have to
look it up since I do not do some of these queries every day.
Good luck.
Mark Powell -- The only advise that counts is the advise that you follow
so follow your own advise
Received on Sat Jan 04 1997 - 00:00:00 CST
