Search

OakieTags

Who's online

There are currently 0 users and 34 guests online.

Recent comments

Oakies Blog Aggregator

KScope13 conference in New Orleans

ImSpeaking Kscope13

Please come join us in the Big Easy — also, check out the no-extra-cost Hands-On Labs scheduled throughout the conference.

Lock Bug

Here’s an oddity that I ran into a little while ago while trying to prepare a sample trace file showing a particular locking pattern; it was something that I’d done before, but trace files can change with different versions of Oracle so I decided to use a copy of 11.2.0.2 that happened to be handy at the time to check if anything had changed since the previous (11gR1) release. I never managed to finish the test; here are the steps I got through:


-- created a table t1 to work with, no data needed, column definitions don't matter.

Session 1:
lock table t1 in row exclusive mode;        -- mode 3, lock acquired

Session 2:
lock table t1 in row exclusive mode;        -- mode 3, lock acquired

Session 3:
lock table t1 in exclusive mode;            -- mode 6, goes into wait for session 1

Session 1 (again):
lock table t1 in share row exclusive mode;  -- mode 5, should move to converter queue, wait for session 2

The mode 3 to mode 5 conversion is what happens if you have a foreign key referential integrity constraint without a covering index and run code that does something like “delete child rows for parent X, delete parent X” (which also happens under the covers if your foreign key is declared as “on delete cascade”).

Notice the “should” on the lock conversion line – this is exactly what happens in 10.2.0.3 and 11.2.0.3; but on the system I was using session 1 got an immediate deadlock (ORA-00060) error – in the absence of a deadlock scenario !

My problem is this – the instance that gives the deadlock error is 64-bit 11.2.0.2 running RAC on Linux (OEL 5); the instances that don’t give the error are 32-bit, non-RAC, running on Windows XP Pro. SO is the anomaly due to some difference in:

  • Windows vs. Linux
  • 32 bit vs. 64 bit
  • RAC vs. non-RAC
  • Specific version of Oracle

I’m inclined to think it’s a version dependent bug, but it’s possible that it’s a necessary side effect of RAC. So if you’ve got 11.2.0.3 RAC, or 11.2.0.2 non-RAC, or the terminal releases of 10g and 11.1 whether RAC or non-RAC, I’d like to hear from you which versions – if any – produce the same deadlock and which don’t. (And the scientific method being what it is, anyone with 11.2.0.2 on RAC on Linux might like to confirm – or contradict – my result.)

Update 8:30 p.m. GMT

The results in so far tend to support the idea that this may be a RAC-related issue. Justin Mitchell’s result is particularly interesting, and I confirmed on my 11.2.0.2 RAC system that if the two starting sessions are on different nodes then I don’t get the deadlock behaviour.

Users of RAC will probably be aware of the fact that v$lock.block commonly sets itself to 2 (potential blocker) on RAC the moment you acquire the lock; and this is what happens to both the “row exclusive” locks. When we request the exclusive lock, both TM locks change from block=2 to block=1 if they are on the same node; if they are on different nodes then only one of them changes.

Unfortunately Oleksandr didn’t see a deadlock when he tried RAC 11.2.0.3 – and since he didn’t make any comment about using multiple nodes we will have to wait to see if he can repeat the test and tell us whether his results are consistent with Justin’s, or whether they suggest that there are further considerations in effect.

10053 Trace Files - Different Plan in Different Environments

Rather than just describing the contents of the trace file, I thought it might be a good idea to tie the various sections into how they might help you solve Real WorldTM problems. Which might not be immediately obvious when the first example I use is the trace file for :- 

SELECT * FROM DUAL;

But here it is. The first thing to note is that it's a 66KB file of over 2000 lines, even for something so trivial, which is just a taste of just how massive these files can be. It will also be environment and version-specific, as you'll see. Such is the nature of low-level trace files.

Going through the initial sections at a very high level, we have ....

Lines 1-20
- The standard type of trace file pre-amble that you might have seen in other trace files including

- The trace file name
- Instance and version information
- Host and O/S information
- Session Instrumentation of the type set by calls to DBMS_APPLICATION_INFO

Then we get to the first 10053-specific information (lines 22-27) which registers the various Query Blocks in this query. Understandably there is only one entry here in the QUERY BLOCK SIGNATURE section, were Oracle automatically names the query block to SEL$1

Line 28 is a note from SQL Plan Management highlighting that this statement does not already exist in the SQL Management Base.

Lines 29-32 contain a note that 11g Auto-DOP is disabled and at this point hopefully you'll start to see that if you already have a reasonable understanding of the CBO and related features, the trace file is actually pretty descriptive and verbose. From memory, I'm not sure it was always as easy to read.

For more information on Predicate Move-Around (as mentioned in lines 33-35), this 1994 VLDB paper is worth a look. Of course, when your statement is SELECT * FROM DUAL, there aren't exactly a lot of predicates to move around! ;-)

Next we go into a long section describing OPTIMIZER INFORMATION gathered from a variety of sources.

Line 40 shows the SQL statement and dont underestimate how important this is as further confirmation that you're looking at the right tracefile.

Lines 42-94 are a very handy Legend that lists the abbreviations that are used in the trace file. Some of these might have been guessable but, with so many terms used, it's great that you don't have to guess any more.

Lines 95-419 are a section that I often find very handy in solving issues with bad plans in two different database environments. The classic case of a developer telling me that it runs fine in Test but not in Production. The developer might send along the two plans and, even with a couple of good SQL Monitoring reports or the output of DBMS_XPLAN, that doesn't really tell me why the two plans are different, just that they are different. Working in an environment with multiple Dev, Test and Prod environments, it's not unusual to find that there is some drift in the instance configurations or someone has different session parameters set. It's a quick job to just open up the two trace files in a visual diff tool and make absolutely sure that the parameters the optimizer references (and you'll see just how many there are these days!) are truly identical.

It's just a small tip, but you'd be surprised by the number of issues that's helped me identify!

Big Data is the Commercial Supercomputing in the Age of Datafication

class="l-submain">
class="l-submain-h g-html i-cf">
id="attachment_53081" style="width: 160px" class="wp-caption alignleft">NERSC's Hopper NERSC; Design: Caitlin Youngquist/LBNL Photo: Roy Kaltschmidt/LBNL
class="wp-caption-text">NERSC’s Hopper NERSC; Design: Caitlin Youngquist/LBNL Photo: Roy Kaltschmidt/LBNL

I’ve been reading a book by my good friend Jeff Needham “Disruptive Possibilities: How Big Data Changes Everything” and it cemented some thoughts that had been forming in my head for a while and gave me bunch of new insights. Jeff managed to pack an incredible amount of information in a very concise form. I thoroughly recommend getting a copy for yourself when it’s published. (I’ve got an author’s copy from Hortonworks at StrataConf in Santa Clara few weeks ago.) id="more-53059">

Supercomputers, and later High Performance Computing (HPC), have been around for a while. However, they were only available to organizations and projects that were extremely well funded. Military defense and intelligence departments are a good example. Oil and gas exploration that required an enormous amount of processing to predict where to drill with a high degree of certainty is another good example. They can afford to invest in supercomputers because the alternative was to drill expensive wells with little chance to hit the oil reserves. Since drilling a single well costs millions of dollars, companies can get a high return on investments into supercomputing.

Modern commercial supercomputing in the age of Datafication is what we today call Big Data. I think a better term for it would be Data Supercomputing, but the industry has already spoken, so Big Data it is. The architecture shifted from environments that required massively-parallel compute-intensive number crunching to massively-parallel data-volume-intensive processing. The performance of processors and storage media capacity has been growing much quicker than storage and network performance while data volumes growth trumped all of them. This was triggered by the shift in supercomputing architecture.

Hadoop is the first modern commercial supercomputing platform — it’s here to stay and evolve.

The post Big Data is the Commercial Supercomputing in the Age of Datafication appeared first on Pythian - Data Experts Blog.

Delphix Debrief

I’ve had my week in Palo Alto with the Delphix people. I really don’t know where the time went to – but I had a lot of interesting conversations with a number of very able people; plenty of time to experiment; and I’ve even been able to install Delphix on my laptop (as a 64-bit Open Solaris 10 VM under VMWare). I liked the product, and I was impressed with the team they’ve got working on it.

We’ve pencilled in an intial online webinar for Friday 5th April which will probably feature an informal chat where Kyle Hailey and I talk about my impressions of the product and what I’ve done with it so far. Then, in about a month’s time, we’ll have a more technical discussion.

In the meantime, it occurred to me that my next blog post ought to be about LOBs as this would be a nice way to introduce you to one of the key ideas behind DxFS (the Delphix-extended file system that’s based on ZFS).

Update 22nd March

The link to register for the online conversation is now up. We’re timetabled for 5th April 9:00 am PDT (which is currently 4:00 pm GMT, but it will be 5:00 pm BST by then.)

10053 Trace Files - Getting Started

Before getting into the contents of a 10053 trace file and looking at any useful stuff, you need to know what the files are for and how and where they are created.

Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and the results of it's calculations whilst walking through the decision-making process to determine the best execution plan. It includes the options that it has considered and discarded, those that it has accepted and options which are unavailable for various reasons. Because the decision making process is detailed and extensive, the files tend to be large for all but the most trivial statements and I'd challenge most people to read and understand an entire 10053 trace file! However, you are often looking for the reason for a particular bad decision, which helps to narrow the scope and, personally, I've found recent versions of 10053 trace files much more verbose and readable.

The best and most detailed reference I've seen is Wolfgang Breitling's paper 'A Look under the Hood of the CBO' although it was written many years ago and, as with all low-level undocumented Oracle information, things change frequently and without warning. At the start of the paper he describes how to set event 10053 to generate the related trace file in the instance's user dump destination. However, since Oracle 11g, there is a more flexible way to generate the trace file that Maria Colgan describes in a couple of posts on the Optimizer Development Groups blog here and here. The latter post is particularly interesting because that approach automatically triggers a hard parse of the statement in order to generate the trace file.

Which solves what I suspect is one of the most confusing aspects of generating 10053 trace files when you're first getting used to it. The statement needs to be hard-parsed to ensure that the trace file will be generated. It might help you to remember what it is that is being traced - the CBO making it's decisions as it chooses an optimal execution plan. So, if the plan has already been generated then no trace file! One simple technique to get around this prior to 11g is to add a new comment to the statement to force a hard parse. Remember that if you find that the trace file is not being produced as expected, that might be the reason.

In the next post, I'll start to look at the contents of a 10053 trace file.

10053 Trace Files

Sometimes I'm really not sure whether a blog post is a good idea or not. This is one of those times.

I remember a while ago that Neil Chandler wrote a blog post about why you probably don't need 10046 trace files that I didn't completely agree with and I kept thinking I must comment on it or write a post from a different perspective. Neil's a mate, a London Oracle Beers regular and I understood where he was coming from in that post. He makes some excellent points, so I recommend you read it. But I don't think it's so hard to get a trace enabled these days as people think. You don't necessarily need to change a line of code with the availability of the DBMS_MONITOR package and (although I accept this is unusual) the client I've worked for most recently makes trace file access very easy for developers. We also have ASH, AWR and SQL Monitoring though and I tend to agree with Neil that the times when 10046 trace files are truly required to solve problems are limited. Now I read this back, it sounds like I agree with him, but I know that I use 10046 trace files more regularly than the post suggests. I would say 10046 trace files are incredibly useful sometimes. In fact, when they are useful they are the only correct tool for the job.

But the real reason for referencing Neil's post is this statement.

"I have never used a 10053 trace on a Production system. I have simply
never needed to know the decisions taken by the optimizer in that much
detail.
"

I've had this discussion with most well-known experts in the Oracle community at some point or other and there's general agreement that there's no need to bother with those pesky and ridiculously geeky 10053 trace files most of the time. Most SQL performance problems simply aren't that complicated. I've probably agreed whenever the argument has come up but the fact is that I increasingly find myself using them and it worries me a little that 'most of the time you don't need them - concentrate on the basics' is heard as - 'don't ever bother looking at them - they won't help' and I've been tempted to redress the balance for a while.

But something stopped me. 10053 trace files are exceptionally long and contain a lot of information that I wouldn't begin to pretend I understand and so if you can't cover a subject properly and it is quite a technical subject then, not only are you in danger of doing people a disservice but you are also opening yourself up to all sorts of challenges, corrections and debates. But, hey, that's what a community and what blogging is all about - people learning from each other. I'm also not a big fan of any technical writing which is about how clever the writer is above it actually being useful! The more I know, the more I find myself avoiding the wilfully geeky stuff.

I still wasn't sure but at least one person at my current client has been badgering me about this for ages so I've decided, what the hell, I'm going to write a few blog posts about the things I find useful about 10053 trace files and hopefully give some very high level hints about how you might use them too.

If it's not technical enough for some people then tough and if I go astray, there are plenty of people out there who probably know the subject much better who can keep me straight ;-) (Just off the top of my head, I can think of Maria Colgan, Jonathan Lewis, Wolfgang Breitling, Christian Antognini .... well, it could be a long list).

Enough of the intro, but let me finish by saying what I think is the most important reason to use 10053 trace files. There are many posts about inaccurate row source cardinalities leading to bad plans (This is one of my favourites). But I believe that it's actually only the experts who can compare E-ROWS and A-ROWS and make educated guesses about why the cardinalities are wrong. (To give you an example, many is the time that Jonathan Lewis has said to me - 'Oh, that looks like a classic Optimizer 5% guess' - or words to that effect, anyway.) But most of us can't just 'see' those things when we look at a plan. At best, a 10053 trace file offers the possibility of knowing why the CBO picked the wrong plan.

asqlmon.sql: SQL Monitoring-like execution plan line level drilldown into SQL response time

I don’t have much time for a thorough blog post, so I’ll just paste in an example output of my asqlmon.sql script, which uses ASH sql_plan_line columns for displaying where inside your execution plan response time has been spent. Why not just use Oracle’s own SQL Monitoring reports? Well, SQL monitoring is meant for “long running” queries, which are not executed very frequently. In other words, you can’t use SQL Monitoring for drilling down into your frequently executed OLTP-style SQL. I am copying my recent post to Oracle-L mailing list here too:

The main performance impact of the old GATHER_PLAN_STATISTICS / statistics_level = ALL instrumentation came from the fact that expensive timing (gettimeofday()) system calls were used for getting A-Times of row sources.

That’s why the _rowsource_statistics_sampfreq was introduced, to not have to get the timing every switch between row sources, but only at every 128th time (by default). This caused some interesting measurement (or rather extrapolation) errors where the parent row source seemingly used less time than all of its children combined. (By the way, depending on your platform and recency of it, lightweight “fast trap” system calls may be used for getting the current timestamp, which reduces the overhead somewhat – Solaris since long time ago and RHEL 5.4+ for example).

Anyway, the row source timing info in SQL Monitoring comes from ASH sql_plan_line samples, so this instrumentation is always enabled anyway – SQL Monitoring will just query ASH for these records. The other data in V$SQL_MONITOR views doesn’t cause huge overhead – it depends on your query, but a heavy logical IO nested loop (my lotslios.sql) consumed around 0.6% more CPU with MONITOR hint compared to running with NO_MONITOR hint. I tested it on a VM and ran it only a couple of times, so this might just be statistical error or due to some other background activity. Not significant overhead in other words.

Now this was about long running queries, but you should not go and enable SQL Monitoring for all your short (OLTP) queries with a hint now as the SQL Monitoring data has to be kept and updated in a shared pool memory structure – and if all of your 1000 sessions suddenly start updating their monitoring data for every execution of a SQL, you will end up waiting for Real-time plan statistics latch contention (and some CPU overhead because so frequent searching and purging of previous SQL execution’s monitoring data).

The asqlmon (ASH-based SQL Monitoring like report) assumes that the corresponding execution plan is still present in library cache, it will take SQL_ID and child_number as a parameter. It does not show only a single execution as the SQL Monitoring report does, but  rather a plan-line level breakdown across all executions of that SQL/child captured in ASH data (you can change the WHERE clause to change the scope of the measurement):

SQL> @ash/asqlmon 6c45rgjx1myt6 0 

   SECONDS Activity Visual       Line ID Parent ASQLMON_OPERATION                                                                                    SESSION EVENT                                                            OBJ_ALIAS_QBC_NAME                                                                                 ASQLMON_PREDICATES                                                                                   PROJECTION
---------- -------- ------------ ------- ------ ---------------------------------------------------------------------------------------------------- ------- ---------------------------------------------------------------- -------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  % |          |       0        SELECT STATEMENT
         5   10.4 % |#         |       1      0  SORT AGGREGATE                                                                                      ON CPU                                                                    [SEL$1]                                                                                                                                                                                                (#keys=0) COUNT(*)[22]
         3    6.3 % |#         |       2      1   COUNT STOPKEY                                                                                      ON CPU                                                                                                                                                                      [F:]ROWNUM<=1000000000
                  % |          |       3      2    MERGE JOIN CARTESIAN
                  % |          |       4      3     VIEW  [DBA_SOURCE]                                                                                                                                                        DBA_SOURCE@SEL$1 [SET$1]
                  % |          |       5      4      UNION-ALL                                                                                                                                                                 [SET$1]
                  % |          |       6      5       FILTER                                                                                                                                                                   [SEL$335DD26A]                                                                                    [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR
                                                                                                                                                                                                                                                                                                                                 (INTERNAL_FUNCTION("O"."TYPE#") AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
                                                                                                                                                                                                                                                                                                                                 "U"."TYPE#"<>2) OR ("U"."TYPE#"=2 AND
                                                                                                                                                                                                                                                                                                                                 "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))

                  % |          |       7      6        HASH JOIN                                                                                                                                                                                                                                                                 [A:] "O"."OBJ#"="S"."OBJ#"                                                                           (#keys=1) "O"."OBJ#"[NUMBER,22], "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |       8      7         HASH JOIN                                                                                                                                                                                                                                                                [A:] "O"."SPARE3"="U"."USER#"                                                                        (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |       9      8          INDEX FULL SCAN [I_USER2]                                                                                                                                            U@SEL$2 [SEL$335DD26A]                                                                                                                                                                                  "U"."USER#"[NUMBER,22]
                  % |          |      10      8          HASH JOIN                                                                                                                                                                                                                                                               [A:] "O"."OWNER#"="U"."USER#"                                                                        (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |      11     10           INDEX FULL SCAN [I_USER2]                                                                                                                                           U@SEL$3 [SEL$335DD26A]                                                                                                                                                                                  "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
         1    2.1 % |          |      12     10           INDEX FAST FULL SCAN [I_OBJ2]                                                              ON CPU                                                                   O@SEL$3 [SEL$335DD26A]                                                                             [F:](("O"."TYPE#"=13 AND "O"."SUBNAME" IS NULL) OR INTERNAL_FUNCTION("O"."TYPE#"))                   "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22]
                  % |          |      13      7         INDEX FAST FULL SCAN [I_SOURCE1]                                                                                                                                      S@SEL$2 [SEL$335DD26A]                                                                                                                                                                                  "S"."OBJ#"[NUMBER,22]
                  % |          |      14      6        NESTED LOOPS                                                                                                                                                            [SEL$5]
                  % |          |      15     14         INDEX SKIP SCAN [I_USER2]                                                                                                                                             U2@SEL$5 [SEL$5]                                                                                   [A:] "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))         "U2"."USER#"[NUMBER,22]
                                                                                                                                                                                                                                                                                                                                 [F:]("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))

                  % |          |      16     14         INDEX RANGE SCAN [I_OBJ4]                                                                                                                                             O2@SEL$5 [SEL$5]                                                                                   [A:] "O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#"
                  % |          |      17      5       NESTED LOOPS                                                                                                                                                             [SEL$68B588A0]
                  % |          |      18     17        NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                   "O"."SPARE3"[NUMBER,22]
                  % |          |      19     18         NESTED LOOPS                                                                                                                                                                                                                                                                                                                                                                  "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22]
                  % |          |      20     19          FIXED TABLE FULL [X$JOXSCD]                                                                                                                                          S@SEL$6 [SEL$68B588A0]                                                                                                                                                                                  "S"."JOXFTOBN"[NUMBER,22]
                  % |          |      21     19          TABLE ACCESS BY INDEX ROWID [OBJ$]                                                                                                                                   O@SEL$7 [SEL$68B588A0]                                                                                                                                                                                  "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22]
                  % |          |      22     21           INDEX RANGE SCAN [I_OBJ1]                                                                                                                                           O@SEL$7 [SEL$68B588A0]                                                                             [A:] "O"."OBJ#"="S"."JOXFTOBN" AND "O"."TYPE#"=28 [F:]"O"."TYPE#"=28                                 "O".ROWID[ROWID,10], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |      23     18         INDEX RANGE SCAN [I_USER2]                                                                                                                                            U@SEL$7 [SEL$68B588A0]                                                                             [A:] "O"."OWNER#"="U"."USER#" [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR
                                                                                                                                                                                                                                                                                                                                 BITAND("U"."SPARE1",16)=0)

                  % |          |      24     17        INDEX RANGE SCAN [I_USER2]                                                                                                                                             U@SEL$6 [SEL$68B588A0]                                                                             [A:] "O"."SPARE3"="U"."USER#"
#ff0000;">        39   81.3 % |########  |      25      3     BUFFER SORT                                                                                      ON CPU                                                                                                                                                                                                                                                                           (#keys=0)
                  % |          |      26     25      VIEW  [DBA_OBJECTS]                                                                                                                                                      DBA_OBJECTS@SEL$1 [SET$2]
                  % |          |      27     26       UNION-ALL                                                                                                                                                                [SET$2]
                  % |          |      28     27        FILTER                                                                                                                                                                  [SEL$18BE6699]                                                                                    [F:](("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
                                                                                                                                                                                                                                                                                                                                 "O"."TYPE#"<>87 AND "O"."TYPE#"<>88) OR BITAND("U"."SPARE1",16)=0 OR (INTERNAL_FUNCTION("O"."TYPE#")
                                                                                                                                                                                                                                                                                                                                 AND ((SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2) OR ("U"."TYPE#"=2
                                                                                                                                                                                                                                                                                                                                 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))

                  % |          |      29     28         HASH JOIN                                                                                                                                                                                                                                                                [A:] "O"."SPARE3"="U"."USER#"                                                                        (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |      30     29          INDEX FULL SCAN [I_USER2]                                                                                                                                            U@SEL$10 [SEL$18BE6699]                                                                                                                                                                                 "U"."USER#"[NUMBER,22]
                  % |          |      31     29          HASH JOIN                                                                                                                                                                                                                                                               [A:] "O"."OWNER#"="U"."USER#"                                                                        (#keys=1) "U"."SPARE2"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "O"."OBJ#"[NUMBER,22], "O"."SPARE3"[NUMBER,22], "O"."TYPE#"[NUMBER,22]
                  % |          |      32     31           INDEX FULL SCAN [I_USER2]                                                                                                                                           U@SEL$12 [SEL$18BE6699]                                                                                                                                                                                 "U"."USER#"[NUMBER,22], "U"."TYPE#"[NUMBER,22], "U"."SPARE1"[NUMBER,22], "U"."SPARE2"[NUMBER,22]
                  % |          |      33     31           TABLE ACCESS FULL [OBJ$]                                                                                                                                            O@SEL$12 [SEL$18BE6699]                                                                            [F:]("O"."TYPE#"<>10 AND BITAND("O"."FLAGS",128)=0 AND "O"."NAME"<>'_NEXT_OBJECT' AND                "O"."OBJ#"[NUMBER,22], "O"."OWNER#"[NUMBER,22], "O"."TYPE#"[NUMBER,22], "O"."SPARE3"[NUMBER,22]
                                                                                                                                                                                                                                                                                                                                 "O"."NAME"<>'_default_auditing_options_' AND "O"."LINKNAME" IS NULL)

                  % |          |      34     28         NESTED LOOPS                                                                                                                                                           [SEL$14]
                  % |          |      35     34          INDEX SKIP SCAN [I_USER2]                                                                                                                                            U2@SEL$14 [SEL$14]                                                                                 [A:] "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))         "U2"."USER#"[NUMBER,22]
                                                                                                                                                                                                                                                                                                                                 [F:]("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))

                  % |          |      36     34          INDEX RANGE SCAN [I_OBJ4]                                                                                                                                            O2@SEL$14 [SEL$14]                                                                                 [A:] "O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#"
                  % |          |      37     27        NESTED LOOPS                                                                                                                                                            [SEL$15]
                  % |          |      38     37         INDEX FULL SCAN [I_LINK1]                                                                                                                                             L@SEL$15 [SEL$15]                                                                                                                                                                                       "L"."OWNER#"[NUMBER,22]
                  % |          |      39     37         INDEX RANGE SCAN [I_USER2]                                                                                                                                            U@SEL$15 [SEL$15]                                                                                  [A:] "L"."OWNER#"="U"."USER#"

40 rows selected.

The output is deliberately very wide – as I like to have relevant info to a plan line on the same text line visually. I navigate around such plans by visually identifying where most of the time was spent (Activity% and Visual columns), then double or triple-click that line in my terminal window to highlight the whole line (it becomes like a marker) and then scroll left-right to see more info about it, such as the filter conditions and projection etc.

Note that this script does not give you all the info that a SQL Monitoring report (or the old-fashioned SQL Plan Profiling would give), like A-rows and Starts, because ASH just doesn’t provide data at this scope. But again, this approach works with frequently executed OLTP-style queries and does not require you to enable any session settings or add hints for getting quick overview of on which execution plan line does your SQL spend the most of its time.

Update: I also have a RAC-aware / GV$-using gasqlmon.sql script, but I haven’t kept it up to date for a while, so it might return incorrect output at the moment (there are some notable challenges as the child# of interest may not be the same on all instances, so for simplicity I look into a single instance at a time – non-parallel SQL tuning rarely requires a whole cluster overview in one script).


STDDEV: Standing Sentinel on Your Data

Oracle Database implements a family of STDDEV functions for computing the
standard deviation from the mean. If you think of the mean as beginning to
paint a picture of the underlying data, then...



Read the full post at www.gennick.com/database.

STDDEV: Standing Sentinel on Your Data

Oracle Database implements a family of STDDEV functions for computing the
standard deviation from the mean. If you think of the mean as beginning to
paint a picture of the underlying data, then...



Read the full post at www.gennick.com/database.