Drilling into Session Detail from the Operating System – On the Windows Platform 2

9 01 2010

January 9, 2010

In the earlier post, you saw a script output that looked something like the following:

1/8/2010 12:57:56 PM Processes: 73 Threads: 861 C. Switches: 35972 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.33S Sys  Time: 25.19S Memory: 141.11MB Page File: 0.63MB
  Handle: 1444 User Time: 9.34S Sys  Time: 25.5S ElapsedTime: 70S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 103.9S Sys  Time: 0.13S Memory: 442.42MB Page File: 0.67MB
  Handle: 3520 User Time: 34.13S Sys  Time: 0.02S ElapsedTime: 69S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

  Handle: 2152 User Time: 34.04S Sys  Time: 0S ElapsedTime: 69S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

The above was echoed to the command line window and also written to a logging file.  So, how do we create this output?  Save the following on the root of the C:\ drive as CPUHighLoadProcesses.vbs:

Const adNumeric=131
Const adParamInput=1
Const adCmdText=1
Const adVarChar=200

Dim i                       'Loop counter for iterations
Dim j                       'Loop counter to find the previous stats for the thread
Dim intInstance             'Index to the instance statistics
Dim IntOldCSPerSec          'Previous value for Context Switches Per Second
Dim intOldProcesses         'Previous value for the number of processes
Dim intOldThreads           'Previous value for the number of threads
Dim strSQL                  'SQL to check WMI Win32_PerfRawData_PerfOS_System
Dim strSQL2                 'SQL to find the Oracle processes using WMI Win32_Process
Dim strSQL3                 'SQL to drill into the threads in the Oracle processes using WMI Win32_Thread
Dim strSQL4                 'SQL to find the Oracle session, SQL statement, and execution plan
Dim strOut                  'Data to be written to the text file
Dim strComputer             'Oracle database server name, or . for the current computer
Dim sglUMTime(20)           'User mode time in seconds for the Oracle instance
Dim sglKMTime(20)           'Kernel mode time in seconds for the Oracle instance
Dim sglWorkingSet(20)       'Working set memory size for the Oracle instance
Dim sglPageFileUsage(20)    'Page/swap file usage for the Oracle instance
Dim sglOUMTime(20)          'Previous user mode time in seconds for the Oracle instance
Dim sglOKMTime(20)          'Previous kernel mode time in seconds for the Oracle instance
Dim sglOldWorkingSet(20)    'Previous working set memory size for the Oracle instance
Dim sglOldPageFileUsage(20) 'Previous page/swap file usage for the Oracle instance
Dim objWMIService           'An object used to pass in the WMI calls
Dim colItems                'Collection of processes running on the server
Dim objItem                 'An individual process running on the server
Dim colThreads              'Collection of threads running in a process
Dim objThread               'An individual thread running in a process
Dim intThread               'Index to the previous values for the thread's statistics
Dim intThreadCount(20)      'Maximum previous thread index for the instance
Dim intThreadH(20,600)      'Thread handle
Dim sglTUMTime(20,600)      'User mode time in seconds for the thread
Dim sglTKMTime(20,600)      'Kernel mode time in seconds for the thread
Dim sglTETime(20,600)       'Elapsed time in seconds for the thread
Dim adsFile                 'ADO stream object used to write out the log file
Dim snpData                 'ADO recordset used to query V$SESSION/V$SQL
Dim comData                 'ADO command object used to permit passing in bind variables for the V$SESSION/V$SQL query
Dim snpDataPlan             'ADO recordset used to retrieve the execution plan for the session's SQL statement
dim comDataPlan             'ADO command object used to permit passing in bind variables for the execution plan
Dim dbDatabase              'ADO database connection object
Dim intCheckIterations      'Number of times to check the instances
Dim intDelayIterations      'Number of seconds to delay between iterations
Dim sglThreadBusyPercent    'Percentage of the seconds in the iteration delay does a session need to consume to be examined

Set snpData = CreateObject("ADODB.Recordset")
Set comData = CreateObject("ADODB.Command")
Set snpDataPlan = CreateObject("ADODB.Recordset")
Set comDataPlan = CreateObject("ADODB.Command")
Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

intCheckIterations = 10
intDelayIterations = 60
sglThreadBusyPercent = 0.50  '50%

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open

'Should verify that the connection attempt was successful, but I will leave that for someone else to code
On Error Resume Next  'Allow continuing the script if an error happens

'Prepare the ADO Stream to write the data to the text file
Set adsFile = CreateObject("ADODB.Stream")
adsFile.Type = 2
adsFile.Charset = "iso-8859-1"
adsFile.Open

'Prepare the SQL statements
strSQL = "SELECT * FROM Win32_PerfRawData_PerfOS_System"
strSQL2 = "SELECT * FROM Win32_Process Where Name like 'Oracle%'"
strSQL3 = "SELECT * FROM Win32_Thread Where ProcessHandle="

With comData
  strSQL4 = "SELECT" & VBCrLf
  strSQL4 = strSQL4 & "  P.PID," & VBCrLf
  strSQL4 = strSQL4 & "  P.SPID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SERIAL#," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.USERNAME,' ') USERNAME," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.MACHINE,' ') MACHINE," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(S.PROGRAM,' ') PROGRAM," & VBCrLf
  strSQL4 = strSQL4 & "  S.SQL_ID," & VBCrLf
  strSQL4 = strSQL4 & "  S.SQL_CHILD_NUMBER," & VBCrLf
  strSQL4 = strSQL4 & "  NVL(SQL.SQL_TEXT,' ') SQL_TEXT" & VBCrLf
  strSQL4 = strSQL4 & "FROM" & VBCrLf
  strSQL4 = strSQL4 & "  V$PROCESS P," & VBCrLf
  strSQL4 = strSQL4 & "  V$SESSION S," & VBCrLf
  strSQL4 = strSQL4 & "  V$SQL SQL" & VBCrLf
  strSQL4 = strSQL4 & "WHERE" & VBCrLf
  strSQL4 = strSQL4 & "  P.SPID=?" & VBCrLf
  strSQL4 = strSQL4 & "  AND P.ADDR=S.PADDR" & VBCrLf
  strSQL4 = strSQL4 & "  AND S.SQL_ID = SQL.SQL_ID(+)" & VBCrLf
  strSQL4 = strSQL4 & "  AND S.SQL_CHILD_NUMBER = SQL.CHILD_NUMBER(+)" & VBCrLf
  strSQL4 = strSQL4 & "ORDER BY" & VBCrLf
  strSQL4 = strSQL4 & "  S.USERNAME," & VBCrLf
  strSQL4 = strSQL4 & "  P.PROGRAM"

  .Parameters.Append .CreateParameter("spid", adNumeric, adParamInput, 12, 0)
  .CommandText = strSQL4
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

With comDataPlan
  strSQL4 = "SELECT" & VBCrLf
  strSQL4 = strSQL4 & "  *" & VBCrLf
  strSQL4 = strSQL4 & "FROM" & VBCrLf
  strSQL4 = strSQL4 & "  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(?, ?, 'TYPICAL'))" & VBCrLf

  .Parameters.Append .CreateParameter("sql_id", adVarchar, adParamInput, 20, "")
  .Parameters.Append .CreateParameter("child_number", adNumeric, adParamInput, 12, 0)
  .CommandText = strSQL4
  .CommandType = adCmdText
  .CommandTimeout = 30
  .ActiveConnection = dbDatabase
End With

strComputer = "."  ' the . indicates the local computer
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\CIMV2")

IntOldCSPerSec = 0

For intInstance = 1 to 20
    sglOUMTime(intInstance) = 0
    sglOKMTime(intInstance) = 0
    sglOldWorkingSet(intInstance) = 0
    sglOldPageFileUsage(intInstance) = 0
    intThreadCount(intInstance) = 0
    intThreadH(intInstance,1) = 0
    sglTUMTime(intInstance,1) = 0
    sglTKMTime(intInstance,1) = 0
    sglTETime(intInstance,1) = 0
Next

For i = 1 to intCheckIterations 'Number of iterations
  Set colItems = objWMIService.ExecQuery(strSQL,"WQL",48)

  For Each objItem in colItems
    strOut = Now() & " Processes: " & objItem.Processes
    strOut = strOut & " Threads: " & objItem.Threads
    strOut = strOut & " C. Switches: " & objItem.ContextSwitchesPersec - IntOldCSPerSec
    strOut = strOut & " Q. Length: " & objItem.ProcessorQueueLength

    'Write to screen
    Wscript.Echo strOut

    'Write to log file
    adsFile.WriteText(strOut & vbCrLf)

    IntOldCSPerSec = objItem.ContextSwitchesPersec
    intOldProcesses = objItem.Processes
    intOldThreads = objItem.Threads
  Next

  Set colItems = Nothing
  Set colItems = objWMIService.ExecQuery(strSQL2,"WQL",48)

  intInstance = 0
  For Each objItem in colItems
    intInstance = intInstance + 1
    sglUMTime(intInstance) = Round(objItem.UserModeTime/10000000, 2)
    sglKMTime(intInstance) = Round(objItem.KernelModeTime/10000000, 2)
    sglWorkingSet(intInstance) = Round(objItem.WorkingSetSize/1048576, 2)
    sglPageFileUsage(intInstance) = Round(objItem.PageFileUsage/1048576, 2)

    strOut = "Instance: " & objItem.CommandLine & vbCrLf
    strOut = strOut & " User Time: " & Round(sglUMTime(intInstance) - sglOUMTime(intInstance),2) & "S"
    strOut = strOut & " Sys  Time: " & Round(sglKMTime(intInstance) - sglOKMTime(intInstance),2) & "S"
    strOut = strOut & " Memory: " & sglWorkingSet(intInstance) & "MB"
    strOut = strOut & " Page File: " & sglPageFileUsage(intInstance) & "MB" & vbCrLf

    Set colThreads = Nothing
    Set colThreads = objWMIService.ExecQuery(strSQL3 & cStr(objItem.ProcessID),"WQL",48)

    For Each objThread in colThreads
      'Find the statistics for thread from the previous iteration's statistics
      intThread = intThreadCount(intInstance) + 1
      For j = 1 to intThreadCount(intInstance)
        If intThreadH(intInstance,j) = objThread.Handle Then
          intThread = j
          Exit For
        End If
      Next

      If intThreadH(intInstance,intThread) = 0 Then
        'This is a new thread that was not captured before
        intThreadH(intInstance,intThread) = objThread.Handle
        If intThreadCount(intInstance) < intThread Then
          intThreadCount(intInstance) = intThread
        End If
      End If

      If (cSng(Round(objThread.UserModeTime/1000,2)-sglTUMTime(intInstance,intThread)+ _
          Round(objThread.KernelModeTime/1000,2)-sglTKMTime(intInstance,intThread)) / _
          intDelayIterations) >= sglThreadBusyPercent Then
        strOut = strOut & "  Handle: " & cStr(objThread.Handle)
        strOut = strOut & " User Time: " & Round((Round(objThread.UserModeTime/1000,2)-sglTUMTime(intInstance,intThread)),2) & "S"
        strOut = strOut & " Sys  Time: " & Round((Round(objThread.KernelModeTime/1000,2)-sglTKMTime(intInstance,intThread)),2) & "S"
        strOut = strOut & " ElapsedTime: " & Round((Round(objThread.ElapsedTime/1000,2)-sglTETime(intInstance,intThread)),2) & "S"
        strOut = strOut & " Priority: " & objThread.Priority
        strOut = strOut & " ThreadState:"
        Select Case objThread.ThreadState
          Case 0
            strOut = strOut & " Initialized"
          Case 1
            strOut = strOut & " In Run Queue"
          Case 2
            strOut = strOut & " Running"
          Case 3
            strOut = strOut & " Preparing to Run"
          Case 4
            strOut = strOut & " Terminated"
          Case 5
            strOut = strOut & " Idle"
          Case 6
            strOut = strOut & " Non-CPU Wait Event:"
            'See http://msdn.microsoft.com/en-us/library/aa394494(VS.85).aspx
            Select Case objThread.ThreadWaitReason
              Case 0
                strOut = strOut & "Executive"
              Case 1
                strOut = strOut & "FreePage"
              Case 2
                strOut = strOut & "PageIn"
              Case 3
                strOut = strOut & "PoolAllocation"
              Case 4
                strOut = strOut & "ExecutionDelay"
              Case 5
                strOut = strOut & "FreePage"
              Case 6
                strOut = strOut & "PageIn"
              Case 7
                strOut = strOut & "Executive"
              Case 8
                strOut = strOut & "FreePage"
              Case 9
                strOut = strOut & "PageIn"
              Case 10
                strOut = strOut & "PoolAllocation"
              Case 11
                strOut = strOut & "ExecutionDelay"
              Case 12
                strOut = strOut & "FreePage"
              Case 13
                strOut = strOut & "PageIn"
              Case 14
                strOut = strOut & "EventPairHigh"
              Case 15
                strOut = strOut & "EventPairLow"
              Case 16
                strOut = strOut & "LPCReceive"
              Case 17
                strOut = strOut & "LPCReply"
              Case 18
                strOut = strOut & "VirtualMemory"
              Case 19
                strOut = strOut & "PageOut"
              Case 20
                strOut = strOut & "Unknown"
              Case Else
                strOut = strOut & objThread.ThreadWaitReason
            End Select
          Case 7
            strOut = strOut & " Unknown"
          Case Else
            strOut = strOut & objThread.ThreadState
        End Select       

        strOut = strOut & vbCrLf
        comData("spid") = objThread.Handle
        Set snpData = comData.Execute

        If Not(snpData Is Nothing) Then
          If Not(snpData.EOF) Then
            strOut = strOut & "   PID:" & snpData("pid")
            strOut = strOut & " SPID:" & snpData("spid")
            strOut = strOut & " SID:" & snpData("sid")
            strOut = strOut & " SERIAL#:" & snpData("serial#")
            strOut = strOut & " USERNAME:" & snpData("username")
            strOut = strOut & " MACHINE:" & snpData("machine")
            strOut = strOut & " PROGRAM:" & snpData("program") & vbCrLf
            strOut = strOut & "   " & snpData("sql_text") & vbCrLf

            If Not(IsNull(snpData("sql_id"))) Then
              comDataPlan("sql_id") = snpData("sql_id")
              comDataPlan("child_number") = snpData("sql_child_number")
              Set snpDataPlan = comDataPlan.Execute

              If Not(snpDataPlan Is Nothing) Then
                strOut = strOut & vbCrLf
                Do While Not(snpDataPlan.EOF)
                  strOut = strOut & "    " & snpDataPlan(0) & vbCrLf
                  snpDataPlan.MoveNext
                Loop
                snpDataPlan.Close
              End If
              strOut = strOut & vbCrLf
            End If
          End If
          snpData.Close 
        End If
      End If

      sglTUMTime(intInstance,intThread) = Round(objThread.UserModeTime/1000,2)
      sglTKMTime(intInstance,intThread) = Round(objThread.KernelModeTime/1000,2)
      sglTETime(intInstance,intThread) = Round(objThread.ElapsedTime/1000,2)
    Next
    strOut = strOut & vbCrLf

    'Write to screen
    Wscript.Echo strOut

    'Write to log file
    adsFile.WriteText(strOut & vbCrLf)

    sglOUMTime(intInstance) = Round(objItem.UserModeTime/10000000, 2)
    sglOKMTime(intInstance) = Round(objItem.KernelModeTime/10000000, 2)
    sglOldWorkingSet(intInstance) = Round(objItem.WorkingSetSize/1048576, 2)
    sglOldPageFileUsage(intInstance) = Round(objItem.PageFileUsage/1048576, 2)
  Next

  'Wait intDelayIterations seconds before sampling again
  Wscript.Sleep intDelayIterations * 1000
Next

adsFile.SaveToFile "C:\CPUHighLoadProcesses.txt", 2 
adsFile.Close

dbDatabase.Close

Set snpData = Nothing
Set comData = Nothing
Set snpDataPlan = Nothing
Set comDataPlan = Nothing
Set dbDatabase = Nothing
Set adsFile = Nothing

If you decide to use the above script, test it very carefully.  There may be one or two typos or logic errors.  If you have administrator rights on the server, the script can be executed remotely (it does not need to be run directly on the server).  WMI queries are given a very low priority, so if the CPU run queue is long, the script may appear to hang for a long time.  To run the script, open a command line window and type:

cscript C:\CPUHighLoadProcesses.vbs

WMI queries are very powerful, as demonstrated by the above script.

Update: 1.5 hours before this blog article is scheduled to appear:

{RANT}

After I wrote the above script and this and the previous blog articles I stumbled upon the following very recent news article that states the following:
dba-oracle.com/t_display_background_processes_windows.htm

“In Windows, the ‘thread’ model is used, and Oracle dispatches his own background tasks within the domain of the single process, oracle.exe.  Hence, you cannot see any background processes from the Windows OS (but you can see listener process and parallel query slaves).”

OK, forget about my script, because the quote directly above states that it is not possible.  Someone please call Oracle Corp. and tell them that their Oracle Administration Assistant for Windows needs to be removed from the documentation because what it shows is simply not possible. Did I mention that I dislike being confused?

{/RANT}





Drilling into Session Detail from the Operating System – On the Windows Platform

9 01 2010

January 9, 2010

(Forward to the Follow-Up Article)

As you might be aware, Oracle running on Unix/Linux uses a process model, while Oracle running on Windows uses a thread model.  With the process model it is fairly easy to use operating system tools, such as ps or top, to monitor the performance of individual database sessions, and then the DBA can use that information to manually drill back into what the high utilization sessions are doing.  Unfortunately, with the thread model, that procedure is impossible.  Unless, of course, you know a trick.

In one of the two chapters of the Expert Oracle Practices book, Randolf and I stated the following:

“In the case of performance problems caused by Oracle-related processes, consider drilling into the Oracle process activity by using the operating system process identifier (PID) to search V$PROCESS for a matching PID, and then join to V$SESSION on V$PROCESS.ADDR=V$SESSION.PADDR.”

I think that we also stated somewhere in the chapters that WMI queries could be used to drill into the session level activity on the Windows platform.  We did not provide a script to demonstrate the process – the chapters were running long by a couple pages (OK more than a couple if you have seen the chapters), so we did not bother to construct a demonstration.  I played with the idea a little, and then decided that the 10 to 20 pages it would take to describe the process could be better used for something else.

So, let’s play a game of bury the poor Windows box (this happens to be a computer with a dual core CPU).  It would be neat, if we have 20 database instances running on this poor Windows box, to play a game of Whose Got My CPU (R).  So, it would be neat if we could send something like this out to a command line window:

1/8/2010 12:47:18 PM Processes: 73 Threads: 880 C. Switches: 10573606 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 668.4S Sys  Time: 295.9S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 663.32S Sys  Time: 296.99S ElapsedTime: 3358S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 2959.84S Sys  Time: 11.73S Memory: 539.5MB Page File: 0.67MB
  Handle: 3520 User Time: 1081.91S Sys  Time: 0.92S ElapsedTime: 3238S Priority: 8 ThreadState: Running

The above states that at 12:47 there were 73 processes running on this poor Windows box with 880 process threads.  There were 10,573,606 context switches over some period of time, and the run queue length is currently 7 (could this be a sign of a problem on a computer with a single dual core CPU – no, can’t be – someone on the Internet said that pushing the CPUs to 100% utilization is desirable, and because it is on the Internet, it must be true).

Next, the above output shows that an Oracle 10.2.0.x database instance was found (OR10).  This instance had consumed 668.4 seconds of CPU time in user mode, and 295.9 seconds of CPU time in system (kernel) mode – that system mode time seems to be high compared to the user mode time.  The instance was using about 276MB of memory. A single thread representing a dedicated connection was created 33,585 seconds earlier (that might be a calculation error – looks like I should have divided that number by 10).  That thread has used 663.32 seconds of user mode CPU time, and 296.99 seconds of system (kernel) mode CPU time.  The thread had a scheduling priority of 8 assigned to it and at the time was sitting in the run queue waiting for the CPU to become available.

Next, the above output shows that an Oracle 11.1.0.x database instance was found (OR11).  This instance consumed 2959.84 seconds of CPU time in user mode, and 11.73 second of CPU time in system (kernel) mode.  The instance was using about 540MB of memory.   A single thread representing a dedicated session was created 32,385 seconds earlier.  That thread had used 1081.91 seconds of user mode CPU time (about 1/3 of the total for the instance) and 0.92 seconds of system (kernel) mode CPU time.  The thread had a scheduling priority of 8 assigned to it and at the time was running on the CPU (something else must have been running on the other CPU core – possibly the script that collected this data).

OK, the above is kind of neat, but what are the sessions doing that caused the CPU usage?  I don’t have the SID and SERIAL# for the sessions, so I can’t enable a 10046 trace.  It will take too long to fire up a GUI of some sort to see what is happening.  I wonder if I can do anything with the handle number that was output?  Well, I read the chapters that Randolf and I wrote, and that Handle just so happens to be treated like the PID on Oracle platforms that use a process model.  So, I could do all kinds of interesting things once I resolve the displayed Handle to a SID.  For instance, I could do something like this:

1/8/2010 12:47:18 PM Processes: 73 Threads: 880 C. Switches: 10573606 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 668.4S Sys  Time: 295.9S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 663.32S Sys  Time: 296.99S ElapsedTime: 3358S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 2959.84S Sys  Time: 11.73S Memory: 539.5MB Page File: 0.67MB
  Handle: 3520 User Time: 1081.91S Sys  Time: 0.92S ElapsedTime: 3238S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

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

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 983.26S Sys  Time: 2.74S ElapsedTime: 3209S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 532.46S Sys  Time: 0.98S ElapsedTime: 1676S Priority: 8 ThreadState: Idle
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe

Neat, for one of the database instances I am able to see the PID and SPID (in case I want to use SQLPLUS’ ORADEBUG), as well as the SID and SERIAL# (in case I want to enable a 10046 trace).  While I only retrieved the SQL statement being executed and its plan, I could have done a lot of other things also to investigate what is happening in the sessions.  I just need to find a way to log into the database to accomplish the above – note that in the above, I only logged into one database in the script. Having the CPU consumed since the thread started might be helpful, but it would be better to know how much was consumed in a particular time period, for instance the last couple minutes.  With a loop in the script, we can accomplish this task.  The script output continues (something is happening in the 10.2.0.x session, compare the user mode time with the system (kernel) mode time for this time period):

1/8/2010 12:48:31 PM Processes: 73 Threads: 866 C. Switches: 53465 Q. Length: 4
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 10.75S Sys  Time: 25.27S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 11.37S Sys  Time: 26.88S ElapsedTime: 78S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 107.79S Sys  Time: 0.16S Memory: 543.09MB Page File: 0.67MB
  Handle: 3520 User Time: 74.24S Sys  Time: 0.02S ElapsedTime: 76S Priority: 8 ThreadState: In Run Queue
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

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

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.25S Sys  Time: 0S ElapsedTime: 75S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:49:51 PM Processes: 73 Threads: 869 C. Switches: 43307 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 11.9S Sys  Time: 27.29S Memory: 275.59MB Page File: 0.63MB
  Handle: 1444 User Time: 11.54S Sys  Time: 26.1S ElapsedTime: 76S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 117.66S Sys  Time: 0.11S Memory: 545.34MB Page File: 0.67MB
  Handle: 3520 User Time: 75.34S Sys  Time: 0.01S ElapsedTime: 76S Priority: 8 ThreadState: In Run Queue
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

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

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.44S Sys  Time: 0S ElapsedTime: 76S Priority: 8 ThreadState: Running
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:51:07 PM Processes: 73 Threads: 865 C. Switches: 41992 Q. Length: 7
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 10.81S Sys  Time: 26.83S Memory: 276.09MB Page File: 0.63MB
  Handle: 1444 User Time: 10.02S Sys  Time: 25.51S ElapsedTime: 71S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 112.71S Sys  Time: 0.09S Memory: 545.63MB Page File: 0.67MB
  Handle: 3520 User Time: 72.57S Sys  Time: 0S ElapsedTime: 74S Priority: 10 ThreadState: Idle
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,   REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID, REQ.R2_PART_ID, REQ.R3_PART_ID,...

    SQL_ID  3p1v051atxt1z, child number 0
    -------------------------------------
    SELECT   TOP_LEVEL_PART_ID, REQ.PURC_PART_ID, REQ.TOTAL_QTY,  
    REQ.TOTAL_QTY*TPPD.INCREASE "Increase",   REQ.R1_PART_ID,
    REQ.R2_PART_ID, REQ.R3_PART_ID,   REQ.R4_PART_ID, REQ.R5_PART_ID,
    REQ.R6_PART_ID,   REQ.R1_CALC_QTY, REQ.R2_CALC_QTY, REQ.R3_CALC_QTY,  
    REQ.R4_CALC_QTY, REQ.R5_CALC_QTY, REQ.R6_CALC_QTY FROM   (SELECT    
    TOP_LEVEL_PART_ID,     DECODE(R6_PART_ID,NULL,      
    DECODE(R5_PART_ID,NULL,         DECODE(R4_PART_ID,NULL,          
    DECODE(R3_PART_ID,NULL,             DECODE(R2_PART_ID,NULL,            
           R1_PART_ID,R2_PART_ID),                  R3_PART_ID),           
        R4_PART_ID),              R5_PART_ID),            R6_PART_ID)
    PURC_PART_ID,     NVL(R1_CALC_QTY,0)*NVL(R2_CALC_QTY,1)*NVL(R3_CALC_QTY,
    1)*NVL(R4_CALC_QTY,1)*NVL(R5_CALC_QTY,1)*NVL(R6_CALC_QTY,1) TOTAL_QTY, 
       R1_PART_ID, R1_CALC_QTY, R2_PART_ID, R2_CALC_QTY, R3_PART_ID,
    R3_CALC_QTY,     R4_PART_ID, R4_CALC_QTY, R5_PART_ID, R5_CALC_QTY,
    R6_PART_ID, R6_CALC_QTY   FROM     (SELECT       PL.PART_ID TOP_LEVE

    Plan hash value: 3313542492

    ---------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
    ---------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                |                      |       |       |   101 (100)|          |        |
    |   1 |  VIEW                                           | VM_NWVW_1            |     1 |   225 |   101   (2)| 00:00:02 |   OR11 |
    |   2 |   HASH UNIQUE                                   |                      |     1 |   498 |   101   (2)| 00:00:02 |        |
    |   3 |    NESTED LOOPS OUTER                           |                      |     1 |   498 |   100   (1)| 00:00:02 |        |
    |*  4 |     FILTER                                      |                      |       |       |            |          |        |
    |   5 |      NESTED LOOPS OUTER                         |                      |     1 |   474 |    99   (2)| 00:00:02 |        |
    |   6 |       NESTED LOOPS OUTER                        |                      |     1 |   435 |    96   (2)| 00:00:02 |        |
    |   7 |        NESTED LOOPS OUTER                       |                      |     1 |   411 |    95   (2)| 00:00:02 |        |
    |*  8 |         FILTER                                  |                      |       |       |            |          |        |
    |   9 |          NESTED LOOPS OUTER                     |                      |     1 |   387 |    94   (2)| 00:00:02 |        |
    |* 10 |           FILTER                                |                      |       |       |            |          |        |
    |  11 |            NESTED LOOPS OUTER                   |                      |     1 |   348 |    91   (2)| 00:00:02 |        |
    |  12 |             NESTED LOOPS OUTER                  |                      |     1 |   309 |    88   (2)| 00:00:02 |        |
    |  13 |              NESTED LOOPS OUTER                 |                      |     1 |   285 |    87   (2)| 00:00:02 |        |
    |  14 |               NESTED LOOPS OUTER                |                      |     1 |   261 |    86   (2)| 00:00:02 |        |
    |* 15 |                FILTER                           |                      |       |       |            |          |        |
    |  16 |                 NESTED LOOPS OUTER              |                      |     1 |   237 |    85   (2)| 00:00:02 |        |
    |* 17 |                  FILTER                         |                      |       |       |            |          |        |
    |  18 |                   NESTED LOOPS OUTER            |                      |     1 |   198 |    82   (2)| 00:00:01 |        |
    |  19 |                    NESTED LOOPS                 |                      |     1 |   159 |    79   (2)| 00:00:01 |        |
    |  20 |                     NESTED LOOPS                |                      |     1 |   118 |    76   (2)| 00:00:01 |        |
    |  21 |                      MERGE JOIN CARTESIAN       |                      |     1 |    94 |    71   (2)| 00:00:01 |        |
    |* 22 |                       TABLE ACCESS FULL         | TEMP_PART_PRICE_DATE |     1 |    85 |     2   (0)| 00:00:01 |   OR11 |
    |  23 |                       BUFFER SORT               |                      |  7016 | 63144 |    69   (2)| 00:00:01 |        |
    |* 24 |                        TABLE ACCESS FULL        | CUSTOMER_ORDER       |  7016 | 63144 |    69   (2)| 00:00:01 |   OR11 |
    |* 25 |                      TABLE ACCESS BY INDEX ROWID| CUST_ORDER_LINE      |     1 |    24 |     5   (0)| 00:00:01 |   OR11 |
    |* 26 |                       INDEX RANGE SCAN          | SYS_C0028623         |     9 |       |     1   (0)| 00:00:01 |   OR11 |
    |  27 |                     TABLE ACCESS BY INDEX ROWID | REQUIREMENT          |     1 |    41 |     3   (0)| 00:00:01 |   OR11 |
    |* 28 |                      INDEX RANGE SCAN           | X_REQUIREMENT_5      |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  29 |                    TABLE ACCESS BY INDEX ROWID  | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 30 |                     INDEX RANGE SCAN            | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  31 |                  TABLE ACCESS BY INDEX ROWID    | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 32 |                   INDEX RANGE SCAN              | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 33 |                TABLE ACCESS BY INDEX ROWID      | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 34 |                 INDEX UNIQUE SCAN               | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 35 |               TABLE ACCESS BY INDEX ROWID       | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 36 |                INDEX UNIQUE SCAN                | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 37 |              TABLE ACCESS BY INDEX ROWID        | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 38 |               INDEX UNIQUE SCAN                 | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  39 |             TABLE ACCESS BY INDEX ROWID         | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 40 |              INDEX RANGE SCAN                   | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |  41 |           TABLE ACCESS BY INDEX ROWID           | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 42 |            INDEX RANGE SCAN                     | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 43 |         TABLE ACCESS BY INDEX ROWID             | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 44 |          INDEX UNIQUE SCAN                      | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |* 45 |        TABLE ACCESS BY INDEX ROWID              | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 46 |         INDEX UNIQUE SCAN                       | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    |  47 |       TABLE ACCESS BY INDEX ROWID               | REQUIREMENT          |     1 |    39 |     3   (0)| 00:00:01 |   OR11 |
    |* 48 |        INDEX RANGE SCAN                         | SYS_C0028831         |     1 |       |     2   (0)| 00:00:01 |   OR11 |
    |* 49 |     TABLE ACCESS BY INDEX ROWID                 | PART                 |     1 |    24 |     1   (0)| 00:00:01 |   OR11 |
    |* 50 |      INDEX UNIQUE SCAN                          | SYS_C0028742         |     1 |       |     0   (0)|          |   OR11 |
    ---------------------------------------------------------------------------------------------------------------------------------

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

       4 - filter(("TPPD"."PART_ID"=DECODE(DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID"
                  ,NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'N
                  ONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE
                  (NVL("P5"."FABRICATED",'NONE'),'Y',"R6"."PART_ID",NULL)),NULL,DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NO
                  NE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(N
                  VL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",
                  NULL)),NULL,DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."
                  FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NU
                  LL,DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NON
                  E'),'Y',"R3"."PART_ID",NULL)),NULL,DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,"R"."PART_ID"
                  ,DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL)),DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_
                  ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL))),DECODE(DECODE(DECODE(NVL("P"."FABRICA
                  TED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,
                  DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL))),DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),
                  'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FABRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P
                  3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)
                  )),DECODE(DECODE(DECODE(DECODE(DECODE(NVL("P"."FABRICATED",'NONE'),'Y',"R2"."PART_ID",NULL),NULL,NULL,DECODE(NVL("P2"."FA
                  BRICATED",'NONE'),'Y',"R3"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P3"."FABRICATED",'NONE'),'Y',"R4"."PART_ID",NULL)),NULL
                  ,NULL,DECODE(NVL("P4"."FABRICATED",'NONE'),'Y',"R5"."PART_ID",NULL)),NULL,NULL,DECODE(NVL("P5"."FABRICATED",'NONE'),'Y',"
                  R6"."PART_ID",NULL))) AND "R6"."SUBORD_WO_SUB_ID" IS NULL))
       8 - filter("R5"."SUBORD_WO_SUB_ID" IS NULL)
      10 - filter("R4"."SUBORD_WO_SUB_ID" IS NULL)
      15 - filter("R3"."SUBORD_WO_SUB_ID" IS NULL)
      17 - filter("R2"."SUBORD_WO_SUB_ID" IS NULL)
      22 - filter("TPPD"."INCREASE"<>0)
      24 - filter(("CO"."STATUS"='C' OR "CO"."STATUS"='F' OR "CO"."STATUS"='P' OR "CO"."STATUS"='R' OR "CO"."STATUS"='U'))
      25 - filter("COL"."DESIRED_SHIP_DATE">SYSDATE@!-365)
      26 - access("CO"."ID"="COL"."CUST_ORDER_ID")
      28 - access("R"."WORKORDER_TYPE"='M' AND "COL"."PART_ID"="R"."WORKORDER_BASE_ID" AND "R"."WORKORDER_LOT_ID"='0' AND
                  "R"."WORKORDER_SPLIT_ID"='0' AND "R"."SUBORD_WO_SUB_ID" IS NULL)
      30 - access("R2"."WORKORDER_TYPE"='M' AND "R"."PART_ID"="R2"."WORKORDER_BASE_ID" AND "R2"."WORKORDER_LOT_ID"='0')
      32 - access("R3"."WORKORDER_TYPE"='M' AND "R2"."PART_ID"="R3"."WORKORDER_BASE_ID" AND "R3"."WORKORDER_LOT_ID"='0')
      33 - filter("P3"."FABRICATED"='Y')
      34 - access("R3"."PART_ID"="P3"."ID")
      35 - filter("P"."FABRICATED"='Y')
      36 - access("R"."PART_ID"="P"."ID")
      37 - filter("P2"."FABRICATED"='Y')
      38 - access("R2"."PART_ID"="P2"."ID")
      40 - access("R4"."WORKORDER_TYPE"='M' AND "R3"."PART_ID"="R4"."WORKORDER_BASE_ID" AND "R4"."WORKORDER_LOT_ID"='0')
      42 - access("R5"."WORKORDER_TYPE"='M' AND "R4"."PART_ID"="R5"."WORKORDER_BASE_ID" AND "R5"."WORKORDER_LOT_ID"='0')
      43 - filter("P4"."FABRICATED"='Y')
      44 - access("R4"."PART_ID"="P4"."ID")
      45 - filter("P5"."FABRICATED"='Y')
      46 - access("R5"."PART_ID"="P5"."ID")
      48 - access("R6"."WORKORDER_TYPE"='M' AND "R5"."PART_ID"="R6"."WORKORDER_BASE_ID" AND "R6"."WORKORDER_LOT_ID"='0')
      49 - filter("P6"."FABRICATED"='Y')
      50 - access("R6"."PART_ID"="P6"."ID")

  Handle: 2152 User Time: 37.07S Sys  Time: 0.05S ElapsedTime: 75S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:52:17 PM Processes: 73 Threads: 861 C. Switches: 1548363 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 11.42S Sys  Time: 27.27S Memory: 232MB Page File: 0.63MB
  Handle: 1444 User Time: 11.85S Sys  Time: 27.97S ElapsedTime: 69S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 55.19S Sys  Time: 0.78S Memory: 519.81MB Page File: 0.67MB
  Handle: 2152 User Time: 48.79S Sys  Time: 0.66S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:53:24 PM Processes: 73 Threads: 859 C. Switches: 66962 Q. Length: 2
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 21.67S Sys  Time: 43.91S Memory: 175.28MB Page File: 0.63MB
  Handle: 1444 User Time: 22.11S Sys  Time: 45.18S ElapsedTime: 70S Priority: 8 ThreadState: Running

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 65.88S Sys  Time: 0.08S Memory: 441.9MB Page File: 0.67MB
  Handle: 2152 User Time: 66.77S Sys  Time: 0.06S ElapsedTime: 68S Priority: 8 ThreadState: Running
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:54:35 PM Processes: 73 Threads: 860 C. Switches: 47967 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 15.63S Sys  Time: 48.93S Memory: 141.9MB Page File: 0.63MB
  Handle: 1444 User Time: 14.4S Sys  Time: 46.38S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 75.85S Sys  Time: 0.09S Memory: 438.77MB Page File: 0.67MB
  Handle: 2152 User Time: 64.79S Sys  Time: 0S ElapsedTime: 71S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

1/8/2010 12:55:42 PM Processes: 73 Threads: 864 C. Switches: 35978 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 8.83S Sys  Time: 23.93S Memory: 141.25MB Page File: 0.63MB
  Handle: 1444 User Time: 9.08S Sys  Time: 24.16S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 98.7S Sys  Time: 0.22S Memory: 445.1MB Page File: 0.67MB
  Handle: 2152 User Time: 32.07S Sys  Time: 0S ElapsedTime: 65S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 65.07S Sys  Time: 0S ElapsedTime: 65S Priority: 8 ThreadState: Running
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

1/8/2010 12:56:47 PM Processes: 73 Threads: 856 C. Switches: 59846 Q. Length: 5
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.3S Sys  Time: 22.7S Memory: 141.1MB Page File: 0.63MB
  Handle: 1444 User Time: 9.25S Sys  Time: 22.85S ElapsedTime: 65S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 96.47S Sys  Time: 0.03S Memory: 440.21MB Page File: 0.67MB
  Handle: 2152 User Time: 32.72S Sys  Time: 0S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 54.01S Sys  Time: 0S ElapsedTime: 67S Priority: 8 ThreadState: In Run Queue
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

1/8/2010 12:57:56 PM Processes: 73 Threads: 861 C. Switches: 35972 Q. Length: 6
Instance: c:\oracle\product\10.2.0\db_1\bin\ORACLE.EXE OR10
 User Time: 9.33S Sys  Time: 25.19S Memory: 141.11MB Page File: 0.63MB
  Handle: 1444 User Time: 9.34S Sys  Time: 25.5S ElapsedTime: 70S Priority: 8 ThreadState: In Run Queue

Instance: c:\oracle\product\11.1.0\db_1\bin\ORACLE.EXE OR11
 User Time: 103.9S Sys  Time: 0.13S Memory: 442.42MB Page File: 0.67MB
  Handle: 3520 User Time: 34.13S Sys  Time: 0.02S ElapsedTime: 69S Priority: 8 ThreadState: Running
   PID:18 SPID:3520 SID:146 SERIAL#:4 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;

    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

  Handle: 2152 User Time: 34.04S Sys  Time: 0S ElapsedTime: 69S Priority: 8 ThreadState: In Run Queue
   PID:19 SPID:2152 SID:145 SERIAL#:12 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is the long description for this number '|| TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000)))...

    SQL_ID  6hxcgpp1rd1ag, child number 0
    -------------------------------------
    INSERT INTO T1 SELECT   CEIL(ABS(SIN(ROWNUM/9.9999)*10000)),   'This is
    the long description for this number '||
    TO_CHAR(CEIL(ABS(SIN(ROWNUM/9.9999)*10000))) FROM   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000),   (SELECT     ROWNUM
    RN   FROM     DUAL   CONNECT BY     LEVEL<=10000)

    Plan hash value: 643243249

    -----------------------------------------------------------------------------------
    | Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | INSERT STATEMENT                   |      |       |     4 (100)|          |
    |   1 |  LOAD TABLE CONVENTIONAL           |      |       |            |          |
    |   2 |   COUNT                            |      |       |            |          |
    |   3 |    MERGE JOIN CARTESIAN            |      |     1 |     4   (0)| 00:00:01 |
    |   4 |     VIEW                           |      |     1 |     2   (0)| 00:00:01 |
    |   5 |      COUNT                         |      |       |            |          |
    |   6 |       CONNECT BY WITHOUT FILTERING |      |       |            |          |
    |   7 |        FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
    |   8 |     BUFFER SORT                    |      |     1 |     4   (0)| 00:00:01 |
    |   9 |      VIEW                          |      |     1 |     2   (0)| 00:00:01 |
    |  10 |       COUNT                        |      |       |            |          |
    |  11 |        CONNECT BY WITHOUT FILTERING|      |       |            |          |
    |  12 |         FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------

  Handle: 5960 User Time: 34.18S Sys  Time: 0S ElapsedTime: 68S Priority: 8 ThreadState: In Run Queue
   PID:20 SPID:5960 SID:132 SERIAL#:55 USERNAME:TESTUSER MACHINE:HOME.NET\AIRFORCE-3 PROGRAM:sqlplus.exe
   DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop; End;
    SQL_ID  cujkdbu2npk0x, child number 0

    DECLARE   i NUMBER := 0;   STime DATE := SYSDATE; BEGIN   WHILE
    (SYSDATE - STime) < 0.006945 LOOP     i := i + + 0.000001;   End Loop;
    End;

    NOTE: cannot fetch plan for SQL_ID: cujkdbu2npk0x, CHILD_NUMBER: 0
          Please verify value of SQL_ID and CHILD_NUMBER;
          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

Notice in the above that toward the end two sessions were doing nothing but burning CPU in a PL/SQL loop - the script caught the person trying to soak the computer's dual core CPU.  The CPU queue length jumped significantly as a result of the activity of the two sessions.

Well, the above is neat, but what about the script to create the output?  Sorry, I ran out of space in this blog post - check back in a little while... (Forward to the Follow-Up Article)








Follow

Get every new post delivered to your Inbox.

Join 139 other followers