Archive for the ‘Exadata’ Category.
February 9, 2011, 7:50 pm
I have been getting quite a few questions about our upcoming Exadata Book lately so I thought I would post a quick update. We are working feverishly on it so please give us a break!
Just kidding!
I am actually feeling like we can see the light at the end of the tunnel now. We are well past the half way mark and I am feeling confident about the content. Well more than confident actually. I think it’s going to be awesome! In large part that’s due to the fact that I feel like we have the Dream Team working on the project. Tanel Poder has signed on as a co-author. Kevin Closson is the Official Technical Reviewer (and we’re tentatively planning on including a number of his comments in the book – in little “Kevin Says” sidebars). As one of the main architects of Exadata, this should provide some interesting perspective. Arup Nanda has volunteered as an unofficial technical reviewer as well. I have to say that Arup has been a great help. And I really appreciate him providing another perspective on what we’re writing about. All three of these guys are fellow Oak Table bretheren, by the way. Randy Johnson is the other co-author, and although he generally prefers to keep a low profile, he is extremely knowledgeable on things that the rest of us don’t deal with that much on a day to day basis, namely backup and recovery and storage configuration. He has a great RAC and ASM background as well. I have to also say that a guy none of you has ever heard of (Andy Colvin) has been a huge help as well. He is our in-house Exadata patching guru. Without him I’m not sure we would have been able to do the necessary testing to complete the book.
I must say that I feel honored to be involved in a project with such an accomplished group of guys. And by the way, we have had numerous offers from people that I have a lot of respect for to help with various aspects of the project. I want to thank all of you for those offers, even if we haven’t taken you up on all of them (our little brains can only absorb so much feedback at any one time). The book is actually available for pre-order on the Amazon already (so someone must think we are actually going to finish it pretty soon). I think we’re right on track for later spring delivery. :-)
January 30, 2011, 7:52 pm
Ever wonder why that weird line shows up in the Predicate Section of an execution plan on Exadata? Me too! The STORAGE bit tells us it’s a filter applied at the storage cell layer, but the rest is nonsensical. Well I recently ran across a very brief mention of it in a Metalink note. (I know it’s not called Metalink any more, but I’m kind of set in my ways). The note said it was related to distribution of rows to PX slaves. Ah ha! Let’s test it out. Here’s a plan with the predicate just so you can see what it looks like.
KSO@arcp> @dplan
Enter value for sql_id: a9axwj6ym3b29
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a9axwj6ym3b29, child number 0
-------------------------------------
select /*+ parallel_index(t, "ISD_SI_I03",8) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(0)
no_monitoring no_substrb_pad no_expand index_ffs(t,"ISD_SI_I03") */
count(*) as nrw,count(distinct sys_op_lbid(725425,'L',t.rowid)) as
nlb,count(distinct hextoraw(sys_op_descend("SUPPORT_LEVEL")||sys_op_desc
end("SUPPORT_LEVEL_KEY")||sys_op_descend("NAME")||sys_op_descend("VALUE"
))) as ndk,sys_op_countchg(substrb(t.rowid,1,15),1) as clf from
"ISD"."SUPPORT_INFO" sample block ( .0503530742,1) t where
"SUPPORT_LEVEL" is not null or "SUPPORT_LEVEL_KEY" is not null or
"NAME" is not null or "VALUE" is not null
Plan hash value: 1766555783
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 672 (100)| | | | |
| 1 | SORT GROUP BY | | 1 | 56 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 56 | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT GROUP BY | | 1 | 56 | | | Q1,01 | PCWP | |
| 5 | PX RECEIVE | | 1 | 56 | | | Q1,01 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 1 | 56 | | | Q1,00 | P->P | HASH |
| 7 | SORT GROUP BY | | 1 | 56 | | | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 397K| 21M| 672 (1)| 00:00:01 | Q1,00 | PCWC | |
|* 9 | INDEX STORAGE SAMPLE FAST FULL SCAN| ISD_SI_I03 | 397K| 21M| 672 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - storage(:Z>=:Z AND :Z< =:Z AND ("SUPPORT_LEVEL" IS NOT NULL OR "SUPPORT_LEVEL_KEY" IS NOT NULL OR "NAME" IS NOT NULL OR
"VALUE" IS NOT NULL))
filter(("SUPPORT_LEVEL" IS NOT NULL OR "SUPPORT_LEVEL_KEY" IS NOT NULL OR "NAME" IS NOT NULL OR "VALUE" IS NOT NULL))
37 rows selected. |
Notice that the plan is for a PX statement. So how can we convince ourselves that it is a PX related predicate. We can try two tests.
- Let’s see if we can find any SQL statements that have the predicate that aren’t PX statements.
- Let’s see if we can find any PX statements that don’t have the predicate.
So here we go.
Continue reading ‘storage(:Z>=:Z AND :Z< =:Z)’ »
January 22, 2011, 6:27 pm
Well I turned in the HCC chapter on the Exadata book last week and of course as is usually the case, I immediately ran across something kind of cool on the topic I just finished writing about. (we still have several editing passes though, so I can add it later). Anyway, although I don’t have time to post much these days, I thought this one would be a quick little snippet. So here it is.
The Compression Advisor is part of the DBMS_COMPRESSION package. Specifically it is the GET_COMPRESSION_RATIO procedure. This procedure is worthy of a separate post but I won’t discuss it here except to say that as of 11.2.0.2 you can use it to test HCC compression ratios on non-Exadata platforms. That’s pretty cool, but what I wanted to tell you about is a handy little function in the same package called GET_COMPRESSION_TYPE. This function can tell you exactly what level of compression has been applied to a single row. This can come in handy for investigating the inner workings of HCC (or OLTP or BASIC compression for that matter).
As you probably already know, HCC is only applied to records loaded via direct path writes. Any updates cause rows to be migrated out of that storage format into blocks flagged for OLTP compression. Of course OLTP compression on a block only kicks in when a block is “full”. On top of this, altering a table to change it’s compression does not actually change the storage format of any existing records (unless you use the MOVE keyword). So you could load some data and then change the designation (say from QUERY LOW to QUERY HIGH). Rows that are inserted after the change will be stored in the new format (assuming the records are loaded via direct path writes of course). So why am I telling you all this. Well, because I ran across a statement in some Oracle documentation that said you can check to see what compression method a table is stored with by looking at the COMPRESS_FOR column in the DBA_TABLES view. This column does reveal what the table designation is. However, the setting actually only tells you how rows inserted in the future will be compressed. It tells you absolutely nothing about the way current rows are stored.
As for the mechanics, it appears that each row has a bitmask associated with it showing what compression format is being used. So I wrote a little script to give me what I want to see (check_row_comp.sql) using the DBMS_COMPRESSION.GET_COMPRESSION_TYPE function. Here’s an example of its use.
Continue reading ‘EHCC and the GET_COMPRESSION_TYPE function’ »
January 14, 2011, 10:07 pm
I saw an interesting post recently where Greg Rahn talked about HCC mechanics. He claimed that an update to a record stored in HCC format did not require decompressing the whole Compression Unit (CU) which consist of several Oracle blocks. I’m assuming by this he meant that all the records contained in the CU did not get written back to storage in a non-HCC format due to a single record being updated. Greg then showed an example proving row migration occurred for an updated record. He didn’t show that the other records had not been decompressed though. So since I was already working on an HCC chapter for the upcoming Apress Exadata book, I thought I would take time off from the book writing to post this (hopefully the editors will forgive me).
Here’s the recipe: Basically we’ll update a single row, see that its rowid has changed, veify that we can still get to the record via its original rowid, and check to see if the TABLE FETCH CONTINUED ROW statistic gets updated when we we access the row via its original rowid, thus proving basic row migration (this is what Greg has already shown). Then we’ll look at block dumps for the original and new block to see what’s there.
Continue reading ‘EHCC Mechanics – Proof that whole CU’s are not decompressed’ »
November 13, 2010, 1:19 pm
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 <variable> <value>
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 <object_type> for specific help syntax.
<object_type>: {ACTIVEREQUEST | ALERTHISTORY | ALERTDEFINITION | CELL
| CELLDISK | FLASHCACHE | FLASHCACHECONTENT | GRIDDISK
| IORMPLAN | KEY | LUN
| METRICCURRENT | METRICDEFINITION | METRICHISTORY
| PHYSICALDISK | THRESHOLD }
CellCLI> help list FLASHCACHECONTENT
Usage: LIST FLASHCACHECONTENT [<filters>] [<attribute_list>] [DETAIL]
Purpose: Displays specified attributes for flash cache entries.
Arguments:
<filters>: An expression which determines the entries to be displayed.
<attribute_list>: 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.
