Top 60 Oracle Blogs

Recent comments

November 2010

Returning the Days in a Month

How do you write a query to return all the days in a month as individual
rows? Given a single date, you want one row to be returned for each day of
the month into which the date falls. 

Read the full post at


Just got back from watching Skyline. I read some reviews before I went, most of which said it was terrible and something to avoid at all cost. With that message firmly embedded in my brain I went with zero positive expectations and quite enjoyed it.

Most of the reviews said the acting was really bad. It wasn’t wonderful, but it certainly wasn’t Twilight bad. The reviews said it lacked plot. Well yeah, but Cloverfield and District 9 weren’t exactly brimming with plot and they were cool. Now don’t get me wrong, I’m not heaping praise on this film, it’s certainly a B movie, but it’s by no means the worst film I’ve ever seen. It beats the hell out of any Twilight movie, but then so does a blank screen. :)

The film wears its influences very much on its sleeve. A lot of the visuals look like they were stolen heavily inspired by The Matrix and Independence Day (I really hate that film). There is a District 9 and Cloverfield feel to the film, with some of the visuals kinda stolen inspired by Cloverfield too, along with a bit of War of the Worlds for good measure.

One of the most interesting things about the film is it allegedly cost less than $10 Million to make. That’s like one episode of Friends (final season). I think the visuals are pretty sweet for that price. It also means it might make enough money, even with half empty cinemas, for a sequel to happen, which they definitely want judging by the ham-fisted hook into a sequel at the end. If one happens I will go to see it just out of curiosity.

So go expecting the worst and it won’t be quite as bad as you expected, possibly… :)



Cellcli Command Syntax – Top 10 List

Exadata storage software uses the cellcli utility as its command line interface. Unfortunately, although the documentation set that comes with Exadata does have many examples of cellcli commands, and even a chapter dedicated to cellcli, it does not include any reference material on the syntax itself (particularly the LIST command). So I thought I would write up a few of the things I’ve learned while picking around at it. But first a little bit of a rant on why they wrote yet another command line interface.

They already had SQL*Plus for crying out loud. Why not just use that. SQL*Plus has all kinds of functionality for using variables, formatting output, etc… And on top of that, they invented a new syntax. Why use LIST instead of SELECT? They used WHERE and LIKE, so why not SELECT? I find it more than a little annoying (in case you couldn’t tell). I’m told that storage admins don’t like SQL and that’s one of the reasons for not using straight SQL syntax. That seems pretty silly since the storage is designed specifically for use with Oracle databases.

So anyway, here’s my quick top ten list of things you should know:

  1. cellcli does have a handful of SQL*Plus commands (START (@), SET ECHO ON, SPOOL, DESCRIBE, HELP)
  2. SELECT is replaced by LIST and it must be the first key word on the command line
  3. There is no FROM keyword (the LIST keyword must be immediately followed by the ObjectType which is equivalent to a table name)
  4. There is a DESCRIBE command which displays the attributes (columns) that make up an ObjectType (table)
  5. Column names are specified with the ATTRIBUTES keyword followed by the columns you wish to be displayed
  6. There is a default set of columns for each Object that will be returned if the ATTRIBUTES keyword is not specified
  7. There is a WHERE clause that can be applied to any attribute and multiple conditions can be ANDed together (no OR though)
  8. There is no ORDER BY equivalent
  9. The DETAIL key word can be appended to any LIST command to change the output from column oriented to row oriented
  10. The LIKE operator works but instead of the standard SQL wildcard, %, cellcli uses regex – so ‘%’ = ‘.*’

So here are a few examples:

CellCLI> help 
 HELP [topic]
   Available Topics:
        ALTER CELL
        ALTER LUN
        ASSIGN KEY
        CREATE KEY
        DROP CELL
        LIST CELL
        LIST KEY
        LIST LUN
CellCLI> help set
  Usage: SET  
  Purpose: Sets a variable to alter the CELLCLI environment settings for your
           current session.
    variable and value represent one of the following clauses:
    ECHO { ON | OFF }
    set dateformat local
    set echo on 
CellCLI> help list
  Enter HELP LIST  for specific help syntax.
                     | IORMPLAN | KEY | LUN 
                     | PHYSICALDISK | THRESHOLD }
  Purpose: Displays specified attributes for flash cache entries.
    :  An expression which determines the entries to be displayed.
    : The attributes that are to be displayed.
                      ATTRIBUTES {ALL | attr1 [, attr2]... }
    [DETAIL]: Formats the display as an attribute on each line, with
              an attribute descriptor preceding each value.

So as you can see, the help system allows you to see a bit of the syntax for each command. You may also have noticed a couple of SQL*Plus carry-overs. SET, SPOOL, and START work pretty much as expected. Note the @ is equivalent to START and that the only things you can SET are ECHO and DATEFORMAT. Now for a couple of queries (er LIST commands):

CellCLI> desc flashcachecontent
CELL-01504: Invalid command syntax.
CellCLI> describe flashcachecontent
CellCLI> set echo on
CellCLI> @fc_content
> CellCLI> list flashcachecontent where dbUniqueName like 'EXDB' and hitcount > 100 attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize, hitCount, missCount
         EXDB    2       0       4194304         600     208
         EXDB    40      0       2424832         376     60
         EXDB    224     0       1802240         115     80
         EXDB    267     0       458752          128     9
         EXDB    383     0       2547712         157     27
         EXDB    423     0       1867776         180     41
         EXDB    471     0       4071424         552     85
         EXDB    472     0       1277952         114     22
         EXDB    474     0       13246464        286     326
         EXDB    475     0       5914624         519     124
         EXDB    503     0       5308416         669     455
         EXDB    5710    0       3735552         363     90
         EXDB    6207    0       393216          112     9
         EXDB    6213    0       3842048         359     147
         EXDB    6216    0       1245184         184     29
         EXDB    6373    0       3481600         222     61
         EXDB    56085   0       4194304         822     129
         EXDB    66849   0       438763520       1221    3322
         EXDB    71493   0       5636096         302     127
         EXDB    71497   0       1351680         320     22
         EXDB    71573   0       2760704         101     37
         EXDB    71775   0       1801412608      34994   46315
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 -
> attributes dbUniqueName, objectNumber, cachedKeepSize, cachedSize 
         EXDB    2       0       4194304
         EXDB    18      0       1179648
         EXDB    37      0       622592
         EXDB    40      0       2424832
         EXDB    63      0       524288
         EXDB    104     0       688128
         EXDB    224     0       3407872
         EXDB    267     0       458752
         EXDB    383     0       2670592
         EXDB    420     0       1507328
         EXDB    423     0       1867776
         EXDB    424     0       720896
         EXDB    471     0       4071424
         EXDB    472     0       1277952
         EXDB    473     0       2351104
         EXDB    474     0       13574144
         EXDB    475     0       5521408
         EXDB    503     0       5308416
         EXDB    5702    0       262144
         EXDB    5709    0       2416640
         EXDB    5710    0       3735552
         EXDB    6207    0       393216
         EXDB    6210    0       131072
         EXDB    6213    0       4227072
         EXDB    6216    0       1245184
         EXDB    6373    0       3579904
         EXDB    56085   0       4194304
         EXDB    66849   0       438763520
         EXDB    71493   0       5636096
         EXDB    71497   0       1351680
         EXDB    71573   0       2801664
         EXDB    71775   0       1801412608
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775
CellCLI> list flashcachecontent where dbUniqueName like '.*X.?.?' and objectNumber like '.*775' detail                                      
         cachedKeepSize:         0
         cachedSize:             1801412608
         dbID:                   2356637742
         dbUniqueName:           EXDB
         hitCount:               34994
         missCount:              46315
         objectNumber:           71775
         tableSpaceNumber:       6
CellCLI> list flashcachecontent where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         2356637742      6       71775
CellCLI> list flashcachecontent attributes objectNumber, hitCount, missCount where dbUniqueName like 'EX.?.?' and hitcount > 100 and objectNumber like '.*775'
         71775   34994   46315

So DESC doesn’t work as an abbreviation of DESCRIBE. Notice that there are no headings for column oriented output. As you can see, you can run “scripts” and SET ECHO ON to display the commands in any scripts that you execute. One of the LIST commands was strung across two lines by using the continuation operator (-). The LIST commands look a lot like SQL except for LIST being used instead of SELECT and the regex expressions for matching when using the LIKE key word. Also notice that in the last command a number was matched with a regex expression implying a data type conversion, although all data may be treated at text. You can see that the ATTRIBUTES and WHERE key words can be anywhere on the command line after the “LIST objectName” keywords. In other words, these two key words are not positional, either one can be first. Finally, the DETAIL keyword turns the output sideways. Or as the help says, “Formats the display as an attribute on each line, with
an attribute descriptor preceding each value.”

So the cellcli interface is really not that bad, I just happen to like SQL*Plus better. ;) I do think it would have been a simple matter to reuse SQL*Plus since they already have all the functionality built into it, but maybe there were other concerns that I’m not aware of. But cellcli works. And by the way, cellcli has the ability to scroll though previous commands and edit them via the arrow keys which is quite handy. The editing capability is definitely a step forward from SQL*Plus on unix like platforms (although you can use rlwrap to accomplish this – see this post for more details on that: Using rlwrap on Windows) And regex also provides a very powerful pattern matching capability although it’s still a little confusing to have SQL like syntax mixed with regex to my way of thinking. Maybe if they just added the ability to use the % wildcard in addition to the regex I would feel better about it.

Dynamic Sampling Changes

November 12, 2010 I read a message thread on the Oracle-L list that caused me to pause for a moment… If you display an execution plan and see a message stating “Dynamic sampling used”, you might be left wondering what caused the dynamic sampling.  From the Oracle Database 11.2 documentation, the various levels of dynamic sampling: [...]


… (or “Fall” for speakers of American) has arrived in the UK – and once again I am reminded how gardening and trouble-shooting are just two aspects of the same problem.

I have several trees in and around my garden, including two rather large Oak trees, and at this time of year it takes a couple of hours at the weekend to rake up the fallen leaves. The comparison with solving performance problems is obvious:

Every Saturday, I look at the leaves on the ground and the leaves still on the trees and quite often manage to persuade myself that there’s no point in doing anything just yet.

On the Saturdays when I decide that I really do have to rake up the leaves I aim to clear about 99% of the problem – there’s no point in clearing to 100% because if I go for perfection it’s only going to last a couple of minutes before more leaves start coming down or blowing in. Of course, after I’ve spent ages clearing 99% of the mess, my wife (the end user) is quite likely to say: “you haven’t finished yet”

After I’ve done a really good job raking up enough leaves I look up at the trees and know that all those leaves are going to be heading my way and I’m probably going to have to do it all over again next week, and there’s nothing appropriate that I can do to stop it happening.


ODTUG Kscope11

ODTUG Kscope11
Event date: 
Sun, 2011-06-26 - Thu, 2011-06-30

 ODTUG Kscope11 is the conference for you if you are looking for training and answers in the following areas:
Take a sneak peak at Kscope 11 content by topic area by clicking on your area of interest below:

BI and Oracle EPM

Application Express

Database Development

Oracle Fusion Middleware



Why? Because Kscope offers the best content and the most sessions on these topics than any other conference. 

Collaborate 11

Collaborate 11
Event date: 
Sun, 2011-04-10 - Thu, 2011-04-14

COLLABORATE 11: Technology & Application Forum for the Oracle Community
April 10-14, 2011  I  Orange County Convention Center  I  Orlando, Florida

RMOUG Training Days 2011

RMOUG Training Days 2011
Event date: 
Tue, 2011-02-15 - Thu, 2011-02-17

Registration for RMOUG Training Days 2011 is now open. RMOUG Training Days is the largest regional user group conference in the United States. It is the place to network with local Oracle professionals and talk with internationally renowned Oracle experts. Check out the Schedule-at-a-Glance -- it has close to one hundred technical sessions.
University sessions -- back by popular demand!!!
RMOUG is offering in-depth University Sessions on Tuesday, February 15 2010 (the day before the main conference) from 1:00pm - 5:00pm. The registration fee for each University Session is $125.00.
If you register for both the Training Days conference on February 16-17, as well as the University Sessions on February 15, you will automatically receive a 10% discount on your University Sessions' registration.
Participation in these classes is limited due to room size, so early enrollment is encouraged.

Hotsos Symposium 2011

Event date: 
Sun, 2011-03-06 - Thu, 2011-03-10

 Join the world's top Oracle performance analysts at the ninth annual Hotsos Symposium, which is the conference dedicated to issues of Oracle system performance. The Symposium will be held March 6 — 10, 2011 at the Omni Mandalay Hotel in Irving, TX.
"The Symposium was awesome. I really mean that. I had a great time and the speakers were great. It is a real testament to your company how well this seminar proceeds. Everything from the selections of food for lunch and breakfast to the timely topics is very well thought out. There wasn't one lost moment.

Everyone who spoke was fascinating and obviously expert at what they were speaking on. I enjoyed Tom Kyte's keynote address as well as his follow-up session on the importance of Metadata. Richard Foote's information on indexes was particularly helpful to me. We tested out Kerry Osborne's scripts to control execution plans without changing code and it worked as advertised. Every speaker was very informative and I got a lot out of the seminar. 

All in all - time well spent."


— Tom Hennessy

Why Is the Hotsos Symposium Different?
Hotsos Symposium is the best conference in the Americas devoted to Oracle system performance. The combination of topic focus, small audience size, and large speaker list make the Hotsos Symposium an unsurpassed networking opportunity.
Hotsos Symposium reaches for a new technical high with presentations focused on techniques, experimental results, and field-tested scripts that attendees can take home and apply to real-world tasks. The speakers routinely offer an excellent balance between database principles and problem-solving techniques. The speakers also show you how to determine the difference between reliable information and bad advice around Oracle performance optimization.
Like what you read, but want a little taste of Symposium? Watch this video to see what you missed last year.
Symposium Speaker Sessions
The call for papers has ended, and we received the greatest response ever! Speakers and sessions will be announced on November 5, 2010. Be sure to register today because you won't want to miss this event.
Hotsos is excited to announce that Kerry Osborne will give a technical keynote for Symposium 2011. Kerry's topic is entitled, Back to the Future: Mainframes, Open Systems, and the New Role of Exadata. To find out more about Kerry and his topic, please visit ourKeynote page.
Optional Training Day
We call her the Grand PooBah of SQL, some call her the Queen of SQL, but most know her as Karen Morton. Karen is the headliner for Training Day 2011, and her topic is Managing SQL Performance - Practical Information and Tools for Writing and Maintaining Optimally Performing SQL. To find out more information about Karen and what she will cover, please visit the Training Day page.
Registration and Pricing
Registration for Hotsos Symposium 2011 is open. Visit our Registration page to sign up.
The pricing for the Symposium is as follows:

  • $1195, if registering on or before December 31, 2010
  • $1350, if registering on or before February 11, 2011
  • $1495, if registering after February 11, 2011

Pricing for the optional Training Day is:

  • $500

**NEW!** Registration Price including Hotel:

Hotsos has partnered with the Omni to provide a bundled price for Symposium and Training Day this year.

  • $1695 for Symposium, plus three nights of hotel lodging, if registering on or before December 31, 2010
  • $2362 for Symposium, Training Day and four nights of hotel lodging, if registering on or before December 31, 2010

In order to take advantage of these discounts, please register and pay prior to the deadlines listed above. To find out more detailed information about registration, pricing and our cancellation policy, please visit our Registration page.
Presentation Materials - Going Green again in '11!

E2SN Virtual Conference on Oracle Performance

E2SN Virtual Conference on Oracle Performance
Event date: 
Thu, 2010-11-18 - Fri, 2010-11-19

 The E2SN Virtual Conferences are webinar-style online training sessions, but just like at conferences, you can listen to multiple top speakers within a single event.
The standard conference consists of 4 x 1.5 hour presentations with 30-minute Q & A sessions after each presentation. The four sessions are spread across two days, so the conference takes only four hours per day. So, in addition to not even having to leave your desk for learning from world's top experts, you can still get your important work done in the office, too!
The first scheduled virtual conference is focusing on a walk-through on Systematic Oracle SQL Optimization, with emphasis on practical applications and real-life scenarios.

Systematic Oracle SQL Optimization virtual conference:

  • The speakers are: Cary Millsap, Jonathan Lewis, Kerry Osborne and Tanel Poder
  • The conference takes place on 18. and 19. November ( 2 x 0.5 days )
  • The time of the conference sessions is: 08:00am - 12:00pm Pacific Standard Time (PST) on both days
  • For this inaugural event we have a special launch price of 475 USD per attendee!