My client has a large 5-node RAC with the counts & metrics shown below. It's stated purpose is a data warehouse, but in reality it is used for many various types of data & processes. It seems to perform reasonably well for now, but I have some concerns about object counts and its multi-faceted use. My gut-instinct would be to split it up into smaller instances that were more focused toward the specific uses. I know that the amount of data is reasonable, and I don't think it's approaching any hard-limits yet, but the dictionary does seem sluggish (example: querying the count from dba_extents below took 25 minutes).
Are my concerns valid?
Are there other problems we need to consider or is this a reasonable load for an Oracle DB?
What kind of 'object-counts' should make a DBA nervous?
/* = 2,138 */ select count(*) from dba_data_files ;
/* = 15,632,812 */ select count(*) from dba_extents ;
/* = 39,551 */ select count(*) from dba_indexes ;
/* = 63,675 */ select count(*) from dba_ind_columns ;
/* = 160,969 */ select count(*) from dba_ind_partitions ;
/* = 157,322 */ select count(*) from dba_ind_subpartitions ;
/* = 549,434 */ select count(*) from dba_objects ;
/* = 406,332 */ select count(*) from dba_segments ;
/* = 1,866,084 */ select count(*) from dba_source ;
/* = 32,474 */ select count(*) from dba_tables ;
/* = 538 */ select count(*) from dba_tablespaces ;
/* = 1,156,530 */ select count(*) from dba_tab_cols ;
/* = 47,959 */ select count(*) from dba_tab_partitions ;
/* = 14,800 */ select count(*) from dba_tab_subpartitions ;
/* = 1,264 */ select count(*) from dba_users ;
/* = 10,761 */ select count(*) from dba_views ;
select sum(bytes) from dba_data_files ; /* = 73 terabytes */
select sum(bytes) from dba_free_space ; /* = 9 terabytes */
select * from v$version ;
/* Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
*/
Thanks for any advice you can provide.
You're approaching this backwards. A single application could have hundreds or thousands of tables. So you could have a huge number of objects, but no (simple) way to split it across databases.
Equally I'd be concerned about a large application that has a tiny number of tables. Say less than 10. It's a sign that there's a reliance on "generic" data models.
So rather than looking at objects counts, ask whether the objects are logically related. If if you're able to support business needs.
For example:
- Are customers happy with the performance of their queries/applications?
- Are developers able to build & release changes in a reasonable timeframe?
- Can you backup and restore data in a way that meets your business' recovery time & point objects?
- ...
And ask if any of these could be improved by splitting up the database.
A key area where having a single "bucket" database holds you back is often patching & upgrades. For example, answering yes to any of the following may mean a small piece of functionality stops you upgrading the database:
- Does the DB host any third party applications which only support specific versions?
- Is testing everything in the database a significant blocker to patching & upgrades?
- Have you had to apply one-off patches or set underscore parameters to fix a small part of the app?
- ...
In which case it can make sense to split these into separate databases. If, when you upgrade to 12c+, you're lucky enough to get the Multitenant option using separate pluggable databases can help with this.
That said, there are some physical database limits. You can check what these are at:
https://docs.oracle.com/cd/E11882_01/server.112/e40402/limits002.htm#REFRN0042 Also, large "bucket" databases like this often contain many obsolete objects. For example, deprecated functionality, temporary tables created for backup or reporting purposes, etc. It may be worth looking to see if there's anything you can remove ;)
PS - In Oracle Database terms the database is the files. Instances are the processes running on the server & the memory areas. So if you have a 5-node RAC, you have one database, five instances. Not one instance!