List Exadata Storage Cell disk summary with cellpd.sql and cellpdx.sql scripts

May 24, 2013 by 1 Comment

In the previous post I explained how to list Exadata disk layout and topology details with the exadisktopo scripts, in this post I’ll introduce one celldisk overview script, which I use to quickly see the celldisk configuration, specs and error statuses. The cellpd.sql script (Cell Physical Disk) will show the following output:

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

DISKTYPE             CELLNAME             STATUS                 TOTAL_GB     AVG_GB  NUM_DISKS   PREDFAIL   POORPERF WTCACHEPROB   PEERFAIL   CRITICAL
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
FlashDisk            192.168.12.3         normal                      183         23          8
FlashDisk            192.168.12.3         not present                 183         23          8                     3
FlashDisk            192.168.12.4         normal                      366         23         16
FlashDisk            192.168.12.5         normal                      366         23         16
HardDisk             192.168.12.3         normal                    22352       1863         12
HardDisk             192.168.12.4         normal                    22352       1863         12
HardDisk             192.168.12.5         normal                    22352       1863         12

Above you see that some of my Flash Disks are missing and before they went completely missing (because the Oracle Ninja removed them :) these disks indicated the “poor performance” status.

The cellpdx.sql script will display extended info for each celldisk, including disk model, firmware version, serial number and various error metrics. Warning, lots of wide output coming again:

[Read more...]

Filed Under: Exadata, Oracle

List Exadata Disk Layout and Topology with the exadisktopo scripts

May 24, 2013 by 1 Comment

Here are two more Exadata scripts for listing the end-to-end ASM<->Exadata disk topology from V$ASM_ views and from V$CELL_CONFIG. These scripts see both the ASM level layout and the storage cell-level disk topology.

The exadisktopo.sql script shows all disks starting from the ASM diskgroup layer, going deeper and deeper all the way to the OS disk device level in the storage cells. It uses outer joins, so will show celldisks even if there are no corresponding grid disks allocated on them (or if there are no ASM disks using them). It also shows the Flash cards used as flash cache, thus there are no ASM disks on them usually.

The output should be self-explanatory, you just read the data from left to right (from ASM diskgroup to ASM disk to Grid Disk level etc), the more rightwards you scroll, the “deeper” you go in the Exadata IO stack layers. Warning, lots of wide output coming :)

[Read more...]

Filed Under: Exadata, Oracle

V$CELL_THREAD_HISTORY – “ASH” for Exadata Storage Cells

May 23, 2013 by Leave a Comment

Did you know that there’s something like Active Session History also in the Exadata storage cells? ;-)

The V$CELL_THREAD_HISTORY view is somewhat like V$ACTIVE_SESSION_HISTORY, but it’s measuring thread activity in the Exadata Storage Cells:

SQL> @desc v$cell_thread_history
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      CELL_NAME                                VARCHAR2(1024)
    2      SNAPSHOT_ID                              NUMBER
    3      SNAPSHOT_TIME                            DATE
    4      THREAD_ID                                NUMBER
    5      JOB_TYPE                                 VARCHAR2(32)
    6      WAIT_STATE                               VARCHAR2(32)
    7      WAIT_OBJECT_NAME                         VARCHAR2(32)
    8      SQL_ID                                   VARCHAR2(13)
    9      DATABASE_ID                              NUMBER
   10      INSTANCE_ID                              NUMBER
   11      SESSION_ID                               NUMBER
   12      SESSION_SERIAL_NUM                       NUMBER

It keeps about 10 minutes worth of samples of Exadata Storage Cell thread activity:

SQL> @minmax snapshot_time v$cell_thread_history
Show min/max (low/high) values in column "snapshot_time" of table v$cell_thread_history...

MIN(SNAPSHOT_TIME MAX(SNAPSHOT_TIME
----------------- -----------------
20130419 14:42:15 20130419 14:52:54

Note that it’s not the V$ view or the database instance which stores this array – it’s the storage cells themselves. If you query the v$cell_thread_history view, your Oracle database session is going to “gather” this instrumentation data from all the required cells and present it to you, that’s why the “cell statistics gather” wait event shows up:

PARSING IN CURSOR #140596385017248 len=42 dep=0 uid=0 oct=3 lid=0 tim=1366404896817011 hv=4063158547 ad='19e452578' sqlid='63awy1gg
t2xs8m'
select count(*) from v$cell_thread_history
END OF STMT
PARSE #140596385017248:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817010
EXEC #140596385017248:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817127
WAIT #140596385017248: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1366404896817152
WAIT #140596385017248: nam='cell statistics gather' ela= 283 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896818846
WAIT #140596385017248: nam='cell statistics gather' ela= 352 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896819317
WAIT #140596385017248: nam='cell statistics gather' ela= 376 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896820929
WAIT #140596385017248: nam='cell statistics gather' ela= 326 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896822198
WAIT #140596385017248: nam='cell statistics gather' ela= 580 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896823620
...

Now, how frequently do the cells sample their “ASH” data:

SQL> SELECT DISTINCT (snapshot_time - LAG(snapshot_time) 
                      OVER (ORDER BY snapshot_time)) * 86400 lag_seconds
     FROM (SELECT distinct snapshot_time 
           FROM v$cell_thread_history WHERE cell_name = '192.168.12.3');

LAG_SECONDS
-----------

          1

Looks like the sampling is done exactly once per second!

So, great, what can we do with this data?

Here’s an Exadata performance script (cth.sql) which uses V$CELL_THREAD_HISTORY to dig into cell activity from the database layer.

[Read more...]

Filed Under: Exadata, Oracle

Oracle Database 11.2 Upgrade and Migration slides

April 14, 2013 by 3 Comments

Check out the extensive slide deck (over 500 slides) about upgrading techniques to Oracle 11.2, by Oracle Corp (Roy Swonger and Mike Dietrich):

It has lots of examples (from real customer upgrade cases) in it.

Thanks to Randolf Geist for telling me about this.

You can also download other interesting presentations from that page (check the right hand side download section), like the Oracle Database Behavior Changes 8.0 through 11.2 doc. Pretty cool!

 

Filed Under: Oracle

How to Compose New Gmail Messages in Full Screen (instead of the tiny compose box of new Gmail)

April 9, 2013 by 10 Comments

I’m writing this (unusual) post as I am a long time Gmail user and recently I’ve seen plenty of people & articles complain about the Gmail’s new compose window (the one that shows up as a small hovering window in the bottom right of your screen):

gmail_new_compose3

The top google hits so far only return tips to disable the new editor completely, but I want to use the new one, just in a bigger window! There is a very easy workaround for that – and there’s no need to switch back to the old compose mode at all!

If you are using your mouse, then just:

  1. Hold down SHIFT key when clicking the Compose button to get a stand-alone new window for composing a message.
  2. Hold down CTRL (on Windows) or Command (on Mac OSX) key to get a full-screen new browser tab for composing a message.

Examples 1 & 2 below:

gmail_new_window_composegmail_full_screen_compose

If you use Gmail keyboard shortcuts for productivity (they’re awesome!), then you can just:

  1. Press “c” for the new (small) compose box
  2. Press SHIFT-C (capital “C”) for the stand-alone new window for composing a message
  3. Press D for a full screen new browser tab for composing a message

Note that you can view the Gmail shortcuts reference, if this feature is enabled, just by pressing the ? (question mark) key:
gmail_vim_navigation_keys
So, there’s no need to switch back to the old compose mode completely, just remember SHIFT, CTRL and the “D” keys :)

Filed Under: Productivity

Public Appearances and Exadata Performance Training

April 2, 2013 by 1 Comment

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 2013 – UKOUG 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 2013 – Advanced 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 2013 – Advanced 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 2013 – Enkitec 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 2013 – Oracle 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!

Filed Under: Announcement, Exadata, Oracle

Understanding what a hint affects using the V$SQL_FEATURE views

April 1, 2013 by Leave a Comment

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):
[Read more...]

Filed Under: Oracle

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

March 21, 2013 by 8 Comments

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! :)

Filed Under: Exadata, Oracle

Alter session force parallel query doesn’t really force anything

March 20, 2013 by 2 Comments

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.

Filed Under: Oracle

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

March 17, 2013 by 17 Comments

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

Filed Under: Oracle
«Older Posts