Oakies Blog Aggregator

Sql plan enhancements in 10g

New plan features in 10g make SQL performance investigations much more simple. December 2004 (updated November 2007)

Pl/sql optimisation in 10g

Oracle 10g's compiler optimisation for faster PL/SQL, with a new section on optimisation bugs. November 2004 (updated March 2008)

2000 columns

How many columns does the largest table you've ever worked with contain? The current project I'm working on has 1 table with almost 2000 columns (and it's likely to add more!). This is the most highly denormalized design I've ever encountered and there's something about it that makes the performance optimizer in me cringe. But, the statisticians that have to munch and crunch this data in SAS tell me this format best suits their needs (based on similar designs used successfully in previous projects).

I think I'm really more concerned about the work that has to be done to populate these columns as most of the columns contain aggregations or formulations of some sort or another. So, perhaps it's not the number of columns that really is niggling at me as it is everything that must occur to produce the values contained in a single row (it's a lot).

What's your experience? Did the number of columns help, hinder or make no difference in the design and performance of the application that used such wide tables? This phase of the project is a proof of concept so it'll be a while before I get hard data on how well (or not) this design really works, but it has certainly made me curious about what other folks have experienced.

Let me hear from you if you have any comments or insights!

Using pipelined table function as the UI API object

In my previous post I've introduced you to an example WoD application page, and showed how the render-flow could be built in an Helsinki manner using a ref-cursor. The UI technology stack would call a stored procedure which in turn would return a ref-cursor. Inside the UI code stack all that needs to be done is fetch the rows from the ref-cursor (until %NOTFOUND), then close the cursor, and

Query result cache in oracle 11g

Oracle adds a new cache for storing the results of queries. December 2007

Pivot and unpivot queries in 11g

Turn rows into columns and vice versa with new extensions to the SELECT statement. April 2008

Pl/sql enhancements in oracle 11g

Various minor language and operability enhancements for PL/SQL in 11g. May 2008

Partway Researched With A Chance Of FUD

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 as a conservative, reliable number. We see higher numbers in disk tests, but never anywhere near 125MB/sec.” -Barry Zane, Chief Technology Officer (link) Far Fetched Or Fact? As a database performance engineer, I strive to be extremely detailed and well researched with my work. Clearly, these comments from Rick and Barry were not well researched as is evident from information publicly available on the Internet. The first bit of documentation I would research before making such comments would be the hard disk drive specification sheet. The 12 drives in the Exadata Storage Server, a Sun Fire X4275, are [...]

2009 Year-End Zeitgeist

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

it didn't work ...

I did manage to publish a link to this blog post, 'Releasing early is not always good? Heresy!' at about 3:00 am on Jan 1st. The plan was that I'd close out 2009 by getting the last few posts related to Agile design off of my mind so I could start 2010 ready to return my focus to measurement. Fail.I woke up late that morning, drank my coffee and thought about problems in the design and