All posts by Ann Sjökvist

Standard Edition has the same core tech as Enterprise Ed. It just haven't all nice gadgets available so different solutions is required. Easy to violate license agreements, so be careful out there :)

Pipelined Table Function in Standard Edition

pipedTableFunction_2ways

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.

pipelinedTable_function_se_twitter

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.


What is this Pipelined Table Function?

From the “Database Data Cartridge Developer’s Guide” 11gR2 document we can read:

tableFunctions_summary

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“.

First we need to check the  Oracle License Document from release 11.2 for some guidances:

11gr2_features_edition_performance

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:

paralelltable_function

And the 11gR2 License Document will tell us these things about parallelism usage (same in 12c):

    • Parallel backup and recovery                      N N Y
    • Parallel query/DML                                          N N Y
    • Parallel statistics gathering                          N N Y
    • Parallel index build/scans                              N N Y
    • Parallel Data Pump Export/Import          N N Y
    • In-memory Parallel Execution                     N N Y
    • Parallel Statement Queuing                         N N Y
    • Parallel capture and apply via XStream N N Y
    • Parallel spatial index builds                           N N Y

The 12c Enterprise Manager License Document

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:


Summary

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:
  1. 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:
    1. The function has a PARALLEL_ENABLE clause in its declaration
    2. 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.
Confirmation:
lothar_conf
Take care, stay well, and enjoy the weekend!
-Ann
Turku 15th May 2015

Standard Edition 12.1.0.2 without EE options?

 

Standard Edition 12.1.0.2 without Enterprise Edition options?oracle12c

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 12.1.0.2 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!

-Ann

Turku 15th May 2015

Real-Time SQL Monitoring in Standard Edition

 

RealTime SQL Monitoring

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?

11gFrom 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.

12cand 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?

11gSQL 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 11.1.0.7.

12cIn 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!

-Ann

Turku 14th May 2015

 

Gold Fringed Data in Standard Edition

guld

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

twitter_bd_se

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:

  1. 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.
  2. 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”


Take care, stay well and have a lovely day

-Ann

Turku 13.5.2015

Big Data and Standard Edition

bigdata_post

Standard Edition and Big Data Seriously?


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!

-Ann

Turku 12.5.2015

 

 

Standard Edition is something for the Enterprise

 

http://thegeekymormon.com (c)

SE in the Enterprise ?


A little while ago I was asked to make a podcast on my thoughts on Standard Edition and can this edition be something for the Enterprise.

If you are interested of my thoughts, check this ad-hoc interview made by Kay Williams from Madora Consulting.


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!

-Ann

Turku 12.5.2015

 

 

Standard Edition Future?

A while ago I receiving a rumour saying

“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 12.1.0.1 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 12.1.0.2 Standard Edition Database future version is not yet available. Thanks Bertrand!

Inevitable this gap of a missing Standard Edition version 12.1.0.2, and the note on “end of patch of 12.1.0.1”, can cause some headache to some people planning an upgrade project in the Oracle Standard Edition Database Community.

When will the 12.1.0.2 Standard Edition Database version be available?

From Oracle Doc:1905806.1 this can be found: “A separate announcement will be made when we release 12.1.0.2 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 12.1.0.2 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”.

Ann

28th April 2015sefuture

APEX5 on standard edition database

apex5 standard edition database

APEX 5 on a Standard Edition Database

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!

The advice

  • 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.

  1. I started by reading the Application Express Guide for 12c, to see how to uninstall the old preinstalled version of APEX from my 12c CDB.
  2. 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?
  3. I installed and configured the Oracle REST data service by using this guide
  4. 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/

apex5_error_images

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)

Nothing helped.

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.

[oracle@se12t1 ords]$ cat apex.properties
#Sat Apr 25 21:54:37 EEST 2015
apex.images=/home/oracle/apex/images
http.port=8080


Summary (your feedback is welcome)

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.


Take care, stay well and enjoy your week!

Ann

Turku 26th April 2015

dbms_space – Standard Edition proof?

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.

 Please 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.


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.”

“There are neither any explicit information found about this package from Oracle License Document nor the Enterprise Manager License document, saying that this DBMS_SPACE package is part of any management pack”.

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 12.1.0.1 Standard Edition with one PDB
    • control_management_pack_access     string NONE
  • Oracle Linux 6.6
  • VirtualBox
  • These tests are done connected to the CDB. I will later write about the results from the PDB.
  • TC methods:
    • 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?

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.

-Ann

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).


Next step is to check the DBMS_SPACE PL/SQL reference document to see if there might be any license related information in there:

dbms_space_summary

One function in particularly – the OBJECT_GROWTH_TREND, caught my attention:

dbms_space_object_growth

“…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:

dbms_space_dependencies

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

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Baseline before tests: 

banner

options

So after a clean installation (click-click method) there are 17 options in use by default out of 82 options (ver 12.1.0.1).


Test Case 1: DBMS_SPACE.ASA_RECOMMENDATIONS Function

Returns recommendations/findings of segment advisor run automatically by the system or manually invoked by the user

t1

Now the check. Has my database recorded this?

t1_r

Conclusion on  DBMS_SPACE.ASA_RECOMMENDATIONS Function : Nothing was recorded by Oracle Standard Edition.


Test Case 2: DBMS_SPACE.CREATE_INDEX_COST PROCEDURE 

Determines the cost of creating an index on an existing table

t2

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

tc2

Conclusion on  DBMS_SPACE.CREATE_INDEX_COST PROCEDURE : As in TC1, nothing was recorded by Oracle Standard Edition.


Test Case 3: DBMS_SPACE.CREATE_TABLE_COST Procedures

Determines the size of the table given various attributes

Overload 1:

t3

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t3

Conclusion on  DBMS_SPACE. CREATE_TABLE_COST Procedures: As in TC1, and TC2. Nothing was recorded by Oracle Standard Edition.


Test Case 4: DBMS_SPACE.FREE_BLOCKS Procedure

Information about free blocks in a table, index, or cluster. Only work on tablespaces w/o ASSM. With ASSM use dbms_space.space_usage

t4 (1)

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t4

Conclusion on  DBMS_SPACE. FREE_BLOCKS Procedure : As in the previous test cases, nothing was recorded by Oracle Standard Edition.


Test Case 5: DBMS_SPACE.ISDATAFILEDROPPABLE_NAME Procedure

Checks whether datafile is droppable

t5t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

Conclusion on  DBMS_SPACE. ISDATAFILEDROPPABLE_NAME Procedure  : As in the others test cases, nothing was recorded by Oracle Standard Edition.


Test Case 6: DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS Function

Returns the list of segments that are associated with an object

t6t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE. OBJECT_DEPENDENT_SEGMENTS Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.


Test Case 7: DBMS_SPACE.OBJECT_SPACE_USAGE Procedures

Returns the space used, space allocated, and percentage of chained rows in a table. 

t7t

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

<no picture…since the result is the same as previous TC>

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.OBJECT_SPACE_USAGE Function  : Same result as in the previous TC’s. Nothing was recorded by Oracle Standard Edition.


Test Case 8:  DBMS_SPACE.SPACE_USAGE Procedures

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.

Overload 1

t8t

EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t8

SQL> @/home/oracle/scripts/used_options_details
<No Rows Returned>

Conclusion on  DBMS_SPACE.SPACE_USAGE Procedures : As in previous tests. Nothing was recorded by Oracle Standard Edition.


Test Case 9: DBMS_SPACE.UNUSED_SPACE Procedure

Returns information about unused space in a table, index, or cluster 

DECLARE
   segown varchar2(30):='SYS';
   segname varchar2(30):='USERS_T';
   segtype varchar2(30):='TABLE';
   partname varchar2(30);
   totblock number;
   totbytes number;
   unusedbl number;
   unusedby number;
   lu_ef_id number;
   lu_eb_id number;
   lu_block number;
BEGIN
DBMS_SPACE.unused_space(segown, segname, segtype, totblock,
totbytes, unusedbl, unusedby, lu_ef_id, lu_eb_id,
lu_block, partname);
dbms_output.put_line('Total Blocks: ' || TO_CHAR(totblock));
dbms_output.put_line('Total Bytes: ' || TO_CHAR(totbytes));
dbms_output.put_line('Unused Blocks: ' || TO_CHAR(unusedbl));
dbms_output.put_line('Unused Bytess: ' || TO_CHAR(unusedby));
dbms_output.put_line('Last Used Extent File ID: ' ||    TO_CHAR(lu_ef_id));
dbms_output.put_line('Last Used Extent Block ID: ' || TO_CHAR(lu_eb_id));
dbms_output.put_line('Last Used Block: ' || TO_CHAR(lu_block));
END;
Total Blocks: 8
Total Bytes: 65536
Unused Blocks: 3
Unused Bytess: 24576
Last Used Extent File ID: 6
Last Used Extent Block ID: 176
Last Used Block: 5
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t9

SQL> @/home/oracle/scripts/used_options_details

<No Rows Returned>

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?


Test Case 10: DBMS_SPACE.OBJECT_GROWTH_TREND Function

Pipelined table function where each row describes the space usage of the object at a specific point in time

This function on the other hand, should update the use of something that is not ok in our Standard Edition database, since it according to the documentation make use of the AWRF.

Since it’s all about history, let’s insert 150 rows to the USER_T

SQL> exec gen_data('USERS_T',150);
SQL> commit;

And now we run the dbms_space.object_growth_trend function

set linesize 121
col timepoint format a40

SELECT *
FROM TABLE(dbms_space.object_growth_trend('SYS', 'TAB$', 'TABLE'));
11-MAR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
12-MAR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
......(shortened the list)......
 09-APR-15 08.04.34.006835 PM 8996062 12582912 INTERPOLATED 
10-APR-15 08.04.34.006835 PM 8996062 12582912 GOOD 
11-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
12-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
13-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
14-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
15-APR-15 08.04.34.006835 PM 8996062 12582912 PROJECTED 
36 rows selected.
EXEC DBMS_FEATURE_USAGE_INTERNAL.exec_db_usage_sampling(SYSDATE);

Now the check. Has my database recorded this?

SQL> @/home/oracle/scripts/option_usage

t10

What! My Oracle Standard Edition database didn’t record this?

@/home/oracle/scripts/used_options_details

<no rows returned>

More investigations

 select NAME,
 detected_usages USED,
 to_char(first_usage_date,'dd.mm.yyyy hh24:mi:ss') FirstUsage,
 to_char(last_usage_date,'dd.mm.yyyy hh24:mi:ss') LastUsage,
 aux_count AuxCount
 from dba_feature_usage_statistics
 where detected_usages >0;

NAME                 USED FirstUsage
LastUsage           AuxCount
------------------------------------------
Automatic SGA Tuning 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Automatic Undo Management 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Deferred Segment Creation 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1689 
Automatic Maintenance - Optimizer Statistics Gathering 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 
Automatic Maintenance - Space Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Maintenance - SQL Tuning Advisor 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic Segment Space Management (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 2 Automatic SQL Execution Memory 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Character Set 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Locally Managed Tablespaces (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Locally Managed Tablespaces (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Managed Files 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Oracle Multitenant 5 10.04.2015 17:24:26 10.04.2015 20:07:49 1 
Partitioning (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4.2 
Recovery Area 5 10.04.2015 17:24:26 10.04.2015 20:07:49 5033164800 
Logfile Multiplexing 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Server Parameter File 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
Virtual Private Database (VPD) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
LOB 5 10.04.2015 17:24:26 10.04.2015 20:07:49 
SQL Plan Directive 5 10.04.2015 17:24:26 10.04.2015 20:07:49 135 
SecureFiles (user) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 325 
SecureFiles (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 374 
Heat Map 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
Job Scheduler 5 10.04.2015 17:24:26 10.04.2015 20:07:49 4 
Oracle Java Virtual Machine (system) 5 10.04.2015 17:24:26 10.04.2015 20:07:49 32082 
Unified Audit 5 10.04.2015 17:24:26 10.04.2015 20:07:49 0 
26 rows selected.

Conclusion on  DBMS_SPACE. OBJECT_GROWTH_TREND Function  :  Nothing was recorded by Oracle Standard Edition, which to my understanding is strange, since the PL/SQL reference guide  said:

“…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”

What do YOU think about theses results?

27th May 2015: A confirmation received from an Oracle License Expert was added to this post. Scroll up to the red text, if interested.

Have a nice Sunday! Take care, stay well, and see you soon again!

-Ann

Turku 12th April, 2015