-- Oracle Compression Offerings --

I recently was approached by a consultant that was confused about the different compression offerings from Oracle, so I decided to write a high-level short summary to provide some clarity.

Oracle offers 3 types of Compression:

A) Basic Table and Index Compression (Good for Data Warehouses): This feature has many years in the market (at least 10 (Oracle 9i)) and it is part of Oracle Enterprise Edition (no additional license cost for you), when Oracle designed this feature the goal as the name of the feature says was to compress data to save space, however, what they found is that by compressing the data, and as a natural consequence you reduced the I/O, and if you add to it the fact that their compression / decompression algorithm is very light and simple (just consume negligent additional CPU amounts) the query performance often increases.

A.1) What are the disadvantages of it:  With this type of compression Oracle only compresses the data when you are inserting it into the table, and you need to insert it using “direct path insert”. The other disadvantage is that during updates Oracle needs to decompress the records and after updating them it does not compress it again, therefore you will end with a mix of compressed and uncompressed records. This type of compression is not good for OLTP databases, but ideal for Data Warehouses where you mostly do read-only transactions and you populate the data through an ETL tool doing the direct path insert.

A.2) What is direct path insert and what does this means: Direct path insert is a special mode if the insert where Oracle while inserting data will ignore the available free space on the table, in other words, it will not reuse the available free space on the table, it will do the insert after the existing data in the table, therefore wasting disk space, but increasing performance because it bypasses the buffer cache and writes directly to the data files.

A.3) What queries will see a performance advantage: Those queries that requires fetching millions of records and do aggregations where I/O is the bottleneck over tables that have a high compression ratio,  tables with small numbers of rows or small size will see adverse performance impact due to the CPU overhead.

A.4) What compression ratio can be achieved: It depends on the nature of the data, tables with duplicated data will achieve higher compression while tables with almost no data duplication will achieve less to none.

B) Oracle Advanced Compression (Available 11g and above) (a.k.a OLTP Compression): This is a licensed feature that can be implemented on top of Oracle Enterprise Edition only, it works similar to Basic table compression but it allows data to be compressed during all types of DML operations such as Insert or Updates, the compression algorithm is enhanced therefore reducing the overhead of write operations. The other advantage is that Oracle is able to read the rows without uncompressing them, therefore, there is almost no performance degradation in accessing compressed data, in fact in many cases you may see performance improvement due to the reduced I/O.


C) Hybrid Columnar compression (HCC): This is a feature available on Oracle Exadata, and recent rumors say that is available on the ODA X5-2. HCC uses an orchestration of Hardware and Software capabilities, it achieves higher compression rates than the other 2 methods, it has  4 different types of setting (Query high / Query Low/ Archive high / Archive low) that tells Oracle how you want  to balance the compression vs. performance impact; in other words the more compression the higher the CPU cost involved in performing operations, the lower compression the less CPU cost . Different from the other 2 methods this one stores the data in columnar format and that is the secret sauce for achieving higher compression, the disadvantage is that you could have adverse performance effects if you query more than one column at a time, although this is often offset by the use of the Exadata Smart scan feature. You still need to do bulk load of the information to maximize the compression ratio. Oracle includes an advisor that tells you what kind of compression you could expect to achieve. 

-- How to quickly change the AWR Interval and how to create one on demand --

Check the current defaults.

set linesize 100

col snap_interval format a20

col retention format a20

col topnsql format a20

select * from dba_hist_wr_control;

DBID SNAP_INTERVAL        RETENTION            TOPNSQL            

---------- -------------------- -------------------- --------------------

3610569430 0 0:60:0.0           7 0:0:0.0            DEFAULT              

Below we are changing the interval from 1 hour (Default) to 30 min.

execute dbms_workload_repository.modify_snapshot_settings ( interval => 30);

anonymous block completed

Check that the change was applied.

set linesize 100

col snap_interval format a20

col retention format a20

col topnsql format a20

select * from dba_hist_wr_control;

DBID SNAP_INTERVAL        RETENTION            TOPNSQL            

---------- -------------------- -------------------- --------------------

3610569430 0 0:30:0.0           7 0:0:0.0            DEFAULT    

Create AWR on demand

When debugging SQLs you may want to get an AWR snapshot before and after you run an specific SQL.

exec dbms_workload_repository.create_snapshot();

select * from xxxx /* Your query */


exec dbms_workload_repository.create_snapshot();

-- Oracle Openworld 2014 --

During my visit to the 2014 Oracle Openworld I had the opportunity to talk with some of Oracle's customers and with different vendors who are leaders in their industries.

This year there is a big focus on the cloud, which is not surprise. Companies are becoming more aggressive in adopting a hybrid between cloud and traditional information systems on premises. Oracle is trying to determine how to support 20 year old applications which are no longer supported by the original vendors, and how to avoid for those apps to age out placing customers on a bad spot in the future. A lot of companies are embracing the SaS schema, especially companies with global operations because that allows them standardization and also resolves the problem of keeping up with application upgrades.

The other emphasis is to provide real time data to the executives, including information from social media. The goal here is to enable executives to make decisions on the spot rather than weeks or months after obtaining the data when they finally get the results from their analytics. Companies believe that there is traditionally a big gap between the time they obtain the information and the time they react to it. By the time they shift gears in their business they missed some or all of the momentum. In order to achieve rapid response time, there are dedicated people constantly studying customer science and interpreting the volume and complexity of the data. This allows them to engage the customer digitally in real time before the analytics age out. 

Many companies find they are experiencing the same types of problems. Some common trends are: reducing the number of legacy applications; moving into a single CRM and ERP systems; achieving globalization and standardization by leveraging Cloud-like technologies; and simplification and modularization of infrastructure technology to enable the use of common computing power.

There has been a shift where companies are not longer organizing and following sales leads by territory and region, but by customers that are most likely to buy, with the implementation of digital technology, it is not longer that relevant to have a presence on a specific territory or  region, and companies can save in OPEX by making strategic use of their sales force. 

On the technical side Oracle is promoting the consolidation trend by recommending to implement multi tenant databases, this is specially good and true for environments such as development and test sharing the same hardware, but maintaining their inter-independence. On the analytics side once we get to Oracle 12c there is a paid feature called in memory database  which allows you non only to store tables in memory, but to organize them in a columnar fashion, which makes your analytics performance many times faster. 

Talking to some of the vendors they tell me nobody is doing money with Big Data, companies simple do not have interest on it yet, but they see a lot of potential and started to see tangible sales on SaS and other Cloud like technologies, the big concern here is still security and the big challenge is extracting your data if you decide to no longer be on a hosted Cloud service. 

-- Resetting a table or Index high water mark --

We recently got confronted in our Data Warehouse database with an issue where one of the database files was a "bigfile" tablespace and it was located on a drive that can't be expanded. Yes, I know that is the rule No.1 about using "bigfile" tablespaces, if you are going to have storage limitations do not use tablespaces. In my defense I inherited the environment, someone else designed it :) . 

Our application administrator told us that he deleted a humongous amount of space from the biggest table, however you will not see that space back in the tablespace as it is still being help as empty blocks on the table. Out grid control critical threshold of 97% was triggered.   

Although the application administrator told us not to worry about it, because the table will reuse the empty space and won't try to consume more space in the tablespace, I didn't feel well just knowing that the next time that we get an issue I won't get an alert, I will get a call from the application administrator telling me that the system in halt because of the space. Since this is a 1.7TB database, it will take long time for me to move that tablespace to somewhere else with more space.

So what I decided to do was to reset the high water mark of those tables and indexes so it returns the space to the tablespace and I get my Grid control alert clear again.

This is the way you accomplish that.

SQL> alter table enable row movement;
SQL > alter table shrink space;

If you want to reset the high water mark for all dependent objects yo do this:

SQL > alter table shrink space cascade;

If you just want to compact the table without resetting the high ware mark you do

SQL > alter table shrink space compact;

Please note that the operations above can be done online with minimum impact (row level locking for a brief moment). 

For indexed you do


SQL > alter index shrink space;

Enjoy. 

-- Challenges of Project Managing & Agile Methodologies --

Although these two subjects could be seen as two different topics (and they are),  they are intimate related and therefore I decided to cover both of them on the same paper. In my personal opinion Agile surges as a solution to the drastic increase of many Information System Development (ISD) projects failures.  However rather that analyzing the failures and correcting them, we decided to re-invent the wheel.

Let me explain, for a project to be successful there are few elements that are needed, you need the resources (people), they need to have the technical knowledge adequate to be able to develop the solution, you need a problem or requirements (what is needed, what is the problem that I am trying to solve), you need a solution or solutions (how I am going to resolve of mitigate the issue),  and you need a plan to get there.

 I learned earlier in my career that the most important elements on Developing information system are

1.- You need to have a clear understanding of what is that you are trying to solve.
2.- You need to understand how the proposed solution will solve the problem.
3.- You need to have a fix scope of what is that you are going to do, and make the business sign on it.

It is often that software engineers don't understand the problem (many times because the business do not understand it either, and provides very vague description and requirements). I find often that the issue here is the technological gap in communication. Depending who is the person from the business that you are getting the requirements from that person will be able to provide the exact requirements needed or will just give you a vague idea, which will likely translate in a poor design that does not meet the business requirements.

Many times business owners wants to set deadlines that are just not feasible, and developers need to compromise in the quality or their programs in order to meet the deadline, which end creating production issues. Another typical issue is that due to the constants business changes the scope of the project is changing all the time. So basically if you spend time understanding the requirements, you have solid knowledge of the technology that you will use for the development, you have a clear scope of the project,  your deadlines are feasible and you have a project manager supporting you, then you have a project that have a very high percentage of success rate.

Another challenge that was introduced in the last 10 years is that a big portion of the development projects are outsourced overseas, with this the communication issue that I explained above gets exponentially worse. The other problem that is difficult to solve is the constant changing requirements, and that is where Agile plays a role, the idea behind agile is to create a virtual team that will be able to address the constant changes on the fly, what Agile provides as well is a series of micro managing tools that ensure the project is on track before is too late.

Agile in theory looks good, however I have not seeing a lot of adoption on it, and the degree of success of Agile projects vs non-agile projects is still to be proven. At this point you may be asking yourself, if we do not use Agile how else can we close this gap, what other methodology can we use, to what I will answer that it depends of each business and organization needs, there is not a single solution that fixes all the problems.

Regarding to what would I propose for a project where the scope is constantly changing, my answer would be to have additional development groups that will start new development at the tail ending of the other, in that way you can take in production a complete project and you are already working on a new version.

-- The Future of Enterprise Applications --

Like any other technology "Enterprise Applications" surged from a need, initially companies were trying to resolve very specific problems.  Initially there were MRP. (Material Requirement Planning) systems (1970) that intended to aid in accomplish tasks such as schedule production. Later on that need evolved in good part because companies saw the capabilities of the technology and how it turned over time more efficient and more cost effective, the second wave of enterprise systems is constituted by what is call MRP. II  (Manufacturing Resource Planning) Systems (1980), these application not only focused exclusively in production data, but exploded the relationships with Marketing and Finance data between others. Later in 1990 these systems evolved once more into what is noways called ERP. or Enterprise Resource Planning.

As you can imagine by the word Enterprise the idea behind is to integrate in an orchestrated way all business functions with the goal of improving operations, some key features that characterize these applications are.

• Global Scale.
• Focus in Collaboration and Product Strategy.
• Based on a particular business model.
• Aims to adapt and correct based on the markets conditions.
• Leverage business intelligence  and analytic techniques.
• Integrated Information Exchange.

So what is next in the ERP arena?. Well to start with we are in the era of mobility (access your data any time any where from any device).  Mobility will not only add  convenience to Enterprise Systems but also will add a new level of real time data. But what do we need for that to happen, well there is an implicit cost in all these systems, so how do you manage this cost?.

The answer is to use SAS. (Software as a Service) which is usually stored in the cloud where you pay a fraction of the cost or (pay per use). So why this has not taken off?

Below are some of the few reasons:

•    Hesitant due to security.     
•    Technology not being totally there.       
•    Scalability.      
•     Interdependence, could  not exist without supporting range of other services.

What can you do to mitigate the risks

•  You need to build fault tolerance infrastructure or hire a third party vendor that offers this.
•  You need to plan for it instead of stay away from it..

-- Oracle RDBMS 12c was formaly released today --

The new version of Oracle has over 500 new features, some of those features indicate to me that in order to achieve them they did a massive re-architecture. The one that called my attention the most was pluggable database, apparently you create a database defined as a container, and you can add up to 255 pluggable databases to the same container, now this is the interesting part, in the past 1 instance belongs to one and only one database, however a database can have many instances (RAC), well now that instance that is the container is shared among all the pluggable databases on it. All the databases in the same container inherit the features of the container database (RAC, DG, etc).

Tom Kyte presented his favorite 12 features of 12C, he mentioned that it was a challenge because there were 500 new features, which he narrowed to 80 and then to 12 :). Here they are.

1. Default to Column
2. Even better PL/SQL
3. Increase of size limits for certain data types.
4. Easy Top-N and pagination queries
5. Row pattern matching (good for analysis of data)
6. Lots of partitioning improvements (move partitions online)
7. Adaptive execution plan (Your execution plan changes on the fly)
8. Enhanced Statistics (New histograms).
9. Temporary undo (Temporary tables store undo on temporary tablespace)
10. New Data Optimization capabilities (Heat map)
11. Application continuity (this is an extension of TAF), Transaction guard (this ensures web transaction do not get duplicated by error).
12. Pluggable Databases.

You can get more details about this at http://otn.oracle.com