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:
As you can see all columns except "table_name" contain NULL values.
Now I tell Oracle to gather tablestats for the table, this can be done with DBMS_STATS but also manually with the ANALYZE TABLE clause:
2 DBMS_STATS.GATHER_TABLE_STATS( 'SYSTEM', 'DEMO');
Thanks a lot! You've been very helpful!. I've tried that and it works.
One thing though, do you know of Oracle stores a subset of its tables ANYWHERE? ie. for a specific table, sample maybe 5% of the rows and store those rows somewhere?
I'm asking because although the statistical information gathered by calling gather_table_stats are only stuff like Density, number of Nulls, etctec, but what I need is a subset of the data.
I'll give an example of what I need:
for this table called Patients, I'll have 1000000 records, I want to store for example the top 10 patients with the most entries. ie so let Bob be that patient who is responsible for 25000 of those records. and the 2nd highest might be Kelly who has 20000 records.
I could do this by going
select patient_name, count(*) group by count(*) or something to that effect, and pick out the top 10 entries.
BUT i am hoping that Oracle store this data (or a subset of sample representing the data) somewhere already so I don't have to do a query on 1000000 records.
I am trying to be clear but if I am not, please ask
Thanks for the complement on my site (I'm working very hard to get it operational by the end of this month ;-)
Anyway, I think I have to disappoint you, Oracle does not store a subset or anything like that of tabledata.
So if you have to run a query for the top 10 patients you'll have to run it time and again. Because the data in your table could be changed from the last time your ran it, therefore the query has to ben run again!
Maybe you could consider a to create a SNAPSHOT view that refreshes say every morning, you can then query this snapshot instead of the underlying table, but then you must accept that the result from your "top 10 query" is only as accurate as the time of your last snapshot.
how does the performance issue compare? ie taking a snapshot vs querying the whole db? doing this once in a while will not be a problem if the benefits are much greater to take snapshots vs querying the whole db.