Article about Some useful tools from my SE Toolbox…and some SE2 License stuff at the end 😉
A New kid on the block – SQLCLI
As a Standard Edition DBA, I’m sure you have numerous times used the good old SQL*PLUS. Recently I noticed that the new SQLCLI tool is not a common known fact among “DBA’s not attending any conferences”, so I like to promote this nice new tool to the Standard Edition database DBA’s toolbox.
The Oracle SQL Developer Product Manager Jeff Smith’s team has developed this new tool “SQLCLI”, and it can be downloaded from SQLDeveloper homepage. (scroll down the page).
After the download and installation, it is ready for use. Note! On my mac the “sql” didn’t run, so I just started the java jar file from the “sqlcli/lib- directory:
SE2mac$ java -jar oracle.sqldeveloper.sqlcl.jar hr@//se12t1:1521/se2pdb
Why not Check some youtube videos about SQLCLI, to get the drift about this tool?
In older versions of SQLDeveloper, I faced some pitfalls with the predefined DBA reports. For example I was able to use the “AWR reports” without any notifications about violating the license agreement in my Standard Edition database.
The latest version of SQLDeveloper, version 4.1.1, will give you a warning about the license requirements:
And why do you need to READ this note and answer NO?
By answering YES, I am able to use these AWR reports without any problems, and if proceeding I will violate the License agreement, since I am on a Standard Edition 2 database environment.
Note! Also in a Enterprise Edition database this violation is possible, if you haven’t purchased the “Diagnostic&Tuning Pack”.
By answering YES, you will be able to use the reports as shown in the screenshot below:
The “dialog” warning, looks like a normal behaviour independently of the software. For example TOAD software provide with the same kind of dialog during the installation phase. And remember, you can at any later state enable the pack, by changing the softwares preferences/properties; so an other challenge to remember.
Standard Edition 2 on the Menu
After a long wait amongst people in the Standard Edition Community, Oracle Corporation finally released the 184.108.40.206 edition of the Standard Edition Database on the 1st of September.
With this release, lots of buzz has been generated in all Social Medias available, and lots of blog posts have been generated.
As always, a message can be interpreted in many ways, and both good and less good stuff can be found out there on what this SE2 realease bring to the table; after you have carefully read the license documents available, and still feeling unsure, please check with your local Oracle LMS before making any false or unecessary assumptions on the impacts on your business.
Some small rules to remember about SE2
The minimums for Database Standard Edition 2 are 10 Named User Plus licenses per server
This metric is used in environments where users can be identified and counted.
Install onto a servers that have a maximum capacity of 2 sockets (*see)
Oracle Database Standard Edition 2 database may use a maximum of 16 CPU threads(**see) at any time
Oracle Real Application Clusters, Oracle Database Standard Edition 2 may only be licensed on a maximum of 2 one-socket servers
on RAC – each Oracle Database Standard Edition 2 database may use a maximum of 8 CPU threads per instance at any time
Hard partitioning (*** see) can be used on 2 sockets servers to enable you to run RAC on 2 two socket servers with both CPUs populated
(*)When licensing Oracle programs with Standard Edition One, Standard Edition 2, or Standard Edition in the product name, a processor is counted equivalent to an occupied socket;however, in the case of multi-chip modules, each chip in the multi-chip module is counted as one occupied socket.
(**)From the CPU's perspective, a thread (short for "thread of execution") is merely an ordered sequence of instructions that tells the computer what to do.
So when someone talks about a "multithreaded processor," they're talking about a processor that can execute multiple instruction streams simultaneously. There are two ways that a processor can perform such a feat: simultaneous multithreading, and using multiple cores. Neither of these methods is mutually exclusive, and both are often used together.
*** HARD partitioning physically segments a server, by taking a single large server and separating it into distinct smaller systems.
*** SOFT Partitioning: Unless explicitly stated elsewhere in this document, soft partitioning (including features/functionality of any technologies listed as examples above) is not permitted as a means to determine or limit the number of software licenses required for any given server or cluster of servers.
220.127.116.11 Standard Edition R2 is planned for Q3 CY2015
NOTE – UPDATE – 8th of July 2015
Changes has been announced today, so PLEASE CHECK these new documents!
Replaced by updates in Doc1905806.1 and Doc742060.1
What an amazing day of turbulence on twitter!
NOTE – 3rd July 2015 Breaking News
Standard Edition (SE) and Standard Edition One (SE1) are replaced by Standard Edition 2.
Customers running SE or SE1 will need to migrate their licenses to SE2 to be able to upgrade to 18.104.22.168. Note 2027072.1
During last months the Standard Edition Community have often asked themselves:
“When will Standard Edition Database release 2 (22.214.171.124) arrive?”
Yesterday I received a question from Laurent Leturgez , asking if I had any information about SE126.96.36.199:
Since I hadn’t, I asked the Database Product Manager at Oracle Dominic Giles, and shortly the Standard Edition Community received the information we all been waiting for:
The Standard Edition Database version 188.8.131.52 (Rel2) is planned to arrive in Q3 CY2015! And 184.108.40.206 Support extended 6 months
Thanks Dominic for assisting the Oracle Standard Edition Community during this time of uncertainty.
Twitter Turbulence – 3rd of July 2015 (Europe temp > 35°C)
However, this message kicked off some “turbulence on twitter”, and besides facts, of cause also lots of seeds of rumours were planted.
This topic is doubtfully something to write about, and I was very pleased to find this blog post about
Native Network Encryption and SSL/TLS are not part of the Advanced Security Option
As you already might have noticed, I look into things from a Standard Edition Database point of view, and to me Tim Hall’s post was executed in a more “Standard Edition Mind Thinking” fashion, and that is for me so amazing to notice, since there are not too many blogs out there at the moment, which are blending the “Standard Edition Database challenges” flavour into their post.
Please check his blog, but the main thing for SE is:
“Network encryption (native network encryption and SSL/TLS) and strong authentication services (Kerberos, PKI, and RADIUS) are no longer part of Oracle Advanced Security and are available in all licensed editions of all supported releases of the Oracle database.”
GDRP – Standard Edition Database – any options out-of-the-box?
Oracle released this document in January 2017:
“Accelerate Your Response to the EU General Data Protection Regulation (GDPR) – Using Oracle Database Security Products”
When you have an Enterprise Edition Database, and money are no issue, it looks like a pretty straight solution.
The “Oracle Maximum Data Security architecture” gives a nice overlook about what is available for EE:
Automatic Data Optimisation (=ADO) in Oracle Standard Edition?
Time to inspect the Automatic Data Optimization feature that Oracle12c provides from a Standard Edition Database perspective.
Updated 22.6.2015: I feel privileged having peers reading my blog, and receiving feedback. Standard Edition Community needs a blog with facts, so Thanks Franck Pachot for correcting me on Basic Compression. I would also like to thank my friend Joel Goodman for advice on this topic.
I am actually pleased that I made this mistake since it only reminded me on how easy it is to misinterpret and make wrong assumptions.
I found this very good post on basic table compression by Jonathan Lewis. As a none native English speaking person, both the way the text below is written and the use of the word “core” made me make the wrong assumptions, and I undermined my own advice “always check and double-check the License Document”.
“Of the three mechanisms, index compression and basic table compression are part of the core product, while OLTP compression is part of the separately licensed “Advanced Compression Option (ACO)”. In this first article we will restrict ourselves to creating data using basic table compression, leaving the question of updating and deleting compressed data to the second article, which will lead us into “OLTP” compression in the third article. We’ll leave index compression to stand alone in the fourth and fifth articles.” (Jonathan Lewis).
NOTE!! The Basic Compression is not Standard Edition Database Proof. So do not use it in a Standard Edition Database!
So I checked the License information on Oracle9i:
So wait, doesn’t it tell us that Compression is ok to use like J Lewis said? No! Once again I made the wrong assumption! The structure of the Oracle9i document is not the same we are used to (SE1 | SE | EE).
In Oracle9i the structure is (SE | EE|Personal Edition) – Yes Oracle tells us if a feature is available in Personal Edition:
Explanation for a Business Management’s type of person.
The rapid growth of data presents daunting challenges for IT, both in cost and performance. Although the cost of storage keeps declining, fast-growing data volumes make storage one of the costliest elements of most IT budgets. In addition, the accelerating growth of data makes it difficult to meet performance requirements while staying within budget.
Explanation for a more Technical person:
Automatic Data Optimization (=ADO) automatically moves and compresses data according to user-defined policies based on the information collected by Heat Map. Heat Map automatically tracks modification and query timestamps at the row and segment levels, providing detailed insights into how data is being accessed.
Explanation in a Graphical view
Note: I am not an expert on HW, so when making this graph the choice of storage might not be the best, but to my understanding both the raid50 and Hadoop HDFS alternative seem like good options. To my understanding HDFS is available as a plug-in to ADO, even thought by doing so, the full “data travel path” will not be automatically managed by the database, but that’s a whole different story.
Explanation from the License perspective:
Clear and loud. We cannot use it in a Standard Edition Database environment.
This new Automatic Data Optimisation feature can exploit the functionality of the Partitioning Option and the Advanced Compression Option, and neither of those capabilities are available in a Standard Edition Database Environment.
Note: the partitioned views is not a requirement of ADO, but without the pertaining functionality you will lose the flexibility of using different storage tiers and different compression on different parts of the same table. And as you already maybe have read from my blog, we can create partitioning views in our Standard Edition Database Environment, and by doing so, we can achieve some degree of flexibility also in our Standard Edition Database Environment.
ADO feature allows active data to live on high performance storage like solid state technology, and older inactive data to live on high capacity drives, and allows the DBA to leverage different storage technologies for the same table. The magic behind all this is the new technology called Heat Map.
Heat map tracks the last access or update time for segments and extents and can also track at the database block level. This includes the time of last modification and access of tables and partitions. There is also a Row level Heat Map that tracks modification times for individual rows (aggregated to the block level).
How does this magic come into place?
You should enable Heat Map (alter system set heat_map = on scope=both)(manually performed)
Heat Map Statistics are collected
V$HEAT_MAP_SEGMENT (MMON collects and “write” the info to this view), HEAT_MAP_STAT$ (dbms_scheduler regularly flushes the info to this table from previous view), DBA_HEAT_MAP_SEG_HISTOGRAM & DBA_HEAT_MAP_SEGMENT (are views based on the previous table)
You create ADO Policy on table (manually)
ADO Policy get evaluated
ADO actions will be executed
A beautiful feature but we cannot use it in Standard Edition.
Can we enable ADO in Standard Edition?
There are things a Standard Edition DBA can unintentionally perform, when not alert, and the software will not tell you if a license agreement violation will occur by continuing performing the task you were just about to perform.
Will Oracle give us a helping hand by performing the steps above to activate Heat Map and ADO feature in a Standard Edition Database?
Sandbox and Testcase
Oracle Standard Edition version 12.
Database Patch Set Update : 220.127.116.11.7
Linux se12t1.min.com 3.8.13-55.1.5.el6uek.x86_64
An out-of-the-box installation of the Standard Edition Database
SQL> SELECT sys_context('USERENV', 'AUTHENTICATED_IDENTITY') as Environment FROM dual union all SELECT sys_context('USERENV', 'CDB_NAME') FROM dual union all SELECT sys_context('USERENV', 'CON_NAME') FROM dual;
SQL>ALTER SYSTEM SET heat_map=ON scope=both;
alter system set heat_map=on scope=both
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00439: feature not enabled: Heat Map
Bang! We cannot even perform step one from the list above. Great and Thanks Oracle!
Can we mimic ADO in Standard Edition?
NOTE!! The Basic Compression is not Standard Edition Database Proof. So do not use it in a Standard Edition Database!
Note: Starting from 12c we can use the SecureFile for LOBS, but remember the SecureFile Compression is part of the Advanced Compression option, and that is an Enterprise Edition option.
Basic Compression – NOT OK in Standard Edition
Basic Table Compression N N Y
The Oracle Advanced Compression option contains the following features:
SecureFiles Compression and Deduplication
--CTAS with basic compression enabled
create table t_se1 compress basic
select * from [some_table] where [column] = [value];
--Direct path insert into empty table defined as compressed
create table t_se1 compress basic
select * from [some_table] where rownum = 0;
insert /*+ append */ into t_se1 select * from [some_table] where [column] = [value];
How about moving data according to usage?
Archived data is usually cold, so that should be easy to move to a less expensive storage tier. But even so, I do think this requires a good data model, knowledge about not only the application capabilities, but also good knowledge about how the end-user is actually using the application and what is the business process that they are following. Again, the importance of good collaboration between people is vital.
Figuring out what can be moved in SE
This SQL shows information about segments read and write occurrence per date, and according to the License Document, these views are Standard Edition Proof :
WITH se_ado as (
SELECT o.owner,o.object_name, o.object_type,s.db_block_changes_delta bcd,
WHERE o.owner='SEHC'and s.obj# = o.obj#
and sn.snap_id = s.snap_id
GROUP BY o.owner,o.object_name,o.object_type,s.db_block_changes_delta,to_char(begin_interval_time,'mm/dd')
ORDER BY 4,1,3)
select se_ado.* from se_ado where se_ado.writes=0 or se_ado.reads=0
With this script as a starting point, plus lots of evaluation on what are your company’s metrics on moving data to a less expensive storage tier destination, and both some scripting and some maintenance windows, I think you could mimic ADO to some degrees.
When all your metrics have been evaluated, a scheduled job could perform the collection on a regular basis, that send an email with the results to the DBA.
The magic of moving objects around will be very difficult to mimic, because as the license document reminds us of, ONLINE functionality is only available in Enterprise Edition Database Environment.
So for us Standard Edition DBA’s, we need to have a maintenance window. In my experience, this is not a big issue, when all the parties have agreed and are committed to a solution that now a then requires a maintenance window.
Solution good enough for your company or not? It depends.
Personally I am a strong believer of the concept that I came up with during a discussion at the Harmony15 Conference; “a Mental Sauna” meeting. Briefly it’s about not “pull rank” on someone else. You have no uniform on, so no visible rank to fall back on. In a Finnish Sauna it’s tradition to join, having either only your birthday suit or a swimming suit on, so it is understandable very difficult to tell is a person a priest, a CEO, a plumber, or what so ever, and that nurtures an environment were people freely exchange their thoughts.
Note: The Finnish Sauna tradition is something special, so if you have no experience of it, please read the article before making any assumptions.
I believe the result after such a “Mental Sauna”-meeting on for example “Shall we mimic ADO or go for the genuine ADO solution?” would be more uniform since all parties (people like: Application Vendor, the Business Owner, the IT department, support, the end-user, and solution conductor etc) have already discussed the advantages and disadvantages of the topic, and therefore have a stronger commitment to the solution.
Pretty cool feature Oracle engineers have created, isn’t it?
If you are running on a Standard Edition Database, and find this ADO feature appealing, why not start a “mental sauna”-meeting and see which road you choose to take.
Oracle12c Application Continuity in Standard Edition
This morning I read some stuff on the new 12c feature APPLICATION CONTINUITY and TRANSACTION GUARD.
To my understanding this feature is more a JDBC driver feature than a database feature, so I said “yes” of cause this is Standard Edition Proof, but after having some issues summing-up the facts around this feature, I hope you could assist me on this question
Update 8th June 2015: No this feature is not Standard Edition Proof, so please tell your Java Developer NOT TO IMPLEMENT.
If you are interested in, what made me ask this question, keep on reading.
“Is this feature Oracle Standard Edition Proof or not?” The Answer:NO
Facts from Oracle’s own documents
By googling on the option we can see from OTN what it can provide us:
Application Continuity (AC) is a new feature available with the Oracle Real Application Clusters (RAC), Oracle RAC One Node and Oracle Active Data Guard options that masks outages from end users and applications by recovering the in-flight database sessions following recoverable outages.
Transaction Guard is a reliable protocol and tool available with the Oracle Database 12c that returns the outcome of the last in-flight transaction after outages that make the database session unavailable. It is used by Application Continuity to ensure that an in-flight transaction is committed successfully, no more than once. It can also be used directly by applications to return a known outcome to the end users.
RAC feature is by default included in the Standard Edition Database for free, so it should be ok – right?
From the white paper we can read this
Introduced in Oracle Database 12c, Application Continuity strengthens the fault tolerance of systems and applications that use an Oracle database. Application Continuity is available for
Oracle WebLogic Server
Oracle Universal Connection Pool, used standalone or as a data source for a third party Application Server
Standard 3rd Party JDBC application servers using the JDBC PooledConnection interface
Oracle JDBC-Thin Driver
WebLogic Server is a product on its own, and with its own license model, but the three others is to my understanding Standard Edition proof “methods”.
12c Support information
Application Continuity for Oracle Database 12c supports the following client and server features:
Oracle Database 12c Client
Oracle JDBC Replay Driver 12c or later. This is a JDBC driver feature provided with Oracle Database 12c for Application Continuity, referred to as the “replay driver” onwards.
This text is from the WP, and below is how the text continues:
General information on JDBC: The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and a wide range of databases…(picture below)
Can third party vendors distribute Oracle’s JDBC drivers along with their own software? If you are a third party software company (and Oracle partner) then please check out Oracle’s licensing terms spelled out at Oracle Licensing Agreement Please contact your local Oracle sales rep for more details.
Behind the link, you will find the License Rights:
Oracle License Document
Here is where I start to have problems summing-up the facts.
According to the license, we cannot use the Application Continuity and since the Transaction Guard is part of the AC, not that either.(TG is used by Application Continuity to ensure that an in-flight transaction is committed successfully, no more than once.)
RAC is included for free in a Oracle12c Standard Edition Database (Note: not in SE one), and this feature relies on JDBC that is Standard Edition proof, and the License Document tell us this feature is not Standard Edition Proof.
So what do you think?
“Is Application Continuity and the Transaction Guard features Oracle Standard Edition proof or not?”
The Answer is : NO – we cannot use this feature with a Standard Edition Database.
And a reminder….
Note: 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.
Take care, stay well, and let’s enjoy the summer….Please send some summer warm weather!
…In Finland it’s only 14 degrees at the moment, and a cold wind…
Note: 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.
The Oracle12c Database has provided the Oracle community with many new features, and one of them is “the Row Pattern Match“.
In this video by Tom Kyte from December 2013, we can se that he put this feature on the Top 12 features list.
What is Row Pattern Match?
Oracle now provides a completely new native SQL syntax for pattern matching.
It has adopted the regular expression capabilities of Perl by implementing a core set of rules to define patterns in sequences (streams of rows) using SQL.
This new inter-row pattern search capability complements the already existing capabilities of regular expressions that match patterns within character strings of a single record.
The 12c MATCH_RECOGNIZE feature allows the definition of patterns, in terms of characters or sets of characters, and provides the ability to search for those patterns across row boundaries.
This is a nice feature, and nice features usually are delivered through some options or management packs, and only to the Enterprise Edition database.
This post asks the question is: “the Row Pattern Match feature Standard Edition Proof? Can I use it?”
You can check this article by Tom Kyte with an example on how to use this feature. Here is the example from that article:
SQL> SELECT *
2 FROM stocks MATCH_RECOGNIZE
3 ( PARTITION BY symbol
4 ORDER BY tstamp
6 STRT.tstamp AS start_tstamp,
7 LAST(DOWN.tstamp) AS
8 LAST(UP.tstamp) AS end_tstamp
9 ONE ROW PER MATCH
10 AFTER MATCH SKIP TO LAST UP
11 PATTERN (STRT DOWN+ UP+)
13 DOWN AS
DOWN.price < PREV(DOWN.price),
14 UP AS UP.price > PREV(UP.price)
15 ) MR
16 ORDER BY MR.symbol,
SYMBOL START_TST BOTTOM_TS END_TSTAM
—————— ————————— ————————— —————————
XYZ 01-SEP-12 03-SEP-12 07-SEP-12
XYZ 07-SEP-12 10-SEP-12 13-SEP-12
Question: PARTITION – so I cannot use the “Row Pattern Matching”-feature? Or is this referring to something else and not the Oracle Partitioning context?
By checking the Oracle12c License Document and the Enterprise Manager License documents, I cannot find anything referring to the keywords. There is though one row on Oracle Advanced Analytics (=OAA) option, and a second row on Oracle On-Line Analytic Processing (OLAP). Both options are only available in an Enterprise Edition Database.
Oracle OTN site on OLAP, you can find this video explaining the features of OLAP. After looking at this video, OLAP option can to my understanding, make use of any available Analytic Functions as well as basic SQL commands together with its own Cube/OLAP specific “language”, so I am tempted to assume that “Row Pattern Matching” is not part of the OLAP option.
“Analyze “market baskets” to discover associations, PATTERNS and relationships”
Can it be so, that “Row Pattern Matching” is part of OAA option?
Reaching out for help from an Advanced Analytic Expert
Since the documentations is not very straightforward on answering my question, (or maybe its the fact that English is not my mother tongue), I thought I better reach out to one of my peers, Brendan Tierney – who is an expert on Advanced Analytics, and see what kind of thoughts he might have about this new feature:
“As far as I am aware of, the Row Pattern Matching is part of the suite of Analytic Function in Oracle, and as far as I know, it is not part of the Advanced Analytics Option. It has never been mentioned or associated with Oracle Advanced Analytics.”
Promising….. How about PARTITION?
“The PARTITION clause is a special feature of the Analytics Functions that allows for the processing/analytics to be performed on different groupings of attributes and their values.”
This is also verified in the above Analytic Function document (Note that the term “partitions” used within the context of analytic functions is unrelated to Oracle Partitioning.)
BTW: Brendan is going to speak about “Predictive Analytics”, “Data Miner”, and”Predictive Queries” at OUGE Harmony15 Conference in Tallinn. Why not join?
Thanks for sharing your knowledge, and helping me out!
How cool would it be to find a link within the Oracle License Documentation to a list on for example the OAA options all packages, functions, tables, views, procedures etc that it is using – It would give a very quick answer to Standard Edition specific questions. Now finding an answer to the question “SE Proof?”, can be pretty time consuming, because the information is scattered around. Or a Standard Edition Spokesperson, to whom you could send your question(s) to? Wouldn’t that also be pretty cool?
The “Row Pattern Matching” feature seems to be part of the general Analytic Functions Suite that seems to be a standard functionality of the Oracle database, and I couldn’t find any text referring to the Enterprise Edition options mentioned above. I am tempted to say “yes, it’s SE proof”
What’s your thoughts? Is this new feature Standard Edition Proof or not?
June: Message from an Oracle License Expert: Yes it is Standard Edition Proof.
Take care (also remember your data), stay well and lets enjoy the long and bright Summer Nights of Scandinavia!
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.
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 18.104.22.168.
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!
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?