Database Reference
In-Depth Information
(SELECT COUNT(*) FROM DLV_SUBSCRIPTION) AS DLV_SUBC,
(SELECT COUNT(*) FROM DOCUMENT_DLV_MSG_REF) AS DOC_DLV_MSG_REF,
(SELECT COUNT(*) FROM DOCUMENT_CI_REF) AS DOC_CI_REF,
(SELECT COUNT(*) FROM WORK_ITEM) AS WRK_ITEM
FROM DUAL;
These queries are not intended to replace the DBA's traditional monitoring tools,
but rather to provide guidance to potential areas of table growth.
Available maintenance strategies
To manage the database growth as a result of a high volume of instance pro-
cessing and transactions, there must be a strategy in place that can delete his-
torical data that are no longer required in order to reclaim the disk space back.
It is also impossible to have a generic strategy that can be applied to all types
of infrastructure as there is no one-size-fits-all solution to this, but this chapter
will provide an insight into all the available mechanisms, depending upon the
database profile, process requirements, and other factors. Once there is enough
understanding of these various factors, a combination of these strategies can
be applied to have the most relevant and optimal permutations in place. We re-
commend that regardless of which purging strategy is chosen, it needs to be
followed up with proper testing against a production-like dataset. It is also advis-
able to engage a skilled DBA to review the Dehydration Store data management
mechanism. In any case the purging strategy cannot be left as an afterthought
and needs to become a part of the performance exercise, a thorough testing is
recommended to complete this cycle. If an ineffective purging strategy is imple-
mented, the Oracle SOA Suite
11g
tables may grow to very large sizes, thereby
affecting the overall system performance and leading to an urgent need to re-
claim space. The larger the size of tables, the harder it becomes to delete rows
and reclaim space and hence it is important to schedule a maintenance strategy
to frequently purge data from them.
ating the database usage based on the number of instances and their message
sizes. The graph is indicative of a small process with a few dehydration points;
