The Easy Way and the Hard Way to Optimize an Oracle Database

It may seem that the companies entangled in the web of Oracle and its ecosystem have a limited set of options when it comes to performance tuning. It’s either – pay for extra functionalities and navigate Oracle’s complex licensing, risking non-compliance penalties. This often leads to a conservative approach to optimization, stifling innovation due to the fear of overstepping licensing terms.

But do we always have to go the hard way? 

There’s a much simpler route that doesn’t involve trembling with fear of clicking the wrong thing. That’s what we will be talking about today. 

The Hard Way: Navigating Oracle’s Paid Options

Opting for the hard way feels a bit like going for a law course. It’s rarely about the optimization itself and more often about going through the minefield of Oracle’s licensing terms for its options and versions. Just like on the minefield, the risk here is real – accidentally enabling a feature that’s off-limits according to your license can have significant financial repercussions. 

Checking the validity of the Oracle options you’re using is no straightforward task. With each Oracle version, the names of database types, their options, and even their usage rules can change. In some extreme cases, a certain SELECT statement with specific WHERE conditions might be allowed, while others on the same view are not. Unfortunately, there’s no easy roadmap for this process; each attempt is an adventure (not a good kind though). Even upon completing it, you’re left wondering if you might have overlooked something crucial.

Oracle’s official documentation

Some help can be found in Oracle’s official documentation on docs.oracle.com, specifically from the section “Checking for Feature, Option, and Management Pack Usage“. This procedure involves downloading and executing the options_packs_usage_statistics.sql script from https://support.oracle.com, as detailed in document ID 1317265.1. Running this script on your database will indicate options used, potentially flagging any unauthorized usage.

Community For the Rescue

Community-backed optimization

People such as Tim Hall are a true pillar of the database community, In his article, he offers a script for checking usage and direct links to documentation spanning Oracle versions 10gR1 to 19.

But is it truly that simple? Unfortunately, the answer is no. A third resource, also community-based, an article by Grégor Steulet, reveals the nuances that must be considered if the scripts indicate the usage of an option that we didn’t actually employ. As Steulet discusses, only Oracle’s License Management Services (LMS) department has the final say on a database’s compliance status. Yet, he points out that some usage information reported by these scripts can be misleading, resulting from application bugs. Thus, it’s still important to verify any dubious information against the Oracle support portal.


There exists an ‘easy way’ that frees companies from these constraints while still equipping them with the tools needed for effective performance tuning. This simpler, more straightforward approach is the DBPLUS PERFORMANCE MONITOR (PM).

DBPLUS PM stands out because it doesn’t rely on any of Oracle’s paid options, making it an ideal choice not just for Oracle databases but also for other platforms like MS SQL, PostgreSQL, and SAP HANA. It delivers critical insights necessary for tuning, independent of the specific Oracle version or the options purchased. 

Key functionalities of DBPLUS Performance Monitor that highlight its utility include:

  • SQL query history is stored without an expiration date – no vital data is lost over time.
  • The Anomaly Monitor feature automatically detects potentially harmful changes within the database. Then, it alerts administrators to issues that need immediate attention based on automatically created baselines.
  • A comprehensive dashboard acts as an entry and central point for monitoring the status of all tracked databases. This feature provides a bird’s eye view of the entire set of databases.
  • Cloud database support – DBPLUS PM is equally effective in managing databases housed on cloud platforms.

What’s more. Its interface remains consistent across different database types. The unified ‘tuning feeling’ eases the learning curve for database administrators as they work across various platforms. So an Oracle DBA can easily check PostgreSQL, MSSQL, or SAP HANA databases in order to see what is going on inside.

For those looking to discover more about DBPLUS Performance Monitor offers and how it can be utilized within their operations, more information and a demo are available online – direct link to demo