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...1g.htm#1004271

http://www.csis.gvsu.edu/GeneralInfo...ts.htm#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!

anyone have ANY idea? I just need to figure out how to access the statistics gathered. do i have to create a cursor and output them row by row?

need help desperately

thanks!

Hi,

I just took a quick look in the docs and found the following procedures in DBMS_STATS....

CREATE_STAT_TABLE Procedure Creates a table with name stattab in ownname's schema which is capable of holding statistics.

EXPORT_COLUMN_STATS ProcedureRetrieves statistics for a particular column and stores them in the user stat table identified by stattab.

EXPORT_INDEX_STATS Procedure Retrieves statistics for a particular index and stores them in the user stat table identified by stattab.

EXPORT_TABLE_STATS Procedure

I couldn't post all of the relevant stuff here, but you can search the Oracle docs on oracle.com

The document I found this in is
Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)
Part Number A76936-01

It doesn't answer your question but hope it atleast points you in the right direction.

Regards
Bill

Hi

Here we go,

When you create a table you can see Oracle will NOT gather ANY stats unitl you explicitely tell Oracle to do:

SQL> CREATE TABLE DEMO(K NUMBER);

Table created.

SQL> SELECT
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE
7 FROM
8 USER_TABLES WHERE TABLE_NAME = 'DEMO'
9 /

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------- ---------- ---------- ------------ ----------
DEMO

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:

[SYSTEM@HA3D.WORLD:BENDEV06] BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS( 'SYSTEM', 'DEMO');
3 END;
4 /

PL/SQL procedure successfully completed.

I can also do this with the following clause:

SQL> ANALYZE TABLE SYSTEM.DEMO COMPUTE STATISTICS;

Table analyzed.

Now we can see that user_tables contains more values:

SQL> SELECT
2 TABLE_NAME,
3 NUM_ROWS,
4 BLOCKS,
5 EMPTY_BLOCKS,
6 AVG_SPACE
7 FROM
8 USER_TABLES WHERE TABLE_NAME = 'DEMO'
9 /

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------- ---------- ---------- ------------ ----------
DEMO 0 0 1 0

Note I only took a few columns of USER_TALBLES, USER_TABLE contains a lot more columns:

SQL> DESC USER_TABLES
Name
-----------------------------------------------
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT
NEXT_EXTENT
MIN_EXTENTS
MAX_EXTENTS
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING
BACKED_UP
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
AVG_SPACE
CHAIN_CNT
AVG_ROW_LEN
AVG_SPACE_FREELIST_BLOCKS
NUM_FREELIST_BLOCKS
DEGREE
INSTANCES
CACHE
TABLE_LOCK
SAMPLE_SIZE
LAST_ANALYZED
PARTITIONED
IOT_TYPE
TEMPORARY
SECONDARY
NESTED
BUFFER_POOL
ROW_MOVEMENT
GLOBAL_STATS
USER_STATS
DURATION
SKIP_CORRUPT
MONITORING
CLUSTER_OWNER



Good luck and pay me a visit http://ora-0000.com/

Remi Vissser

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 your help!

nice site btw, congratulation on the OCP !!

Hi Energy

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.

Hope this helps.

Remi

oh snapshots? ummm..

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.

is it hard to take snapshots?


thanks!

Sorry Snapshots are called MATERIALIZED VIEW nowadays:

-- From the Oracle Manual:
-- The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.


The Performance Issue benefit of using MATERIALIZED VIEW is that you can SCHEDULE the refresh of your materialized VIEW.
IE you can run it at night.

Here's a simple complete example of using a MATERIALIZED VIEW:

First take care you have been granted to following system privilege:

SQL> CREATE ANY MATERIALIZED VIEW to <YOUR_USERNAME>;

Grant succeeded.

Now I create a demo table and I insert some test data:
(Make sure you have a primary key for the ORA-12014: table '' does not contain a primary key constraint)

[SYSTEM@DB1.BEN.NL:PCHQ-1093] CREATE TABLE DEMO(K NUMBER PRIMARY KEY);

Table created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(1);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(2);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(3);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(4);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(5);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] COMMIT;

Commit complete.


Next I create the materialized view (my stored query), in my case it's a silly query but you can use your heavy query instead:

You need a materialized view log that Oracle uses to sync (refer the Oracle's Docs)

[SYSTEM@DB1.BEN.NL:PCHQ-1093] CREATE MATERIALIZED VIEW LOG ON DEMO WITH PRIMARY KEY;

Materialized view log created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] CREATE MATERIALIZED VIEW MV_DEMO
2 REFRESH FAST NEXT SYSDATE + 1
3 AS
4 SELECT
5 *
6 FROM
7 DEMO
8 WHERE
9 K > 4
10 ;

Materialized view created.

No I select from the MATERIALIZED VIEW :

[SYSTEM@DB1.BEN.NL:PCHQ-1093] SELECT * FROM MV_DEMO ;

K
----------
5

Below you can see the disadvatage, I insert into the master table demo and the materialized view doesn't display these new results until SYSDATE +1:

[SYSTEM@DB1.BEN.NL:PCHQ-1093] INSERT INTO DEMO VALUES(6);

1 row created.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] COMMIT;

Commit complete.

[SYSTEM@DB1.BEN.NL:PCHQ-1093] SELECT * FROM MV_DEMO ;

K
----------
5

Hope this helps

Remi