 Optimisation Strategies [message #454813] |
Fri, 07 May 2010 11:20  |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
Hi I have been given the task of optimising / performance tuning a large oracle database.
It has not yet been rolled out, so I do not know where there will be areas of bottlenecks / waits etc etc
The task of optimising it now is a pre-emptive initiative before it goes live.. is the following strategy to do this a good idea?
1. Determine specific scenarios and ways the database will be used
2. Write SQL that will be used to carry out these scenarios
3. Run the SQL and look at the response times and statistics
4. Add indexes / Partitions / optimise SQL statements to attempt a performance increase...
Does anyone have any other ideas of a better strategy?
Also I believe there are tools that can be used to optimise the structure of SQL statements when given one, as in re-formatting them and re-writing them... does anyone know of any of these tools?
Any help would be appriciated
|
|
|
|
|
|
| Re: Optimisation Strategies [message #454818 is a reply to message #454814] |
Fri, 07 May 2010 11:38  |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
Thanks, I have already read that, its doesn't really give much advice for the situation im in,
I have 3 days to optimise the database iv been given, I'm just looking for an overview from people opinions and experiences.. anything they can add to or criticise about the way I intended on doing this in my original post
|
|
|
|
| Re: Optimisation Strategies [message #454829 is a reply to message #454818] |
Fri, 07 May 2010 12:01 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This document is the reference, if you don't understand how to tune the database with it, you won't understand nothing.
What you are asking is for someone to do the job for you. So hire a consultant.
The steps to optimize a database are clearly describeb in Performances book.
And even more clearly in the old Performance Tuning Methods one.
Regards
Michel
[Updated on: Fri, 07 May 2010 12:02] Report message to a moderator |
|
|
|
| Re: Optimisation Strategies [message #454846 is a reply to message #454829] |
Fri, 07 May 2010 13:44 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
NO clearly I am not asking someone to do the job for me!
I am asking someone if they agree with the generalised overview that I have given
Why hire a consultant, when someone who knows what there doing can answer a simple question on here in 2 seconds
|
|
|
|
|
|
|
|
| Re: Optimisation Strategies [message #454849 is a reply to message #454848] |
Fri, 07 May 2010 14:22 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
Thanks Blackswan
The application does not exist, I have been given a dump file of a database, and i just have to attempt to have a look at how I place indexes / partitions on it where it may be needed.
This is my dillehma, It looks like im going to have to write some SQL which 'may' be used by the application, simply so I can make a list of suggestions where and why certain indexes or partitions may be needed.
Beleieve me I know its a reckless move, but this is what I have been asked to do.
Do you have any alternative suggestions, considering I am faced with a database, (which will be used for allocating employees to shifts ... not that this info helps) and no SQL, and I have simply been asked to experiment and see where they may need to put indexes / partitions...
Im not asking how to put indexes and partitions on... im asking about the methodology I use to face this task, if you see what I mean
|
|
|
|
| Re: Optimisation Strategies [message #454850 is a reply to message #454849] |
Fri, 07 May 2010 14:29 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
|
I have not even been asked to optimise the entire database, but just to have a look at some of the tables that will heavily been used, and make some suggestions
|
|
|
|
|
|
|
|
|
|
| Re: Optimisation Strategies [message #454938 is a reply to message #454854] |
Sat, 08 May 2010 11:43 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Your question is very broad so it is hard to answer with specifics. Let me try to give a little help anyway. How you tune depends upon several things but notably:
1) what kind of database are you building (Operational or Warehouse)
2) what is the specific SQL workload
If you are tuning an Operational System, then I suggest the following:
1) define all constraints (Primary Keys, Unique Keys, Foreign Keys). Make sure they are defined as NOT DEFERRED and IMMEDIATE (aka. use the defaults). I have reasons why I do not use deferred constraints or non-immediate constraints but I won't go into them here and I recognize that some people like to build apps that are LOOSE in their treament of data and thus must use deferred constraints.
As a note, many databases I have seen have been built by amatuers who have no concept of how insidious it can become when constraints are not defined properly on the data. I hope you don't have one of those, but if you are finding tables that do not have good Primary Keys, Unique Keys, and Foreign Keys, then go with the flow and see if later you can fix it.
2) make sure that all these constraints are supported by some index. With today's Oracle, indexes do not need to exactly match the constraint they support, and one index can support more than one constraint so you need fewer indexes now than you used to with previous versons of Oracle, but in genenral you should be able to say that each constraint is supported by index XYZ. Primary and Unique constraints will create an index for themselves if they cannot find one to use so if nothing else, make sure that all Foreign Keys map to some index where the FK columns are at the front of that index. Additionally let me suggest to you that, you do not need a unique index in order for you to have a PRIMARY KEY or UNIQUE KEY use that index. Consider what this means and how it can help you though since you are under the gun, not today.
3) collect stats the right way. DBMS_STATS. Do some reading online to get a better idea.
This should make a good week-1 setup.
4) After this, you need to run your application for a while aka. your trial period. They are running your system for a trial period right? This will put your SQL Worload into the database cache which you can then interrogate. You want to find the most expensive sql by CPU/Buffer Gets/Disk IOs/Sorts. Then you change the sql to make it do less of the above.
I have attached my wonder tool for finding expensive sql. This is one of my most powerful tools but since you came to OraFAQ I am letting you have it for free. Look at it, understand what it is trying to tell you, then amaze everyone with your power of observation in finding opportunities to tune your sql workload. I have been using some variation of this script for 24 out of the 25 years I have been working with the Oracle database. I got it initially from an Oracle Consultant (25 years ago Oracle charged 2000$/day for this one guy) who was called in to tune an oracle database and they assigned me to him because I was new at the job and they had nothing for me to work on at the time. What a stroke of luck for me eh! He was a little geeky so aside from a report to management, I ended up being the only guy he talked to for two weeks since no one else wanted to associate with him. Being just as geeky and a kid out of college with no life of my own at the time, this was an awesome two weeks. He gave me a few basic rules of thumb about tuning Oracle and after a quarter of a century, they are just as true and useful today as they were when he gave them to me, maybe even more so. Anyway...
5) once your system is outfitted/tuned as above, you need to pay attention to user complaints, and job runtimes to see if you are making your batch windows, and to see that user response times on screens are good enough. Notice neither of these requires that screens be as fast as possible or that batch jobs finish as quickely as possible.
Quote:AS FAST AS IT CAN is not tuning it is stupidity.
Quote:Tuning is MEETING REASONABLE EXPECTATIONS AND TIME LIMITATIONS.
If you are tuning a warehouse system, then that is another animal altogether and we will wait on that for another day.
Good luck.
[Updated on: Sat, 08 May 2010 13:11] Report message to a moderator |
|
|
|
| Re: Optimisation Strategies [message #454942 is a reply to message #454813] |
Sat, 08 May 2010 12:56 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
Wow, Kevin ... Just when I had given up hope with some of the poeple on this site!
You are tuley a star, thankyou very much, its people like you within the database community that us youngesters can truley learn from and develop our skills.
Once again thankyou for your insights
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Optimisation Strategies [message #454948 is a reply to message #454813] |
Sat, 08 May 2010 13:19 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
Micheal, as 'helpful' as you have been, it is not about the effort taken to read a document, I have read and read, you should consider what is more valuable, the good judgement and experience of a soldier on the front line or doing what the army manual tells you. Once again thanks anyway
Kevin, Yes I have downloaded the file, however have not had a play with it yet
|
|
|
|
|
|
| Re: Optimisation Strategies [message #454950 is a reply to message #454949] |
Sat, 08 May 2010 14:19 |
dsub42
Messages: 72
Registered: April 2010
Location: uk
|
Member |
|
|
No, the previous postings I explained that there was no programmed application yet .... you told me "yes there is" ... which ok fair enough whatever...
But I also stated that I would be creating the SQL to perform the business functions myself (which will no doubt be the same SQL used in the application)
Which now I have done, yes
Why?
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Optimisation Strategies [message #455060 is a reply to message #455053] |
Mon, 10 May 2010 01:25 |
 |
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 10 May 2010 01:11No, you cannot, with ratio you only get relative values, that is you can get a very bad query (relative to the ratio) that has a very low cost as it is executed only once.
Regards
Michel
Even this is True.
[Updated on: Mon, 10 May 2010 02:50] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
but with Ratio we can chart the Graph ( relative though) , and I think this can be a good step to Start with.