Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Unanswered: DBMS_STATS and Statistics question

    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!

  2. #2
    Join Date
    Dec 2002
    Posts
    7
    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!

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    Stats

    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  5. #5
    Join Date
    Dec 2002
    Posts
    7

    Re: Stats

    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!

  6. #6
    Join Date
    Dec 2002
    Posts
    7
    nice site btw, congratulation on the OCP !!
    Last edited by energie; 12-19-02 at 13:12.

  7. #7
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    Sample Data

    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

  8. #8
    Join Date
    Dec 2002
    Posts
    7
    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!

  9. #9
    Join Date
    Dec 2002
    Location
    Netherlands
    Posts
    118

    Perf issue

    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
    Remi Visser

    Oracle Database Administrator
    (OCP certified 8i)

    Homepage: http://home.remidian.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •