Home » RDBMS Server » Server Administration » Upgrade 816 to 901
| Upgrade 816 to 901 [message #161494] |
Sun, 05 March 2006 12:42  |
rdonneuk
Messages: 2
Registered: March 2006
|
Junior Member |
|
|
Hi,
I am upgrading from 816 to 901 on Windows. I would like to introduce things such as spfile, locally managed tablespaces, undo tablespace, dynamic sga, pga aggregate target, temporary tablespace with tempfiles, and statistics.
I have configured the test environment, and tests will be carried out over the next 2 weeks. However these tests exclude load testing, so it is very hard to get an idea of performance problems that may result.
Do you think I should simply upgrade in the first instance. And then, once things are going okay, do a second round, where i introduce the new features? the problem with doing it this way is that i may not be able to get colleagues to redo their testing. But as it is not load testing anyway, problems will probably not show up until too late. I would appreciate any recommendations - i.e. taking snapshots before upgrade statspack) and after, so i can compare....explain plans and so forth.
I am particularly concerned about stats, as currently, the database is set to choose, and there are not stats. I presume it will run better with stats, and set to cost based optimizer, however there is no sure way of telling, as no one knows much about the applications. I could always run explain plan before and after. Any suggestions?
Thanks
Rowena
|
|
|
|
| Re: Upgrade 816 to 901 [message #161555 is a reply to message #161494] |
Mon, 06 March 2006 00:50  |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
You said :
| Quote: |
I am particularly concerned about stats, as currently, the database is set to choose, and there are not stats. I presume it will run better with stats, and set to cost based optimizer, however there is no sure way of telling, as no one knows much about the applications. I could always run explain plan before and after. Any suggestions?
|
When Optimizer is set to choose
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.
The Best way for you to check performance gain/loss is to gather Database performance report before collecting stats & after collecting stats. (use Statspack report or similar tools for this).
You said :
| Quote: |
Do you think I should simply upgrade in the first instance. And then, once things are going okay, do a second round, where i introduce the new features?
|
Yes, in my opinion, you should first simply upgrade & then gradually test & implement new features.
Dear Experts, what you all say on this? Am I correct in saying this?
--Girish
[Updated on: Mon, 06 March 2006 00:51] Report message to a moderator |
|
|
|
| Re: Upgrade 816 to 901 [message #161594 is a reply to message #161555] |
Mon, 06 March 2006 03:57 |
rdonneuk
Messages: 2
Registered: March 2006
|
Junior Member |
|
|
Thanks Girish. I agree with what you said about the optimizer being set to Choose. I was actually trying to work out the impact on performance if I changed the system from choose and no stats, to cost with stats being refreshed frequently. I guess you answered the question by saying i should collect stats through statspack then run a report, then execute stats and change optimizer mode to cost and then collect stats through statspack and run a report, then compare them both.
I am not sure if statspack would be the best tool to use for this, or if i am better using explain plan or sqltrace...surely stats will mainly effect the execution path of sql statements and this is where is should focus rather than the stats gathered for statspack. What do you think?
Also, I can see where you are coming from regarding doing the migration, and then, as a seperate process, introducing new features, but there is still a chance that 9i will consume more resources than in 8i, hence i may be worse off leaving parameters as is.
Any advice is appreciated.
Thanks,
Rowena
|
|
|
|
| Re: Upgrade 816 to 901 [message #161635 is a reply to message #161594] |
Mon, 06 March 2006 07:03 |
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
|
Senior Member |
|
|
Hi
If you want just to measure the impact of collecting stats & then using CBO as optimizer, on the performanc of the system, then I think statspack should do. But if you wish to further improve the performce with features of 9i & can modify your application code's sql statements (in order to have optimum execution plan), then yes, going through explain plan & sqltrace will definitely be the way.
Yes.. I missed one point of yours in earlier post : " i may not be able to get colleagues to redo their testing." In that case, what I think is that you should first prepare the system with all planned parameters (that you wish to apply) & then move further with testing.
What you say on this?
--Girish
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 13:49:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
