Oracle 10g Impact

[Steven Chen_1]

Hi,

I am planning to upgrade Oracle817 to 10g, but looking at the kernel configuration change requirements (quite a few parameters increases), I feel there must be some performance impact on the box.

Is there anyone who can comment this?

Thanks.

Steven

[Indira Aramandla · Accepted solution]

Hi Steven,

Yes upgrading from oracle 8i to 10g will involve in kernel parameters changes. When you say impact on the machine, well it depends on what other applications and databases you have got on the same server.
As a systems administrator, one will often be confronted with users who say that the response time on the system is unacceptable. What is unacceptable performance? There are two sides to this:
1) Quantifiable performance and the 2) Unmeasurable performance like user dissatisfaction.
Performance tuning Oracle databases can be divided into three subcategories:
1. Application tuning
2. RDBMS tuning
3. UNIX system tuning
For example the shared memory feature of the Unix operating system is required by Oracle. The System Global Area (SGA) resides in shared memory; therefore, enough shared memory must be available to each Oracle process to address the entire SGA.
If you create a large SGA and a large portion of your machine's physical memory is devoted to the shared memory, it will resulting in poor performance. However, there are also performance benefits to having your entire SGA located in shared memory. Therefore, when sizing your SGA, you need to balance Oracle performance needs with the demands your machine and operating system can meet without suffering performance degradation

So when upgrading the database after you got confirmation that your applications are supported and certified on the new Oracle Database Version you are moving to, it is highly recommended that you do test upgrade/migrate. This will allow you to test the process it self and also you will be able to test the upgraded DB performance under workload similar to the workload on the production DB. Doing so will allow you to discover problems earlier and fix them without affecting the production DB availability to the end users.

The following steps help you diagnose performance problems after upgrade/migrate

1- Clearly identify the problem.
Is the problem in general DB performance problem (i.e. every thing is slow in the DB) or it's specific to a certain application or SQL statement? This is very important question you have to answer before you proceed because it will determine what information we need to collect to identify and solve the problem.

(A) Database level problem
If every thing in the DB is running slower in the new version then a good point to start diagnosing the performance problem is the statspack report and OS statistics. Get a statspack report and OS statistics for the DB now when you have the problem and compare it to similar statspack report and OS statistics taken before upgrade (baseline).

If you identified some SQL statement to be the source of the DB slowdown, then you can also consider getting trace files generated from and . will collect information about SQL statements like Wait events, execution plans and row source information. will dump optimizer stats. Get the trace files for the same SQL statement from before and after the upgrade. You can then upload the trace files generated from both cases to Oracle support to identify the problem. Without the before upgrade/migrate traces it will be harder to identify the real cause of the problem and solving it may take longer time. This shows you the importance of backing up the DB or having the old DB on another machine. In some cases getting a system state dumps and hanganalyze traces is good idea and should provide more information that will help identifying the cause of the problem.

(B) SQL statements Problem
If you have identified the problem to be only with certain application and/or SQL statement then you need to concentrate on this SQL statement and find out why it is running slower now and try to tune it. Again getting trace files generated from and . will be the best thing to do in this case. Get the trace files for the same SQL statement from before and after the upgrade. Without this information it will be harder to identify the real cause of the problem.

Incase of SQL statements performance problems we looks for differences between the execution plans from before and after the upgrade, we look for wait events, statistics like number of physical and logical reads and many other thing available in the trace files.

2- Identify differences
Find the differences between different statistics and trace files you collected from before and after the problem. This information should guide you to the right path to solve the problem.

3- Tune it.
After you identify the problem, you should work on tuning it. The tuning process is outside the scope of this note but you can use the information you collected from step 1 and 2 to search Metalink and you should find plenty of information.

Attached is a document of few features og Oracle 10g regarding performance.

Indira A

[Yogeeraj_1]

hi,

for my 10g installation i had to adjust only a few of the parameters in the list:
====================================
KSI_ALLOC_MAX (NPROC*8)
MAX_THREAD_PROC 256
MAXDSIZ 1073741824 bytes
MAXDSIZ_64BIT 2147483648 bytes
MAXSSIZ 134217728 bytes
MAXSSIZ_64BIT 1073741824
MAXSWAPCHUNKS 16384
MAXUPRC ((NPROC*9)/10)
MSGMAP (2+MSGMNI)
MSGMNI NPROC
MSGSEG 32767
MSGTQL 4096
NCSIZE (NINODE+VX_NCSIZE)
NFILE (15*NPROC+2048)
NFLOCKS 4096
NINODE (8*NPROC+2048)
NKTHREAD (((NPROC*7)/4)+16)
NPROC 4096
SEMMAP (SEMMNI+2)
SEMMNI 4096
SEMMNS (SEMMNI*2)
SEMMNU (NPROC -4)
SEMVMX 32768
SHMMAX AvailMem
SHMMNI 512
SHMSEG 120
VPS_CEILING 64
====================================

Most probably because i had already finetuned it previously for running 9iAS. For sure, you will be requiring more memory...

good luck

regards
Yogeeraj

[Steven Chen_1]

Thank you all for the great help.

I would list a specific example for your opinion:

My box has 4G mem with conf. of dbc_max_pct 5%, min 3%, sga 1.6G, shmmax=1073741824, running Oracle 817.

If going to 10g with the recomm. kernel conf., do I see performance impact? Would more mem be needed, according to your experience?

Very appreciated!

Steven

[Jean-Luc Oudart]

Hi Steven,

Are you running the 32bits or 64 bits version (Oracle8i) ?

Shmmax in my view should be greater than your SGA (so in one segment).

Before you upgrade take a baseline for performance you can compare with in the future.

Regards
Jean-Luc

[Steven E. Protter]

There is a significant impact on performance.

SGA will have to be changed. You will probably need to tune the box in general.

Forget about the upgrade if you have less than 2 GB of Ram on the box. It could push low memory machines into paging mode.

SEP

[Steven Chen_1]

Specifically, I would like to get comment on shmmax if there are 4G phy mem. It would be bigger than sga, which is sure, then what is the available memory for SHMMAX?

[Jean-Luc Oudart]

Steven

I would put 4Gb for shmmax if you have 4Gb RAM on the box.

This is only a high water mark, it does not mean your SGA has to be that big.

Of course if you have opportunity (and budget) to increase the memory, Oracle will be pleased to use it !
In any case don't page !

http://docs.hp.com/en/TKP-90202/re87.html
http://docs.hp.com/en/939/KCParms/KCparam.ShMmax.html

Regards
Jean-Luc

[Jean-Luc Oudart]

Steven

you may read these threads too.

http://forums1.itrc.hp.com/service/forums/parseCurl.do?CURL=%2Fcm%2FQuestionAnswer%2F1%2C%2C0x8f2e8f960573d611abdb0090277a778c%2C00.html&admit=716493758+1104944507832+28353475
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=745598

Regards
Jean-Luc

[Indira Aramandla]

Hi Steven,

Refer to the attached document.



Indira A

[Indira Aramandla]

Sorry here it is.

[Yogeeraj_1]

hi steven,

you should consider raising the value of swap to at least a one to one ratio since your physical memory is at 4 GB.

With less memory you may have to raise it to a 3 to 1 ratio of swap to physical memory.

hope this helps!
regards
Yogeeraj

[Yogeeraj_1]

hi again,

also consider running the Pre-install checks as recommended by metalink note: 283731.1

In fact, Oracle Support has a flexible and reliable method of performing health checks on Oracle products and host environment.

Amongst the several tests, there are the following:
* OS certified?
* /tmp adequate?
* Swap (in Mb)
* RAM (in Mb)
* SwapToRAM OK?
* Disk Space OK?
* Kernel params OK?
* Links and libs OK?
* ulimits OK?
* User in /etc/passwd?
* Got OS packages?
* Got OS patches?
* Other OUI up?
* Group in /etc/group?
* Umask set to 022?
* LDLIBRARYPATH unset?
* SHLIB_PATH unset?

hope this helps!
regards
Yogeeraj

[Eric Antunes]

Hi Steven,

I'm realy impressed with your SGA! What are this huge 1.6Gb amount for? What values do you have for the following init.ora parameters?

db_block_buffers = ?
shared_pool_size = ?

Best Regards,

Eric

[Eric Antunes]

If you want to see what SGA you are efectively using, run the following query:

select name, bytes/1024/1024 "Free Size in MB"
from v$sgastat
where name='free memory';

Large unused space in the Shared Pool increase the probability of latch contention...

Eric

[Yogeeraj_1]

hi,

i think Eric's query missed the pool column ;)

it should rather be:
e.g.
yogeeraj@MYDB.MU> r
1 select pool, name, bytes/1024/1024 "Free Size in MB"
2 from v$sgastat
3* where name='free memory'

POOL NAME Free Size in MB
___________ __________________________ _______________
shared pool free memory 25.8043671
large pool free memory .5859375
java pool free memory 19.6132813

Elapsed: 00:00:00.01
yogeeraj@MYDB.MU>


regards
yogeeraj

[Hein van den Heuvel]

I would be less worried about problems, memory overload then most replies to so far suggest.
This upgrade is first and foremost an opportunity for increased performance.
Oracle has learned a lot over those years and has implemented lots of tools to help you, not to hurt you.
The system tuning will be minor concern compared to the other potential impacts.

As Inidira and others suggest, please make a serious attempt to collect a performance baseline while you are still using 8.1.7.
This would consist of
- a statspack output, and sar collection for a critical procution window in the day (perhaps 9am - 11am, or 2am - 4am, whatever your business dictates, and for an entire 'typical' day.
- Acknowledge that there is not typical, and that loads will change, and thus you should alss make an attempt to capture a repeatable benhcmark job, typically a heavy report that can be run over and over, but hopefully also a tasks the updates and inserts. Normalize the output with 'business elements processed' (customers? orders? phonecalls?...)

Typcially the 8.1.7. setup is still sort of locked into the past.
Explore and exploit the new opportunities:
o Rule based optimized ---> Cost based (but you need stats to make that work, and be sure to check execution plans for hot queries.
o Dictionary managed tablespaces --> locally managed (pre-define before import)
o Rollback segment --> Automatic undo
o Consider multiple pages sizes / sga pools?
o Static sort area --> pga aggregate target
o dynamic sql --> check out cursor_sharing = similar
o and on and on...


OS tuning will be a minor tweak in the end!

Embrace, Enjoy!
Hope this helps some,

Hein.