Oracle

Oracle Database XE (Express Edition) 11.2 Beta has been released!

Here you go:

• http://www.oracle.com/technetwork/database/express-edition/11gxe-beta-download-302519.html

The documentation is here (the link at oracle.com/technetwork is broken right now…)

I’m sure you want to know about it’s limitations, these are documented in the license guide above, but here are the main figures from Oracle XE 11.2 license guide:

1. Express Edition is limited to a single instance on any server;
2. Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
3. Express Edition may only be used to support up to 11 GB of user data (not including Express Edition system data);
4. Express Edition may use up to 1 GB RAM of available memory.

I am pleased to announce that we, the program committee members, just finalized the program for the 6th Planboard DBA Symposium to be held at May 17 2011 in Amstelveen. There are 10 presentations scheduled in 2 parallel tracks by Frits Hoogland, Johan van Veen, Iloon Ellen-Wolff, Paul der Kinderen, Wouter Wethmar, Alex Nuijten, Gert […]

I’m very excited to announce this somewhat old news — we have a brand new Oracle ACE at Pythian — Yury Velikanov. Fantastic addition to Pythian’s team of ACEs and ACE Directors — Fahd Mirza, Gwen Shapira, Jared Still, Christo Kutrovsky and myself. If you want to know more about Oracle ACE Program, the latest issue of Oracle Magazine has an article written by the fellow Oracle ACE Mike Riley — Everything Is Coming Up ACEs!.

I’ve known Yury for a while as I met him online on Oracle-L many years ago. The world is small and after living on different continents (North America and Europe), providence put us together on the third continent — Australia. As I was building our Australian business in Sydney, it took me a year to convince Yury to join Pythian. But I tell you it was worth the efforts!

Yury is one of our top Senior Oracle Apps DBAs and he is also extremely capable when it comes to Oracle core database technology and middleware which you would expect from a very good Apps DBA. A decade ago, Yury was already fusing Oracle E-Business Suite with Oracle RAC when most people couldn’t even afford to think about it. In 2003, Yury became one of the first five Oracle Certified Masters in Europe. In other words, Yury is very talented individual with top notch Oracle EBS and Oracle database skills.

As you probably know, excellent technical skills are not the only required quality of an Oracle ACE. Oracle ACEs are distinguished for their contributions to the community and Yury has always been an enthusiastic Oracle expert willing to both share his knowledge and learn from others. As a result, he is an active contributor to Oracle community, regionally and globally.

Yury founded Oracle e-Business Suite related mailing list several years ago to help Oracle Apps DBAs around the world exchange their experiences and help others. In Australia, Yury has become an organizer of the Sydney Oracle Meetup – an informal group of Oracle professionals meeting regularly face to face.

Yury also has a long list of conferences he presented at including Hotsos Symposium, UKOUG, AUSOUG and others. Recently, Yury has also started blogging on Pythian Blog and is planning to do more and more.

I would like to note that Yury is a person who never refuse to help — he will lose sleep, work 42 hours per day if need but won’t step away if asked for help. I’ve been there myself and I know that he is a very approachable individual and will do whatever it takes.

After all this, how could I not nominate him as an Oracle ACE? Right. There was no way! Now few months late Yury is happily joined our Oracle ACE Team at Pythian. It’s actually happened a few weeks ago, it just took me so long to write about it!

Ok, it’s official – the first and only Oracle Troubleshooting TV show is live now!

The first show is almost 2 hours about the ORA-4031 errors and shared pool hacking. It’s a recording of the US/EMEA timezone online hacking session I did some days ago.

There are a couple of things to note:

1. The text still isn’t as sharp as in the original recording, but it’s much better than in my previous upload attempts and is decently readable. I’ll try some more variations with my next shows so I hope the text quality will get better! Or maybe I should just switch to GUI tools or powerpoint slides? ;-)
2. You probably should view this video in full screen (otherwise the text will be tiny and unreadable)
3. There’s advertising in the beginning (and maybe end) of this show! I’ll see how much money I’ll make out of this – maybe these shows start contributing towards the awesome beer selection I’ll have in my fridge some day (right now I have none). Viewing a 30-sec advert is small price to pay for 2 hours of kick-ass shared pool hacking content !!!
4. You can download the scripts and tools used in the demos from http://tech.e2sn.com/oracle-scripts-and-tools/
   
5. Make sure you check out my online Oracle troubleshooting seminars too (this April and May already)

View the embedded video below or go to my official Oracle Troubleshooting TV show channel:

http://tanelpoder.blip.tv

Enjoy!

You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:

I was working on an external table today and fat fingered something. This is the error report-

Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!

Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!

Apparently some of the Oracle developers have a sense of humor.

By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:

 
SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause:  User attempted on operation on an external table which is
//          not supported.
// *Action: Don't do that!

People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)

I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.

So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).

Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):

Check it out and if you like MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!

• http://www.oracle-developer.net/utilities.php

Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code… Note that MOATS is still kind of beta right now…

P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)

P.P.S. Have you already figured out how it works?! ;-)

Update: Now you can suggest new features and improvement requests here:

• http://goo.gl/mod/0ANl

Last week the email account associated with my blog amassed no less than 83 emails from readers asking what I’m up to in response to the cliff-hanger I left in my post entitled Will Oracle Exadata Database Machine Eventually Support Offload Processing for Everything?

I appreciate all the email and I regret I was unable to answer any of them as I was taking some time away with my family.

I’ve resigned from my position of the last 4 years as performance architect in Oracle’s Exadata development organization and have joined the EMC Data Computing Division to focus on Greenplum in a performance engineering role.  While this is a big and exciting news piece for me personally, I need to make this a small and quick blog entry at this time.

Filed under: oracle

As I set out to make this blog entry I considered using the “OT” (off-topic) preface so as to respect readers’ time in case this ended up seeming like a SPAM entry. After typing for a moment I realized this is completely on-topic.  Consider the following quotes from the below-referenced web news pieces (bold font added for effect):

The central computer system for Alaska Airlines […]

We are working to restore the computer system and to accommodate our passengers […]

The computer system is used to plan all flights […]

A statement posted on the airline’s website said technical specialists had made some progress in restoring the system since it first went down at 3 a.m. […]

All of you who are regular readers of this site know why I highlighted certain words in bold font!

Why isn’t there any news yet questioning the obvious lack of business continuity systems, procedures, operations, switch-over to whatever redundant system Alaska Airlines /Horizon Air must certainly have in place?

Here is my take on the computer crash that crashed spring break. Please give it a read and then, perhaps, comment on the DR/BC failure that put this whole blog entry into motion:

Alaska Airlines / Horizon Air Computer Crash Crashes Spring Break

References:

CNN coverage of the Alaska Airlines / Horizon Air Computer Infrastructure Meltdown

http://www.businessweek.com/ap/financialnews/D9M72I4O0.htm

Filed under: oracle

Q: How do you tune Exadata?
A: Look for long running queries that are not using Smart Scans and then fix them so they do.

We’ve worked on a bunch of Proof of Concepts (POC’s) for customers over the last year or so. These usually involve loading a few terabytes of data and running a bunch queries or some other workload on the data. Generally speaking, anything we have thrown at Exadata has blown the doors off of the platforms that the applications were previously running on. But occasionally we run into a situation where the speed up is just not what we’ve come to expect. Generally speaking it’s because we’ve done something that has kept Exadata from doing what it does best – Smart Scans. While my lead in is obviously a tongue in cheek over simplification, it is basically true. Unfortunately, it’s not as easy as it sounds to determine whether a statement has been offloaded, because our main tool for looking at how a statement was executed (the execution plan) doesn’t tell us whether a Smart Scan was used or not. So in this post, my intent is to give you a couple of options for determining whether Smart Scans are happening or not. Here’s a quick example showing a basic execution plan:

 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
SANDBOX1         24-MAR-2011 16:19 25-MAR-2011 22:57    1.28     110283
 
SYS@SANDBOX> 
SYS@SANDBOX> set timing on
SYS@SANDBOX> @avgskew3
SYS@SANDBOX> select avg(pk_col) from kso.skew3
  2  where col1 > 0
  3  /
 
AVG(PK_COL)
-----------
 16093750.2
 
Elapsed: 00:00:34.80
SYS@SANDBOX> select sql_id, sql_text from v$sql
  2  where sql_text like 'select avg(pk_col) from kso.skew3 where col1 > 0';
 
SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------------
4p62g77m9myak select avg(pk_col) from kso.skew3 where col1 > 0
 
Elapsed: 00:00:00.14
SYS@SANDBOX> @dplan
Enter value for sql_id: 4p62g77m9myak
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4p62g77m9myak, child number 0
-------------------------------------
select avg(pk_col) from kso.skew3 where col1 > 0
 
Plan hash value: 2684249835
 
------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |       |       |   535K(100)|          |
|   1 |  SORT AGGREGATE            |       |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW3 |   383M|  4028M|   535K  (1)| 01:47:02 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1">0)
       filter("COL1">0)
 
 
20 rows selected.
 
Elapsed: 00:00:00.22

The storage line in the predicate section indicates that a Smart Scan is possible, but it doesn’t actually tell us that one occurred. So how can you tell. Well there are several ways.

1. You can Millsap it. (generate a 10046 trace)
2. You can Poder it. (use Tanel Poder’s snapper script to check stats and wait events)
3. You can Wolfgang it. (generate a 10053 trace) – well actually this doesn’t work since the optimizer doesn’t know whether a statement will do a Smart Scan or not.
4. Or you can look in v$sql – I wrote a little script called fsx.sql (short for Find_Sql_eXadata.sql) to do that.

I think that tracing is the most foolproof way to verify a Smart Scan (just look for “cell smart table/index scan” wait events). But it can be a little cumbersome to generate a trace and then find it. (Note: Method-R has a great tool to make this easier called MR Trace which is a plug in for Oracle’s SQL Developer). Tanel’s snapper script is an awesome tool that is very versatile – so it’s a very valid option as well. But both of these methods depend on the fact that you can catch the statement of interest while it is executing. They provide no way of looking back at statements that ran in the past. My fsx script is not nearly as comprehensive as either of these approaches, but it has an advantage in that it looks at values stored in v$sql (which are also captured in AWR by the way). This allows us to do analysis that is not limited to what is happening right now. (i.e. we don’t have to catch the query while it’s running).

So how does it work?

The v$sql view contains a column (IO_CELL_OFFLOAD_ELIGIBLE_BYTES) which tells us whether this child has been offloaded or not. Very simply, if the column contains a value greater than 0, then the statement was processed with a Smart Scan. Here’s the basic trick:

   decode(IO_CELL_OFFLOAD_ELIGIBLE_BYTES,0,'No','Yes') Offload 

So basically the fsx script just does a decode on the column and if it’s value is 0 then it returns ‘No’, otherwise it returns ‘Yes’. The script does a few other things too like attempting to calculate the savings in terms of reduced volume of data being transferred across the InfiniBand fabric that the Smart Scan was responsible for. I called the output column IO_SAVED_% although it’s not really I/O, it’s bytes transferred between the storage layer and the database layer. There are some situations where the results of this calculation don’t seem to make much sense, by the way. But that’s too much to go into here. (There are more details in our upcoming Apress Exadata Book if you’re so inclined.) Anyway, here’s a quick example of using the fsx script.

SYS@SANDBOX> @fsx
Enter value for sql_text: select avg(pk_col) from kso.skew3%
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0

So that’s pretty easy and straight forward. You can enter a bit of a SQL statement’s text or a SQL_ID or both to locate statements of interest in v$sql. The script’s output will tell you whether it was offloaded or not. But what if we want to get a feel for how the whole system is running with regard to Offloading. We could simply add a couple of additional where clauses to the fsx script to allow us to limit the rows returned based on whether the statements were offloaded or not and maybe add a filter on average execution time as well so we can just look at the long running statements. Have a look at fsxo.sql which does just that.

SYS@SANDBOX> @fsxo
Enter value for sql_text: 
Enter value for sql_id: 
Enter value for min_etime: 10
Enter value for offloaded: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
09m6t5qpgkywx      0 1885411402      1     116.79      0 No             .00 select /*+ bloom join 2  use_hash (skew temp_skew) */ a.col2, sum(a.co
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0
5zruc4v6y32f9      0          0      2     362.05      0 No             .00 DECLARE job BINARY_INTEGER := :job;  next_date TIMESTAMP WITH TIME ZON
b6usrg82hwsa3      0          0      2     305.30      0 No             .00 call dbms_stats.gather_database_stats_job_proc (  )
 
6 rows selected.
 
SYS@SANDBOX> @fsxo
Enter value for sql_text: 
Enter value for sql_id: 
Enter value for min_etime: 
Enter value for offloaded: YES
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
0qa98gcnnza7h      0  568322376      2       3.56      0 Yes        -905.77 select avg(pk_col) from kso.skew where col1 > 0
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
266gctwscrnn2      0  568322376      3       1.06      0 Yes        -805.04 select /*+ result_cache */ avg(pk_col) from kso.skew where col1 > 1
2uzgbm8azqqv3      0 2974987230      2       1.56      0 Yes          71.79 select avg(pk_col) from kso.skew_encrypt where col1 > 0
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0
d15cdr0zt3vtp      0   62424106      1        .31      0 Yes          99.96 SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:
 
7 rows selected.
 
SYS@SANDBOX> @fsxo
Enter value for sql_text: %skew%
Enter value for sql_id: 
Enter value for min_etime: 5
Enter value for offloaded: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
09m6t5qpgkywx      0 1885411402      1     116.79      0 No             .00 select /*+ bloom join 2  use_hash (skew temp_skew) */ a.col2, sum(a.co
1nfa7trushhpm      0 2684249835      2      15.87      0 Yes          74.60 select /*+ result_cache */ avg(pk_col) from kso.skew3 where col1 > 1
35q8ahgw2xhsp      0 3734762968      1      37.12      0 Yes          34.22 select /*+ bloom join  use_hash (skew temp_skew) */ a.col2, sum(a.col1
399m90n8jzpu6      0 1923773943      2       5.09      0 No             .00 select avg(pk_col) from kso.skew
4p62g77m9myak      0 2684249835      2      18.31      0 Yes          71.85 select avg(pk_col) from kso.skew3 where col1 > 0

So the idea is to be able to take a high level look at what’s being offloaded and what’s not. Obviously you can do the same thing with AWR data for a longer look back in history. But I’ll leave that as an exercise for the reader. Note that my lab system was recently bounced and so the number of statements is very small. On larger production systems you will probably want to limit yourself to longer running statements.

By the way, there is another tool that can be very helpful in determining if a statement was offloaded, DBMS_SQLTUNE.REPORT_SQL_MONITOR. But I’ll have to save that for another post.

There’s a neat new feature for editioning in 11.2.0.2 that allows you to associate an edition with a service. I’ve added it to the end of my Edition-Based Redefinition article here.

Cheers

Tim…