1

As I understand, Data Dictionary

  • Is a collection of tables, part of SYS schema (if not the whole SYS schema)
  • It is stored as 'regular' tables, so whatever is applied to database tables, also applies to data dictionary (e.g. indexes, statistics)
  • Its statistics should be updated
  • It is always cached

Can the the size and the complexity of oracle data dictionary affect the database performance?

When I add a new object on my database a new (or multiple) entries is/are added on data dictionary. A scenario I can think of has to do with partitioning. So if I have a table which have a partition and subpartitions, I will have an entry for the table, an entry for each partition and an entry of each partition subpartitions in data dictionary. Those entries are spawned among different 'tables'. Imagine know having tables with thousands of partitions and hundreds of subpartition. Again this number of records may not be something that a dbms can handle, but how it will affect its performance as it grows?

Note: I have read a lot of discussion on if and how partitioning affects performance in general. This is out of scope for this question. Partitioning is just an example I gave, to show how data dictionary volume can grow.

Improve this question
4
  • 1
    Have a look at SYSTEM tablespace. Typically this is less than 1 GB. With nowadays RAM it should be no problem to put them all into memory.
    – Wernfried Domscheit
    Commented Dec 27, 2017 at 15:54
  • @WernfriedDomscheit I run the following query: select tablespace_name, sum(bytes)/1024/1024 as size_in_mb from dba_data_files where tablespace_name = 'SYSTEM' group by tablespace_name. If this is correct my SYS schema is more than 3GB in my testing environment.
    – Athafoud
    Commented Dec 27, 2017 at 16:21
  • 1
    @Athafoud: your query returns total size. Try checking used (select round(sum(bytes)/1024/1024) usedspace from dba_segments where tablespace_name='SYSTEM' ) or free (select round(sum(bytes)/1024/1024 ) as free_space from dba_free_space where tablespace_name='SYSTEM')
    – a1ex07
    Commented Dec 27, 2017 at 18:47
  • @a1ex07 Thanks for the updated query. The results are almost the same, something near 3GB.
    – Athafoud
    Commented Dec 28, 2017 at 7:39

3 Answers 3

Reset to default
1

So a good starting point for this is Martin Widlake's post on the size of the 'data dictionary'. The term can be a bit woolly as there's a bunch of stuff that Oracle stores (such as executable code, job history etc) which may or may not be relevant.

https://mwidlake.wordpress.com/2009/08/03/why-is-my-system-tablespace-so-big/

He points out an example where he has 13 Gb in a data dictionary segment "C_OBJ#_INTCOL#". That is technically a cluster rather than a regular table, but it is really just a special table where the data is arranged so that related items tend to be stored in the same block on disk. That object is storing histogram information for table columns which tells you that, for a specific table/column, you might have 60% of values as US, 25% as Canada and 15% Mexico, or that you have twice as much data for October 2017 as you did for October 2016.

Whether that's a good use of space will depend on your situation. Histograms are basically used to determine the best access path. For example, given a query that has both a date and zip code paths available, which is going to involve less work.

Ultimately, it isn't about the size of a data dictionary, but a more precise question of whether storing additional information in there can help avoid mistakes. Even then, the downside is often the impact of gathering the additional information and keeping it up-to-date rather than the space required to store it.

Additional : A cluster tends to take up a bit more space than a regular table as trying to keep related data together needs to allow more space for the data to grow rather than moving it around when it doesn't fit.

Improve this answer
0

What kind of performance problems are we talking about here?

If it is a query performance for a application it has nothing to do with data dictionary size but with a way query is written, does it use indexes or not, do indexes exist or not on important columns, are statistics up to date etc. Or performance problems in adding new partitions, tables and columns? This is simply adding or updating a few rows and it shouldn't be a problem for a oracle rdbms.

I have seen large sysaux tablespaces on various versions(xe, se, ee), oem repository gets too big, or addm not purged on time or some other oracle bug. System tablespace can grow if auditing is enabled on default location (AUD or FGA_LOG tables). You could query v$sysaux_occupants(for sysaux), or dba_segments to see what size objects are, or to track how they change in time.

Improve this answer
1
  • I am not talking about any specific problem, I haven't run any tests. I am still learning oracle and this was a question that popped up in my mind. As I understand oracle uses the Data Dictionary, to help executing the query, e.g. find in which partition the data resides. However I haven't found any resources to support this or prove me wrong.
    – Athafoud
    Commented Dec 28, 2017 at 7:45
0

First of all I do not have any experience with the Oracle Data Dictionary, but I am on a project where we are coming up with our own customised data dictionary to enhance the development of huge DBs which would not perform as well without the capability that the data dictionary provides. Yes, this does take up space but in my experience data dictionaries only serve to enhance performance by limiting search results.

Now I note you mention the size and complexity of the data dictionary. Space should always be a consideration. I am not sure how big your database will grow, but in such considerations in my organisation, we have always introduced some middleware for the data dictionary. This operates independently of the RDBMS. Mulesoft is the one that we use and I have checked - this is compatible with Oracle.

Improve this answer
3
  • While this is of potential interest, it does nothing to answer whether increases in the size of the built-in "data dictionary" tables found in Oracle (among other DBMSes) have a noticeable impact on performance. The "data dictionary" tables in the question simply define the structure of the database (table names, column names and types, triggers, indexes, possibly stored procedures and functions. etc.); they don't include any info on the intended contents of the columns (which, at a glance, seems like what the MuleSoft offering is about). These are two fundamentally different things.
    – RDFozz
    Commented Dec 27, 2017 at 22:46
  • 2
    According to dba-oracle.com/concepts/data_dictionary.htm The data dictionary views can be used to manage your database and good DBA's will put scripts together that run on a regular schedule....to monitor the database looking for problems, like running out of disk space. Data dictionaries are a necessary and crucial component to the performance of the db - therefore I cannot see how they could negatively impact performance. In my experience performance always comes down to the design of the db.
    – Treasa Ní Mhaoltuile
    Commented Dec 27, 2017 at 23:41
  • I agree with you there. Personally, I would be quite surprised if the built-in data structure table or views could be so filled with information as to cause a performance problem. However, as I have no definitive proof one way or the other, I'm not (actively) chiming in. Oh, and welcome to DBA.SE! Just to clarify, your post was quite nice - I just felt I had to note that it didn't seem to answer the OP's question, when it came up in the review queue. I look forward to more from you!
    – RDFozz
    Commented Dec 28, 2017 at 0:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.