Adding Features to PowerPoint Based Oracle Presentation Files

28 04 2011

April 28, 2011

This blog article is not specific to Oracle Database, but I thought that I would share the concepts anyway.  If you are giving presentations and using PowerPoint, consider including detailed notes sections in the presentation.  What benefit do those notes sections serve?  Well, if you share the presentation files with the viewers of the presentation, those note sections act as a reminder of what was stated during your presentation.  In fact, you could go so far as to type everything that you intend to say during the presentation into the presentation notes section for the slides.

Let’s take a look at a couple of interesting features that can be implemented in a PowerPoint presentation when detailed notes sections are provided.

Read to Me:

In 1998 (or maybe it was 1999) I experimented with the Microsoft Speech API, which at that time was still in Beta form for the initial release.  More recent releases of the Microsoft Speech API are obviously much more sophisticated, but at the time it was possible to easily change between one of several “voices” with different pitch and speed settings.  It is very easy to incorporate speech capabilities into a PowerPoint presentation, because the Speech API is installed by default on computers running Microsoft Office (I believe that the Speech API is also included in Windows operating systems starting with Microsoft Vista).  A very simple, generic PowerPoint macro may be used to read back the notes section of the currently displayed slide:

Sub SpeakNotes()
    Const SVSFlagsAsync = 1
    Const SVSFPurgeBeforeSpeak = 2
    'Dim strText As String
    Dim strSpeech As String
    Dim objSpeech As Object
    Dim lngCurrentSlide As Long

    On Error Resume Next

    Set objSpeech = CreateObject("SAPI.SpVoice")

    lngCurrentSlide = SlideShowWindows(1).View.CurrentShowPosition

    If Application.Version <= "11.0" Then
        strSpeech = ActivePresentation.Slides(lngCurrentSlide).NotesPage.Shapes.Placeholders(2).TextFrame.TextRange.Text
        'Change the pitch
        'strSpeech = "<pitch middle='25'>" & ActivePresentation.Slides(lngCurrentSlide).NotesPage.Shapes.Placeholders(2).TextFrame.TextRange.Text
    Else
        strSpeech = ActivePresentation.Slides(lngCurrentSlide).NotesPage.Shapes.Placeholders(2).TextFrame.TextRange.Text
    End If

    objSpeech.Speak strSpeech

    Set objSpeech = Nothing
End Sub 

In the above you will notice that the macro code checks the version of PowerPoint so that it can potentially run a different set of control commands for the speech API (I do not recall the exact reason why I included this years ago, but I believe it is because the default voice in Microsoft Office 2003 is a male voice, while the default voice in Microsoft Office 2007 is a female voice).  Now all that needs to be done is to create a picture or object of some sort on a slide and associate an action with the object that executes the above macro.  I have used a couple of different objects over the years, typically designed to clearly communicate what will happen when the object is clicked, for example:

Write to Me:

Another interesting feature that may be implemented is exporting the slides to JPG pictures, and then building a Microsoft Word Document from the exported JPG pictures and the slide notes – this is helpful for both the presenter and the people learning from the presentation.  In the past I had to manually create these types of handouts, so I thought “why not automate the process?”

We will start with the code to generate the JPG pictures from the presentation slides:

 
Sub WriteSlidestoJPG()
    On Error Resume Next

    'Create a folder for the slides if one does not already exist
    If Len(Dir("C:\Presentation Slides", vbDirectory)) < 4 Then
        MkDir "C:\Presentation Slides"
    End If

    'Remove any slides from a previous execution
    Kill "C:\Presentation Slides\*.*"
    'Save the slides as JPG pictures
    ActivePresentation.Export "C:\Presentation Slides", "JPG", 640, 480  '640 pixels by 480 pixels
End Sub

Next, we will add a second macro that builds the Microsoft Word document:

Sub SendPowerPointSlidestoWord()
    Dim i As Integer
    Dim objWord As Word.Application

    On Error Resume Next

    Set objWord = New Word.Application

    If Err = 0 Then
        WriteSlidestoJPG

        With objWord
            .Documents.Add
            .Visible = True
            With .ActiveDocument.Styles(wdStyleNormal).Font
                If .NameFarEast = .NameAscii Then
                    .NameAscii = ""
                End If
                .NameFarEast = ""
            End With
            With .ActiveDocument.PageSetup
                .TopMargin = InchesToPoints(0.5)
                .BottomMargin = InchesToPoints(0.5)
                .LeftMargin = InchesToPoints(0.75)
                .RightMargin = InchesToPoints(0.25)
                .HeaderDistance = InchesToPoints(0.25)
                .FooterDistance = InchesToPoints(0.25)
            End With

            If .ActiveWindow.View.SplitSpecial <> wdPaneNone Then
                .ActiveWindow.Panes(2).Close
            End If
            .ActiveWindow.ActivePane.View.Type = wdPrintView
            .ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
            .Selection.Style = .ActiveDocument.Styles("Heading 1")
            .Selection.TypeText Text:=Left(ActivePresentation.Name, InStrRev(ActivePresentation.Name, ".") - 1)
            .Selection.TypeText Text:="   by " & ActivePresentation.BuiltInDocumentProperties.Item("author").Value
            .ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
            .Selection.ParagraphFormat.TabStops(InchesToPoints(6)).Position = InchesToPoints(7.5)
            .Selection.TypeText Text:=vbTab & vbTab & "Page "
            .Selection.Fields.Add Range:=.Selection.Range, Type:=wdFieldPage
            .Selection.TypeText Text:=" of "
            .Selection.Fields.Add Range:=.Selection.Range, Type:=wdFieldNumPages
            .ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument

            .Selection.MoveLeft Unit:=wdCharacter, Count:=2

            .ActiveDocument.Tables.Add Range:=.Selection.Range, NumRows:=ActivePresentation.Slides.Count, NumColumns _
                :=2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                wdAutoFitFixed
            With .Selection.Tables(1)
                .Columns.PreferredWidth = InchesToPoints(7.5)
            End With
            With .Selection.Tables(1)
                .TopPadding = InchesToPoints(0)
                .BottomPadding = InchesToPoints(0)
                .LeftPadding = InchesToPoints(0.08)
                .RightPadding = InchesToPoints(0.08)
                .Spacing = 0
                .AllowPageBreaks = True
                .AllowAutoFit = False
            End With
            .Selection.Tables(1).Columns(1).PreferredWidthType = wdPreferredWidthPoints
            .Selection.Tables(1).Columns(1).PreferredWidth = InchesToPoints(3)
            .Selection.Move Unit:=wdColumn, Count:=1
            .Selection.SelectColumn
            .Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
            .Selection.Columns.PreferredWidth = InchesToPoints(4.5)

            .Selection.MoveLeft Unit:=wdCharacter, Count:=2

            For i = 1 To ActivePresentation.Slides.Count
                .Selection.InlineShapes.AddPicture FileName:="C:\Presentation Slides\Slide" & Format(i) & ".JPG", LinkToFile:=False, SaveWithDocument:=True
                .Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
                .Selection.InlineShapes(1).LockAspectRatio = msoTrue
                .Selection.InlineShapes(1).Width = 203.05
                .Selection.InlineShapes(1).Height = 152.65
                .Selection.MoveRight Unit:=wdCharacter, Count:=2
                With .Selection.Font
                    .Name = "Times New Roman"
                    .Size = 8
                    .Bold = False
                End With
                .Selection.TypeText Text:=ActivePresentation.Slides(i).NotesPage.Shapes.Placeholders(2).TextFrame.TextRange.Text
                .Selection.MoveDown Unit:=wdLine, Count:=1
                .Selection.MoveLeft Unit:=wdCharacter, Count:=1
            Next i
        End With
    End If
    Set objWord = Nothing
End Sub 

—————

Anyone else have additional ideas for adding features to PowerPoint based Oracle Presentation Files?





What to Do When Performance Problems Appear

23 04 2011

April 23, 2011

I have a bit of a performance problem.  To be more specific, I am preparing to give a presentation on the topic of Oracle Database performance tuning for a specific ERP product, and I have encountered a performance problem.  Tuning the performance problem will requiring dropping too many slides – 59 slides for a 60 minute presentation with live demos taking up probably 20 minutes.  I think that my performance problem stems from spending too much time in PowerPoint – in the screen capture below a person watching the presentation can click the smiley face in the purple box to have the computer read my notes to the viewer.

The notes that go along with the above slide are as follows:

So, what do you do when you encounter a performance problem… contacting (the vendor’s) support probably is not the best approach.

You must be specific – stating that the database is slow does not offer a suitable starting point for performance improvement.  Pick one person who is encountering performance problems, and ask the person to describe what is happening – has this feature always been slow?  Gather metrics that measure the current performance, otherwise you will not know if the changes that are made will help or hurt performance. 

Work your way out from the database instance to the server, the network, and then the client.  If the database instance reports that the vast majority of the time is spent waiting for the next request from the client, then you need to move on to checking the network and client computer.  If the database instance reports that a lot of CPU time is being consumed, but not by the instance, then you need to look at the other processes that are running on the server.  If the database instance reports significant amounts of CPU time or wait events, then you need to take a closer look at the server configuration, database instance configuration, and execution plans.

Once you implement a change you should again measure the performance to verify that it improved – you might then identify another issue that should be addressed.  Avoid compulsive tuning disorder.

Just to make certain that that the above is clear, I put together this additional bit of information:

Of course, there are notes to go along with the slide:

Breaking it down again – when a performance problem is identified you will ideally take it down to the report or SQL statement level for analysis.  That level very often utilizes 10046 extended SQL traces for analysis.  The other suggestions provided on this slide will hopefully help you determine if the performance problem is in the database instance, or somewhere else.

If you had this performance tuning problem, what would you do (remember, there is only 60 to 120 seconds to convey the message of the slides)?  The second of the above slides was originally intended to appear as a decision tree, but that organization never quite materialized.

On a related topic, I was reviewing the data captured by one of my tools and found an old friend of a SQL statement:

This is the SQL statement that is highlighted, second from the bottom:

(SELECT /*+ RULE */ '', T.owner, T.table_name, TO_NUMBER(NULL), '', '', 0, TO_NUMBER(NULL), '', '', num_rows, blocks, NULL FROM ALL_TABLES T
WHERE table_name='PART' UNION SELECT /*+ RULE */ '', a.table_owner, a.table_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name,
 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b
WHERE a.owner = b.index_owner AND a.index_name = b.index_name AND a.table_owner='TESTUSER' AND a.table_name='PART'
UNION SELECT /*+ RULE */ '', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name, 3, b.column_position,
 b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c
WHERE c.synonym_name='PART' AND c.owner='TESTUSER' AND c.table_name = a.table_name AND c.table_name = b.table_name
AND c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner AND a.index_name = b.index_name )
ORDER BY 4, 5, 6, 8 

The SQL statement directly below it is child 1 of the same SQL ID.  I know that you just can’t wait to see the execution plan for this SQL statement (take a close look at the above picture to see the CPU and consistent gets for a 70 second capture period):

SQL_ID  6wyvp7mhhmv91, child number 0
-------------------------------------
(SELECT /*+ RULE */ '', T.owner, T.table_name, TO_NUMBER(NULL), '', '', 0, TO_NUMBER(NULL),
'', '', num_rows, blocks, NULL FROM ALL_TABLES T WHERE table_name='PART' UNION SELECT /*+
RULE */ '', a.table_owner, a.table_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner,
a.index_name, 3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks,
NULL FROM ALL_INDEXES a, ALL_IND_COLUMNS b WHERE a.owner = b.index_owner AND a.index_name =
b.index_name AND a.table_owner='TESTUSER' AND a.table_name='PART' UNION SELECT /*+ RULE */
'', c.owner, c.synonym_name, decode(a.uniqueness, 'UNIQUE', 0, 1), a.owner, a.index_name,
3, b.column_position, b.column_name, 'A', a.distinct_keys, a.leaf_blocks, NULL FROM
ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c WHERE c.synonym_name='PART' AND
c.owner='TESTUSER' AND c.table_name = a.table_name AND c.table_name = b.table_name AND
c.table_owner= a.table_owner AND c.table_owner= b.table_owner AND a.owner = b.index_owner
AND a.index_name = b.index_na

Plan hash value: 80321140

----------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name               | Inst   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                    |        |
|   1 |  SORT UNIQUE                                                 |                    |        |
|   2 |   UNION-ALL                                                  |                    |        |
|*  3 |    FILTER                                                    |                    |        |
|   4 |     NESTED LOOPS                                             |                    |        |
|   5 |      NESTED LOOPS                                            |                    |        |
|   6 |       NESTED LOOPS OUTER                                     |                    |        |
|   7 |        NESTED LOOPS OUTER                                    |                    |        |
|   8 |         NESTED LOOPS OUTER                                   |                    |        |
|   9 |          NESTED LOOPS OUTER                                  |                    |        |
|  10 |           NESTED LOOPS                                       |                    |        |
|  11 |            NESTED LOOPS                                      |                    |        |
|  12 |             MERGE JOIN                                       |                    |        |
|* 13 |              FIXED TABLE FULL                                | X$KSPPI            |   TEST |
|* 14 |              SORT JOIN                                       |                    |        |
|  15 |               FIXED TABLE FULL                               | X$KSPPCV           |   TEST |
|* 16 |             TABLE ACCESS FULL                                | OBJ$               |   TEST |
|* 17 |            TABLE ACCESS CLUSTER                              | TAB$               |   TEST |
|* 18 |             INDEX UNIQUE SCAN                                | I_OBJ#             |   TEST |
|  19 |           TABLE ACCESS BY INDEX ROWID                        | OBJ$               |   TEST |
|* 20 |            INDEX UNIQUE SCAN                                 | I_OBJ1             |   TEST |
|* 21 |          INDEX UNIQUE SCAN                                   | I_OBJ1             |   TEST |
|  22 |         TABLE ACCESS CLUSTER                                 | USER$              |   TEST |
|* 23 |          INDEX UNIQUE SCAN                                   | I_USER#            |   TEST |
|  24 |        TABLE ACCESS CLUSTER                                  | SEG$               |   TEST |
|* 25 |         INDEX UNIQUE SCAN                                    | I_FILE#_BLOCK#     |   TEST |
|  26 |       TABLE ACCESS CLUSTER                                   | TS$                |   TEST |
|* 27 |        INDEX UNIQUE SCAN                                     | I_TS#              |   TEST |
|  28 |      TABLE ACCESS CLUSTER                                    | USER$              |   TEST |
|* 29 |       INDEX UNIQUE SCAN                                      | I_USER#            |   TEST |
|  30 |     NESTED LOOPS                                             |                    |        |
|  31 |      FIXED TABLE FULL                                        | X$KZSRO            |   TEST |
|* 32 |      INDEX RANGE SCAN                                        | I_OBJAUTH2         |   TEST |
|* 33 |       FIXED TABLE FULL                                       | X$KZSPR            |   TEST |
|* 34 |    FILTER                                                    |                    |        |
|  35 |     NESTED LOOPS                                             |                    |        |
|  36 |      NESTED LOOPS                                            |                    |        |
|  37 |       NESTED LOOPS OUTER                                     |                    |        |
|  38 |        NESTED LOOPS                                          |                    |        |
|  39 |         NESTED LOOPS                                         |                    |        |
|  40 |          NESTED LOOPS                                        |                    |        |
|  41 |           NESTED LOOPS                                       |                    |        |
|  42 |            NESTED LOOPS OUTER                                |                    |        |
|  43 |             NESTED LOOPS OUTER                               |                    |        |
|  44 |              NESTED LOOPS                                    |                    |        |
|  45 |               NESTED LOOPS                                   |                    |        |
|  46 |                NESTED LOOPS OUTER                            |                    |        |
|  47 |                 NESTED LOOPS                                 |                    |        |
|  48 |                  NESTED LOOPS OUTER                          |                    |        |
|  49 |                   NESTED LOOPS                               |                    |        |
|  50 |                    NESTED LOOPS                              |                    |        |
|  51 |                     TABLE ACCESS BY INDEX ROWID              | USER$              |   TEST |
|* 52 |                      INDEX UNIQUE SCAN                       | I_USER1            |   TEST |
|* 53 |                     TABLE ACCESS BY INDEX ROWID              | OBJ$               |   TEST |
|* 54 |                      INDEX RANGE SCAN                        | I_OBJ2             |   TEST |
|* 55 |                    TABLE ACCESS CLUSTER                      | IND$               |   TEST |
|* 56 |                     INDEX UNIQUE SCAN                        | I_OBJ#             |   TEST |
|  57 |                   TABLE ACCESS BY INDEX ROWID                | OBJ$               |   TEST |
|* 58 |                    INDEX UNIQUE SCAN                         | I_OBJ1             |   TEST |
|* 59 |                  TABLE ACCESS BY INDEX ROWID                 | OBJ$               |   TEST |
|* 60 |                   INDEX UNIQUE SCAN                          | I_OBJ1             |   TEST |
|  61 |                 TABLE ACCESS CLUSTER                         | USER$              |   TEST |
|* 62 |                  INDEX UNIQUE SCAN                           | I_USER#            |   TEST |
|  63 |                TABLE ACCESS CLUSTER                          | USER$              |   TEST |
|* 64 |                 INDEX UNIQUE SCAN                            | I_USER#            |   TEST |
|  65 |               TABLE ACCESS BY INDEX ROWID                    | USER$              |   TEST |
|* 66 |                INDEX UNIQUE SCAN                             | I_USER1            |   TEST |
|  67 |              TABLE ACCESS CLUSTER                            | SEG$               |   TEST |
|* 68 |               INDEX UNIQUE SCAN                              | I_FILE#_BLOCK#     |   TEST |
|  69 |             TABLE ACCESS CLUSTER                             | TS$                |   TEST |
|* 70 |              INDEX UNIQUE SCAN                               | I_TS#              |   TEST |
|  71 |            TABLE ACCESS BY INDEX ROWID                       | OBJ$               |   TEST |
|* 72 |             INDEX RANGE SCAN                                 | I_OBJ2             |   TEST |
|* 73 |           TABLE ACCESS BY INDEX ROWID                        | IND$               |   TEST |
|* 74 |            INDEX UNIQUE SCAN                                 | I_IND1             |   TEST |
|  75 |          TABLE ACCESS BY INDEX ROWID                         | ICOL$              |   TEST |
|* 76 |           INDEX RANGE SCAN                                   | I_ICOL1            |   TEST |
|* 77 |         TABLE ACCESS CLUSTER                                 | COL$               |   TEST |
|* 78 |        TABLE ACCESS CLUSTER                                  | ATTRCOL$           |   TEST |
|  79 |       TABLE ACCESS BY INDEX ROWID                            | OBJ$               |   TEST |
|* 80 |        INDEX UNIQUE SCAN                                     | I_OBJ1             |   TEST |
|  81 |      TABLE ACCESS CLUSTER                                    | USER$              |   TEST |
|* 82 |       INDEX UNIQUE SCAN                                      | I_USER#            |   TEST |
|  83 |     NESTED LOOPS                                             |                    |        |
|  84 |      FIXED TABLE FULL                                        | X$KZSRO            |   TEST |
|* 85 |      INDEX RANGE SCAN                                        | I_OBJAUTH2         |   TEST |
|* 86 |       FIXED TABLE FULL                                       | X$KZSPR            |   TEST |
|  87 |        NESTED LOOPS                                          |                    |        |
|  88 |         FIXED TABLE FULL                                     | X$KZSRO            |   TEST |
|* 89 |         INDEX RANGE SCAN                                     | I_OBJAUTH2         |   TEST |
|* 90 |          FIXED TABLE FULL                                    | X$KZSPR            |   TEST |
|* 91 |    FILTER                                                    |                    |        |
|  92 |     NESTED LOOPS OUTER                                       |                    |        |
|  93 |      NESTED LOOPS OUTER                                      |                    |        |
|  94 |       NESTED LOOPS OUTER                                     |                    |        |
|  95 |        NESTED LOOPS                                          |                    |        |
|  96 |         NESTED LOOPS OUTER                                   |                    |        |
|  97 |          NESTED LOOPS                                        |                    |        |
|  98 |           NESTED LOOPS                                       |                    |        |
|  99 |            NESTED LOOPS                                      |                    |        |
| 100 |             NESTED LOOPS                                     |                    |        |
| 101 |              NESTED LOOPS OUTER                              |                    |        |
| 102 |               NESTED LOOPS                                   |                    |        |
| 103 |                NESTED LOOPS                                  |                    |        |
| 104 |                 NESTED LOOPS                                 |                    |        |
| 105 |                  NESTED LOOPS                                |                    |        |
| 106 |                   NESTED LOOPS                               |                    |        |
| 107 |                    NESTED LOOPS                              |                    |        |
| 108 |                     NESTED LOOPS                             |                    |        |
| 109 |                      VIEW                                    | ALL_SYNONYMS       |   TEST |
| 110 |                       SORT UNIQUE                            |                    |        |
| 111 |                        UNION-ALL                             |                    |        |
|*112 |                         FILTER                               |                    |        |
| 113 |                          NESTED LOOPS                        |                    |        |
| 114 |                           NESTED LOOPS                       |                    |        |
| 115 |                            TABLE ACCESS BY INDEX ROWID       | USER$              |   TEST |
|*116 |                             INDEX UNIQUE SCAN                | I_USER1            |   TEST |
|*117 |                            TABLE ACCESS BY INDEX ROWID       | OBJ$               |   TEST |
|*118 |                             INDEX RANGE SCAN                 | I_OBJ2             |   TEST |
| 119 |                           TABLE ACCESS BY INDEX ROWID        | SYN$               |   TEST |
|*120 |                            INDEX UNIQUE SCAN                 | I_SYN1             |   TEST |
|*121 |                          FILTER                              |                    |        |
|*122 |                           FILTER                             |                    |        |
| 123 |                            NESTED LOOPS                      |                    |        |
| 124 |                             NESTED LOOPS                     |                    |        |
| 125 |                              TABLE ACCESS BY INDEX ROWID     | USER$              |   TEST |
|*126 |                               INDEX UNIQUE SCAN              | I_USER1            |   TEST |
| 127 |                              TABLE ACCESS BY INDEX ROWID     | OBJ$               |   TEST |
|*128 |                               INDEX RANGE SCAN               | I_OBJ2             |   TEST |
|*129 |                             INDEX RANGE SCAN                 | I_OBJAUTH1         |   TEST |
|*130 |                           FIXED TABLE FULL                   | X$KZSRO            |   TEST |
|*131 |                            FIXED TABLE FULL                  | X$KZSPR            |   TEST |
| 132 |                         NESTED LOOPS                         |                    |        |
| 133 |                          NESTED LOOPS                        |                    |        |
| 134 |                           NESTED LOOPS                       |                    |        |
| 135 |                            TABLE ACCESS BY INDEX ROWID       | USER$              |   TEST |
|*136 |                             INDEX UNIQUE SCAN                | I_USER1            |   TEST |
| 137 |                            VIEW                              | _ALL_SYNONYMS_TREE |   TEST |
|*138 |                             CONNECT BY WITHOUT FILTERING     |                    |        |
|*139 |                              FILTER                          |                    |        |
| 140 |                               COUNT                          |                    |        |
| 141 |                                NESTED LOOPS                  |                    |        |
| 142 |                                 NESTED LOOPS                 |                    |        |
| 143 |                                  NESTED LOOPS                |                    |        |
| 144 |                                   NESTED LOOPS               |                    |        |
| 145 |                                    TABLE ACCESS FULL         | USER$              |   TEST |
| 146 |                                    TABLE ACCESS BY INDEX ROWI| OBJ$               |   TEST |
|*147 |                                     INDEX RANGE SCAN         | I_OBJ2             |   TEST |
| 148 |                                   TABLE ACCESS BY INDEX ROWID| SYN$               |   TEST |
|*149 |                                    INDEX UNIQUE SCAN         | I_SYN1             |   TEST |
| 150 |                                  TABLE ACCESS BY INDEX ROWID | USER$              |   TEST |
|*151 |                                   INDEX UNIQUE SCAN          | I_USER1            |   TEST |
|*152 |                                 TABLE ACCESS BY INDEX ROWID  | OBJ$               |   TEST |
|*153 |                                  INDEX RANGE SCAN            | I_OBJ2             |   TEST |
|*154 |                               FILTER                         |                    |        |
| 155 |                                NESTED LOOPS                  |                    |        |
| 156 |                                 NESTED LOOPS                 |                    |        |
| 157 |                                  NESTED LOOPS                |                    |        |
|*158 |                                   TABLE ACCESS BY INDEX ROWID| SYN$               |   TEST |
|*159 |                                    INDEX UNIQUE SCAN         | I_SYN1             |   TEST |
| 160 |                                   TABLE ACCESS BY INDEX ROWID| USER$              |   TEST |
|*161 |                                    INDEX UNIQUE SCAN         | I_USER1            |   TEST |
| 162 |                                  TABLE ACCESS BY INDEX ROWID | OBJ$               |   TEST |
|*163 |                                   INDEX RANGE SCAN           | I_OBJ2             |   TEST |
|*164 |                                 INDEX RANGE SCAN             | I_OBJAUTH1         |   TEST |
|*165 |                                FIXED TABLE FULL              | X$KZSRO            |   TEST |
| 166 |                              COUNT                           |                    |        |
| 167 |                               NESTED LOOPS                   |                    |        |
| 168 |                                NESTED LOOPS                  |                    |        |
| 169 |                                 NESTED LOOPS                 |                    |        |
| 170 |                                  NESTED LOOPS                |                    |        |
| 171 |                                   TABLE ACCESS FULL          | USER$              |   TEST |
| 172 |                                   TABLE ACCESS BY INDEX ROWID| OBJ$               |   TEST |
|*173 |                                    INDEX RANGE SCAN          | I_OBJ2             |   TEST |
| 174 |                                  TABLE ACCESS BY INDEX ROWID | SYN$               |   TEST |
|*175 |                                   INDEX UNIQUE SCAN          | I_SYN1             |   TEST |
| 176 |                                 TABLE ACCESS BY INDEX ROWID  | USER$              |   TEST |
|*177 |                                  INDEX UNIQUE SCAN           | I_USER1            |   TEST |
|*178 |                                TABLE ACCESS BY INDEX ROWID   | OBJ$               |   TEST |
|*179 |                                 INDEX RANGE SCAN             | I_OBJ2             |   TEST |
|*180 |                           TABLE ACCESS BY INDEX ROWID        | OBJ$               |   TEST |
|*181 |                            INDEX UNIQUE SCAN                 | I_OBJ1             |   TEST |
| 182 |                          TABLE ACCESS BY INDEX ROWID         | SYN$               |   TEST |
|*183 |                           INDEX UNIQUE SCAN                  | I_SYN1             |   TEST |
| 184 |                      TABLE ACCESS BY INDEX ROWID             | USER$              |   TEST |
|*185 |                       INDEX UNIQUE SCAN                      | I_USER1            |   TEST |
| 186 |                     TABLE ACCESS BY INDEX ROWID              | USER$              |   TEST |
|*187 |                      INDEX UNIQUE SCAN                       | I_USER1            |   TEST |
| 188 |                    TABLE ACCESS BY INDEX ROWID               | OBJ$               |   TEST |
|*189 |                     INDEX RANGE SCAN                         | I_OBJ2             |   TEST |
| 190 |                   TABLE ACCESS CLUSTER                       | ICOL$              |   TEST |
|*191 |                    INDEX UNIQUE SCAN                         | I_OBJ#             |   TEST |
| 192 |                  TABLE ACCESS BY INDEX ROWID                 | OBJ$               |   TEST |
|*193 |                   INDEX UNIQUE SCAN                          | I_OBJ1             |   TEST |
|*194 |                 TABLE ACCESS BY INDEX ROWID                  | IND$               |   TEST |
|*195 |                  INDEX UNIQUE SCAN                           | I_IND1             |   TEST |
|*196 |                TABLE ACCESS CLUSTER                          | COL$               |   TEST |
|*197 |               TABLE ACCESS CLUSTER                           | ATTRCOL$           |   TEST |
| 198 |              TABLE ACCESS CLUSTER                            | USER$              |   TEST |
|*199 |               INDEX UNIQUE SCAN                              | I_USER#            |   TEST |
| 200 |             TABLE ACCESS BY INDEX ROWID                      | USER$              |   TEST |
|*201 |              INDEX UNIQUE SCAN                               | I_USER1            |   TEST |
|*202 |            TABLE ACCESS BY INDEX ROWID                       | OBJ$               |   TEST |
|*203 |             INDEX RANGE SCAN                                 | I_OBJ2             |   TEST |
|*204 |           TABLE ACCESS BY INDEX ROWID                        | IND$               |   TEST |
|*205 |            INDEX UNIQUE SCAN                                 | I_IND1             |   TEST |
| 206 |          TABLE ACCESS BY INDEX ROWID                         | OBJ$               |   TEST |
|*207 |           INDEX UNIQUE SCAN                                  | I_OBJ1             |   TEST |
|*208 |         TABLE ACCESS BY INDEX ROWID                          | OBJ$               |   TEST |
|*209 |          INDEX UNIQUE SCAN                                   | I_OBJ1             |   TEST |
| 210 |        TABLE ACCESS CLUSTER                                  | USER$              |   TEST |
|*211 |         INDEX UNIQUE SCAN                                    | I_USER#            |   TEST |
| 212 |       TABLE ACCESS CLUSTER                                   | SEG$               |   TEST |
|*213 |        INDEX UNIQUE SCAN                                     | I_FILE#_BLOCK#     |   TEST |
| 214 |      TABLE ACCESS CLUSTER                                    | TS$                |   TEST |
|*215 |       INDEX UNIQUE SCAN                                      | I_TS#              |   TEST |
| 216 |     NESTED LOOPS                                             |                    |        |
| 217 |      FIXED TABLE FULL                                        | X$KZSRO            |   TEST |
|*218 |      INDEX RANGE SCAN                                        | I_OBJAUTH2         |   TEST |
|*219 |       FIXED TABLE FULL                                       | X$KZSPR            |   TEST |
| 220 |        NESTED LOOPS                                          |                    |        |
| 221 |         FIXED TABLE FULL                                     | X$KZSRO            |   TEST |
|*222 |         INDEX RANGE SCAN                                     | I_OBJAUTH2         |   TEST |
|*223 |          FIXED TABLE FULL                                    | X$KZSPR            |   TEST |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL))
  13 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  14 - access("KSPPI"."INDX"="KSPPCV"."INDX")
       filter("KSPPI"."INDX"="KSPPCV"."INDX")
  16 - filter((BITAND("O"."FLAGS",128)=0 AND "O"."NAME"='PART'))
  17 - filter(BITAND("T"."PROPERTY",1)=0)
  18 - access("O"."OBJ#"="T"."OBJ#")
  20 - access("T"."DATAOBJ#"="CX"."OBJ#")
  21 - access("T"."BOBJ#"="CO"."OBJ#")
  23 - access("CX"."OWNER#"="CU"."USER#")
  25 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")
  27 - access("T"."TS#"="TS"."TS#")
  29 - access("O"."OWNER#"="U"."USER#")
  32 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
  33 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))
  34 - filter((("IDX"."OWNER#"=USERENV('SCHEMAID') OR "BASE"."OWNER#"=USERENV('SCHEMAID')
              OR  IS NOT NULL OR  IS NOT NULL) AND ("IO"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR 
              IS NOT NULL)))
  52 - access("IU"."NAME"='TESTUSER')
  53 - filter("IO"."TYPE#"=2)
  54 - access("IO"."OWNER#"="IU"."USER#" AND "IO"."NAME"='PART')
  55 - filter((("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND BITAND("I"."FLAGS",4096)=0))
  56 - access("I"."BO#"="IO"."OBJ#")
  58 - access("I"."INDMETHOD#"="ITO"."OBJ#")
  59 - filter(BITAND("O"."FLAGS",128)=0)
  60 - access("O"."OBJ#"="I"."OBJ#")
  62 - access("ITO"."OWNER#"="ITU"."USER#")
  64 - access("U"."USER#"="O"."OWNER#")
  66 - access("U"."NAME"="IO"."NAME")
  68 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#")
  70 - access("I"."TS#"="TS"."TS#")
  72 - access("IO"."USER#"="IDX"."OWNER#" AND "O"."NAME"="IDX"."NAME")
  73 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
  74 - access("IDX"."OBJ#"="I"."OBJ#")
  76 - access("IC"."OBJ#"="IDX"."OBJ#")
  77 - filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2
              "))
  78 - filter("C"."INTCOL#"="AC"."INTCOL#")
  80 - access("IC"."BO#"="BASE"."OBJ#")
  82 - access("BO"."USER#"="BASE"."OWNER#")
  85 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
  86 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))
  89 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
  90 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))
  91 - filter((("IDX"."OWNER#"=USERENV('SCHEMAID') OR "BASE"."OWNER#"=USERENV('SCHEMAID')
              OR  IS NOT NULL OR  IS NOT NULL) AND ("IO"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR 
              IS NOT NULL)))
 112 - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR "O"."OWNER#"=1 OR  IS NOT NULL OR
              ("S"."NODE" IS NULL AND  IS NOT NULL)))
 116 - access("U"."NAME"='TESTUSER')
 117 - filter("O"."TYPE#"=5)
 118 - access("O"."OWNER#"="U"."USER#" AND "O"."NAME"='PART')
 120 - access("O"."OBJ#"="S"."OBJ#")
 121 - filter(( IS NOT NULL OR "BA"."GRANTOR#"=USERENV('SCHEMAID')))
 122 - filter(:B1 IS NULL)
 126 - access("BU"."NAME"=:B1)
 128 - access("BU"."USER#"="BO"."OWNER#" AND "BO"."NAME"=:B1)
 129 - access("BA"."OBJ#"="BO"."OBJ#")
 130 - filter("KZSROROL"=:B1)
 131 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))
 136 - access("U"."NAME"='TESTUSER')
 138 - access("BO"."OBJ#"=PRIOR NULL)
 139 - filter( IS NOT NULL)
 147 - access("O"."OWNER#"="U"."USER#")
 149 - access("S"."OBJ#"="O"."OBJ#")
 151 - access("S"."OWNER"="BU"."NAME")
 152 - filter("BO"."TYPE#"=5)
 153 - access("BU"."USER#"="BO"."OWNER#" AND "S"."NAME"="BO"."NAME")
 154 - filter(( IS NOT NULL OR "OA"."GRANTOR#"=USERENV('SCHEMAID')))
 158 - filter("S"."NODE" IS NULL)
 159 - access("S"."OBJ#"=:B1)
 161 - access("S"."OWNER"="BU"."NAME")
 163 - access("BU"."USER#"="BO"."OWNER#" AND "S"."NAME"="BO"."NAME")
 164 - access("BO"."OBJ#"="OA"."OBJ#")
 165 - filter("KZSROROL"=:B1)
 173 - access("O"."OWNER#"="U"."USER#")
 175 - access("S"."OBJ#"="O"."OBJ#")
 177 - access("S"."OWNER"="BU"."NAME")
 178 - filter("BO"."TYPE#"=5)
 179 - access("BU"."USER#"="BO"."OWNER#" AND "S"."NAME"="BO"."NAME")
 180 - filter(("O"."NAME"='PART' AND "O"."OWNER#"="U"."USER#" AND "O"."TYPE#"=5))
 181 - access("O"."OBJ#"="ST"."SYN_ID")
 183 - access("O"."OBJ#"="S"."OBJ#")
       filter("S"."OBJ#"="ST"."SYN_ID")
 185 - access("C"."TABLE_OWNER"="BO"."NAME")
 187 - access("C"."TABLE_OWNER"="IU"."NAME")
 189 - access("BO"."USER#"="BASE"."OWNER#" AND "C"."TABLE_NAME"="BASE"."NAME")
 191 - access("IC"."BO#"="BASE"."OBJ#")
 193 - access("IC"."OBJ#"="IDX"."OBJ#")
 194 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
 195 - access("IDX"."OBJ#"="I"."OBJ#")
 196 - filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2
              "))
 197 - filter("C"."INTCOL#"="AC"."INTCOL#")
 199 - access("IO"."USER#"="IDX"."OWNER#")
 201 - access("U"."NAME"="IO"."NAME")
 202 - filter(BITAND("O"."FLAGS",128)=0)
 203 - access("U"."USER#"="O"."OWNER#" AND "O"."NAME"="IDX"."NAME")
 204 - filter((("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND BITAND("I"."FLAGS",4096)=0))
 205 - access("O"."OBJ#"="I"."OBJ#")
 207 - access("I"."INDMETHOD#"="ITO"."OBJ#")
 208 - filter(("IO"."TYPE#"=2 AND "IO"."OWNER#"="IU"."USER#" AND
              "C"."TABLE_NAME"="IO"."NAME"))
 209 - access("I"."BO#"="IO"."OBJ#")
 211 - access("ITO"."OWNER#"="ITU"."USER#")
 213 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#")
 215 - access("I"."TS#"="TS"."TS#")
 218 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
 219 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))
 222 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
 223 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR
              (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR (-"KZSPRPRV")=(-50))))

Note
-----
   - rule based optimizer used (consider using cbo) 

So, where does this SQL statement come from?  If memory serves correctly, this SQL statement is submitted by the Oracle 10.2 ODBC connector.  Oracle Database 11.2 comes up with a slightly different execution plan for the same SQL statement, but I do not know if the 11.2 ODBC connector ever submits the same SQL statement.  The execution plan from Oracle Database 11.2.0.2 for the above SQL statement (STATISTICS_LEVEL = ALL):

Plan hash value: 558250693

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                  | Name               | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                           |                    |      1 |      1 |00:00:00.15 |   29866 |       |       |          |
|   1 |  SORT UNIQUE                                               |                    |      1 |      1 |00:00:00.15 |   29866 |  2048 |  2048 | 2048  (0)|
|   2 |   UNION-ALL                                                |                    |      1 |      1 |00:00:00.15 |   29866 |       |       |          |
|*  3 |    FILTER                                                  |                    |      1 |      1 |00:00:00.02 |     943 |       |       |          |
|   4 |     NESTED LOOPS OUTER                                     |                    |      1 |      1 |00:00:00.02 |     943 |       |       |          |
|   5 |      NESTED LOOPS OUTER                                    |                    |      1 |      1 |00:00:00.02 |     943 |       |       |          |
|   6 |       NESTED LOOPS OUTER                                   |                    |      1 |      1 |00:00:00.02 |     941 |       |       |          |
|   7 |        NESTED LOOPS                                        |                    |      1 |      1 |00:00:00.02 |     939 |       |       |          |
|   8 |         NESTED LOOPS                                       |                    |      1 |      1 |00:00:00.02 |     937 |       |       |          |
|   9 |          NESTED LOOPS OUTER                                |                    |      1 |      1 |00:00:00.02 |     935 |       |       |          |
|  10 |           NESTED LOOPS                                     |                    |      1 |      1 |00:00:00.02 |     932 |       |       |          |
|  11 |            NESTED LOOPS                                    |                    |      1 |      2 |00:00:00.02 |     925 |       |       |          |
|  12 |             MERGE JOIN                                     |                    |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|* 13 |              FIXED TABLE FULL                              | X$KSPPI            |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|* 14 |              SORT JOIN                                     |                    |      1 |      1 |00:00:00.01 |       0 | 59392 | 59392 |53248  (0)|
|  15 |               FIXED TABLE FULL                             | X$KSPPCV           |      1 |   2649 |00:00:00.01 |       0 |       |       |          |
|* 16 |             TABLE ACCESS FULL                              | OBJ$               |      1 |      2 |00:00:00.01 |     925 |       |       |          |
|* 17 |            TABLE ACCESS CLUSTER                            | TAB$               |      2 |      1 |00:00:00.01 |       7 |       |       |          |
|* 18 |             INDEX UNIQUE SCAN                              | I_OBJ#             |      2 |      1 |00:00:00.01 |       4 |       |       |          |
|  19 |           TABLE ACCESS CLUSTER                             | SEG$               |      1 |      1 |00:00:00.01 |       3 |       |       |          |
|* 20 |            INDEX UNIQUE SCAN                               | I_FILE#_BLOCK#     |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  21 |          TABLE ACCESS CLUSTER                              | TS$                |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 22 |           INDEX UNIQUE SCAN                                | I_TS#              |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|  23 |         TABLE ACCESS CLUSTER                               | USER$              |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 24 |          INDEX UNIQUE SCAN                                 | I_USER#            |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 25 |        INDEX RANGE SCAN                                    | I_OBJ1             |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|  26 |       TABLE ACCESS CLUSTER                                 | USER$              |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 27 |        INDEX UNIQUE SCAN                                   | I_USER#            |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 28 |      INDEX RANGE SCAN                                      | I_OBJ1             |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  29 |     NESTED LOOPS                                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  30 |      FIXED TABLE FULL                                      | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 31 |      INDEX RANGE SCAN                                      | I_OBJAUTH2         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 32 |     FIXED TABLE FULL                                       | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  33 |    NESTED LOOPS OUTER                                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  34 |     TABLE ACCESS BY INDEX ROWID                            | COL$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 35 |      INDEX UNIQUE SCAN                                     | I_COL3             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 36 |     TABLE ACCESS CLUSTER                                   | ATTRCOL$           |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 37 |    FILTER                                                  |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  38 |     NESTED LOOPS                                           |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  39 |      NESTED LOOPS                                          |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  40 |       NESTED LOOPS OUTER                                   |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  41 |        NESTED LOOPS                                        |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  42 |         NESTED LOOPS                                       |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  43 |          NESTED LOOPS                                      |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  44 |           NESTED LOOPS                                     |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  45 |            NESTED LOOPS                                    |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  46 |             NESTED LOOPS                                   |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  47 |              NESTED LOOPS OUTER                            |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  48 |               NESTED LOOPS OUTER                           |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  49 |                NESTED LOOPS OUTER                          |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  50 |                 NESTED LOOPS OUTER                         |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  51 |                  NESTED LOOPS                              |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  52 |                   NESTED LOOPS                             |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
|  53 |                    TABLE ACCESS BY INDEX ROWID             | USER$              |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|* 54 |                     INDEX UNIQUE SCAN                      | I_USER1            |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|* 55 |                    INDEX RANGE SCAN                        | I_OBJ2             |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|* 56 |                   TABLE ACCESS CLUSTER                     | IND$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 57 |                    INDEX UNIQUE SCAN                       | I_OBJ#             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  58 |                  TABLE ACCESS CLUSTER                      | SEG$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 59 |                   INDEX UNIQUE SCAN                        | I_FILE#_BLOCK#     |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  60 |                 TABLE ACCESS CLUSTER                       | TS$                |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 61 |                  INDEX UNIQUE SCAN                         | I_TS#              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 62 |                INDEX RANGE SCAN                            | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  63 |               TABLE ACCESS CLUSTER                         | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 64 |                INDEX UNIQUE SCAN                           | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 65 |              TABLE ACCESS BY INDEX ROWID                   | OBJ$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 66 |               INDEX RANGE SCAN                             | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  67 |             TABLE ACCESS CLUSTER                           | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 68 |              INDEX UNIQUE SCAN                             | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 69 |            INDEX RANGE SCAN                                | I_OBJ2             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 70 |           TABLE ACCESS BY INDEX ROWID                      | IND$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 71 |            INDEX UNIQUE SCAN                               | I_IND1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  72 |          TABLE ACCESS BY INDEX ROWID                       | ICOL$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 73 |           INDEX RANGE SCAN                                 | I_ICOL1            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 74 |         TABLE ACCESS CLUSTER                               | COL$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 75 |        TABLE ACCESS CLUSTER                                | ATTRCOL$           |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 76 |       INDEX RANGE SCAN                                     | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  77 |      TABLE ACCESS CLUSTER                                  | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 78 |       INDEX UNIQUE SCAN                                    | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  79 |     NESTED LOOPS                                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  80 |      FIXED TABLE FULL                                      | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 81 |      INDEX RANGE SCAN                                      | I_OBJAUTH2         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 82 |     FIXED TABLE FULL                                       | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  83 |     NESTED LOOPS                                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  84 |      FIXED TABLE FULL                                      | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 85 |      INDEX RANGE SCAN                                      | I_OBJAUTH2         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 86 |     FIXED TABLE FULL                                       | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  87 |    NESTED LOOPS OUTER                                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|  88 |     TABLE ACCESS BY INDEX ROWID                            | COL$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 89 |      INDEX UNIQUE SCAN                                     | I_COL3             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 90 |     TABLE ACCESS CLUSTER                                   | ATTRCOL$           |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|* 91 |    FILTER                                                  |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  92 |     NESTED LOOPS OUTER                                     |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  93 |      NESTED LOOPS OUTER                                    |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  94 |       NESTED LOOPS                                         |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  95 |        NESTED LOOPS                                        |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  96 |         NESTED LOOPS                                       |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  97 |          NESTED LOOPS                                      |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  98 |           NESTED LOOPS OUTER                               |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|  99 |            NESTED LOOPS OUTER                              |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 100 |             NESTED LOOPS                                   |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 101 |              NESTED LOOPS                                  |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 102 |               NESTED LOOPS OUTER                           |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 103 |                NESTED LOOPS                                |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 104 |                 NESTED LOOPS                               |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 105 |                  NESTED LOOPS                              |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 106 |                   NESTED LOOPS                             |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 107 |                    VIEW                                    | ALL_SYNONYMS       |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
| 108 |                     SORT UNIQUE                            |                    |      1 |      0 |00:00:00.13 |   28918 |  1024 |  1024 |          |
| 109 |                      UNION-ALL                             |                    |      1 |      0 |00:00:00.13 |   28918 |       |       |          |
|*110 |                       FILTER                               |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
| 111 |                        NESTED LOOPS                        |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
| 112 |                         NESTED LOOPS                       |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
| 113 |                          NESTED LOOPS                      |                    |      1 |      0 |00:00:00.01 |       5 |       |       |          |
| 114 |                           TABLE ACCESS BY INDEX ROWID      | USER$              |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*115 |                            INDEX UNIQUE SCAN               | I_USER1            |      1 |      1 |00:00:00.01 |       1 |       |       |          |
|*116 |                           INDEX RANGE SCAN                 | I_OBJ5             |      1 |      0 |00:00:00.01 |       3 |       |       |          |
| 117 |                          TABLE ACCESS BY INDEX ROWID       | SYN$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*118 |                           INDEX UNIQUE SCAN                | I_SYN1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 119 |                         TABLE ACCESS CLUSTER               | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*120 |                          INDEX UNIQUE SCAN                 | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*121 |                        FIXED TABLE FULL                    | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*122 |                        FILTER                              |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*123 |                         FILTER                             |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 124 |                          NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 125 |                           NESTED LOOPS                     |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 126 |                            NESTED LOOPS                    |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 127 |                             TABLE ACCESS BY INDEX ROWID    | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*128 |                              INDEX UNIQUE SCAN             | I_USER1            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*129 |                             INDEX RANGE SCAN               | I_OBJ5             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 130 |                            TABLE ACCESS CLUSTER            | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*131 |                             INDEX UNIQUE SCAN              | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*132 |                           INDEX RANGE SCAN                 | I_OBJAUTH1         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*133 |                         FIXED TABLE FULL                   | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 134 |                         NESTED LOOPS                       |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*135 |                          INDEX RANGE SCAN                  | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*136 |                          TABLE ACCESS CLUSTER              | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*137 |                           INDEX UNIQUE SCAN                | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 138 |                        NESTED LOOPS                        |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*139 |                         INDEX RANGE SCAN                   | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*140 |                         TABLE ACCESS CLUSTER               | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*141 |                          INDEX UNIQUE SCAN                 | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*142 |                       VIEW                                 | _ALL_SYNONYMS_TREE |      1 |      0 |00:00:00.13 |   28913 |       |       |          |
|*143 |                        CONNECT BY WITH FILTERING           |                    |      1 |     34 |00:00:00.13 |   28913 |  6144 |  6144 | 6144  (0)|
|*144 |                         FILTER                             |                    |      1 |     34 |00:00:00.13 |   28820 |       |       |          |
| 145 |                          NESTED LOOPS                      |                    |      1 |     34 |00:00:00.13 |   28747 |       |       |          |
| 146 |                           NESTED LOOPS                     |                    |      1 |     34 |00:00:00.13 |   28709 |       |       |          |
| 147 |                            NESTED LOOPS                    |                    |      1 |     34 |00:00:00.13 |   28671 |       |       |          |
| 148 |                             NESTED LOOPS                   |                    |      1 |     34 |00:00:00.13 |   28662 |       |       |          |
| 149 |                              NESTED LOOPS                  |                    |      1 |  27736 |00:00:00.09 |   28611 |       |       |          |
| 150 |                               NESTED LOOPS                 |                    |      1 |  27736 |00:00:00.02 |     871 |       |       |          |
| 151 |                                TABLE ACCESS FULL           | USER$              |      1 |     72 |00:00:00.01 |       6 |       |       |          |
|*152 |                                INDEX RANGE SCAN            | I_OBJ5             |     72 |  27736 |00:00:00.01 |     865 |       |       |          |
| 153 |                               TABLE ACCESS CLUSTER         | USER$              |  27736 |  27736 |00:00:00.07 |   27740 |       |       |          |
|*154 |                                INDEX UNIQUE SCAN           | I_USER#            |  27736 |  27736 |00:00:00.02 |       4 |       |       |          |
| 155 |                              TABLE ACCESS BY INDEX ROWID   | SYN$               |  27736 |     34 |00:00:00.03 |      51 |       |       |          |
|*156 |                               INDEX RANGE SCAN             | I_SYN2             |  27736 |     34 |00:00:00.02 |      50 |       |       |          |
| 157 |                             TABLE ACCESS BY INDEX ROWID    | OBJ$               |     34 |     34 |00:00:00.01 |       9 |       |       |          |
|*158 |                              INDEX RANGE SCAN              | I_OBJ1             |     34 |     34 |00:00:00.01 |       8 |       |       |          |
| 159 |                            TABLE ACCESS CLUSTER            | USER$              |     34 |     34 |00:00:00.01 |      38 |       |       |          |
|*160 |                             INDEX UNIQUE SCAN              | I_USER#            |     34 |     34 |00:00:00.01 |       4 |       |       |          |
| 161 |                           TABLE ACCESS CLUSTER             | USER$              |     34 |     34 |00:00:00.01 |      38 |       |       |          |
|*162 |                            INDEX UNIQUE SCAN               | I_USER#            |     34 |     34 |00:00:00.01 |       4 |       |       |          |
|*163 |                          FILTER                            |                    |     34 |     34 |00:00:00.01 |      73 |       |       |          |
| 164 |                           TABLE ACCESS BY INDEX ROWID      | SYN$               |     34 |     34 |00:00:00.01 |      73 |       |       |          |
|*165 |                            INDEX UNIQUE SCAN               | I_SYN1             |     34 |     34 |00:00:00.01 |      39 |       |       |          |
|*166 |                           FIXED TABLE FULL                 | X$KZSPR            |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|*167 |                           FILTER                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*168 |                            FILTER                          |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 169 |                             NESTED LOOPS                   |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 170 |                              NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 171 |                               NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 172 |                                TABLE ACCESS BY INDEX ROWID | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*173 |                                 INDEX UNIQUE SCAN          | I_USER1            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*174 |                                INDEX RANGE SCAN            | I_OBJ5             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 175 |                               TABLE ACCESS CLUSTER         | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*176 |                                INDEX UNIQUE SCAN           | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*177 |                              INDEX RANGE SCAN              | I_OBJAUTH1         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*178 |                            FIXED TABLE FULL                | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 179 |                            NESTED LOOPS                    |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*180 |                             INDEX RANGE SCAN               | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*181 |                             TABLE ACCESS CLUSTER           | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*182 |                              INDEX UNIQUE SCAN             | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 183 |                          NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*184 |                           INDEX RANGE SCAN                 | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*185 |                           TABLE ACCESS CLUSTER             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*186 |                            INDEX UNIQUE SCAN               | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 187 |                          NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*188 |                           INDEX RANGE SCAN                 | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*189 |                           TABLE ACCESS CLUSTER             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*190 |                            INDEX UNIQUE SCAN               | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*191 |                         FILTER                             |                    |      1 |      0 |00:00:00.01 |      93 |       |       |          |
| 192 |                          NESTED LOOPS                      |                    |      1 |      0 |00:00:00.01 |      93 |       |       |          |
| 193 |                           NESTED LOOPS                     |                    |      1 |      0 |00:00:00.01 |      93 |       |       |          |
| 194 |                            NESTED LOOPS                    |                    |      1 |      0 |00:00:00.01 |      93 |       |       |          |
| 195 |                             NESTED LOOPS                   |                    |      1 |      0 |00:00:00.01 |      93 |       |       |          |
| 196 |                              NESTED LOOPS                  |                    |      1 |     34 |00:00:00.01 |      85 |       |       |          |
| 197 |                               NESTED LOOPS                 |                    |      1 |     34 |00:00:00.01 |      47 |       |       |          |
| 198 |                                NESTED LOOPS                |                    |      1 |     34 |00:00:00.01 |       9 |       |       |          |
| 199 |                                 CONNECT BY PUMP            |                    |      1 |     34 |00:00:00.01 |       0 |       |       |          |
| 200 |                                 TABLE ACCESS BY INDEX ROWID| OBJ$               |     34 |     34 |00:00:00.01 |       9 |       |       |          |
|*201 |                                  INDEX RANGE SCAN          | I_OBJ1             |     34 |     34 |00:00:00.01 |       8 |       |       |          |
| 202 |                                TABLE ACCESS CLUSTER        | USER$              |     34 |     34 |00:00:00.01 |      38 |       |       |          |
|*203 |                                 INDEX UNIQUE SCAN          | I_USER#            |     34 |     34 |00:00:00.01 |       4 |       |       |          |
| 204 |                               TABLE ACCESS CLUSTER         | USER$              |     34 |     34 |00:00:00.01 |      38 |       |       |          |
|*205 |                                INDEX UNIQUE SCAN           | I_USER#            |     34 |     34 |00:00:00.01 |       4 |       |       |          |
| 206 |                              TABLE ACCESS BY INDEX ROWID   | SYN$               |     34 |      0 |00:00:00.01 |       8 |       |       |          |
|*207 |                               INDEX RANGE SCAN             | I_SYN2             |     34 |      0 |00:00:00.01 |       8 |       |       |          |
| 208 |                             TABLE ACCESS BY INDEX ROWID    | OBJ$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*209 |                              INDEX RANGE SCAN              | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 210 |                            TABLE ACCESS CLUSTER            | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*211 |                             INDEX UNIQUE SCAN              | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 212 |                           TABLE ACCESS CLUSTER             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*213 |                            INDEX UNIQUE SCAN               | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 214 |                          NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*215 |                           INDEX RANGE SCAN                 | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*216 |                           TABLE ACCESS CLUSTER             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*217 |                            INDEX UNIQUE SCAN               | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 218 |                          NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*219 |                           INDEX RANGE SCAN                 | I_OBJ4             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*220 |                           TABLE ACCESS CLUSTER             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*221 |                            INDEX UNIQUE SCAN               | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 222 |                    TABLE ACCESS BY INDEX ROWID             | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*223 |                     INDEX UNIQUE SCAN                      | I_USER1            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*224 |                   INDEX RANGE SCAN                         | I_OBJ2             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 225 |                  TABLE ACCESS CLUSTER                      | ICOL$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*226 |                   INDEX UNIQUE SCAN                        | I_OBJ#             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 227 |                 TABLE ACCESS CLUSTER                       | COL$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*228 |                TABLE ACCESS CLUSTER                        | ATTRCOL$           |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*229 |               INDEX RANGE SCAN                             | I_OBJ2             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*230 |              TABLE ACCESS CLUSTER                          | IND$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*231 |               INDEX UNIQUE SCAN                            | I_OBJ#             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 232 |             TABLE ACCESS CLUSTER                           | SEG$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*233 |              INDEX UNIQUE SCAN                             | I_FILE#_BLOCK#     |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 234 |            TABLE ACCESS CLUSTER                            | TS$                |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*235 |             INDEX UNIQUE SCAN                              | I_TS#              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 236 |           TABLE ACCESS BY INDEX ROWID                      | OBJ$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*237 |            INDEX RANGE SCAN                                | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*238 |          TABLE ACCESS BY INDEX ROWID                       | IND$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*239 |           INDEX UNIQUE SCAN                                | I_IND1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 240 |         TABLE ACCESS CLUSTER                               | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*241 |          INDEX UNIQUE SCAN                                 | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*242 |        TABLE ACCESS BY INDEX ROWID                         | OBJ$               |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*243 |         INDEX RANGE SCAN                                   | I_OBJ2             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*244 |       INDEX RANGE SCAN                                     | I_OBJ1             |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 245 |      TABLE ACCESS CLUSTER                                  | USER$              |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*246 |       INDEX UNIQUE SCAN                                    | I_USER#            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 247 |     NESTED LOOPS                                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 248 |      FIXED TABLE FULL                                      | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*249 |      INDEX RANGE SCAN                                      | I_OBJAUTH2         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*250 |     FIXED TABLE FULL                                       | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 251 |     NESTED LOOPS                                           |                    |      0 |      0 |00:00:00.01 |       0 |       |       |          |
| 252 |      FIXED TABLE FULL                                      | X$KZSRO            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*253 |      INDEX RANGE SCAN                                      | I_OBJAUTH2         |      0 |      0 |00:00:00.01 |       0 |       |       |          |
|*254 |     FIXED TABLE FULL                                       | X$KZSPR            |      0 |      0 |00:00:00.01 |       0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("O"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL))
  13 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled')
  14 - access("KSPPI"."INDX"="KSPPCV"."INDX")
       filter("KSPPI"."INDX"="KSPPCV"."INDX")
  16 - filter((BITAND("O"."FLAGS",128)=0 AND "O"."NAME"='PART'))
  17 - filter(BITAND("T"."PROPERTY",1)=0)
  18 - access("O"."OBJ#"="T"."OBJ#")
  20 - access("T"."TS#"="S"."TS#" AND "T"."FILE#"="S"."FILE#" AND "T"."BLOCK#"="S"."BLOCK#")
  22 - access("T"."TS#"="TS"."TS#")
  24 - access("O"."OWNER#"="U"."USER#")
  25 - access("T"."DATAOBJ#"="CX"."OBJ#")
  27 - access("CX"."OWNER#"="CU"."USER#")
  28 - access("T"."BOBJ#"="CO"."OBJ#")
  31 - access("GRANTEE#"="KZSROROL" AND "OA"."OBJ#"=:B1)
  32 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
  35 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2-1)
  36 - filter("TC"."INTCOL#"="AC"."INTCOL#")
  37 - filter((("IDX"."OWNER#"=USERENV('SCHEMAID') OR "BASE"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL) AND
              ("IO"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL)))
  54 - access("IU"."NAME"='CUSTAPP')
  55 - access("IO"."OWNER#"="IU"."USER#" AND "IO"."NAME"='PART' AND "IO"."TYPE#"=2)
       filter("IO"."TYPE#"=2)
  56 - filter((("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND
              BITAND("I"."FLAGS",4096)=0))
  57 - access("I"."BO#"="IO"."OBJ#")
  59 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#")
  61 - access("I"."TS#"="TS"."TS#")
  62 - access("I"."INDMETHOD#"="ITO"."OBJ#")
  64 - access("ITO"."OWNER#"="ITU"."USER#")
  65 - filter(BITAND("O"."FLAGS",128)=0)
  66 - access("O"."OBJ#"="I"."OBJ#")
  68 - access("U"."USER#"="O"."OWNER#")
  69 - access("USER#"="IDX"."OWNER#" AND "O"."NAME"="IDX"."NAME")
  70 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))
  71 - access("IDX"."OBJ#"="I"."OBJ#")
  73 - access("IC"."OBJ#"="IDX"."OBJ#")
  74 - filter("C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2"))
  75 - filter("C"."INTCOL#"="AC"."INTCOL#")
  76 - access("IC"."BO#"="BASE"."OBJ#")
  78 - access("BO"."USER#"="BASE"."OWNER#")
  81 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
  82 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
  85 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
  86 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
  89 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2-1)
  90 - filter("TC"."INTCOL#"="AC"."INTCOL#")
  91 - filter((("IDX"."OWNER#"=USERENV('SCHEMAID') OR "BASE"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL) AND
              ("IO"."OWNER#"=USERENV('SCHEMAID') OR  IS NOT NULL OR  IS NOT NULL)))
 110 - filter((("O"."SPARE3"=USERENV('SCHEMAID') OR "O"."SPARE3"=1 OR ("S"."NODE" IS NULL AND  IS NOT NULL) OR  IS NOT NULL) AND (("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 (("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR
              "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR
              ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
 115 - access("U"."NAME"='CUSTAPP')
 116 - access("O"."SPARE3"="U"."USER#" AND "O"."NAME"='PART' AND "O"."TYPE#"=5)
       filter("O"."TYPE#"=5)
 118 - access("O"."OBJ#"="S"."OBJ#")
 120 - access("O"."OWNER#"="U"."USER#")
 121 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
 122 - filter((( IS NOT NULL OR "BA"."GRANTOR#"=USERENV('SCHEMAID')) AND (("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR
              "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
 123 - filter(:B1 IS NULL)
 128 - access("BU"."NAME"=:B1)
 129 - access("BU"."USER#"="O"."SPARE3" AND "O"."NAME"=:B1)
 131 - access("O"."OWNER#"="U"."USER#")
 132 - access("BA"."OBJ#"="O"."OBJ#")
 133 - filter("KZSROROL"=:B1)
 135 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 136 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 137 - access("O2"."OWNER#"="U2"."USER#")
 139 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 140 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 141 - access("O2"."OWNER#"="U2"."USER#")
 142 - filter(("ST"."SYN_OWNER"='CUSTAPP' AND "ST"."SYN_NAME"='PART'))
 143 - access("S"."BASE_SYN_ID"=PRIOR NULL)
 144 - filter(( IS NOT NULL AND (("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11
              OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (("U"."TYPE#"<>2 AND
              SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
              OR  IS NOT NULL))) AND (("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11
              OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (("U"."TYPE#"<>2 AND
              SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
              OR  IS NOT NULL)))))
 152 - access("BU"."USER#"="O"."SPARE3" AND "O"."TYPE#"=5)
       filter("O"."TYPE#"=5)
 154 - access("O"."OWNER#"="U"."USER#")
 156 - access("S"."OWNER"="BU"."NAME" AND "S"."NAME"="O"."NAME")
 158 - access("S"."OBJ#"="O"."OBJ#")
 160 - access("O"."SPARE3"="U"."USER#")
 162 - access("O"."OWNER#"="U"."USER#")
 163 - filter((("S"."NODE" IS NULL AND  IS NOT NULL) OR  IS NOT NULL))
 165 - access("S"."OBJ#"=:B1)
 166 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
 167 - filter((( IS NOT NULL OR "BA"."GRANTOR#"=USERENV('SCHEMAID')) AND (("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR
              "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
              (("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND
              "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))) OR  IS NOT NULL)))))
 168 - filter(:B1 IS NULL)
 173 - access("BU"."NAME"=:B1)
 174 - access("BU"."USER#"="O"."SPARE3" AND "O"."NAME"=:B1)
 176 - access("O"."OWNER#"="U"."USER#")
 177 - access("BA"."OBJ#"="O"."OBJ#")
 178 - filter("KZSROROL"=:B1)
 180 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 181 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 182 - access("O2"."OWNER#"="U2"."USER#")
 184 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 185 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 186 - access("O2"."OWNER#"="U2"."USER#")
 188 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 189 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 190 - access("O2"."OWNER#"="U2"."USER#")
 191 - filter(((("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11
              OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (("U"."TYPE#"<>2 AND
              SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
              OR  IS NOT NULL))) AND (("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 (("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11
              OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND (("U"."TYPE#"<>2 AND
              SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE') OR ("U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
              OR  IS NOT NULL)))))
 201 - access("connect$_by$_pump$_076"."prior s.syn_id "="O"."OBJ#" AND "O"."TYPE#"=5)
       filter("O"."TYPE#"=5)
 203 - access("BU"."USER#"="O"."SPARE3")
 205 - access("O"."OWNER#"="U"."USER#")
 207 - access("S"."OWNER"="BU"."NAME" AND "S"."NAME"="O"."NAME")
 209 - access("S"."OBJ#"="O"."OBJ#")
 211 - access("O"."SPARE3"="U"."USER#")
 213 - access("O"."OWNER#"="U"."USER#")
 215 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 216 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 217 - access("O2"."OWNER#"="U2"."USER#")
 219 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
 220 - filter(("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) AND "U2"."TYPE#"=2))
 221 - access("O2"."OWNER#"="U2"."USER#")
 223 - access("C"."TABLE_OWNER"="BO"."NAME")
 224 - access("BO"."USER#"="BASE"."OWNER#" AND "C"."TABLE_NAME"="BASE"."NAME")
 226 - access("IC"."BO#"="BASE"."OBJ#")
 228 - filter("C"."INTCOL#"="AC"."INTCOL#")
 229 - access("IO"."OWNER#"="USER#" AND "C"."TABLE_NAME"="IO"."NAME" AND "IO"."TYPE#"=2)
       filter("IO"."TYPE#"=2)
 230 - filter((("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND
              BITAND("I"."FLAGS",4096)=0))
 231 - access("I"."BO#"="IO"."OBJ#")
 233 - access("I"."TS#"="S"."TS#" AND "I"."FILE#"="S"."FILE#" AND "I"."BLOCK#"="S"."BLOCK#")
 235 - access("I"."TS#"="TS"."TS#")
 237 - access("IC"."OBJ#"="IDX"."OBJ#")
 238 - filter((("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9) AND
              "C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2")))
 239 - access("IDX"."OBJ#"="I"."OBJ#")
 241 - access("USER#"="IDX"."OWNER#")
 242 - filter(BITAND("O"."FLAGS",128)=0)
 243 - access("U"."USER#"="O"."OWNER#" AND "O"."NAME"="IDX"."NAME" AND "O"."OBJ#"="I"."OBJ#")
       filter("O"."OBJ#"="I"."OBJ#")
 244 - access("I"."INDMETHOD#"="ITO"."OBJ#")
 246 - access("ITO"."OWNER#"="ITU"."USER#")
 249 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
 250 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))
 253 - access("GRANTEE#"="KZSROROL" AND "OBJ#"=:B1)
 254 - filter(("INST_ID"=USERENV('INSTANCE') AND ((-"KZSPRPRV")=(-45) OR (-"KZSPRPRV")=(-47) OR (-"KZSPRPRV")=(-48) OR (-"KZSPRPRV")=(-49) OR
              (-"KZSPRPRV")=(-50))))

Note
-----
   - rule based optimizer used (consider using cbo) 

I seem to have forgotten the message of this blog article, was it about rules based optimization?  :-)





Brain Teaser: 10046 Extended SQL Trace Shows “EXEC #435118472:c=15600,e=510″, How is that Possible?

14 04 2011

April 14, 2011

I find interesting details from time to time about Oracle Database.  I am in the process of organizing material for a presentation, and I encountered something interesting when testing Oracle Database 11.2.0.2 with patch 3 applied.  I traced the execution of an application and then processed the 660MB  trace file using a trace file profiler that I created.  A portion of my trace file profiler’s output looks like this:

Statement Depth 0 (Application Code)
Cursor 27   Ver 1   Parse at 170.113677 SQL_ID='aw9mpsx4pyta2'  (TD Prev 25.179488)  Similar Cnt 1
|PARSEs       1|CPU S    0.000000|CLOCK S    0.000491|ROWs        0|PHY RD BLKs         0|CON RD BLKs (Mem)         0|CUR RD BLKs (Mem)         0|SHARED POOL MISs      1|
|EXECs    82848|CPU S   25.849376|CLOCK S   26.046921|ROWs    82848|PHY RD BLKs       560|CON RD BLKs (Mem)    248703|CUR RD BLKs (Mem)     84702|SHARED POOL MISs      1|
|FETCHs       0|CPU S    0.000000|CLOCK S    0.000000|ROWs        0|PHY RD BLKs         0|CON RD BLKs (Mem)         0|CUR RD BLKs (Mem)         0|SHARED POOL MISs      0|
  CPU S 20.68%  CLOCK S 20.47%
|                ++++||                ++++|
UPDATE REQUIREMENT SET REQUIRED_DATE = :1 WHERE WORKORDER_TYPE = :2 AND WORKORDER_BASE_ID = :3 AND WORKORDER_SUB_ID = :4
 AND WORKORDER_LOT_ID = :5 AND WORKORDER_SPLIT_ID = :6 AND OPERATION_SEQ_NO = :7 AND PIECE_NO = :8

       (Rows 0)   UPDATE  REQUIREMENT (cr=3 pr=2 pw=0 time=861 us)
       (Rows 1)    INDEX UNIQUE SCAN SYS_C0025561 (cr=3 pr=2 pw=0 time=754 us cost=2 size=33 card=1

The above output shows that this SQL statement, which was executed 82,848 times, consumed a total of 25.85 seconds of CPU time and 26.05 second for the elapsed time.  This SQL statement accounted for 20.68% of the total CPU consumption found in the trace file, and 20.47% of the total elapsed time found in the trace file.  The execution plan displayed is from the first execution of the SQL statement, which is output by default after the first execution starting with Oracle Database 11.1.  Since this SQL statement consumed twice as much CPU and elapsed time as the second highest consumer, I thought that I would take a look in the raw 10046 trace file to see what is happening.  Among other things, I was attempting to determine if there is a time consuming row-level trigger that is firing on each execution, which then caused another row-level trigger to fire.

Here is a portion of that trace file which shows two executions of the SQL statement:

PARSING IN CURSOR #435118472 len=219 dep=0 uid=70 oct=6 lid=70 tim=150927664059 hv=1230988610 ad='466efdfb8' sqlid='aw9mpsx4pyta2'
UPDATE REQUIREMENT SET REQUIRED_DATE = :1 WHERE WORKORDER_TYPE = :2 AND WORKORDER_BASE_ID = :3 AND WORKORDER_SUB_ID = :4
 AND WORKORDER_LOT_ID = :5 AND WORKORDER_SPLIT_ID = :6 AND OPERATION_SEQ_NO = :7 AND PIECE_NO = :8
END OF STMT
PARSE #435118472:c=0,e=491,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=150927664059
WAIT #435118472: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150927664110
WAIT #435118472: nam='SQL*Net message from client' ela= 364 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=150927664488
...
WAIT #435118472: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927704272
WAIT #435118472: nam='SQL*Net message from client' ela= 284 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927704573
BINDS #435118472:
 Bind#0
  oacdty=96 mxl=32(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=256 off=0
  kxsbbbfp=0532a410  bln=32  avl=16  flg=05
  value="2011-04-20-0:0:0"
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
  kxsbbbfp=0532a430  bln=32  avl=01  flg=01
  value="W"
 Bind#2
  oacdty=96 mxl=32(05) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=64
  kxsbbbfp=0532a450  bln=32  avl=05  flg=01
  value="16750"
 Bind#3
  oacdty=96 mxl=32(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=96
  kxsbbbfp=0532a470  bln=32  avl=03  flg=01
  value="200"
 Bind#4
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=128
  kxsbbbfp=0532a490  bln=32  avl=01  flg=01
  value="1"
 Bind#5
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=160
  kxsbbbfp=0532a4b0  bln=32  avl=01  flg=01
  value="0"
 Bind#6
  oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=192
  kxsbbbfp=0532a4d0  bln=32  avl=02  flg=01
  value="20"
 Bind#7
  oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=224
  kxsbbbfp=0532a4f0  bln=32  avl=02  flg=01
  value="30"
EXEC #435118472:c=0,e=300,p=0,cr=3,cu=1,mis=0,r=1,dep=0,og=1,plh=755874150,tim=150927704902
WAIT #435118472: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927704977
WAIT #435118472: nam='SQL*Net message from client' ela= 177 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927705171
BINDS #435118472:
 Bind#0
  oacdty=96 mxl=32(16) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=256 off=0
  kxsbbbfp=0532a410  bln=32  avl=16  flg=05
  value="2011-04-20-0:0:0"
 Bind#1
  oacdty=96 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=32
  kxsbbbfp=0532a430  bln=32  avl=01  flg=01
  value="W"
...
 Bind#7
  oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=178 siz=0 off=224
  kxsbbbfp=0532a4f0  bln=32  avl=02  flg=01
  value="40"
EXEC #435118472:c=15600,e=510,p=0,cr=3,cu=1,mis=0,r=1,dep=0,og=1,plh=755874150,tim=150927705707
WAIT #435118472: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927705800
WAIT #435118472: nam='SQL*Net message from client' ela= 1217 driver id=1413697536 #bytes=1 p3=0 obj#=81989 tim=150927707038 

Looking at the above, you may see a couple of odd items.  First, the cursor number is not 27 as was indicated in my trace file profiler’s output – it is instead the huge number 435,118,472.  Tanel Poder provided an explanation of this change which was introduced in Oracle Database 11.2.0.2 here – that change initially caused a lot of problems for my trace file profiler which in hindsight made the silly assumption that no session would have more than 5,000 or 10,000 open cursors (which would thus result in cursors numbers below 10,000).

What I thought that the casual observer might find interesting is this line in the trace file:

EXEC #435118472:c=15600,e=510,p=0,cr=3,cu=1,mis=0,r=1,dep=0,og=1,plh=755874150,tim=150927705707

The above line indicates that the instance consumed 0.015600 seconds of CPU time in a 0.000510 second time period – a bit of an unusual feat to see the CPU consumption 30 times greater than the elapsed time considering that this SQL statement was not executed in parallel.  Is there a clue why this happened?  Take a look at the trace file line that shows the previous execution:

EXEC #435118472:c=0,e=300,p=0,cr=3,cu=1,mis=0,r=1,dep=0,og=1,plh=755874150,tim=150927704902 

This time the elapsed time is 300 / 0 times longer than the CPU time.  So now the brain teaser: if the CPU time is 30 times (15600 / 510) greater than the elapsed time, and then the elapsed time is infinitely (300 / 0) longer than the CPU time, how is it possible that the overall CPU time for this SQL statement is roughly the same as the overall elapsed time?  (On a side note, you might wonder if this could compound into odd time reporting errors if different SQL statements are executed in between the executions of this SQL statement.)

Just for fun, here is the TKPROF output for this SQL statement:

SQL ID: aw9mpsx4pyta2 Plan Hash: 755874150

UPDATE REQUIREMENT SET REQUIRED_DATE = :1
WHERE
 WORKORDER_TYPE = :2 AND WORKORDER_BASE_ID = :3 AND WORKORDER_SUB_ID = :4 AND
  WORKORDER_LOT_ID = :5 AND WORKORDER_SPLIT_ID = :6 AND OPERATION_SEQ_NO = :7
  AND PIECE_NO = :8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  82848     25.84      26.04        556     248550      84702       82848
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    82849     25.84      26.04        556     248550      84702       82848

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 70 
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  REQUIREMENT (cr=3 pr=2 pw=0 time=861 us)
         1          1          1   INDEX UNIQUE SCAN SYS_C0025561 (cr=3 pr=2 pw=0 time=754 us cost=2 size=33 card=1)(object id 81989)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   82849        0.00          0.10
  SQL*Net message from client                 82849        0.00         19.33
  db file sequential read                        95        0.00          0.03
  db file scattered read                         95        0.00          0.04 




On the Topic of Technology… 2

11 04 2011

April 11, 2011

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Roughly a year ago I wrote a blog article that described the process that I went through, wandering around a big chain electronics store in pursuit of an iPad.  I first wandered past the netbooks, and if that had not happened, I probably would have walked out of the store with an iPad rather than a Toshiba netbook.  The netbook still works reasonably well for those times when I need an ultra-portable full featured computer – however, I more than doubled the value of the netbook by installing 2GB of memory, upgrading Windows 7 Starter to Windows 7 Professional, and installing Microsoft Office 2010.

Well, nearly a year later with the launch of the iPad 2 underway, I again went for a walk through another big chain electronics store in search of an interesting electronics gadget.  I found the iPad 2 display and experimented with one of the two units that was on display.  My first reaction was a remark about the snazzy screen effects when opening applications – an impressive feature.  The iPad 2 reminded me a lot of the iPad that I experimented with a year earlier – it is a media consumption device.  Since I still have a Verizon wireless plan for Internet access, with steep overage charges, an Internet media consumption device would still have limited usefulness for me.  Still no Flash support, so that would limit its usefulness of accessing the Flash based sites (such as the Oracle support site) on the iPad 2.

I wandered around the electronics store a bit more and found another tablet tucked away in the corner with a couple of laptops – a Motorola Xoom with 3G wireless.  My first reaction to the less than snazzy screen effects (compared to the iPad 2) was simply – is that all there is?  I experimented with the tablet for a couple of minutes.  Neat satellite view of the store on the little tablet, and wow is it fast updating the screen.  Moderately neat book application.  Sweeping my finger across the screen… that’s kind of neat – I can’t do that on the netbook (the netbook and Xoom have similar screen dimensions and aspect ratio).  Viewing web pages seems to work, even if the websites deliver the mobile versions of the web pages.  Interesting, or as useless as the iPad 2 for what I would do with it?  I left the store empty handed.

I did a bit more research on the Motorola Xoom tablet.  It seems that not only does the Xoom have a 5 megapixel camera on its backside that records 720p video, but it also has a high resolution forward facing camera.  Oh, it supports Flash also, and is apparently the particular tablet model that Google used during devlopment of the new Android 3 (Honeycomb) operating system.  The information that I had found on the Internet suggested that the Xoom was less of a media consumption device than an iPad 2, and more of a content producer device.  Interesting, but can it run Oracle Database?

Considering that flyers and articles about various tablets (iPad, Zoom, BlackBerry, Dell, etc.)  were stacking up on my desk at work, I thought that I would try an experiment:

Zooming in on the center of the above picture, we see that the screen on the Xoom is the My Oracle Support Flash-based site.  As of yet I have not determined how to display the on-screen keyboard on demand, so I can’t yet type into the text entry fields:

Flash works, and I am able to see the non-mobile, non-limited versions of websites.  So, what is the killer application for the Xoom?  Well, it is hard to beat a talking cat for $1.00:

So, the question again, can it run Oracle?  Well, I think that this picture answers that question (click the piture to zoom in on the guy who is waving from the boat):

If it were not for the crashing applications including the Android Apps/Books Market and SpeedView (uses the Xoom’s built-in GPS to display various vehicle speed statistics), the device would be nearly perfect as a  productivity tool that also is able to consume media (the TuneIn Radio application is quite handy, and with an HDMI cable plugged into a 7.1 receiver, it sounds very good).  The QuickOffice application, at $15, is certainly less expensive than Office 2010 that is installed on the netbook; the Power Point document editor in QuickOffice seems to have difficulty with complexly formatted Power Point files, but it works OK as a viewer (as long as there is no animation).  The PDF viewer that is part of QuickOffice works, but it is not great.  No problem, just a couple more dollars for the ezPDF reader and I am able to view various PDF books and the Oracle documentation library.  Attaching a $12 HDMI cable will even allow the screen to be quickly projected onto the TV, while attaching the included USB cable allows organizing the various files loaded onto the Xoom into folders and to easily rename those files.

So, is it worth $600?  If you need a high definition video camera, a 5 mega-pixel camera, a GPS, easy/fast speech recognition, inexpensive applications, the ability to view just about any web page in a light-weight, mobile package, then the Xoom just might be worth $600.  If you have no use for those features, then the Xoom will make an excellent paperweight to keep the tablet flyers and news articles from blowing off the desktop.





Using ROWNUM in the Where Clause Causes Problems

4 04 2011

April 4, 2011

A couple of years ago a very well written article appeared in Oracle Magazine that described how to use ROWNUM in the WHERE clause, and also why using ROWNUM might not work quite as expected.

Let’s assume that we want to do something a bit unusual, as described in this forum thread.  We set up the tables for the test case to see what is happening:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  TRUNC(SYSDATE+ROWNUM) C2,
  LPAD('A',15,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=10000
ORDER BY
  DBMS_RANDOM.VALUE;

CREATE TABLE T2 AS
SELECT
  ROWNUM C1,
  TRUNC(SYSDATE+ROWNUM) C2,
  LPAD('A',15,'A') PADDING
FROM
  DUAL
CONNECT BY
  LEVEL<=10000
ORDER BY
  DBMS_RANDOM.VALUE;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2') 

In the above, 10,000 rows were inserted into the T1 and T2 tables in random order, as the tables were created.  Trying a basic query, if we execute the following query, we should see six randomly selected rows:

SELECT
  *
FROM
  T1
WHERE
  ROWNUM IN (6,5,4,3,2,1);

  C1 C2        PADDING
---- --------- ---------------
9546 23-MAY-37 AAAAAAAAAAAAAAA
4894 27-AUG-24 AAAAAAAAAAAAAAA
1106 14-APR-14 AAAAAAAAAAAAAAA
1144 22-MAY-14 AAAAAAAAAAAAAAA
2281 02-JUL-17 AAAAAAAAAAAAAAA
4832 26-JUN-24 AAAAAAAAAAAAAAA

6 rows selected. 

And, if we had not read the article that is linked to at the start of this blog article, we might assume that the following would also return six rows:

SELECT
  *
FROM
  T1
WHERE
  ROWNUM IN (7,6,5,4,3,2); 

no rows selected

How about something like the following?  If there were only 10 rows in table T2, we might expect the following to return 10 rows from table T1 (a random selection of rows), or 10,000 rows if both tables T1 and T2 contain 10,000 rows:

SELECT
  *
FROM
  T1
WHERE
  ROWNUM IN (
    SELECT
      ROWNUM RN
    FROM
      T2);

no rows selected 

Interesting (and expected if you read the article that is linked to at the start of this blog article).  Let’s take a look at the execution plan:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T1
WHERE
  ROWNUM IN (
    SELECT
      ROWNUM RN
    FROM
      T2);

Execution Plan
----------------------------------------------------------
Plan hash value: 881956856

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   100M|  2574M|     8   (0)| 00:00:01 |
|   1 |  COUNT                |      |       |       |            |          |
|*  2 |   FILTER              |      |       |       |            |          |
|   3 |    TABLE ACCESS FULL  | T1   | 10000 |   263K|     6   (0)| 00:00:01 |
|*  4 |    FILTER             |      |       |       |            |          |
|   5 |     COUNT             |      |       |       |            |          |
|   6 |      TABLE ACCESS FULL| T2   |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter( EXISTS (???)
   4 - filter(ROWNUM=ROWNUM) 

Interesting, after applying the EXISTS (???) filter (plan ID line 2), the optimizer expects 10,000 rows to become 100,000,000 rows.  Secondly, ff the value of ROWNUM is assigned only after the WHERE clause is evaluated, could the ROWNUM=ROWNUM filter (plan ID line 4) ever be true (and result in a row passing through)?  I suspect that is why this query returns no rows – is there a better answer?

What about this query, which should yield the originally expected results (note that AUTOTRACE was still enabled):

SELECT
  T1.*
FROM
  (SELECT
     T1.*,
     ROWNUM RN
   FROM
     T1) T1,
  (SELECT
     T2.*,
     ROWNUM RN
   FROM
     T2) T2
WHERE
  T1.RN=T2.RN;

Execution Plan
----------------------------------------------------------
Plan hash value: 2458726244

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  1000K|    54M|    17  (30)| 00:00:01 |
|*  1 |  HASH JOIN           |      |  1000K|    54M|    17  (30)| 00:00:01 |
|   2 |   VIEW               |      | 10000 |   126K|     6   (0)| 00:00:01 |
|   3 |    COUNT             |      |       |       |            |          |
|   4 |     TABLE ACCESS FULL| T2   | 10000 |       |     6   (0)| 00:00:01 |
|   5 |   VIEW               |      | 10000 |   429K|     6   (0)| 00:00:01 |
|   6 |    COUNT             |      |       |       |            |          |
|   7 |     TABLE ACCESS FULL| T1   | 10000 |   263K|     6   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."RN"="T2"."RN") 

The optimizer is predicting that 1,000,000 rows will be returned, and the aliased ROWNUM values (“T1″.”RN”=”T2″.”RN”) are being compared.  So, will this query return 0 rows, 10,000 rows, or 1,000,000 rows?  Let’s find out:

SET AUTOTRACE OFF

/

        C1 C2        PADDING                 RN
---------- --------- --------------- ----------
...
      8353 15-FEB-34 AAAAAAAAAAAAAAA       9989
      8566 16-SEP-34 AAAAAAAAAAAAAAA       9990
      8202 17-SEP-33 AAAAAAAAAAAAAAA       9991
      7980 07-FEB-33 AAAAAAAAAAAAAAA       9992
      6708 15-AUG-29 AAAAAAAAAAAAAAA       9993
      5848 08-APR-27 AAAAAAAAAAAAAAA       9994
      9006 30-NOV-35 AAAAAAAAAAAAAAA       9995
      6423 03-NOV-28 AAAAAAAAAAAAAAA       9996
      3272 19-MAR-20 AAAAAAAAAAAAAAA       9997
       921 11-OCT-13 AAAAAAAAAAAAAAA       9998
      7519 04-NOV-31 AAAAAAAAAAAAAAA       9999
      9311 30-SEP-36 AAAAAAAAAAAAAAA      10000

10000 rows selected.

So, from the above, we find that by placing both halves of the query into inline views, and joining the aliased ROWNUM values, the query worked as expected.

Let’s try just placing what had been an IN list sub-query in the original query into an inline view to see what happens:

SELECT
  *
FROM
  T1,
  (SELECT
     T2.*,
     ROWNUM RN
   FROM
     T2) T2
WHERE
  T1.ROWNUM=T2.RN;

SQL> SELECT
  2    *
  3  FROM
  4    T1,
  5    (SELECT
  6       T2.*,
  7       ROWNUM RN
  8     FROM
  9       T2) T2
 10  WHERE
 11    T1.ROWNUM=T2.RN;
  T1.ROWNUM=T2.RN
     *
ERROR at line 11:
ORA-01747: invalid user.table.column, table.column, or column specification 

Needless to say, that did not work.  One final thought: ROWNUM is not a fixed attribute of a specific row in a table – if you attempt to use it as such, you will not achieve the results that you are expecting.








Follow

Get every new post delivered to your Inbox.

Join 140 other followers