Search

Top 60 Oracle Blogs

Recent comments

July 2011

Expert Oracle Exadata virtual conference

Today is the last day for getting the early bird’s rate!

http://blog.tanelpoder.com/seminar/expert-oracle-exadata-virtual-conference/

Also, our book will be out on Monday!

Oracle CPU time

There are 3 kinds of CPU in the Oracle stats.

  1. Oracle CPU used
  2. System CPU used
  3. Oracle demand for CPU

Starting in 10g Oracle records both the CPU used by the instance as well as the load on the system in v$sysmetric. This is awesome as we can see how busy the system is and how much of the CPU Oracle is responsible for:

col metric_name for a25
col metric_unit for a25
select metric_name, value, metric_unit from v$sysmetric where metric_name like'%CPU%' where group_id=2;
METRIC_NAME                         VALUE METRIC_UNIT
------------------------------ ---------- ------------------------------
CPU Usage Per Sec              251.067016 CentiSeconds Per Second
CPU Usage Per Txn              5025.52477 CentiSeconds Per Txn
Host CPU Utilization (%)       11.6985845 % Busy/(Idle+Busy)
Database CPU Time Ratio        76.3291033 % Cpu/DB_Time

Now the question is how do we convert these to something useful? For me I put it into the equivalent of AAS and compare it to the core count:

   select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                             AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                        CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)    sAAS
            from
              ( select value busy from v$sysmetric
                where metric_name='Host CPU Utilization (%)'
                 and group_id=2 ) prcnt,
             ( select value cpu_count
                 from v$parameter
                where name='cpu_count' )  parameter;

CLASS                  AAS
---------------- ----------
CPU_ORA_CONSUMED       .002
CPU_OS                 .022

An AAS of 1 is equivalent to 100% of a core, so, OS CPU is about 2% of a core and of that Oracle used 0.2% of a core.
Not a very active system, and we can look at an active system later, but what I wanted to point out is that this query is missing an important statistic: the demand for CPU by Oracle. We can only add that, AFAIK, by joing in ASH:

   select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                             AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                         CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)     AAS
            from
              ( select value busy from v$sysmetric
                 where metric_name='Host CPU Utilization (%)'
                   and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter
                 where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (60/(24*60*60));

CLASS                   AAS
---------------- ----------
CPU_ORA_CONSUMED       .001
CPU_ORA_DEMAND          .02
CPU_OS                 .019

So the demand for CPU was higher than the amount consumed. Now the demand for CPU is coming from ASH which is sampled so the accuracy is weak, but in larger sample sets or busier systems it’s pretty darn good. The demand alert us to CPU starvation on a busy  system.

I like to wrap all this up into a query with all the wait classes to see the overall load on Oracle including CPU consumed by Oracle, CPU demanded by Oracle and CPU used at the OS level:

select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (60/(24*60*60));

CLASS                   AAS
---------------- ----------
CPU_ORA_CONSUMED       .002
CPU_ORA_DEMAND          .03
CPU_OS                 .023
Commit                    0
User I/O                  0
Wait                      0

Ideally I’d want the CPU stats to be subsets of each other so that I could have a graphically stack-able set of statistics

now rolling it all together
with AASSTAT as (
           select
                 decode(n.wait_class,'User I/O','User I/O',
                                     'Commit','Commit',
                                     'Wait')                               CLASS,
                 sum(round(m.time_waited/m.INTSIZE_CSEC,3))                AAS
           from  v$waitclassmetric  m,
                 v$system_wait_class n
           where m.wait_class_id=n.wait_class_id
             and n.wait_class != 'Idle'
           group by  decode(n.wait_class,'User I/O','User I/O', 'Commit','Commit', 'Wait')
          union
             select 'CPU_ORA_CONSUMED'                                     CLASS,
                    round(value/100,3)                                     AAS
             from v$sysmetric
             where metric_name='CPU Usage Per Sec'
               and group_id=2
          union
            select 'CPU_OS'                                                CLASS ,
                    round((prcnt.busy*parameter.cpu_count)/100,3)          AAS
            from
              ( select value busy from v$sysmetric where metric_name='Host CPU Utilization (%)' and group_id=2 ) prcnt,
              ( select value cpu_count from v$parameter where name='cpu_count' )  parameter
          union
             select
               'CPU_ORA_DEMAND'                                            CLASS,
               nvl(round( sum(decode(session_state,'ON CPU',1,0))/60,2),0) AAS
             from v$active_session_history ash
             where SAMPLE_TIME > sysdate - (60/(24*60*60))
)
select
       ( decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) +
       CPU_ORA_CONSUMED +
        decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED ))) CPU_TOTAL,
       decode(sign(CPU_OS-CPU_ORA_CONSUMED), -1, 0, (CPU_OS - CPU_ORA_CONSUMED )) CPU_OS,
       CPU_ORA_CONSUMED CPU_ORA,
       decode(sign(CPU_ORA_DEMAND-CPU_ORA_CONSUMED), -1, 0, (CPU_ORA_DEMAND - CPU_ORA_CONSUMED )) CPU_ORA_WAIT,
       COMMIT,
       READIO,
       WAIT
from (
select
       sum(decode(CLASS,'CPU_ORA_CONSUMED',AAS,0)) CPU_ORA_CONSUMED,
       sum(decode(CLASS,'CPU_ORA_DEMAND'  ,AAS,0)) CPU_ORA_DEMAND,
       sum(decode(CLASS,'CPU_OS'          ,AAS,0)) CPU_OS,
       sum(decode(CLASS,'Commit'          ,AAS,0)) COMMIT,
       sum(decode(CLASS,'User I/O'        ,AAS,0)) READIO,
       sum(decode(CLASS,'Wait'            ,AAS,0)) WAIT
from AASSTAT)
/

    CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ------------ ---------- ---------- ----------
       .02       .002            0          0          0          0

Now let’s run up some load on a machine and database.
Take two databases, run up the CPU demand on both and add some wait contention. The machine has 24 cores so there is a definitely a problem when the CPU_TOTAL goes over 24. I’m running 14 sessions each trying to burn a core on two different databases. The first few lines the test is ramping up

SQL> /

 CPU_TOTAL     CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ---------- ------------ ---------- ---------- ----------
    14.887       .387     13.753         .747          0          0       .023

SQL> /

 CPU_TOTAL     CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ---------- ------------ ---------- ---------- ----------
    21.989      7.469     12.909        1.611          0          0       .044

SQL> /

 CPU_TOTAL     CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ---------- ------------ ---------- ---------- ----------
    26.595     12.125     11.841        2.629          0          0       .025

SQL> /

 CPU_TOTAL     CPU_OS    CPU_ORA CPU_ORA_WAIT     COMMIT     READIO       WAIT
---------- ---------- ---------- ------------ ---------- ---------- ----------
    27.045     12.125     11.841        3.079          0          0       .025

Historically CPU used by Oracle was derived from

v$sysstat.name=’CPU used by this session’

but this statistic had problems as the value was only updated every time a call ended. A call could be a 1 hour PL/SQL procedure which would thus report zero cpu usage in the stats until it finished and the CPU would spike off the scale.

ASH had always been the most stable way to gather CPU demand, though Oracle has made improvements in gathering CPU statistics. I believe that the time model gathers CPU every 5 seconds in 10g, and in 11g it’s possible that CPU stats are gathered every second

Friday Philosophy – PowerPoint Picture Pain

The title does not refer to the agony of sitting through a presentation with loads of pointless graphics and zip-all content. Rather, it encompasses the agony of trying to produce some decent graphical content for PowerPoint. Or Word or any other software-based method of presenting information. I think we generally had better presentations when we had overhead projectors or flip charts and I go “powerpoint naked” to present sometimes.

However, I do present quite often using the ubiquitous PowerPoint. I spend hours on my slides, both trying to put good textual content into them for both the actual presentation and also for anyone who downloads them later. I also spend ages trying to put together graphics as we humans are visual creatures and a good {let’s pause for a second and stress the word good} picture or diagram can help enormously. I did a little animated graphic a few years back to show how DNA is unzipped and duplicated, allowing the near perfect transmission of our DNA as cells divide. Modesty prevents me saying it was good but {drat, I just said it} but several people told me it really helped them understand something that text descriptions struggle to put across.

{Hmm, OK, I’ll see if I can get this to work. Try this how-dna-duplicates powerpoint show. It shows how the two strands of DNA are “matched”. A, C, T and G are the 4 letters of the DNA and each one is associated with its “pair” in the second strand. A to T, T to A, C to G and G to C. Split the pair into two strands and you can make each a perfect copy of the original by adding back the pairs. Understand? Oh, and there are very, very, very rare copy mistakes, in this case a C is matched to a T rather than an A. Go on, fire up the slide.}

The reality is though that most of my presentations have very average slides. When you present for the UK Oracle User group they are good enough to solicit feedback from the audience and feed it back to the presenter. You get scored on topic, content, style, slides, charisma, sex appeal… The lowest part of my score is always, Always, ALWAYS the slides. This started getting on my nerves so one year I tried really hard on the slides. It was still my weakest point.

The thing is, I have never had a graphics team (let alone a whole marketing department) to help me out. Either I have worked for a company with no such thing or I have been a lone consultant doing what I can in my spare time. It is especially frustrating as large companies that do have access to teams of graphic artists seem to present with a large number of very pretty, very well drawn slides with zip-all content and the graphics add almost nothing to understanding (and we are back to the first line of this posting). In fact, I often find that slides with lots of pretty icons representing part of the technology stack with chunky arrows sweeping across them showing flow to be {cough} pointless to my understanding. One thing I have picked up from all those pretty pictures though is that a feeling of depth and texture is very effective in altering how a slide looks.

So, I decided for my posts on Index Organised Tables I would spend some time messing around with such things in PowerPoint. My Graphic of the Thread is:

I’m quite pleased with it, especially as in doing so I found out quite a bit about how to use the gradient fills, shadowing, 3D formating and rotating of simple shapes. It is certainly better than my previous stock graphic for an index:

I think most people would agree, it is an improvement. But is it two days worth of effort improvement. Yes, that is how long I spent messing around with the image options in PowerPoint to replace a flat, uninspiring green blob with 3 lines on it to represent an index into a nicely shaped, pseudo-3D image with a tad more information. *sigh*.

I guess I can now convert other flat pictures made up of boxes, triangles and lines into something that look a bit more like a professional diagram than something knocked up by a database geezer late one evening the day before the presentation. But if it does not help me produce a diagram that aids understanding rather than just presentation fluff, like those marketing slides I’ve just wasted a lot of time.

I tell you what though, having spent so long on that diagram, I’m going to use it to death :-)


Oracle Apps? VMware? Interesting? Throw It Out The Window.

When I announced I was giving up my Architect post in Oracle Server Technologies  I specifically called out some of the technology I was interested in pursuing. One of those areas is Oracle software on VMware. For those of you who share that penchant you might take interest in Technology Defenestration.  Interesting name for an Apps DBA blog! I’ve read quite a bit of J’s writings and have learned some interesting things.

I like blogs written by people “in the trenches.”

Perhaps you will too.

Filed under: oracle

Cardinality Feedback

I ran into an interesting issue last week having to do with plan stability. The problem description went something like this:

“I have a statement that runs relatively quickly the first time I run it (around 12 seconds). Subsequent executions always run much slower, usually around 20 or 30 minutes. If I flush the shared pool and run it again elapsed time is back to 12 seconds or so.”

The query looked a little like this:

Compiling iozone for Solaris 10 on SPARC

I have started working with ZFS and its various ways of protecting disks from failure. It’s a low end setup at best, where a JBOD is used as an extension to a M-series server. Many JBODs come with SAS connectivity only, and no on-board intelligence so a host based solution has to be chosen to protect the lot from disk failures.

For Solaris, you can use ZFS amongst other solutions. Alternatively, ASM is a possibility. I couldn’t reproduce the exact setup, so I had to improvise. Still I wanted to find out how ZFS performs compared to ASM. For this purpose I used an EMC VMX and a Sun 5410 server which had 10 multipathed 10G LUNs presented to it via EMC Power Path 5.3.

To test the file system performance I decided to use both IOZone and Bonnie++. Bonnie++ is no problem, you can get it from Sun Freeware. Note that Oracle no longer produce the Companion CD, which leaves SunFreeware the only source for alternative packages.

Before you can compile anything on Solaris, you need a compiler (why doesn’t Solaris come with one?). Installing the compiler was simple. I got the required files from these URLS:

They can be installed by unzipping and pkgadd:

bash-3.00# pkgadd -d gcc-3.4.6-sol10-sparc-local

The following packages are available:
  1  SMCgcc     gcc
                (sparc) 3.4.6

Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]:

Processing package instance  from 

gcc(sparc) 3.4.6
FSF

The selected base directory  must exist before
installation is attempted.

Do you want this directory created now [y,n,?,q] y
Using  as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
## Checking for setuid/setgid programs.

Installing gcc as 

## Installing part 1 of 1.
/usr/local/bin/c++
/usr/local/bin/cpp
...
/usr/local/share/locale/rw/LC_MESSAGES/gcc.mo
/usr/local/share/locale/sv/LC_MESSAGES/gcc.mo
/usr/local/share/locale/tr/LC_MESSAGES/gcc.mo
[ verifying class  ]

Repeat the procedure for libiconv as well.

Once that is done, it’s time to compile iozone. Get the latest tar ball from http://www.iozone.org/ (it was iozone3_397.tar in my case) and unzip it somewhere on your system. Change directory to stageDIR/src/current. For the makefile to work, set your path to include /usr/local/bin and /usr/ccs/bin. The makefile comes with different targets depending on your architecture and compiler. In my case I thought that Solaris10gcc-64 would be most appropriate. Trying this option however didn’t succeed:

bash-3.00# make Solaris10gcc-64

Building iozone for Solaris10gcc-64

gcc -O -c  -Dunix -DHAVE_ANSIC_C -DASYNC_IO -D__LP64__ \
                -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -Dsolaris \
                 -m64 libbif.c -o libbif10-64.o
gcc -O -c  -Dunix -DHAVE_ANSIC_C -DASYNC_IO -D__LP64__ \
                -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -Dsolaris \
                -DNAME='"Solaris10gcc-64"'  -m64 libasync.c -o libasync10-64.o
gcc -c -O -Dunix -DHAVE_ANSIC_C -DASYNC_IO \
                -D_LARGEFILE64_SOURCE -D_FILE_OFFSET_BITS=64 -Dsolaris \
                -DNAME='"Solaris10gcc-64"'  -m64 iozone.c -o iozone_solaris10gcc-64.o

Building fileop for Solaris10gcc-64

gcc -c -O  -m64 fileop.c -o fileop_Solaris10gcc-64.o

Building the pit_server

cc -c   pit_server.c  -o pit_server.o
gcc  -O  -m64 iozone_solaris10gcc-64.o libasync10-64.o libbif10-64.o \
        -lthread -lpthread -lposix4 -lnsl -laio \
        -lsocket -o iozone
gcc  -O -m64 fileop_Solaris10gcc-64.o -o fileop
gcc  -O -m64 pit_server.o -lthread -lpthread -lposix4 \
        -lnsl -laio -lsocket -o pit_server
ld: fatal: file pit_server.o: wrong ELF class: ELFCLASS32
ld: fatal: File processing errors. No output written to pit_server
collect2: ld returned 1 exit status
*** Error code 1
make: Fatal error: Command failed for target `Solaris10gcc-64'

ELFCLASS32? I specified that I wanted 64bit! Looking at the output a bit closer it turned out that cc (which I sym-linked to gcc in /usr/local/bin) created a 32bit object file. This was easy to fix. Instead of

# cc -c   pit_server.c  -o pit_server.o

I executed

# gcc -m64  -c pit_server.c  -o pit_server.o

That created a 64bit object file:

bash-3.00# file pit_server.o
pit_server.o:   ELF 64-bit MSB relocatable SPARCV9 Version 1

Now the linking worked as well:

bash-3.00# gcc  -O -m64 pit_server.o -lthread -lpthread -lposix4 \
>         -lnsl -laio -lsocket -o pit_server
bash-3.00# echo $?
0

And finally pit_server was 64 bit:

bash-3.00# file pit_server
pit_server:     ELF 64-bit MSB executable SPARCV9 Version 1,
                dynamically linked, not stripped, no debugging information available

Happy benchmarking

SESSION_CACHED_CURSORS – Possibly Interesting Details

July 21, 2011 Have you ever wondered about the V$OPEN_CURSOR view, the SESSION_CACHED_CURSORS parameter, and the two session-level statistics “session cursor cache count” and “session cursor cache hits”?  I did after reading from two different sources that stated essentially that a larger shared pool would be required when the value for the SESSION_CACHED_CURSORS parameter is [...]

It's over, kind of

(No, not the blogging.)

The fact that I'm writing this post from my new home in London, rather than a hotel room or an airport lounge, should mean that the move from Edinburgh to London is done. If only it was that simple. As with any move, new things to take care of keep popping up like I'm playing an unenjoyable version of Whack-A-Mole.

Blogging is not the only activity that I've neglected for the past month or two so it's going to take me a while to clear my growing To Do list before I get back to it. Whilst the blog might appear to have dried up in recent times, I have a massive list of things to blog about when I find some time and energy. In the meantime and to help clear a couple of reminders in my Inbox (yes, I know it's the wrong place for reminders), here are a couple of resources I've been meaning to blog about.

For those of you who haven't been fortunate enough to attend one of the Real World Performance days, there are two opportunities to see what you've been missing. First, you could simply enjoy a nice trip to Edinburgh to see the real thing, which is bound to be better with the Q & A opportunities. Alternatively, you can sample a taste of Andrew Holdsworth on video here (Note that you'll need an Oracle SSO account to view them).

I also want to draw attention to Robin Moffat's blog. We have very similar interests in data warehouses and optimiser statistics (the two often go together) and we discussed his interesting post on Global Statistics and copying stats. With useful input from Maria Colgan of Oracle, I think the post captures as many strategic points - what should we be using this stuff for and how - as it does technical ones. Good stuff.

Maybe at some point I can come up with a blog post all of my own! ;-)

Oracle time units in V$ views

Oracle has a crazy mix of units of time in various v$ views

  • seconds
  • centi-seconds
  • milliseconds
  • microseconds

Some are straight forward such as time_waited_micro, but what unit is “TIME_WAITED”  or “WAIT_TIME” in? For example

v$session
WAIT_TIME -  centi
SECONDS_IN_WAIT

v$session_wait
WAIT_TIME – centi
SECONDS_IN_WAIT

v$system_event
TIME_WAITED – centi
AVERAGE_WAIT – centi
TIME_WAITED_MICRO

v$system_wait_class
TIME_WAITED – centi

v$eventmetric
TIME_WAITED – centi

v$waitclassmetric
DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” *
TIME_WAITED – centi

v$waitclassmetric_history
DBTIME_IN_WAIT – “percentage of the measured wait time that was actually in foregrounds and therefore part of DB time” *
TIME_WAITED – centi

dba_hist_system_event
TIME_WAITED_MICRO

v$active_session_history
WAIT_TIME -  micro, not for general use
TIME_WAITED – micro, only the last sample is fixed up, the others will have TIME_WAITED=0*

dba_hist_active_sess_history
WAIT_TIME -  micro , not for general use
TIME_WAITED = micro

v$session_wait_history

WAIT_TIME  – centi
WAIT_TIME_MICRO  – 11g only
TIME_SINCE_LAST_WAIT_MICRO – 11g only

in 10g, v$session_wait_history is pretty worthless IMO as one of the best uses of it would be to find average wait times for events, and even histograms of wait times and better yet,  correlating I/O sizes with I/O times, but alas as most interesting operations are in the micro to millisecond times and wait_time is in centi, most of the interesting data is lost, luckily this is fixed in 11g

 

With the list in one place it looks like everything is centi unless otherwise stated except for ASH  which is micro.

Please correct and/or add other examples to this list – thanks

* thanks to John Beresniewicz for this info.

Enabling and Reading event 10046 / SQL Trace

As I’m done with the book and back from a quick vacation (to Prague, which is an awesome place – well, at least during the summer) I promised (in Twitter) that now I’d start regularly writing blog articles again. In a separate tweet I asked what to write about. Among other requests (which I’ll write about later), one of the requests was to write something about enabling and reading SQL trace files… 

I am probably not going to write (much) about SQL trace for a single reason – Cary Millsap has already written a paper so good about this topic, that there’s no point for me to try to repeat it (and my paper wouldn’t probably be as clear as Cary’s).

So, if you want to get the most out of SQL Trace, read Cary’s Mastering Performance with Extended SQL Trace paper:

 

The above link directs you to Method-R’s article index, as there’s a lot of other useful stuff to read there.

Wow, now I’m done with my first request – to write something about SQL Trace :-)