Solved

Oracle 9i Histogram Help

Posted on 2004-09-10
7
1,108 Views
Last Modified: 2008-01-09
I am trying to implement histograms and have been following Burleson's method of implementing.  I'm on Oracle 9i Rel 2 Ver 9.2.0.5 on Windows 2000 Advanced Server. I have run :

begin
   dbms_stats.gather_schema_stats(
      ownname          => 'SNAP',
      estimate_percent => dbms_stats.auto_sample_size,
      method_opt       => 'for all columns size skewonly',
      degree           => 4
   );
end;
/

And am trying to interpret what I am seeing in DBA_histograms.  I am the first one to run histograms on this db and there are only about 300 tables in this schema with about 15 columns or so per table.  I am ending up with a lot of rows in this table.  I wasn't expecting that.  I am expecting one row per histogram and one histogram per column.   Is that right?  Can histograms be deleted?  Using delete schema stats didn't do it....


0
Comment
Question by:DonFreeman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 1

Author Comment

by:DonFreeman
ID: 12027359
Here is what I got:

TEST1-DFREEMAN> select count(*) from dba_histograms;

                                          COUNT(*)
--------------------------------------------------
                                             76318

1 row selected.

TEST1-DFREEMAN> select count(*) from all_tab_columns where owner ='SNAP';

                                          COUNT(*)
--------------------------------------------------
                                              5345
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12027535
0
 
LVL 1

Author Comment

by:DonFreeman
ID: 12027576
I can't get this link to work.  I have been on AskTom quite a bit this morning researching this issue but I can't find a description of how the dba_histogram table works.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:mottor
ID: 12028508
Oracle docs:
 Choosing the Number of Buckets for a Histogram
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87503/stats.htm#27027
0
 
LVL 1

Author Comment

by:DonFreeman
ID: 12044048
I'm struggling through this.  I'm trying to figure out exactly how to clear that histogram information out of dba_histograms.   I only have this database part-time and would like to return it to pre-histogram state.  I did it running skewonly method.  I tried to remove it using dbms_stats.delete_schema_stats.   The links aren't working for me guys.  I have links to 500 docs.  What I need is an unambiguous explanation of 'middle-level' of how this works.  

Also, I tried to turn on schema monitoring using:  NEDSSART-DFREEMAN> BEGIN
  2   DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING (
  3     ownname=>'SNAP',
  4     monitoring=>TRUE);
  5  END;
  6  /
 DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING (
            *
ERROR at line 2:
ORA-06550: line 2, column 13:
PLS-00302: component 'ALTER_SCHEMA_TABLE_MONITORING' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
0
 
LVL 4

Accepted Solution

by:
mottor earned 125 total points
ID: 12045199
use DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING
not DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING
0
 
LVL 4

Expert Comment

by:mottor
ID: 12045847
Now,
There is another link:
http://asktom.oracle.com/pls/ask/f?p=4950:8:6965706299122648996::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3126073805757,
It contains article (search fo 'Article'), which is unambiguous explanation of 'middle-level' .
And be sure you read the whole.

Regards.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question