From sqlgreg@pacbell.net Tue, 12 Jun 2001 19:36:24 -0700 From: Greg Moore Date: Tue, 12 Jun 2001 19:36:24 -0700 Subject: Re: Why is db slow after analyze? Message-ID: MIME-Version: 1.0 Content-Type: text/plain >> Drop the statsistics as soon as possible off the SYS/SYSTEM objects.   When I do nothing more than bounce the db, two things happen.  The simple select on dba_extents immediately performs in less than a second (instead of 45 seconds).  Also, right after doing the dbms_stats.gather_database_stats, the query on dba_extents returned approx 3,400 rows.  But after the bounce it's only returning approx 2,300 rows.  It appears that 1,000 extents went away.   I'm wondering if the problem is related to RBS's, and if an inefficient number or sizing of RBS's may be causing the high CPU usage.       
----- Original Message -----
From: Christopher Spence To: Multiple recipients of list ORACLE-L Sent: Tuesday, June 12, 2001 7:15 PM Subject: RE: Why is db slow after analyze? If you analyze SYS/SYSTEM you will cripple the performance of the database, namely the table you mentioned and exports. Drop the statsistics as soon as possible off the SYS/SYSTEM objects.   This bug was suppose to be fixed in 8.1.7 and existed for some time, I am not sure if it ever got fixed as I have not tested lately.     "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -----Original Message-----From: Greg Moore [mailto:sqlgreg@pacbell.net]Sent: Tuesday, June 12, 2001 9:41 PMTo: Multiple recipients of list ORACLE-LSubject: Why is db slow after analyze? I ran dbms_stats.gather_database_stats( ); on a test machine with no other users.  After this, running a simple query on dba_extents was extremely slow.   Does anyone have an idea on what's the likely cause of this?  I'd like to check after gathering stats, but am not sure what to look at to verify why the simple query runs so, so slowly.  (Bouncing the db brings things back to normal, but doesn't help me understand what the holdup was.)   Thanks.