This discussion is archived
10 Replies Latest reply: Feb 15, 2013 8:15 AM by 858716 RSS

DataBase is very slow performance

590875 Newbie
Currently Being Moderated
Hi,
we are using oracle 10g (10.0.2.0.3 version). We have table1 which had 4 million records lastweek.
For better performance, we decided to move records less than JULY 2010 to history table (table2) which is created as like table1. We used INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE STARTDATE < TO_DATE('070110','MMDDYY') query. It has moved some 3.5 million records from table1 to table2.

After moving these records, we have deleted the moved records from table1. Our query is accessing only table1.

We have expected better performance. But after this Move and Delete operation, Oracle performance is very slow. It is taking more than 30 to 50 sec to insert single record into table1 which is currently having only 400000 records alone.

We have index on STARTDATE field in table1. We have recreated the index and rebuild it and we restarted the server also. But it dont make any difference. Oracle performance is very slow.

How to solve this problem?

Regards,
Senthil
  • 1. Re: DataBase is very slow performance
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Have you gathered stats as well after deleting so many rows ?
    It is taking more than 30 to 50 sec to insert single record into table1
    What kind of insert is it ?

    Nicolas.
  • 2. Re: DataBase is very slow performance
    Ulfet Tanriverdiyev Journeyer
    Currently Being Moderated
    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.

    Rebuild index.

    Gather statistic table, index.
  • 3. Re: DataBase is very slow performance
    P.Forstmann Guru
    Currently Being Moderated
    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.
  • 4. Re: DataBase is very slow performance
    590875 Newbie
    Currently Being Moderated
    Hi,
    Are you mentioning the table1 stats?
    No.. we didnt collect.
    How to collect it (table stats)?


    It is inserting of 15 columns per record.

    Regards,
    Senthil
  • 5. Re: DataBase is very slow performance
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    user587872 wrote:
    Hi,
    Are you mentioning the table1 stats?
    No.. we didnt collect.
    How to collect it (table stats)?
    exec dbms_stats.gather_table_stats(user,'TABLE1',cascade=>true)
    It is inserting of 15 columns per record.
    What kind of insert ? I meant INSERT INTO ... VALUES () or INSERT INTO ... SELECT ... ?

    Nicolas.
  • 6. Re: DataBase is very slow performance
    137669 Explorer
    Currently Being Moderated
    Hi,

    I recommande you to execute an ALTER TABLE MOVE t1, to rebuild all indexes of the table t1 and to gather the statistics for t1.

    Regards
    Maurice
  • 7. Re: DataBase is very slow performance
    590875 Newbie
    Currently Being Moderated
    Hi,
    It is
    Insert into Table2 select * from table1 query.

    Regards,
    senthil
  • 8. Re: DataBase is very slow performance
    858716 Newbie
    Currently Being Moderated
    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.

    Now questions...

    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?

    Any clues?
  • 9. Re: DataBase is very slow performance
    sb92075 Guru
    Currently Being Moderated
    userAtoZ,

    you should start your own thread


    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 10. Re: DataBase is very slow performance
    858716 Newbie
    Currently Being Moderated
    Thanks sb92075 for reply.
    But since problem is very similar and it is still unanswered, I thought of using this thread.
    Lot of information about the scenario may help you guys to comment precisely.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points