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.
<RANT>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. </RANT>
So anyway, here’s my quick top ten list of things you should know:
- cellcli does have a handful of SQL*Plus commands (START (@), SET ECHO ON, SPOOL, DESCRIBE, HELP)
- SELECT is replaced by LIST and it must be the first key word on the command line
- There is no FROM keyword (the LIST keyword must be immediately followed by the ObjectType which is equivalent to a table name)
- There is a DESCRIBE command which displays the attributes (columns) that make up an ObjectType (table)
- Column names are specified with the ATTRIBUTES keyword followed by the columns you wish to be displayed
- There is a default set of columns for each Object that will be returned if the ATTRIBUTES keyword is not specified
- There is a WHERE clause that can be applied to any attribute and multiple conditions can be ANDed together (no OR though)
- There is no ORDER BY equivalent
- The DETAIL key word can be appended to any LIST command to change the output from column oriented to row oriented
- 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
ALTER ALERTHISTORY
ALTER CELL
ALTER CELLDISK
ALTER GRIDDISK
ALTER IORMPLAN
ALTER LUN
ALTER THRESHOLD
ASSIGN KEY
CALIBRATE
CREATE
CREATE CELL
CREATE CELLDISK
CREATE FLASHCACHE
CREATE GRIDDISK
CREATE KEY
CREATE THRESHOLD
DESCRIBE
DROP
DROP ALERTHISTORY
DROP CELL
DROP CELLDISK
DROP FLASHCACHE
DROP GRIDDISK
DROP THRESHOLD
EXPORT CELLDISK
IMPORT CELLDISK
LIST
LIST ACTIVEREQUEST
LIST ALERTDEFINITION
LIST ALERTHISTORY
LIST CELL
LIST CELLDISK
LIST FLASHCACHE
LIST FLASHCACHECONTENT
LIST GRIDDISK
LIST IORMPLAN
LIST KEY
LIST LUN
LIST METRICCURRENT
LIST METRICDEFINITION
LIST METRICHISTORY
LIST PHYSICALDISK
LIST THRESHOLD
SET
SPOOL
START
CellCLI> help set
Usage: SET
Purpose: Sets a variable to alter the CELLCLI environment settings for your
current session.
Arguments:
variable and value represent one of the following clauses:
DATEFORMAT { STANDARD | LOCAL }
ECHO { ON | OFF }
Examples:
set dateformat local
set echo on
CellCLI> help list
Enter HELP LIST for specific help syntax.
: {ACTIVEREQUEST | ALERTHISTORY | ALERTDEFINITION | CELL
| CELLDISK | FLASHCACHE | FLASHCACHECONTENT | GRIDDISK
| IORMPLAN | KEY | LUN
| METRICCURRENT | METRICDEFINITION | METRICHISTORY
| PHYSICALDISK | THRESHOLD }
CellCLI> help list FLASHCACHECONTENT
Usage: LIST FLASHCACHECONTENT [] [] [DETAIL]
Purpose: Displays specified attributes for flash cache entries.
Arguments:
: An expression which determines the entries to be displayed.
: The attributes that are to be displayed.
ATTRIBUTES {ALL | attr1 [, attr2]... }
Options:
[DETAIL]: Formats the display as an attribute on each line, with
an attribute descriptor preceding each value.
Examples:
LIST FLASHCACHECONTENT DETAIL
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
cachedKeepSize
cachedSize
dbID
dbUniqueName
hitCount
hoursToExpiration
missCount
objectNumber
tableSpaceNumber
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.
[…] used to manage Exadata storage, is another hot topic of the week. Kerry Osborne sheds light on it here, and Pythian Blog […]
Thank you Kerry- these helpful hints come right at a time I am studying this aspect of Exadata.
The flash cache content has been a significant eye opener to me as I’ve worked through the data!
Kerry,
Have a look at what Guy’s cooked up:
http://guyharrison.squarespace.com/blog/2011/7/31/a-perl-utility-to-improve-exadata-cellcli-statistics.html?lastPage=true#comment15512073
dcli command to show flash logs info
dcli -l root -g cell_group cellcli -e “list metriccurrent where name like \’FL_.*_FIRST\'”
[…] The script is based on the dcli and the cellcli commands and their regular expressions (wich are described into Kerry Osborne’s  post). […]
Hello,
I just want to let you know that I developed a script to extract exadata real-time metric information based on cumulative metrics.
The main idea is that cumulative, instantaneous, rates and transition exadata metrics are not enough to answer all the basic questions.
That’s why the script has been created as it provides a better understanding of what’s is going on on the cells right now.
More details (on how and why) here : http://bdrouvot.wordpress.com/2012/11/27/exadata-real-time-metrics-extracted-from-cumulative-metrics/
Please don’t hesitate to give your opinion and report any issue you may found with it.
Thx
Bertrand
Thanks Bertrand, I’ll give it a look.
[…] http://kerryosborne.oracle-guy.com/2010/11/cellcli-command-syntax-top-10/ […]