It may be because, the query is not properly tuned. You may also want to
check the value of SORT_AREA_AIZE parameter in the init.ora file. Check for
any paging and swapping activity on your machine.
--
Vijaya Kumar Vardhineni
Oracle DBA, EDS
Plano, TX
Erlend Dyrnes <erlend_at_bitcon.no> wrote in article
<5air7c$mee_at_swing.st.statoil.no>...
>
> Hi net!
>
> I'm pondering about some rather irritating performance problems we
> have, and wondering whether you have any suggestions for me to
> consider.
>
> Here's the thing:
>
> We run 2 HP9000 K420 machines in a HP cluster. Both equipped with 1GB
> of memory, 2 processors and fibre channel for internal communication.
> Disk ? Enough! One of them is our database server, running Oracle
> 7.2.3, and SQL*Net v1 (Yes, I know!). The database serves the other
> HP over the fibre channel into a Powerhouse application.
>
> Database characteristics:
>
> Sizes:
>
> TABLESPACE_NAME MB COUNT(*)
> ------------------------------ ---------- ----------
> GEN_FREE_TEXT 800 1
> INDEX01 2400 6
> RBS 800 2
> SYSTEM 100 1
> TEMP 400 1
> TOOLS 50 1
> USERS 50 1
> VMS 2000 5
>
> SGA:
>
> SQLDBA> show sga
> Total System Global Area 287680840 bytes
> Fixed Size 47936 bytes
> Variable Size 164589064 bytes
> Database Buffers 122880000 bytes
> Redo Buffers 163840 bytes
>
> optimizer_mode CHOOSE
>
> We have optimizer statistics.
>
> Tables, views and indices :
>
> Tables: 351
> Views: 40
> Indices: 607
>
> Concurrent users (approx) : 80
>
>
> OK, and now the problem:
>
> 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. Not even a sqlplus session login. The
> hangs may last from 5 seconds up to over 1 minute. As of now we can
> cope, but as we increase number of concurrent users, I'am afraid the
> problem will be a nightmare.
>
> I ask myself :
>
> 1. Is the hangs due to the very large SGA ?
> Is such a large SGA generally a good idea ?
> 2. Should I use the cost-based optimizer in Oracle 7.2.3 ?
>
> And now I ask you:
>
> Am I asking myself the right questions ?
> Any other experience on this matter you would like to share with me ?
>
> Regards.
>
> erlend
>
>
>
>
> Erlend Dyrnes: Senior systems consultant, MBS Fjerndata AS, Bergen,
Norway
> System manager (HP-UX) , application configuration
> email: Erlend.Dyrnes_at_mbs.no or k38122_at_ddb.be.statoil.no
> - I do *not* know what it means to miss New Orleans
>
>
Received on Fri Jan 03 1997 - 00:00:00 CST
