[tal_olier]

Hi,

We have a strange case at a customer site that his Oracle rewrite queries that hangs if we collect statistics on a table.
In order to disable rewrite (query_rewrite_enabled already set to false) I have deleted the table histograms using the ANALYZE table DELETE STATISTICS and the re-write stopped.

We can verify that histograms data is created using the following query:
SELECT TABLE_NAME,COLUMN_NAME FROM DBA_HISTOGRAMS WHERE TABLE_NAME ='<table_name>';

I tried using ANALYZE command or DBMS_STATS package (compute or estimate) in various options but both have created histograms data all the time.

I have tried looking in Oracle docs but found not syntax to disable the histogram data collection.

Is there a way to do it?
Idea anyone?

Thanks in advanced,

Tal (otal@mercury.co.il).

[evanhattem]

Hi,

for as far as I know, collecting statistics always result in at least 1 histogram per table / index. There's no way to avoid this.
Seems like you have trouble using the Cost Based Optimizer.
Did you try to avoid the CBO by changing to the rule based optimizer? Or is your db version too high (9i)?

Other wise i think it's time to contact oracle about this.

Edwin

[AlanP]

You could also try the norewrite hint which may disable rewrite if you have access to the sql.

Alan