DBMS_STATS package

[oradba88]

Hi!

I'm using DBMS_STATS in my DB.

Some tables are very huge, because of which the TEMP space is never enough & the stats do not complete. So part of my DB is analyzed & part isn't.

I need to know if there is a procedure or such, that I can use to pass the table names to dbms_stats, so I can specify which tables to analyze & which to skip.

Thanks.

[davey23uk]

gather_table_stats

or make TEMP bigger

[waitecj]

Are you computing or estimating stats?

[TimHall]

Hi.

Try:

EXEC DBMS_STATS.gather_table_stats('my_schema', 'my_table', estimate_percent => 10, cascade => TRUE);

Cheers

Tim...

[oradba88]

I'm computing stats. TEMP is 20GB. Isn't that too much already?

[yxez]

how about...
analyze table EMP compute statistics?
Is my answer too far and wild...

[Thomasps]

I'm computing stats. TEMP is 20GB. Isn't that too much already?

I dont' think you need to COMPUTE statistics for a large table rather use ESTIMATE with 10% - 20%. See the syntax from Tamil, how to ESTIMATE Statistics. I felt 20GB Temp space is something pretty big.

[yxez]

He is Tim not Tamil
mine is simplier...analyze table estimate statistics;
I'm the woman behind you.

[davey23uk]

He is Tim not Tamil
mine is simplier...analyze table estimate statistics;
I'm the woman behind you.

thats a really crap way of doing it which should be outlawed

[slimdave]

I dont' think you need to COMPUTE statistics for a large table rather use ESTIMATE with 10% - 20%. See the syntax from Tamil, how to ESTIMATE Statistics. I felt 20GB Temp space is something pretty big.

Even 10-20% may be over doing it. For many tables you'll get good statistics with 1-5%