Ignore stats while executing a query
June 15, 2020 - 1:28 pm UTC
Reviewer: Apr from USA
True - if there are no stats on the tables, dynamic sampling will kick in , by default and that is what is happening in my case and making the query run fast, even if I delete all the stats on the tables.
But it is surprising that the stats gathered on the fly by dynamic sampling is a lot better than the stats gathered by dbms_stats. I guess in 12c , dynamic sampling and stats gathered online while executing the query is far better than the previous versions
June 16, 2020 - 2:24 am UTC
But it is surprising that the stats gathered on the fly by dynamic sampling
This is not *usually* the case, but its also not entirely rare. The most common example is that stats are gathered (typically) over night, and then the table data changes to some degree before queries are run . Thus dynamic sampling may represent a more up-to-date statistical model of the data.
19c improves upon this with near real-time statistics facilities.
June 15, 2020 - 7:10 pm UTC
Reviewer: J. Laurindo Chiappa from Sao Paulo, SP, Brazil
Hi - as far as I know, the main difference between the statistics gathered on the fly by dynamic sampling versus the statistics gathered by DBMS_STATS is that dynamic sample collects just the basic informations, such as blocks, number of rows and such alike : in routine situations, the abscense of NDVs , Histograms and things like that make on-the-fly statistics a poor choice...
If this is not your case, imho you HAVE some very unusual the situation : maybe a number so big of distinct values, or a data distribution so weird that the default routines / algorithms in DBMS_STATS are insufficient - please take a 10053 trace from a "fast" and from a "slow" execution and contrast both . Further refs for 10053 trace file production and interpretation are : Support Note:338137.1 – CASE STUDY: Analyzing 10053 Trace Files, http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.pdf
, the series in http://oracledoug.com/serendipity/index.php?/archives/1701-10053-Trace-Files-Getting-Started.html
P.S. : please read about Histograms size/type/frequency in the articles residing in https://jonathanlewis.wordpress.com/category/oracle/statistics/histograms/
: I have a feeling that this could be very useful in your analysis....
June 16, 2020 - 6:26 am UTC
Reviewer: A reader
If you want that the table should have fresh stats gathered then you can LOCK the table stats.
Otherwise (as suggested above) revisit histograms etc. We had a similar problem but not without stats but had to gather stats differently with "FOR ALL COLUMNS SIZE 254" and it did wonders.
Sometimes the default gather stats will not just cut it. In that case identify the correct one and set for the table in question.
June 17, 2020 - 11:47 am UTC
Reviewer: David D. from Paris - France
Maybe an idea here : if you have an application or a PL/SQL script, it is possible to
- delete the stats of your table with DBMS_STATS.DELETE_TABLE_STATS
- lock the empty stat with DBMS_STATS.LOCK_TABLE_STATS
- run your query with the hint OPTIMIZER_DYNAMIC_SAMPLING with the value 0 or 1 to be sure that the CBO will no generate stats
- and after the result, to unlock the stat on your table with DBMS_STATS.UNLOCK_TABLE_STATS
- to generate fresh stats with DBMS_STATS.GATHER_TABLE_STATS for the others queries
I did not verify but maybe it is a soultion...