Category Archives: Enterprise Edition

Standard Edition 2 – DBA’s Toolbox

 SE2 Toolbox

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:

cd $HOME/sqlcl/lib
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?

SQLDeveloper – a must in SE/SE2

This tool has grown in maturity since the first version, and I think it’s the first tool to add to your SE/SE2 toolbox. It is free and will bring a nice value to your DBA job.

Check this link to get started.

SQLDeveloper pitfall in SE/SE2:

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:

(c)Ann Sjökvist

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:

(c) Ann Sjökvist

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

IMG_0244

After a long wait amongst people in the Standard Edition Community, Oracle Corporation finally released the 12.1.0.2 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.

Take care, stay well and let’s enjoy the Autumn.

-Ann
Helsinki, 24st September 2015

12.1.0.2 Standard Edition R2 is planned for Q3 CY2015

(c) Ann Sjökvist
Finland 2:30 am – Summer The Best Time to Visit

 

3rd of July 2015 SE Day

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

Doc2027072.1 Disappeared.

Replaced by updates in Doc1905806.1 and Doc742060.1

(c) Ann Sjökvist


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 12.1.0.2. Note 2027072.1

During last months the Standard Edition Community have often asked themselves:

“When will Standard Edition Database release 2 (12.1.0.2) arrive?”

Yesterday I received a question from Laurent Leturgez , asking if I had any information about SE12.1.0.2:

(c)Ann Sjökvist

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:

(c)AnnSjökvist

The Standard Edition Database version 12.1.0.2 (Rel2) is planned to arrive in Q3 CY2015! And 12.1.0.1 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.

(c) Ann Sjökvist

Please check out Laurent’s blog

(c)Ann Sjökvist

(c) Ann Sjökvist

Please check out Stefan’s blog

(c) Ann Sjökvist

(c)Ann Sjökvist

(c) Ann Sjökvist

Please read Ludovico’s thought about SE2 from his blog

(c) Ann Sjökvist

Please read Jan’s own thoughts about Standard Edition Rel2. Jan thanks for the mention on your blog.

(c) Ann Sjökvist

Please check out Lothar’s blog.

(c) Ann Sjökvist

(c) Ann Sjökvist

(c) Ann Sjökvist

Please check out Alberto’s blog.

(c) Ann Sjökvist

Please check out Franck’s blog.

(c) Ann Sjökvist

(c) Ann Sjökvist

Please check out Dominic’s blog. Thanks Dominic for your help! I am sure everybody in Oracle Standard Edition Community really appreciate your helping hand!

(c) Ann Sjökvist

As Ludovico mentioned on one of his tweets, we absolutely need the twitter buzz around Standard Edition, but we also know the effect of “bad apples in the basket”.

So would it be more beneficial for Standard Edition Community to give the amazing team working on Oracle Standard Edition a break? I am sure Dominic will provide us with more facts when possible.

I liked the initiative from Franck asking for information on Standard Edition REL2 New Features. We need more initiative like this. Thanks Franck for supporting the Standard Edition Community!


Oracle Note 2027072.1 on SE2

Please read Note 2027072.1 for more information. The  License Agreement policy will change between Standard Edition / Standard Edition One 12.1.0.1 and 12.1.0.2. But once again. The expected release:

Availability
The release of Oracle Database 12.1.0.2 SE2 is planned for Q3 CY2015

And the extended support for 12.1.0.1 Standard Edition is 6 months.


Take care, stay well, and let’s enjoy this beautiful and warm weather! Yes finally even Scandinavia have got warm weather.

-Ann

Turku 4th of July 2015 – Happy Independence Day to you in U.S!

(c)http://www.4th-of-july-2015.com/wp-content/uploads/2015/06/4th-of-july-independence-day-2.jpg
http://www.4th-of-july-2015.com/wp-content/uploads/2015/06/4th-of-july-independence-day-2.jpg

Network Encryption in Standard Edition

lock-icon

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?

updated: 31.7.2017

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:Oracle Corp.(c) + my added SE notes

(pic:Oracle Corp (c) + My SE notes)

Take care, stay well, and enjoy the weekend!

-Ann

Turku 3rd July 2015

ADO in Oracle Standard Edition?

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

compression_no

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

compress_9i

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:

o91_license

compress_9i

EndOfUpdate 22.6.2015


Original post:

Explanation for a Business Management’s type of person.

The Whitepaper on ADO feature tells us:

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 

(c) Ann Sjökvist
(c) Ann Sjökvist

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:

ado_license

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?

  1. You should enable Heat Map (alter system set heat_map = on scope=both)(manually performed)
  2. 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)
  3. You create ADO Policy on table (manually)
  4. ADO Policy get evaluated
  5. ADO actions will be executed

A beautiful feature but we cannot use it in Standard Edition.


switch 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      Sandbox and Testcase

  • Oracle Standard Edition version 12.
  • Database Patch Set Update : 12.1.0.1.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;
 ENVIRONMENT
 ---------------------
 oracle
 cdbse
 CDB$ROOT
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!


ape
By Ian Leonard

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 
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf

 

--CTAS with basic compression enabled
create table t_se1 compress basic
as
select * from [some_table] where [column] = [value];
--Direct path insert into empty table defined as compressed
create table t_se1 compress basic
as
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.

(c) Ann Sjökvist 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,
to_char(begin_interval_time,'mm/dd') c1, 
         sum(physical_reads_total) reads, 
         sum(physical_writes_total) writes 
        FROM 
          dba_hist_seg_stat s, 
          dba_hist_seg_stat_obj o, 
          dba_hist_snapshot sn 
        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
OWNER OBJECT_NAME OBJECT_TYP  BCD C1 READS WRITES
------------------------------------------------- 
SEHC SE_INDB_ARCH TABLE       0 06/21    0      8
SEHC SE_INDB_ARCH TABLE      16 06/21    0      8
 

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.

online

online_df

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.


Conclusion

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.


Take care, stay well, and enjoy the summer!

-Ann

21st June 2015

Application Continuity Standard Edition Proof?

12capplicationcontinuityillustration

(c)Oracle WhitePaper 12c


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.

Thanks Markus Michalewicz, Franck Pachot, Ludovico Caldara, and Philippe Fierens for your intervention, and for giving us the facts on this one!

ac_notinSE

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:

ac12c_wp


Oracle JDBC FAQ

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)

general_jdbc

More information:

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:

jdbc_license


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

applicationContinuty_errataMaybe

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.

ac_notinSE

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…

-Ann

Turku 7th June 2015

 

Row Pattern Match in Oracle 12c Standard Edition?

Row Pattern Match – Standard Edition proof


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
  5    MEASURES
  6       STRT.tstamp AS start_tstamp,
  7       LAST(DOWN.tstamp) AS 
          bottom_tstamp,
  8       LAST(UP.tstamp) AS end_tstamp
  9    ONE ROW PER MATCH
 10    AFTER MATCH SKIP TO LAST UP
 11    PATTERN (STRT DOWN+ UP+)
 12    DEFINE
 13      DOWN AS 
         DOWN.price < PREV(DOWN.price),
 14      UP AS UP.price > PREV(UP.price)
 15  ) MR
 16   ORDER BY MR.symbol, 
               MR.start_tstamp;

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.

  1. 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.
  2. Oracle OTN site on Oracle Advanced Analytics 

o_advanced_analyze

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


Summary

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!

– Ann

Turku 1st June 2015

Engage Big Data in Standard Edition

bigdata_5v  No need to hesitate


Time to Engage Big Data in Standard Edition

Last week I asked “Big Data in Standard Edition Seriously” and after one week of studying at EMC Education Centre, I truly believe that smaller companies should start to embrace the opportunities that this technology can bring.

When asking different people the question “What is  Big Data?“,  many different explanations will be provided. But the one I received from EMC Education Centre, opened my mindset about the Big Data concept:

Big Data is really about ANY attribute that challenges the constraints of a system capability or business need

It’s not only about size – examples

spreadsheetI have a 50 MB spreadsheet document that I cannot share it with my colleague via e-mail or a shared storage due to limitations of space – I just bumped into the “challenge of a system capability”

marketingMy CMO assistant made a 1,1TB marketing ad, that cannot be displayed remotely in real time to the board of Executives – once again, I just bumped into the “challenge of a system capability”

timeglassMy CMO came up with a new service, and a new 1PB marketing video need to be edited and rendered, but the Editor’s job is interrupted due to lack of resources on his computer – once again, I just bumped into the “challenge of a system capability”

So to my understanding, at all three examples we are facing “Big Data”.


One other thing that EMC Education pointed out to me, is :

“How Can We Get Closer to Big Data & Start Taking Advantage of it?”

That question I think, is not an exclusive property for big enterprises , but also applicable to smaller companies who is running their applications on an Oracle Standard Edition Database environment.

Since I am a novice on Big Data, and in the beginning of my learning path, I asked one of the instructors Barry Heller about his thoughts on how a small company could start to use their “Big Data”, to grow their understanding and sees the opportunities of the insight such analyse will bring:

  • what new services should they implement?
  • how to grow their business?
  • do we need to change our processes or create new ones?

Advices from the expert Barry Heller (c)

  • identify one pain point or opportunity within a company and produce a quick win.
  • Possible projects may be to build a
    • recommender system,
    • reduce customer churn,
    • identify upselling
    • cross-selling opportunities
  • For statistical analyses
    • the R programming language is a great place to start.  
    • the Apache Hadoop ecoystem is great place to start for
      • storing
      • processing
      • analysing Big Data.  

Beyond the technology, another important aspect is how the project will be staffed; small companies may not have the expertise in-house.

Thanks Barry for guidance, and to me, this sounds like an opportunity to all companies running an Oracle Standard Edition Database; Big Data is available to all business – it’s not about the size.

So why not start thinking about transforming your data to the next level, and look into new business options/possibilities?

Even as a novice, I must say THAT would be a great challenge to have a Standard Edition Database Environment, and start a Big Data project, to find new opportunities of any kind to the company!


 

Take care, stay well and enjoy this Monday!

– Ann

Turku 18th May 2015

 

 

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

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