Christmas Explain Plan Patterns

Here’s a lovely Candy Striped pattern in an Explain Plan. Looks like the traditional Christmas candy canes. Just in time for the Holiday Season!

| 156 |     FAST DUAL                                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 157 |      FAST DUAL                                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 158 |       FAST DUAL                                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 159 |        FAST DUAL                                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 160 |         FAST DUAL                                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 161 |          FAST DUAL                                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 162 |           FAST DUAL                                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 163 |            FAST DUAL                                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 164 |             FAST DUAL                                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 165 |              FAST DUAL                                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 166 |               FAST DUAL                                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 167 |                FAST DUAL                                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 168 |                 FAST DUAL                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 169 |                  FAST DUAL                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 170 |                   FAST DUAL                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 171 |                    FAST DUAL                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 172 |                     FAST DUAL                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 173 |                      FAST DUAL                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 174 |                       FAST DUAL                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 175 |                        FAST DUAL                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 176 |                         FAST DUAL                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 177 |                          FAST DUAL                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 178 |                           FAST DUAL                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 179 |                            FAST DUAL                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 180 |                             FAST DUAL                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 181 |                              FAST DUAL                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 182 |                               FAST DUAL                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 183 |                                FAST DUAL                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 184 |                                 FAST DUAL                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 185 |                                  FAST DUAL                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 186 |                                   FAST DUAL                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 187 |                                    FAST DUAL                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 188 |                                     FAST DUAL                |                                |     1 |       |     2   (0)| 00:00:01 |
| 189 |                                      FAST DUAL               |                                |     1 |       |     2   (0)| 00:00:01 |
| 190 |                                       FAST DUAL              |                                |     1 |       |     2   (0)| 00:00:01 |
| 191 |                                        FAST DUAL             |                                |     1 |       |     2   (0)| 00:00:01 |
| 192 |                                         FAST DUAL            |                                |     1 |       |     2   (0)| 00:00:01 |
| 193 |                                          FAST DUAL           |                                |     1 |       |     2   (0)| 00:00:01 |
| 194 |                                           FAST DUAL          |                                |     1 |       |     2   (0)| 00:00:01 |
| 195 |                                            FAST DUAL         |                                |     1 |       |     2   (0)| 00:00:01 |
| 196 |                                             FAST DUAL        |                                |     1 |       |     2   (0)| 00:00:01 |
| 197 |                                              FAST DUAL       |                                |     1 |       |     2   (0)| 00:00:01 |
| 198 |                                               FAST DUAL      |                                |     1 |       |     2   (0)| 00:00:01 |
| 199 |                                                FAST DUAL     |                                |     1 |       |     2   (0)| 00:00:01 |
| 200 |                                                 FAST DUAL    |                                |     1 |       |     2   (0)| 00:00:01 |
| 201 |                                                  FAST DUAL   |                                |     1 |       |     2   (0)| 00:00:01 |
| 202 |                                                   FAST DUAL  |                                |     1 |       |     2   (0)| 00:00:01 |
| 203 |                                                    FAST DUAL |                                |     1 |       |     2   (0)| 00:00:01 |
| 204 | L                                                   FAST DUA |                                |     1 |       |     2   (0)| 00:00:01 |
| 205 | AL                                                   FAST DU |                                |     1 |       |     2   (0)| 00:00:01 |
| 206 | UAL                                                   FAST D |                                |     1 |       |     2   (0)| 00:00:01 |
| 207 | DUAL                                                   FAST  |                                |     1 |       |     2   (0)| 00:00:01 |
| 208 |  DUAL                                                   FAST |                                |     1 |       |     2   (0)| 00:00:01 |
| 209 | T DUAL                                                   FAS |                                |     1 |       |     2   (0)| 00:00:01 |
| 210 | ST DUAL                                                   FA |                                |     1 |       |     2   (0)| 00:00:01 |
| 211 | AST DUAL                                                   F |                                |     1 |       |     2   (0)| 00:00:01 |
| 212 |.FAST DUAL                                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 213 |. FAST DUAL                                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 214 |.  FAST DUAL                                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 215 |.   FAST DUAL                                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 216 |.    FAST DUAL                                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 217 |.     FAST DUAL                                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 218 |.      FAST DUAL                                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 219 |.       FAST DUAL                                             |                                |     1 |       |     2   (0)| 00:00:01 |
| 220 |.        FAST DUAL                                            |                                |     1 |       |     2   (0)| 00:00:01 |
| 221 |.         FAST DUAL                                           |                                |     1 |       |     2   (0)| 00:00:01 |
| 222 |.          FAST DUAL                                          |                                |     1 |       |     2   (0)| 00:00:01 |
| 223 |.           FAST DUAL                                         |                                |     1 |       |     2   (0)| 00:00:01 |
| 224 |.            FAST DUAL                                        |                                |     1 |       |     2   (0)| 00:00:01 |
| 225 |.             FAST DUAL                                       |                                |     1 |       |     2   (0)| 00:00:01 |
| 226 |.              FAST DUAL                                      |                                |     1 |       |     2   (0)| 00:00:01 |
| 227 |.               FAST DUAL                                     |                                |     1 |       |     2   (0)| 00:00:01 |
| 228 |.                FAST DUAL                                    |                                |     1 |       |     2   (0)| 00:00:01 |
| 229 |.                 FAST DUAL                                   |                                |     1 |       |     2   (0)| 00:00:01 |
| 230 |.                  FAST DUAL                                  |                                |     1 |       |     2   (0)| 00:00:01 |
| 231 |.                   FAST DUAL                                 |                                |     1 |       |     2   (0)| 00:00:01 |
| 232 |.                    FAST DUAL                                |                                |     1 |       |     2   (0)| 00:00:01 |
| 233 |.                     FAST DUAL                               |                                |     1 |       |     2   (0)| 00:00:01 |
| 234 |.                      FAST DUAL                              |                                |     1 |       |     2   (0)| 00:00:01 |
| 235 |.                       FAST DUAL                             |                                |     1 |       |     2   (0)| 00:00:01 |

6 Comments

  1. […] Kerry Osborne shares the Christmas Explain Plan patterns in a lovely way. […]

  2. sid says:

    hi Kerry,

    i’m just curious, what’s the sql to generate the lovely plan? :-)

  3. osborne says:

    Well it’s a pretty simple query on a very complex view. Too ugly to clean up and present without offending anyone. The part that generated this portion of the plan (which was several hundred lines long by the way) was a bunch of items in the select list of one of the many sub-queries that looked like this:

    select 
    (select myfunction(x) from dual) x,
    (select myfunction(y) from dual) y,
    (select myfunction(z) from dual) z,
    from some_table
    ...
    

    of course these values were joined elsewhere which resulted in nested loops which resulted in the indented

    FAST DUAL
     FAST DUAL
      FAST DUAL
    ...
    

    output from XPLAN.

  4. Fei says:

    I know it’s not the right place to ask this question. but Just want to try the luck.

    Basically I want to know what does obj#=-1 mean. From what I can search online, it means the operation is upon no object in particular. But for my case, the wait is on index split, and suppose the obj# is the object_id of the specific index.

    *** 2011-12-07 21:26:44.313
    WAIT #13: nam=’enq: TX – index contention’ ela= 33006199 name|mode=1415053316 usn<<16 | slot=14876705 sequence=14555 obj#=-1 tim=1323322004313235
    WAIT #13: nam='buffer busy waits' ela= 114 file#=19 block#=54725847 class#=1 obj#=259012 tim=1323322004313424

    I hope I can get some feedback. And happy holidays.

    Thanks,

  5. osborne says:

    Hi Fei,

    My experience with obj# in wait events is that the developers were not very consistent about cleaning up after themselves. It is pretty common to see events that are not related to individual objects that have an obj# (presumably because the developers didn’t clear the field), -1 is generally used when an obj# does not apply and the developers have taken the time to set it. But it could also be a case where the value -1 is still hanging around and was not set properly. It is also possible that it is just part a string variable that didn’t get cleared properly. For example the log file sync event, which clearly has no relation to a specific object, looks like this in a recent trace file I generated in 11.2.0.2:

    WAIT #47177491275624: nam='log file sync' ela= 661 buffer#=24356 sync scn=816350767 p3=0 obj#=-1 tim=1323740791181536
    

    Of course “log file sync” is not even supposed to have an obj# parameter.

    
    SYS@dbm1> @valid_events
    Enter value for event_name: log file sync
    
    EVENT                                                            CLASS           P1                   P2                   P3
    ---------------------------------------------------------------- --------------- -------------------- -------------------- --------------------
    log file sync                                                    Commit          buffer#              sync scn
    |
    

    By the way, it looks like the “enq: TX – index contention” event is not supposed to have the obj# parameter either.

    SYS@dbm1> @valid_events
    Enter value for event_name: %index content%
    
    EVENT                                                            CLASS           P1                   P2                   P3
    ---------------------------------------------------------------- --------------- -------------------- -------------------- --------------------
    enq: TX - index contention                                       Concurrency     name|mode            usn< <16 | slot       sequence
    |
    

    So I can only assume that this is just a bug and not really related to the event at all.

    Kerry

  6. Fei says:

    Hi Kerry,

    Thanks very much for the reply.

    I thought the obj# of ‘enq: TX – index contention’ is the object id of the index. Like this I got in my trace file.

    WAIT #9: nam=’enq: TX – index contention’ ela= 423 name|mode=1415053316 usn<<16 | slot=13041696 sequence=97164 obj#=243941 tim=1323553225899572

    When it shows -1, it is difficult to find the exact index has this enqueue.

    Thanks,
    Fei

Leave a Reply