This content has been marked as final. Show 10 replies
Why you did not use partition feature?
You can create table with partition by year. Also, change location of datafile ralated tablespace.
select tablespace_name from dba_tables where table_name='YOUR_TABLE';
then offline tablespace, change datafile location to speed partition (disk) for improving IO.
Also set mbrc (multi block read count) this parameter determine the average number of blocks which the system reads in one I/O operation at a full table or index scans.
Gather statistic table, index.
Requiring 30 seconds to insert a single row in a table is not slow but ... very very very slow.
The right way to analyse a reproducible performance problem is to use SQL trace and TKPROF on the database session that is slow.
see http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php. So please trace your session, process raw trace file with TKPROF and post TKPROF output.
Are you mentioning the table1 stats?
No.. we didnt collect.
How to collect it (table stats)?
It is inserting of 15 columns per record.What kind of insert ? I meant INSERT INTO ... VALUES () or INSERT INTO ... SELECT ... ?
I have exactly similar issue. But here I am not talking about millions of records. So the difference that I have is.
1. I am talking about max 5000 records in DB.
2. Instead of moving few records, all records are deleted.
3. Stats process run every day midnight.
Now the select queries which were taking few seconds are now taking minutes to execute.
I had opened a ticket with Oracle and they said, whenever Table size is changed by more than 10% of its size, the plan and stats are stale. They have to be regenerated.
Oracle guy also said that Restarting Oracle Db reinitializes the plan tables internally and thats why it helps.
Restarting Oracle Db helps sometimes and sometimes it doesn't help.
This behaviour of deleting the records and re-using the application to generate the records was working from long time and all of a sudden problem started showing up.
1. Does this 10% change logic applies when the table size is small like 5000?
2. Why restarting DB helps sometimes?
3. Would updating the DB to 10.2.0.5 from 10.2.0.3 would help?