Partitioning is an essential performance feature for an Oracle data warehouse because partition elimination (or partition pruning) generally results in the elimination of a significant amount of table data to be scanned. This results in a need for less system resources and improved query performance. Someone once told me "the fastest I/O is the one that never happens." This is precisely the reason that partitioning is a must for Oracle data warehouses - it's a huge I/O eliminator. I frequently refer to partition elimination as the anti-index. An index is used to find a small amount data ...

Data Warehousing, Oracle, Performance, VLDB

Editor's note: This blog post does not cover Exadata Hybrid Columnar Compression. The first thing that comes to most people's mind when database table compression is mentioned is the savings it yields in terms of disk space. While reducing the footprint of data on disk is relevant, I would argue it is the lesser of the benefits for data warehouses. Disk capacity is very cheap and generally plentiful, however, disk bandwidth (scan speed) is proportional to the number of spindles, no mater what the disk capacity and thus is more expensive. Table compression reduces the footprint on the ...

Data Warehousing, Oracle, Performance, VLDB

I tend to keep the content of this blog fairly technical and engineering focused, but every now and then I have to venture off and do an editorial post.  Recently some of the ParAccel management decided to fire up the FUD machine on the ParAccel blog and take aim at Oracle's Exadata making the following claims: "There are 12 SAS disks in the storage server with a speed of about 75 MB/s [The SUN Oracle Exadata Storage Server datasheet claims 125 MB/s but we think that is far-fetched.]" -Rick Glick, Vice President of Technology and Architecture (link) "We stand by the 75MB/sec ...

Exadata, Oracle

Another year in the books and another year on the Structured Data blog.  Hopefully 2009 treated you well and 2010 will bring good things in addition.  I thought I'd throw a few Top 5 lists together to reminisce about 2009.  Enjoy! Top 5 Most Visited Blog Posts of 2009 DBMS_STATS, METHOD_OPT and FOR ALL INDEXED COLUMNS Choosing An Optimal Stats Gathering Strategy Top 10 Oracle 11gR2 New Features Troubleshooting Bad Execution Plans Oracle 11g: Real-Time SQL Monitoring Using DBMS_SQLTUNE.REPORT_SQL_MONITOR Top 5 Most Popular Search Queries of 2009 structured data oracle 11gr2 new features db_file_multiblock_read_count oracle analytic functions dbms_stats method_opt

Oracle

If you want to build a house that will stand the test of time, you need to build on a solid foundation. The same goes for architecting computer systems that run databases. If the underlying hardware is not sized appropriately it will likely lead to people blaming software. All too often I see data warehouse systems that are poorly architected for the given workload requirements. I frequently tell people, "you can't squeeze blood from a turnip", meaning if the hardware resources are not there for the software to use, how can you expect the software to ...

Data Warehousing, Oracle, Performance, VLDB

The HP-UX Itanium and AIX (PPC64) ports of Oracle Database 11g Release 2 can now be downloaded from OTN. Happy Holidays!!!

11gR2, Oracle

At the 2009 Oracle OpenWorld Unconference back in October I lead a chalk and talk session entitled The Core Performance Fundamentals Of Oracle Data Warehousing. Since this was a chalk and talk I spared the audience any powerpoint slides but I had several people request that make it into a presentation so they could share it with others. After some thought, I decided that a series of blog posts would probably be a better way to share this information, especially since I tend to use slides as a speaking outline, not a condensed version of a white paper. ...

Data Warehousing, Oracle, Performance, VLDB

The Solaris x86-64 port of Oracle Database 11g Release 2 can now be downloaded from OTN. Get it while it's hot!

11gR2, Oracle

Just a quick note that there is now a patch for the 11.2 Oracle Enterprise Linux (OEL) database ports to enable the database flash cache (not to be confused with the Exadata flash cache). Go to the My Oracle Support site [link] and search for patch 8974084 - META BUG FOR FLASH CACHE 11.2PL BUGS TO BACKPORT TO 11.2.0.1 OEL You can download Oracle Database 11g Release 2 from OTN. Note: The db flash cache is already built into the Solaris ports so no patch is needed.

11gR2, Oracle

One of the less "traditional" tracks at Oracle OpenWorld is the Oracle Unconference. The Unconference allows for a much more informal setting for sessions and generally is more interactive and audience driven compared to the usually amounts of PowerPoint sessions contain which can cause eyes to bleed. This year I've signed up to give a session at the Unconference entitled Chalk & Talk: The Core Performance Fundamentals of Oracle Data Warehousing. This session will be a PowerPoint free session (hence Chalk & Talk) and it will be a very engineering heavy discussion around what techniques I use ...

Oracle

The internet buzz seems to be that Larry Ellison, CEO, Oracle Corporation and John Fowler, EVP, Sun Microsystems, Inc. will be announcing a new product, the world's first OLTP database machine with Sun's brand new FlashFire technology on Tuesday, September 15, 2009, 1 p.m. PT. Both Sun and Oracle have Webcast invitations on their websites: Oracle Invitation Sun Invitation I plan on being at the Oracle Conference Center for the launch and will try and Tweet the highlights. First Oracle Database 11g Release 2, now an OLTP database machine. Are there more innovations up Oracle's sleeve? I guess we'll have to ...

Exadata, OLTP, Oracle

In catching up on blog posts I see that Jonathan Lewis, Christian Antognini and Nuno Souto picked up on the deferred segment creation new feature in Oracle 11gR2. In keeping with the theme, I thought I'd put together the top 10 new features in Oracle Database 11g Release 2 (11.2) that I consider significant. Analytic Functions 2.0 Recursive WITH Clause Preprocessing Data for ORACLE_LOADER Access Driver in External Tables In-Memory Parallel Execution Auto Degree of Parallelism (Auto DOP) and Parallel Statement Queuing Significant Performance Improvement of MV On-Commit Fast Refresh Database Resource Manager Instance Caging ASM Intelligent Data Placement Database File System (DBFS) Hybrid Columnar Compression In future posts I'll ...

11gR2, Performance