Big Data => Gold Fringed Data in Oracle Standard Edition?
Yesterday I wrote a post where I asked the question “Big Data in Standard Edition Seriously?“, because it struck me that I had misinterpreted the concept Big Data, and as I see it, this is not something that should only be available to large Enterprises.
I am a complete novice on this, since I started to study this yesterday, but somehow I cannot understand why this should be a “no-can-do-thing” for smaller companies. To my understanding all companies struggles with the same issues like “how to grow”, “how to make their customers satisfied”, “what service should we provide”, “what processes do we need to refine” and so on, they only have less data. The concept “Big Data” is therefor misleading, and I would like to call the small companies data “Gold Fringed Data”, since it is as valueable to their business as “Big Data” is for the enterprise.
I found this article from Pierre-Luc Paquin on twitter
This article gave me new insight in what kind of “Big Data=Gold Fringed Data” actually already is available to small companies.
Another article from SAS just made me more convinced about the benefits small companies could gain from their “Gold Fringed Data”
A part from the article about why you should consider using Hadoop:
Low-cost storage and active data archive. The modest cost of commodity hardware makes Hadoop useful for storing and combining big data such as transactional, social media, sensor, machine, scientific, click streams, etc. The low-cost storage lets you keep information that is not currently critical but could become useful later for business analytics.
Staging area for a data warehouse and analytics store. One of the most prevalent uses is to stage large amounts of raw data for loading into an enterprise data warehouse (EDW) or an analytical store for activities such as advanced analytics, query and reporting, etc. Organisations are looking at Hadoop to handle new types of data (e.g., unstructured), as well as to offload some historical data from their EDWs.
I know of some Oracle Standard Edition Customers in the past that I have been working with, that have large amount of historical data that are not dead, but ice cold – They are seldom using it, but every now and then, an old customer asks about the information, and they need to put a very large amount of time and effort into retrieving the old data. So I just asked my self, wouldn’t Hadoop bring a nice solution to this? To my knowledge, Hadoop is an open-source, so the costs wouldn’t be that high I assume.
When Hadoop once is implemented who knows, by letting the imagination loose, maybe one or two new services could just flourish from there, and the small company can grow to become an Enterprise.
It’s easy letting the imaginagation loose at the beginning of a new learning path. Everything is possible. That’s the beauty of learning.
If you have good articles or stuff about Big Data I hope you share the information, since I am searching for opportunities for smaller companies that are using Oracle Standard Edition Database (or any other vendor), because I am sure they value their “Gold Fringed Data” as much as the big Enterprise values their “Big Data”
dbms_space -Can we use this package to the fullest in Standard Edition?
Updated: 27.5.2015 Confirmation received from an Oracle License Expert….scroll down to
the red part ,There is the confirmation.
27th May 2015/Ann
The Original post 12th April 2015 starts here
When I was googling for information about this DBMS_SPACE package, I found that some people say “No you need the diagnostic & tuning pack”, and other people say “go ahead and use it”.
So this requires a test case.
This situation of finding out “can we use something or not” is for us Oracle Standard Edition DBA’s basic “daily“- routines (at least for a customer oriented DBA).
Oracle has started to help out more and more, when comparing to old documentation vs. new documentation, but there are still stuff out there that need to be better documented, or even better; the software should tell us if something is not ok to use.
I feel that this DBMS_SPACE package is one of those things that need better documentation.
Oracle licensing is a complicated business. The notes here are only a guide. You should always discuss your licensing with Oracle License Management Services or any other third-party company, who are professionals on this business.
SUMMARY of Test Case on the DBMS_SPACE package
“Technical blog post sometimes become to lengthy with all the tech stuff“, so for you who are not keen on knowing the details, here is a brief summary of the test cases performed on DBMS_SPACE and the challenges I think it causes us Standard Edition DBA’s.
Some test results I have received are of a surprise to me. And I would love to hear from you gurus, is my test method to simple and need corrections, or why is the outcome as it is?
<27th May 2015 – Confirmation from an Oracle License Expert START
According to a letter from an Oracle expert on License that I received, the DBMS_SPACE package is Standard Edition proof:
“the DBMS_SPACE can be used in Standard Edition, since it is making use of DBA_HIST_SEG_STAT and DBA_HIST_SEG_STAT_OBJ views, and doesn’t require the Diagnostic Pack”
Let’s take a look at the Enterprise Manager License document:
“All data dictionary views beginning with the prefix DBA_HIST_* are part of this pack, along with their underlying tables. The only exception is the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.”
As always when you get questions about Oracle License, follow the note above, and verify with an Oracle License Expert. Thanks Oracle License Expert for your assistant!
Note to myself: On the internet you can find an article on how to Measuring Database Space Growth if you would like another method on how to accomplish the growth measurement, instead of using this package.
27th May 2015 -Confirmation from an Oracle License Expert END>
….and back to the post written on the 12th Apr 2015 …
From the Oracle Document on Diagnostic Pack API’s, I cannot find any information about DBMS_SPACE (if it’s ok to use in Standard Edition or not)
This post by Tim Hall on DBMS_SPACE (old yes, but still valid?) Crunchy “To Be Or Not To Be?”; Question still remains.
Test environment & methods information:
fresh newly installed 220.127.116.11 Standard Edition with one PDB
control_management_pack_access string NONE
Oracle Linux 6.6
These tests are done connected to the CDB. I will later write about the results from the PDB.
1) Read Oracle License document
2) Read PL/SQL reference document
3) run the DBMS_SPACE.[function][procedure]
4) run DBMS_FEATURE_USAGE_INTERNAL (update usages)
5) check DBA_FEATURES_OPTIONS_STATISTICS
6) Reporting the test results
Since Oracle provide with two scripts for checking the usage of a feature/option (see below), I just used them (see below for the Doc Id).
According to the documentation, Oracle automatically update the statistics every 7th day, and according to Dan Morgan (see below), this can be overrides by running the package DBMS_FEATURE_USAGE_INTERNAL, so of cause I used that instead of waiting 7 days after every test case :). The Description for this package: “Collects usage statistics for all features”.
RESULTS: My Oracle Standard Edition did not register any use of these (question: test methods to simple? ), and I would have expected that at least one function should have been registered.
Or is it just so that this package can be used to the fullest in a Standard Edition Database? What do YOU think?
Test Case 10: DBMS_SPACE.OBJECT_GROWTH_TREND Function – This one uses the AWRF, so this usage should have been registered in the DBA_FEATURE_OPTION_STATISTICS – or that’s what I expected. But no information?
As I already pointed out. I think there are something fishy in here, and I will continue to investigate to why my Oracle Standard Edition didn’t record the use of at least one function, that use the AWRF.
We already know the facts – we cannot buy the diagnostic & tuning pack for our Standard Edition database environment, so when the documentation states that something is using the AWRF, at least I would have expected my standard edition database to register the function that I ran.
If the reason is that my test case methods is to simple – than this is just awesome, since I just redefine my test methods.
BUT if the reason is something within Oracle, that for example the registration of using something is more complicated, than running this DBMS_FEATURE_USAGE_INTERNAL package as Dan Morgan told us to, than this is not good – how on earth can a Standard Edition DBA be sure of not violating the license agreement, if neither the software nor the documentation is guiding us?
I hope you enjoyed this post, and if you would like to know more details about the tests, just keep on reading.
And to you who are not so much into details I wish you a nice Sunday!
Take care, stay well, and see you soon again.
Turku 12th April, 2015
DETAILS onTest Case on the DBMS_SPACE package
First I check the Oracle 12 License document to see what information can be found about the DBMS_SPACE package.
There are no specific information about DBMS_SPACE in the license document.
(Note: With Oracle, the right to use or not to use something depends on the version of the database, so remember to verify against the right database versions license document).
One function in particularly – the OBJECT_GROWTH_TREND, caught my attention:
“…will be retrieved from Automatic Workload Repository Facilities (AWRF), or the current space usage will be computed and combined with space usage deltas retrieved from AWRF”
This is to my understanding pretty straight forward – This function uses AWRF, and that means the Diagnostic Pack, and that we CANNOT use in an Oracle Standard Edition.
How about the rest of the procedures/functions?
My third step; I googled a little bit more on the subject and from this great blog (the example SQL was used in this tests) by Dan Morgan I saw this dependencies list:
The Dependencies part of this post, mentiones tables starting with WRI$, WRH$, X$, and at least the two first prefixed tables are AWR related, so for me this is clearly a signal: more investigations are required.
Fourth step: As many of you already know, Oracle Support provide two scripts on “Database Options/Management Packs usage” (Doc ID 1317265.1), to help us find out what is actually happening in our database.
My test is very simple. After running the specific function/procedure, I ran these scripts, to see what my Oracle Standard Edition database thinks about life.
Note: from DOC: “Note that the feature usage data in the view is updated once a week, so it may take up to 7 days for the report to show recent usage of options and/or packs.”
This can be fixed according to Morgan’s Library that has some notes about how to force an update by using
Shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.
Conclusion on DBMS_SPACE.UNUSED_SPACE Procedure : As in all other test cases. Nothing was recorded by Oracle Standard Edition. What would you say? Is it fare to assume it is ok to use in a standard edition database?