Home » RDBMS Server » Performance Tuning » Interpreting Histograms (10g)
Interpreting Histograms [message #347760] Sat, 13 September 2008 14:14
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
Again I wasn't able to understand this on the docu. I tried this steps:



SQL>exec dbms_stats.gather_table_stats(user, 'EMP', method_opt => 'FOR COLUMNS SIZE 10 empno');

SQL> select endpoint_value ev, endpoint_number en, endpoint_actual_value eav
  2  from user_tab_histograms
  3  where table_name = 'EMP'
  4    and column_name = 'EMPNO'
  5  /

        EV         EN EAV
---------- ---------- --------------------
      7369          1
      7499          2
      7566          3
      7654          4
      7782          5
      7788          6
      7844          7
      7876          8
      7902          9
      7934         10

10 rows selected.




Based on the docs the endpoint_number is the bucket, and the endpoint_value is the "normalized" endpoint value. What does it mean by normalized? How does it differ in the endpoint_actual_value column? Here I assume the endpoint_value are the actual values in the empno column, is that right? and from bucket 2 to bucket 3, it means those empno between 7499 and 7566 was within that range. I did a select of empno

SQL> select empno
  2  from emp
  3  order by empno;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876

     EMPNO
----------
      7900
      7902
      7934

14 rows selected.


Does this mean 7521 is between bucket 2 and 3? Is that how I should interpret it? If so how would I know the exact/estimate number of *values* of empno between those two buckets? for me to know which value (i.e. which empno) appears in many records and which empno appears in one or two rows.

Also if I tried the ename:


SQL> exec dbms_stats.gather_table_stats(user, 'EMP', method_opt => 'FOR COLUMNS SIZE 10 ename');

PL/SQL procedure successfully completed.

SQL> l
  1  select endpoint_number en, endpoint_value ev
  2  from user_tab_histograms
  3  where table_name = 'EMP'
  4*  and column_name = 'ENAME'
SQL> /

        EN         EV
---------- ----------
         1 3.3888E+35
         2 3.3905E+35
         3 3.4943E+35
         4 3.6507E+35
         5 3.8584E+35
         6 3.9091E+35
         7 4.0129E+35
         8 4.3233E+35
         9 4.3788E+35
        10 4.5305E+35

10 rows selected.



why does the endpoint_value contains numbers and not the values of ename (which I think is because endpoint_value is a number column), but in this case what would my interpretation should be?

Hope I've explained it well. Thank you very much again!
Previous Topic: New predicate increasing response time from 30 sec to 6 min
Next Topic: NumNull Stats
Goto Forum:
  


Current Time: Fri Dec 09 11:57:01 CST 2016

Total time taken to generate the page: 0.11737 seconds