Oracle 8.1.7 Performance and Tuning

[kimosabe]

How do I improve my performance on Oracle 8.1.7 running on Windows NT? Does anyone have any tech/tips that can be supplied for all the parameters that I can modified and improve? Your patience will be very much appreciated.

PS
I have modified the common init<sid>.ora parameters. Please forward any additional parameters or articles that I can check. Thank you.

 

[MikeLacey]

Kimosabe,

That is a large question.

Could you tell us a little more about the system please? Mike
______________________________________________________________________
&quot;Experience is the comb that Nature gives us after we are bald.&quot;

Is that a haiku?
I never could get the hang
of writing those things.

 

[kimosabe]

Oracle 8.1.7
Windows NT 4.0
Memory available 2gb
2 disk C= 5 gb
D= 100 gb
Running 2 database in 1 instance
Users Max = 10 including Oracle
This system links with SAP when a request from SAP is issued.

When I log on to the system, I think it should have more power due to the resource that is available. I may have not all the parameters define properly? What are ther other parameters that should be taken into consideration?

I did the DBBuffers, DB blocks, Multi blocks, # of files and SHare memory.

Do you have any other advise? Your patience will be very much appreciated. Thank you.

 

[casajarm]

You should be wary of changing the init.ora unless you have a reason and have researched it well. That said, some of the initialization settings that can affect SQL performance are

OPTIMIZER_MODE
SORT_AREA_SIZE
HASH_AREA_SIZE
HASH_JOIN_ENABLED

There are many others but tuning is usually done at the statement level not with system level settings. TOAD by Quest Software has an excellent add on called SQL Tuning Lab which can lead you through the process.

You should look at some of the queries and determine an indexing strategy.

You should make sure you analyze the tables and indexes using ANALYZE TABLE table [COMPUTE|ESTIMATE] FOR ALL INDEXED COLUMNS. You can probably use the COMPUTE option unless you have some really really big tables.

You might want to buy the Oracle SQL Tuning Pocket Reference from O'Reilly.

 

[kimosabe]

Thank you. That is helpful. I will read them and apply them carefully. If you have more, it will be very helpful. Thanks.

 

[MikeLacey]

Oracle is a multiprocess database and will benefit from more than one processor if they're available.

If you find that the disks are very busy you will get a performance benefit from storing the data on muliple disks, in a stripe set, rather than on a single disk.

If possible, put data on one stripe set (or disk) and indexes on another.

And by stripe set - I don't mean raid 5 Mike
______________________________________________________________________
&quot;Experience is the comb that Nature gives us after we are bald.&quot;

Is that a haiku?
I never could get the hang
of writing those things.

 

[kimosabe]

Thanks Mike. I will check for raid 5 availability. I am also checking my indexes, my data files, and SGA. I am looking for more obvious performance parameters which can be part of my checklist that can impede performance. If any kindly advise.

Again, thank you for your patience and help.