Archive for the ‘Oracle’ Category.
July 6, 2011, 9:13 am
I will be participating in an Exadata Virtual Conference which has been organized by Tanel Poder on August 3rd and 4th. This conference will follow the same format as the one Tanel and I did last year with Jonathan Lewis and Cary Millsap. It will be two half days which should provide some flexibility for people with busy schedules. The online format allows all participants to interact directly via chat while the speakers are presenting and then via voice during question and answer sessions. This is a great opportunity to talk to some guys that have done a bunch of work with Exadata. Andy Colvin will be discussing patching which has been problematic for some shops. Andy has done more patching than anyone I know. Randy Johnson will be discussing Resource Management on Exadata which is a key to successful consolidation projects. I will be talking about how Smart Scans work under the covers and covering techniques for determining when and where they are (or are not) occurring. Tanel will be covering in depth tuning and diagnostic techniques specific to Exadata. Of course, Randy, Tanel and I collaborated on the upcoming Apress book, Expert Oracle Exadata, which should be out a couple of weeks before the conference. Here’s a link to the page with all the conference details:
Exadata Virtual Conference
Note that there is a discount for early registration. I hope to see you there.
June 14, 2011, 7:42 am
Here’s a link to brand new blog by a very experienced Exadata guy. Andy has done a bunch of Exadata projects over the last year and a half and has done more patching than anyone I know. Should be some interesting info coming from him. His first post is about an X2-8 that he is currently working on (with pictures). And here’s the link to the home page on his blog:
Andy Colvin’s Oracle Blog
Check him out.
June 11, 2011, 9:32 pm
Well my lone abstract submission didn’t get selected at Open World this year. But apparently they have a second chance system where you can “Suggest a Session” and users can vote on which papers they’d like to see on the agenda. I went ahead and suggested “Tuning Exadata” – It sounds like something you shouldn’t have to do, but remember that Exadata is not an appliance that has few or no knobs to turn. It has all the power and options of an Oracle database and there are certainly things that you can do wrong that will keep Exadata from performing at its best. So the paper is about how you can know for sure whether Exadata is doing what it should and how to coerce it if you need to.
The mix.oracle.com site where this voting is supposed to take place is a little difficult to navigate (in my humble opinion) so here’s a direct link to the page where you can see the abstract (and vote if you deem it worthy). ;)
You will have to log in with your Oracle Single Signon account (what you use for My Oracle Support – or Metalink for the old guys) or I think you can create an separate account if you want. By the way, Andy Colvin has submitted an abstract for a talk on Exadata Patching, which should be very informative if it gets picked. He’s done more Exadata patching than anyone I am aware of. Here’s a link to his abstract:
There will undoubtedly be many deserving abstracts. For example, several of my OakTable brethren have suggested sessions as well. So please look around the site for others of interest as well. You can vote for as many as you want.
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;
