dtrace - miracle scotland database forum

Playing around with DTrace

(… and Oracle, of course)

Doug Burns

dougburns@yahoo.comhttp://oracledoug.com

Agenda

Introduction What is DTrace? DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

Introduction Welcome to Scotland

I’m a Campbell

Search Google for “Campbell Glencoe”

The picture you are about to see is well-chosen

The Glencoe Massacre of 1692

Introduction So now you know I am definitely not to

be trusted. I’m a Campbell I enjoy traditional Highland hospitality … … and then kill my hosts

However, you may be safe … I don’t drink Whisky I don’t wear Skirts!

See how dangerous a simple Tie can be?

What is DTrace?

Introduction What is DTrace? DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

Similar story to Oracle Wait Statistics See Juan Loiza quotation in Chapter 2 of

TOTOT

A problem with a benchmark Lots of guesses - none correct Instrumentation helps - if you thought of it Sometimes you have to stop to move forward

Sometimes you need to see what’s really going on – even (or particularly) on Production systems

What is DTrace? - History

Dynamic Tracing framework. Why ‘Dynamic’?

No need to instrument your code Although you can, by implementing a DTrace

provider. When you hit a performance problem, you can

enable probes Perhaps like ASH but without the constant overhead? More like dbms_support.start_trace_in_session

What can I trace? Erm, just about everything

What is DTrace? – The Name

What is DTrace? – The Future

Open Source Thriving Community

DTrace is available on Solaris 10+ Remember that Solaris != SPARC FreeBSD and Mac OS X - soon AIX?

Search Google for ‘AIX 6 Preview’ probevue

Linux?

DTrace Basics

Introduction The Need for DTrace DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

DTrace Basics – D Language

‘D’ Scripting Language A combination of C and awk?

But don’t let that put you off ;-) Event-driven so you :-

Define the Events (Probes) to trap Write the handlers

Let’s take a look at a simple example But ‘Hello World’ is not interesting

DTrace Basics – Defining Probes

Probes can be identified by name or number

A probe name is a 4-tuple provider:module:function:name A space or asterisk is a wildcard Some providers do not provide modules

Examples pid456:libc:malloc:entry pid1910:oracle:krccdw: sched:::enqueue dtrace:::BEGIN

DTrace Basics – One-Liner

Use any probe definition as a one line program Enable probes using -n

dtrace -n pid1910:oracle:krccdw:

dtrace: description 'pid1910:oracle:krccdw:' matched 108 probes

CPU ID FUNCTION:NAME 0 66998 krccdw:entry 0 207834 krccdw:0 0 207835 krccdw:1 0 207836 krccdw:3 0 207837 krccdw:6 0 207838 krccdw:9 0 207839 krccdw:a 0 207840 krccdw:b 0 207841 krccdw:c

DTrace Basics – One-Liner

Make a small change in probe definition

dtrace -n ::oracle:krccdw:entry

dtrace: description '::oracle:krccdw:entry' matched 1 probe

CPU ID FUNCTION:NAME 2 43530 krccdw:entry 3 43530 krccdw:entry 1 43530 krccdw:entry 1 43530 krccdw:entry 1 43530 krccdw:entry 2 43530 krccdw:entry 2 43530 krccdw:entry 2 43530 krccdw:entry 2 43530 krccdw:entry

DTrace Basics - Providers

sched provider Access to O/S scheduling information Probes which can trap information when

processes are placed on to the run queue and when they are dequeued and allowed to run. sched:::enqueue sched:::dequeue

Probes that can be used to monitor time spent on the CPU. sched:::on-cpu sched:::off-cpu

DTrace Basics - Providers

dtrace io pid Proc syscall sysinfo

DTrace Basics – A Script

A more realistic Program Structure

#pragma D option quiet

proc:::exec{ self->parent = execname;}

proc:::exec-success/self->parent != NULL/{ @[self->parent, execname] = count();

self->parent = NULL;}

proc:::exec-failure/self->parent != NULL/{ self->parent = NULL;}

END{ printf("%-20s %-20s %s\n", "WHO", "WHAT", "COUNT");

printa("%-20s %-20s %@d\n", @);}

DTrace Basics – A Script

Output

# dtrace -s ./whoexec.d^CWHO WHAT COUNTmake.bin yacc 1sh sed 4sh tr 4make make.bin 4sh install.bin 5sh rm 6cc ir2hf 33cc ube 33sh date 34sh mcs 34cc acomp 34sh cc 34sh basename 34basename expr 34make.bin sh 87

DTrace Basics -

DTrace Toolkit – Brendan Gregg A Useful Place to Start

Learn the D language Initial ideas of what to try Reasonably well documented Can run into resource limits if you apply them

to oracle whole-sale (more later) Free Constant Development Examples will use DTrace Toolkit

programs

A Few Oracle Examples

Introduction The Need for DTrace DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

A Few Oracle Examples

iotop –d md0 1

iosnoop –p 1885

A Few Oracle Examples

topsyscall

A Few Oracle Examples

One for Alex … Change Block Tracking Find the CTWR process

ps -ef|grep ctwr|grep -v grep

oracle 1911 1 0 May 19 ? 2:47 ora_ctwr_TEST1020

A Few Oracle Examples

Traditional approach – truss (or strace)

A Few Oracle Examples

dtruss – significantly less overhead

A Few Oracle Examples

dapptrace –aFp PID

Quirks and Issues

Introduction The Need for DTrace DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

Quirks and Issues

./dapptrace -p 5728

First, think about what you’re doing! Edit /kernel/drv/fasttrap.conf Default is 250,000

Increase to 1,000,000? update_drv fasttrap Probably need a reboot

Quirks and Issues

./dapptrace -p 5728

Not zeroing variables when they’re no longer used

Caused by running out of finite space for variables

Look at tuning dynvarsize and cleanrate

Quirks and Issues

No Overhead? When probes are not enabled

No or very low overhead Sampling - A probe event rate of less than

1000 per second Likely to have negligible impact.

Tracing “Slow” disk events – maybe less than 0.2% CPU Process creation – closer to 0% Every Application Function – maybe 10%?

Source: DTrace wiki at solarisinternals.com

Quirks and Issues

No Overhead A simple example Start a sqlplus session and pick up

dedicated server process dapptrace –p PID SELECT sysdate FROM dual;

Normally sub-second With dapptrace, 5 – 20 seconds!

No overhead?

Quirks and Issues

Tracing vs. Sampling Hey, it’s the 10046/Statspack debate!

DTrace can do both – horses for courses

DTrace can drown you in detail Unless you already know what you’re

looking for Aggregation Functions can also make

results more manageable

Conclusions

Introduction The Need for DTrace DTrace Basics A Few Oracle Examples Quirks and Issues Conclusions

Conclusions “Just tell me the top things to try” The Good

You may get some quick performance wins A very practical way to introduce you to DTrace

The Bad Can only introduce you to a fraction of the field of

DTrace Every site is different, there is no one-size-fits-all set

of scripts Somewhat ill-conceived; DTrace wins often aren't that

quick

The Ugly A customer declaring that "DTrace found nothing"

after only trying the quick wins

Conclusions From the DTrace Toolkit FAQ (Docs/Faq)

1.4. Am I now a performance tuning expert?

The DTraceToolkit does not turn people into performance tuning experts in the same way that owning a set of golf clubs won't make you a professional golfer. Experience and understanding are necessary.

Conclusions Not the ideal cure for Compulsive

Tuning Disorder1

Like presenting an alcoholic with the distillery keys.

You could spend a lot of time with this. If you’re a geek!

1 Gaja Krishna Vaidyanatha - TOTOT

Conclusions

My final and most important conclusion

Never Trust a Campbell!

Playing around with DTrace

(… and Oracle, of course)

Doug Burns

dougburns@yahoo.comhttp://oracledoug.com

References and Additional Info

Brendan Gregg (including link to DTrace Toolkit) http://www.brendangregg.com/dtrace.html

Solaris Internals - DTrace Topics wiki http://www.solarisinternals.com/wiki/index.php/DTrace_T

opics

Tips, Tricks and Gotchas from DTrace authors http://learningsolaris.com/docs/dtrace_tips_public.pdf