Re: Why is db slow after analyze?

Clarification on my gather_database_stats question:
 

While running the simple query on dba_extents, which should send data flying to the screen, but instead sends the result set in slow, small bursts, I noticed the Solaris CDE performance monitor graphs show CPU is pinned at 100% during the 45 seconds it takes to get the dba_extents data (which normally would come flying back in less than a second.)  None of the other performance monitors showed unusual activity.
 

Running the simple query (select segment_name from dba_extents) a second and third time produces the same results -- long run time, CPU pinned at 100%.
 

- Greg
<BLOCKQUOTE dir=ltr
style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">

• Original Message ----- <DIV style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black">From: Greg Moore

  To: <A href="mailto:ORACLE-L_at_fatcity.com"   title=ORACLE-L_at_fatcity.com>ORACLE-L_at_fatcity.com   Sent: Tuesday, June 12, 2001 5:25
PM
  Subject: Why is db slow after
  analyze?   

  I ran dbms_stats.gather_database_stats(   ); on a test machine with no other users.  <FONT   face=Arial size=2>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.
   
  <BLOCKQUOTE dir=ltr
  style="BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">