Search

OakieTags

Who's online

There are currently 0 users and 27 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

Interview with Kyle Hailey in oracle-class.com

Interview appears in  www.oracle-class.com:


 

1- Why did you choose to specialize in Oracle databases?

I wanted to live in San Francisco and Oracle was the only company in 1989 recruiting on campus at Cornell from San Francisco. Living in San Francisco was my goal and Oracle got me there. At the time I thought databases were boring having majored in physics and mathematics. I had wanted to work on numerical models of trading systems and arbitrage calculations.

2- What made you specialize in Oracle Performance Tuning?

I like finding patterns in quantitative data whether it’s the stock market or databases. Oracle performance tuning has given me this opportunity.

3- Do you still remember the first day in your first job?

Not really. I do remember though starting in support and thinking how crazy it was answering customer phone calls acting like I was an expert and I’d never used Oracle!

4- Is there anybody you have regarded as role model at the beginning of your career?

Funny enough I went to high school with Micheal Dell, but I’d hardly say he was an inspiration. I was also in the computer science building at Cornell when Robert Morris released the first major internet worm in 1988, but that wasn’t much of an inspiration either, though we did go over his computer attacks in my operating system class the next day!

I’ve found several people inspirational. I met two of my biggest inspiration at the same time and place which was the Europecar Oracle 7.0 parallel server site in 1994. The two people were Roger Saunders from Oracle and Jan Simon Pendry from Sequent.
Roger had written and was using code to attach to the SGA and collect information much similar to active session history and this was almost a decade before Oracle did it officially. Roger was years ahead of the curve and his work blew me away and inspired me.
Jan Simon had never worked with Oracle before Europecar but with in a week he’d figured out the Oracle trace files, how to parse them and had created a stress test benchmarking system based on the trace files. On top of that, he wrote the first graphic monitoring tool I’d ever seen which led to my beginning to write graphic performance tools. Both Roger and Jan Simon changed my career.

5- What are the resources that you consult regularly to keep up-to-date with Oracle topics?

Oaktable email list and the Oaktable blog roll.

6- 3 Books that you would recommend reading to every Oracle professional?

Trouble Shooting Oracle Performance – Cristian Antognini
- best overall Oracle performance book
Oracle Wait Interface – Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan
- best book on Oracle wait interface which is crucial to Oracle performance tuning
Practical Oracle 8i- Jonathan Lewis
- old book but one of the best for understanding how Oracle works, what the important features are and most importantly for understanding how to boundary test new features to see if the will meet your needs or break before investing time and becoming dependent on a feature that might not be sufficient.

7- What was the biggest technical challenge in your career so far?

By far my biggest challenge has been and still is selling my ideas to people. My ideas have been successful but there has been a lot missed opportunities. I offered my ideas for performance tuning to Quest who turned them down, but Oracle picked them up in OEM 10g and it was a great success. At Oracle I had many more ideas that were turned down but then Embarcadero picked them up and they were successful in DB Optimizer. At Embarcadero I had more ideas that were turned down and now Delphix is picking them up. I wish I could just create a Steve Jobs reality distortion field and enroll people into my ideas. It would be more fun, efficient and stable!

8- How do you disconnect from work in your spare time?

That’s tough. I think about work too much. Connecting with my little boy is the best way.

9- What advise would you give to somebody who just started studying computer science?

Tough to say. I wish I had majored in computer science but that was 25 years ago. Now I’m not sure I’d still want to major in computer science. Computer usage is becoming ubiquitous. I’d focus more on current problems such as how to mine data and visualize data. Actually if I was in school now I’d be more interested entrepreneurship or possibly biotech and nanotech.

 

An introduction to collectl

Some of you may have seen on twitter that I was working on understanding collectl. So why did I start with this? First of all, I was after a tool that records a lot of information on a Linux box. It can also play information back, but this is out of scope of this introduction.

In the past I have used nmon to do similar things, and still love it for what it does. Especially in conjunction with the nmon-analyzer, an Excel plug in it can create very impressive reports. How does collectl compare?

Getting collectl

Getting collectl is quite easy-get it from sourceforge: http://sourceforge.net/projects/collectl/

The project website including very good documentation is available from sourceforge as well, but uses a slightly different URL: http://collectl.sourceforge.net/

I suggest you get the archive-independent RPM and install it on your system. This is all you need to get started! The impatient could type “collectl” at the command prompt now to get some information. Let’s have a look at the output:

$ collectl
waiting for 1 second sample...
#<--------CPU--------><----------Disks-----------><----------Network---------->
#cpu sys inter  ctxsw KBRead  Reads KBWrit Writes   KBIn  PktIn  KBOut  PktOut
1   0  1163  10496    113     14     18      4      8     55      5      19
0   0  1046  10544      0      0      2      3    164    195     30      60
0   0  1279  10603    144      9    746    148     20     67     11      19
3   0  1168  10615    144      9    414     69     14     69      5      20
1   0  1121  10416    362     28    225     19     11     71      8      35
Ouch!

The “ouch” has been caused by my CTRL-c to stop the execution.

Collectl is organised to work by subsystems, the standard option is to print CPU, disk and network subsystem, aggregated.

If you don’t know what information you are after, you could use the –all flag to display aggregated information across all subsystems. Be warned that you need a large screen for all that output! For even more output, add the –verbose flag to the –all option and you need a 22” screen at least. The verbose flag prints more output, as the name suggests. For the disk subsystem you can view the difference:

$ collectl -sd -i 5 --verbose
waiting for 5 second sample...

# DISK SUMMARY (/sec)
#KBRead RMerged  Reads SizeKB  KBWrite WMerged Writes SizeKB
162     136     10     15      187      30     19      9
109      24      9     11      566     118     23     24
Ouch!
$ collectl -sd -i 5
waiting for 5 second sample...
#<----------Disks----------->
#KBRead  Reads KBWrit Writes
9865     73    190     23
Ouch!

Each subsystem can be queried individually, the default monitoring interval is 1 second. The man page for collectl lists the following subsystems:

SUMMARY SUBSYSTEMS

b - buddy info (memory fragmentation)
c - CPU
d - Disk
f - NFS V3 Data
i - Inode and File System
j - Interrupts
l - Lustre
m - Memory
n - Networks
s - Sockets
t - TCP
x - Interconnect
y - Slabs (system object caches)

As the name suggests, these sub systems provide summary information. Summaries are ok for a first overview, but don’t forget that information is aggregated and detail is lost.

From an Oracle point of view I’d probably be most interested in the CPU, disk and memory usage. If you are using RAC, network usage can also be interesting.

Detailed subsystem information is available for these (again taken from the excellen manual page):

C - CPU
D - Disk
E - Environmental data (fan, power, temp),  via ipmitool
F - NFS Data
J - Interrupts
L - Lustre OST detail OR client Filesystem detail
N - Networks
T - 65 TCP counters only available in plot format
X - Interconnect
Y - Slabs (system object caches)
Z - Processes

You can combine subsystems, and you can combine detail and summary information. Bear in mind though that this becomes a lot of information for a putty session of gnome-terminal!

In interactive mode, you might want to consider the –home flag, which does a top-like refresh and prints real time information without scrolling: very neat!

But even with the –-home option, digesting all that information visually can be a bit daunting, which leads me to my next section.

Generating graphical output

While all the textual information is all nice and good, it is difficult to visualise. Collectl can help you with that as well. All you need to do is generate a file in tab format, which is as simple as adding the –P and –f options. Since you can’t be overwhelmed with the information gathered in a file (unlike on standard out), you could use the detail switches. If you have the luxury, create the file with the information in a directory expored via samba and analyse it with Excel or other utilities. It’s possible to use gnuplot as well, but I found that a bit lacking for interactive use. The collectl-utils provide a CGI script to analyse collectl files on the host which can be convenient. Here is an example for measuring CPU, memory and all disks with a monitoring interval of 15 seconds. The file will be in “Plot” format (-P) and goes to /export/collectl/plotfiles:

$ collectl -sdmn -i 15 -P -f /export/collectl/plotfiles

Note that you can’t use the verbose flag here, and you also shouldn’t use a file name with the –f switch!

The resulting file is called hostname-yyyymmdd.tab. After renaming it to hostname-yyyymmdd.txt it can quite easily be imported using your favourite spreadsheet application. Imagine all the graphs you could produce with it! Also the header contains interesting information:

################################################################################
# Collectl:   V3.5.1-1  HiRes: 1  Options: -sdmn -i 15 -P -f /export/collectl/plotfiles
# Host:       node1 DaemonOpts:
# Distro:     Red Hat Enterprise Linux Server release 5.5 (Tikanga)  Platform:
# Date:       20110805-142647  Secs: 1312550807 TZ: +0100
# SubSys:     dmn Options: z Interval: 1 NumCPUs: 16  NumBud: 0 Flags: i
# Filters:    NfsFilt:  EnvFilt:
# HZ:         100  Arch: x86_64-linux-thread-multi PageSize: 4096
# Cpu:        AuthenticAMD Speed(MHz): 2210.190 Cores: 4  Siblings: 4
# Kernel:     2.6.18-194.el5  Memory: 65990460 kB  Swap: 16809976 kB
# NumDisks:   173 DiskNames: c0d0 sda sdb sdc sdd sde sdf sdg sdh sdi sdj sdk sdl sdm sdn sdo sdp sdq sdr sds sdt sdu sdv sdw sdx sdy sdz sdaa sdab sdac sdad sdae sdaf sdag sdah sdai sdaj sdak sdal sdam sdan sdao sdap sdaq sdar sdas sdat sdau sdav sdaw sdax sday sdaz sdba sdbb sdbc sdbd sdbe sdbf sdbg sdbh sdbi sdbj sdbk sdbl sdbm sdbn sdbo sdbp sdbq sdbr sdbs sdbt sdbu sdbv sdbw sdbx sdby sdbz sdca sdcb sdcc sdcd sdce sdcf sdcg sdch sdci sdcj sdck sdcl sdcm sdcn sdco sdcp sdcq sdcr sdcs sdct sdcu sdcv sdcw sdcx sdcy sdcz sdda sddb sddc sddd sdde sddf sddg dm-0 dm-1 dm-2 dm-3 dm-4 dm-5 dm-6 dm-7 dm-8 dm-9 dm-10 dm-11 dm-12 dm-13 dm-14 dm-15 dm-16 dm-17 dm-18 dm-19 dm-20 dm-21 dm-22 dm-23 dm-24 dm-25 dm-26 dm-27 dm-28 dm-29 dm-30 dm-31 dm-32 dm-33 dm-34 dm-35 dm-36 dm-37 dm-38 dm-39 dm-40 dm-41 dm-42 dm-43 dm-44 dm-45 dm-46 dm-47 dm-48 dm-49 dm-50 dm-51 dm-52 dm-53 dm-54 dm-55 dm-56 dm-57 dm-58 dm-59 dm-60
# NumNets:    8 NetNames: lo: eth0: eth1: eth2: eth3: sit0: bond0: bond1:
# SCSI:       DA:0:00:00: ... DA:2:00:00:00
################################################################################

This should be enough to remind you of where you were running this test.

Run duration and interval

Use the –i flag to change the monitoring interval, this is the same as you’d do with SAR or iostat/vmstat and the like. You could then either use the –c option to count n samples, or alternatively use –R to run for n weeks, days, hours, minutes or seconds, each of which are abridged with their first letter. For example to run for 15 minutes with samples taken every 15 seconds, you’d say collectl –i 15 –R 15m.

Quick and dirty

If you need an interactive overview of what’s going on top-style, you can use the –top flag. This will print output very similar to the top command, but this time you have a lot more options to sort on. Use collectl –showtopops. This is so cool that I couldn’t help just listing the options here:

$ collectl --showtopopts
The following is a list of --top's sort types which apply to either
process or slab data.  In some cases you may be allowed to sort
by a field that is not part of the display if you so desire

TOP PROCESS SORT FIELDS

Memory
vsz    virtual memory
rss    resident (physical) memory

Time
syst   system time
usrt   user time
time   total time

I/O
rkb    KB read
wkb    KB written
iokb   total I/O KB

rkbc   KB read from pagecache
wkbc   KB written to pagecache
iokbc  total pagecacge I/O
ioall  total I/O KB (iokb+iokbc)

rsys   read system calls
wsys   write system calls
iosys  total system calls

iocncl Cancelled write bytes

Page Faults
majf   major page faults
minf   minor page faults
flt    total page faults

Miscellaneous (best when used with --procfilt)
cpu    cpu number
pid    process pid
thread total process threads (not counting main)

TOP SLAB SORT FIELDS

numobj    total number of slab objects
actobj    active slab objects
objsize   sizes of slab objects
numslab   number of slabs
objslab   number of objects in a slab
totsize   total memory sizes taken by slabs
totchg    change in memory sizes
totpct    percent change in memory sizes
name      slab names

Filtering information

Let’s say you are running multiple ASM disk groups in your system, but you are only interested in the performance of disk group DATA. The –sD flag will print all the information for all disks (LUNs) of the system. Collectl reports disks as the native devices and dm- devices. For multipathed devices you obviously want to look at the dm- device. You could use the multipath –ll command to map dm- device to WWIDs and your disks in the end. Let’s say you found out that the disks you need to look at are /dev/dm-{1,3,5,8} you could use the –dskfilt flag, which takes a perl regex. In my example, I could use the following command to check on those disks:

collectl -sD -c 1 --dskfilt "dm-(1\b|3\b|5\b|8\b)"
waiting for 1 second sample...

# DISK STATISTICS (/sec)
#          <---------reads---------><---------writes---------><--------averages--------> Pct
#Name       KBytes Merged  IOs Size  KBytes Merged  IOs Size  RWSize  QLen  Wait SvcTim Util
dm-1             0      0    0    0       0      0    0    0       0     0     0      0    0
dm-3             0      0    0    0       0      0    0    0       0     0     0      0    0
dm-5             0      0    0    0       0      0    1    1       0     0     0      0    0
dm-8             0      0    0    0       0      0    0    0       0     0     0      0    0
$

Note the “\b” boundary, which is my uneducated way to saying that the expression should match dm-1, but not 10, or anything else that extends beyond number one.

Additional filters you can apply can be found in the output of collectl –showsubopts as well as in section subsystem options in the manpage.

Summary

Used correctly, collectl is the swiss army knife for system monitoring, the level of detail which can be gathered is breathtaking. Thanks Mark Seger! And aplogies for all the good stuff I’ve been missing!

Nice one:http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html

Nice one:http://ora-00001.blogspot.com/2011/07/mythbusters-stored-procedures-edition.html

The New Order Oracle Coding Challenge 3 – Mind Boggle

August 5, 2011 (Modified August 7, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In part 1 of this series the challenge was to simply reverse the order of digits in the numbers from 1 to 1,000,000 to find that cases where the numbers formed by the reverse ordered digits [...]

Friday Philosophy – Oracle Performance Silver Bullet


Silver Cartridge and Bullet

For as long as I have been working with Oracle technology {which is now getting towards 2 decades and isn’t that pause for thought} there has been a constant search for Performance Silver Bullets – some trick or change or special init.ora parameter {alter system set go_faster_flag=’Y'} you can set to give you a guaranteed boost in performance. For all that time there has been only one.

There are a few performance Bronze Bullets…maybe Copper Bullets. The problem is, though, that the Oracle database is a complex piece of software and what is good for one situation is terrible for another. Often this is not even a case of “good 90% of the time, indifferent 9% of the time and tragic 1% of the time”. Usually it is more like 50%:30%:20%.


Cartridge with copper bullet &spent round

I’ve just been unfair to Oracle software actually, a lot of the problem is not with the complexity of Oracle, it is with the complexity of what you are doing with Oracle. There are the two extremes of OnLine Transaction Processing (lots of short running, concurrent, simple transactions you want to run very quickly by many users) and Data Warehouse where you want to process a vast amount of data by only a small number of users. You may well want to set certain initialisation parameters to favour quick response time (OLTP) or fastest processing time to completion (DW). Favouring one usually means a negative impact on the other. Many systems have both requirements in one… In between that there are the dozens and dozens of special cases and extremes that I have seen and I am just one guy. People get their database applications to do some weird stuff.

Partitioning is a bronze bullet. For many systems, partitioning the biggest tables makes them easier to manage, allows some queries to run faster and aids parallel activity. But sometimes (more often than you might think) Partitioning can drop rather than increase query or DML performance. In earlier versions of Oracle setting optimizer_index_caching and optimizer_index_cost_adj was often beneficial and in Oracle 9/8/7 setting db_file_multiblock_read_count “higher” was good for DWs….Go back to Oracle 7 and doing stuff to increase the buffer cache hit ratio towards 98% was generally good {and I will not respond to any comments citing Connors magnificent “choose your BCHR and I’ll achieve it” script}.
You know what? There was an old trick in Oracle 7 you could maybe still look at as a bronze bullet. Put your online redo logs and key index tablespaces on the fastest storage you have and split your indexes/tables/partitions across the faster/slower storage as is fit. Is all your storage the same speed? Go buy some SSD and now it isn’t….


Cartridge with Wooden Bullet

Then there are bronze bullets that you can use that very often improve performance but the impact can be catastrophic {Let’s call them wooden bullets :-) }. Like running your database in noarchivelog mode. That can speed up a lot of things, but if you find yourself in the situation of needing to do a recovery and you last cold backup is not recent enough – catastrophe. A less serious but more common version of this is doing things nologging. “oh, we can just re-do that after a recovery”. Have you done a test recovery that involved that “oh, we can just do it” step? And will you remember it when you have a real recovery situation and the pressure is on? Once you have one of these steps, you often end up with many of them. Will you remember them all?

How many of you have looked at ALTER SYSTEM SET COMMIT_WRITE=’BATCH,NOWAIT’? It could speed up response times and general performance on your busy OLTP system. And go lose you data on crash recovery. Don’t even think about using this one unless you have read up on the feature, tested it, tested it again and then sat and worried about could possibly go wrong for a good while.

That last point is maybe at the core of all these Performance Bronze Bullets. Each of these things may or may not work but you have to understand why and you have to understand what the payback is. What could now take longer or what functionality have I now lost? {hint, it is often recovery or scalability}.

So, what was that one Silver Bullet I tantalizingly left hanging out for all you people to wait for? You are not going to like this…

Look at what your application is doing and look at the very best that your hardware can do. Do you want 10,000 IOPS a second and your storage consists of less than 56 spindles? Forget it, your hardware cannot do it. No matter what you tune or tweak or fiddle with. The one and only Performance Silver Bullet is to look at your system and your hardware configuration and work out what is being asked and what can possibly be delivered. Now you can look at:

  • What is being asked of it. Do you need to do all of that (and that might involve turning some functionality off, if it is a massive drain and does very little to support your business).
  • Are you doing stuff that really is not needed, like management reports that no one has looked at in the last 12 months?
  • Is your system doing a heck of a lot to achieve a remarkably small amount? Like several hundred buffer gets for a single indexed row? That could be a failure to do partition exclusion.
  • Could you do something with physical data positioning to speed things up, like my current blogging obsession with IOTs?
  • You can also look at what part of your hardware is slowing things down. Usually it is spindle count/RAID level, ie something dropping your IOPS. Ignore all sales blurb from vendors and do some real-world tests that match what you app is or wants to do.

It’s hard work but it is possibly the only Silver Bullet out there. Time to roll up our sleeves and get cracking…

{Many Thanks to Kevin Closson for providing all the pictures – except the Silver Bullet, which he only went and identified in his comment!}

Exadata: It’s The World’s Fastest Database Machine And The Best For Oracle Database – Part I. Do As I Say, Not As I Do!

Two days ago Oracle published a video about the world’s “fastest database machine.” This, of course, refers to Exadata. Although the video has been online for two days its view rate is very low (less than 1,000). So, I thought I’d refer to it and give it a boost.

This is actually an interesting video. The folks in the video—namely Juan, Kodi, Amit and Ron—are some of the class acts in the Exadata organization within Oracle Server Technologies. I have respect for these folks and I’ve known some of them for many, many years and the remainder at least dating back to the commencement of my tenure in Juan Loaiza’s organization back in 2007. They mean well and I mean it when I say they are a collective class act. Having respect for these gentlemen doesn’t mean I have to agree with everything they say. To that end I aim to respectfully offer some differing views on some of what has been said in the video.

I’d like to first offer my commentary regarding the messaging in the video after which I’ll provide a link.

  • The World’s Fastest Database Machine. The first point I’ll make is about the title of the video. Exadata is very good at a lot of things–obviously. Readers of this blog know my position on the matter quite well. That aside, I have a problem with the notion of referring to Exadata as “the world’s fastest database machine” without any data to back up the claim. That was a point of contention I had when I was still with Oracle. Exadata is not the established fastest machine in any database category as per the standard in the matter—which at this time is Transaction Processing Council (TPC) workloads. For that matter even the lower-level Storage Performance Council workloads would be a starting point for validation of these claims (particularly the unstructured data claims made by Exadata marketing) but to-date there are no audited industry-standard benchmark results with Exadata. Please don’t get me wrong. I’m not harping on the lack of published benchmarks for the many reasons I point out here. I’m only making the point that claims of world dominance should be backed up with industry-standard proof points. As for industry-standard benchmarks, the world’s fastest database machine for OLTP is the SPARC SuperCluster.  The world’s fastest database machine for DW/BI/DSS is Dell kit with Exasol at every scale except 30 TB where Oracle Database 10g on HP Superdome (Itanium) remains the top-dog. I’ll point out that the only independently validated proof point of Exadata, to date, was the Winter Corporation proof point I did back in 2009.  With that memory in mind, I’m led to the next point of contention with the video.
  • Setting Out To Build The World’s Fastest Database Machine. The stage for the video is set right at the beginning when Juan speaks of the motive behind building the “world’s fastest database machine” when referring to Exadata. The problem is that the product name Exadata came long after what was actually being built. When Exadata was first released, the term only applied to the storage cells. The first embodiment of a Database Machine was the HP Oracle Database Machine which included Exadata Storage Servers. When Sun Microsystems was acquired by Oracle the Exadata brand was sort of promoted to mean everything in the rack and, of course, current work for ports of Exadata storage connectivity (iDB) to such platforms as HP-UX were dropped. So what’s my point? There was, in fact, a port of iDB that allowed the HP-UX port of Oracle Database 11g instances to connect to Exadata Storage Servers but the effort was killed and the mutual respect between long-time partners Oracle and HP crumbled. Personalities sometimes get in the way it seems, but I digress. The very fact that such porting was in progress sort of suggests that what ended up being the only embodiment of Exadata technology was clearly not always the plan for the technology and thus the notion of “setting out to build the world’s first” seems more like hind-sight. In the “olden days” we looked at Exadata as optimal storage for Oracle Database—nothing more grandiose than that. But those were exciting and rewarding times nonetheless!
  • Ideal System For Running The Oracle Database. Juan points out that one of the goals in Exadata development was to create the ideal system for running the Oracle database. I think that is a good design center, but I stand fast in my position that the ideal system for Oracle database depends on the workload. There is no one-size fits all. The one-size fits all positioning is pervasive though. Another member of Juan’s team, Tim Shetler, garners the same level of esteem I have for those I’ve previously mentioned but I don’t always have to agree with him either. In this article in Database Trends and Applications, Tim puts it this way (emphasis added by me):

Our mission around Exadata is to create absolutely the best platform for running the Oracle Database.  Those words are carefully chosen because it is very focused. It is not the best platform for running databases. It is not the best platform for running data warehouses on Oracle. It is: Any application that uses the Oracle Database will run best if it uses the Exadata platform to host the Oracle Database.

Do As I Say, Not As I Do
The  problem I have with this idea that Exadata is the best platform for Oracle database full-stop is in spite of being “the best platform for running databases”, and best for “any application”,  Oracle IT doesn’t even use Exadata for ERP. We know from reading Oracle Corporation’s Mission Critical Systems Update (Google Docs View) that years after the production release of Exadata, Oracle IT migrated from older SPARC gear to an M9000. This is Oracle’s central ERP system. Nothing is more critical than that. This may sound like FUD, but the migration started last September (2010)—years after Oracle approached customers to adopt Exadata technology—and the configuration is still being expanded. I quote :

The additional M9000 SPARC system installation began at midnight March 3rd, 2011, and was completed, in full, at 11:31am the next day, March 4th, 2011. There was no down time of the live GSI database/ERP systems during installation by the Oracle PDIT staff.

                                                            — Chris Armes, Sr. Director, Oracle Systems

Let me paraphrase that. The core ERP systems at Oracle (they are not Exadata) were still being built-out as I was finishing my two-weeks notice of resignation from my position in the Exadata development organization! That hardware is going nowhere soon. Do As I Say, Not As I Do.

I’ll watch the view count on that YouTube video while I consider Part II in this series.

I was just joking about giving the video viewership a boost.

Here is the link: http://www.youtube.com/watch?v=ZHlFDgci9Fc

Filed under: oracle

Trace3 customer experience with Delphix

People often ask me what I work on now after having been at Oracle, Embarcardero and Quest. I’m now at a company called Delphix where I work on performance monitoring and tuning. Here is a great customer case writeup from  Trace3 about what Delphix does.

 

From Trace3:


 

“After their SAP administrator provisioned a 7.2 terabyte database
in 15 minutes with no assistance from the storage group, UNIX
group, DBAs, or backup/restore, they really understood the full
value of the Delphix solution.”

PAUL MONTOYA – Professional Services Architect
Trace3

Every once in a while, a technology comes along that addresses our customers’ pain
points so thoroughly that it pretty much sells itself. What if you could promise customers
a solution that would allow them to provision full copies of multi-terabyte databases
in minutes, with no impact to production, while achieving a huge consolidation of the
underlying storage?

 

No doubt you’ve heard of server virtualization, the foundation of private cloud computing.
It’s an easy sale: buy less hardware, provision resources faster, and reduce costs.
Database virtualization offers those same benefits to customers that routinely work
with large data sets, especially in dev/test environments. Right now, that technology is
exclusively the domain of Delphix, a Trace3 technology partner.

 

Just a few years ago, many customers were reluctant to virtualize their servers because
the technology was viewed by many as not ready for production. Today, virtual machines
are ubiquitous. Showing customers the benefits of database virtualization now will not
only generate sales, it will also build loyalty to Trace3, giving our customers confidence
that we stay on the leading edge.

 

The featured case study explains how Trace3 worked closely with a semiconductor
customer to embrace database virtualization.  A successful proof of concept resulted in
a 99% reduction in database refresh time—from 5 days to 15 minutes—while yielding a
45:1 storage consolidation ratio, for a projected payback of 1.9 months.

… article continued at Trace3


 

Of course you can check out my  current company straight from the horses mouth at Delphix.com

Oracle OpenWorld 2011: The Oracle Real-World Performance Group

The summer is flying by and in no time it will be October and that means Oracle OpenWorld 2011 should be on your radar. Once again the Oracle Real-World Performance Group will be hosting three sessions.  For those unfamiliar with our presentations, you will get marketing free, no nonsense performance insight of the highest caliber from Oracle’s most elite database performance engineers — the kind of things hard core Oracle people want to hear about.  Hope to see you there!

style="cursor: default;" width="100%" border="1" cellpadding="1">
style="cursor: text; margin: 8px;" bgcolor="#eeeeee" width="120"">Session ID: style="cursor: text; margin: 8px;">13641 (Wednesday Oct. 5th, 10:00) style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Title: style="cursor: text; margin: 8px;">Real-World Performance: The Forklift Upgrade style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Abstract: style="cursor: text; margin: 8px;">Today the motivation to consolidate and migrate existing data and applications into the extreme-high-performance database environments of Oracle Exadata and Oracle Exalogic is being driven by a desire to reduce costs and deliver increased performance and service levels to users. The process is often called a forklift migration, because applications and data are simply picked up and lifted onto new platforms.In this session, Oracle’s Real World Performance group describes how best to maximize your investment and achieve world-class performance and discusses the challenges and compromises that need to be made in the process. style="cursor: text; margin: 8px;" bgcolor="#999999"> style="cursor: text; margin: 8px;" bgcolor="#999999"> style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session ID: style="cursor: text; margin: 8px;">13643 (Tuesday Oct. 4th 13:15) style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Title: style="cursor: text; margin: 8px;">Real-World Performance: How Oracle Does It style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Abstract: style="cursor: text; margin: 8px;">Oracle’s Real-World Performance Group has been achieving world-class performance for clients for more than 15 years. In this session, some of the senior engineers describe the techniques, philosophy, and tools they use to address performance challenges. The session is packed with demos and examples to convey the pragmatism required in real-world performance today. style="cursor: text; margin: 8px;" bgcolor="#999999"> style="cursor: text; margin: 8px;" bgcolor="#999999"> style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session ID: style="cursor: text; margin: 8px;">13640 (Thursday Oct. 6th 10:30) style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Title: style="cursor: text; margin: 8px;">Real-World Performance Questions and Answers style="cursor: text; margin: 8px;" bgcolor="#eeeeee">Session Abstract: style="cursor: text; margin: 8px;">This is your chance to pose specific questions to Oracle’s Real-World Performance Group. All questions need to be in writing (yes, cards will be provided) and should relate to database performance challenges. In the past, the best questions have been related to specific topics of interest to the audience.

The Dresden Files: Ghost Story…

Ghost Story is the latest book in The Dresden Files series by Jim Butcher.

Spoiler Alert. Don’t read on if you are currently reading the series and haven’t got to this book yet. It’s hard to talk about the book without doing a bit of a spoiler.

 

At the end of the last book Harry Dresden died. At the start of this book he comes back as a ghost and is sent back to Chicago to help his friends. The fact that there is another book planned makes you think one of two things are going to happen.

  1. He will be so successful as a ghost version of himself he will be able to carry on in that capacity.
  2. He will somehow be ressurected.

This book is more predictable than any of the other books in the series, but that’s not a bad point. The outcomes of certain situations are predictable, but the journeys to those outcomes are far from predictable, which is half the fun. You are sitting there thinking, I know how this is meant to pan out, but how the hell is he going to get there? :)

It was a great story, and well deserving of its place in the series. I’ve now got a really long wait for the next book (Cold Days) to be released. :(

Cheers

Tim…




Baselines and SQL_ID

I ran across a great post by Marcin Przepiorowski (How to find SQL_ID and PLAN_HASH_VALUE in Oracle SQL Plan Management Baselines) thanks to Karl Arao‘s tweet. Marcin posted a bit of code for calculating a SQL_ID using the text of a SQL statement (based on some research by Tanel Poder). I was aware of Tanel’s research but had not taken the time to try to figure out how to code it. The context of Marcin’s post was based on wanting to relate a Baseline to a SQL_ID. This is something I’ve wanted to do in the past so I was quite interested in the post. As a bonus Marcin also demonstrated a technique to pull the PLAN_HASH_VALUE associated with a Baseline. Of course I can never leave well enough alone and so I had to re-arrange Marcin’s code a little bit to suite my own purposes. So I created a function that returns either the PLAN_HASH_VALUE or the SQL_ID of a Baseline. Here’s the code to create the function: create_baseline_info.sql and a script that uses it: baselines2.sql

Here’s an example of how to use them:

 
SYS@SANDBOX1> select count(*) from kso.skew;
 
  COUNT(*)
----------
  32000004
 
SYS@SANDBOX1> @fsx
Enter value for sql_text: select count(*) from kso.skew
Enter value for sql_id: 
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
gm0w4as5hhr4m      0  578366071      1       1.30      8 Yes          71.78 select count(*) from kso.skew
 
SYS@SANDBOX1> @dplan
Enter value for sql_id: gm0w4as5hhr4m
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gm0w4as5hhr4m, child number 0
-------------------------------------
select count(*) from kso.skew
 
Plan hash value: 578366071
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |       |  6183 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE                |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR               |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)         | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE             |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR         |          |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS STORAGE FULL| SKEW     |    32M|  6183   (1)| 00:01:15 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - storage(:Z>=:Z AND :Z<=:Z)
 
 
23 rows selected.
 
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: gm0w4as5hhr4m
Enter value for plan_hash_value: 578366071
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: gm0w4as5hhr4m
plan_hash_value: 578366071
fixed: NO
enabled: YES
plan_name: SQLID_gm0w4as5hhr4m_578366071
sql_handle: SQL_e86d1a732b7a5c25
Baseline SQLID_gm0w4as5hhr4m_578366071 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> select sql_handle, plan_name from dba_sql_plan_baselines where plan_name like upper('SQLID_gm0w4as5hhr4m_578366071');
 
SQL_HANDLE           PLAN_NAME
-------------------- ------------------------------
SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071
 
SYS@SANDBOX1> @baselines2
Enter value for sql_text: select count(*) from kso.skew%
Enter value for name: 
Enter value for plan_name: 
 
SQL_ID        SQL_TEXT                                           PLAN_HASH_VALUE SQL_HANDLE           PLAN_NAME                      ENABLED ACC FIX LAST_EXECUTED
------------- -------------------------------------------------- --------------- -------------------- ------------------------------ ------- --- --- ----------------
1jp7sjmt0wp1j select count(*) from kso.skew s , skew_gtt g where 2414665383      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ncefcc71f YES     NO  NO
                                                                 2178955164      SQL_936b37ad684d18d4 SQL_PLAN_96utrppn4u66ne441e87d YES     NO  NO
gm0w4as5hhr4m select count(*) from kso.skew                      578366071       SQL_e86d1a732b7a5c25 SQLID_GM0W4AS5HHR4M_578366071  YES     YES NO
 
SQL> -- cool - it's a match

So in the previous listing I ran a select statement, found the SQL_ID and PLAN_HASH_VALUE for that statement (using fsx.sql), displayed the plan (using dplan.sql) and created a Baseline for the statement (using create_baseline.sql). I then verified that the Baseline existed in DBA_SQL_PLAN_BASELINES and ran the baselines2.sql script which returns the SQL_ID and PLAN_HASH_VALUE using the baseline_info() function, which as you can see matched the original query.