Home » SQL & PL/SQL » SQL & PL/SQL » please help with statistics!
please help with statistics! [message #228042] Fri, 30 March 2007 22:06 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, I am in trouble Smile I analyzed a table and my query is running twice as slow Sad Is there a way to drop statistics on a table? thank you!
Re: please help with statistics! [message #228043 is a reply to message #228042] Fri, 30 March 2007 22:32 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
nevermind, figured out. I was wondering though if we have an optimizer set to CHOOSE and I gather statistics on the table, why would my selects actually run slower? I thought that for CHOOSE, Oracle will see if there are statistics gathered and if they are then it will use these statistics..Any insight would be great, its like 11:30 pm on friday and I need a life Smile
Re: please help with statistics! [message #228051 is a reply to message #228043] Sat, 31 March 2007 01:14 Go to previous message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
I guess it depends on a few things. For instance:
How did you gather statistics. If you did a dbms_stats.gather_schema_stats('<schema>') then probably the indexes were not analyzed. You could try the following for analyzing: dbms_stats.gather_schema_stats('<schema>', cascade=>true); This tells dbms_stats also to analyze the underlying indexes also.

When you have no statistics at all the optimizer will work rule-based. I can imagine that the eisting indexes are used, in that case. In that way the query could be efficient.

When gathering statistics whithout the index-statistics I can imagine (not to sure) that the indexes will not be used. The query can be very slow then. The optimizer will work cost-based then.

Maybe someone with some more knowledge can telle whether the above is BS or actually contains some truth.

Maybe you could post an explainplan and the query. We then have the opportunity to see what is happening.
Previous Topic: dimensions
Next Topic: about indexes
Goto Forum:
  


Current Time: Sun Dec 11 02:26:06 CST 2016

Total time taken to generate the page: 0.04483 seconds