Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Oracle performace degradation problem

Oracle performace degradation problem

From: Nata <ignata_at_gmail.com>
Date: 28 Feb 2006 04:12:54 -0800
Message-ID: <1141128774.873771.146080@t39g2000cwt.googlegroups.com>


Hi,

Original data: a huge schema - dump in size of 4-5G, e.g., one of the "big" tables counts 10,393,403 rows.
We decided to "archive" part of the schema. "Archive" is our internal application that creates similar empty schema on the same data base and executes copy of data from original schema A to archive schema B and after that deletes transfered data from original schema A. After archiving process, the table that counted 10,393,403 rows counts 4,803,078 rows. Of course, not only this table was influenced.

After archiving the analyze (gather_schema_stats) was performed and DB instance was restarted and tests were performed. Now the problem: we discovered performace degradation of some queries on the original schema in 200%-300%. The question: WHY??? Ok, we have not executed yet de fragmentation and index rebuild, but WHY performace results got worsen and not remained at least the same????

P.S. We executed after that de fragmentation (alter table move)+index rebuild+another analyze with cleaning of statistics and compute option instead of gather_schema_stats. Performace improved, but degradation still exists. Received on Tue Feb 28 2006 - 06:12:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US