Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

High Performance Oracle 11g in the Amazon Cloud

Jeremiah Wilton will be presenting High Performance Oracle 11g in the Amazon Cloud at Collaborate 2010 – an updated version of his February RMOUG presentation.  For a preview, you can find both the white paper and presentation slides from RMOUG on our white paper page.  Currently scheduled for Monday, April 19, the session abstract reads:

The Amazon Elastic Computing Cloud is rapidly gaining acceptance as an enterprise-class Oracle platform. In this virgin territory, Oracle professionals need a complete understanding of cloud computing concepts and architectures. This session addresses the basics, and goes further, providing guidance on how best to optimize and configure Oracle for performance, stability and manageability in the cloud. Gain a complete understanding of Cloud Computing Learn the details of Amazon’s Elastic Compute Cloud Develop the knowledge needed to deploy and effectively manage high-performance Oracle services on Amazon EC2.

RT @gwenshap – What a Difference a Month Makes

Apparently its a “tweet” so I because I am not so handy I just “re-tweet” this stuff…(far too lazy to login on twitter / or however this works)

;-)

Its March 22. Exactly one month ago, I came back from few days spent in Colorado. I gave presentations, met amazing people, enjoyed skiing and drank a lot of beer, wine and whiskey. I also barely made it back, but that’s a different story.

Obviously I drank too much, or maybe I gave too many presentations. I don’t remember.

What I do know is that in the month since that eventful weekend, my life has taken a sharp turn.

I’m about to start working for Pythian. There will be a separate post about that, where I explain how amazing Pythian is and how to continue to follow my blog once it is merged with the official Pythian blog. Same hypothetical blog post will also include comments about how much I love my current colleagues and how sorry I am to leave them.

But more important – I’m now an Oakie. Seriously. Check it out Can you tell I’m stoked?

What a Difference a Month Makes

Its March 22. Exactly one month ago, I came back from few days spent in Colorado. I gave presentations, met amazing people, enjoyed skiing and drank a lot of beer, wine and whiskey. I also barely made it back, but that’s a different story.

Obviously I drank too much, or maybe I gave too many presentations. I don’t remember.

What I do know is that in the month since that eventful weekend, my life has taken a sharp turn.

I’m about to start working for Pythian. There will be a separate post about that, where I explain how amazing Pythian is and how to continue to follow my blog once it is merged with the official Pythian blog. Same hypothetical blog post will also include comments about how much I love my current colleagues and how sorry I am to leave them.

But more important – I’m now an Oakie. Seriously. Check it out :) Can you tell I’m stoked?

This is not really a blog post, more of a tweet really. But I had to share the news.

5th Planboard DBA Symposium Featuring Jonathan Lewis

I am pleased to announce that we, the program committee members, just finalized the program for the 5th Planboard DBA Symposium to be held at June 8 in Driebergen. Because this is the 5th edition we wanted to bring you something special and I am proud to announce that Jonathan Lewis is going to talk [...]

Oracle Session Snapper v3.10

Hi all, long time no see! =8-)
Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.
The first is Oracle Session Snapper version 3!
There are some major improvements in Snapper 3, like ASH style session activity sampling!

Oracle Session Snapper v3.10

Hi all, long time no see!  =8-)

Now as I’m done with the awesome Hotsos Symposium (and the training day which I delivered) and have got some rest, I’ll start publishing some of the cool things I’ve been working on over the past half a year or so.

The first is Oracle Session Snapper version 3!

There are some major improvements in Snapper 3, like ASH style session activity sampling!

When you troubleshoot a session’s performance (or instance performance) then the main things you want to know first are very very simple:

  1. Which SQL statements are being executed
  2. What are they doing, are they working on CPU or waiting.
  3. If waiting, then for what

Often this is enough for troubleshooting what’s wrong. For example, if a session is waiting for a lock, then wait interface will show you that. If a single SQL statement is taking 99% of total response time, the V$SESSION (ASH style) samples will point out the problem SQL and so on. Simple stuff.

However there are cases where you need to go beyond wait interface and use V$SESSTAT (and other) counters and even take a “screwdriver” and open Oracle up from outside by stack tracing :-)

When I wrote the first version of Snapper for my own use some 4-5 years ago I wrote it mainly having the “beyond wait interface” part in mind. So I focused on V$SESSTAT and various other counters and left the basic troubleshooting to other tools. I used to manually sample V$SESSION/V$SESSION_WAIT a few times in a row to get a rough overview of what a session was doing or some other special-purpose scripts.

However after Snapper got more popular and I started getting some feedback about it I saw the need for covering more with Snapper, not just the “beyond wait interface” part, but also the “wait interface” and “which SQL” part too.

So, now I’m presenting Snapper v3 which does all the 3 points above using ASH style V$SESSION sampling and it still has the first step to “beyond wait interface” part in it, which is very useful for advanced performance troubleshooting and diagnosis – I’m talking about the V$SESSTAT counters above.

I’ve made some syntax changes in Snapper too and right now the v3 doesn’t work on Oracle 9.2 (it will work some day :)

To give you an idea of the new ASH style sampling capabilities, heres some example output:

SQL> @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all
Sampling...
-- Session Snapper v3.10 by Tanel Poder @ E2SN ( http://tech.e2sn.com ) --------------------------------------------------------------
Active% |    SID | EVENT                     | WAIT_CLASS
-------------------------------------------------------------- 100% |    133 | db file scattered read    | User I/O 5% |    165 | control file parallel wri | System I/O 2% |    162 | ON CPU                    | ON CPU 2% |    167 | db file parallel write    | System I/O 2% |    166 | log file parallel write   | System I/O
--------------------------------------------------- Active% | PLSQL_OBJE | PLSQL_SUBP | SQL_ID --------------------------------------------------- 77% |            |            | a5xyjp9gt796s 23% |            |            | 4g4u44bk830ms 12% |            |            |
------------------------------------------------------------------------------------------- Active% | PROGRAM                   | MODULE                    | ACTION ------------------------------------------------------------------------------------------- 100% | sqlplus@mac01 (TNS V1-V3) | sqlplus@mac01 (TNS V1-V3) | 5% | oracle@solaris02 (CKPT)   |                           | 2% | oracle@solaris02 (DBW0)   |                           | 2% | oracle@solaris02 (CJQ0)   |                           | 2% | oracle@solaris02 (LGWR)   |                           | --  End of ASH snap 1, end=2010-03-22 17:35:06, seconds=5, samples_taken=43

You can read some usage examples and download it here:

P.S. People who attended Hotsos Symposium Training Day where I demoed the initial version of Snapper v3 – download the new version from above link (v3.10), it’s much more flexible than the one I demoed couple of weeks ago!

#ff0000;">#000000;">

Share/Bookmark

VST - Visual SQL Tuning , the next generation

If you saw my presentation at HOTSOS 2010 or attended one of my recent Oracle 10g Performance Tuning course, you know I'm excited about Visual SQL Tuning or VST diagrams.

Now, I'm even more excited because we are adding statistics to the diagram! For example:
In the above diagram the #s in red are the two table join set sizes and the numbers in green are the table sizes. I can see immediately that the query should return 0 rows because the join (rentalitem,movierental) returns 0 rows. I can also see that the join (mr,movierental) is a horribly inefficient join it returns over 1 million rows for a join between 2018 rows and 2018 rows.
The VST diagram tells me immediately what's happening in the query and now with the statistics I can immediately see where there are efficient joins and inefficient joins.
The text for this query, which has none of this information is:
SELECT
cs.customerid,
cs.firstname,
cs.lastname,
mr.rentalid,
mr.duedate,
mr.totalcharge,
ri.itemnumber
FROM
MOVIES.customer cs,
MOVIES.movierental mr,
MOVIES.rentalitem ri
WHERE
LENGTH (cs.lastname) = 5 AND
cs.zip > 75062 AND
1 <>
cs.phone BETWEEN 9625569900 AND 9999569900 AND
ROUND (ri.rentalid) > 10 AND
TRUNC (ri.itemnumber) > 1 AND
mr.totalcharge > (SELECT AVG (totalcharge)
FROM MOVIES.movierental) AND
mr.CUSTOMERID = cs.CUSTOMERID AND
ri.RENTALID = mr.RENTALID
Stay tuned because next week we will be adding filter ratios to the diagram. A filter ratio is the ratio of rows returned after a predicate filter has been applied to a table. In the above diagram CUSTOMER and RENTALITEM both have predicate filters as denoted by the "F" in green.
In DB Optimizer the diagram is context sensitve so if I click on a table in the diagram the text will be highlighted
VST diagrams are available now in DB Optimizer 2.0 but in May we are scheduled to release VST diagrams with displayed statistics in with DB Optimizer 2.5 !
Big thanks to DB Optimizer's development team for this awesome work. Special kudo's to Stan who's recuperating and itching to even start working again even from his bed!

Detecting Corrupt Data Blocks

Or more accurately, how not to detect corrupt data blocks.

This thread on Oracle-L is regarding lost writes on a database.

One suggestion was made to use the exp utility to export the database, thereby determining if there are corrupt blocks in the database due to disk failure. I didn't give it much thought at first, but fellow Oak Table member Mark Farnham got me thinking about it.

Using exp to detect corrupt blocks, or rather, the absence of corrupt blocks may work, but then again, it may not. It is entirely possible to do a  full table scan on a table successfully, as would happen during an export, even though the blocks on disk have been corrupted.

This can be demonstrated by building a table, ensuring the contents are cached, then destroying the data in the data file, followed by a successful export of the table.

Granted, there are a lot of mitigating factors that could be taken into consideration as to whether or not this would happen in a production database. That's not the point: the point is that it could happen, so exp is not a reliable indicator of the state of the data files on disk.

This test was performed on Oracle 10.2.0.4 EE on RH Linux ES 4. Both are 32 bit.

First create a test tablespace:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create tablespace lost_write datafile '/u01/oradata/dv11/lost_write.dbf' size 1m
extent management local
uniform size 64k
/

Next the table LOST_WRITE is created in the tablespace of the same name. This will be used to test the assertion that a successful export of the table can be done even though the data on disk is corrupt.

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">create table lost_write
cache
tablespace lost_write
as
select * from dba_objects
where rownum <= 1000
/

begin
dbms_stats.gather_table_stats(user,'LOST_WRITE');
end;
/

select tablespace_name, blocks, bytes
from user_segments
where segment_name = 'LOST_WRITE'
/


TABLESPACE_NAME BLOCKS BYTES
------------------------------ ---------- ----------
LOST_WRITE 16 131072

1 row selected.

Next, do a full table scan and verify that the blocks are cached:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">select * from lost_write;

Verify in cache:
select file#,block#,class#, status
from v$bh where ts# = (select ts# from sys.ts$ where name = 'LOST_WRITE')
order by block#
/

FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
40 2 13 xcur
40 3 12 xcur
40 9 8 xcur
40 10 9 xcur
40 11 4 xcur
40 12 1 xcur
40 13 1 xcur
40 14 1 xcur
40 15 1 xcur
40 16 1 xcur
40 17 1 xcur
40 18 1 xcur
40 19 1 xcur
40 20 1 xcur
40 21 1 xcur
40 22 1 xcur
40 23 1 xcur


Now swap the bytes in the file, skipping the first 2 oracle blocks
Caveat: I don't know if that was the correct # of blocks, and I didn't spend any time trying to find out
Also, I belatedly saw that count probably should have been 22 rather than 16, but the results still served the purpose of corrupting the datafile, as we shall see in a bit.

What this dd command is doing is using the same file for both input and output, and rewriting blocks 3-18, swapping each pair of bytes.

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">dd if=/u01/oradata/dv11/lost_write.dbf of=/u01/oradata/dv11/lost_write.dbf bs=8129 skip=2 count=16 conv=swab,notrunc

The effect is demonstrated by this simple test:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">jkstill-19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out

Now we can attempt the export:

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">exp tables=\(jkstill.lost_write\) ...

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table LOST_WRITE 1000 rows exported
Export terminated successfully without warnings.
19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out

So, even though the data on disk has been corrupted, the export succeeded. That is due to the table being created with the CACHE option, and all the blocks being cached at the time of export. It may not be necessary to use the CACHE option, but I used it to ensure the test would succeed.

Now let's see what happens when trying to scan the table again. First the NOCACHE option will be set on the table, then a checkpoint.

#eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;">10:42:45 dv11 SQL> alter table lost_write nocache;

10:43:02 dv11 SQL> alter system checkpoint;

Now try to scan the table again:

10:43:14 ordevdb01.radisys.com - js001292@dv11 SQL> /
select * from lost_write
*
ERROR at line 1:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: '/u01/oradata/dv11/lost_write.dbf'

A corollary conclusion can drawn from this example.

If you do discover bad data blocks, you just might be able to do an export of the tables that are in the affected region before doing any recovery. This might be a belt and suspenders approach, but DBA's are not generally being known for taking unnecessary chances when possible data loss is on the line.

Test to verify feed settings

Feed transfer did not apparently work.

VMWare Hires Redis Key Developer – But Why?

My friend MosheZ alerted me to the fact (which few hours later appeared all over the net) that VMWare hired Redis key developer. Which is as close to acquisition as you can get with an open source project.

What is Redis? Redis is yet-another-NoSQL. A key-value store, somewhat similar to Tokyo Cabinet. Except that Redis does persistence differently, which makes it faster in many cases. Redis started as a Memcached replacement, so a lot of the documentation describes it as follows: Redis is like Memcached, except it supports more data types, it is persistent to some degree and it is not distributed.

But the more interesting question is – Why does VMWare need Redis?

VMWare says: “As VMware continues its investments in the context of cloud computing, technologies such as Redis become key for future cloud based apps, whether private or public cloud, and the cloud infrastructure itself.”

So Redis is cloud and VMWare is a major cloud player, therefore VMWare needs redis. Two discrepancies stand out in this story:

  1. Redis is not distributed system. Unlike Cassandra, where you can scale by quickly adding more Cassandras to the party, Redis is just one (very fast) server, only supporting master-slave replication. VMWare is all about adding new machines quickly. Something doesn’t fit.
  2. While key-value stores are  cloudy and VMWare is cloudy, there is no natural match between their cloudiness. VMWare itself can’t use Redis – because Redis technology is a natural match for big-data websites which VMWare clearly isn’t. Some VMWare customers can benefit from Redis, but most can’t. What’s going on here?

Clearly, the place to look is not in existing value but in the future. So here are my predictions:

  1. Redis will become distributed. It can certainly be done. Perhaps it can even be done without losing it’s performance edge.
  2. VMware will announce an Amazon-like, cloud-for-rent service. They have the technology for this, and Redis will help them manage the “huge website” part of it.
  3. They may also offer Redis on top of the virtual servers, as something built in. Like Amazon’s Oracle servers.
  4. VMWare can also offer storage for rent. They can do it with EMC storage (since VMWare is an EMC company), but I’m betting that they’ll do it with Netapp – their favorite cloud partner.  I can totally imagine a near-future Netapp-Vmware offering that is similar to Amazon’s EC2+ S3+AWS.

Predicting is very difficult (especially about the future) and I’m very much ready to regret ever posting my day dreams in public, but these are exciting possibilities. I wonder if they make sense to anyone else.

*********************************

And speaking of MosheZ, he is a prolific song writer, and he wrote a song about DBAs! I’m thinking of performing it live during one of my presentations. Actually I’m thinking of writing a presentation “How to win arguments or influence users” just to have an excuse to sneak this song in :)