How to manually gather clean statistics using GATHER_TABLE_STATS procedure in oracle19c

284 Views Asked by At

I used below method to gather the statistics, but it seems that it is not generating the clean statistics. So, when I am gathering stats with below mentioned way, query execution is very quick for the first attempt. But in subsequent attempt query execution takes more than 5 minutes.  

BEGIN DBMS_STATS.GATHER_TABLE_STATS (
                                            OwnName           => 'USER2',
                                            TabName           => 'Name of table',
                                            Estimate_Percent  => 0,
                                            Method_Opt        => 'FOR ALL COLUMNS SIZE 1',
                                            Degree            => 4,
                                            Cascade           => TRUE,
                                            No_Invalidate     => FALSE); 
END;

I checked it from few sources and got know that Oracle might find mistakes in statistics on the first run and then in subsequent runs it tries to improve those statistics which results in slowing down the SQL statement execution in subsequent runs.

I tried various other ways of gathering clean statistics which are as follows:

1.

BEGIN DBMS_STATS.GATHER_TABLE_STATS (
                                            OwnName           => 'USER2',
                                            TabName           => 'Name of tables',
                                            No_Invalidate     => FALSE); 
END;

2.

BEGIN DBMS_STATS.GATHER_TABLE_STATS (
                                                    OwnName           => 'USER2',
                                                    TabName           => 'Name of table',
                                                    Estimate_Percent  => 0,
                                                    Degree            => 4,
                                                    Cascade           => TRUE,
                                                    No_Invalidate     => FALSE); 
END;

3.

BEGIN DBMS_STATS.GATHER_TABLE_STATS (
                                                    OwnName           => 'USER2',
                                                    TabName           => 'Name of table',
                                                    Estimate_Percent  => 0,
                                                    Degree            => 4);
END;

4.

BEGIN DBMS_STATS.GATHER_TABLE_STATS (
                                                    OwnName           => 'USER2',
                                                    TabName           => 'Name of table',
                                                    Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                                    Method_Opt        => 'FOR ALL COLUMNS SIZE AUTO',
                                                    Degree            => 4,
                                                    Cascade           => TRUE,
                                                    No_Invalidate     => FALSE); 
END;

But none of them actually seems to be working because these have even slowed down the query execution when ran for the first time.

Is there any way which would gather the clean statistics and also will not slow down the execution of query?

0

There are 0 best solutions below