Re: question about DBMS_STATS package/Statistics?

energie wrote:

> Hi all.
>
> I have a question regarding the statistics gathered after I called for
> example DBMS_STATS.GATHER_TABLE_STATS?
>
> this is what I am trying to do:
>
> I am trying to make use of the statistics Oracle uses to do its query
> estimation.
> In SQL server, if they have a table with 100000 rows of records, the
> estimator might sample 5000 rows and store it in their system tables,
> and later on use this when they need to estimate a query.
>
> I know ORACLE has this as well, but I've tried to look at what
> GATHER_TABLE_STATS, GET_TABLE_STATS etc will return. But I can't even
> see what they store.
>
> Right now I am just testing with SQL PLUS, can someone give me a hint on
> how to view the "STATS" gathered after calling GATHER_TABLE_STATS?
>
> this is my idea:
>
> create a cursor and have that cursor point to the resultset that It
> generates ( I am hoping that it will generate some kind of resultset ).
>
> I find it very hard to look for examples and samples on the
> DBMS_STATS package.
> this is some of the stuff I've found so far:
>
> http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a8-
> 9852/dbms_11g.htm#1004271
>
> http://www.csis.gvsu.edu/GeneralInfo/Oracle/appdev.920/a96612/d_stats.h-
> tm#1012305
>
> I've looked at thse stuff for a very long time, but I still don't have a
> good grasp on it.
>
> It would be great and very helpful if someone can point me to the right
> direction.
>
> Thanks in advance!
>
> --
> Posted via http://dbforums.com

I would forget GATHER_TABLE_STATS and use GATHER_SCHEMA_STATS. The ability to gather stats on tables and indexes CASCADE=>TRUE helps.

Then build a new table, examine the null columns in ALL_TABLES, gather statistics, and take another look at ALL_TABLES. You will see the statistics that have been gathered.

Dan Morgan Received on Mon Dec 16 2002 - 20:18:48 CST