Tag Archives: solutions

DBA_FEATURE_USAGE_STATISTICS and SE2


This blog post is inspired on work I have been doing on Standard Edition databases and the returning confusion about what is and what is not part of Standard Edition.

DBA_FEATURE_USAGE_STATISTICS is a tool in determining license usage for the Oracle database. It is good to understand the implications of each entry, know what is happening in your database and thus be able to have a substantial conversation about the usage of your license, being SE, SEO, SE2 or EE!

This list is the full list of DBA_FEATURE_USAGE_STATISTICS and I have found no source where there is a mapping of these features to database editions. As it is a lot of tedious work I call upon the community to help complete the list and make it as accurate as can be. So, if you have news, improvements, other bits of information, please send it to me and I will make sure it gets added!

WARNING: Still… with all the work that goes into these answers, it is not the law, it is a very serious interpretation of facts which will pay a part in helping you make the right decision when it comes to database licensing.

Feature Standard Edition
Active Data Guard – Real-Time Query on Physical Standby NO !
ADDM NO !
Advanced Replication NO !
Application Express YES
ASO native encryption and checksumming NO – EE option !
Audit Options NO !
Automatic Maintenance – Optimizer Statistics Gathering YES
Automatic Maintenance – Space Advisor YES
Automatic Maintenance – SQL Tuning Advisor NO !
Automatic Memory Tuning
Automatic Segment Space Management (system) YES
Automatic Segment Space Management (user)
Automatic SGA Tuning YES
Automatic SQL Execution Memory YES
Automatic SQL Tuning Advisor NO !
Automatic Storage Management
Automatic Undo Management
Automatic Workload Repository
AWR Baseline NO !
AWR Baseline Template NO !
AWR Report NO !
Backup BASIC Compression
Backup BZIP2 Compression
Backup Encryption
Backup HIGH Compression
Backup LOW Compression
Backup MEDIUM Compression
Backup Rollforward
Backup ZLIB Compression
Baseline Adaptive Thresholds
Baseline Static Computations
Bigfile Tablespace
Block Media Recovery NO !
Change Data Capture NO !
Change-Aware Incremental Backup
Character Semantics
Character Set
Client Identifier
Clusterwide Global Transactions
Compression Advisor
Crossedition Triggers
CSSCAN
Data Guard NO !
Data Mining NO – EE option !
Data Recovery Advisor
Database Migration Assistant for Unicode
Database Replay: Workload Capture NO ! 1
Database Replay: Workload Replay NO ! 1
DBMS_STATS Incremental Maintenance
Deferred Open Read Only
Deferred Segment Creation NO !
Direct NFS
Dynamic SGA
Editioning Views
Editions
EM Database Control
EM Grid Control
EM Performance Page
Encrypted Tablespaces
Exadata
Extensibility
File Mapping
Flashback Data Archive NO ! 2
Flashback Database NO !
GoldenGate NO – EE option ! 3
HeapCompression
Hybrid Columnar Compression NO !
Instance Caging NO !
Internode Parallel Execution
Job Scheduler
Label Security NO – EE option !
LOB
Locally Managed Tablespaces (system) YES
Locally Managed Tablespaces (user)
Locator YES
Logfile Multiplexing
Long-term Archival Backup
Materialized Views (User) NO !
Messaging Gateway NO !
MTTR Advisor
Multi Section Backup
Multiple Block Sizes
Object
OLAP – Analytic Workspaces NO – EE option !
OLAP – Cubes NO – EE option !
Oracle Database Vault NO – EE option !
Oracle Java Virtual Machine (system) YES
Oracle Java Virtual Machine (user)
Oracle Managed Files
Oracle Multimedia
Oracle Multimedia DICOM
Oracle Secure Backup
Oracle Text
Oracle Utility Datapump (Export)
Oracle Utility Datapump (Import)
Oracle Utility External Table
Oracle Utility Metadata API
Oracle Utility SQL Loader (Direct Path Load)
Parallel SQL DDL Execution NO !
Parallel SQL DML Execution NO !
Parallel SQL Query Execution NO !
Partitioning (system) YES
Partitioning (user) NO – EE option !
PL/SQL Native Compilation
Quality of Service Management NO !
Read Only Tablespace
Real Application Clusters (RAC) YES 4
Real-Time SQL Monitoring
Recovery Area
Recovery Manager (RMAN) YES
Resource Manager NO !
Restore Point
Result Cache NO !
RMAN – Disk Backup
RMAN – Tape Backup
Rules Manager
SecureFile Compression (system) YES
SecureFile Compression (user)
SecureFile Deduplication (system) YES
SecureFile Deduplication (user)
SecureFile Encryption (system) YES
SecureFile Encryption (user)
SecureFiles (system) YES
SecureFiles (user)
Segment Advisor (user)
Segment Shrink
Semantics/RDF NO !
Server Flash Cache
Server Parameter File
Services
Shared Server
Spatial NO – EE option !
SQL Access Advisor
SQL Monitoring and Tuning pages NO – EE option !
SQL Performance Analyzer NO !
SQL Plan Management NO !
SQL Profile
SQL Repair Advisor
SQL Tuning Advisor
SQL Tuning Set (system) YES
SQL Tuning Set (user)
SQL Workload Manager
Streams (system) YES 5
Streams (user)
Transparent Data Encryption
Transparent Gateway YES – option
Transportable Tablespace NO ! 6
Tune MView
Undo Advisor
Very Large Memory
Virtual Private Database (VPD) NO !  7
Workspace Manager
  1. Unless used for upgrade to Enterprise Edition.
  2. Unless used without history table optimization.
  3. Goldengate can also be used with Standard Edition, it is a separate product.
  4. RAC on Enterprise Edition is an option.
  5. No capture from redo.
  6. Import transportable tablespaces in all editions.
  7. Policies on XDB$ACL$xd_sp in sys.v_$vpd_policy are internal ( “out of the box”) policies that are used by XDB to control the access to certain internal tables. All the logic is implemented in the xdb.DBMS_XDBZ package and there is no way one can control / influence the way this is working.


sqldev_startup1

Updating SQL Developer to use newer Java version

I was being teased by SQL Developer.

Everytime I started it came nagging about that it was being forced to live in an old Java version called jdk1.7.0_45 and that is was not feeling happy about it.
So, I should remedy this, I thought to myself.

First visit was, inspired by some search-work on the WWW, a file called product.conf. Which offered two possiblities:

java

SetJavaHome to some logical location
or
SetJavaHome to nothing, and then SQL Developer would kindly ask me to point it to somwhere to live.

Well… no. My SQL Developer refused it all and just started with this jdk 1.7.

Same hack done in another file on another location, a file called sqldeveloper.conf.
Same result.

Freshly downloaded SQL Developer, put in place… No help!

Erm…

Rename
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 jdk1.7.0_45.jdk
in /Library/Java/JavaVirtualMachines
to
drwxr-xr-x  3 root  wheel  102 Jan  6  2014 xxx-jdk1.7.0_45.jdk

Nope! Still the same nagging…

What now?

In the end, I wound up with one of Jeff Smits’s helpers.
This guy aksed me to “start SQL Developer from the commandline”. Right, but how?

So I finally found:
/Applications/SQLDeveloper.app/Contents/MacOS/sqldeveloper.sh

And that did start SQL Developer from the command-line…

But… wait… an .sh-file!! Interesting!!

And, behold… in this .sh-file lies the answer:sqldev_startup1

So the file reads:
export JAVA_HOME=`/usr/libexec/java_home -v 1.7`
Which I hacked to:
export JAVA_HOME=`/usr/libexec/java_home -v 1.8`

And, presto, error-message gone and SQL Developer now happily lives in Java 8.

Hope this helps somebody out!!

Can you boost your Oracle database performance on HP-UX for free?

Database performance, as is true with all performance related matters, has to do with resources.

This story specifically focuses on a real life experience with Oracle database performance on HP Unix running on Intel integrity CPU’s like these:

CPUinfo

The issue with installation is the hyper-threading aka. the use of the logical processors.
When the server is booted and is running, you can do basic performance review with a default tool like top.

lcpu_attr=0

In this exact case the server is running fine and there is no need to investigate further. But, in cases where there were performance issues, it would be a good idea to be aware of the numbering of the CPU’s in this overview (0, 2). This numbering suggests there would also be a ‘1’ and, where there would be a ‘1’ there would probably also be a ‘2’…

Yes there is and it is called ‘lcpu_attr’. A HP Unix kernel parameter which is, to my taste, a bit odd, not well known or well documented…

lcpu_attr (Tunable Kernel Parameter)

When turned on, lcpu_attr activates the logical CPU’s immediately. When you run top again, this is what it’ll look like (immediately)

lcpu_attr=1

Okay! Great… but… there are some catches.
This parameter lcpu_attr is a dynamically tunable kernel parameter but… it’ll crash your databases. So you will need a minimum of planned downtime for this action.

Also, you can set hyper-threading on in the EFI boot-loader.
But then you should be aware of this!

In the end, in this real-life story, we helped the situation advance by just doing:

1. stopping Oracle database(s)
2. kctune lcpu_attr=1
3. starting Oracle databas(s)

All in all, it could be not difficult to boost your Oracle database performance on HP-UX for free!

Thanks to my good friend Gerard van der Kooij for finding the final link!

Cloud Database Offers On-premise Advantages

These are times when there are technologies abundantly available to help you make the very best of the data you gather from your business processes.

Increasing numbers of businesses choose the option to host their production database environment in one of the many cloud forms that are available these days. This example of a smart alternative discusses an additional service you could implement or request when you are dealing with cloud based databases.

In many organizations there is a BI-team responsible for the development of company specific KPIs or compose competitively strategic information based on the information that is gathered during day-to-day business. There often are key management positions that have a need for ad hoc queries to live data. In recent years the grave importance of this intelligence has been recognized as being of the greatest importance for decision support, and giving your organization the biggest competitive advantage possible.

Developing or even running these activities on live data gives the sharpest edge. Doing this on a production environment, nevertheless, is out of the question. Uninterrupted availability and maximum responsiveness for regular activities of these databases are unquestionably important. How can you combine these factors with the proposition of running your database in the cloud while staying smart?

The smart alternatives of Dbvisit enable you to do just this! By leveraging Dbvisit Replicate in a hosted environment you can create one or many local copies of live production data with specific local database settings to do precisely what you need, be it running or developing heavy BI queries or having departmental management looking at or analyzing data as it is recorded. Having (a subset of) the live data uni-directionally delivered from the cloud to your local (desktop) database creates a safe environment to analyze and enable knowledge workers to do the their job without any holds barred!

Retail Innovation with Dbvisit Replicate

In these current times it’s a “dog-eat-dog” world like it has never been. We are fighting over every bit of margin and trying to create value without increasing cost. The following example from a retail background shows an innovative way you could accomplish this, leveraging the #1 database at the lowest thinkable investment combined with the smart alternative from the makers of Dbvisit Software.

In this example we are following a supermarket in their quest to “do business a little different” and they are thinking of combining ‘shopping audience attitude’ in an interactive way with a time specific advertisement technique.
The idea is to look at what people are checking out at the cash register and combining this, in real-time, with both amounts of items in stock and possible specific business rules applicable to any discounts to be given.

By gathering information at the counters, information about what kind of groceries people are actually buying at that specific moment, you get an insight in the natural fluctuation of buyers behavior during the day. With this information you can do stuff, like figure out how much of what articles you need in stock or direct resupplies in the store during the day, getting the maximum revenue out of the employees responsible for making sure everything is plentyfull ready for the taking.
But why not take this one step further they thought. If we can combine this cash-register information with some kind of continuously changing system of discounting, we can create an element of interactivity. By looking at what articles are sold, combining this with remaining stock and using the fact if there already is some regular discount on specific articles, you can make a system where, for instance every 15 minutes, there is another specific item on ‘super special sale’. Delivering this “buy now” message to the actual customer can be done in several ways, either by loading this information in self-checkout bar-code scanners or for instance by label printers offering the specific discount label to be scanned at the checkout counter. After a ‘super special sale’ moment elapses, everything changes and a new article is the hot deal of the moment.

Where in a normal setting the POS entries are fed into the regular business database to be processed in a batch-like fashions you would have no chance of getting this information recycled. This backbone infrastructure cannot be used for the very data intensive activities we would need for our initiative to take shape. Having delays here would inevitably mean delays and errors at the checkout counters with queues and unsatisfied customers as the least of your concerns.

Regular data replication solutions would render any business case useless before somebody even had the heart to dream up such an idea. Today, by leveraging the Oracle database Standard Edition or even Standard Edition One, you have an environment which is capable of handling such information loads. Combine this hyper cost effective installation with a Smart Alternative like Dbvisit Replicate, replicating data away from you core POS infrastructure, delivering this data at a special database for this initiative. Here it is combined with stock information, also delivered by Dbvisit Replicate, to create a system that is real-time, robust and a system that doesn’t interfere with regular business. Moreover it creates a system which does support the business case by requesting up to 80% less investment.
This example shows that many of the smart ideas which were created by the business have stranded in an impossible business case. Today, the Smart Alternatives of Dbvisit create the opportunity for you to rethink these ideas and really start realizing them.

Just because it’s possible now!