OraNA :: Database Management and Performance

Grid Control & OID: a deadly combo

Frank — Fri, 12 Mar 2010 01:28:29 PST

If you ever experience these symptoms, ask your self whether you have Grid Control (or OEM) running.Single Sign On failsOID Processes suddenly have stopped (which explains the above)ODS database account is locked, which seems to cause the problemUnlocking the ODS account resolves the problem, but it gets locked after a short period.The cause is the fact you changed the ODS password. Now, you may

From a VBS Script to a 10046 Trace and Back into a VBS Script

Charles Hooper — Thu, 11 Mar 2010 22:00:25 PST

March 12, 2010

I thought that we would try something a bit more difficult today. In previous articles I showed how to generate and read 10046 trace files using various methods, and I also showed a couple of different VBS scripts that could interact with an Oracle database. With some effort we could even read through a 10046 to pull out bits of information, much like TKPROF, but it probably does not make much sense to reproduce what TKPROF already accomplishes. I thought instead what I would do is to create a VBS script that generates a 10046 trace file at level 4, while executing a couple of SQL statements. A second VBS script will read the raw 10046 trace file and convert that trace file back into a VBS script, complete with bind variables. The code for the second VBS script is based on some of the code in my Toy project for performance tuning – something that I originally created just to see if it could be done.

First, we need a table to use as the data source for the first VBS script – this is the test table used in this blog article:

CREATE TABLE EMPLOYEE_RECORD_TEST AS
SELECT
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

With the test table built, we execute the following simple VBS script (using either CSCRIPT or WSCRIPT on a Windows client):

Const adCmdText = 1
Const adNumeric = 131
Const adDate = 7
Const adDBDate = 133
Const adDBTimeStamp = 135
Const adDBTime = 134
Const adVarChar = 200
Const adParamInput = 1

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Dim dbDatabase
Dim snpDataEmployees
Dim comDataEmployees
Dim snpDataAttend
Dim comDataAttend
Dim snpDataEmpRecord
Dim comDataEmpRecord

Set dbDatabase = CreateObject("ADODB.Connection")
Set snpDataEmployees = CreateObject("ADODB.Recordset")
Set comDataEmployees = CreateObject("ADODB.Command")
Set snpDataAttend = CreateObject("ADODB.Recordset")
Set comDataAttend = CreateObject("ADODB.Command")
Set snpDataEmpRecord = CreateObject("ADODB.Recordset")
Set comDataEmpRecord = CreateObject("ADODB.Command")

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

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

dbDatabase.Execute "ALTER SESSION SET TRACEFILE_IDENTIFIER = 'VBS2TRACE2VBS'"
dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 4'"

strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & VBCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & VBCrLf
strSQL = strSQL & "  SHIFT_DATE," & VBCrLf
strSQL = strSQL & "  INDIRECT_ID)" & VBCrLf
strSQL = strSQL & "VALUES(" & VBCrLf
strSQL = strSQL & "  ?," & VBCrLf
strSQL = strSQL & "  ?," & VBCrLf
strSQL = strSQL & "  ?)"

With comDataEmpRecord
    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30
    .ActiveConnection = dbDatabase

    .Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "TEST")
    .Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, Date)
    .Parameters.Append .CreateParameter("indirect_id", adVarchar, adParamInput, 15, "HOL")
End With

'Rollback Test
dbDatabase.BeginTrans

comDataEmpRecord.Execute

dbDatabase.RollbackTrans

strSQL = "SELECT DISTINCT" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE>= ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID= ?" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID"

With comDataEmployees
    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30
    .ActiveConnection = dbDatabase

    .Parameters.Append .CreateParameter("shift_date", adDate, adParamInput, 8, DateAdd("d", -90, Date))
    .Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "VAC")
End With

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID= ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID= ?"

With comDataAttend
    'Set up the command properties
    .CommandText = strSQL
    .CommandType = adCmdText
    .CommandTimeout = 30
    .ActiveConnection = dbDatabase

    .Parameters.Append .CreateParameter("employee_id", adVarChar, adParamInput, 15, "HOOPER")
    .Parameters.Append .CreateParameter("indirect_id", adVarChar, adParamInput, 15, "EXCUSE")
End With

Set snpDataEmployees = comDataEmployees.Execute

If Not (snpDataEmployees Is Nothing) Then
    Do While Not snpDataEmployees.EOF
        comDataAttend("employee_id") = snpDataEmployees("employee_id")
        comDataAttend("indirect_id") = "EXCUSE"
        Set snpDataAttend = comDataAttend.Execute
        If Not snpDataAttend.EOF Then
            'Do Something with the data
        End If
        snpDataAttend.Close

        comDataAttend("indirect_id") = "ABS"
        Set snpDataAttend = comDataAttend.Execute
        If Not snpDataAttend.EOF Then
            'Do Something with the data
        End If
        snpDataAttend.Close

        snpDataEmployees.MoveNext
    Loop

    snpDataEmployees.Close
End If

dbDatabase.Close
Set snpDataEmployees = Nothing
Set snpDataAttend = Nothing
Set comDataEmployees = Nothing
Set comDataAttend = Nothing
Set snpDataEmpRecord = Nothing
Set comDataEmpRecord = Nothing
Set dbDatabase = Nothing

(TestScript.vbs - save as TestScript.vbs)

In the above, replace MyDB with a valid database name from the tnsnames.ora file, MyUsername with a valid username, and MyPassword with the password for the user. The script starts by starting a transaction (the default behavior is an implicit commit), a row is inserted into the test table, and then a ROLLBACK is performed. The script then submits a SQL statement that retrieves a list of 5 employees from the test table. For each of the (up to) 5 employees a second SQL statement is executed with two different bind variable sets to determine the number of each week day the employee has been out of work on an excused (EXCUSE) or unexcused (ABS) absence. If I were writing a real program to accomplish this task I would combine the three SELECT statements into a single SELECT statement, but I want to demonstrate how the second VBS script handles multiple SQL statements that are open at the same time.

Running the above script generated a trace file when executed against Oracle Database 11.2.0.1: or112_ora_5482_VBS2TRACE2VBS.trc (save as C:\or112_ora_5482_VBS2TRACE2VBS.trc – Windows users can view the file with Wordpad and convert the file into a plain text file that can be opened with Notepad). The goal is to take the trace file and transform it back into a VBS script, ignoring SQL statements that appear in the trace file at a depth greater than 0.

The output of the VBS script that reads the 10046 trace file and generates a VBS file should look something like this:

'Source File:C:\or112_ora_5482_VBS2TRACE2VBS.trc

HyperactiveTrace

Sub HyperactiveTrace()
    Const adCmdText = 1
    Const adCmdStoredProc = 4
    Const adParamInput = 1
    Const adVarNumeric = 139
    Const adBigInt = 20
    Const adDecimal = 14
    Const adDouble = 5
    Const adInteger = 3
    Const adLongVarBinary = 205
    Const adNumeric = 131
    Const adSingle = 4
    Const adSmallInt = 2
    Const adTinyInt = 16
    Const adUnsignedBigInt = 21
    Const adUnsignedInt = 19
    Const adUnsignedSmallInt = 18
    Const adUnsignedTinyInt = 17
    Const adDate = 7
    Const adDBDate = 133
    Const adDBTimeStamp = 135
    Const adDBTime = 134
    Const adVarChar = 200
    Const adChar = 129
    Const adUseClient = 3

    Dim i
    Dim strSQL
    Dim strUsername
    Dim strPassword
    Dim strDatabase

    Dim dbDatabase
    Set dbDatabase = CreateObject("ADODB.Connection")
    Dim snpData1
    Dim comData1
    Set snpData1 = CreateObject("ADODB.Recordset")
    Set comData1 = CreateObject("ADODB.Command")
    Dim snpData2
    Dim comData2
    Set snpData2 = CreateObject("ADODB.Recordset")
    Set comData2 = CreateObject("ADODB.Command")
    Dim snpData3
    Dim comData3
    Set snpData3 = CreateObject("ADODB.Recordset")
    Set comData3 = CreateObject("ADODB.Command")
    Dim snpData4
    Dim comData4
    Set snpData4 = CreateObject("ADODB.Recordset")
    Set comData4 = CreateObject("ADODB.Command")
    Dim snpData5
    Dim comData5
    Set snpData5 = CreateObject("ADODB.Recordset")
    Set comData5 = CreateObject("ADODB.Command")
    Dim snpData6
    Dim comData6
    Set snpData6 = CreateObject("ADODB.Recordset")
    Set comData6 = CreateObject("ADODB.Command")
    Dim snpData7
    Dim comData7
    Set snpData7 = CreateObject("ADODB.Recordset")
    Set comData7 = CreateObject("ADODB.Command")
    Dim snpData8
    Dim comData8
    Set snpData8 = CreateObject("ADODB.Recordset")
    Set comData8 = CreateObject("ADODB.Command")
    Dim snpData9
    Dim comData9
    Set snpData9 = CreateObject("ADODB.Recordset")
    Set comData9 = CreateObject("ADODB.Command")
    Dim snpData10
    Dim comData10
    Set snpData10 = CreateObject("ADODB.Recordset")
    Set comData10 = CreateObject("ADODB.Command")
    Dim snpData11
    Dim comData11
    Set snpData11 = CreateObject("ADODB.Recordset")
    Set comData11 = CreateObject("ADODB.Command")
    Dim snpData12
    Dim comData12
    Set snpData12 = CreateObject("ADODB.Recordset")
    Set comData12 = CreateObject("ADODB.Command")
    Dim snpData13
    Dim comData13
    Set snpData13 = CreateObject("ADODB.Recordset")
    Set comData13 = CreateObject("ADODB.Command")
    Dim snpData14
    Dim comData14
    Set snpData14 = CreateObject("ADODB.Recordset")
    Set comData14 = CreateObject("ADODB.Command")
    Dim snpData15
    Dim comData15
    Set snpData15 = CreateObject("ADODB.Recordset")
    Set comData15 = CreateObject("ADODB.Command")
    Dim snpData16
    Dim comData16
    Set snpData16 = CreateObject("ADODB.Recordset")
    Set comData16 = CreateObject("ADODB.Command")
    Dim snpData17
    Dim comData17
    Set snpData17 = CreateObject("ADODB.Recordset")
    Set comData17 = CreateObject("ADODB.Command")
    Dim snpData18
    Dim comData18
    Set snpData18 = CreateObject("ADODB.Recordset")
    Set comData18 = CreateObject("ADODB.Command")
    Dim snpData19
    Dim comData19
    Set snpData19 = CreateObject("ADODB.Recordset")
    Set comData19 = CreateObject("ADODB.Command")
    Dim snpData20
    Dim comData20
    Set snpData20 = CreateObject("ADODB.Recordset")
    Set comData20 = CreateObject("ADODB.Command")

    On Error Resume Next

    strUsername = "MyUsername"
    strPassword = "MyPassword"
    strDatabase = "MyDB"
    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

    'dbDatabase.BeginTrans

    'Transaction Committed and NO Records were Affected, Need to determine transaction start
    dbDatabase.CommitTrans

    'dbDatabase.BeginTrans

    Set comData3 = CreateObject("ADODB.Command")

    strSQL = "INSERT INTO EMPLOYEE_RECORD_TEST(" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
    strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
    strSQL = strSQL & "  INDIRECT_ID)" & vbCrLf
    strSQL = strSQL & "VALUES(" & vbCrLf
    strSQL = strSQL & "  ?," & vbCrLf
    strSQL = strSQL & "  ?," & vbCrLf
    strSQL = strSQL & "  ?)"

    With comData3
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'comData3.Execute

    With comData3
        .Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "TEST")
        .Parameters.Append .CreateParameter("B2", adDate, adParamInput, 7, "3/11/2010 0:0:0")
        .Parameters.Append .CreateParameter("B3", adChar, adParamInput, 3, "HOL")
    End With

    comData3("B1") = "TEST"
    comData3("B2") = cDate("3/11/2010 0:0:0")
    comData3("B3") = "HOL"

    comData3.Execute

    'Transaction Rolled Back and Records Should have been Affected, Need to determine transaction start
    dbDatabase.RollbackTrans

    'dbDatabase.BeginTrans

    'Cursor 2 Closing
    If snpData2.State = 1 Then
        snpData2.Close
    End If
    Set comData2 = Nothing

    Set comData2 = CreateObject("ADODB.Command")

    strSQL = "SELECT DISTINCT" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  SHIFT_DATE>= ?" & vbCrLf
    strSQL = strSQL & "  AND INDIRECT_ID= ?" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID"

    With comData2
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'Set snpData2 = comData2.Execute

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    With comData2
        .Parameters.Append .CreateParameter("B1", adDate, adParamInput, 7, "12/11/2009 0:0:0")
        .Parameters.Append .CreateParameter("B2", adChar, adParamInput, 3, "VAC")
    End With

    comData2("B1") = cDate("12/11/2009 0:0:0")
    comData2("B2") = "VAC"

    Set snpData2 = comData2.Execute

    If Not (snpData2 Is Nothing) Then
        Do While Not snpData2.EOF

            snpData2.MoveNext
        Loop
    End If

    'Cursor 4 Closing
    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set comData4 = Nothing

    Set comData4 = CreateObject("ADODB.Command")

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'2',1,0)) MON_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'3',1,0)) TUE_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'4',1,0)) WED_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'5',1,0)) THU_COUNT," & vbCrLf
    strSQL = strSQL & "  SUM(DECODE(TO_CHAR(SHIFT_DATE,'D'),'6',1,0)) FRI_COUNT" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  EMPLOYEE_ID= ?" & vbCrLf
    strSQL = strSQL & "  AND INDIRECT_ID= ?"

    With comData4
        'Set up the command properties
        .CommandText = strSQL
        .CommandType = adCmdText
        .CommandTimeout = 30
        .ActiveConnection = dbDatabase
        'Bind variables will be defined below, if there are no bind variable, uncomment the next line and add the looping construct
    End With
    'Set snpData4 = comData4.Execute

    With comData4
        .Parameters.Append .CreateParameter("B1", adChar, adParamInput, 4, "ERIC")
        .Parameters.Append .CreateParameter("B2", adChar, adParamInput, 6, "EXCUSE")
    End With

    comData4("B1") = "ERIC"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    comData4("B1") = "ERIC"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    comData4("B1") = "JOE"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "JOE"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "MIKE"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "MIKE"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "SAM"
    comData4("B2") = "EXCUSE"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    comData4("B1") = "SAM"
    comData4("B2") = "ABS"

    Set snpData4 = comData4.Execute

    If Not (snpData4 Is Nothing) Then
        Do While Not snpData4.EOF

            snpData4.MoveNext
        Loop
    End If

    'Transaction Committed and NO Records were Affected, Need to determine transaction start
    dbDatabase.CommitTrans

    'dbDatabase.BeginTrans

    'Cursor 5 Closing
    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set comData5 = Nothing

    '*************************************************************
    'Maximum Recordset Number Used is 5 - Adjust the Code at the Start Accordingly
    '*************************************************************

    If snpData1.State = 1 Then
        snpData1.Close
    End If
    Set snpData1 = Nothing

    If snpData2.State = 1 Then
        snpData2.Close
    End If
    Set snpData2 = Nothing

    If snpData3.State = 1 Then
        snpData3.Close
    End If
    Set snpData3 = Nothing

    If snpData4.State = 1 Then
        snpData4.Close
    End If
    Set snpData4 = Nothing

    If snpData5.State = 1 Then
        snpData5.Close
    End If
    Set snpData5 = Nothing

    Set comData1 = Nothing
    Set comData2 = Nothing
    Set comData3 = Nothing
    Set comData4 = Nothing
    Set comData5 = Nothing

    dbDatabase.Close
    Set dbDatabase = Nothing
End Sub

(TraceToVBSOutput.vbs – save as TraceToVBSOutput.vbs)

If you compare the original TestScript.vbs with the above output, we see that the two scripts are similar, but with a couple of distinct differences:

It is not necessarily easy to determine when a transaction starts, but it is possible to determine when a transaction ends. The script that reads the trace file inserts ‘dbDatabase.BeginTrans where it believes that a transaction should start – remove the ‘ if that is the correct starting point for the transaction.
Looping structures with nested SQL statements (the retrieval of the employee list from the EMPLOYEE_RECORD_TEST table and the probing of matching rows for each of those employees) cannot be reproduced automatically – you will have to recognize when one SQL statement is feeding the bind variable values of a second SQL statement.
The VBS script assumes that up to 20 cursors will be open at any one time, but will automatically handle many more than 20 simultaneously open cursors. The resulting VBS file should be cleaned up to remove the unneeded comData and snpData objects.
There are spurious snpDatan.Close statements – see the suggestions for improvements.
SQL statements submitted without bind variables will not have code written to execute those statements in the generated VBS file - see the suggestions for improvements.
Bind variables that are submitted as VARCHAR (adVarchar) are written to the trace file as if the bind variables were declared as CHAR (adChar) – while this does not appear to cause a problem, it might appear to be an unexpected change when comparing the test script with the automatically generated script.

Suggestions for improvement:

Recognize the EXEC line in the 10046 trace and use that to actually indicate that a SQL statement should execute in the generated script, rather than executing the SQL statement in response to the submission of bind variables.
Allow submitting the source trace file name and the destination (generated) VBS filename on the command line.
Allow submitting the username, password, and database name on the command line or in a web-based user interface.
Correct the script so that it does not attempt to close recordsets when those recordsets were never opened at dep=0 – this is caused by the script seeing a recursive SQL statement that is preparing to open with that cursor number.

The VBS script that converts 10046 trace files to VBS script files may be downloaded here: TraceToVBS.vbs (save as TraceToVBS.vbs). There may be bugs in the script, but it should be close enough to provide some degree of educational benefit.

Hotsos 2010 – A Training Day with Tanel Poder

Marco Gralike — Thu, 11 Mar 2010 17:16:39 PST

My last day already, so I hereby leave you with some impressions from Tanel’s Training Day. Maybe until next year.

If you also want to learn from him, then here’s your chance in Holland. Scripts and tools used by Tanel can be found here.

Friday Philosophy – CABs {an expensive way to get nowhere?}

mwidlake — Thu, 11 Mar 2010 15:24:21 PST

A few years ago, my wife and I went to New York for a holiday. We got a cab from the airport into Manhattan. It was an expensive way to see, at great length, some of the more uninteresting automobile transit routes through New York. We arrived at our hotel a great deal later than we anticipated. And with most of our paper dollars no longer in our possession.

I’ve also taken cabs through London, usually at the weekend to get back to Liverpool Street Station. The trip is generally quick, painless and not too expensive. Those black-cab drivers know their stuff.

Of course, the CABs I refer to in the title of this Friday Philosophy are not private cars for hire. In this context CAB is Change Advisory Board. A term that can make grown developers weep. If you do not know, the Change Advisory Board is a group of people who look at the changes that are planed for a computer system and decide if they are fit for release. My personal experience of them has been similar to my experience of the taxi variety, though sadly more of the New York than London variety.

You might expect me to now sink into a diatribe {ie extended rant} about how I hate CABs. Well, I don’t. CABs can be a part of a valuable and highly worthwhile process control mechanism. Just as proper QA is core to any mature software development process, so CABs are important in getting informed, talented stakeholders to review proposed changes. They check for overall system impact, clashes with other proposed changes that individual development streams may be unaware of and to verify Due Diligence has been invoked {that last one is a bit of a minefield and where, I believe, most CABs fail}.

Sadly, though this is often the aim. The end result is a bunch of uninformed and technically naive politicos trying to wield power, using the CAB meeting as an extended game of management chess.

I’ve seen CABs trade changes. “I’ll let you have X if I can have Y and Z”. I’ve seen CABs turn down changes because the form had spelling mistakes in it. I’ve seen CABs object to a change that will save the company 5 million pounds a day because it lacked a signature.

That last one just stopped me in my tracks {I’m not exaggerating either, if anything I am underplaying the cost impact of that decision. I saw the figures and I wasted a couple of days of my life checking, 5 million pounds a day was the least I felt I could prove.} We are talking about enough money every day to pay the salary of everyone on the CAB for several years. And they blocked it because the DBA team had not signed off the change.

The DBA Team had not signed off the change because the one and only DBA Team Leader who was allowed to sign off was on holiday for two weeks. They needed that holiday too, for other but I suspect linked reasons.

Now I knew the DBA Team Lead and he was a good bloke, he knew his stuff and he was not paid 5 million pounds a day. His deputy was paid even less but was no less talented but she was not allowed to sign off the change as she was not the DBA Team Lead.

That was a CAB gone very wrong. The process of the CAB had been allowed to over-rule good business sense. It was also overruling general and technical sense, but that really is secondary to what keeps the business making a profit.

I’ve seen the opposite of course, technical teams that just apply whatever changes they feel are fit and, to be honest, they seem to mess up less often than a poor CAB process as they know they are the ones who will spend the weekend fixing a mess if one occurs. But that mess will occur eventually, if control is lacking.

So, I feel CABs are good, if you have the right people on them and you have a sensible cascade of authority so one person being away does not block the system. That is quite a bit harder to put in place than a simple “Dave A, John, Andrea, Alex, Raj, Dave P, Mal, Malcolm and Sarah have final signoff” which most CABs effecively become.

But there is one last fault of CABs I want to highlight. They tend to treat all changes in the same way and all changes are not the same. Upgrading the underlying OS is not the same as adding a cardinality hint to one Business Objects report.

If your CAB or change process treat the two above examples the same, then your CAB or change process is broken. Now, in all IT “rules of thumb” there is an exception. In this case, I am truly struggling to think of one. If your change process treats an OS upgrade the same as adding a hint to a report, it is not fit for purpose. That is my main issue with CABs. They should be of significant business importance, but nearly always they are implemented with one process to deal with all situations and then get taken over by people with an “Office Politics” agenda as opposed to a “Getting the best job we can reasonably expect done” agenda.

I’m very passionate about this and I have a way I hope can throw this issue into context, an analogy.

Ask yourself one this senario.
You go to your Doctor with a niggly cough you have had for a week OR you go to your doctor because you almost passed out each day you got out of bed for the last three days.
If your doctor treated you the same for both sets of symptoms, would you be happy with that doctor?

Why are all IT changes handled by most CABs in exactly the same way?

I could say no one dies when it comes to IT, but you work in healthcare computing for a while and that argument falls apart a little.

Oh dear, got a bit “deep” there!

(BTW if you ever almost collapse when you get out of the bed in the morning, do NOT go to work, go instead to your doctor and ask them for a full medical and if he/she does not take blood pressure tests and order a full blood chemisty test, go find a new doctor.)

Upgrading to R12.1.2, Tip #1

Jeff Hunter — Thu, 11 Mar 2010 15:11:00 PST

While applying patch 7303033 on top of a 12.1.0 R12 installation, I got the following error in one of my workers:

FNDLOAD APPS/***** 0 Y UPLOAD @FND:patch/115/import/afscursp.lct @JTF:patch/115/import/US/jtfdiagresp.ldt -

Connecting to APPS......Connected successfully.

Calling FNDLOAD function.

Returned from FNDLOAD function.

Log file: /r12u/apps/apps_st/appl/admin/UPG/log/

Hotsos 2010 - Monique

dougburns@yahoo.com (Doug Burns) — Thu, 11 Mar 2010 14:10:00 PST

There she goes, squirreling into the corner of your carry-on. Ready for another adventure. Tucked between a fun floppy hat and the flourescent sun-block, of which she uses plenty, pale creature that she is, Monique is situated to prove to you that a seasoned traveller knows best. Dressed in a curly mohair (so as not to show the wrinkles that betray one new to the jet set) and sporting a pale chiffon scarf - to double as a disquise in those cities where she is already known for her somewhat silly escapades - she seems to wink at you and beckon you along. Forget your cares, but by no means your playing cards, and step lightly into whatever dreams your next voyage should hold for you: let Monique be your guide.

Her very presence alleviates all the maladies brought on by the jostling of trains, the dipping of ships, the swerving of buses and even by the simple fact that you are far from home. For she has discovered the secret to successful travel; with the exception of the luscious few minutes in the X-ray machine, where she delights in making funny faces at the operators searching for more dangerous contents. Monique never looks back.

Charleen Kinser Designs

(With thanks to Carol Dacko for bringing along an interesting dinner companion for my own cuddly friends. Somehow I doubt they'll ever be the same - she's terribly sophisticated for my lot!)

Hotsos 2010 - What's THAT?

dougburns@yahoo.com (Doug Burns) — Thu, 11 Mar 2010 14:00:00 PST

I heard someone bemoaning the lack of Swag at Hotsos - just an event program, couple of magazines and a small clockwork toy sponsored by Oracle. I don't think I ever come to the Symposium expecting Swag - it's just not that kind of event. But you do tend to get good speaker gifts, including the best speaker gift I ever received. This year, it's a digital photo frame.

As for that clockwork toy, the Cuddly Toys think it's *brilliant*!!! At first, their reaction was "What's THAT?!" then when I wound it up and let it dance in front of them, they fell in love.

The only thing is that it doesn't run for long per wind-up, so my right wrist is aching from them continually beggind me to "Do it again, Douglas!"

Then again, their reaction wasn't quite as relaxed as when they met Carol Dacko's friend while we were at the restaurant. Apologies for the quality of the photo, it was dark in there, but at least it proves that Little H and Chris were allowed out for dinner and that not all Cuddly Toys are made the same.

They were just stunned by their new friend. What's THAT?

All will be revealed in the next post ....

Hotsos 2010 - Day 4

dougburns@yahoo.com (Doug Burns) — Thu, 11 Mar 2010 10:25:35 PST

Hotsos 2010 - Day 4

First up was Cary Millsap's - Lessons Learned, Version 2010.03 As Cary pointed out, they always try to put the best speakers in the toughest slots - 8:30 in the morning post-party. I think local guys are slightly more reliable too because they might have actually gone home the night before! He started with a quick Hangover Survey (me - check!) and then pressed on talking about how we test system performance.

He showed a video of Boeing stress-testing the wing of the 787 and, as he pointed out, aircraft manufacturers really know how to stress-test! (Of course whether that reassures you as it does me, or makes you wish no-one would talk about wings disintegrating, as it probably would Mads, is personal.) They showed Boeing test equpiment which is complicated, expensive and non-revenue generating. Those tests are expensive but when people's lives are on the line, what choice is there? Boeing knows that it has to test the analytic models used in the design. He spent a lot of time talking about good test design. A few thoughts that stood out to me ...

- Some stress tests are a waste of time. Will the Boeing 787 land on the moon? If this test fails, what has it proven? If it passes, then it's awesome but it would be a very expensive way to prove it can cope with commercial flights in Earth's atmosphere.

- Why test for more than you will see in Production? Because you don't really know for sure what you'll see in Production.

- At some point, but I can't remember the context, he used a Scottish phrase that he'd heard Billy Connolly shout (although the Big Yin was only fully credited later in the day) ...

   "There's no such thing as bad weather, just the wrong clothes"

... looked over at me and said - "I'd love to hear you say that, with the proper accent". I declined politely.

- Most people try to prove only that their systems will work.

- Most tests of systems that are destined to fail never proved it in advance.

- Test to destruction

   a) Test
   b) Until the system melts
   c) Decide whether your real requirements are likely to be lower or higher than melting point.

There was a small amount of time for questions and once it looked like they were done, I granted Cary's wish (never thought I'd say that), stuck my hand up and repeated The Big Yin's words. It was only after the laughter had stopped that I realised I might have ruined his big closing, but I think he was ok about it

Next was Tanel Poder talking about LGWR, log file sync waits and COMMIT performance and shock, horror, I was actually going to say that this was one of the least rewarding sessions of the week for me. What?!? Tanel? But he's, like, an Oracle God! LOL But there were reasons

- I realise that I know a *lot* about how log file sync and log file parallel write work, how they relate to each other and some of the problems they might help you identify. Because it's a subject I'm *so* familiar with, I didn't learn much.

- His main demo didn't quite show what he wanted it to because it didn't run multiple sessions but, frankly, I'm in no position to talk about demos this week!

By the end, the presentation turned out ok, not least because there was another unexpected appearance from Bob Sneed to talk about the I/O components involved in redo log management including a suggestion that LGWR be put into a higher scheduling class (but not Real Time!) I'll try to find a link to his slides and let you take a look yourself.

I loved Tanel's Big Log File Sync Tuning Secret, though ...

   COMMIT LESS!

It was particularly relevant to me because I had a Big Log File Sync Tuning Secret as the closing moment of my own presentation. The problem was I couldn't use it after the demos went wrong!

   USE ASYNCHRONOUS COMMITS

But, in my case, that was supposed to be funny, too.

I ran off to try and use the free breakfast voucher that Marco had given me but I was just too late. No food again, then Well, I had a couple of slices of cold meat at lunchtime, but mainly to catch up with Alex G before he had to present and then head back to Ottawa. I managed to skip one session at this stage but, after a quick call home, I decided to go along to Alex's RAC Connection Management presentation after all (a little late). Although I have seen some of this stuff before, I always enjoy watching Alex's demos and was particularly impressed by the fact that he'd managed to write his own RAC connection load balancer! I was waiting for the applause in the room but either people didn't quite get it or there was just a lack of energy post-lunch on the last day. I suspect the latter.

Of course, once I'd said goodbye to Alex properly (don't see him nearly enough), I was a little late for whichever session was going to be my final one of the conference and I was hopelessly torn between Kyle Hailey's modern SQL performance tools presentation (Kyle's done a lot of cool work in the area of Oracle Performance Visualisation) and Chris Antognini's Diagnosing Parallel Executions Performance. In the end I plumped for the latter because I thought it was going to be like something I'd unsuccessfully attempted a couple of years ago and I wanted to see if Chris had a different angle on it and had been more successful. In the end, I probably made the wrong choice because although Chris' presentation was great, it was really all stuff I already knew. Definitely my bad call, though. Hopefully I'll get a chance to catch up with Kyle's presentation at some point in the future too!

After that there was just the usual short farewell and thanks from Gary Goodman of Hotsos. Although the thanks were appreciated, I'm glad they were spread around everybody because the attendees are one of the things that make this conference great and Becky and Rhonda did their usual sterling job of organising everything.

Then it was time for some Fajitas with a few friends (actually, a whopping great number of friends who practically filled the Mexican restaurant!) and a few very sedate beers. (We are old men (and women) now and the night before had a big one!) While we were waiting to go to the Mexican, I had one great surprise left - Alex's flights weren't going to get him home, so he came back from the airport and had to check in overnight! At least I got a chance to talk to him properly when I wasn't hopelessly drunk and didn't try to seduce him this time.

Now I need to stop blogging and get back to listening to Tanel's Training Day (good stuff, too, but more about that later)

Dropping OUTLN

Jonathan Lewis — Thu, 11 Mar 2010 09:35:16 PST

I had a very pleasant day yesterday at a SIG meeting of the UK Oracle User Group where I did the presentation about “drawing your SQL” that is the basis of the article I wrote for Simple Talk a little while ago. One of the other sessions had the entertaining title: “What happens if you drop [...]

20. - 21. May EMEA Harmony Conference in Tallinn

ilmar.kerm@gmail.com (Ilmar Kerm) — Wed, 10 Mar 2010 23:03:00 PST

An excellent joint conference for Estonian, Finnish, Latvian and Russian Oracle User Groups in Tallinn, 20. - 21. May 2010.
Speakers also include Tom Kyte, Tanel Põder, Chris J. Date and Steven Feuerstein.

Read the agenda here and register in OUGF home page (250€+VAT registration fee).

In addition, just before the conference, 17.-18. May, Chris Date will perform his "How to Write Correct SQL and Know It: A Relational Approach to SQL" seminar in Helsinki. More info here and more detailed information here.

What does ‘extended’ mean when referring to RAC clusters

John Hallas — Thu, 11 Mar 2010 00:12:19 PST

Yesterday I attended and spoke at the Scotland DBA SIG. It was an enjoyable day with not one but two presentation from Julian Dyke. The second one was an overview of HA options and it in Julian mentioned extended RAC. Now I have worked at several sites which have used or wanted to use extended RAC and I have heard a number of views on what exactly the term extended means.

From MoS Note 220970.1 we get the definitive statement on the maximum distance for extended RAC

The high impact of latency create practical limitations as to where this architecture can be deployed. While there is not fixed distance limitation, the additional latency on round trip on I/O and a one way cache fusion will have an affect on performance as distance increases. For example tests at 100km showed a 3-4 ms impact on I/O and 1 ms impact on cache fusion, thus the farther distance is the greater the impact on performance. This architecture fits best where the 2 datacenters are relatively close (<~25km) and the impact is negligible. Most customers implement under this distance w/ only a handful above and the farthest known example is at 100km. Largest distances than the commonly implemented may want to estimate or measure the performance hit on their application before implementing. Do ensure a proper setup of SAN buffer credits to limit the impact of distance at the I/O layer.

So we now have figures that 100Km is technically possible but probably won’t work well and most sites use less than 25Km. Co-incidentally in the paragraph above that quotation it refers to SE RAC (10g) as being limited to having all nodes located in the same room. So in two lines of text we have just gone from a 100ft to 100KMs.

Yesterday Julian’s answer when I asked him the question was that he thought extended was defined as a couple of kilometres or above which is reasonable. However I am thinking that a better definition of extended is not to focus on distance of nodes apart but on what connects the nodes. My thoughts are that that if fibre channel or DWDM is involved and the nodes are not in the same building then that would count as an extended RAC cluster despite potentially only being a 100 yards apart. Another view would be that if some form of array based mirroring is in place and RAC technology is in place then that again would define the system as ‘extended’ .

Now that all fits with a view I have heard from a well-known RAC consultant from Oracle who defines any RAC system where nodes are in different rooms as extended. I have never subscribed to that theory but I can see that the definition I provided in the previous paragraph would lead to that conclusion.

Now we just need to consolidate on the word ‘extended’ rather than the use of ’stretched’ which I see occasionally.

I really do hope I get some comments on this piece as I am sure it is worthy of debate and comment and anything that helps to create a shared view on the matter can only be good.

Select Statement Causing an ORA-00001?

Charles Hooper — Wed, 10 Mar 2010 22:00:24 PST

March 11, 2010

Sometimes I receive seemingly interesting emails showing Oracle errors – leaving me to ponder… certainly, that can’t cause an error, can it? Here is one that I received a year ago (paraphrased):

The commercially developed application that we are using displayed an error message identifying a SELECT statement as the source of an ORA-00001 error. What is the source of the Oracle constraint error? The error message displayed by the application is as follows:
select account_period
from PROJECT_SUMMARY
where project_id = :m_saProjSumProjectID[nProjSumIndex]
and id =:m_saProjSumSavedID[nProjSumIndex]
into :nACCOUNT_PERIOD

ORA-00001: unique constraint (TESTUSER.SYS_C006354) violated

This transaction has resulted in violating an Oracle defined constraint.
Constraints are enforced by the database manager.  This transaction has
been rolled back.

My first thought at the sight of this error was that the commercially developed application was actually displaying one of the SQL statements that was executed after the SQL statement which triggered the primary key violation. It is easy to let a runtime error slide through for a period of time before the program notices that an error happened – maybe it is just a sign of sloppy programming (I hope not, because this has happened in some of my custom-developed programs too).

How would we start to troubleshoot this error message? The “SYS_C” portion of the constraint name indicates that the constraint is most likely a system generated constraint name, probably intended to enforce a uniqueness requirement for a primary key column. Exporting the data from the database using Datapump export (or the legacy exp utility) and importing the data into a new database could cause the number following “SYS_C” to change, and it is likely that constraint SYS_C006354 in my database (that is used by the same application) is very different from that of the person who posed the question to me.

Let’s see if we are able to find the answer by working the problem in a circular fashion. For example, let’s find the name of the index that is used to enforce the primary key constraint on of one the application’s tables:

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  INDEX_NAME LIKE 'SYS%'
  AND TABLE_NAME='INVENTORY_TRANS';

INDEX_NAME
-----------
SYS_C005168

Now that we know that the index is named SYS_C005168, we could do something like this:

SELECT
  DC.OWNER,
  DC.CONSTRAINT_NAME,
  DC.CONSTRAINT_TYPE,
  DC.TABLE_NAME,
  DC.STATUS,
  SUBSTR(DCC.COLUMN_NAME,1,30) COLUMN_NAME
FROM
  DBA_CONSTRAINTS DC,
  DBA_CONS_COLUMNS DCC
WHERE
  DC.CONSTRAINT_NAME='SYS_C005168'
  AND DC.OWNER='TESTUSER'
  AND DC.OWNER=DCC.OWNER
  AND DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME
ORDER BY
  DCC.POSITION;

OWNER   CONSTRAINT_NAME  CONSTRAINT_TYPE  TABLE_NAME       STATUS   COLUMN_NAME
------  ---------------  ---------------  ---------------  -------  --------------
SYSADM  SYS_C005168      P                INVENTORY_TRANS  ENABLED  TRANSACTION_ID

The above output shows that the primary key constraint SYS_C005168 enforces the uniqueness of the primary key (TRANSACTION_ID) column in the table INVENTORY_TRANS. We just demonstrated that we now know what we already mostly knew.

In the case of the person who sent the email to me, the table name was not known. So, we could take the last of the above SQL statements and substitute SYS_C006354 in place of SYS_C005168 to find the table name and primary key column that was violated. If the SQL statement failed to return usable information the next step might be to enable a 10046 trace at level 4 for one of the affected sessions, and try to reproduce the problem. A 10046 trace will list the sequence of events that led up to the error message appearing in the client application.

ODTUG Kaleidoscope Conference 2010

Optimizer Development Group — Wed, 10 Mar 2010 19:57:40 PST

ODTUG Kaleidoscope 2010, June 27 - July 1st Washington DC is a great conference for Oracle developers and architects, offering the best content by renowned experts. We will have the privilege of delivering two Optimizer sessions this year, 'Explaining the Explain plan' and 'Finally Plan Stability during Database Upgrade with SQL Plan Management'. In the Explain the Explain plan session we will discuss each aspect of an execution plan (from selectivity to parallel execution), explain what information you should be getting from the plan, and how it will affects the execution. While in the SQL Plan Management session, we will will give detailed instructions on how to capture your existing execution plans before you upgrade to 11g, as well as an in-depth discussion on what to expect from the Optimizer after you upgrade to 11g.
ODTUG is a great conference where you can learn lots in a fun and casual atmosphere. Looking forward to seeing some of you there!

Clustering Factor: Row Migration’s Victim

Markus Winand — Tue, 09 Mar 2010 02:15:46 PST

This article describes the effects of a high row migration rate on the clustering factor and the optimizer’s ability to select the best execution plan.

In my previous article—Row Migration and Row Movement—I have demonstrated that the “insert empty, update everything” anti-pattern can lead to 100% row migration. This article continues the research on row migration and unveils surprising effects on the clustering factor. To be precise, the clustering factor can become completely bogus in presence of a very high row migration rate. Once the clustering factor is “wrong”, it’s just a finger exercise to construct an optimizer trap and proof that row migration can affect the query plan.

To start off, I create a table similar to the one in the previous article. However, I add one more column and populate it with random values from 0 to 9. I will need this column to build my optimizer trap later on.

CREATE TABLE row_mig1 (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  x NUMBER      NOT NULL,
  filter NUMBER NOT NULL,
  CONSTRAINT row_mig1_pk PRIMARY KEY (x)
) ENABLE ROW MOVEMENT;

BEGIN
   FOR i IN 1..100000 LOOP
      INSERT INTO row_mig1
                    (x, filter)
             VALUES (i, trunc(dbms_random.value(0, 10)));

      UPDATE row_mig1
         SET a ='a', b = 'b', c = 'c'
       WHERE x=i;
   END LOOP;
END;
/
COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1', CASCADE=>TRUE);

Up till now everything is very similar to the last article, except that I used DBMS_STATS instead of ANALYZE TABLE. Although we know exactly that almost every row was migrated, DBMS_STATS doesn’t care about that:

SQL> SELECT num_rows, chain_cnt
       FROM user_tables
      WHERE table_name='ROW_MIG1';

  NUM_ROWS  CHAIN_CNT
---------- ----------
    100000          0

SQL>

However, let’s have a look at the index:

SQL> SELECT num_rows, leaf_blocks, clustering_factor
       FROM user_indexes
      WHERE index_name = 'ROW_MIG1_PK';

  NUM_ROWS LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
    100000         187               918

SQL>

Well, that’s an excellent clustering factor. A low clustering factor indicates that the index is in the same sequence as the table. That’s true in that case because the index column corresponds to the order in which the rows were inserted to the table. However, 918, how can this be? The clustering factor is supposed to be between the number of table blocks—which indicates a good clustering factor—and the number of index rows—which is the worst case. So, let’s look at the table size:

SQL> SELECT t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE index_name = 'ROW_MIG1_PK';

TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ----------------------- ----------
      100877                     918     100000

SQL>

According to that, the lower bound for the clustering factor is higher than the upper bound. Hmm, let’s investigate the clustering factor manually and verify the distribution of the rows across the table blocks:

SQL> SELECT * FROM (
       SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_number
            , COUNT(*) rows_in_block
         FROM row_mig1
        GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
     ) WHERE ROWNUM <=10;

BLOCK_NUMBER ROWS_IN_BLOCK
------------ -------------
         523           109
         524           113
         525           109
         526           109
         527           110
         536           109
         537           110
         538           109
         539           109
         540           110

10 rows selected.

SQL>

There are 109 different ROWIDs that refer to the block number 523. But we know that each record has about 6k. It is impossible to fit 109 rows into a single block of 8k. However, the “insert empty, update everything” anti-pattern makes it possible. The game goes like this:

The very first row is inserted into a new block.
The first row is updated, and fits into the same block. The free space in that particular block is still more than a kilobyte.
The second row is inserted into the very same block, because there is enough free space available in that block.
The update of the second row triggers the migration of that row to a different block.

The row migration changes neither the ROWID nor the index entry. That means that the forwarding address—that is, somehow, the new ROWID—is stored in the original block so that the index can find the row.
The third row is inserted into the very first table block, again.

Because the second row was moved into a different block, the very first block has still free space. There is only the first row—as a whole—and one forwarding address stored in that block. So, the insert of the third row can take place there.
And so on. Until the forwarding addresses fill the block—up to PCTFREE.

That’s a good one, hmm?

We can even verify that:

SQL> SELECT * FROM (
       SELECT MIN(X)                               min_x
            , MAX(x)                               max_x
            , MAX(x) - MIN(x) + 1                  diff_x
            , DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_number
            , COUNT(*)            rows_in_block
         FROM row_mig1
        GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
        ORDER BY min_x
     ) WHERE ROWNUM <=10;

     MIN_X      MAX_X     DIFF_X BLOCK_NUMBER ROWS_IN_BLOCK
---------- ---------- ---------- ------------ -------------
         1        113        113          524           113
       114        222        109          523           109
       223        332        110          537           110
       333        441        109          538           109
       442        550        109          539           109
       551        660        110          540           110
       661        769        109          541           109
       770        878        109          542           109
       879        987        109          543           109
       988       1096        109          525           109

10 rows selected.

SQL>

You see that the first row was inserted into block number 524. All subsequent rows up to the 113^th were put into the same block. When that block was finally filled up—with one row and 112 forwarding addresses—the game starts over in the next block. All the INSERT statements took place in just 918 distinct blocks. Because the ROWID is assigned during the INSERT, the subsequent migration of the row due to the UPDATE is not reflected in the ROWID.

Neither DBMS_STATS nor ANALYSE TABLE look into the table to check if the row is really there or if the particular block it is just an accumulation of forwarding addresses. From their perspective, all the rows are in the same block—this is how the clustering factor is calculated. Although correctly calculated—technically—and up to date, the clustering factor of this index does not reflect the real situation.

The “correct” value—in that sense that it reflects the data distribution correctly—for the clustering factor would be 100.000; that is, the number of rows. If the clustering factor equals the number of rows in the index—which is the worst possible case—it means that there are no two adjacent index entries that refer to the same table block. This is actually the case because no 8k block can contain two complete 6k rows.

The Clustering Factor is Wrong, So What?

So, what’s the problem if the clustering factor is wrong?

The problem is that the Cost Based Optimizer (CBO) uses the clustering factor in its cost calculation for an INDEX RANGE SCAN (see Fallacies of the Cost Based Optimizer [pdf]). That means, the cost of the INDEX RANGE SCAN will be too low, because the clustering factor is way too low. Luckily there is an effect that makes all that less problematic; all indexes on that table are affected.

Let’s make a second index to verify that:

SQL> CREATE INDEX row_mig1_idx ON row_mig1(filter);

Index created.

SQL> BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1',
                                     CASCADE => TRUE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG1';

INDEX_NAME   TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ------------ ----------------------- ----------
ROW_MIG1_PK        100877                     918     100000
ROW_MIG1_IDX       100877                    9180     100000

SQL>

The clustering factor of the new index is bigger than the one of the original index, but still way off its real value. The “correct” clustering factor for the new index would be 100.000 because there are no two adjacent index entries that refer to the table block. Well, they actually do, but there is nothing more than the forwarding address in those blocks.

Ten Times as High

The clustering factor is ten times as high because the filter column has ten distinct value. That means that the adjacent index entries will refer to ten blocks at least, because those entries were not inserted in the same order as they are stored in the index. On the other hand, the index on the filter column is not only sorted by the filter value, but also by the ROWID—as every nonunique index in Oracle—so that the clustering factor is kept at a minimum. Finally, the clustering factor is ten times as high because it refers to ten times as many table blocks, but does not grow above that because the index order keeps the clustering factor at a minimum.

Although the clustering factor does not correctly reflect the efforts to fetch the table rows, it correctly reflects the relation between the two indexes. The primary key index has a lower clustering factor because it has the same sequence as the table itself. On the other side, the index on the filter column has a different order, thus the value is higher. The side note explains why it is Ten Times as High.

If all indexes are affected, there is hardly any real problem I can see for a single table access. Even queries that can be executed with two different indexes, the CBO will most likely not do wrong because both clustering factors are misleading.

The Join Trap

If it’s not possible to confuse the optimizer with a single table, let’s use more of them. So, I try to build a trap where the optimizer’s decision of the join order is influenced by the phony clustering factor so that the optimizer takes the less efficient execution plan.

For that purpose, I build a second table very similar to the first one. There are only two differences:

I don’t follow the “insert empty, update everything” anti-pattern—there will be no row migration.
The selectivity of the filter columns is slightly increased (about 6% instead of 10%).

Here is the overall script:

CREATE TABLE row_mig2 (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  x NUMBER      NOT NULL,
  filter NUMBER NOT NULL,
  CONSTRAINT row_mig2_pk PRIMARY KEY (x)
) ENABLE ROW MOVEMENT;

INSERT INTO row_mig2 (x, filter, a, b, c)
     SELECT level, trunc(dbms_random.value(0,17)), 'a', 'b', 'c'
       FROM dual CONNECT BY level <= 100000;
COMMIT;

CREATE INDEX row_mig2_idx ON row_mig2(filter);

EXEC DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG2', CASCADE=>TRUE);

Now let’s check the statistics:

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG2';

INDEX_NAME   TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
------------ ------------ ----------------------- ----------
ROW_MIG2_PK        100749                  100000     100000
ROW_MIG2_IDX       100749                  100000     100000

SQL>

Please note that the clustering factor is at the upper bound of the expected range; that means, it indicates that there are no two adjacent index entries referring to the same table block. That’s somehow logical, if we consider that every row is in its own table block.

After that preparation, I can present my “trapQL”:

SELECT *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

It is a rather trivial join on the primary keys. Additionally each table is filtered on the filter column for one particular value. The trap works because a NESTED LOOPS join is possible in both ways. Either by filtering the first table by an INDEX RANGE SCAN on filter and then fetch the corresponding entry from the second by a primary key lookup, or vice versa. However, because we—as well as the optimizer—know that the second table is more selective than the first one, the more efficient way to execute that query is to first perform the INDEX RANGE SCAN on the second table and then join in the first one. In that way, the number of primary key lookups is reduced and the overall performance will be better. Considering that the first table suffers from heavy row migration, that effect becomes even more relevant. However, it is of course the purpose of the discussion to proof that the optimizer is doing “wrong”:

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* original clustering factor */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

And the result is:

577 rows selected.

Elapsed: 00:01:08.35

Execution Plan
----------------------------------------------------------
Plan hash value: 4162018446

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 10941 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 10941 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG1     | 10000 |   938 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG1_IDX | 10000 |    20 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG2_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG2     |     1 |     1 |
---------------------------------------------------------------------

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

   4 - access("D1"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D2"."FILTER"=0)

Statistics
----------------------------------------------------------
        913  recursive calls
          0  db block gets
      35584  consistent gets
      21027  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        577  rows processed

The optimizer has chosen to perform the INDEX RANGE SCAN on ROW_MIG1_IDX first. The optimizer is well aware of the fact that the INDEX RANGE SCAN will return about 10000 rows; still it was preferred over the alternative execution plan.

So let’s check what happens if we tell the optimizer the truth about that table’s indexes?

BEGIN
  DBMS_STATS.SET_INDEX_STATS(null, 'ROW_MIG1_PK', clstfct=>100000);
  DBMS_STATS.SET_INDEX_STATS(null, 'ROW_MIG1_IDX',clstfct=>100000);
END;
/

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* updated clustering factor */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

The only change is that the statistics have been manually updated to the “more correct” clustering factor of 100.000. Unfortunately neither DBMS_STATS nor ANALYZE TABLE can be used for that purpose, so I did it manually. Please note that the table itself was not changed; most of the rows are still migrated.

And the result is:

577 rows selected.

Elapsed: 00:00:59.91

Execution Plan
----------------------------------------------------------
Plan hash value: 3004301745

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 11780 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 11780 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG2     |  5882 |  5896 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG2_IDX |  5882 |    12 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG1_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG1     |     1 |     1 |
---------------------------------------------------------------------

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

   4 - access("D2"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D1"."FILTER"=0)

Statistics
----------------------------------------------------------
        913  recursive calls
          0  db block gets
      20695  consistent gets
      12817  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
        577  rows processed

The more representative clustering factor makes the optimizer take the expected plan. The filtering takes place on the more selective table first, which matches about 5900 rows. The other table is joined in later. The execution is about 13% faster, the logical and physical gets dropped by about 40% both. That makes quite a difference.

The cost of the TABLE ACCESS BY INDEX ROWID that follows the INDEX RANGE SCAN reflects the clustering factor’s impact. The second query plan has a cost of about 5900, that actually means that each fetched row will need a block read. The original execution plan had a cost value of 938 for that step, so that the overall cost value was lower.

After that I must remind the reader that the rows in table ROW_MIG1 are still migrated. The performance difference is not cause by the row migration per se, but by the misleading statistics that result out of the row migration.

Correcting the Row Migration

To complete the exercise, I will correct the row migration, run the statement again, and compare the performance improvement:

SQL> ALTER TABLE row_mig1 MOVE;

Table altered.

SQL> ALTER INDEX row_mig1_pk REBUILD;

Index altered.

SQL> ALTER INDEX row_mig1_idx REBUILD;

Index altered.

SQL> BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(null, 'ROW_MIG1',CASCADE=>TRUE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> SELECT i.index_name         index_name
          , t.blocks             table_blocks
          , i.clustering_factor  index_clustering_factor
          , i.num_rows           index_rows
       FROM user_indexes i
       JOIN user_tables t USING (table_name)
      WHERE table_name = 'ROW_MIG1';

INDEX_NAME     TABLE_BLOCKS INDEX_CLUSTERING_FACTOR INDEX_ROWS
-------------- ------------ ----------------------- ----------
ROW_MIG1_PK          100506                  100000     100000
ROW_MIG1_IDX         100506                  100000     100000

SQL>

Then execute the statement again:

SET AUTOTRACE TRACEONLY;
SET TIMING ON;

SELECT /* no row migration */ *
  FROM row_mig1 d1
  JOIN row_mig2 d2 ON (d1.x = d2.x)
 WHERE d1.filter = 0
   AND d2.filter = 0;

SET TIMING OFF
SET AUTOTRACE OFF

Because the statistics are almost identical, the plan doesn’t change, nor does the cost. What does changed is the execution time as well as the number of logical gets:

577 rows selected.

Elapsed: 00:00:30.90

Execution Plan
----------------------------------------------------------
Plan hash value: 3004301745

---------------------------------------------------------------------
| Id | Operation                     | Name         | Rows  | Cost  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  5882 | 11780 |
|  1 |  NESTED LOOPS                 |              |       |       |
|  2 |   NESTED LOOPS                |              |  5882 | 11780 |
|  3 |    TABLE ACCESS BY INDEX ROWID| ROW_MIG2     |  5882 |  5896 |
|* 4 |     INDEX RANGE SCAN          | ROW_MIG2_IDX |  5882 |    12 |
|* 5 |    INDEX UNIQUE SCAN          | ROW_MIG1_PK  |     1 |     0 |
|* 6 |   TABLE ACCESS BY INDEX ROWID | ROW_MIG1     |     1 |     1 |
---------------------------------------------------------------------            

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

   4 - access("D2"."FILTER"=0)
   5 - access("D1"."X"="D2"."X")
   6 - filter("D1"."FILTER"=0)

Statistics
----------------------------------------------------------
        925  recursive calls
          0  db block gets
      14271  consistent gets
      11952  physical reads
          0  redo size
      39012  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
         40  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
        577  rows processed

Summary

The article investigates the effects of row migration on the clustering factor and the optimizer. A “proof of concept” SQL demonstrates that row migration can affect the optimizer. The lessons from this article are:

The “insert empty, update everything” pattern can lead to a very high row migration rate.
DBMS_STATS doesn’t populate the CHAIN_CNT.
A high row migration rate can cut the clustering factor, even below its theoretic minimum.
A wrong clustering factor can affect the optimizer and result in an suboptimal plan.

Disclaimer

Please note that this trap was intentionally built, just to proof that row migration can potentially influence the optimizer. There are better ways to tune that particular SQL, foremost a better indexing approach.

I have put many adjectives in quotation marks because they are not in line with the technical definition of the respective noun.

The “insert empty, update everything“ anti-pattern was used to create a very high row migration rate. Although that anti-pattern can lead to 100% migration rate, it does not always.

I have successfully verified my results on Oracle 10gR1, 11gR1 and 11gR2.

Thanks

Thanks to the guys at 25th-floor who verified my results on Oracle 11gR1.

Row Migration and Row Movement

Markus Winand — Tue, 23 Feb 2010 02:52:34 PST

The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.

Luckily all three are well described in excellent articles: The Secrets of Oracle Row Chaining and Migration and Row Movement in Oracle.

For the impatient, I provide some very short definitions:

Row Chaining: Distribution of a single table row across multiple data blocks.
Row Migration: Relocation of an entire table row to a new place, without updating the indexes.
Row Movement: Relocation of an entire table row to a new place and updating the indexes.

This article was inspired by the question if Oracle 11r2 performs Row Movement instead of Row Migration for ordinary UPDATE statements—that is, in absence of partitions. The short answer is: no, it doesn’t. The long answer is the rest of this article.

The difference between Row Chaining and Row Migration is somehow understandable: If the row doesn’t fit into a single data block, it must be chained; otherwise it can be migrated as a whole. The limitation of the Row Migration is that it does not update the indexes on the table. That means, the ROWID that is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

The more modern Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation. On the short run, the actual UPDATE operation is much more complex. I suppose this is the reason why a regular update does not (yet) trigger a Row Movement.

The Row Movement was introduced to support an UPDATE on a partition key—so that a table row is moved from one table partition to another one. In the meanwhile it is also used for flashback and space management—as described in the above-mentioned article.

As of Oracle Release 11g2, Row Movement is optional and disabled per default. It can be enabled per table with a very trivial ALTER TABLE statement. The usual reason to enable it is that one of the features which require Row Movement is used; partition key update, table shrink and flashback. There is hardly any reason not to enable row movement—the only side effect is that ROWID’s might change; that should not affect well designed applications.

Row Migration has some considerable problems, as pointed out in the later in this article. On the other hand, Row Movement has also a drawback; that is, the update of all the affected indexes can be very expansive. It’s a trade off between read and write performance. While Row Movement is more expansive for UPDATES, it maintains best index performance. In contrast, the Row Migration favors UPDATE performance over index speed. Although not true for all cases, I believe that most data is read more often than it is written—especially in our modern society where nobody every deletes data—so that Row Movement is generally the better choice.

The article examines how to get 100% migrated rows with the “insert empty, update everything” anti-pattern, why DBMS_STATS isn’t a perfect substitute for ANALYZE TABLE, the immunity of migrated rows to alter table ... shrink space and why PCTFREE is still the only rescue from DBAs perspective.

Just to show what I mean, I repeat a modified version of the test originally created by Tom Kyte and reused by Martin Zahn:

CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement
/

I have changed the size of the CHAR field to 2k because my block size is 8k whereas it was 4k in the original example. I have also put the x column to the end for a better verification of Row Migration versus Row Chaining. Finally I enable Row Movement, which does actually not change anything but allows me to perform an alter table ... shrink space.

The next step is a classical insert empty, update everything sequence:

INSERT INTO row_migration_demo (x) values (1);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=1;
COMMIT;

Up till now everything is perfectly fine and the entire row is in a single block:

SQL> select * from row_migration_demo where x=1;

         X
----------
         1

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            0

So, lets do it again and insert a second row:

INSERT INTO row_migration_demo (x) values (2);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=2;
COMMIT;

And select it again:

SQL> select * from row_migration_demo where x=2;

         X
----------
         1

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            1

Hooray, the row is migrated:

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         2          1

Note: ANALYZE is deprecated. Yup, I know, but DMBS_STATS.GATHER_TABLE_STATS does not propagate the CHAIN_CNT column. Ask Tom!

It’s hard to figure out if the row is chained or migrated—I am actually not sure is there any difference in the data structure. Because the additional table fetch continued row occurs for the very first column, I believe the row is migrated:

SQL>  select length(a) from row_migration_demo where x=2;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

To be on the safe side, I will double verify:

INSERT INTO row_migration_demo (x, a) values (3, 'a');
UPDATE row_migration_demo
   SET b = 'b', c = 'c', d = 'd', e = 'e'
 WHERE x=3;

Indeed, the a column is at the place where the row was inserted while the e column is somewhere else:

SQL>  select length(a) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

SQL>  select length(e) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            3

To make things even worse, single rows can be migrated and chained. If we insert two more records, it becomes visible:

INSERT INTO row_migration_demo (x) values (4);
UPDATE row_migration_demo
   SET a='a', b='b', c='c', d='d', e='e'
 WHERE x=4;

INSERT INTO row_migration_demo (x) values (5);
UPDATE row_migration_demo
   SET a='a', b='b', c='c', d='d', e='e'
 WHERE x=5;

COMMIT;

Each of those rows must be chained, because they need more space than a single block has. However, due to migration, the row is actually distributed to three blocks:

SQL>  select length(a) from row_migration_demo where x=5;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            4

SQL>  select length(b) from row_migration_demo where x=5;

 LENGTH(B)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            5

SQL>  select length(c) from row_migration_demo where x=5;

 LENGTH(C)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            7

Please note that the “continued fetch” increased by two for the select on column c . The statistics show that all but one row is chained (the very first row):

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         5          4

If we continue the game and continue to insert data in this way, we get an astonishing chain count:

begin
   for i in 10..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

All the inserted rows are chained.

The only way to clean up the mess is to move the table. Shrinking doesn’t help to much, especially not if there was no DELETE or UPDATE that has freed some space:

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

Even if some space is freed and rows are moved, the chaining is not notably reduced:

SQL> delete from row_migration_demo where x < 200;

195 rows deleted.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        801

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        800

Please note that the shrink has corrected one chained row. That proofs that a Row Movement assigns a new ROWID and updates the index. However, one “unchained” row isn’t really the correction I would like to see. The only way to correct all chains, is to mote the table:

SQL> alter table row_migration_demo move;

Table altered.

SQL> alter index row_migration_pk rebuild;

Index altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801          0

No chaining anymore.

There is actually another way to correct row migration. The Oracle documentation suggest to delete and re-insert all affected rows. Watch out for your triggers.

From administrators‛ perspective, the only way to prevent row migration in the first place is to increase PCTFREE. The complete re-execution of this test with PCTFREE increased to 50% “solves” the problem and no chained rows occur anymore:

drop table row_migration_demo;
CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement pctfree 50
/
begin
   for i in 1..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/
commit;
analyze table row_migration_demo compute statistics;
select num_rows, chain_cnt
       from user_tables
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
      1000          0

If this is not acceptable, there is one very last option: change the application.

However, it would be very nice if an operation—similar to shrink—would move migrated rows. It would be even more compelling if a table option would allow to disable Row Migration in favor of Row Movement.

UPDATE 2010-03-09: A follow up article describes the impact of Row Migration o the Clustering Factor.

Appendix

After some years of professional experience, I always wonder why a particular idea of mine should be unique. So I suspected that this feature might be there, somewhere hidden in Oracle. So I checked for hidden parameters (thanks to a little documentation on that topic):

SQL> select a.ksppinm parameter, b.kspftctxvl
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn)
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           FALSE

SQL> alter session set "_disable_implicit_row_movement" = true;

Session altered.

SQL> select a.ksppinm parameter, b.kspftctxvl
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn)
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           TRUE

SQL>

As suggested by the name of the parameter, this doesn’t change anything in the direction I would like.

Oracle Trace File Rotation

Markus Winand — Mon, 01 Feb 2010 09:30:11 PST

Under very rare circumstances, I need Oracle SQL trace files from a long period of time. Because trace files usually grow large—especially over several days—there is the need to rotate the trace file during that time so that they can be compressed and put away. The problem is that there is no “rotate tracefile“ button in Oracle. However, I have found a “undocumented feature“ that does exactly that—without disabling tracing.

My procedure uses the close_trace call of oradebug. This call closes the currently written trace file for a session. Alex Gorbachev has used this to delete big trace files that are still open. My procedure goes a little bit further and exposes one more side effect of oradebug close_trace.

Warning: The technique described here uses the undocumented and unsupported oradebug facility. Although I am not aware of any negative side effects of this procedure, the use of this method takes place at your own risk.

The oradebug utility can be used from the SQL*Plus prompt. If you have an account with sufficient rights (sigh) you can attach to any session using the oradebug setorapid command. To obtain the required Oracle PID, just query the v$process view:

SQL> SELECT s.sid, s.serial#, p.pid 
       FROM v$session s, v$process p
      WHERE s.paddr=p.addr;

       SID    SERIAL#        PID
---------- ---------- ----------
         1          1          2
[... skipped ...]
        20       3126         21

26 rows selected.

SQL> oradebug setorapid 21
Oracle pid: 21, Unix process pid: 145, image: oracle@test.fatalmind.com
SQL>

Once attached to a specific process, every oradebug operation is applied to that particular process. The trick to rotate the trace file is very simple and exploits a “feature” of UNIX file systems; that is, that the name of the file is used only at the moment the file is opened. Once the file is open, all access is managed with the so called inode and the name is not relevant for the read and write operations anymore. The inode system has some more implications, one of them is frequently used to rotate log files without data loss. Another implications makes it hard to delete open files, as Alex Gorbachev explained.

The log rotation trick is to rename the open file and then cause the writing process to re-open that file. All write operations to the renamed file will be written properly to new file name—more correctly expressed: to the file which can be accessed under the new name. If the writing process re-opens the file under its original name, it will create a new file because there is no file with that name anymore. Every subsequent write operation goes to the new file. The original file will not grow anymore and can be taken away safely.

Although the oradebug command name close_trace doesn’t suggest that the trace file is reopened, it is—if SQL tracing is enabled. So we have everything to rotate a trace file. Consider the following example:

$ ls -lrt TEST_ora_14457.trc*
-rw-r----- 1 oracle oinstall 14758723 2010-02-01 TEST_ora_14457.trc
$ mv  TEST_ora_14457.trc  TEST_ora_14457.trc.old

After renaming the file, the file still grows as the SQL trace is written. You can now use oradebug close_trace to actually rotate the file:

SQL> oradebug close_trace
Statement processed.
SQL>

The verification in the file system shows that the ‘old’ file became bigger, and the new file is also filling up:

$ ls -lrt TEST_ora_14457.trc*
-rw-r----- 1 oracle oinstall 18878704 2010-02-01 TEST_ora_14457.trc.old
-rw-r----- 1 oracle oinstall  1555972 2010-02-01 TEST_ora_14457.trc
$

Because I often need to trace many sessions, I wrote a very tiny script (switch_traces.sql) that creates another script to re-open all trace files:

set feedback off
set pages 0
set heading off
set echo off

spool oradebug_close_traces.sql

select cmd from (
select pid, 1 o, 'oradebug setorapid ' || pid cmd from v$process
UNION ALL
select pid, 2 o, 'oradebug flush' from v$process
UNION ALL
select pid, 3 o, 'oradebug close_trace' from v$process) order by pid,o;

spool off
set echo on
set feedback on
set pages 1000
set heading 0n

prompt
prompt c&p >>> @oradebug_close_traces.sql <<< to switch traces

This script will close all traces in all sessions and continue tracing to new files if SQL tracing is enabled. You might want to add additional where clauses to limit your scope—e.g., to skip background processes. The script will also issue a flush before the close_trace. I don’t know if this is required or not, it doesn’t seem to make any harm. The use of the script is very simple:

SQL> @switch_traces.sql
SQL> set feedback off
SQL> set pages 0
SQL> set heading off
SQL> set echo off
oradebug setorapid 1
oradebug flush
oradebug close_trace

[... skipped ...]

oradebug setorapid 29
oradebug flush
oradebug close_trace
SQL> set feedback on
SQL> set pages 1000
SQL> set heading on
SQL>
SQL> prompt

SQL> prompt c&p >>> @oradebug_close_traces.sql <<< to switch traces
c&p >>> @oradebug_close_traces.sql <<< to switch traces
SQL> @oradebug_close_traces.sql
SQL> oradebug setorapid 1
ORA-00072: process "Unix process pid: 0, image: PSEUDO" is not active
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.
SQL> oradebug setorapid 2
Oracle pid: 2, Unix process pid: 137, image: oracle@test.fatalmind.com
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.

[... skipped ...]

Oracle pid: 29, Unix process pid: 138, image: oracle@test.fatalmind.com
SQL> oradebug flush
Statement processed.
SQL> oradebug close_trace
Statement processed.
SQL>

In case you have not forgotten to rename your trace files you will see the new files growing now.

Read also my previous article To Trace or Not to Trace for information about fine grained SQL tracing.

Oracle JDBC PreFetch Portability

Markus Winand — Fri, 29 Jan 2010 06:23:11 PST

In a previous article about (network) latencies, I have presented the OracleStatement.setRowPrefetch() method to reduce round trips for SELECT statements. I must admit that I was a little bit wrong. Not in the essence; increasing the PreFetch size is still a great (read: simple) way to reduce latencies. However, there is a better approach to set the PreFetch size.

Since the raise of Java, many portability issues have vanished. There are most definitely new ones—think of mobile devices—and some others are still eminent—think of Internet Explorer versus all other browsers. Nevertheless, Java improved the situation quite notable—most credible: Unicode support from the beginning.

Still there are some people like me who just cast the portable JDBC class to whatever they believe it is, and do some cool things with that. Every now and then there is the need to use proprietary functions, but I should have checked the portable API first. Shame on me. In fact there is the standard JDBC method Statement.setFetchSize() that does exactly the same. Even more interesting, it’s there since ages (JDBC 2.1 at least).

Although the documentation seems to be exactly what I was looking for, I wrote a little test program to verify. Just because I know that Oracle can profit from an increased PreFetch size and the standard provides a method to set it, it doesn’t mean that it works. Especially not if the standards says that the method is a hint to the JDBC driver. So let’s double check.

import java.sql.*;
import oracle.jdbc.*;

class JDBCFetchSize {
  public static void main(String[] argv) throws Exception {
    if (argv.length != 4) {
      throw new RuntimeException("JDBCFetchSize <jdbc-connect url> "
                               + "<username> <password> <FetchSize>");
    }
    Connection con = connect(argv[0], argv[1], argv[2]);
    int roundtripoffset = getRoundTripOffset(con);

    PreparedStatement ps = con.prepareStatement(seqsql);

    setFetchSize(ps, argv[3]);

    int start = getCurrentRoundTripCount(con);
    long startts = System.currentTimeMillis();

    int rowsfetched = executeTest(ps);

    int end    = getCurrentRoundTripCount(con) - roundtripoffset;
    long endts = System.currentTimeMillis();

    ps.close();

    System.out.println(rowsfetched + " rows fetched in "
                       + (end-start) + " server round trips and "
                       + (endts-startts) + " ms");
  }

  // oracle SQL to fetch round trip counter
  final static String rtsql =
      " select value "
    + "   from v$mystat ms, v$statname sn"
    + "  where ms.value > 0"
    + "    and ms.statistic#=sn.statistic#"
    + "    and sn.name IN ('SQL*Net roundtrips to/from client')";

  // sequence generator
  final static String seqsql =
      " select level from dual connect by level <= ?";

  static PreparedStatement rtstm;

  private static Connection
    connect (String url, String user, String pass)
    throws ClassNotFoundException, SQLException
  {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    return DriverManager.getConnection(url, user, pass);
  }

  private static int
    getRoundTripOffset(Connection con)
    throws SQLException
  {
    // The very first time we call it, it is not prepared.
    // so we don't consider that at all.
    getCurrentRoundTripCount(con);

    int first = getCurrentRoundTripCount(con);
    return getCurrentRoundTripCount(con) - first;
  }

  static void
    setFetchSize(Statement stm, String size)
    throws SQLException
  {
    System.out.println("getFetchSize(): " + stm.getFetchSize());

    stm.setFetchSize(Integer.parseInt(size));

    // uncomment the not portable OracleStatement
    // variant for verification
    // ((OracleStatement)stm).setRowPrefetch(Integer.parseInt(size));

    System.out.println("getFetchSize(): " + stm.getFetchSize());
  }

  static int
    executeTest(PreparedStatement ps)
    throws SQLException
  {
    int cnt = 0;
    ps.setInt(1, 100000);
    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
      ++cnt;
    }
    rs.close();
    return cnt;
  }

  static int
    getCurrentRoundTripCount(Connection con)
    throws SQLException
  {
    if (rtstm == null) {
      rtstm = con.prepareStatement(rtsql);
    }
    ResultSet rs = rtstm.executeQuery();
    rs.next();
    int rv = rs.getInt(1);
    rs.close();

    return rv;
  }
}

The result proofs that the portable JDBC setFetchSize() is the better choice over Oracle’s proprietary setRowPrefetch():

$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 10
getFetchSize(): 10
getFetchSize(): 10
100000 rows fetched in 10002 server round trips and 521 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 20
getFetchSize(): 10
getFetchSize(): 20
100000 rows fetched in 5002 server round trips and 324 ms
$ java JDBCFetchSize jdbc:oracle:thin:@//host/service user password 50
getFetchSize(): 10
getFetchSize(): 50
100000 rows fetched in 2002 server round trips and 191 ms
$

Calling setRowPrefetch() affects the number of server round trips as expected.

And because consistency (=Quality) matters, I will right away update the previous article.

Recent oracle docs list some limitations.

After all, there is still some room for portability pitfalls. If you want to change the default value from 10 to some other value, Oracle has two solutions for that. Read again: Oracle has two solutions for that. JDBC has none (correct me if I am wrong).

When passing Properties to the DriverManager.getConnection() method, Oracle allows to set the defaultRowPrefetch property. The other way is to cast the JDBC Connection to OracleConnection and use the setDefaultRowPrefetch() method (hurray — cast again!). Disclaimer: I did not test any of those.

Please note that my tests were based on Oracle 11r2, other release might behave differently. During my research I found some Oracle 8.1.6 docs which also mention the portable setFetchSize() method, that makes me believe it works since then.

Latency: Security vs. Performance

Markus Winand — Tue, 22 Dec 2009 00:01:53 PST

At a client’s Christmas party, I have witnessed a very short talk between a network engineer and a top level manager. The network guy explained that the firewall adds about 0.2 milliseconds latency to each round trip between the application server and the database, which adds up to some hours in one particular case. So the manager asked what could be done and the network guy quickly provided two solutions:

Change the application to make less round trips
Accept the security risk and don’t put a firewall in between those two tiers

Funny enough the network guy explained that the second option needs the managers signature because it bypasses the corporate security guidelines and somebody must take the responsibility for that.

Consider you are the manager, would you sign such a paper?

I guess I would not, and I also guess my client didn’t (otherwise it was against my advice ).

In this article I will explain the term latency, give some insight why it is so easy to build latency bandit applications and show some ways to reduce latencies.

Weekend Shopping

The best explanation for latency I ever came across is from the guys over at RoughSea. There is a video that describes shopping like that (simplified):

Drive to the super market
Locate what’s needed (e.g., milk)
Pay
Store the item in the car
Drive back home
Store the item (e.g., fridge)
Then start again for the next item on the shopping list (e.g., corn flakes).

The whole 20 minutes video is a great watch and covers many other frequent issues as well. If you just want to watch that particular scene, here is a direct youtube link.

When watching this video it’s hard not to D’oh! The video makes it very obvious that most of the time is spent to drive. The actual shopping takes much less time than all the overhead for driving, parking, paying,….

Latency in software has very similar causes, most prominently that the application asks the database for many items individually.

How Can This Happen?

This question has actually two sides:

How can somebody implement software that way?
How can a latency of 0.2 milliseconds make any difference?

First of all let’s look at the 0.2 milliseconds statement again. This value was provided by the network engineer as additional latency because of the new firewall. That’s on top of the latency the network anyway, even without firewall. A typical value for a switched LAN, without firewall, is around 0.1 – 0.2 milliseconds. If a firewall adds 0.2ms to that, the overall latency raises to about 0.4ms per round trip. In other words 2500 round trips take a second. Although this sounds a lot, it’s actually not so much if you consider how many round trips take place for an average operation. A average latency of 0.4 milliseconds is still very reasonable and will cause problems only in extreme cases. Nevertheless latencies can grow because of increased network complexity or during peak load.

The situation becomes more relevant because one round trip is hardly enough to perform any reasonable operation. Realistically, an application issues many different SQLs to accomplish a single task. A single SQL statement might also need multiple round trips to complete; a SELECT statement against an Oracle database needs typically two round trips, as explained farther down the article. An application that executes 10 SELECTs has already 8ms of latencies. More complex applications might execute 100 SQL’s and reach 80 milliseconds of latencies. I have seen batch jobs that issue 50 million SQLs so that the round trip latency sums up to 10 hours or more!

So, why! Why is it done that way? Because it is easier to implement. Therefore faster to implement and less error prone. Consider the shopping example again. The algorithm is very simple; just repeat all steps until the shopping list is done. This works irrespective of the size of the shopping list.

An improved variant of the algorithm, that is latency aware, might look like this:

Drive to the super market
FOR EACH ITEM: Locate item (milk, corn flakes)
Pay
FOR EACH ITEM: Store into the car
Drive back home
FOR EACH ITEM: Store the item (e.g., fridge, larder)

Please note that what has been one loop over the complete algorithm before, was turned into three loops somewhere inside the algorithm. The complexity has grown. Even worse, there are more complex error scenarios with the new algorithm. E.g., a very long shopping list might exceed the capacity of the car to transport all the items. Such scenarios need special error handling. Alternatively the algorithm could prevent that scenario by splitting up large purchases into multiple smaller parts. The additional complexity requires more time to implement and introduces more scenarios to test. The efforts quickly explode, especially since the overflow scenarios are hard to test.

One more word of warning: The case that your shopping doesn’t fit into your car might look very unlikely, and perhaps it is. However, in computer programs, there a less natural limits than in real life—e.g., what your family can eat. From personal experience I would say chances are 2:1 to hit this error scenario within the first year that implementation is used.

After all, there is a trade off between software complexity and software performance. From another point of view, it is even a trade off between software development speed and software runtime speed. If you are pushed to deliver working software, better sooner than later, which algorithm would you implement?

Finally, there is also a reason why latency bastard applications can be produced by accident:

But it works in development/test environment

Very often, development takes place on desktop hardware where everything is installed on the same machine. Obviously, there is no firewall in place nor is there any network involved. The involved latency in such an environment is only a fraction (e.g., a 10th) of the latency involved in an n-tier production deployment. In the shopping example, this would mean that the super market is in the room next to the kitchen. Item-by-item shopping might work fine in that case. Unfortunately this type of shopping infrastructure is not yet commonplace.

How to Avoid it

So let’s look into the more technical part of the story. Which techniques can be used to reduce the number of database round trips? The following list gives the three most effective ways to avoid unneeded latency:

Use Joins
Use array/batch functionality
Use advanced techniques (open cursors, fetch buffers, bulk/implicit commit, …)

Join

The most important technique to avoid latencies is to tell the database which data is needed at once. A very problematic implementation is to query one table and issue another select within the fetch loop of the first statement. I refer to this method as nested select as opposed to a nested loop join. Both methods implement the same idea while the nested select involves additional round trips. With SQL, it is always possible to construct a join that delivers the same result within the same time—but usually much faster.

The nested select anti-pattern can be applied to any extend. They can be nested several levels deep, or kept at one level that triggers more than just one nested select. I have mentioned the 50 million SQL batch job that took more than 10 hours, just for latency. This particular job has queried some 10 additional child tables for each record fetched from the main query. The main query has returned some million records so that the latency became the most dominant performance factor. In that particular case, the developers explained that the coding guidelines force them to use the existing functions instead of writing new SQL. Another difficulty was that the nested statements were depended on the data from the main table so that a trivial join doesn’t work (conditional join). In fact there were only three different ways to join the tables, therefore the correct solution is to make three different SQLs. In case a single result set is needed (e.g., because of a order clause), a UNION can be used. After all, 10 out of 12 hours overall run time was saved just by using joins.

Batch Execution

The second major latency reduction technique is to properly use batch execution that is sometimes also called array execution. Many databases provide a way to execute more than one statement in one round trip. Not all APIs make this functionality available to the client, but JDBC does. However, there are some pitfalls:

Error handling is terrible.
Although the API defines methods to identify which statement failed, the API is very cumbersome to use. To make things worse it’s not even defined if the execution of the batch stops on errors. My Best Current Practice is to set a Savepoint prior array execution. In case an error occurs, a rollback to the Savepoint can be done.
Very large batches.
Very large batches can have their own problems. As with the shopping example, every implementation must be prepared to use multiple batches in case the number of executions becomes very high.
Does not always work faster.
The actual performance gain is vendor specific and, more importantly, depends on the method used. For example, Oracle benefits from batching only if PreparedStatements are used. Batching Statement objects doesn’t give any performance gain with Oracle. On the other side, MySQL can also benefit from Statement batches. As always, the advice is to use PreparedStatements whenever possible. This will almost always result in major performance improvements.
The JDBC API is optional.
Prior using that API you must check if the driver supports it. Realistically I would suggest to build an abstraction that takes care of this and executes the statements individually if batch execution is not support. Ideally the JDBC driver would do so.

These are important issues, that cause considerable efforts, to take care of when using batch execution. Still all the efforts can easily pay off. In many cases the execution of a PreparedStatement batch with two elements takes no longer than the execution of a single statement, so that the execution is effectively twice as fast. For trivial SQL statements, a speed improvement of factor 10 is within easy reach. That justifies some efforts.

Advanced Techniques

Besides the two most powerful techniques explained above, there are many more ways to reduce latency. They can be classified in two different types: SQL improvements or API improvements.

Let’s start with SQL improvements.

Join.
Again, just as a reminder. The most powerful way to reduce latencies.
IN clauses
Consider to use an IN clause to fetch multiple records form the same table in one run. Unfortunately, the SQL language doesn’t guarantee that the order of the result sets corresponds to the IN clause order. An additional layer (e.g., HashMap) might be used to handle that issue. Another potential problem is that the number of items in the IN list has a maximum—1000 in Oracle: ORA-01795. However, if the list can grow that big, chances are good that the list itself was retrieved from the database. In that case, a join might be the better solution—just to mention it once more.
UNION
If data from two different tables is needed that have the same structure, you can use a UNION to fetch both at once. For example a CURRENT and a HISTORY table might contain the life-cycle of a row. To obtain the current and all past versions, both tables must be queried. Another examples are pre-calculated aggregates; one table contains all the aggregates as of yesterday and a second table all the new records since then. To get the total figure—as of “now”—both tables must be queried.
RETURNING clause.
If you use a surrogate primary key that is generated from a sequence, you must usually perform two steps to fetch the surrogate key. The least troublesome solution is to first SELECT the new ID and the perform the INSERT with that ID. The Oracle answer to this problem is the RETURNING clause of the INSERT statement. Although the JDBC API acknowledges the problem since version 3.0 the Oracle implementation is not very useful—it returns the ROWID. A workaround is available.
1×1 Cartesian product or CROSS join

The so called Cartesian production (everything with everything) is sometimes used accidentally and results in huge result sets. That’s the reason why SQL-92 introduced a distinct keyword for it: CROSS. On the other side, the Cartesian product of two single row statements is, again, a single row:

SELECT a.*, b.* FROM a CROSS JOIN b WHERE a.id=1 AND b.id=2

This technique can be used to combine multiple single-row statements into a single serve round trip. Please note that this works only if both statements return exactly one row, not more, not less. This is more often a last resort option than a first choice.

These are the typical SQL based techniques to reduce the number of individual SQLs executed that in turn reduces the number of round trips. Another strategy to reduce the number of round trips is to use the API differently:

Batch execution.
Sorry for the repetition, but it’s the most important technique.
Open cursors
When executing the same PreparedStatement multiple times in a row–with different bind values—the same PreparedStatement instance should be used for all executions. The first execution of a Statement involves an additional round trip to open the cursor. This additional round trip can be avoided when the same PreparedStatement instance is used again. As a side effect, PARSING overhead is also reduced.
Increased PreFetch
The Oracle network layer utilizes a technique called PreFetch to reduce round trips for SELECT statements. Instead of retrieving every single row individually from the server, a certain number or rows is transferred in one round trip. The Oracle client library handles this PreFetch transparently and still provides one row after each other to the application software. For SELECT statements, PreFetch is almost as powerful as batch execution for INSERT/UPDATE/DELETE statements and can reduce the latencies by an order of magnitude for large result sets. The current default for the Oracle JDBC driver is to fetch at most 10 rows in each server round trip. The JDBC standard provides a way manipulate this value: Statement.setFetchSize(20); If your application typically fetches 20 records, you might set the value accordingly. There is no (notable) harm for statements that return less than 20 records.
UPDATE 2010-01-29: This paragraph was updated to reflect my later findings regarding Oracle JDBC PreFetch Portability.

The following chart shows various select executions with different PreFetch sizes. The first pair is a mass select to retrieve 40000 rows from the database. Fetching twice as many records in one round trip, cuts the overall time down to the half. The other bars show the execution of a statement that returns 20 rows. To get reasonable values those statements were executed 1000 times in a row. The repeated execution allows the open cursor optimization to be applied as well. The middle pair of bars shows the execution performance with a PreparedStatement that is closed after each execution. The last bars show the same but the PreparedStatement was opened only once and used then for all 1000 executions.

The latency difference between 10 records PreFetch with a “closed cursor” to the 20 records PreFetch with an “open cursor” is 70%.

Please note that the statement used for this statistics was very trivial and can be assumed to be executed instantly. The improvements visible in the chart above do therefore indicate the maximum possible improvement.

The Bottom Line

Increasing network complexity introduces additional latencies that might affect applications performance.

There are many ways to reduce server round trips to make an application less sensitive to network latencies. Most of them involve revised process flows and increase application complexity.

“Firewall qualified” applications already have an advantage in competition and attest awareness for complex production deployments.

It’s not fatal to open your mind to new approaches.

How Internet Works

There is another excellent cartoon that explains latency in context of the Internet.

To Trace or Not to Trace

Markus Winand — Tue, 24 Nov 2009 09:49:44 PST

I have always been a fan of the oracle SQL trace facility. It’s a very powerful method to analyze performance problems, especially if you don’t know the application very well. If you don’t know oracle SQL tracing yet, you will find some info at orafaq and some more details at orable-base. Obviously, Oracle has also some documenation about it.

In short, the Oracle SQL tracing facility causes the database server to write all executed SQL statements to a file for later analysis. One of the main issues with SQL tracing is the huge size of the trace files; every statement execution is logged, potentially with additional data about the execution plan, wait events, and bind values.

This article introduces the new possibilities of Oracle 10g to enable tracing on a very fine level. With a properly instrumented application, tracing can be enabled for specific users or program modules only. With such a fine control over the tracing, the generated data volume can be kept small and SQL tracing becomes a much more useful tool.

The first part of the article explains how a Java application can be instrumented to allow fine-grained tracing while the second part shows the administrative interface to enable tracing for specific modules.

Instrumenting the Application

First things first. Before the database can selectively trace for specific users or modules, the application must tell the database to which user or module an actual SQL statement belongs.

Luckily we don’t have to explicitly specify this for each individual statement. It’s more like a session property that can be set at any time. Once set, it affects all SQLs until a different value is set.

The three values that affect tracing are CLIENT_IDENTIFIER, MODULE and ACTION. Those must be properly set in order to allow selective tracing. With properly I mean two aspects:

Allow meaningful filtering
CLIENT_IDENTIFIER can be used freely. MODULE and ACTION depend on each other; it’s not possible to trace a specific action in every module. In other words, a MODULE is a container for ACTIONS.
Don’t introduce a performance impact

The Oracle database provides a way to set the CLIENT_IDENTIFIER, MODULE and ACTION without doing an additional server round-trip. The information is sent piggyback with the next round-trip. You must take care to use the proper implementation to not introduce an additional server round-trip that would introduce a major performance impact.

UPDATE: The issue about server round tips is also the topic of later article: Latency: Security vs. Performance.

The Required JDBC API

Since all of the described functionality is an oracle specific extension, the JDBC standard doesn’t provide an API to set the required attributes. To access that functionality you must cast the standard JDBC Connection to an OracleConnection object first. That’s all, the OracleConnection class provides the two methods we need:

The naming might confuse in the first place, but when you consider that the actual identifier for a real human being is intended to be put into the CLIENT_IDENTIFER field, it might become clear that this is really for end-to-end measurements.

The actual data is passed as an String array that contains an entry for the properties we are interested in —along with others. You can get all of them and provide new ones. Remember that neither getEndToEndMetrics() nor setEndToEndMetrics() causes a server round trip. In case you change any property, the change is sent piggyback with the next server round-trip. I have used the following class for my tests:

class OracleEndToEnd {
 String oldData[];
 OracleConnection ocon;

 public
   OracleEndToEnd(Connection con, String module,
                  String client , String action)
   throws SQLException
 {
   if (con instanceof OracleConnection) {
     ocon = (OracleConnection)con;
     oldData = ocon.getEndToEndMetrics();
     if (oldData == null) {
       oldData =
          new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
     }

     String[] metrics =
        new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];

     int minidx = (metrics.length < oldData.length)
                                  ?
                   metrics.length : oldData.length;
     System.arraycopy(oldData, 0, metrics, 0, minidx);

     if (action != null) {
       metrics[OracleConnection.END_TO_END_ACTION_INDEX] = action;
     }
     if (client != null) {
       metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = client;
     }
     if (module != null) {
       metrics[OracleConnection.END_TO_END_MODULE_INDEX] = module;
     }
     ocon.setEndToEndMetrics(metrics, (short) 0);
   }
 }

 public void
 endSection()
 throws SQLException
 {
   if (ocon != null) {
     ocon.setEndToEndMetrics(oldData, (short) 0);
   }
 }
}

This class is intended to be used as wrapper around a specific section of your code (not to say module or action) like this:

OracleEndToEnd oee = new OracleEndToEnd(con, "MOD1", null, "ACT1");
try {
   selectIt(con);
} finally {
   oee.endSection();
}

When done carefully, a hierarchical structure of MODULES and ACTIONS can be build. This code was of course only my proof-of-concept code.

To instrument your existing application, you should look out for central places where you can add the code as shown above. In example, many applications use a database connection pool explicitly. By instrumenting the pool and passing additional parameters—like CLIENT_IDENTIFER and MODULE—to the get()/borrow()/whatever() method, much of the overhead can be centralized.

The best way to instrument your application depends to the frameworks in use. That’s the time to mention that I am consultant, let me know if I can help you

Performance Comparison

I have written an example Java implementation—on basis of the class presented above—to check the performance impact of a properly instrumented application. To make the long story short: in a networked environment there isn’t any considerable impact when properly instrumenting a Java application. In case the application and the database are hosted on the same node, if no network involved, the impact might become noticeable (8% in my experiment), but this was still an extreme case. Generally I believe that a proper instrumentation does not impact performance in a noticeable way.

For my performance experiment, I used a program that issued the same SQL statement many thousand times. To keep the execution time of the statement low, I have used a very trivial statement:

select 1 from dual

I have executed the program with and without instrumentation for the following scenarios:

Without any tracing enabled
With full tracing enabled (trace everything)
Trace only every second SQL execution (only with the instrumented code)
Trace only 1% of the executed statements (only with the instrumented code).

The results are in the chart in a networked environment—application and database hosted on different nodes:

There is NO measurable impact when tracing is disabled. This is the most important aspect. You can just add the needed CLIENT_IDENTIFIER, MODULE and ACTION and it will not harm performance—when done properly. When tracing is enabled, there actually IS a performance drawback. I am not sure where this comes from; I could imagine that the additional data written into the trace file—module name and so on—cause the regression . However, that impact is still minor, 5% in my extreme test scenario. For test case 3 and 4 there is no direct comparison because partial tracing requires the instrumented code. The checked bars are the results of the code without instrumentation but full tracing enabled—the only way to gather similar SQL trace files. You see that the performance impact relates to the traced volume. When tracing only a very small fraction of the overall code the impact is hardly measurable.

All of this requires a proper instrumentation of the application not to cause additional round trips.

The Administrative Interface

With Oracle 10g, yet another way to enable tracing is provided in form of the DBMS_MONITOR package. This package adds some great tools to perform SQL Tracing in a real world scenario.

Instead of

ALTER SESSION SET SQL_TRACE=TRUE;

ALTER SESSION SET EVENT='10046 trace name context forever, level 4'

we can now use

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(binds=>true);

Which is actually the same in pink—except the less cryptic syntax—no benefit. Very often we want to enable tracing from outside the current session, which is also possible with the same procedure:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE(sid, serial#, binds=>true);

Again, a nicer way to do what was possible before; with DBMS_SYSTEM.SET_EV. But now, you have only one package to remember: DMBS_MONITOR.

Sometimes we want to trace as session that will be opened somewhat later. For example, a batch job that runs at night. For this case the traditional solution was to create a SYSTEM LOGON trigger. The body of the trigger typically checks some conditions to decide to trace or not to trace. Very often, the decision is based on the user-name that is fetched via SYS_CONTEXT/USERENV or information from v$session. Many times, applied guesswork is added to the trigger. As a last resort, if it’s not even possible to trace all session that are opened during a specific time frame, it’s also possible to trace much more than needed and filter afterwards.

In a real world scenario, tracing everything is often not feasible because of the huge data volume created. Many people also argue that there is a considerable performance hit. Although I admit, and demonstrate below, that there IS a performance hit, it is usually negligible if neither waits nor binds are traced. The reason that tracing can’t be enabled globally on a production system, is the disk space requirement.

The DBMS_MONITOR Killer Features

The real power of DBMS_MONTOR isn’t that it provides a more convenient interface to enable tracing. The real power comes from

the ability to persistently configure tracing
the ability to perform fine grained, selective tracing

With DBMS_MONITOR, the need for LOGON triggers is gone. E.g., to enable tracing for all active and all future session for a specific service—like SERVICE_NAME in the connect descriptor—use the following:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME');

This will enable tracing for all current and future sessions with the specified SERVICE_NAME.

The second killer feature is the ability to perform selective tracing based on

client identifier
module
action

These are session properties can be set the the application. When done properly, the application can change those properties at a very high frequency—for every statement—without introducing a considerable performance hit.

For example, if the application sets the CLIENT_IDENTIFIER to reflect the user ID of a websites user, tracing can be enabled for that particular user only. Similarly selective tracing on MODULE and ACTION level is possible. Consider the following activities happening in a session:

Module	Action	statement
MOD1	ACT1	select * from …
MOD1	ACT2	insert into …
MOD2	ACT1	update…
MOD2	ACT3	delete …

With DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE we can enable tracing for MOD1 only if you like:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME', 'MOD1');

The effect is as expected: only the statements attributed to MOD1 will be traced. Consequently you can drill down to trace only ACT1 within MOD1:

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME', 'MOD1',
                                  'ACT1', waits=>true, binds=>true);

This will trace only the insert statement, with wait and bind data. Similar possibilities exist for the CLIENT_IDENTIFIER:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('CLIENT_IDENTIFIER',
                                         waits=>false, binds=>true);

Remember that all of this is persistent, even across database restarts. So we need to disable it again. Of course we will forget what was enabled, so there is a table to query for that:

SQL> SELECT trace_type, primary_id,
            qualifier_id1 ID1, qualifier_id2 ID2,
            waits, binds
       FROM dba_enabled_traces;

TRACE_TYPE             PRIMARY_ID        ID1    ID2   WAITS BINDS
---------------------- ----------------- ------ ----- ----- -----
SERVICE_MODULE_ACTION  SERVICE_NAME      MOD1   ACT1  TRUE  TRUE
SERVICE                SERVICE_NAME                   TRUE  FALSE
SERVICE_MODULE         SERVICE_NAME      MOD1         TRUE  FALSE
CLIENT_ID              CLIENT_IDENTIFIER              FALSE TRUE

With these methods we can enable tracing on a very fine granularity in a very convenient way. Since the tracing can be focused, space and performance issues are less problematic.

Résumé

Once an application is instrumented, fine-grained tracing can be applied very straight. Because of the reduced overhead and disk space requirements, SQL Tracing might become a more frequently used tool to troubleshoot poor performance.

You can choose the effort you put into instrumentation by yourself. For example, you might choose to set the MODULE only for one particular module that you need to trace right now, and don’t adopt other modules. In a step-by-step manner you can extend the instrumentation as needed.

During my experiments for this article I have found some traps you should be aware of:

The ACTION seems to consider only 8 characters
During my tests I noticed that the significant length of the ACTION seems to be only 8 characters (although documented as 32 bytes). However, any attempt to enable tracing for an ACTION longer than 8 bytes failed on my 10g test machine.
A wrong implementation might introduce a major performance penalty
Double check your implementation for that.
SERVICE_NAME might be set to an unexpected value
Depending on the way the application connects, SERVICE_NAME might not be present so that it defaults to SYS$USER. If it doesn’t work on the first try, verify that the sessions you want to trace has the expected SERVICE_NAME (select serivce_name from v$session....). Use the correct connect URL with the thin driver.
When using DBMS_MONITOR, wait event tracing is TRUE per default

It’s not fatal to open your mind to new approaches.

Pipelined Functions: Better Than DBMS_OUTPUT

Markus Winand — Wed, 11 Nov 2009 07:36:06 PST

Every now and then I need some PL/SQL that prints something to the terminal. The traditional solution for this is DBMS_OUTPUT.PUT_LINE. If you used it before, you probably know that there are some obstacles:

Don’t forget to set serveroutput on
Don’t forget to set an appropriate buffer size (but there is still a absolute maximum)
Don’t wonder about missing blanks
No way to flush the output

In case you don’t know yet, the first three can be taken care of by issuing the following in SQL*Plus:

set serveroutput on size 1000000 format wrapped;

However, the absolute buffer size limit remains. The years have passed, and the limitations were accepted. Until I noticed that there is an alternative.

With release 9iR2, Oracle delivered a package to format the PLAN_TABLE’s content: DBMS_XPLAN. A very nifty tool which finally rendered the SQLs to format the explain plan useless. But explain plan is not today’s topic; lets just focus in how the formatted plan reaches our screen:

select * from table(DBMS_XPLAN.DISPLAY);

The easiest way to use the package is shown above. It’s obvious that the data is actually fetched by regular select statement. You will also notice this by SQL*Plus artifacts like the feedback line (8 rows selected):

SQL> explain plan for select * from dual;

Explained.

SQL>  select * from table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 272002086

---------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------
|  0 | SELECT STATEMENT  |      |    1 |     2 |     2   (0)| 00:01 |
|  1 |  TABLE ACCESS FULL| DUAL |    1 |     2 |     2   (0)| 00:01 |
---------------------------------------------------------------------

8 rows selected.

SQL>

The table you see isn’t actually a table (as the database knows it), it’s just formatted output. When using DBMS_XPLAN, I always noted to myself to investigate this. Some years later, the time has come.

Pipelined Table Functions

Oracle’s (pipelined) table functions are PL/SQL functions that return data as (more or less) regular tables that can be accessed with regular SQL. The pipelined feature makes it possible to fetch the first records while the function is still running.

It seems that the primary target of this feature are custom analytic functions and data re-arranging like pivot. However, I use it to get rid of DBMS_OUTPUT.

Hello World

The following is the most simplistic example how to use a pipelined function to return arbitrary output:

create or replace type piped_output_table as table of varchar2(4000);
/

create or replace
 function hello_world return piped_output_table pipelined IS
 text varchar2(4000);
begin
 text := 'Hello world';
 pipe row(text);
 RETURN;
END;
/

The hello_world function must be wrapped in a table() expression to access it like a regular table in SQL:

SQL>  select * from table(hello_world);
COLUMN_VALUE
---------------------------------------------------------------------
Hello world

1 row selected.

SQL>

We have already mastered everything important to replace DBMS_OUTPUT:

Define a table type to be used as return type of the function
Add the pipelined keyword
The PIPE ROW() statement to actually produce a row.
The RETURN statement without an argument.

However, there are two more issues to consider:

You might want to have a different column name (instead of COLUMN_VALUE)
You might need to produce output from nested functions

Custom Column Names

The first issue is rather simple; to name the returned column(s) you must create a object type and base the table type upon this object type:

create or replace type piped_output is object (output varchar2(4000));
/

create or replace type piped_output_table is table of piped_output;
/

create or replace function
   hello_world return piped_output_table pipelined IS
   rec piped_output := piped_output(NULL);
begin
   rec.output := 'Hello world';
   pipe row(rec);
   RETURN;
END;
/

The column header can have an arbitrary column name. Since it is a column name, all respective limitations apply (e.g., no spaces).

Nested Functions

The final requirement to replace DBMS_OUTPUT is produce output from nested function calls. With DBMS_OUTPUT this is rather straight:

CREATE OR REPLACE PACKAGE dbms_output_nested IS
   PROCEDURE hello_world;
END;
/

CREATE OR REPLACE PACKAGE body dbms_output_nested IS
   PROCEDURE nested_procedure IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('  hello world from nested function') ;
   END nested_procedure;

   PROCEDURE hello_world IS
   BEGIN
      DBMS_OUTPUT.ENABLE();
      DBMS_OUTPUT.PUT_LINE('hello world') ;
      nested_procedure;
   END hello_world;
END dbms_output_nested;
/

Unfortunately, I am not aware of an easy way to accomplish this with pipelined table functions. It seems that there is no way to directly pipe one functions output into the output pipe of another function. So far, the only solution I found is to explicitly copy the records over:

CREATE OR REPLACE PACKAGE piped_nested IS
   TYPE piped_output IS RECORD (output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;

   FUNCTION hello_world
     RETURN piped_nested.piped_output_table PIPELINED;
   FUNCTION nested_procedure
     RETURN piped_nested.piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY piped_nested IS
   FUNCTION nested_procedure
     RETURN piped_nested.piped_output_table PIPELINED IS
      rec piped_nested.piped_output;
   BEGIN
      rec.output := '   hello world from nested function';
      PIPE ROW(rec);
      RETURN;
   END;

   FUNCTION hello_world
     RETURN piped_nested.piped_output_table PIPELINED IS
      rec piped_nested.piped_output;
   BEGIN
      rec.output := 'Hello World';
      PIPE ROW(rec);

      FOR r IN (select * from table(nested_procedure)) LOOP
         rec.output := r.output;
         PIPE ROW(rec);
      END LOOP;

      RETURN;
   END;
END piped_nested;
/

You see that there are two major drawbacks:

The nested_procedure is public
The copy bloats the hello_world function

As mentioned above, I don’t have a good solution for this (let me know if you find something).

Mixing DBMS_OUTPUT with Pipelined Functions

If you have legacy PL/SQL code that uses DBMS_OUTPUT and would like to migrate to pipelined functions, you might consider the following wrapper:

CREATE OR REPLACE PACKAGE dbms_output_table IS
   TYPE piped_output IS RECORD (dbms_output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;
   FUNCTION display RETURN piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY dbms_output_table IS
   FUNCTION display RETURN piped_output_table PIPELINED IS
      rec piped_output;
      p_status NUMBER;
   BEGIN
      DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      WHILE p_status = 0 LOOP
         PIPE ROW(rec);
         DBMS_OUTPUT.GET_LINE(rec.dbms_output, p_status);
      END LOOP;
      RETURN;
   END;
END;
/

With this function you can retrieve the current content of the DBMS_OUTPUT buffer via a pipelined table function. Please note that the producer of the DBMS_OUTPUT must enable the DBMS_OUTPUT facility and SQL*Plus must be set serverout off (otherwise SQL*Plus would retrieve it):

SQL>  exec dbms_output_nested.hello_world;
hello world
hello world from nested function

PL/SQL procedure successfully completed.

SQL> set serverout off
SQL>  exec dbms_output_nested.hello_world;

PL/SQL procedure successfully completed.

SQL> select * from table(dbms_output_table.display);

DBMS_OUTPUT
---------------------------------------------------------------------
hello world
 hello world from nested function

SQL>

The Best of Both Worlds?

This—and similar methods—can be used so that nested functions still use the DBMS_OUTPUT interface, while the front end function returns its output as pipelined table. A mixed approach allows you to hide the nested function—no need to have them in the public package declaration—but reapplies some of the DBMS_OUTPUT limitations; the maximum buffer size (although at smaller level), no flush.

CREATE OR REPLACE PACKAGE mixed_package IS
   TYPE piped_output IS RECORD (output VARCHAR2(4000));
   TYPE piped_output_table IS TABLE OF piped_output;
   FUNCTION hello_world RETURN piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY mixed_package IS
   PROCEDURE prepare_dbms_output IS
   BEGIN
      DBMS_OUTPUT.DISABLE; -- removes any stale data
      DBMS_OUTPUT.ENABLE(1000000);
   END;

   PROCEDURE nested_function IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('   Hello world from nested function');
   END;

   FUNCTION hello_world RETURN piped_output_table PIPELINED IS
      rec piped_output;
      p_status NUMBER;
   BEGIN
      prepare_dbms_output;

      rec.output := 'Hello World';
      PIPE ROW(rec);

      nested_function;

      DBMS_OUTPUT.GET_LINE(rec.output, p_status);
      WHILE p_status = 0 LOOP
         PIPE ROW(rec);
         DBMS_OUTPUT.GET_LINE(rec.output, p_status);
      END LOOP;

      RETURN;
   END;
END;
/

You can also use other methods to return row data from nested functions and pass it on to the PIPE ROW statement in the topmost stack frame. No matter in which way pass rows from nested functions, you must be aware that they are passed as a bulk when the nested function finishes. That means, the are kind of flushed once for each nested function call. If all you data is returned from a nested function, you might effectively disable the pipelined feature.

Final Comparison and Critique

The user interface of pipelined table functions is much better than the well established DBMS_OUTPUT for several reasons:

No separate API needed to access the returned data
Most programming languages don’t have a convenient way to access the serveroutput. Very often the only way to fetch the buffer is to manually call DBMS_OUTPUT.GET_LINES. On a quick view, only the perl DBI/DBD::Oracle framework has addressed the problem (a little bit):
But wait, your scripts are anyways intended for SQL*Plus usage only? Besides the fact that you never know in which context your scripts are used, there are also other user interfaces than SQL*Plus to access an Oracle database. There are many IDE that support database access. Most of those have support for DBMS_OUTPUT in the meanwhile but it’s different in each implementation. You can avoid a great deal of questions where to find the output of your script in tool XYZ by using table functions.
No size limitation
But wait, your script isn’t returning much data? Yes, you usually know how much data your script will return. With a call to DBMS_OUTPUT.ENABLE you can make the buffer big enough. But if you are wrong? You are lucky if the user knows how to enlarge the buffer. You are even more happy if the hard limit of the buffer size is not reached (1,000,000 bytes). I know of a case where the user was required to change a script to use UTL_FILE because the output was a few megabytes (luckily they had access to the database back-end, where the file was written).
Pipelined
Self-explaining? No major usability benefit, I guess. However, it is a benefit to see that something is happening while the function is running. It is definitely a benefit not to buffer everything in memory until the end—that is probably the reason why there is a hard limit on the buffer size.

I guess the article gave you insight to the major drawback of the pipelined table function method as well:

Producing output from nested function calls is a pain in the a**
It is possible, but inconvenient—if not delusive because it exploits internal functions in the public interface. The use of collections in the nested function makes it possible to maintain a clean public interface. However, copying the data over and over again is inconvenient, if not problematic, because it might reintroduce the size issue (when using DBMS_OUTPUT the already mentioned limit of 1,000,000 bytes per nested function call apply).
DML in the table function cannot participate in the caller’s transaction
UPDATE 2010-03-11: Gary commented that
```
select * from table(hello_world)
```
works only if the function does not perform any DML. Gary is of course right; any attempt to do so results in ORA-14551. The only possibility to perform DML in the function is to use an autonomous transaction.

My personal Best Current Practice is to use other methods (collections or even DBMS_OUTPUT) in the private functions, but use a pipelined function interface for the user. This is sometimes very inconvenient to code but the improved user experience is worth that effort.

Of course I would have some wishes how the situation could be improved. The most powerful way would be to allow something like this:

PIPE ROW(nested_pipelined_function);

Where nested_pipelined_function doesn’t need to be part of the public package interface. Each PIPE ROW() in the nested function should directly output as if was written in the calling function. On the other hand, it would also be great to have something to pipe a complete collection at once:

PIPE ROWS(collection);

I am well aware that the usage of pipelined table functions as demonstrated in this article might not reflect the intended usage of that feature, thus its fitness for that purpose is limited.

It’s not fatal to open your mind to new approaches.