Pipelined & Parallel Table Function in Standard Edition?
Earlier today I received a question from Mr. Lothard Flatz concerning a featured that to my understanding has been available since Oracle9i.
Thank you Lothard. And without any further information, I assume this question targets the database version 11gR2 or 12cR1.
My answer is … It depends…
To be frank, my first thought was “yes this is ok to use in a Standard Edition Database”, but after reading more about this feature, I started to hesitate and doubt my own answer. Why?
Please keep on reading, and you can judge for your self, am I making the correct assumptions or not.
Hopefully somebody among my peers can either verify or correct my assumptions in the end of this post.
Please also NOTE this:
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 classified as professionals on this business.
And while you are making your self acquainted with the subject, why not also read this article from Tim Hall about this feature before continuing.
“parallel execution” – in standard edition?
As you noticed from the picture above, Oracle documentation mentioned the word “parallel execution”, and that is always generating the question “is this ok in a Standard Edition Database environment, or not“.
We can only find something on the PL/SQL Function Result Cache.
I assume this is referring to the RESULT_CACHE clause, since that will to my understanding, boost the performance by reusing function results, instead of recalculating.
From the internet, we can find more information about RESULT_CACHE from either this post by Tim Hall or from the reference guide from 12c.
Note to my self: “So is the License document also referring to the DBMS_RESULT_CACHE package, and therefor we cannot use it in Standard Edition?”
Parallell Table Function
Since the “Table Function”- feature also has the parallel option available, it might be good to analyse it as well. The “Database Data Cartridge Developer’s Guide” 11gR2 document tell us this about parallel table functions:
And the 11gR2 License Document will tell us these things about parallelism usage (same in 12c):
I couldn’t find any additional information about “Pipelined Table Functions” or “Parallel Table Functions” from this document, so from the information provided by the License documents I would summaries the result:
Yes, to my understanding one of the features is available in our Standard Edition Database Environment – the Pipelined Table function.
But the other one – the Parallel Table Function I would like to classify as a “forbidden fruit”, and the reasons why is what the License Document tell us about Parallelism and this information from the Database Data Cartridge Developer’s Guide:
For a table function to be executed in parallel, it must have a partitioned input parameter. Parallelism is turned on for a table function if, and only if, both the following conditions are met:
The function has a PARALLEL_ENABLE clause in its declaration
Exactly one REF CURSOR is specified with a PARTITION BY clause- I hope somebody among my peers could verify or correct this assumption.
So what do you think?
As I mentioned earlier. The last resort when unsure, is asking Oracle for assistant.
Standard Edition 188.8.131.52 without Enterprise Edition options?
I just received a very good question, that need to get some attention and an answer, so this post is purely a request for help from anybody who might know.
This is the question I received from Bertrand:
“One more question, as 184.108.40.206 will be a SE release do you know what EE options will be removed ?”
Why did I address this question to four persons?
I took the liberty of addressing this question (on LinkedIn) to four people (Graham Wood, Mike Dietrich, Bryn Llewellyn, Kuassi Mensah) that I met last summer at the OUGF conference, since this question deserve some attention and I think they might be abel to bring some light to this question, or have somebody in their network that might be able to help.
The Standard Edition Community is concerned and interested in things addressing Standard Edition Database future, and by addressing this question to somebody, it will hopefully receive some attention and by so also an answer, and not get lost in the noise of information.
Of cause this question is to any Oracle Peer, who might have some information on the subject.
Thanks for Your help!
Take care (also remember to take care of your data), stay well, and have a lovely day!
Real-Time SQL Monitoring in Oracle Standard Edition
Feature is RED ALERT if you have a Standard Edition Database
Remember this feature needs the “Tuning pack”, and that we cannot buy when we are working on a Standard Edition Database Environment.
Please also keep in mind that if you have a mixed environment with both Enterprise Edition databases, and some Standard Edition databases, and you use the Enterprise Manager Cloud Control/Database Express tool, YOU MAY NOT use this feature on any database that has not been licensed with the Tuning Pack.
Anyhow, why not keep on reading, since this feature might bring new business value to your work as a DBA, and you only need to provide the solid information on Real Time SQL Monitoring features benefits to your manager.
I found this good article by Yasin Baskin about Real-Time Monitoring, so you can get more aquatinted with the subject. It was released the 7th of May this year.
Below you will find a small summary about this feature.
What is this Real-Time SQL Monitoring?
From the Oracle 11g Documentation we can see what this neat little feature can provide us:
Real-time SQL monitoring, introduced in Oracle Database 11g, provides a very effective way to identify run-time performance problems with resource intensive long-running and parallel SQL statements.
and from the Oracle12c documentation we can see it has been enhanced:
AWR has been enhanced in Oracle Database 12c to include reports from Real-Time SQL Monitoring, Real-Time ADDM and Database Operations Monitoring.
….Traditionally, this analysis has been done using reactive methods like SQL tracing, but the addition of Real Time SQL Monitoring enables you to monitor SQL statements while they are executing. Live execution plans of long running SQL are automatically displayed on the SQL Monitor page in Oracle Enterprise Manager using new, fine- grained SQL statistics that are tracked out-of-the-box.
Has it always been there?
SQL Monitoring was instrumented and available for command line use in the first release of the Oracle Database 11g. The graphical user interface to monitor active running SQL made its debut in Grid Control 10.2.0.5 and Database Control 220.127.116.11.
In Oracle12c you can find this tool as before, the name has just changed, so in 12c we can use both Oracle Enterprise Manager Cloud Control and Oracle Enterprise Manager Database Express.
Take care (remember to take care of your data as well), stay well, and enjoy the day!
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”
Right now I am enjoying my time with EMC training and a course on Big Data. During the course a statement about what is Big Data got me thinking
How many small companies running their business on a Standard Edition database system are maybe misinterpreting the concept of Big Data, and thereby loosing the opportunities that Big Data Analysis can bring them?
This is the statement I am referring to is (EMC Education property):
Big Data is really about ANY attribute that challenges the constraints of a system capability or business need, and it’s not only about size.
Thank’s EMC for pointing out this to me, and others that maybe like I have misinterpreted the concept Big Data.
So for those of you who are working on a Standard Edition Database, why not put your data to the next level, and begin to discuss the possibilities and extra value your data can give your business. As its not only about size, the Big Data Analysis is probably beneficial everywhere.
Maybe by analysing your data, a new services could be created, and by doing so, increase the business value for both you and your customers.
Take care (also remember to take care of your data), stay well and enjoy the day!
Two things that I would like to add to the interview:
Do I have any knowledge about any company who has upgraded/downgraded their database edition?
Yes I have experience from both.
a) One customer had the Standard Edition database, and performed an upgrade to Enterprise Edition database since they needed more advanced security options.
b) An other customer had been running their Enterprise on an Enterprise Edition database, and found out that the software actually is not using anything that the Standard Edition database couldn’t handle, so they made a downgrade.
As i said in the interview
It depends on the business and it depends on the application. If it is possible to use Standard Edition, or not. I don’t really believe in the fact that IT should just do the demands. I prefer the other way around.
Take care, stay well and enjoy the opportunities the Standard Edition can bring!
“the Oracle Standard Edition Database will stop being in the future”.
(this article was originally posted on LinkedIn)
I asked some peers about this rumour about standard edition future, to see if there might be a truth in there. Some had heard it and some had not.
I liked the comment from one person:
“Putting a rumour like that in the air, would be a easy way of checking peoples opinions, since if this edition is widely in use this rumour will cause some buzz among its peers”.
Many of us working with or having a big interest in Oracle Standard Edition database have noticed this from Oracle Support (Doc ID 742060.1 the end of patching for 18.104.22.168 is planned for July 2015), this is for all editions on 12c.
Bertrand sent me a question on my blog, that fired of this post, because the 22.214.171.124 Standard Edition Database future version is not yet available. Thanks Bertrand!
Inevitable this gap of a missing Standard Edition version 126.96.36.199, and the note on “end of patch of 188.8.131.52”, can cause some headache to some people planning an upgrade project in the Oracle Standard Edition Database Community.
When will the 184.108.40.206 Standard Edition Database version be available?
From Oracle Doc:1905806.1 this can be found: “A separate announcement will be made when we release 220.127.116.11 Standard Edition and Standard Edition One. When a public schedule is announced, it will be added to Release Schedule of Current Database Releases (Doc ID 742060.1).”
It was good to hear from one of my peers that Oracle is really working hard on a 18.104.22.168 Standard Edition Database version. Still the fact about the gap is there, and inevitable this timeframe may cause some stress in near future upgrading projects.
After all this rumours and facts around Standard Edition, it made me ask my self:
In the Oracle Community we have all kinds of ACE(s), which are people who love to help the society to get better OracleTechnology knowledge/information through all kind of social media, but I haven’t heard anybody speaking about any Standard Edition Ambassador, so
Is there such a person, to whom we can turn to, when we search for some specific Standard Edition Database information?
If such person is not available, would it be benefitial for the Oracle Standard Edition Community to have such a person?
Since you are still reading this, I hope the post awakened some thoughts and questions, since solutions only comes by questioning, and if there is any truth in this rumour, now is the time to speak up – if you would like to keep on enjoying Standard Edition Database and its opportunities in the future.
Since both Finland and Sweden will have “Vappu”/”Valborgs” on Thursday, I wish you all either “Klara Vappen (Finnish salut)”, or “Trevlig Valborg”!
Take Care, stay well, and on Thursday: “drink not drive”.
Long time ago Oracle released the Oracle webDB product, which to my understanding is the far away ancestor of the latest APEX 5 product. The knowledge of webDB isn’t much of a help anymore, so before proceeding to install APEX5 on my Standard Edition Database environment, I decided its better to ask an expert on the subject, for some guidance. I didn’t need an upgrade, since this was a clean environment.
Last year at the OUGN14 conference, I had the pleasure to meet this very nice lady Iloon Ellen-Wolff, and since she’s the Global Technical Lead Application Express, I asked her for some advices. Thanks Iloon for all good information!
since I insisted on staying on my SE, I needed to patch the database with 19769486 (later also found out from a blog, that 20618595 should be implemented, so ended up installing 20299016, which contained both patches)
uninstall the pre-installed APEX402 from the CDB
install the APEX5 into the PDB
Yes in a Standard Edition I am entitle to one PDB
Install and configure the Oracle REST Data Service
I was told, that this is the preferred service to use with APEX5
It later turned out, that I needed the documents mentioned below (1-4) plus some direct questions to different people, and some googling, before my APEX5 + Oracle REST Data service was functioning.
The installation steps for 12c PDB I got from the APEX5 Installation Guide. This text If you configured RESTful Web services, then these additional accounts will be created, from the section 4.5.1 Downloading and installing APEX (step 7) did make me confused; should I have installed the REST before APEX5?
I installed and configured the Oracle REST data service by using this guide
And then performed the authentication specific step by following this guide
Even though I performed the steps I ended up with this error when logging into my http://localhost:8080/ords/
My struggle with Oracle REST Dataservice was inevitable. This was actually a good thing, since personally I believe that by getting errors you actually get the opportunity to learn more.
The above error, wasn’t that simple to get around – I asked different kind of people for their opinions, and I got as many advices as people asked.
The “apex_epg_config.sql” I don’t need to run (admit, I even tried this one), since I am not using the Embedded PL/SQL Gateway. This is to my understanding the preinstalled APEX402 method, since it uses the Oracle XML DB HTTP components, and its a component available to those who don’t want to install/use a seperate HTTP Server.
i.war file was then suggested. Under 1.6.1 Weblogic Server there is a 1.6.4 Configuring …Images. This one I did, and I copied it to two different locations:
/oracle/home/apex/images (the APEX5 zip file was unzipped to /home/oracle/apex)
/oracle/home/ords/apex/images (the REST package was unzipped to /home/oracle/ords, and the APEX- folders images director was copied here)
The last thing I did, was adding this row to the apex.properties file (leaving the i.war files in the above directories), and after this everything worked and I am now constructing my first APEX5 application.
Personally I would have liked the idea of one document in a step-by-step fashion way, and not with all these references to all kind of documents.
Troubleshooting section, did not give me a non-java-developer much help, and I am still wondering about this:
“should the REST service be installed before the APEX5”,
since I was redoing many of the phases during this “image misconfiguration troubleshooting phase”.
I guess I just need to start from scratch one more time, and use my own step-by-step notes that I made, to see if the assumptions I am now making are right or wrong. Or maybe somebody out there have an answer?
But I leave it to another time, because now I am learning to masterAPEX5.
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 22.214.171.124 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?