Oracle Database Discussions

Hi,

Let me give you my current scenario.

My Txn table is huge and used very often. hence it was designed earlier as IOT with range partition on quarterly basis. so 3 months of data will reside in each partition and we have MAXVALUe as TOP partition, Since is daily transaction data you will not get data with future date

We also have a stats program runs every day to update the stats of this txn table.

So to summaries

*. TXN table is IOT
*. All index are local index
*. Quarterly Partition by range.
*. Stats update on daily basis.

So my query on this table will run less than a sec an any given input..


During q2, we added new tablespace, partition and altered index to include new partition. (say its created on March end). so when April 1st comes and data are loaded to this partition (arounf 20 records only).. my query took very long time.. infact it started to hung.. when checked on OEM (Oracle enterprise manager). It seems like Oracle has created 2-3 Query plan and started to use the BAD query plan...

hence the CPU usage was very high.. and same query take ages to return result.

when we asked DBA to check on this.. they done some thing and It started working fine.. when i checked the OEM my query was not using the BAD query plan.. it was using the different one.

time passed..

Same issue happened when we tried to add partition for Q3.

I really dont understand.. y adding partition should causes performance issue. So i have to analyse the table after i create the partition or rebuilt the index? how does it works?


Regards
Balaji Tr.