Home » RDBMS Server » Server Administration » Oracle 8i and NT Enterprise Performance
Oracle 8i and NT Enterprise Performance [message #53612] Tue, 01 October 2002 14:23 Go to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
I'm running Oracle 8i on an NT Enterprise 4 box with 6 xeon processors, 4 gig ram and an extremely fast external raid(5) array. Query time is long, processor usage averages about 1%, and only 1 of 15 Oracle threads appear active. Even when I run queries with the parallel hint I never see activity on more than 1 processor.

What can I do to use the resources of the server more effectively?

Report message to a moderator

Re: Oracle 8i and NT Enterprise Performance [message #53613 is a reply to message #53612] Tue, 01 October 2002 15:08 Go to previous messageGo to next message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
What you have to look into is all the PARALLEL_xxx_xxx
parameters in your init.ora file.

You can start with the PARALLEL_AUTOMATIC_TUNING:

When PARALLEL_AUTOMATIC_TUNING is set to TRUE, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.

If you used parallel execution in a previous release and are now enabling PARALLEL_AUTOMATIC_TUNING, you should reduce the amount of memory allocated from the shared pool to account for the decreased demand on that pool. This memory will be now be allocated from the large pool, and will be computed automatically if LARGE_POOL_SIZE is left unset.

As part of the automatic tuning, Oracle will enable the PARALLEL_ADAPTIVE_MULTI_USER parameter. You can override any of the system-provided defaults if desired.

Hope that helps,

clio_usa
OCP - DBA

Visit our Web site



Report message to a moderator

Re: Oracle 8i and NT Enterprise Performance [message #53619 is a reply to message #53613] Tue, 01 October 2002 15:46 Go to previous messageGo to next message
Tony
Messages: 190
Registered: June 2001
Senior Member
Thanks for the reply.

PARRALEL_AUTOMATIC_TUNING is set to TRUE, parallel_min_servers =4 and parallel_max_servers =128.

I believe I read that if a table is set to a default degree of parallelism the indexes are negated. Do you know if that's true?

Either way, even using the parallelism hint, I can't explain the poor performance of the server.

Report message to a moderator

Re: Oracle 8i and NT Enterprise Performance [message #53627 is a reply to message #53613] Wed, 02 October 2002 04:36 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
well .. SQL tuning is something like wild goose chase..
All things should be considered.
to start with.
1.  increase your sort_area_size SIGNIFICANTLY.
    atleast 5-10 times would do.
    also, make sort_area_retained_size  equal to 
    sort_area_size.
2.  Increase the temp segments available.
3.  Make sure this user (running the query) and any
    other user (including sys) are using TEMP as
    temprary tablespace.
4.  Make sure, the sql utilizes the indexes. If
    possible, rebuild the indexes.

if any of these are helping atleast to some extent,
then u can get in Parallel executions.

Report message to a moderator

Re: Oracle 8i and NT Enterprise Performance [message #53660 is a reply to message #53613] Thu, 03 October 2002 12:04 Go to previous messageGo to next message
Tony Pilant
Messages: 1
Registered: October 2002
Junior Member
sort_are_size is 250000000 at db_block_size of 8192. I've changed the sort_are_retained_size to match.

Users are pointed to temp as a temporary tablespace. I have 5 temp datafiles assigned to the temp tablespace. Temp segments are set to 640 k with a max of 2 gig per datafile.

We do have what I condiser to be a large database (65 gig), data is stored based on date time, so the indexes are always changing. Reindexing improves performance for a while, but still typical cpu use is 1%. One cpu may jump to 20% during a query while the other 5 are basically flat line, and only one Oracle thread shows activity.

Report message to a moderator

Re: Oracle 8i and NT Enterprise Performance [message #59175 is a reply to message #53612] Sun, 02 November 2003 21:43 Go to previous message
Tony
Messages: 190
Registered: June 2001
Senior Member
For those who may be interested I found the problem.
When Oracle was installed (not by me ;-) ) MTS was not enabled. On install, if you want to use parallel processesing, use the custom configuration and enable multi threads.

Report message to a moderator

Previous Topic: PERFS : Is separate data/index into different tablespaces is better ?
Next Topic: User Creation script for Navneet
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 11:31:43 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.