DBMS_STATS – What is going on? – Learning is not a spectator sport

Big thanks to Nigel Bayliss for his input on the post

I was helping a customer recently with some of their queries against the data dictionary running more slowly than they expected. When we investigated a little further we discovered that this was no “typical” database. Everyone in their organisation got to have a schema which they could use however they liked (the aim here to reduce the amount of uncontrolled Excel spreadsheets floating around the place). As a result, their data dictionary had 10s of thousands of schemas, many of which had 1000s of database objects within it.

The first question I asked of the customer was: “Have you gathered statistics on the data dictionary to let the optimizer know what is inside those dictionary tables?”

Blank stares all around 😁

So we kicked off a DBMS_STATS.GATHER_DICTIONARY_STATS routine and as you can imagine, given the size of their dictionary, it took a while to run which is what motivates this blog post. Once the routine was running, the customer asked the usual questions: “How much longer will it take? How much has it done? What is it currently doing? Is it hung?” and so forth.

If you look at V$SESSION when a DBMS_STATS operation is underway, the usual instrumentation suspects (MODULE, ACTION and CLIENT_INFO) are typically null, so you might be thinking that the folks in the optimizer team were not following standard best practice for instrumenting long running code. But you’d be mistaken 😀

As DBMS_STATS performs operations, it logs information to two DBA views that given excellent information to allow you to track progress.

DBA_OPTSTAT_OPERATIONS
DBA_OPTSTAT_OPERATION_TASKS

For the gathering of dictionary stats at the customer site, we got the following information:


SQL> select * from DBA_OPTSTAT_OPERATIONS
  2  @pr
==============================
ID                            : 1792
OPERATION                     : gather_dictionary_stats
TARGET                        :
START_TIME                    : 10-AUG-23 02.50.51.833564 PM +08:00
END_TIME                      : 10-AUG-23 03.32.53.463748 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
SESSION_ID                    : 146

PL/SQL procedure successfully completed.

SQL> select * from DBA_OPTSTAT_OPERATION_TASKS
  2  where opid = 1792
  3  @pr
==============================
OPID                          : 1792
TARGET                        : "AUDSYS"."AUD$UNIFIED"."SYS_P1408"
TARGET_OBJN                   : 79114
TARGET_TYPE                   : TABLE PARTITION
TARGET_SIZE                   : 179564
START_TIME                    : 10-AUG-23 02.52.47.916578 PM +08:00
END_TIME                      : 10-AUG-23 02.53.00.207007 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
ESTIMATED_COST                : 0
BATCHING_COEFF                : 0
ACTIONS                       : 0
PRIORITY                      : 1
FLAGS                         : 0

==============================
OPID                          : 1792
TARGET                        : "AUDSYS"."AUD$UNIFIED"."SYS_P1388"
TARGET_OBJN                   : 78980
TARGET_TYPE                   : TABLE PARTITION
TARGET_SIZE                   : 35
START_TIME                    : 10-AUG-23 02.53.00.207784 PM +08:00
END_TIME                      : 10-AUG-23 02.53.00.320036 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
ESTIMATED_COST                : 0
BATCHING_COEFF                : 0
ACTIONS                       : 0
PRIORITY                      : 2
FLAGS                         : 0

==============================
OPID                          : 1792
TARGET                        : "AUDSYS"."AUD$UNIFIED"."SYS_P1388"
TARGET_OBJN                   : 78980
TARGET_TYPE                   : TABLE PARTITION
TARGET_SIZE                   : 35
START_TIME                    : 10-AUG-23 02.53.00.242873 PM +08:00
END_TIME                      : 10-AUG-23 02.53.00.320036 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
ESTIMATED_COST                : 0
BATCHING_COEFF                : 0
ACTIONS                       : 0
PRIORITY                      : 2
FLAGS                         : 0

==============================
OPID                          : 1792
TARGET                        : "AUDSYS"."AUD$UNIFIED"."SYS_P1348"
TARGET_OBJN                   : 78252
TARGET_TYPE                   : TABLE PARTITION
TARGET_SIZE                   : 496
START_TIME                    : 10-AUG-23 02.53.00.320643 PM +08:00
END_TIME                      : 10-AUG-23 02.53.00.463198 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
ESTIMATED_COST                : 0
BATCHING_COEFF                : 0
ACTIONS                       : 0
PRIORITY                      : 3
FLAGS                         : 0

==============================
OPID                          : 1792
TARGET                        : "AUDSYS"."AUD$UNIFIED"
TARGET_OBJN                   : 18565
TARGET_TYPE                   : TABLE
TARGET_SIZE                   : 180404
START_TIME                    : 10-AUG-23 02.53.00.463826 PM +08:00
END_TIME                      : 10-AUG-23 02.53.11.942309 PM +08:00
STATUS                        : COMPLETED
JOB_NAME                      :
ESTIMATED_COST                : 0
BATCHING_COEFF                : 0
ACTIONS                       : 0
PRIORITY                      : 4
FLAGS                         : 0
...
...
...

Obviously this is not only related to gathering of dictionary statistics. Wherever you have long running DBMS_STATS calls, these views can be used to give an insight into where the time is being spent. This also includes the nightly optimizer stats gathering tasks that get automatic scheduled by the database task management facility.

Enjoy!

3 responses to “DBMS_STATS – What is going on?”

Michael

August 15, 2023 at 1:02 pm

Hey Connor,

Thanks for this info! I used to query V$SESSION_LONGOPS, but use of these views seems more clean.

Kind regards,
Michael Verheij

Ivan Saez Saez Scheihing

September 1, 2023 at 9:54 pm

Connor,

Great article.
I thought dictionary stats are also gather by the default stats jobs (every night). Why running a manual dictionary stats? And if a dictionary stats is sometimes neede should you also run a gather schema of SYS? I know many (all??) of the SYS objects are part of the dictionary). And what about the fixed objects?

regards,

Ivan

1. Connor McDonald
  
  October 4, 2023 at 2:08 pm
  
  To my knowledge, dictionary stats are not part of the standard nightly run. (I’ll happily stand corrected if they are).
  
  You should not need to ever do a gather on the SYS schema, and for fixed objects – only if you determined you had some performance issues querying them.
  
  Just my $0.02 worth.