Public Appearances and Exadata Performance Training

I will be doing a lot of (Exadata) talking and teaching in the coming months. Here’s a list of events where you’ll see me speaking, teaching, hacking, learning and hopefully also drinking beer:

  • 16 April 2013UKOUG Engineered Systems Summit
    • London, UK
    • I will talk about the common Exadata performance lessons learned in my “4 years of Exadata performance” talk
    • Andy Colvin and Martin Bach of Enkitec will also speak there (yes, Martin has joined Enkitec in Europe!!! :-)
  • 30 April 2013 – NYOUG Training Day
    • Manhattan, NYC
    • This is the first ever public delivery of my new Getting the Most Out of Oracle’s Active Session History, Time Model and Wait events seminar!
  • 2-3 May 2013Advanced Exadata Performance seminar – in-classroom!
    • First public delivery of this class – and we’ll be going very deep into Exadata internals and performance ;-)
    • Irving, TX (in Enkitec HQ)
  • 13-16 May 2013Advanced Exadata Performance seminar – online
    • Same as previous, but online.
  • 10-11 June 2013 – My new ASH seminar – online
    • Getting the Most Out of Oracle’s Active Session History, Time Model and Wait events
    • More details will appear in the training & seminar page soon
  • 13-14 June 2013 – Oracle Data Warehouse Global Leaders forum (by invitation-only event run by Oracle)
    • Amsterdam, Netherlands
    • I will speak about my “4 years of Exadata performance” experience and probably learn from others’ experience too
  • 5-6 Aug 2013Enkitec Extreme Exadata Expo (E4) conference
    • Irving, TX
    • I will be speaking there, haven’t set the exact topic yet, but it will include demos and hacking something I suspect ;-)
  • 22-26 September 2013Oracle OpenWorld conference
    • San Francisco, CA
    • I will attend the ACE Directors briefing before the conference, likely speak, hang out at Enkitec booth, probably hack something at Enkitec’s Conference HQ for fun and definitely drink beer there. Maybe I’ll even attend the Wednesday’s party for a change!

As you see, my great plans to not travel much are not going to work out well :)

Actually it is better, this year I’ve managed to only travel twice so far (and one of the trips was for vacation!) and I haven’t had to do too many of the crazy around-the-world in 5 days trips I did when living in back Singapore… but looks like I’ll get to 2 million km nevertheless this year:

Tanel's Tripit stats April 2013

Well, see you in some corner of the world some day!

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Announcement, Exadata, Oracle | 1 Comment

Understanding what a hint affects using the V$SQL_FEATURE views

You may have used the Oracle 11g V$SQL_HINT view already – it displays all the valid hints (both documented and undocumented ones) available in your Oracle version, for example:

SQL> @hint merge

NAME                                                             VERSION                   VERSION_OUTLINE           INVERSE
---------------------------------------------------------------- ------------------------- ------------------------- ----------------------------------------------------------------
MERGE_CONST_ON                                                   8.0.0
MERGE_AJ                                                         8.1.0                     8.1.7
MERGE_SJ                                                         8.1.0                     8.1.7
MV_MERGE                                                         9.0.0
MERGE                                                            8.1.0                     10.1.0                    NO_MERGE
NO_MERGE                                                         8.0.0                     10.1.0                    MERGE
USE_MERGE_CARTESIAN                                              11.1.0.6                  11.1.0.6
USE_MERGE                                                        8.1.0                     8.1.7                     NO_USE_MERGE
NO_USE_MERGE                                                     10.1.0.3                  10.1.0.3                  USE_MERGE

But there’s more, (semi)undocumented views like V$SQL_FEATURE and V$SQL_FEATURE_HIERARCHY do give us more information about what these hints relate to. For example, if you have ever wondered why is there a MERGE hint and then also a USE_MERGE hint, you can check what do these hints control using my hinth.sql (Hint Hierarchy) script:

SQL> @hinth MERGE
Display Hint feature hierarchy for hints like MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE                                                            ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM

So, the MERGE hints seem to affect the CBO’s query transformation code – (CBQT means Cost-Based Query Transformation and CVM means Complex View Merging, but more about that later).

SQL> @hinth USE_MERGE
Display Hint feature hierarchy for hints like USE_MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
USE_MERGE                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE

And the USE_MERGE hint is about controlling the use of a join method – the sort-merge join.

Let’s list all hints having NL in them:

SQL> @hinth %NL%
Display Hint feature hierarchy for hints like %NL%

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
INLINE_XMLTYPE_NT                                                ALL
NL_SJ                                                            ALL -> COMPILATION -> CBO
NL_AJ                                                            ALL -> COMPILATION -> CBO
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_USE_NL                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL                                                           ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX                                                ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_CONNECT_BY_CB_WHR_ONLY                                        ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY                                           ALL -> COMPILATION -> TRANSFORMATION
INLINE                                                           ALL -> COMPILATION -> TRANSFORMATION
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_NLJ_BATCHING                                                  ALL -> EXECUTION
NLJ_BATCHING                                                     ALL -> EXECUTION
NO_NLJ_PREFETCH                                                  ALL -> EXECUTION
NLJ_PREFETCH                                                     ALL -> EXECUTION

Plenty of interesting stuff here – the new hint TABLE_LOOKUP_BY_NL that has showed up recently seems to have to do with star transformations for example (I just learned this myself from this output).

Interestingly the NLJ_BATCHING and NLJ_PREFETCH hints are considered as execution phase hints apparently (that was my term, I’m thinking about hints (also) affecting a decision in the execution phase, not just during optimization). For example, normally the NLJ prefetch feature can be dynamically turned on & off during the query execution, I guess with a hint this feature would be always enabled (I’m not sure about this here, just trying to reason why a hint is shown to be related to “execution” phase).

If optimizer feature terms like CBQT and CVM do not immediately ring a bell, you can use the V$SQL_FEATURE view (or my sqlfh.sql script) to list some more info about what these SQL feature name abbreviations mean and where in the hierarchy does this particular feature stand.

The script below doesn’t accept any parameters, prints out the entire SQL feature hierarchy (except the temporary bugfix features you can see from V$SYSTEM_FIX_CONTROL):

SQL> @sqlfh

SQL_FEATURE                                             DESCRIPTION
------------------------------------------------------- ----------------------------------------------------------------
ALL                                                     A Universal Feature
  COMPILATION                                           SQL COMPILATION
    CBO                                                 SQL Cost Based Optimization
      ACCESS_PATH                                       Query access path
        AND_EQUAL                                       Index and-equal access path
        BITMAP_TREE                                     Bitmap tree access path
        FULL                                            Full table scan
        INDEX                                           Index
        INDEX_ASC                                       Index (ascending)
        INDEX_COMBINE                                   Combine index for bitmap access
        INDEX_DESC                                      Use index (descending)
        INDEX_FFS                                       Index fast full scan
        INDEX_JOIN                                      Index join
        INDEX_RS_ASC                                    Index range scan
        INDEX_RS_DESC                                   Index range scan descending
        INDEX_SS                                        Index skip scan
        INDEX_SS_ASC                                    Index skip scan ascending
        INDEX_SS_DESC                                   Index skip scan descending
        SORT_ELIM                                       Sort Elimination Via Index
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      CURSOR_SHARING                                    Cursor sharing
      DML                                               DML
      JOIN_METHOD                                       Join methods
        USE_HASH                                        Hash join
        USE_MERGE                                       Sort-merge join
        USE_MERGE_CARTESIAN                             Merge join cartesian
        USE_NL                                          Nested-loop join
        USE_NL_WITH_INDEX                               Nested-loop index join
      JOIN_ORDER                                        Join order
      OPT_MODE                                          Optimizer mode
        ALL_ROWS                                        All rows (optimizer mode)
        CHOOSE                                          Choose (optimizer mode)
        FIRST_ROWS                                      First rows (optimizer mode)
      OR_EXPAND                                         OR expansion
      OUTLINE                                           Outlines
      PARTITION                                         Partition
      PQ                                                Parallel Query
        PARALLEL                                        Parallel table
        PQ_DISTRIBUTE                                   PQ Distribution method
        PQ_MAP                                          PQ slave mapper
        PX_JOIN_FILTER                                  Bloom filtering for joins
      STAR_TRANS                                        Star Transformation
        TABLE_LOOKUP_BY_NL                              Table Lookup By Nested Loop
      STATS                                             Optimizer statistics
        CARDINALITY                                     Cardinality computation
        COLUMN_STATS                                    Basic column statistics
        CPU_COSTING                                     CPU costing
        DBMS_STATS                                      Statistics gathered by DBMS_STATS
        DYNAMIC_SAMPLING                                Dynamic sampling
        DYNAMIC_SAMPLING_EST_CDN                        Estimate CDN using dynamic sampling
        GATHER_PLAN_STATISTICS                          Gather plan statistics
        INDEX_STATS                                     Basic index statistics
        OPT_ESTIMATE                                    Optimizer estimates
        TABLE_STATS                                     Basic table statistics
    QUERY_REWRITE                                       query rewrite with materialized views
    RBO                                                 SQL Rule Based Optimization
    SQL_CODE_GENERATOR                                  SQL Code Generator
    SQL_PLAN_MANAGEMENT                                 SQL Plan Management
    TRANSFORMATION                                      Query Transformation
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      HEURISTIC                                         Heuristic Query Transformation
        CNT                                             Count(col) to count(*)
        COALESCE_SQ                                     coalesce subqueries
        CSE                                             Common Sub-Expression Elimination
        CVM                                             Complex View Merging
        FILTER_PUSH_PRED                                Push filter predicates
        FULL_OUTER_JOIN_TO_OUTER                        Join Conversion
        JPPD                                            Join Predicate Push Down
        OBYE                                            Order-by Elimination
        OLD_PUSH_PRED                                   Old push predicate algorithm (pre-10.1.0.3)
        OUTER_JOIN_TO_ANTI                              Join Conversion
        OUTER_JOIN_TO_INNER                             Join Conversion
        PRED_MOVE_AROUND                                Predicate move around
        SET_TO_JOIN                                     Transform set operations to joins
        SVM                                             Simple View Merging
        TABLE_ELIM                                      Table Elimination
        UNNEST                                          unnest query block
        USE_CONCAT                                      Or-optimization
    XML_REWRITE                                         XML Rewrite
      CHECK_ACL_REWRITE                                 Check ACL Rewrite
      COST_XML_QUERY_REWRITE                            Cost Based XML Query Rewrite
      XMLINDEX_REWRITE                                  XMLIndex Rewrite
  EXECUTION                                             SQL EXECUTION

I highlighted the CVM and CBQT lines above…

Just for reference (and if you’re too lazy to run these scripts yourself), I’ve pasted the full output of the hint feature hierarchy script too (executed in my 11.2.0.3 DB):
Continue reading

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 1 Comment

Listing Exadata storage cells and their configuration info from V$CELL_CONFIG

If you attended my Exadata hacking session today, you saw me using the cellver.sql script which lists some basic configuration info about the currently connected storage cells:

SQL> @exadata/cellver
Show Exadata cell versions from V$CELL_CONFIG....

CELLNAME             CELLSRV_VERSION      FLASH_CACHE_MODE     CPU_COUNT  UPTIME               KERNEL_VERSION                 MAKE_MODEL
-------------------- -------------------- -------------------- ---------- -------------------- ------------------------------ --------------------------------------------------
192.168.12.10        11.2.3.2.1           WriteBack            24         8 days, 2:07         2.6.32-400.11.1.el5uek         Oracle Corporation SUN FIRE X4270 M2 SERVER SAS
192.168.12.11        11.2.3.2.1           WriteBack            24         8 days, 2:06         2.6.32-400.11.1.el5uek         Oracle Corporation SUN FIRE X4270 M2 SERVER SAS
192.168.12.8         11.2.3.2.1           WriteBack            24         8 days, 2:06         2.6.32-400.11.1.el5uek         Oracle Corporation SUN FIRE X4270 M2 SERVER SAS
192.168.12.9         11.2.3.2.1           WriteBack            24         8 days, 2:06         2.6.32-400.11.1.el5uek         Oracle Corporation SUN FIRE X4270 M2 SERVER SAS

The output is pretty self-explanatory. One thing to note is that the CPU_COUNT is not the number of CPU cores, but just the number of “virtual” CPU threads presented to the OS, in this case 24 threads over 12 cores.

The script itself is simple, just extracting some XML values from the “CELL” type config records:

SQL> l
  1  SELECT
  2      cellname cv_cellname
  3    , CAST(extract(xmltype(confval), '/cli-output/cell/releaseVersion/text()') AS VARCHAR2(20))  cv_cellVersion
  4    , CAST(extract(xmltype(confval), '/cli-output/cell/flashCacheMode/text()') AS VARCHAR2(20))  cv_flashcachemode
  5    , CAST(extract(xmltype(confval), '/cli-output/cell/cpuCount/text()')       AS VARCHAR2(10))  cpu_count
  6    , CAST(extract(xmltype(confval), '/cli-output/cell/upTime/text()')         AS VARCHAR2(20))  uptime
  7    , CAST(extract(xmltype(confval), '/cli-output/cell/kernelVersion/text()')  AS VARCHAR2(30))  kernel_version
  8    , CAST(extract(xmltype(confval), '/cli-output/cell/makeModel/text()')      AS VARCHAR2(50))  make_model
  9  FROM
 10      v$cell_config  -- gv$ isn't needed, all cells should be visible in all instances
 11  WHERE
 12      conftype = 'CELL'
 13  ORDER BY
 14*     cv_cellname

I will add some more scripts to the exadata directory over the coming days.

Enjoy! :)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Exadata, Oracle | 11 Comments

Alter session force parallel query doesn’t really force anything

Jonathan Lewis has already written about this behavior from the angle of PARALLEL hints.

I’m writing a similar article just because the word FORCE in the ALTER SESSION FORCE PARALLEL QUERY syntax. Force should mean that some behavior would always happen (when possible), right? Let’s test:

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;

Table created.

SQL> CREATE INDEX i ON t(owner);

Index created.

SQL> @gts t
Gather Table Statistics for table t...

PL/SQL procedure successfully completed.

Now let’s “force” the parallel query in my session, run the query and check the execution plan:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;

Session altered.

SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';

SUM(OBJECT_ID)
--------------
     979900956

SQL> @x

---------------------------------------------------------------------------
| Id  | Operation                    | Name | E-Rows |E-Bytes| Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |        |       |   186 (100)|
|   1 |  SORT AGGREGATE              |      |      1 |    12 |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T    |   6741 | 80892 |   186   (0)|
|*  3 |    INDEX RANGE SCAN          | I    |   6741 |       |    18   (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

The output shows a regular, serial execution plan!

Hmm, let’s increase the “forced” parallelism from 2 to 3 and run exactly the same query again:

SQL> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 3;

Session altered.

SQL> SELECT SUM(object_id) FROM t WHERE owner LIKE 'S%';

SUM(OBJECT_ID)
--------------
     979900956

SQL> @x

------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | E-Rows |E-Bytes| Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |        |       |   128 (100)|        |      |            |
|   1 |  SORT AGGREGATE        |          |      1 |    12 |            |        |      |            |
|   2 |   PX COORDINATOR       |          |        |       |            |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |      1 |    12 |            |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |      1 |    12 |            |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |   6741 | 80892 |   128   (0)|  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T        |   6741 | 80892 |   128   (0)|  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(:Z>=:Z AND :Z<=:Z)
       filter("OWNER" LIKE 'S%')

Now the query will get a parallel plan!

The reason for this behavior is that the FORCE parallel query syntax doesn’t really force Oracle to use a parallel plan, but rather just reduces optimizer cost estimates for full table scans (the higher the parallelism, the lower the FTS costs – Jonathan already has details about this in his blog entry, so I won’t replicate this). But the optimizer is still free to choose some other, non-parallel execution plan if that has a lower cost than the best parallel one!

So what happened above is that with “forced” parallel degree 2, the parallel full table scan plan must have had a higher cost than the serial index range scan (186), but once I increased the parallelism “factor” to 3, then the final cost of the parallel full table scan plan ended up being lower (128) than the best serial plan found.

This is a good example showing that both the PARALLEL hints and the FORCE PARALLEL session settings really just adjust a narrow set of optimizer cost computation inputs and don’t really fix the resulting execution plan. If you really want to fix an execution plan, you need to tie optimizer “hands” in every aspect with a full set of hints just like the stored profiles do. That way, even if there is a lower cost plan available, the optimizer doesn’t know about it as you’ve prohibited it from doing any calculations other than your hints direct it to.

Note that when testing this, your mileage may vary, depending on how much data you have in your test table (or rather in the optimizer stats for that table) plus system stats.

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 4 Comments

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#"
        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).

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 22 Comments

ExaSnapper 0.7 beta download and the hacking session videos

Thank you for attending the Exadata Snapper (ExaSnapper) hacking session!

I have split the recording of this session into 3 pieces and uploaded to enkitec.tv. The ExaSnapper v0.7 beta that I demoed is also available now at enkitec.com (registration needed). See the links below.

For quick reference, here’s the syntax of running ExaSnapper – there are two modes, one is the before/after capture (think Tom Kyte’s runstats, but for exadata metrics) and the other is more like a DBA-monitoring mode, where you can just measure a few seconds worth of a long-running query runtime and get the IO and efficiency figures from there.

Here’s an excerpt from the install script documentation section:

-- Usage:       Take a snapshot of a running session (use QC SID if PX):
--
--                a) Monitor a running query - "DBA mode" 
--
--                   SELECT * FROM TABLE(exasnap.display_sid(, [snap_seconds], [detail_level]));
--
--                   The SID argument can be just a number (SID in local instance) or a remote SID with
--                   @instance after it (like '123@4')
--
--                   SELECT * FROM TABLE(exasnap.display_sid(123));
--                   SELECT * FROM TABLE(exasnap.display_sid('123@4', p_detail=>'%');
--
--                b) Take Before & After snapshots of a query execution - "Developer Mode"
--
--                   1) SELECT exasnap.begin_snap(123) FROM dual;
--                        or
--                      EXEC :begin_snap_id := exasnap.begin_snap(123);
-- 
--                   2) Run your query, wait until it finishes (or CTRL+C)
--
--                   3) SELECT exasnap.end_snap(123) FROM dual;
--                        or
--                      EXEC :end_snap_id := exasnap.end_snap(123);
--
--                   4) SELECT * FROM TABLE(exasnap.display_snap(:begin_snap_id, :end_snap_id, '%'));
--

One slide illustrating the idea of ExaSnapper:

Continue reading

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Exadata, Oracle | 3 Comments

Peeking into Linux kernel-land using /proc filesystem for quick’n'dirty troubleshooting

This blog entry is about modern Linuxes. In other words RHEL6 equivalents with 2.6.3x kernels and not the ancient RHEL5 with 2.6.18 kernel (wtf?!), which is the most common in enterprises unfortunately. And no, I’m not going to use kernel debuggers or SystemTap scripts here, just plain old “cat /proc/PID/xyz” commands against some useful /proc filesystem entries.

Troubleshooting a “slow” process

Here’s one systematic troubleshooting example I reproduced in my laptop. A DBA was wondering why their find command had been running “much slower”, without returning any results for a while. Knowing the environment, we had a hunch, but I got asked about what would be the systematic approach for troubleshooting this – already ongoing – problem right now.

Luckily the system was running OEL6, so had a pretty new kernel. Actually the 2.6.39 UEK2.

So, let’s do some troubleshooting. First let’s see whether that find process is still alive:

[root@oel6 ~]# ps -ef | grep find
root     27288 27245  4 11:57 pts/0    00:00:01 find . -type f
root     27334 27315  0 11:57 pts/1    00:00:00 grep find

Yep it’s there – PID 27288 (I’ll use that pid throughout the troubleshooting example).

Let’s start from the basics and take a quick look what’s the bottleneck for this process – if it’s not blocked by anything (for example reading everything it needs from cache) it should be 100% on CPU. If it’s bottlenecked by some IO or contention issues, the CPU usage should be lower – or completely 0%.

[root@oel6 ~]# top -cbp 27288
top - 11:58:15 up 7 days,  3:38,  2 users,  load average: 1.21, 0.65, 0.47
Tasks:   1 total,   0 running,   1 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.1%sy,  0.0%ni, 99.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2026460k total,  1935780k used,    90680k free,    64416k buffers
Swap:  4128764k total,   251004k used,  3877760k free,   662280k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
27288 root      20   0  109m 1160  844 D  0.0  0.1   0:01.11 find . -type f

Top tells me this process is either 0% on CPU or very close to zero percent (so it gets rounded to 0% in the output). There’s an important difference though, as a process that is completely stuck, not having a chance of getting onto CPU at all vs. a process which is getting out of its wait state every now and then (for example some polling operation times out every now and then and thee process chooses to go back to sleep). So, top on Linux is not a good enough tool to show that difference for sure – but at least we know that this process is not burning serious amounts of CPU time.

Let’s use something else then. Normally when a process seems to be stuck like that (0% CPU usually means that the process is stuck in some blocking system call – which causes the kernel to put the process to sleep) I run strace on that process to trace in which system call the process is currently stuck. Also if the process is actually not completely stuck, but returns from a system call and wakes up briefly every now and then, it would show up in strace (as the blocking system call would complete and be entered again a little later):

[root@oel6 ~]# strace -cp 27288
Process 27288 attached - interrupt to quit

^C
^Z
[1]+  Stopped                 strace -cp 27288

[root@oel6 ~]# kill -9 %%
[1]+  Stopped                 strace -cp 27288
[root@oel6 ~]# 
[1]+  Killed                  strace -cp 27288

Oops, the strace command itself got hung too! It didn’t print any output for a long time and didn’t respond to CTRL+C, so I had to put it into background with CTRL+Z and kill it from there. So much for easy diagnosis.

Continue reading

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 16 Comments

Even more Snapper – v4.03 now works in SQL Developer too!

If you already downloaded snapper v4, then better re-download it again as the v4.03 also runs in SQL Developer!

Snapper 4 inside SQL Developer

You might also want to check how to enable the DBMS_OUTPUT display in SQL Developer post by “That” Jeff Smith :-)

Update: Niall Litchfield pointed out that the “set serverout on” command was ignored on SQL Developer because I was using the short syntax (serverout instead of serveroutput). Once I changed this, the SQL Developer doesn’t print the warning anymore and there’s no need to explicitly open up the separate DBMS_OUTPUT. I have uploaded Snapper v4.04 with this fix included. Thanks Niall!

Also, thanks go to:

  • Hans-Peter Sloot – for building the first cut of RAC support into a previous Snapper version
  • Ronald Rood – for adding the customizations for SQL Developer support into a previous Snapper version

Note that the manual before/after snapshots don’t work inside SQL Developer right now, will try to fix this later …

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | 29 Comments

Manual “before” and “after” snapshot support in Snapper v4

Snapper used to require access to DBMS_LOCK, so it could sleep for X seconds between the “before” and “after” performance data snapshots. Now it is possible to get away without using DBMS_LOCK. Instead you will run Snapper twice, once for taking the “before” snapshot, then run your workload and then run Snapper again for taking the “after” snapshot and print the output.

So, the usual way of running snapper is this:

@snapper4 all 5 1 152

This would take 1 5-second performance snapshot SID 152′s V$ views.

With Snapper4 you can use the old way or just add BEGIN or END keywords to the 1st parameter, like this:

@snapper4 all,begin 5 1 152

Whenever using the begin/end syntax, the 2nd and 3rd parameter specifying snapshot duration and count (“5 1″) are just ignored, but they still have to be there right now as snapper requires 4 parameters when called (I’m thinking about changing this in V4.1).

So, let’s try it:

SQL> @snapper4 all,begin 5 1 152
Sampling SID 152 with interval 5 seconds, taking 1 snapshots...
SP2-0552: Bind variable "SNAPPER" not declared.

Oops! There’s some error!

In this version of Snapper, if you want to use the before/after manual snapshot mode, you need to run one more command in your session before starting (you need to do it only once when logging in, not every time you run snapper in your session):

SQL> VAR SNAPPER REFCURSOR
SQL>
SQL> @snapper4 all,begin 5 1 152
Sampling SID 152 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.02 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

Taking BEGIN sample ...

PL/SQL procedure successfully completed.

The REFCURSOR trick is how I manage to store the “before” snapshot data even though the Snapper anonymous block exits and gives you back control. See the code for more details. I’ve figured out a way to do this in the snapper script itself (it’s not trivial), but haven’t gotten to this yet. Coming soon :)

Anyway, now as we have taken the before snapshot, we can run whatever commands in the session(s) we are measuring (it doesn’t have to be a single session, you can use the usual snapper syntax to pick which sessions to measure).

Once you are ready to take the end snapshot and print the deltas, run this:

SQL> @snapper4 all,end 5 1 152
Sampling SID 152 with interval 5 seconds, taking 1 snapshots...

-- Session Snapper v4.02 BETA - by Tanel Poder ( http://blog.tanelpoder.com ) - Enjoy the Most Advanced Oracle Troubleshooting Script on the Planet! :)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SID, USERNAME  , TYPE, STATISTIC                                                 ,         DELTA, HDELTA/SEC,    %TIME, GRAPH       , NUM_WAITS,  WAITS/SEC,   AVERAGES
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    152, SYS       , STAT, opened cursors cumulative                                 ,         13690,      1.35k,         ,             ,          ,           ,         .7 per execution
    152, SYS       , STAT, opened cursors current                                    ,             1,         .1,         ,             ,          ,           ,  .05*10^-3 per execution
    152, SYS       , STAT, user commits                                              ,            18,       1.78,         ,             ,          ,           ,  .93*10^-3 per execution
    152, SYS       , STAT, recursive calls                                           ,        234274,     23.11k,         ,             ,          ,           ,      12.05 per execution
    152, SYS       , STAT, recursive cpu usage                                       ,           848,      83.64,         ,             ,          ,           ,        .04 per execution
    152, SYS       , STAT, session logical reads                                     ,        267980,     26.43k,         ,             ,          ,           ,      13.78 per execution

... some output removed for brevity ...

    152, SYS       , WAIT, control file sequential read                              ,          1078,   106.33us,      .0%, [          ],       130,      12.82,     8.29us average wait
    152, SYS       , WAIT, log file sync                                             ,         10969,     1.08ms,      .1%, [          ],        15,       1.48,   731.27us average wait
    152, SYS       , WAIT, db file sequential read                                   ,        444277,    43.82ms,     4.4%, [W         ],      1900,      187.4,   233.83us average wait
    152, SYS       , WAIT, db file scattered read                                    ,        812924,    80.18ms,     8.0%, [W         ],      1077,     106.23,    754.8us average wait
    152, SYS       , WAIT, direct path write temp                                    ,          1758,    173.4us,      .0%, [          ],        20,       1.97,     87.9us average wait

--  End of Stats snap 1, end=2013-02-18 23:46:25, seconds=10.1

--  End of ASH snap 1, end=, seconds=, samples_taken=0

And that’s it. Note that there’s no ASH output as if snapper returns control back to you between before & after snapshots, it can not sample the V$SESSION view for printing you the “ASH” top.

With the exception of ASH output and ignoring the snapshot_time and snapshot_count parameter values, Snapper allows you to use the full range of syntax for picking which stats do display (gather,sinclude,winclude,tinclude options) and which sessions to measure (sid@inst, user=joe@2, program=sqlplus%@* and even subqueries). You may want to sample some other session(s) this way or you may want to take before and after snapshots of your own session where snapper runs, there are no restrictions, just remember that when taking snapshots of your own session, there’s some snapper’s own activity which shows up in the session’s metrics. If you want a clean snapshot like autotrace is doing, then use one session for running snapper and another session for running the actual test cases.

Note that I just wrote a blog entry about Snapper v4 RAC GV$ view syntax too.

Enjoy! (and remember that Snapper v4 is still beta and likely has some issues)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle | Leave a comment

Snapper v4.02 and the Snapper launch party video

I have fixed most of the bugs that showed up during the Snapper launch party session and uploaded the new version (v4.02) of Snapper here:

I have also uploaded the launch party hacking session video to enkitec.tv:

I have not updated the snapper documentation yet, but here are the main improvements:

  1. RAC support (query remote instance’s sessions performance data via GV$)
  2. Manually taken “before” and “after” snapshot support. No need to use DBMS_LOCK to sleep for a certain number of seconds between snapshots
  3. Useful averages in the right side of the output (e.g. average bytes sent/received per roundtrip etc). I will keep adding new averages over the coming weeks.

RAC support

I have kept the syntax backwards-compatible, so when you run Snapper like this …

@snapper ash 5 1 user=tanel

… it will still query only local instance’s sessions.

But if you want to measure all tanel’s sessions in let’s say instance #2 only, you can just add @2 to the session filter condition:

@snapper ash 5 1 user=tanel@2

Or if you want all instances then use @*:

@snapper ash 5 1 user=tanel@*

If you want to measure a single session with sid 1234 in instance #2, you can do it in two ways:

@snapper ash 5 1 1234@2

Or use the inst_id,sid tuple syntax:

@snapper ash 5 1 (2,1234)

Note that the brackets are important, as otherwise snapper thinks that you want to measure SID 2 and SID 1234 in the local instance. You can pass multiple inst_id,SID tuples to the snapper though:

@snapper ash 5 1 (2,1234),(4,5678),(3,999)

Snapper still supports the qc= syntax too, to list all sessions started by a PX query coordinator session:

@snapper ash 5 1 qc=1234@*

Note that the qc= syntax is a bit broken/inconsistent right now – what the qc=1234@* means right now is that Snapper looks up the SID 1234 from every instance and lists slave sessions from all instances. If you use qc=1234@2 for example, then Snapper would only look for QC in the instance #2 and any slaves that are also in instance #2 (but with inter-instance parallelism you may have slaves in other instances too). I will fix it in a near-future version.

I will write about the other major features in separate blog entries, but if you want to learn about it now, then grab a beer (or coffe) and watch the latest enkitec.tv show (it’s a bit over 2 hours :-))

P.S. Snapper 4 is still beta! Some queries (like the qc=) one may take a while to run, running dynamic queries with subqueries against GV$ views efficiently is tricky business. This is why I have kept the old snapper v3 (which is more stable and pretty efficient) in the standard (old) snapper address.

Update: If you want to see what Snapper is capable of (it’s about the old version 3), check this document about using Snapper for Oracle performance measurement.

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

Posted in Oracle, Productivity | 3 Comments