Re: Large SGA - performance problems

Another possibility to add to the list:

Is the scan really an update?

Check your alert log during the "freeze up". You may have a "checkpoint not complete" message. This will freeze the db until it completes. If a checkpoint problem, then increase the size and number of your redo logs. Also consider setting "checkpoint_process = true" in your init.ora.

Or perhaps the archiver cannot keep up and all redo logs have been filled. If so, then also do the above tip.

>==========MarkP28665, 1/3/97==========
>
>>>
>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.
>
>1) 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.
>
>2) 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.
>
>3) 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.
>
>4) Check to see if you have rollback segment header contention.
>
>5) Check on the utilization of your buffer pool, ie, hit ratio
>and number
>of read, dirty, and in use blocks.
>
>6) 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.
>
>7) 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

Ron Beery
NCR
"Everything is computerized. What could possibly go wrong?" Received on Mon Jan 06 1997 - 00:00:00 CST