trusted online casino malaysia
«
»

Christmas Explain Plan Patterns

December 8, 2011, 3:44 pm

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 |
Category: Humor, Oracle  |  Comment (RSS)  |  Trackback

6 Comments

  1. Log Buffer #249, A Carnival of the Vanities for DBAs | The Pythian Blog says:
    December 9, 2011 at 1:01 am

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

    Reply to this comment
  2. sid says:
    December 10, 2011 at 2:18 am

    hi Kerry,

    i’m just curious, what’s the sql to generate the lovely plan? 🙂

    Reply to this comment
  3. osborne says:
    December 12, 2011 at 10:47 am

    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.

    Reply to this comment
  4. Fei says:
    December 12, 2011 at 7:07 pm

    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,

    Reply to this comment
  5. osborne says:
    December 12, 2011 at 9:12 pm

    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

    Reply to this comment
  6. Fei says:
    December 13, 2011 at 10:47 pm

    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

    Reply to this comment

Leave a Reply