Skip to Main Content
  • Questions
  • Ignore statistics while executing a SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Baby.

Asked: June 14, 2020 - 9:55 am UTC

Answered by: Connor McDonald - Last updated: June 16, 2020 - 2:24 am UTC

Category: SQL - Version: 12c

Viewed 1000+ times

You Asked

Hi,

Is there a way I can disable statistics while running a SQL.

Unfortunately our SQL runs v fast when all the statistics have been deleted from the tables involved.
But we need the statistics to be gathered for other queries to work efficiently.

Question : Is there a way I can disable/ignore the statistics on the objects(even if they are present) while running a particular SQL/query ? Do we have any hint like
ignore_stats for this ?

thanks
Apraim

and we said...

No, but most likely what is happening is that dynamic sampling is kicking in. For example.

SQL> create table t as select * from dba_objects;

Table created.

SQL>
SQL> exec dbms_stats.delete_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> select max(object_id)
  2  from t
  3  where owner = 'SYS';

MAX(OBJECT_ID)
--------------
         91248

SQL>
SQL> select * from dbms_xplan.display_cursor();

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  b6dm0gh1kx8t2, child number 0
-------------------------------------
select max(object_id) from t where owner = 'SYS'

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   442 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    79 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 54095 |  4173K|   442   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)



So perhaps look at adding a "dynamic_sampling(n)" hint to your query, with increasing levels of "n" for your problematic SQL

and you rated our response

  (4 ratings)

Reviews

Ignore stats while executing a query

June 15, 2020 - 1:28 pm UTC

Reviewer: Apr from USA

Thank you.
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
Connor McDonald

Followup  

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.

To Apr

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 , https://tonguc.wordpress.com/2007/01/20/optimizer-debug-trace-event-10053-trace-file/ , the series in http://oracledoug.com/serendipity/index.php?/archives/1701-10053-Trace-Files-Getting-Started.html and https://blog.toadworld.com/sql-tuning-a-close-look-at-the-10053-cbo-trace ....

Regards,

Chiappa

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....

Table stats

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.

Cheers


No stast

June 17, 2020 - 11:47 am UTC

Reviewer: David D. from Paris - France

Hello,

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...

Good luck

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.