Home » RDBMS Server » Performance Tuning » Partitioning In Phases and Impact
Partitioning In Phases and Impact [message #195260] Wed, 27 September 2006 11:51 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

In our production DB we have plan to implement partitioning on 12 huge tables in 3 phases 4 tables each time, on all the tables partitioning column is a Date field and it is going to be
range partitions,It's a OLTP application. Oracle 9i Rel 2 Version

One of the Question raised by developer is

" Since we are doing in phases that means on 1 weekend we will do on 4 tables, and after 15 days 2 phase and so on..
till the next phase the existing 8 tables are unpartitioned and 4 tables are partitioned,
He says the existing queries will have different plan and will behave differently, since now after 1 phase completion , it has to join partition tables with unpartition table,
But what i wanted to tell him since we are not indexing the partitioning column and the queries don't involve the partitioning column in the where clause it shouldn't have impact, it's just Global indexes on PK column , initially we are doing partitioning for administratio / maintenance purpose.

I know testing will yield some results, but since in testing and development DB size was small i did partition on all tables in 1 go so couldn't do more test of phase wise, regarding queries and select statement in DEV and TEST after partitioning things are same no gain no loss..

Based on your experience can you people put some light on this doubt of doing in phases.


Regards

[Updated on: Wed, 27 September 2006 11:53]

Report message to a moderator

Re: Partitioning In Phases and Impact [message #195311 is a reply to message #195260] Wed, 27 September 2006 20:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The likely effects are:

Desirable
  • Joins between partitioned tables using an equi-join on the partition col will be faster (because of Partition-wise Joins)
  • SQLs that previously performed a full scan for range queries on the partition column will be faster (because of Full Partition Scan)


Undesirable
  • If you have a huge number of partitions (say >500), then range scans on LOCAL partitioned indexes for queries that do NOT use the partition key will be slower (not by a lot, but they have to scan every partition - you would definitely notice it in a loop, or in system load with 100's of users). This will not improve when you partition the rest of the tables.


Some execution plans could change (unrelated to the points above), but usually for the better. Poorer performance would be unusual and unpredictable, and would be unrelated to the fact that only some of the tables are partitioned. You could deal with it on a case-by-case basis.

Ross Leishman
Re: Partitioning In Phases and Impact [message #195419 is a reply to message #195311] Thu, 28 September 2006 09:01 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Ross,

I really appreaciate your excellent comments !, It really helps me in going ahead, because of some unncessary developer's doubt manager get confused and made me think twice can we do in phase or should do in 1 go on all 12 tables which practically was not possible doing in straight 1 go.
Now as earlier planned i will be doing in Phases,
1 clarification on this "
Undesirable

If you have a huge number of partitions (say >500), then range scans on LOCAL partitioned indexes for queries that do NOT use the partition key will be slower (not by a lot, but they have to scan every partition - you would definitely notice it in a loop, or in system load with 100's of users). This will not improve when you partition the rest of the tables.

i have 120 partitions on each table,
I don't have Local Partitionied Index as i said earlier the partitioning Key column is not indexes as of know all are Global Indexes our initial aim is to partition for maintenace/Admin purpose.What do you that undesirable part will not happen in my case am i right ?.


Thanks for your valuable input.

Thanks Again .
Re: Partitioning In Phases and Impact [message #195465 is a reply to message #195419] Thu, 28 September 2006 18:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I know you said there would be no LOCAL indexes, I just didn't believe you.

GLOBAL indexes are a pain for maintenance/admin. You need to rebuild the entire index whenever you DROP/TRUNCATE/EXCHANGE a table partition. If you are only partitioning for backup purposes, forget I said anything.

Don't be surprised though if someone comes up with the bright idea of making the indexes LOCAL in the near future. With 120 partitions, I don't think you'll notice any problem though.

Ross Leishman
Re: Partitioning In Phases and Impact [message #195677 is a reply to message #195465] Sun, 01 October 2006 01:47 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Ross,

Another question on partitioned tables, Just want to know your input on this,
One of DBA says " DBMS_STATS doesn't work good on partitioned tables, analyze table is good "..

How far this is true any link or doc which says this.


Thanks
Re: Partitioning In Phases and Impact [message #195692 is a reply to message #195677] Sun, 01 October 2006 07:11 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> DBMS_STATS doesn't work good on partitioned tables, analyze table is good
That is just a lie.
DBMS_STATS works better on partitioned tables.
The major difference is dbms_stats will collect statistcs for individual partitions and the global statistics for the whole table.
Analayze will consider only the individual partition and derives the global stats from it.
Look into Oracle documentation.
Make use of the PARTNAME feature AND collect the stats in parallel.
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_stats.htm#999107.
Previous Topic: performance tools
Next Topic: want to see the time duration of a statement
Goto Forum:
  


Current Time: Thu Dec 08 16:18:43 CST 2016

Total time taken to generate the page: 0.10924 seconds