Oracle

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…

It looks like 11.2.0.2 has improved most of the original shortfalls of segment creation on demand that were present in 11.2.0.1.

• Segment Creation on Demand (Deferred Segment Creation) in Oracle Database 11g Release 2

Cheers

Tim…

A lot of people have asked me whether there’s some sort of index or “table of contents” of my TPT scripts (there’s over 500 scripts in the tpt_public.zip file – http://tech.e2sn.com/oracle-scripts-and-tools )

I have planned to create such index for years, but never got to it. I probably never will :) So a good way to extract the descriptions of some scripts is this (run the command in the directory where you extracted my scripts to):

A quick post… which is a bit interesting…

As I was searching for Exadata viewlets in Oracle By Example portal (http://goo.gl/HjshR) I clicked on the “Advanced Search”

and then filtered by Product Family (Database) and then by Product…

to my surprise there was an option for “Database 12g”….

Real-Time SQL Monitoring got a lot of air-time when it was released in 11g. I remember being wowed in a number of presentations. What I had failed to notice until recently was a quite interesting update in 11gR2. Previously, the reports always looked a lot prettier in Enterprise Manager than they did in the HTML reports produced by the DBMS_SQLTUNE package. In 11gR2, the introduction of an ACTIVE report means you can now get the EM-style report using the DBMS_SQLTUNE package. Of course, if you are always using EM to look at your SQL monitoring reports, this change is of little value, but if you are using DBMS_SQLTUNE to generate the reports you may want to give it a try because the layout is definitely better. I’ve updated my article on the subject to reflect the changes in 11gR2.

• Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL)

Just goes to show, every time you open the manuals something pops out to make you realize how much you don’t know.

Cheers

Tim…

Real-Time SQL Monitoring got a lot of air-time when it was released in 11g. I remember being wowed in a number of presentations. What I had failed to notice until recently was a quite interesting update in 11gR2. Previously, the reports always looked a lot prettier in Enterprise Manager than they did in the HTML reports produced by the DBMS_SQLTUNE package. In 11gR2, the introduction of an ACTIVE report means you can now get the EM-style report using the DBMS_SQLTUNE package. Of course, if you are always using EM to look at your SQL monitoring reports, this change is of little value, but if you are using DBMS_SQLTUNE to generate the reports you may want to give it a try because the layout is definitely better. I’ve updated my article on the subject to reflect the changes in 11gR2.

• Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL)

Just goes to show, every time you open the manuals something pops out to make you realize how much you don’t know.

Cheers

Tim…