Search

OakieTags

Who's online

There are currently 0 users and 27 guests online.

Recent comments

Affiliations

Oakies Blog Aggregator

date conversions

Dates are a surprising pain for correlating data an at the same time being understandable to the human reader.

For a generic form we can take seconds since 1970, on UNIX, with

$ date '+%s'
1311372938

but then how to we convert it back? There is a neat trick for that, “date -d @”

$ date -d @1311372938  +%H:%M:%S
22:15:38

so in scripts we can load up the date into seconds, that can be subtracted to get deltas and at the same time converted easily to any date format :

date_secs=` date '+%s'`
date -d @$date_secs +%H:%M:%S
22:23:34

 

AWK

AWK takes a bit of a trick to convert seconds to human format:

date_secs=1311372938
# setup the UNIX command
d="date -d @"date_secs" +%H:%M:%S"
# run the command to getline and put it into "date_human_format"
d | getline date_human_format
# be sure and close the open descriptor
close(d)

 

Dtrace
Dtrace will output nano seconds since 1970 in “walltimestamp” function, so just lop off the nanoseconds and you can convert it to date

date_secs=walltimestamp/1000000;

 

Oracle

Oracle  has the nice feature of returning the days along with fractional days when subtracting two dates, so it seems like just subtracting off 1970 should work and then multiplying by the seconds in a day

SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;

But since Oracle can already subtract dates there is little need to do the conversion unless extracting the data to be used by an external consumer.

If Oracle is reading data from an external source in seconds since 1970 we can just reverse the process

select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') + seconds_since_1970 / 86400 ),'DD-MON-YYYY') from dual;

example

SQL> select to_char((TO_DATE('01-JAN-1970','DD-MON-YYYY') +1311372938  / 86400 ),'DD-MON-YYYY') from dual;

TO_CHAR((TO
-----------
22-JUL-2011

SQL> SELECT (SYSDATE - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) from dual;

(SYSDATE-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
------------------------------------------------------
                                            1311424000

 

Oracle TIMESTAMP

One issue on Oracle though, the variable type timestamp can be a bit frustrating to work with, but it can be cast into a date, for example:

select cast(begin_interval_time as date ) from DBA_HIST_SNAPSHOT;

 

UPDATE: just found this link which is pretty good at covering a wide range of languages and databases:

http://www.epochconverter.com/

though the didn’t include AWK :)  – who uses AWK these days anyway?

Pythian Tools: Method R Profiler, MR Tools & MR Trace

Working with 100 talented database engineers is fun and there are lots going on — lots of exciting (and not so much) projects ongoing, huge amount of problems solved, mistakes made (and learned from), many unique (as well as routine) customer needs satisfied, many new (and old) methods applied, many good (and less so) tools [...]

Fastest £1,000 Server – back from supplier

At the risk of turning my Blog into some sort of half-way-house tweet update thing (correct, I’ve never logged into twitter), as a couple of people asked about the outcome with the broken £1,000 server, I’m happy to report it came back this week. The motherboard had died. I’d convinced myself it was the PSU when I trawled the net as it seems to be one of those things that is most likely to die having fired up in the first place, but no, the motherboard. I guess some solder “dried” or the pc pixies just don’t like me. One month turnaround is not very impressive…

They had another motherboard exactly the same in stock so I got a like-for-like swap. I was kind of hoping for a different one with more SATA3 and USB3 headers :-)

Now I’m trying to download the latest oracle 11 for 64 bit windows. I live out in the wilds of North Essex (for non-UK people, this is all of 62 Kilometers North-Northeast of London as the crow flies, so not exactly in an obscure and remote part of the UK! For those who DO know the UK, it is nothing like “the only way is Essex” out here. We have trees, fields, wildlife and a lack of youth culture.) As such, my broadband connect is sloooow. The connection keeps breaking and I lose the download. *tsch*. I’m sure I had a download manager somewhere which got around these issues…


Which Linux do you pick for Oracle Installations?

There was an interesting thread on the OakTable mailing list the other day regarding the choice of Linux distros for Oracle installations. It was started by one member (the name has been withheld to protect the innocent :) ) who said,

“I cannot imagine (but want to understand) why anyone would pick RHEL5.6 for Oracle as opposed to the vastly superior OEL with the UEK.”

I must admit I’ve kinda forgotten that any distro apart from Oracle Linux (OL) exists as far as production installations of Oracle software are concerned.

Some of the reasons cited for people not to pick OL include:

  • The customer has a long relationship with Red Hat and doesn’t want to jump ship.
  • RHEL is the market leading enterprise Linux distro, so why switch to Oracle?
  • The customer doesn’t want to be too dependent on Oracle.
  • The customer has lots of non-Oracle servers running RHEL and doesn’t want a mix of RHEL and OL as it would complicate administration.
  • The customer uses some software that is certified against RHEL, but not OL.
  • The customer prefers Red Hat support over Oracle support. Wait. Red Hat and support in the same sentence. Give me a minute to stop laughing…
  • The customer is using VMware for Virtualization and the Unbreakable Enterprise Kernel (UEK) is not supported on VMware.

I guess every company and individual will have differing justifications for their choice of distro.

So why would you pick OL and Unbreakable Enterprise Kernel (UEK) for Oracle installations?

  • You can run it for free if you don’t want OS support. Using OL without support doesn’t affect the support status of the products (DB, App Servers etc.) running on top of it.
  • It’s what Oracle use to write the Linux version of the products.
  • It’s what Exadata uses.
  • Oracle products are now certified against the OL + UEK before they are certified against the RHEL kernel.
  • UEK is typically a much more up to date version of the kernel than that shipped by RHEL and includes all the patches vital for optimum Oracle performance.
  • Single vendor, so no finger pointing over support issues (from Google+ comment).
  • It is the only enterprise Linux distro that supports kernel patching without reboots thanks to Oracle’s newly aquired Ksplice.

For more information you might want to read this whitepaper or watch this webcast.

If you are looking at things from a purely technical perspective, I guess you are going to pick OL and UEK. Of course, many of us don’t work in a world where technology is picked purely on its merits. :)

Cheers

Tim…

Update: Check out this post by Jay Weinshenker for a different angle on this issue.




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:

SELECT d1.c1  AS c1, 
       d1.c2  AS c2, 
       d1.c3  AS c3, 
       d1.c4  AS c4, 
       d1.c5  AS c5, 
       d1.c6  AS c6, 
       d1.c7  AS c7, 
       d1.c8  AS c8, 
       d1.c9  AS c9, 
       d1.c10 AS c10, 
       d1.c11 AS c11 
FROM   ((SELECT DISTINCT d1.c1        AS c1, 
                         Lpad(' ', 1) AS c2, 
                         d1.c3        AS c3, 
                         d1.c4        AS c4, 
                         d1.c5        AS c5, 
                         d1.c6        AS c6, 
                         d1.c7        AS c7, 
                         d1.c8        AS c8, 
                         d1.c9        AS c9, 
                         d1.c10       AS c10, 
                         d1.c11       AS c11 
         FROM   (SELECT d1.c1  AS c1, 
                        d1.c2  AS c2, 
                        d1.c3  AS c3, 
                        d1.c4  AS c4, 
                        d1.c5  AS c5, 
                        d1.c6  AS c6, 
                        d1.c7  AS c7, 
                        d1.c8  AS c8, 
                        d1.c9  AS c9, 
                        d1.c10 AS c10, 
                        d1.c11 AS c11 
                 FROM   (SELECT d1.c1                            AS c1, 
                                d1.c2                            AS c2, 
                                d1.c3                            AS c3, 
                                d1.c4                            AS c4, 
                                d1.c5                            AS c5, 
                                d1.c6                            AS c6, 
                                d1.c7                            AS c7, 
                                d1.c8                            AS c8, 
                                d1.c9                            AS c9, 
                                d1.c10                           AS c10, 
                                d1.c11                           AS c11, 
                                Row_number() OVER (PARTITION BY d1.c2, d1.c3, 
                                d1.c5, 
                                d1.c6 ORDER 
                                BY 
                                        d1.c2 ASC, 
                                d1.c3 ASC, d1.c5 ASC, d1.c6 ASC) AS c12 
                         FROM   (SELECT d1.c1 
                                        AS c1 
                                        , 
                                        d1.c2 
                                        AS c2 
                                        , 
                                        d1.c3 
                                        AS c3, 
                                        d1.c4 
                                        AS c4 
                                        , 
                                        d1.c5 
                                        AS c5, 
                                        d1.c6 
                                        AS c6 
                                        , 
                                        d1.c7 
                                        AS c7, 
                                        d1.c8 
                                        AS c8 
                                        , 
                                        d1.c9 
                                        AS c9, 
                                        MIN(d1.c10) OVER (PARTITION BY d1.c5, 
                                        d1.c2, 
                                        d1.c6, 
                                        d1.c3) AS 
                                        c10, 
                                        MIN(d1.c11) OVER (PARTITION BY d1.c2, 
                                        d1.c5, 
                                        d1.c6, 
                                        d1.c3) AS 
                                        c11 
                                 FROM   (SELECT d1.c1 AS c1, 
                                                d1.c2 AS c2, 
                                                d1.c3 AS c3, 
                                                d1.c4 AS c4, 
                                                d1.c5 AS c5, 
                                                d1.c6 AS c6, 
                                                d1.c7 AS c7, 
                                                d1.c8 AS c8, 
                                                d1.c9 AS c9, 
                                                CASE 
                                                  WHEN CASE d1.c12 
                                                         WHEN 1 THEN d1.c9 
                                                         ELSE NULL 
                                                       END IS NOT NULL THEN 
                                                  Rank() OVER ( PARTITION BY 
                                                  d1.c5 
                                                  ORDER BY 
                                                  CASE 
                                                  d1.c12 WHEN 1 THEN d1.c9 ELSE 
                                                  NULL END DESC NULLS LAST ) 
                                                END   AS c10, 
                                                CASE 
                                                  WHEN CASE d1.c13 
                                                         WHEN 1 THEN d1.c9 
                                                         ELSE NULL 
                                                       END IS NOT NULL THEN 
                                                  Rank() OVER ( ORDER BY CASE 
                                                  d1.c13 
                                                  WHEN 1 
                                                  THEN d1.c9 
                                                  ELSE NULL END DESC NULLS 
                                                  LAST ) 
                                                END   AS c11 
                                         FROM   (SELECT 
                                        0 
                                        AS 
                                        c1 
                                        , 
                                        d1.c7 
                                        AS c2, 
                                        d1.c8 
                                        AS 
                                        c3 
                                        , 
                                        d1.c9 
                                        AS c4, 
                                        d1.c10 
                                        AS 
                                        c5 
                                        , 
                                        d1.c11 
                                        AS c6, 
                                        CASE 
                                          WHEN d1.c6 = 0.0 THEN 0 
                                          ELSE Nvl(Nvl(d1.c5 + d1.c4 + d1.c3 + 
                                                       d1.c2 + 
                                                       d1.c1, 0) 
                                                   / 
                                                   Nullif(Nvl(d1.c6, 0), 0), 0) 
                                               * 100.0 
                                        END 
                                        AS 
                                        c7 
                                        , 
                                        d1.c6 
                                        AS c8, 
                                        d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1 
                                        AS 
                                        c9 
                                        , 
                                        Row_number() OVER (PARTITION BY d1.c10, 
                                        d1.c7, 
                                        d1.c11, 
                                        d1.c8 
                                        ORDER BY 
                                                d1.c10 
                                        DESC, d1.c7 DESC, d1.c11 DESC, d1.c8 
                                        DESC) AS 
                                        c12 
                                        , 
                                        Row_number() OVER 
                                        (PARTITION BY d1.c7, d1.c10, d1.c11, 
                                        d1.c8 
                                        ORDER BY 
                                                d1.c7 DESC 
                                        , d1.c10 DESC, d1.c11 DESC, d1.c8 DESC) 
                                        AS 
                                        c13 
                                                 FROM   (SELECT d2.c6  AS c1, 
                                                                d2.c7  AS c2, 
                                                                d2.c8  AS c3, 
                                                                d2.c9  AS c4, 
                                                                d2.c10 AS c5, 
                                                                d2.c11 AS c6, 
                                                                d1.c5  AS c7, 
                                                                d1.c1  AS c8, 
                                                                d1.c2  AS c9, 
                                                                d1.c3  AS c10, 
                                                                d1.c4  AS c11 
                                                         FROM   (SELECT DISTINCT 
         CONCAT( 
         CONCAT(t702396.child_nod_dtl, 
         ' - '), 
          t702396.node_descr) AS 
         c1, 
                          CASE 
         WHEN t702396.child_nod_dtl IN 
         (SELECT 
         member_char AS c1 
         FROM 
         wc_abunitbyreg_tree_closure 
         t702410 
         /* Dim_WC_ABUNITBYREG_TREE_CLOSURE_Receivables_Org */ 
         WHERE 
         ( 
         is_leaf 
              = 1 ) 
                                 ) 
                          THEN 1 
                            ELSE 0 
                          END 
         AS c2, 
         t702396.child_nod_dtl 
         AS c3, 
         t702396.tree_node 
         AS c4, 
         t31796.org_name 
         AS c5 
         FROM   wc_abunitbyreg_tree t702396 
         /* Dim_WC_ABUNITBYREG_TREE_Receivables_Org */ 
         , 
         wc_abunitbyreg_tree_closure 
         t702410 
         /* Dim_WC_ABUNITBYREG_TREE_CLOSURE_Receivables_Org */ 
         , 
         wc_ar_aging_accruals_f t699904 
         /* Dim_WC_AR_AGING_ACCRUALS_F */ 
         , 
         w_int_org_d t435564 
         /* Dim_W_INT_ORG_D_Receivables_Org */ 
         , 
         w_party_org_d t31796, 
         w_day_d t31328 
         /* Dim_W_DAY_D_Common */ 
         , 
         wc_ar_aging_accruals_f t699868 
         /* Fact_WC_AR_AGING_ACCRUALS_F */ 
         WHERE  ( t699868.row_wid = 
         t699904.row_wid 
         AND t435564.row_wid = 
         t699868.bu_wid 
         AND t31796.row_wid = 
         t699868.cust_wid 
         AND t31328.row_wid = 
         t699868.snapshot_dt_wid 
         AND t699868.business_unit = 
         t702410.member_char 
         AND t31328.cal_month = 6 
         AND t31328.cal_year = 2011 
         AND t699904.class = 'Billed' 
         AND t702396.child_nod_dtl = 
         t702410.ancestor_char 
         AND CONCAT( 
         CONCAT(t435564.x_consol_cd, 
         '-'), 
         t435564.x_consol_cd_longnm) = 
         'A-Transocean Inc & Subs' 
         AND ( t31796.x_customer_type IN 
         ( 'STD', 'UNS' ) ) 
         AND t699904.due_terms <> 'Not Aged' )) 
         d1, 
         (SELECT SUM(CASE 
         WHEN t699868.due_terms = 
         '> 365 Days' THEN 
         t699868.total_usd_due 
         ELSE 0 
         END)                   AS c6, 
         SUM(CASE 
         WHEN t699868.due_terms = 
         '91-365 Days' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END)                   AS c7, 
         SUM(CASE 
         WHEN t699868.due_terms = '61-90' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END)                   AS c8, 
         SUM(CASE 
         WHEN t699868.due_terms = '31-60' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END)                   AS c9, 
         SUM(CASE 
         WHEN t699868.due_terms = '1-30' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END)                   AS c10, 
         SUM(t699868.total_usd_due) AS c11, 
         t31796.org_name            AS c12, 
         t702396.child_nod_dtl      AS c13 
         FROM   wc_abunitbyreg_tree t702396 
         /* Dim_WC_ABUNITBYREG_TREE_Receivables_Org */ 
         , 
         wc_abunitbyreg_tree_closure t702410 
         /* Dim_WC_ABUNITBYREG_TREE_CLOSURE_Receivables_Org */ 
         , 
         wc_ar_aging_accruals_f t699904 
         /* Dim_WC_AR_AGING_ACCRUALS_F */ 
         , 
         w_int_org_d t435564 
         /* Dim_W_INT_ORG_D_Receivables_Org */ 
         , 
         w_party_org_d t31796, 
         w_day_d t31328 
         /* Dim_W_DAY_D_Common */ 
         , 
         wc_ar_aging_accruals_f t699868 
         /* Fact_WC_AR_AGING_ACCRUALS_F */ 
         WHERE  ( t699868.row_wid = t699904.row_wid 
         AND t435564.row_wid = t699868.bu_wid 
         AND t31796.row_wid = t699868.cust_wid 
         AND t31328.row_wid = 
         t699868.snapshot_dt_wid 
         AND t699868.business_unit = 
         t702410.member_char 
         AND t31328.cal_month = 6 
         AND t31328.cal_year = 2011 
         AND t699904.class = 'Billed' 
         AND t702396.child_nod_dtl = 
         t702410.ancestor_char 
         AND CONCAT(CONCAT(t435564.x_consol_cd, 
         '-'), 
         t435564.x_consol_cd_longnm) = 
         'A-Transocean Inc & Subs' 
         AND ( t31796.x_customer_type IN 
         ( 'STD', 'UNS' ) ) 
         AND t699904.due_terms <> 'Not Aged' ) 
         GROUP  BY t31796.org_name, 
         t702396.child_nod_dtl 
         HAVING SUM(CASE 
         WHEN t699868.due_terms = '1-30' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END) + SUM(CASE 
         WHEN t699868.due_terms 
         = 
         '31-60' THEN 
         t699868.total_usd_due 
         ELSE 0 
         END) + SUM(CASE 
         WHEN t699868.due_terms = 
         '61-90' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END) + SUM(CASE 
         WHEN t699868.due_terms = 
         '91-365 Days' 
         THEN 
         t699868.total_usd_due 
         ELSE 0 
         END) + SUM(CASE 
         WHEN t699868.due_terms = 
         '> 365 Days' THEN t699868.total_usd_due 
         ELSE 0 
         END) <> 
         0) d2 
         WHERE  ( d1.c3 = d2.c13 
         AND Sys_op_map_nonnull(d1.c5) = Sys_op_map_nonnull(d2.c12) )) 
         d1) d1) d1) d1 
         WHERE  ( ( d1.c5 IN ( 'APPLIED DRILLING', 'CHALLENGER MINERAL', 'FEA', 
         'GGA', 
         'IME', 'MED', 'NAM', 'NRS', 
         'NRY', 'SAM', 'TI', 'WAS' ) ) 
         AND d1.c10 <= 10 )) d1 
         WHERE  ( d1.c12 = 1 )) d1 
         UNION ALL 
         SELECT d1.c1  AS c1, 
                d1.c2  AS c2, 
                d1.c3  AS c3, 
                d1.c4  AS c4, 
                d1.c5  AS c5, 
                d1.c6  AS c6, 
                d1.c7  AS c7, 
                d1.c8  AS c8, 
                d1.c9  AS c9, 
                d1.c10 AS c10, 
                d1.c11 AS c11 
         FROM   (SELECT d1.c1 
                        AS c1 
                        , 
                        d1.c2 
                        AS c2, 
                        d1.c3 
                        AS c3 
                        , 
                        d1.c4 
                        AS c4, 
                        d1.c5 
                        AS c5 
                        , 
                        d1.c6 
                        AS c6, 
                        d1.c7 
                        AS c7 
                        , 
                        d1.c8 
                        AS c8, 
                        d1.c9 
                        AS c9 
                        , 
                        d1.c10 
                        AS c10, 
                        d1.c11 
                        AS 
                        c11, 
                        Row_number() OVER (PARTITION BY d1.c2 ORDER BY d1.c2 ASC 
                        ) AS 
                        c12 
                 FROM   (SELECT d1.c1                                 AS c1, 
                                d1.c2                                 AS c2, 
                                d1.c3                                 AS c3, 
                                d1.c4                                 AS c4, 
                                d1.c5                                 AS c5, 
                                d1.c6                                 AS c6, 
                                d1.c7                                 AS c7, 
                                d1.c8                                 AS c8, 
                                d1.c9                                 AS c9, 
                                d1.c10                                AS c10, 
                                MIN(d1.c11) OVER (PARTITION BY d1.c2) AS c11 
                         FROM   (SELECT d1.c1 AS c1, 
                                        d1.c2 AS c2, 
                                        d1.c3 AS c3, 
                                        d1.c4 AS c4, 
                                        d1.c5 AS c5, 
                                        d1.c6 AS c6, 
                                        d1.c7 AS c7, 
                                        d1.c8 AS c8, 
                                        d1.c9 AS c9, 
                                        CASE 
                                          WHEN d1.c9 IS NOT NULL THEN 
                                          Rank() OVER ( PARTITION BY d1.c12 
                                          ORDER BY 
                                          d1.c9 
                                          DESC NULLS LAST ) 
                                        END   AS c10, 
                                        CASE 
                                          WHEN CASE d1.c13 
                                                 WHEN 1 THEN d1.c9 
                                                 ELSE NULL 
                                               END IS NOT NULL THEN 
                                          Rank() OVER ( ORDER BY CASE 
                                          d1.c13 
                                          WHEN 1 
                                          THEN d1.c9 
                                          ELSE NULL END DESC NULLS 
                                          LAST ) 
                                        END   AS c11 
                                 FROM   (SELECT 1 
                                                AS c1 
                                                , 
                                                d1.c7 
                                                AS c2 
                                                , 
                                                Lpad(' ', 1) 
                                                AS c3, 
                                                CAST(NULL AS INTEGER) 
                                                AS c4 
                                                , 
                                                Lpad(' ', 1) 
                                                AS c5, 
                                                Lpad(' ', 1) 
                                                AS c6 
                                                , 
                                                CASE 
                                                  WHEN d1.c6 = 0.0 THEN 0 
                                                  ELSE Nvl(Nvl(d1.c5 + d1.c4 + 
                                                               d1.c3 + 
                                                               d1.c2 + 
                                                               d1.c1, 0) 
                                                           / 
                                                           Nullif(Nvl(d1.c6, 0), 
                                                           0), 0) 
                                                       * 100.0 
                                                END 
                                                AS c7 
                                                , 
                                                d1.c6 
                                                AS c8, 
                                                d1.c5 + d1.c4 + d1.c3 + d1.c2 + 
                                                d1.c1 
                                                AS c9 
                                                , 
                                                d1.c9 
                                                AS c12, 
                                                Row_number() OVER (PARTITION BY 
                                                d1.c7 
                                                ORDER BY 
                                                d1.c7 
                                                DESC) AS 
                                                c13 
                                         FROM   (SELECT SUM(d1.c1) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c1, 
                                                        SUM(d1.c2) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c2, 
                                                        SUM(d1.c3) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c3, 
                                                        SUM(d1.c4) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c4, 
                                                        SUM(d1.c8) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c5, 
                                                        SUM(d1.c6) OVER ( 
                                                        PARTITION BY 
                                                        d1.c7) AS 
                                                        c6, 
                                                        d1.c7 
                                                        AS 
                                                        c7, 
                                                        d1.c8 
                                                        AS 
                                                        c8, 
                                                        d1.c9 
                                                        AS 
                                                        c9 
                                                 FROM   (SELECT SUM(CASE 
                                                        WHEN t699868.due_terms = 
                                                             '> 365 Days' THEN 
                                                        t699868.total_usd_due 
                                                        ELSE 0 
                                                                    END) 
                                                                AS c1 
                                                                , 
                SUM(CASE 
                      WHEN t699868.due_terms = 
                           '91-365 Days' 
                    THEN 
                      t699868.total_usd_due 
                      ELSE 0 
                    END)                   AS c2, 
                SUM(CASE 
                      WHEN t699868.due_terms = '61-90' 
                    THEN 
                      t699868.total_usd_due 
                      ELSE 0 
                    END)                   AS c3, 
                SUM(CASE 
                      WHEN t699868.due_terms = '31-60' 
                    THEN 
                      t699868.total_usd_due 
                      ELSE 0 
                    END)                   AS c4, 
                SUM(t699868.total_usd_due) AS c6, 
                t31796.org_name            AS c7, 
                SUM(CASE 
                      WHEN t699868.due_terms = '1-30' 
                    THEN 
                      t699868.total_usd_due 
                      ELSE 0 
                    END)                   AS c8, 
                t702396.child_nod_dtl      AS c9 
                FROM   wc_abunitbyreg_tree t702396 
                /* Dim_WC_ABUNITBYREG_TREE_Receivables_Org */ 
                , 
                wc_abunitbyreg_tree_closure t702410 
                /* Dim_WC_ABUNITBYREG_TREE_CLOSURE_Receivables_Org */ 
                , 
                wc_ar_aging_accruals_f t699904 
                /* Dim_WC_AR_AGING_ACCRUALS_F */ 
                , 
                w_int_org_d t435564 
                /* Dim_W_INT_ORG_D_Receivables_Org */ 
                , 
                w_party_org_d t31796, 
                w_day_d t31328 
                /* Dim_W_DAY_D_Common */ 
                , 
                wc_ar_aging_accruals_f t699868 
                /* Fact_WC_AR_AGING_ACCRUALS_F */ 
                WHERE  ( t699868.row_wid = t699904.row_wid 
                  AND t435564.row_wid = t699868.bu_wid 
                  AND t31796.row_wid = t699868.cust_wid 
                  AND t31328.row_wid = 
                      t699868.snapshot_dt_wid 
                  AND t699868.business_unit = 
                      t702410.member_char 
                  AND t31328.cal_month = 6 
                  AND t31328.cal_year = 2011 
                  AND t699904.class = 'Billed' 
                  AND t702396.child_nod_dtl = 
                      t702410.ancestor_char 
                  AND t702396.child_nod_dtl = 'TI' 
                  AND CONCAT(CONCAT(t435564.x_consol_cd, 
                             '-'), 
                      t435564.x_consol_cd_longnm) = 
                      'A-Transocean Inc & Subs' 
                  AND ( t31796.x_customer_type IN 
                        ( 'STD', 'UNS' ) ) 
                  AND t699904.due_terms <> 'Not Aged' ) 
                GROUP  BY t31796.org_name, 
                   t702396.child_nod_dtl) d1) d1 
                WHERE  ( d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1 <> 0 )) d1) d1) 
                d1 
                 WHERE  ( d1.c10 <= 10 )) d1 
         WHERE  ( d1.c12 = 1 ) 
         UNION ALL 
         SELECT d1.c1  AS c1, 
                d1.c2  AS c2, 
                d1.c3  AS c3, 
                d1.c4  AS c4, 
                d1.c5  AS c5, 
                d1.c6  AS c6, 
                d1.c7  AS c7, 
                d1.c8  AS c8, 
                d1.c9  AS c9, 
                d1.c10 AS c10, 
                d1.c11 AS c11 
         FROM   (SELECT 2                                     AS c1, 
                        d1.c7                                 AS c2, 
                        Lpad(' ', 1)                          AS c3, 
                        CAST(NULL AS INTEGER)                 AS c4, 
                        d1.c8                                 AS c5, 
                        Lpad(' ', 1)                          AS c6, 
                        CASE 
                          WHEN d1.c6 = 0.0 THEN 0 
                          ELSE Nvl(Nvl(d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1, 0) 
                                   / 
                                   Nullif(Nvl(d1.c6, 0), 0), 0) 
                               * 100.0 
                        END                                   AS c7, 
                        d1.c6                                 AS c8, 
                        d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1 AS c9, 
                        CAST(NULL AS INTEGER)                 AS c10, 
                        CASE 
                          WHEN d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1 IS NOT NULL 
                        THEN 
                          Rank() OVER ( ORDER BY d1.c5 + d1.c4 + d1.c3 + d1.c2 + 
                          d1.c1 
                          DESC 
                          NULLS LAST ) 
                        END                                   AS c11, 
                        CASE 
                          WHEN d1.c5 + d1.c4 + d1.c3 + d1.c2 + d1.c1 IS NOT NULL 
                        THEN 
                          Rank() OVER ( PARTITION BY d1.c8 ORDER BY d1.c5 + 
                          d1.c4 + 
                          d1.c3 
                          + 
                          d1.c2 + 
                          d1.c1 
                          DESC NULLS LAST ) 
                        END                                   AS c12 
                 FROM   (SELECT SUM(CASE 
                                      WHEN t699868.due_terms = '> 365 Days' THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END)                   AS c1, 
                                SUM(CASE 
                                      WHEN t699868.due_terms = '91-365 Days' 
                                    THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END)                   AS c2, 
                                SUM(CASE 
                                      WHEN t699868.due_terms = '61-90' THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END)                   AS c3, 
                                SUM(CASE 
                                      WHEN t699868.due_terms = '31-60' THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END)                   AS c4, 
                                SUM(CASE 
                                      WHEN t699868.due_terms = '1-30' THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END)                   AS c5, 
                                SUM(t699868.total_usd_due) AS c6, 
                                t31796.org_name            AS c7, 
                                t702396.child_nod_dtl      AS c8 
                         FROM   wc_abunitbyreg_tree t702396 
                                /* Dim_WC_ABUNITBYREG_TREE_Receivables_Org */ 
                                , 
                                wc_abunitbyreg_tree_closure t702410 
                                /* Dim_WC_ABUNITBYREG_TREE_CLOSURE_Receivables_Org */ 
                                , 
                                wc_ar_aging_accruals_f t699904 
                                /* Dim_WC_AR_AGING_ACCRUALS_F */ 
                                , 
                                w_int_org_d t435564 
                                /* Dim_W_INT_ORG_D_Receivables_Org */ 
                                , 
                                w_party_org_d t31796, 
                                w_day_d t31328 
                                /* Dim_W_DAY_D_Common */ 
                                , 
                                wc_ar_aging_accruals_f t699868 
                         /* Fact_WC_AR_AGING_ACCRUALS_F */ 
                         WHERE  ( t699868.row_wid = t699904.row_wid 
                                  AND t435564.row_wid = t699868.bu_wid 
                                  AND t31796.row_wid = t699868.cust_wid 
                                  AND t31328.row_wid = t699868.snapshot_dt_wid 
                                  AND t699868.business_unit = 
                                      t702410.member_char 
                                  AND t31328.cal_month = 6 
                                  AND t31328.cal_year = 2011 
                                  AND t699904.class = 'Billed' 
                                  AND t702396.child_nod_dtl = 
                                      t702410.ancestor_char 
                                  AND t702396.child_nod_dtl = 'TI' 
                                  AND CONCAT(CONCAT(t435564.x_consol_cd, '-'), 
                                      t435564.x_consol_cd_longnm) = 
                                      'A-Transocean Inc & Subs' 
                                  AND ( t31796.x_customer_type IN 
                                        ( 'STD', 'UNS' ) ) 
                                  AND t699904.due_terms <> 'Not Aged' ) 
                         GROUP  BY t31796.org_name, 
                                   t702396.child_nod_dtl 
                         HAVING SUM(CASE 
                                      WHEN t699868.due_terms = '1-30' THEN 
                                      t699868.total_usd_due 
                                      ELSE 0 
                                    END) + SUM(CASE 
                                                 WHEN t699868.due_terms = 
                                                      '31-60' THEN 
                                                 t699868.total_usd_due 
                                                 ELSE 0 
                                               END) + SUM(CASE 
                                WHEN t699868.due_terms = 
                                     '61-90' 
                                                          THEN 
t699868.total_usd_due 
ELSE 0 
END) + SUM(CASE 
WHEN t699868.due_terms = 
'91-365 Days' 
         THEN 
t699868.total_usd_due 
           ELSE 0 
         END) + SUM(CASE 
WHEN t699868.due_terms = 
'> 365 Days' THEN t699868.total_usd_due 
ELSE 0 
                    END) <> 
0 
) d1) 
d1 
WHERE  ( d1.c12 <= 10 ))) d1 
ORDER  BY c1

Simple right?

As expected based on the problem description, there were many cursors with different plans and very different statistics in the shared pool. So I had them run a script showing some statistical info about the various plans:

SQL_ID        PLAN_HASH_VALUE          EXECS    AVG_ETIME AVG_CPU_TIME          AVG_LIO         AVG_PIO
------------- --------------- -------------- ------------ ------------ ---------------- - ---------------
9tx5b0cctd8j5      1512746808              5         .618         .398          5,608.4             8.0
9tx5b0cctd8j5       310704641              1        1.496         .900          4,672.0             6.0
9tx5b0cctd8j5      2273374088              1        1.531         .950          5,810.0             8.0
9tx5b0cctd8j5      2878409905              1        1.432        1.080          5,810.0             8.0
9tx5b0cctd8j5      2225548639              1       14.193       10.310      1,057,712.0             8.0
9tx5b0cctd8j5      2174863591              1       13.623       10.660      1,049,157.0             8.0
9tx5b0cctd8j5      2214096856              1       13.573       10.720      1,057,955.0             8.0
9tx5b0cctd8j5      4111066445              1       13.187       11.090      1,049,157.0            10.0
9tx5b0cctd8j5      2307125907              1       13.411       11.200      1,057,779.0             8.0
9tx5b0cctd8j5       983902177              2       12.356       11.245      1,057,614.5            70.5
9tx5b0cctd8j5      2070474756              1       12.940       11.310      1,049,157.0             8.0
9tx5b0cctd8j5       986683435              1       12.158       11.590      1,026,638.0             8.0
9tx5b0cctd8j5      2004450975              1       12.463       11.700      1,049,167.0             8.0
9tx5b0cctd8j5      3593472063              1       12.336       11.710      1,026,638.0            30.0
9tx5b0cctd8j5       453801037              1       13.494       11.820      1,057,931.0             8.0
9tx5b0cctd8j5      3874155392              1       12.252       11.850      1,049,157.0             8.0
9tx5b0cctd8j5      1048579546              1       12.974       11.930      1,057,712.0             8.0
9tx5b0cctd8j5       936861260              1       72.135       66.720     11,711,783.0             2.0
9tx5b0cctd8j5      2279755221              2      128.250      121.020     21,775,758.0              .0
9tx5b0cctd8j5      1351237956              1      554.598      495.800     82,680,484.0             1.0
9tx5b0cctd8j5       116866680              2      539.060      521.715     83,920,850.5              .0
9tx5b0cctd8j5      3869018014              1    1,230.521      585.880    117,828,348.0              .0
9tx5b0cctd8j5      3732072456              2      662.755      644.470    104,823,026.5             2.0
9tx5b0cctd8j5      3282940025              1      825.540      811.690    132,362,095.0              .0
9tx5b0cctd8j5      2292909572              1      957.555      923.070    153,126,414.0             3.0

As you can imagine, the plans are pretty long as well. I’ll spare you the detail of them but show you the notes section of the plans:

 
-- one of the 12 second ones
 
Note
-----
   - star transformation used for this statement
 
 
-- multiple slow ones
 
Note
-----
   - star transformation used for this statement
   - cardinality feedback used for this statement

One of the statements showed about a 12 second average execution time without a note saying cardinality feedback had been used. The rest of the plans showed that cardinality feedback had been used. I suggested that maybe cardinality feedback was introducing the instability and disabling it might be a reasonable thing to try. They tried it using an alter session and the statement consistently ran in 11-12 seconds. I should note that you can also disable this feature for an individual statement by using an OPT_PARAM hint like so:

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) */ …

By the way, as you may have already noticed, it’s clear from the stats that the problem description did not exactly match the data. If you’ll look back at the stats by plan hash value output you’ll see that there are also several plans that produced results in the 1 second range (which they failed to mention). It appears that cardinality feedback actually enabled some pretty good plans that reduced the time by an order of magnitude from the original 12 second plan . But the client wasn’t really looking for the absolute best performance, what they were looking for was stability. They were actually quite happy with 12 seconds, as long as it did that all the time. Had I had access to the system we might have a attempted to lock in the 1 second plan. But this was all done via email without actually logging into the system (which is a bit like performing surgery with a blind fold on – with someone else describing what you’re cutting in to).

I’m used to seeing plan stability issues (often caused by bind variable peeking), but this is the first time I’ve seen this type of behavior due to the new 11g Cardinality Feedback feature. Generally speaking, this feature is a really good idea because it compares cardinality estimates that the optimizer calculates with actual row counts when a query runs. This is actually a very good technique for SQL tuning and Wolfgang Breitling has written an excellent paper on the subject. The basic idea is that if the estimated cardinality and the actual row counts are way off on any of the steps, the optimizer can automatically apply a fudge factor to correct the optimizer’s calculation. The correction is exposed in a 10053 trace file by the application of an OPT_ESTIMATE hint (you know, the same one that is used by SQL Profiles created by the SQL Tuning Advisor). Unfortunately, these hints are not exposed in the OTHER_XML field of the V$SQL_PLAN table or any place else that I’ve uncovered thus far (although it must be stored in memory somewhere). So the only way I know to determine what modifications are being made is to Wolfgang the statement and look at the trace file. Here’s a little excerpt from a 10053 trace file that has used this feature:

 
...
 
******************************************
----- Current SQL Statement for this session (sql_id=2ajw1uv0gpzwq) -----
select product_name
from oe.order_items o, oe.product_information p
where o.unit_price = 15 and quantity > 1
and p.product_id = o.product_id
*******************************************
...
 
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=1.000000 ) OPT_ESTIMATE (INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=1.000000 ) OPT_ESTIMATE (INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=1.000000 ) OPT_ESTIMATE (TABLE "O" ROWS=13.000000 ) */ "P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID"
kkoqbc: optimizing query block SEL$1 (#0)
 
...
 
Content of other_xml column
===========================
  cardinality_feedback: yes
  db_version     : 11.2.0.2
  parse_schema   : SYS
  plan_hash      : 1553478007
  plan_hash_2    : 2615131494
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "O"@"SEL$1")
      FULL(@"SEL$1" "P"@"SEL$1")
      LEADING(@"SEL$1" "O"@"SEL$1" "P"@"SEL$1")
      USE_HASH(@"SEL$1" "P"@"SEL$1")
    END_OUTLINE_DATA
  */
 
...

You can see all the OPT_ESTIMATE hints that have been added to the statement. V$SQL_SHARED_CURSOR does have a flag to indicate whether a cursor has been marked to use this feature. So you can do a little investigation on your own system to see how often this feature is kicking in.

SYS@SANDBOX1> @find_card_feedback_cursors.sql
SYS@SANDBOX1> col sql_text for a80 word_wrap
SYS@SANDBOX1> select a.sql_id, child_number child_no, b.sql_text from v$sql_shared_cursor a, v$sqlstats b
  2  where a.sql_id = b.sql_id
  3  and USE_FEEDBACK_STATS = 'Y'
  4  and a.sql_id like nvl('&sql_id',a.sql_id)
  5  and upper(sql_text) like upper(nvl('&sql_text',sql_text))
  6  and child_number > 0
  7  order by 1, 2
  8  /
Enter value for sql_id: 
Enter value for sql_text: %oe.%
 
SQL_ID          CHILD_NO SQL_TEXT
------------- ---------- --------------------------------------------------------------------------------
0cm4r08vj075r          2 select /*+ monitor */ product_name from oe.order_items o, oe.product_information
                         p where o.unit_price = 15 and quantity > 1 and p.product_id = o.product_id
 
0cm4r08vj075r          3 select /*+ monitor */ product_name from oe.order_items o, oe.product_information
                         p where o.unit_price = 15 and quantity > 1 and p.product_id = o.product_id
 
2ajw1uv0gpzwq          1 select product_name from oe.order_items o, oe.product_information p where
                         o.unit_price = 15 and quantity > 1 and p.product_id = o.product_id

This feature is similar to another 11g feature call Adaptive Cursor Sharing which also attempts to automatically recognize performance problems and create new cursors to overcome the issues. Both features share a couple of traits.

  1. The statement must be run at least once without benefit of the feature in order to discover that there is a potential problem.
  2. The data collected about the statement is not persisted. So any event that causes a statement to be flushed from the shared pool will cause the “learning process” to be repeated.

There is not much available on the web about this feature yet. Here are a few posts that provide some insight.

By Tom Kite
By Dion Cho
By The Optimizer Development Group

One of the comments that Tom makes in his discussion is that SQL Plan Management (Baselines) could be used to curb potential instability issues caused by this new feature. I thought this was an interesting idea so I thought it might be worthwhile to look into the interaction between the Cardinality Feedback feature and Baselines. Note, the client I worked with on the aforementioned issue had actually tried using baselines to keep the plans from changing. But without having access to the system it was just too difficult to try to understand what had or hadn’t been done with the baselines. So I did a little investigation on our Exadata lab environment. After finding a statement that causes Cardinality Feedback to kick in, I flushed the shared pool and ran it a few times. I then create a baseline to force it to go back to the original plan.

SYS@SANDBOX1> !cat b.sql
select /*+ monitor */ product_name
from oe.order_items o, oe.product_information p
where o.unit_price = 15 and quantity > 1
and p.product_id = o.product_id; 
 
SYS@SANDBOX1> set termout off
SYS@SANDBOX1> @b
. . .
SYS@SANDBOX1> @b
SYS@SANDBOX1> set termout on
SYS@SANDBOX1> 
SYS@SANDBOX1> -- let's check the shared pool now
SYS@SANDBOX1> @fsx
Enter value for sql_text: 
Enter value for sql_id: 0cm4r08vj075r
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
0cm4r08vj075r      0 1255158658      1        .04      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
0cm4r08vj075r      1 1553478007      4        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
 
2 rows selected.
SYS@SANDBOX1>  
SYS@SANDBOX1> -- yes we have two cursors with different plans
SYS@SANDBOX1> -- let's see what the plans look like
SYS@SANDBOX1> @dplan
Enter value for sql_id: 0cm4r08vj075r
Enter value for child_no: 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0cm4r08vj075r, child number 0
-------------------------------------
select /*+ monitor */ product_name from oe.order_items o,
oe.product_information p where o.unit_price = 15 and quantity > 1 and
p.product_id = o.product_id
 
Plan hash value: 1255158658
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |       |       |     7 (100)|          |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        |     4 |   128 |     7   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL | ORDER_ITEMS            |     4 |    48 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - storage(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
 
SQL_ID  0cm4r08vj075r, child number 1
-------------------------------------
select /*+ monitor */ product_name from oe.order_items o,
oe.product_information p where o.unit_price = 15 and quantity > 1 and
p.product_id = o.product_id
 
Plan hash value: 1553478007
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                     |       |       |     9 (100)|          |
|*  1 |  HASH JOIN                 |                     |    13 |   416 |     9  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS STORAGE FULL| ORDER_ITEMS         |    13 |   156 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS STORAGE FULL| PRODUCT_INFORMATION |   288 |  5760 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
   2 - storage(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
 
Note
-----
   - cardinality feedback used for this statement
 
 
54 rows selected.
SYS@SANDBOX1> --
SYS@SANDBOX1> -- so the second cursor (1) has a new plan (1553478007) because of cardinality feedback
SYS@SANDBOX1> -- let's create a baseline to force it to stick with the original plan (1255158658)
SYS@SANDBOX1> @create_baseline
Enter value for sql_id: 0cm4r08vj075r
Enter value for plan_hash_value: 1255158658
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (SQLID_sqlid_planhashvalue): 
sql_id: 0cm4r08vj075r
plan_hash_value: 1255158658
fixed: NO
enabled: YES
plan_name: SQLID_0cm4r08vj075r_1255158658
sql_handle: SQL_7912d4b3adf9c3b4
Baseline SQLID_0cm4r08vj075r_1255158658 created.
 
PL/SQL procedure successfully completed.
 
SYS@SANDBOX1> -- now let's run the statement a few more times
SYS@SANDBOX1> set termout off
SYS@SANDBOX1> @b
SYS@SANDBOX1> @b
. . .
SYS@SANDBOX1> @b
SYS@SANDBOX1> @b
SYS@SANDBOX1> set termout on
 
SYS@SANDBOX1> -- and check the children and plans
SYS@SANDBOX1> @fsx
Enter value for sql_text: 
Enter value for sql_id: 0cm4r08vj075r
 
SQL_ID         CHILD  PLAN_HASH  EXECS  AVG_ETIME AVG_PX OFFLOAD IO_SAVED_% SQL_TEXT
------------- ------ ---------- ------ ---------- ------ ------- ---------- ----------------------------------------------------------------------
0cm4r08vj075r      0 1255158658      1        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
0cm4r08vj075r      1 1553478007      1        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
0cm4r08vj075r      2 1255158658      1        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
0cm4r08vj075r      3 1255158658      1        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i
0cm4r08vj075r      5 1255158658      4        .00      0 No             .00 select /*+ monitor */ product_name from oe.order_items o, oe.product_i

Interesting that it created a bunch of new cursors – 2,3 (presumably 4 that got flushed for some reason) and 5. Looks like it finally settled down with 5, since child 5 has been executed 4 times. And look what the plan looks like for the one it settled down on:

 
SYS@SANDBOX1> @dplan
Enter value for sql_id: 0cm4r08vj075r
Enter value for child_no: 5
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0cm4r08vj075r, child number 5
-------------------------------------
select /*+ monitor */ product_name from oe.order_items o,
oe.product_information p where o.unit_price = 15 and quantity > 1 and
p.product_id = o.product_id
 
Plan hash value: 1255158658
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                        |       |       |    16 (100)|          |
|   1 |  NESTED LOOPS                |                        |       |       |            |          |
|   2 |   NESTED LOOPS               |                        |    13 |   416 |    16   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS STORAGE FULL | ORDER_ITEMS            |    13 |   156 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     1 |    20 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - storage(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
       filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))
   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")
 
Note
-----
   - SQL plan baseline SQLID_0CM4R08VJ075R_1255158658 used for this statement
   - cardinality feedback used for this statement
 
 
31 rows selected.

Hmmm … Oracle says it used both the baseline and cardinality feedback, but the plan stayed the same as without the cardinality feedback. It seems a little weird that cardinality feedback would be applied to statement that already has a baseline on it. But it makes sense that the plan would stay consistent because the hints would generally limit the CBO’s choices down to the point where adjustments to cardinality calculations wouldn’t matter. At any rate, it’s an interesting bit of trivia.

Note that I have used several scripts in this post:

  1. create_baseline.sql – create a Baseline on a statement in the shared pool
  2. dplan.sql – produces XPLAN output for a statement in the shared pool
  3. find_card_feedback_cursors.sql – looks for cursors using cardinality feedback
  4. fsx.sql – shows SQL statements in the shared pool along with indication if Exadata Smart Scans were used

Cardinality Feedback Wrap Up

In general I think Cardinality Feedback is a great new feature, but in this particular case it did introduce some instability. This may be the result of some unintended behavior but we did not open an SR to track that down since disabling the feature provided an adequate solution in this case. I would still like to find where it stores those fudge factors used by the OPT_ESTIMATE hints. I was hoping I could find it in one of the SQL Profile objects like sqlobj$data, but haven’t had any luck with that line of investigation so far. So if you have any ideas please let me know.

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