Big Year, Big Data, Big Databases

In birding, a Big Year is seeing or hearing as many different species of birds as possible in a calendar year. In databases, Big Data is something that cannot be defined that clearly.

You can read and try to understand the definition of Wikipedia. Up to you. What I like most is what Christo Kutrovsky says: “Big Data is actually a license issue. It is partially a license issue – Oracle Database is expensive and MySQL isn’t good at data warehouse stuff. It is partially a storage and network issue of scaling large volumes of data, locality of data is becoming more critical.”

Gwen Shapira wrote a very interesting article entitled Oracle Database or Hadoop?. What she (generally) says is not only interesting and joy to read but most of all true: whenever I tell an experienced Oracle DBA about Hadoop and what companies are doing with it, the immediate response is “But I can do this in Oracle”.

And she goes on: “Just because it is possible to do something, doesn’t mean you should.” Yes, and the reason I would say, is that databases are not always in good shape. And why so, one might ask. This is the reason of this blog post.

Reason #1: Databases are very seldom reorganized. It is a very hard task to explain to non-DBAs why this is necessary. Only core/system DBAs comprehend the real benefits of that reorganization. It it an extremely important task that is so often neglected.

Reason #2: Old data is never removed or archived. I dare say that most of the data (more than 80%) in all databases I have seen is rarely touched. Call is junk data, big data, whatever you like.

Reason #3: Databases are not upgraded as new databases. Meaning, I seldom see that a new 11g database is created and the database is imported from the old database. Often migration scripts are being run (easy to do so and minimal downtime) and what you are left with is a fake 11g database. It almost has the Data Dictionary of 11g but modified with scripts. Such databases underperform in most cases.

Reason #4: Patching the databases to the latest possible level. How often I am asked this question: “But Julian, how can one digit at the end matter?”. Well, it matters a lot in most cases.

Reason #5: Automation of several critical tasks is not used at all. One very good example is AST (Automatic SQL Tuning).

So, what is the way ahead if ignoring all of the above? You can decide after all if the following from Future DBA? 5 reasons to learn NOSQL are valid ones to learn NoSQL:

1. Big Data and Scaling
2. Crashing servers, not a problem.
3. Changes aren’t as stressful
4. Be NoSQL Pioneer
5. Work less

Do you believe in Tall Stories? Big Data, Big Problems, Little Talent!

Is Big Data just a trend or is it just another way to solve the big problems we have created over time? Should we concentrate our efforts on “smart use of data” or on “use of big data”?

“Depending on whom you ask, ‘big data’ is either:

A. Bullshit (Brad Feld)
B. No substitute for judgment (David Friend)
C. The marriage of corporate data with external data (Chris Lynch)
D. Data that’s growing faster than Moore’s law (Richard Dale).

After this week, my answer would be E, all of the above.”–Gregory T. Huang, Xconomy

Try the following: type in Google “Big data is” and see if you get what I did:

What happened to machine learning, computational intelligence and data mining?

It is interesting to note that the database conference with highest H-Index is the one on Very Large Databases.

Exadata’s Total Cost of Ownership

According to Elbert Hubbard “One machine can do the work of fifty ordinary men. No machine can do the work of one extraordinary man”.

Of course, neither Exadata nor Bloom filters existed when the Bloomington born philosopher made the above statement.

Forbes magazine published an interesting article this month: Oracle’s Secret Sauce: Why Exadata Is Rocking the Tech Industry.

The article says: “The new approach is embodied in a technology strategy pioneered by Oracle and recently endorsed/followed by IBM (although IBM’s effort to date is rather modest): building richly integrated and fully optimized systems from the ground up, with hardware and software expressly created to work together to deliver maximum performance.”

And as you might guess from the image above, this time I am not only after the technical benefits and advantages of Exadata. I would like to clarify what they bring to business. And see how Oracle Exadata compares to IBM P-Series.

The FactPoint Group created a 30 page cost comparison report for business decision makers: Oracle Exadata Database Machine vs. IBM Power Systems.

In brief, the results of the report are:

• IBM 3 year TCO is 31% higher than Oracle.
• Exadata can be deployed more quickly and easily requiring 59% fewer man-hours than a traditional IBM Power Systems solution.
• Exadata requires 40% fewer sysadmin hours to maintain and operate annually, including quicker support calls because of less finger-pointing and faster service with a single vendor.
• Exadata delivers dramatically higher performance typically up to 12x improvement, as described by customers, over their prior solution.
• Will become even easier to operate over time as users become more proficient and organize around the benefits of integrated infrastructure.
• Supplies a highly available, highly scalable and robust solution that results in reserve capacity that make Exadata easier for IT to operate because IT administrators can manage proactively, not reactively.

Overall, Exadata operations and maintenance keep IT administrators from “living on the edge.” And it’s pre-engineered for long-term growth.

Check Kerry Osborne’s Oracle Blog for more details about the Exadata vs. IBM P-Series comparison.

I personally think that the benefits of Exadata are even much bigger granted the system is properly configured which I see is not always the case but as I said I will not comment on technical issues this time.

But after all, this is a DBA blog, so this part of the research might be of interest for most DBAs:

“For this emerging Database Machine Administrator (DMA) job category, IT employees are cross-trained to handle tasks currently undertaken by admin specialists in hardware, operating systems, network, applications or storage. IT managers who pursue this adaptive path likely will gain operational efficiencies for managing packaged solutions, although it may take several years as IT administrators are trained in new competencies.

The emergence of the DMA also may help restructure IT departments into more efficient operations, but the full benefits of this development cannot be fully realized until most older systems that demand a stove-piped IT organization are decommissioned and IT organizations adapt. At that time, IT operations managers may be able to reduce headcount. In time, packaged solutions should involve not only fewer workers but also fewer IT groups, which should reduce costs; in the meantime IT will be able to do more without adding headcount.”

That is very important! Let me quote here Paul Vallee, who in a recent discussion predicted that in the near future organizations will need few but very skillful DBAs, an opinion I 100% agree with!

“This change in job roles is not necessarily comfortable for everyone in IT because Exadata marginalizes various system administrators as it empowers the DBA: “The DBAs are doing more hardware tasks and diagnostics because most of the Exadata stuff is geared around database commands, not hardware commands or operating system commands. The gearheads have designed Exadata from the DBA’s perspective—when you look at the sys admin portion, it’s all written by a DBA, not by a Sys Admin,” lamented a System Administrator at a Business Services Co.

Other System Administrators have expressed similar sentiments as many of their traditional responsibilities shift towards the DBA—the source of the much of the operational savings we have identified.”

More on the DMA subject from Arup Nanda: Who Manages the Exadata Machine?

For all DBAs: here is an excellent book on Exadata: Expert Oracle Exadata, by Kerry Osborne, Randy Johnson and Tanel Põder.

Tuning Exadata with parallel profiles and SPM baselines

Harold Geneen used to say: “It is much more difficult to measure nonperformance than performance”.

Without automation database management becomes increasingly difficult. An Exadata database can strongly benefit performance-wise from fully enabled Automatic SQL Tuning:

BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter(
    task_name => 'SYS_AUTO_SQL_TUNING_TASK',
    parameter => 'ACCEPT_SQL_PROFILES',
    value     => 'TRUE');
END;
/

I will explain why.

The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

The difference between SQL Profiles and SQL Plan Management Baselines is very well explained by Maxym Kharchenko and Maria Colgan.

The process of automatic evolution of SQL Plan Baselines is described with a perfect example by Christian Antognini.

Converting baselines into profiles is almost straightforward but not simple, Sumit Bhatia explains how.

Now, for complex and long running SQL statements in Exadata, it is quite important to reduce the amount of data being transferred from the storage nodes to the database and to have the right degree of parallelism.

The former is achieved very much by smart scans which take place only when there are full scans or direct path reads. The blocks are not cached but they are returned to the PGA. The latter is even trickier. How in fact to determine the right degree of parallelism for an SQL statement?

Most often parallel_degree_policy is set to manual (default) and parallel_max_servers is sort of guessed. In my opinion, the right way is to use Oracle’s Auto DOP solution by enabling parallel_degree_policy to a non-default value. Let me quote Gwen Shapira:

There are two levels to Auto DOP:
– Limited: when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources
– Auto: Oracle will decide on degree of parallelism for every query. In addition two new features are enabled: parallel statement queuing and in-memory parallel execution.

Even doing all that, because of Oracle’s unpredictable behavior at times, some SQL statements might not get the right number of parallel slaves or might not go for smart scans. For such statements creating an SPM baseline is a good thing to start from. Then, during the Automatic SQL Tuning maintenance window, the SQL Tuning Advisor might notice that there is a non-accepted plan which is better than the accepted one. The recommended SQL profile will simple do nothing else than to evolve the SPM baseline execution plan into an accepted plan.

All that happens automatically due to the fact that we have advised Oracle to automatically accept SQL Profiles.

But there is more to that: how to get a good degree of parallelism for the SQL statement? Oracle will automatically do so if needed by creating a parallel SQL Profile. Note that there are two types of profiles:

– REGULAR_PROFILE – profile without a change to parallel execution
– PX_PROFILE – regular profile with a change to parallel execution

Here is an example:

Automatically accepted parallel profiles can really boost the Exadata performance. But as PX_Profiles can consume also more resources than expected, it is important to have Instance Caging enabled.

The most important step for configuring instance caging is to determine the value of CPU_COUNT for each database instance and the sum of CPU_COUNTs across the server. There are two approaches:

– Partitioned approach for mission-critical pools sum(CPU_COUNT) < 75% x Total CPUs
– Over-subscribed approach for non-critical pools sum (CPU_COUNT) <= up to 3 x Total CPUs

Second step for instance caging: ensure resource_manager_plan is enabled.

Bottom line: tuning an Exadata database is not exactly the same things as tuning a traditional database. Actual tuning of an Exadata database is in fact a lot easier once understood.

And a lot of that tuning work can be really automated!

Curb Your Enthusiasm: Database Virualization

Larry David says: “You write about what you know”.

RAC is one of the most controversial topics among database experts.

An extremely interesting article, called Database Virtualisation: The End of Oracle RAC? was published this month (September 2012) on FlashDBA.com.

I recommend it to every Database Architect, DBA and Developer! Along with the comments after it. Plus its links to related papers and blogs.

Another interesting post on the RAC issue, entitled To RAC or not to RAC and its sequel To RAC or not to RAC (reprise part 2) is worth reading as well.

To make it simple, let me quote techtarget.com:

“The biggest disadvantage is that you are adding more complexity to your database architecture. With more complexity comes a higher cost in maintaining and administering the database along with a higher chance that something will go wrong.

The second biggest disadvantage is the cost associated with RAC. Oracle is touting RAC on Linux as a way to acheive cost savings over large Unix servers. With RAC, the costs shift from hardware to software as you need additional Oracle license fees. The big question is will this shifting of costs result in any cost savings. In some cases, yes, and in other cases, no.”

Which would be the third big disadvantage of RAC? I say the bugs! Or let me put it more mildly: RAC just develops random features. And hunting errors in RAC is complex, right? On top of ORA-600, we now have even ORA-700.

Let me offer you some quotes from the RAC debate on FlashDBA.com:

“Then there are the younger DBAs looking to gain more experience, who may say that RAC is a great thing. Secretly that might not necessarily be true but they want the experience.”

“We also see that there are “no application changes necessary”. I have serious doubts about that last statement, as it appears to contradict evidence from countless independent Oracle experts.”

“Complexity is the enemy of high availability – and RAC, no matter how you look at it, adds complexity over a single-instance implementation of Oracle.”

“At no time do I ever remember visiting a customer who had implemented the various Transparent Application Failover (TAF) policies and Fast Application Notification (FAN) mechanisms.”

So, is Database Virtualization the answer?

I think that database virtualization is a concept that has been misunderstood and most of all wrongly defined by many. According to the Oxford Dictionarries, in Computing, virtual means something which is not physically existing as such but made by software to appear to do so.

Decide for yourself what is then a virtual database. At least, it is not a database built in a virtual server! I did discuss that at the Oracle ACE Director Product Briefing at Oracle headquarters this week with world’s top database experts and what can I say: the topic is highly controversial.

Let us go to the Oracle Database Documentation Library and search for virtualization. Here is the result:

And you will get the same result for virtualisation (with “s”).

So, what do we get? Not much.But let’s continue reading:

Database Virtualization Part 2 – Flash Makes The Difference

The Do’s And Don’ts Of Virtualizing Database Servers

Disadvantages of Virtualization, What’s Your Opinion?

Virtualization Best Practices

Let us wait for next Oracle’s database release and see what it will offer us.

Bloom filters for DBAs

Lady Bird Johnson said: Where flowers bloom so does hope. I would re-phrase: Where Bloom filters bloom, so does database performance.

A Bloom filter is a probabilistic algorithm for doing existence tests in less memory than a full list of keys would require. In other words, a Bloom filter is a method for representing a set of n elements (also called keys) to support membership queries. John Rose wrote a very detailed blog article about Bloom filters. Another good one is by Christian Antognini entitled simply Bloom Filters.

These underscore parameters are all related to Bloom filters:

The Oracle database makes use of Bloom filters in the following 4 situations:

– To reduce data communication between slave processes in parallel joins: mostly in RAC
– To implement join-filter pruning: in partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list
– To support result caches: when you run a query, Oracle will first see if the results of that query have already been computed and cached by some session or user, and if so, it will retrieve the answer from the server result cache instead of gathering all of the database blocks
– To filter members in different cells in Exadata: Exadata performs joins between large tables and small lookup tables, a very common scenario for data warehouses with star schemas. This is implemented using Bloom filters as to determine whether a row is a member of the desired result set.

You can identify a bloom pruning in a plan when you see :BF0000 in the Pstart and Pstop columns of the execution plan and PART JOIN FILTER CREATE in the operations column:

Two hints can be used: px_join_filter and no_px_join_filter; and there are a couple of views for monitoring Bloom filters:

– v$sql_join_filter: information about Bloom filters; number of rows filtered out and probed by Bloom filters
– v$pq_tqstat: check reduced communication due to usage of Bloom filters

There are two undocumented Oracle functions: SYS_OP_BLOOM_FILTER and SYS_OP_BLOOM_FILTER_LIST:

In 11gR2 execution plans you may see :BF0000 replaced by KEY(AP). This is due to new functionality that performs partition pruning based on AND (= And Pruning) multiple pruning descriptors.

In 11.2.0.2 and later a SQL query on a table might not deliver any results. Unfortunately, it might not deliver any error message either. The solution is to disable Bloom filters:

alter system set “_bloom_filter_enabled” = false scope=both;

Restrictions:

– For Exadata customers, I would suggest to set a low degree of parallelism on small tables and indexes in order to take advantage of Bloom filters.
– Bloom filters are not supported in partition-wise joins. This is documented as Bug 14325392 but will be fixed in 12.1.0.
– About small tables with indexes: If Oracle goes for an index range scan of that small index, then it will be done in serial and not parallel and thus no Bloom filter will be used (just drop those small indexes).
– CURRENT_DATE does not use Bloom filters while SYSDATE does.
– Bloom filters are not used in DML statements: Bug 13801198 says that a simple two table join shows use of Bloom filter/pruning when running as a query and not when running as part of a INSERT as SELECT. The performance delta is 4:24 vs 0:24.

Exadata Consolidation: “You must spend money to make money”

Titus Maccius Plautus, a roman poet and philosopher who lived from 254 BC to 184 BC, said: “Wisdom is not attained by years, but by ability”. And also the famous quote in the title above.

What do I have in mind? Even midsize companies have nowadays dozens, if not hundreds, of database instances. Corporate IT departments would not surprise anyone if they support a couple of thousand databases.

The consolidation of these databases can be achieved in several ways. But here are some general guidelines on how to accomplish this task by using Exadata in the most optimal way in order to maximize cost reduction, high availability, secure separation of administrative duties and ease of performance tuning, management and monitoring.

It is really surprising how slowly companies adopt database consolidation granted the pressure IT management has in every possible direction. We can speculate for hours why so, but what I will concentrate on, are the technical aspects of Exadata consolidation.

Oracle recommends the creation of Exadata Hardware Pools. Hardware Pool is a machine or group of machines used as the target consolidation platform.

According to Oracle’s white paper “Best Practices For Database Consolidation On Exadata Database Machine“, an enterprise might create multiple Hardware Pools to make each consolidation target platform more manageable. The recommended minimum Hardware Pool size Oracle Maximum Availability Architecture Exadata Consolidation Best Practices is Exadata X2-2 Half Rack and the maximum recommended Hardware Pool size is two Exadata Database Machines Full Racks (plus additional Exadata storage expansion racks if required). Hardware Pools that fall within this range are the most common Exadata configurations for consolidation and provide sufficient capacity to efficiently achieve objectives for database consolidation.

The recommended storage configuration is one shared Exadata storage grid for each Hardware Pool. This storage grid contains all Exadata cells and Exadata disks, and is configured with either ASM high or normal redundancy. The recommended setup for Oracle Grid Infrastructure (which includes Oracle Clusterware and Oracle ASM) is to use one cluster per Hardware Pool.

Oracle has recommended several parameter settings for Exadata database consolidation:

If PageTables in /proc/meminfo is set to more than 2% of the physical memory size, then set the operating system parameter HugePages to the sum of all shared memory segments. Starting in 11.2.0.2, setting the database initialization parameter USE_LARGE_PAGES=ONLY on all instances prevents any instance from starting unless sufficient HugePages are available. Hugepages can only be used for SGA, so do not over-allocate. Also, the database parameters MEMORY_MAX_TARGET and MEMORY_TARGET are not compatible when HugePages are enabled. This is only for Linux. On Solaris, HugePages are automatically configured and used via intimate shared memory (ISM).

Operating system setting:

Set the number of shared memory segments (kernel.shmmni) greater than the number of databases.
Set the maximum shared memory segment size (kernel.shmmax) to 85% of physical memory size, which is the default.
Set the maximum total number of system semaphores (SEMMNS) greater than the sum of all database processes.
Set the maximum number of semaphores in a semaphore set (SEMMSL) greater than the largest number of processes in any single database.

Exadata Memory:

1. Exadata X2-2 based on the Sun Fire X4170 Oracle Database Servers (also known as V2) has 72 GB per database server.
2. Exadata X2-2 has 96 gigabytes (GB) of memory in the default configuration, with an option to expand to 144 GB of memory (with the Exadata memory expansion kit).
3. Exadata X2-8 has 1 terabyte (TB) (with the X4800) or 2 TB (with the X4800M2) per database server.

An important rule:

OLTP applications: SUM of databases (SGA_TARGET + PGA_AGGREGATE_TARGET) + 4 MB * (Maximum PROCESSES) < Physical Memory per Database Node
DW/BI applications: SUM of databases (SGA_TARGET + 3 * PGA_AGGREGATE_TARGET) < Physical Memory per Database Node

Remember to enable instance caging!

For the Oracle ASM instance, set PROCESSES= 50 * MIN ( # database instances on db node+ 1, 11) + 10 * MAX (# database instances on db node – 10, 0).

Limit PARALLEL_MAX_SERVERS:

1. X2-2: sum(PARALLEL_MAX_SERVERS) for all instances <= 240
2- X2-8: sum(PARALLEL_MAX_SERVERS) for all instances RECOVER MANAGED STANDBY DATABASE … PARALLEL 16.

Limit the number of processes and connections to the database servers:

–
For Exadata running Exadata Storage Server Software 11.2.3.1 or higher, configure a maximum of 60,000 processes per Hardware Pool. Upper limit target is 7,500 processes per node for X2-2. Upper limit target is 30,000 processes per node for X2-8. For Exadata running Exadata Storage Server Software 11.2.2.4.2 or less, configure a maximum of 20,000 processes per Hardware Pool. Upper limit target is 2,500 processes per node for X2-2. Upper limit target is 10,000 processes per node for X2-8.

The temporary tablespace should be:

BigFile Tablespace,
Located in DATA or RECO, whichever one is not HIGH redundancy,
Sized 32GB initially,
Configured with AutoExtend on at 4GB,
Configured with a MaxSize defined to limit out of control growth.

Answers to several additional Exadata related questions can be found in the following blogs/articles:

Who Manages the Exadata Machine? by Arup Nanda
Upgrade Exadata to 11.2.0.3 by Gleb Otochkin from Pythian
Best Practices For Database Consolidation On Exadata by Javier Puerta
Consolidation Strategies for Oracle Exadata and Oracle Database Machine by Dan Norris, X Team, Oracle Exadata Development
Expert Oracle Exadata by Kerry Osborne, Randy Johnson, Tanel Põder
Oracle Exadata – A platform for consolidation by Umesh Tanna

An excellent reference is the Consolidation Parameters Reference Table available at MOS: “Oracle Sun Database Machine Setup/Configuration Best Practices” [ID 1274318.1]

Back to the saying of Plautus: one does not have to wait for years in order to start implementing an Exadata consolidation. The product has the perfect ability to serve its many purposes: this is software, not wine!

Oracle Certification: the confidence to press enter

“You are educated. Your certification is in your degree. You may think of it as the ticket to the good life. Let me ask you to think of an alternative. Think of it as your ticket to change the world.” – Tom Brokaw

I agree. But you can get a ticket for the first row in the balcony, the boxes and there is always one for the last row as well.

You are experienced, you know a lot about Oracle, you have a good job; so why would you need an Oracle certification?

Indeed why? Let me have you Joel Goodman and Julia Johnson tell you about the “Benefits of Oracle Certification“, I would rather tell you what I can conclude based on my experience throughout the years.

I have worked with dozens of Oracle experts and if I pick a football team of 11, perhaps not all would be certified. In fact, while going through the names, some of the top ones I would name have never attended a single certification exam. So obviously, the certification is not decisive in terms of skills, experience and professionalism.

But there are 3 major things that make the Oracle certification important and worth considering:

1. You will find out how broad area Oracle is. How much more you can learn, if you are interested. And you will get familiar, to a very high extent, with what Oracle has to offer and how this complex mechanism works.

2. Confucius’s saying that “Real knowledge is to know the extent of one’s ignorance” will be more comprehensible to you.

3. Your confidence will grow and your self-esteem will reflect your personal overall evaluation or appraisal of your own worth.

That’s it: one, two, three.

Here are some interesting articles in case you are doubtful about certifying Oracle:

The power of Oracle certification by Anantha Narayanan

Oracle certifications: More than a piece of paper? by Shayna Garlick

Should You Get Your Oracle Certification? by Ivan Widjaya, CBR

The Career Benefits of Oracle Certification by Ahmed Aboulnaga

Oracle Certification Blog

And finally, going memory lane: the first 4 Oracle Certified Masters in Europe at Oracle World: 10 years ago in Copenhagen:

Socrates: “To know, is to know that you know nothing. That is the meaning of true knowledge.”

Is Oracle Advanced Compression underestimated?

Abraham Lincoln said: “I can make more generals, but horses cost money”.

Often all I hear about Oracle Advanced Compression is: “.. but it is an extra option on top of Enterprise Edition”. And thus its hidden power is often neglected.

Advanced Compression is not just about having the data occupy less space on disk, tape or in the network. It is also about better performance of the database, deduplication and the utilization of less hardware resources: memory and CPU. Thus, you can consolidate more databases on the same server and reduce the license costs.

I will try to compress this blog post, I will make it as short as possible:

1. What and which tables to compress?

Compress the segments that are involved in most reads: physical, logical or unoptimized. Consider also the segments that are used in SQL statements having most reads.

But do not compress all of them! Use DBMS_COMPRESSION.GET_COMPRESSION_RATIO in order to verify that you will benefit from the compression. Do only those with “Compression Ratio” 2 to 1 and higher. Here is an example of how you can estimate the compression ratio:

set serveroutput on
DECLARE
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO ('PSAPSR3', 'SAPSR3', '/BI0/PMATERIAL', '',DBMS_COMPRESSION.COMP_FOR_OLTP,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
DBMS_OUTPUT.PUT_LINE('Block count compressed = ' || blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Block count uncompressed = ' || blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || row_uncmp);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || comptype_str);
DBMS_OUTPUT.PUT_LINE('Compression ratio = '||round(blkcnt_uncmp/blkcnt_cmp,2)||' to 1');
DBMS_OUTPUT.PUT_LINE('Compression ratio org= '||cmp_ratio);
END;
/

2. How to measure the performance benefits of compression?

For the SQL statements run awrsqrpt.sql and compare the results:

Before Compression:

After Compression:

3. What comes after compression?

Follow the average CPU and I/O statistics for the compressed tables:

col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Owner’               format a10
col c2 heading ‘Object|Type’         format a10
col c3 heading ‘Object|Name’         format a15
col c4 heading ‘Average|CPU|Cost’    format 9,999,999,999
col c5 heading ‘Average|IO|Cost’     format 9,999,999
select
 to_char(sn.begin_interval_time,'mm-dd hh24') c0,  
 p.object_owner                               c1,
 p.object_type                                c2,
 p.object_name                                c3,
 avg(p.cpu_cost)                              c4,
 avg(p.io_cost)                               c5
from
 dba_hist_sql_plan p,
 dba_hist_sqlstat  st,
 dba_hist_snapshot sn
where
 p.object_name = '/BI0/PMATERIAL' 
 and p.sql_id = st.sql_id and st.snap_id = sn.snap_id     
group by
 to_char(sn.begin_interval_time,'mm-dd hh24'),
 p.object_owner,
 p.object_type,
 p.object_name
order by
 1,2,3 desc;  

P.S. About Exadata: do not mix HCC and DML, i.e., do not use HCC in OLTP.

On DBA innovation: who is afraid to fail will keep falling forever

Managers always ask software engineers to deliver something sooner rather than waiting to deliver everything later.

How come it is fine to deliver an incomplete low quality IT product just because it is delivered on time? Most of us have been on several occasions limited by that deadline and time-schedule in terms of creativity, innovation and pro-activeness.

Innovation in database management and database administration has nothing to do with DBAs from whom you often hear phrases like “If it ain’t broke, don’t fix it” or DBAs who follow blindly the KISS principle.

In DBA terms, innovation is the process of introducing and implementing new features in the database and using new options and database products.

A good example is the adoption of Oracle Enterprise Manager Grid/Cloud control. I have seen so many excuses for not implementing it or delaying its implementation. And the benefits and savings of OEM implementation can be measures in times!

Do you wonder how quickly and efficiently one can find out all details of a certain SQL statement without Cloud Control? No other database brand has so highly sophisticated tool for performance analysis like Oracle has. See all the details offered with one mouse click: Exadata Cell Offload Efficiency (96% in this case), Wait Activity in detail, use of the Result Cache, etc. All that from a single screen!

Accepting new innovative database properties, tools and appliances is hard for many IT architects, IT managers and most of all for DBAs who have the primary responsibility to test, verify and promote these features.

Let us have a look at one other innovation from Oracle. Implementing Exadata can be strongly considered if any of the following 5 points are in the IT roadmap:

1. Implementing a new Business Intelligence solution
2. Datawarehouse licenses are up for renewal
3. Database platform consolidation exercise
4. Storage requirements are increasing and the performance is decreasing
5. Performance of transactional systems requires major improvements

Look at the list below and think how many of these are used by your company or client:

– Exadata
– Enterprise Manager Cloud Control
– Edition-Based Redefinition
– Advanced Compression
– Oracle Database Appliance
– Automatic SQL Tuning
– Total Recall
– Real Application Testing
– One of these init.ora parameters: db_ultra_safe, result_cache_mode, optimizer_capture_sql_plan_baselines, awr_snapshot_time_offset

Although I did not put Enterprise Manager Cloud Control on top of the list, is it still a must for every Enterprise using Oracle products. On the light side, I was recently asked if you can see the temperature of a given computer from OEM 🙂 Here we go:

Jon Taplin said it very well in his article on Steve Jobs and Innovation: “At the Innovation Lab we try to inculcate the notion that you can’t be afraid. You can’t be afraid to fail. You can’t be afraid to “be different”. You can’t be afraid to celebrate the weird mix of art and science that is true innovation. Steve Jobs embodied all of those qualities. I wrote a bit about him in my new book and there is a cool video in the book of his graduation speech at Stanford that you will see replayed too often in the next few days.”

As a DBA, one should try to step out of his comfort zone where (s)he is surrounded by the everyday administrative tasks and reactive performance tuning work and try to make difference in the enterprise by acting more like a Database Architect than a Database Operator. Prove the complexity and importance of the DBA role!

P.S. Often in IT, the way from Insight to Action is longer than the Way of St. James.

The Choice of a New Generation

Recently, Coca-Cola Bottling migrated the SAP databases from Oracle to IBM DB2. The Coca Cola case study claims that they gained better performance for the SAP applications, reduced the storage needs and removed workload from the database administrators.

What else can one wish for? Granted that they had zero DB2 experience in-house but the lead DBA knew how to spell DB2! See for yourself:

And they have now higher productivity as the DBAs spend less time with DBA work and can concentrate more on SAP application tuning. If that’s not a perfect example of fast ROI and low TCO!

In another story entitled When will the database world wake up?, the author says that “IBM DB2 is more cost efficient than competing products especially Oracle Database… Better price-performance, better database compression, HADR: are these the only value proportion to buy or move to IBM DB2? No, IBM DB2 offers more values to the customers.”

I am again in a perplexing situation. I go on reading. “Strong partnership exists between IBM and SAP, joint SAP and IBM teams work on all levels of the product development. DB2 is certified by SAP within 4-8 weeks of its general availability (GA) date while Oracle does not have its new database releases certified by SAP until 1.5 to 2+ years after their GA date. All new DB2 features are designed to be 100% compatible and exploited by SAP directly.”

Now, I need a second opinion. Let us see what database guru Jonathan Lewis has to say:

That is, Jonathan says “SAP runs badly on everything, so you might as well choose the cheapest thing to run it badly on, rather than buying a sophisticated database system and then finding that you’re not allowed to use any of the expensive features that you’ve paid for.” Crystal clear!

If you would like to see what Oracle have to say, check this study: Oracle Database 11g vs. IBM DB2 UDB V9.7 Manageability Overview.

What’s next? Let us peek into some of Oracle’s most interesting features and try to find their equivalent in DB2? All comments and moreover answers to DB2 equivalents of the 6 Oracle’s features below are welcome.

1. Real Application Testing Database Reply
2. Automatic SQL Tuning
3. Edition-Based Redefinition
4. Enterprise Manager Cloud Control
5. SQL Performance Analyzer, AWR and ADDM
6. SQL Plan Management

What equivalents?

Another interesting comparison of Oracle and DB2, 51 pages in total, is called Database Manageability and Productivity Cost Comparison Study. The study concluded:

· Oracle Database 11g Release 2 Enterprise Edition resulted in an overall 43 percent time savings when compared to IBM DB2 Enterprise 9.7.
· Oracle Database 11g Release 2 Enterprise Edition resulted in an overall 45 percent step/complexity savings when compared to IBM DB2 Enterprise 9.7.
· Based on a productivity savings of 43 percent, the time savings amounted to USD $51,600.00 per year per DBA for an Oracle Database 11g Release 2 DBA versus an IBM DB2 Enterprise 9.7 DBA.

More interesting, in my opinion, is Conor O’Mahony’s analysis of that study entitled Oracle’s Shameful Study: Oracle Database 11gR2 vs. IBM DB2 9.7. Scott Hayes replies:

I am ready to take part in the study suggested by Scott Hayes, if ever organized. “Each installs their respective database, completes a design implementation, loads data, and performs maintenance tasks.” I am even ready to do it without any links to the documentation and any internet access.

What do you conclude from the above? That setting memory_target is a hard thing to so? Details here.

The Oracle System Monitoring Plug-In for IBM DB2 Database gives DBAs the opportunity to manage also IBM DB2 UDB (LUW) database instances. In fact, OEM Cloud Control 12c supports besides IBM DB2 also TimesTen, Sybase, Microsoft SQL Server and MySQL. Check the heterogeneous (Non-Oracle) management.

P.S. Last Sunday (25th March 2012), Dennis Howlett wrote an article entitled Oracle DB under threat? In summary, he said that Oracle’s database is under the spotlight in SAP shops. In summary, I would say: “Remember what Jonathan Lewis says“.

A couple of days after my post, Julie Bort wrote an interesting note entitled IBM Is Picking A New Fight With Oracle. Let me quote her: “IBM also wants customers to know that it hasn’t turned SAP into an enemy like Oracle has. It promises its new database makes SAP business software work faster than Oracle. Coca-Cola switched to DB2 10 and SAP worked up to 60% faster, it says.” Working up to 60% faster means that it can also work 5% faster or than even it can be slower, right?