Home » RDBMS Server » Performance Tuning » Partition & Performance
icon5.gif  Partition & Performance [message #164767] Sat, 25 March 2006 16:57 Go to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
We have a large table (3GB, 12 million rows, 12 GB index) that holds security data. This size of this table increases rapidly
weekly. We also have some stored procedures loading data to
this table daily or weekly, however, the performance of the stored procedures are very slow even we optimized all queries from stored
procedures. We are thinking if some partitioning scheme will improve performance. If anyone has any suggestions, that will
be a great appreciated.

Thanks,
Lucas
Re: Partition & Performance [message #164773 is a reply to message #164767] Sat, 25 March 2006 21:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Ready, Fire, AIM!
Your application is slow, but you don't really know why.
So you plan on shooting into the dark (partitioning) hoping to get lucky.
You might want to consider improving the Buffer Cache Hit Ratio.
After running out of wild guesses, you might get desparate enough
to approach the problem scientifically.

http://www.amazon.com/gp/product/059600527X/qid=1143341921/sr=1-6/ref=sr_1_6/102-5950635-8036903?s=books&v=glance&n=283155

HTH & YMMV!
Re: Partition & Performance [message #164777 is a reply to message #164767] Sun, 26 March 2006 00:09 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
Thank you for your suggustion, if I have time, I will read
this book. However, i really need to know how partition can
improve the performance and if it is fit in my case.

Lucas
Re: Partition & Performance [message #164793 is a reply to message #164767] Sun, 26 March 2006 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Partitioning could make your application run slower, faster or no change.
It all depends upon the application & data.
The ONLY way to know for sure is to benchmark.
Re: Partition & Performance [message #164838 is a reply to message #164793] Mon, 27 March 2006 00:30 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Seriously, @anacedent's advice - whilst abrupt - is good.

However, I sense that you don't want good advice, you just want your question answered. So here goes:

Take a look at this doc I wrote - it descibes how partitioning may be used to improve performance. Any SQL that does not fit this model will probably stay the same or become slightly slower. It is unlikely to slow anything down significantly.

If you are looking for a free tuning resource, you can try the Oracle Performance Tuning manual, or look at the rest of my website.
_____________
Ross Leishman
Re: Partition & Performance [message #165538 is a reply to message #164838] Thu, 30 March 2006 20:21 Go to previous messageGo to next message
lucas4394
Messages: 24
Registered: October 2005
Junior Member
Thanks anacedent and rleishman for the good advices.

We are thinking about partition by different application
categories. Since we already have lots of stored procedures,
web applications are using this table, we don't know how
the changes effect our current tasks, for example, do we need
to modify the queries in search and update?

Lucas
Re: Partition & Performance [message #165546 is a reply to message #165538] Thu, 30 March 2006 21:32 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Did you even bother to click on the links I posted? Because you sure didn't read them.

Ross Leishman
Previous Topic: Balanced Decision on Index Creation
Next Topic: How I can Eliminate the Full Table scan for the following query?
Goto Forum:
  


Current Time: Thu Mar 28 15:44:31 CDT 2024