Mohamed Houri’s Oracle Notes

April 29, 2015

Real time SQL monitoring limitation

Filed under: Oracle — hourim @ 3:56 pm

I was trying to explain a performance deterioration of a very complex query honoured via an execution plan with 386 operations (386 lines). From where would someone start deciphering this complex and big execution plan without the help of a Real Time SQL monitoring report? As far as this query took 2 hours to complete it is fairly likely that Oracle has monitored it. Unfortunately a select against v$sql_monitor view didn’t returned any rows for this particular sql_id. What came to my mind in front of this situation is that the report has been flushed from memory due to a stress on the library cache. Hopefully, I was able to get the bind variable and re-execute the same query. While the query was running I opened a sqlplus window and run this:

SQL> select sql_id from v$sql_monitor where status = 'EXECUTING';
no rows selected

The query was still runing after a couple of minutes but was still not monitored. I was suspecting the number of operations in the execution plan but has no way to proof the correlation between this number of lines and the absence of the monitoring

Plan hash value: 1504525856
----------------------------------------------------------------------
| Id  | Operation                                                    |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |
|   1 |  UNION-ALL                                                   |
|   2 |   SORT UNIQUE                                                |
|   3 |    MERGE JOIN CARTESIAN                                      |
|   4 |     MERGE JOIN CARTESIAN                                     |
|   5 |      NESTED LOOPS                                            |
|*  6 |       HASH JOIN OUTER                                        |
|   7 |        MERGE JOIN CARTESIAN                                  |
|   8 |         NESTED LOOPS OUTER                                   |
|*  9 |          HASH JOIN OUTER                                     |
|* 10 |           HASH JOIN OUTER                                    |
|* 11 |            HASH JOIN OUTER                                   |
|* 12 |             HASH JOIN OUTER                                  |
|* 13 |              HASH JOIN OUTER                                 |
.../...
|*202 |                                               HASH JOIN      |
| 203 | OUTER                                          NESTED LOOPS  |
| 204 |  OUTER                                          NESTED LOOPS |
|*205 |                                                  HASH JOIN   |
|*206 | OUTER                                             HASH JOIN  |
.../...
| 383 |          BUFFER SORT                                         |
| 384 |           PX RECEIVE                                         |
| 385 |            PX SEND BROADCAST                                 |
| 386 |             TABLE ACCESS FULL                                |
----------------------------------------------------------------------

Spot in passing where the OUTER at line 51 and 53 has been placed.

Google being a good friend I asked him and he directed me to this article where Doug Burns pointed that there is a hidden parameter(_sqlmon_max_planlines) which fixes the maximum number of lines an execution plan must not exceed to be, all other things being equal, monitored.

I decided then to give it a try and I have altered this parameter to accept monitoring my 386 plan operations:

SQL> alter session set "_sqlmon_max_planlines" = 400;

Session altered.

And to my pleasant surprise I found that my query started being monitored


SQL Monitoring Report

Global Information
------------------------------
 Status              :  EXECUTING
 Instance ID         :  1
 Session             :  xxxx (626:3043)
 SQL ID              :  315sc2w0cy05w
 SQL Execution ID    :  16777216
 Execution Started   :  04/29/2015 11:29:39
 First Refresh Time  :  04/29/2015 11:29:46
 Last Refresh Time   :  04/29/2015 11:35:26
 Duration            :  348s
 Module/Action       :  sqldeveloper64W.exe/-
 Service             :  SYS$USERS
 Program             :  sqldeveloper64W.exe   

 SQL Plan Monitoring Details (Plan Hash Value=1504525856)
==============================================================================================
| Id    |                                     Operation                                      |
|       |                                                                                    |
==============================================================================================
|     0 | SELECT STATEMENT                                                                   |
|     1 |   UNION-ALL                                                                        |
|     2 |    SORT UNIQUE                                                                     |
|     3 |     MERGE JOIN CARTESIAN                                                           |
|     4 |      MERGE JOIN CARTESIAN                                                          |
|     5 |       NESTED LOOPS                                                                 |
|     6 |        HASH JOIN OUTER                                                             |
|     7 |         MERGE JOIN CARTESIAN                                                       |

|   383 |           BUFFER SORT                                                              |
|   384 |            PX RECEIVE                                                              |
|   385 |             PX SEND BROADCAST                                                      |
|   386 |              TABLE ACCESS FULL                                                     |
==============================================================================================

And now the serious stuff can start🙂

7 Comments »

  1. Also the /*+ monitor */ hint should override the length limit.
    Wondering aloud and away from a computer, perhaps it can be injected with a sql patch if circumstances required?

    Comment by Dom Brooks — April 29, 2015 @ 5:43 pm | Reply

    • the /*+ monitor */ hint does not override the length limit.
      See also https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4087213300346035820#8875473300346428233

      Comment by Matthias Rogel — April 30, 2015 @ 5:19 am | Reply

      • Hi Dominic,

        I don’t know why it seems to me obvious that the MONITOR hint will not override the length limit. The query was running for more than an hour and it was run in parallel. Two main monitoring ingredients were already implicitly present. There was certainly another reason for this absence which can not be overridden by the MONITOR hint as pointed by Matthias. I’ve just tested it right now:

        
        SQLDEV> SELECT 
          /*+
             MONITOR
          */
                 distinct xxxx
          ;
        
        SQL> select sql_id from v$sql_monitor where status = 'EXECUTING';
             
              aucune ligne selectionnee
        

        It is only when I extended the sqlmon_max_planlines parameter that the query started being monitored

        SQLDEV> alter session set "_sqlmon_max_planlines" = 400;
        
        SQLDEV> SELECT 
          /*+
             MONITOR
          */
                distinct xxxx
        ;
        
        SQL> select sql_id from v$sql_monitor where status = 'EXECUTING';
        
        SQL_ID
        -------------
        c8rttd1dwy613
        
        1 ligne selectionnee.
        

        Comment by hourim — April 30, 2015 @ 6:47 am

      • OK.
        I thought it did, my mistake.
        Thanks for the correction.

        Comment by Dom Brooks — April 30, 2015 @ 12:50 pm

  2. Aziz avait raison.

    Comment by ahmed aangour — April 29, 2015 @ 10:36 pm | Reply

    • Salut Ahmed

      Je vais discuter avec Aziz afin que la prochaine fois il ne me laisse pas chercher trop longtemps quelque chose qu’il connait déjà🙂

      Bien Cordialement

      Comment by hourim — April 30, 2015 @ 6:48 am | Reply

  3. […]  Part 1: where I have explained why I was unable to get the corresponding SQL monitoring report because of the _sqlmon_max_planlines parameter. […]

    Pingback by Don’t pre-empt the CBO from doing its work | Mohamed Houri’s Oracle Notes — July 2, 2015 @ 2:03 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: