What is Stats
A reader, May 21, 2017 - 7:05 am UTC
you said
"The CBO uses stats about your tables (number of rows, distinct values, nulls, etc.)"
you mean Table Statisticks?if,Can we access them(Table Statistics) by using any queries.
May 25, 2017 - 3:18 pm UTC
This information is spread over a number of views, including:
*_tables
*_tab_statistics
*_tab_col_statistics
*_tab_histograms
Additional Statistics information
James Mulder, May 25, 2017 - 11:04 pm UTC
Chris:
Great information.
There's also Partition and Subpartition Statistics:
*_tab_partitions
*_part_col_statistics
*_tab_subpartitions
*_subpart_col_statistics
Also, the Density and Histogram Columns are critical to understanding and troubleshooting statistics.
I wrote a Stats Exploration query to help diagnose at-a-glance statistic issues. It's not perfect but it has helped me in a number of situations over the years. It includes the calculation for DENSITY for no histograms and for FREQUENCY histograms after much research via Tom Kyte, Jonathan Lewis and Wolfgang Breitling. If it is of interest to you feel free to reach out to me. I believe it would be a nice to share with the Oracle community.
James
PS - I have been working with Oracle Statistics since 2006.
May 26, 2017 - 1:09 am UTC
We'd certainly like to see it. Perhaps you could blog about it and put a link in here as a review.
A reader, February 06, 2019 - 1:25 am UTC
We got weird issue and not understanding how to solve it.
We developed a package and tested it in Dev and Test instances.It worked fine but when we migrated this code to prod we got performance issue it taking lot of time to give output.So we again moved it to test there it worked fine.It got issue with only production, what might be the issue, any suggestions on it?
February 06, 2019 - 1:53 am UTC
Production is different to Dev/Test.
For example:
select * from t
where 't' has 10 rows in Dev, and 10million rows in Prod will obviously run differently in each environment.
You need to explore what the differences are between the environments:
- data sizes
- data distribution
- statistics