Easy or Difficult Way to Optimize Oracle Database?
11/4/2024
Tomasz Chwasewicz
It may seem that companies entangled in the network of the Oracle ecosystem have limited
a set of options when it comes to tuning performance. They can even pay for additional features and navigate complex Oracle licenses, risking penalties for non-compliance. This often leads to a conservative approach to optimization, suppressing innovation for fear of exceeding licensing conditions.
But do we always have to go the hard way?
There is a much simpler option that does not require trembling for fear of clicking on the wrong thing. This is what we will talk about today.
Choosing the hard way is a bit like taking a law course. It is rarely about optimization itself, and more often about going through the minefield of Oracle license terms for its options and versions. As in a minefield, the risk is real here - accidentally turning on a feature that is not available under the license can have serious financial repercussions.
Checking the validity of the Oracle options you are using is not a simple task. With each version of Oracle, the names of database types, their options, and even their usage policies may change. In some extreme cases, a certain SELECT statement with certain WHERE conditions may be allowed, while others in the same view are not. Unfortunately, there is no easy map for this process; every attempt is an adventure (and not in the good sense of the word). Even after completing it, you wonder if you missed something crucial.
Some help can be found in the official Oracle documentation at docs.oracle.com, in particular in the section “Checking the use of functions, options and management packages”. This procedure involves downloading and executing the options_packs_usage_statistics.sql script from the page https://support.oracle.com, according to document ID 1317265.1. Running this script in the database will indicate which options have been used, potentially flagging any unauthorized use.
People like Tim Hall are a real pillar of the database community. In his article offers a script for checking usage and direct links to documentation covering Oracle versions 10gR1 through 19.
But is it really that simple? Unfortunately, the answer is no. A third source, also community-based, article by Grégor Steulet, reveals the nuances that must be taken into account if the scripts indicate the use of an option that, in fact, has not been applied. As Steulet discusses, only Oracle License Management Services (LMS) has the final say on the compliance status of the database. However, Steulet points out that some of the usage information reported by these scripts can be misleading and result from application errors. Therefore, it is still important to verify any questionable information in the Oracle Support Portal.
There is a “simple way” that frees companies from these constraints while equipping them with the tools they need to fine-tune performance effectively. The simpler, more direct approach is DBPLUS PERFORMANCE MONITOR (PM).
DBPLUS PM stands out for not relying on any of Oracle's paid options, making it an ideal choice not only for Oracle databases, but also for other platforms such as MS SQL, PostgreSQL, and SAP HANA. It provides the critical information necessary for tuning, regardless of the specific Oracle version or purchased options.
Key features of DBPLUS Performance Monitor that highlight its usefulness include:
What's more. Its interface remains consistent for different types of databases. The unified “tuning feeling” makes learning easier for database administrators who work across platforms. So Oracle DBA can easily check PostgreSQL, MSSQL, or SAP HANA databases to see what's going on in them.
For those who want to learn more about the DBPLUS Performance Monitor offering and how it can be used in their operations, more information and demo are available online - Direct link to demo.
