June 6, 2011, 4:35 pm
We’ve been joking around at the office about whether the Exadata X2-8 model has actually been observed in the wild. Some of the guys have been affectionately referring to it as Sasquatch because we’ve never actually seen one. Well we actually got our hands on real one today.
Not as pretty as the X2-2, but as long as it’s fast it doesn’t really matter what it looks like I guess. Thanks to Andy Colvin for the iPhone snap. We’ll be doing some testing with it soon so stay tuned. By the way, we’ve got our T-Shirts on order:
May 30, 2011, 2:53 pm
I ran into a slightly strange hint last week:
insert /*+ append, nologging, parallel(orders_tab, 4) */ …
Anyone know how this hint will behave right off the top of their head?
When I was in school we used to occasionally have quizzes where we were allowed to use our books. So feel free to use any documentation you can find on the topic.
Note: Be sure and see the comments on this one …
UPDATE 01-JUN-11: – Here’s a test case using two easy to verify hints
SYS@SANDBOX1> select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
2 ;
D
-
X
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
d8yaqqyc0yb9k 0 272002086 1 .00 0 No .00 select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.04
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: d8yaqqyc0yb9k
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d8yaqqyc0yb9k, child number 0
-------------------------------------
select /*+ monitor, GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
---------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 |
---------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
19 rows selected.
Elapsed: 00:00:00.04 |
As you can see in the Note section, the GATHER_PLAN_STATISTICS hint was not obeyed (actually it wasn’t even evaluated). This is due to the fact that the parser quits evaluating the hint text when it hits the comma. Here’s the same statement with the comma removed.
SYS@SANDBOX1> select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual;
D
-
X
Elapsed: 00:00:00.01
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
1scryy04ggv60 0 272002086 1 .00 0 No .00 select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.19
SYS@SANDBOX1> select * from table(dbms_xplan.display_cursor('&sql_id','&child_no','allstats'));
Enter value for sql_id: 1scryy04ggv60
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1scryy04ggv60, child number 0
-------------------------------------
select /*+ monitor GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 2 |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 | 1 | 1 |00:00:00.01 | 2 | 2 |
-----------------------------------------------------------------------------------------------------
13 rows selected.
Elapsed: 00:00:00.08 |
Notice that this time the GATHER_PLAN_STATISTICS hint was obeyed and so the xplan output has the A-Rows column and there is no Note section complaining about missing plan statistics. The documentation states that comment text can be interspersed with valid hints though. So why isn’t the comma treated as comment text. Well apparently it is a reserved word. Oddly enough, the word COMMENT is a reserved word as well. Watch this:
SYS@SANDBOX1> select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual;
D
-
X
Elapsed: 00:00:00.00
SYS@SANDBOX1> @fsx
Enter value for sql_text: select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
8dxup58bgaxsy 0 272002086 2 .02 0 No .00 select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Elapsed: 00:00:00.03
SYS@SANDBOX1> @dplan_allstats
Enter value for sql_id: 8dxup58bgaxsy
Enter value for child_no:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8dxup58bgaxsy, child number 0
-------------------------------------
select /*+ monitor COMMENT GATHER_PLAN_STATISTICS */ * from dual
Plan hash value: 272002086
---------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS STORAGE FULL| DUAL | 1 |
---------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
19 rows selected.
Elapsed: 00:00:00.03 |
So the moral is don’t put comment text in your hints. If you must have a comment in your SQL, put it in a separate comment structure like so:
select /*+ monitor GATHER_PLAN_STATISTICS */ /* This is a comment */ * from dual;
And if you really feel you need commas between your hints try something like this:
select /*+ monitor"," GATHER_PLAN_STATISTICS */ * from dual;
May 9, 2011, 10:35 am
I turned in the first draft of my last chapter in Expert Oracle Exadata last weekend. Tanel and Randy are working on their last chapters so we should be done with the basic material in a matter of days. We’ll still have a fair amount of editing work to do, but that’s a lot easier than coming up with the first pass at the material (which feels a little like giving birth). So as a reward to myself I bought a new guitar.
The picture’s a little dark, but check out the awesome paisley case in the background. I chickened out and didn’t play it on Sunday at my church gig. (takes a little while to get used to a new guitar don’t you know) Maybe next week. ;)
May 4, 2011, 11:09 am
I spoke at an Oracle marketing event in San Antonio last night. Here is a link to the promotional page for the event (it will probably disappear soon). I promised to make a copy of my slides available so here it is:
April 21, 2011, 8:34 pm
I finally took a little time to update the page with some of the presentations I’ve done over the last couple of years. You can get there via the menu bar above or by clicking here: White Papers/Presentations
Maybe some day I’ll have time to organize all the scripts.
