Hard Parses when Using Bind Variables?

31 12 2009

December 31, 2009

Assume that the following tables are created and then statistics are gathered:

CREATE TABLE T3 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE TABLE T4 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE INDEX IND_T4 ON T4(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)

If you then set up SQL*Plus with the following commands:

VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 1000

How many hard parses would you see for the session, and how many child cursors for the SQL statement will be in the library cache, if you do the following in SQL*Plus:

EXEC :N1 := 1
EXEC :N2 := 1
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;

Now, repeat the above 9,999 times in the same session, specifying random values for N1 and N2 such that:

  • N1 >= 1
  • N2 <= 10,000
  • N1 <= N2

Does it matter if you test with Oracle 8.0.5 (assuming that you use the ANALYZE command rather than DBMS_STATS), 11.2.0.1, or something in between?





Excel – The Graphical Master of Oracle Foreign Keys

30 12 2009

December 30, 2009

Foreign keys… those practical rules that help maintain data integrity between parent and child tables, allow for table eliminations in queries beginning with Oracle release 10.2.0.1, and provide the potential for the optimizer to generate additional predicates during query optimization for better cardinality estimates.

Those benefits all sound like they might be helpful to someone, but what good are they for the average person (I mean the average developer)?  Foreign keys are great for generating abstract art (note that I am a poor judge of art), with a bunch of odd writing all over the place.

This example demonstrates how to query an Excel spreadsheet as if it were a database.  The results of the queries are used to build a graphical model of the relationships between the tables based on the foreign key relationships that are established in the database.  Excel 2003 limits text boxes to no more than 255 characters, so use Excel 2007 or later for this example, if possible.

First, we need to build the source Excel spreadsheet that will act as the database that will be queried by the second Excel spreadsheet.  The source Excel spreadsheet will have two worksheets (tabs) named “Data Dict Tables” and “Data Dict Foreign Keys”.  Use the Microsoft Query Tool, or another approach to bring in the data from the Oracle data dictionary.  The queries for each of those worksheets follows:

Data Dict Tables

SELECT
  DT.OWNER,
  DT.TABLE_NAME,
  DTC.COLUMN_NAME,
  DTC.DATA_TYPE,
  DTC.DATA_LENGTH,
  DTC.DATA_PRECISION,
  DTC.DATA_SCALE,
  DTC.NULLABLE,
  DTC.COLUMN_ID,
  DT.TABLESPACE_NAME,
  DTCC.COMMENTS TABLE_COMMENTS,
  SUBSTR(DCC.COMMENTS,1,255) COLUMN_COMMENTS
FROM
  DBA_TABLES DT,
  DBA_TAB_COLUMNS DTC,
  DBA_TAB_COMMENTS DTCC,
  DBA_COL_COMMENTS DCC
WHERE
  DT.OWNER=DTC.OWNER
  AND DT.TABLE_NAME=DTC.TABLE_NAME
  AND DT.OWNER=DTCC.OWNER(+)
  AND DT.TABLE_NAME=DTCC.TABLE_NAME(+)
  AND DTC.OWNER=DCC.OWNER(+)
  AND DTC.TABLE_NAME=DCC.TABLE_NAME(+)
  AND DTC.COLUMN_NAME=DCC.COLUMN_NAME(+)
ORDER BY
  DT.OWNER,
  DT.TABLE_NAME,
  DTC.COLUMN_ID;

— 

Data Dict Foreign Keys

SELECT
  DC2.OWNER,
  DC2.CONSTRAINT_NAME PKEY_CONTRAINT,
  DC1.CONSTRAINT_NAME FKEY_CONTRAINT,
  DCC2.TABLE_NAME PKEY_TABLE_NAME,
  DCC2.COLUMN_NAME PKEY_COLUMN_NAME,
  DCC1.TABLE_NAME FKEY_TABLE_NAME,
  DCC1.COLUMN_NAME FKEY_COLUMN_NAME,
  DCC1.POSITION
FROM
  DBA_CONSTRAINTS DC1,
  DBA_CONSTRAINTS DC2,
  DBA_CONS_COLUMNS DCC1,
  DBA_CONS_COLUMNS DCC2
WHERE
  DC1.CONSTRAINT_TYPE='R'
  AND DC1.R_OWNER=DC2.OWNER
  AND DC1.R_CONSTRAINT_NAME=DC2.CONSTRAINT_NAME
  AND DC1.OWNER=DCC1.OWNER
  AND DC1.CONSTRAINT_NAME=DCC1.CONSTRAINT_NAME
  AND DC2.OWNER=DCC2.OWNER
  AND DC2.CONSTRAINT_NAME=DCC2.CONSTRAINT_NAME
  AND DCC1.POSITION=DCC2.POSITION
  AND DC1.OWNER='SYSADM'
ORDER BY
  DC2.OWNER,
  DCC2.TABLE_NAME,
  DCC1.TABLE_NAME,
  DCC2.POSITION;

The source spreadsheet with the data dictionary details should be saved to the root of the C:\ drive with the name C:\Data Dictionary.xls

In a second spreadsheet we are trying to build the following interface:

Next, we need to add a couple ActiveX controls to the second spreadsheet:
B3: Combo Box  with a name of cboTableName with a blank Text value
C3: Check Box  with a name of chkAliasNames with a Caption of Alias Table Names
C1: Command Button  with a name of cmdInitialize with a Caption of Initialize
A1, A3, B1 – type in the text as shown above

Once we add the macro code, clicking the Initialize button will send a query to the other spreadsheet to retrieve a list of the tables:

And with all of the code in place, selecting a table builds the abstract art:

Running down the left side is the list of columns in the selected table.  Every foreign key that is defined against the selected table will trigger a recursive lookup for child tables of the child table, with the necessary join conditions listed as well as the columns that are defined in the child table.  For instance, there is a foreign key on the CO_PRODUCT table that points back to the primary key on the PART table, and the join between the tables should be P.ID = CP.PART_ID.  The CO_PRODUCT table does not have any child tables.

Skipping down to the CUST_ORDER_LINE table, it has a foreign key that references the PART table on P.ID = COL.PART_ID.  The RECEIVABLE_LINE table has a foreign key that references the CUST_ORDER_LINE table on COL.CUST_ORDER_ID = RL.CUST_ORDER_ID AND COL.LINE_NO = RL.CUST_ORDER_LINE_NO.  The RECV_LINE_BINARY table has a foreign key that references the RECEIVABLE_LINE table on RL.INVOICE_ID = RLB.INVOICE_ID AND RL.LINE_NO = RLB.RECV_LINE_NO.  With this tool we just discovered a way to join the PART table to the RECV_LINE_BINARY by analyzing the defined foreign keys.

OK, enough fun, lets enter the code.  Right-click the worksheet name (probably Sheet1) rename the sheet to ExcelQueryOfExcel and then right-click the sheet and to select View Code.  Let’s start with making the Initialize button work:

'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'Declare a connection object in the general section to hold the connection to the database
Dim dbExcel As New ADODB.Connection

'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String

Private Function ConnectDatabase() As Integer
    Dim intResult As Integer

    On Error Resume Next

    If dbExcel.State <> 1 Then
        'Connection to the database if closed
        strDatabase = Sheets("ExcelQueryOfExcel").Cells(1, 2).Value

        strUserName = ""
        strPassword = ""

        'Connect to the database
        dbExcel.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

        dbExcel.ConnectionTimeout = 40
        dbExcel.CursorLocation = adUseClient
        dbExcel.Open

        If (dbExcel.State <> 1) Or (Err <> 0) Then
            intResult = MsgBox("Could not connect to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")

            ConnectDatabase = False
        Else
            ConnectDatabase = True
        End If
    Else
        ConnectDatabase = True
    End If
End Function

Private Sub cmdInitialize_Click()
    Dim intResult As Integer
    Dim strSQL As String
    Dim strLastTable As String
    Dim snpData As ADODB.Recordset

    On Error Resume Next

    cboTableName.Clear
    cboTableName = ""
    Sheets("ExcelQueryOfExcel").Range("A6:Z10006").Delete Shift:=xlUp

    intResult = ConnectDatabase
    strLastTable = ""

    If intResult = True Then
        Set snpData = New ADODB.Recordset

        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  2"     '  Sort the rows by the second column in the data source
        snpData.Open strSQL, dbExcel

        If snpData.State = 1 Then
            Do While Not snpData.EOF
                If strLastTable <> snpData("table name") Then
                    strLastTable = snpData("table name")
                    cboTableName.AddItem snpData("table name")
                End If

                snpData.MoveNext
            Loop
            snpData.Close
        Else
            intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If

        Set snpData = Nothing
    End If
End Sub

The above code should be sufficient for the Initialize button to work – you may need to turn off Design Mode in Excel for the button to work.

Now we need to work on the recursion when a table is selected (and the rest of the code):

Private Function FindRelatedTables(strTableName As String, lngColumn As Long, lngRow As Long, intAliasTableNames As Integer) As Long
    Dim i As Integer
    Dim lngBoxLeft As Long
    Dim lngBoxTop As Long
    Dim lngBoxWidth As Long
    Dim lngBoxHeight As Long
    Dim lngResult As Long
    Dim intTableColumns As Integer
    Dim intForeignColumns As Integer
    Dim intFlag As Integer
    Dim strOut As String
    Dim strLastTable As String
    Dim strSplit() As String  'Use to alias the table names
    Dim strPrimary As String  'Stores the aliased table primary key table name
    Dim strForeign As String  'Stores the aliased table foreign key table name
    Dim strSQL As String
    Dim snpData As ADODB.Recordset
    Dim snpDataForeign As ADODB.Recordset

    On Error Resume Next

    If intAliasTableNames = True Then
        strPrimary = ""
        strSplit = Split(strTableName, "_")

        For i = 0 To UBound(strSplit)
            strPrimary = strPrimary & Left(strSplit(i), 1)
        Next i
    End If

    Set snpDataForeign = New ADODB.Recordset
    Set snpData = New ADODB.Recordset

    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  *" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  [Data Dict Foreign Keys$]" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  [Pkey Table Name] = '" & strTableName & "'" & vbCrLf
    strSQL = strSQL & "ORDER BY" & vbCrLf
    strSQL = strSQL & "  [Fkey Table Name]," & vbCrLf
    strSQL = strSQL & "  [Position]"
    snpDataForeign.Open strSQL, dbExcel

    If snpDataForeign.State = 1 Then
        If Not snpDataForeign.EOF Then
            strOut = strTableName & " " & strPrimary & " :" & vbLf
            intTableColumns = 0
            intForeignColumns = 0
            strLastTable = snpDataForeign("Fkey Table Name")
            Do While Not snpDataForeign.EOF
                If intAliasTableNames = True Then
                    strForeign = ""
                    strSplit = Split(snpDataForeign("Fkey Table Name"), "_")

                    For i = 0 To UBound(strSplit)
                        strForeign = strForeign & Left(strSplit(i), 1)
                    Next i
                    'Verify that the two aliases are not identical
                    If strForeign = strPrimary Then
                        strForeign = strForeign & "F"
                    End If
                    strOut = strOut & strPrimary & "." & snpDataForeign("Pkey Column Name") & " = " & strForeign & "." & snpDataForeign("Fkey Column Name") & vbLf
                Else
                    strOut = strOut & snpDataForeign("Pkey Table Name") & "." & snpDataForeign("Pkey Column Name") & " = " & snpDataForeign("Fkey Table Name") & "." & snpDataForeign("Fkey Column Name") & vbLf
                End If

                intForeignColumns = intForeignColumns + 1

                snpDataForeign.MoveNext

                'See if we need to retrieve the table columns for the previous table
                intFlag = False
                If snpDataForeign.EOF Then
                    intFlag = True
                Else
                    If strLastTable <> snpDataForeign("Fkey Table Name") Then
                        intFlag = True
                    End If
                End If

                If intFlag = True Then
                    strSQL = "SELECT" & vbCrLf
                    strSQL = strSQL & "  *" & vbCrLf
                    strSQL = strSQL & "FROM" & vbCrLf
                    strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
                    strSQL = strSQL & "WHERE" & vbCrLf
                    strSQL = strSQL & "  [Table Name] = '" & strLastTable & "'" & vbCrLf
                    strSQL = strSQL & "ORDER BY" & vbCrLf
                    strSQL = strSQL & "  [Column Id]"     '  Sort the rows by the second column in the data source
                    snpData.Open strSQL, dbExcel
                    If snpData.State = 1 Then
                        If Not snpData.EOF Then
                            strOut = strOut & vbLf & strLastTable & " " & strForeign & " :" & vbLf
                            intTableColumns = 0
                            Do While Not snpData.EOF
                                strOut = strOut & snpData("column name") & vbLf
                                intTableColumns = intTableColumns + 1

                                snpData.MoveNext
                            Loop
                        End If
                    End If
                    snpData.Close

                    'Create the text box
                    'Strip off the trailing CrLf
                    strOut = Left(strOut, Len(strOut) - 1)

                    lngBoxLeft = lngColumn * 200
                    lngBoxWidth = 200
                    lngBoxTop = lngRow * 20
                    lngBoxHeight = (intTableColumns + intForeignColumns + 3) * 5.5

                    With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
                        With .TextFrame
                            If Application.Version <= 11 Then
                                'There is a 255 character limit on text boxes before Office 2007
                                .Characters.Text = Left(strOut, 255)
                            Else
                                .Characters.Text = strOut
                            End If
                            .Characters.Font.Size = 8
                        End With
                        .Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
                    End With

                    'With Sheets("ExcelQueryOfExcel").Shapes.AddTextbox(msoTextOrientationHorizontal, lngBoxLeft, lngBoxTop, lngBoxWidth, (lngBoxHeight * 2 - 1))
                    '    With .TextFrame
                    '        .Characters.Text = strOut
                    '        .Characters.Font.Size = 8
                    '    End With
                    '    .Fill.ForeColor.RGB = RGB(255 - lngColumn * 10, 255 - lngColumn * 10, 255)
                    'End With

                    If lngColumn < 6 Then
                        'Only recursively call to 6 levels
                        'Recursive call into this function
                        lngResult = FindRelatedTables(strLastTable, lngColumn + 1, lngRow, intAliasTableNames)
                    End If

                    'Prepare for the next text box
                    strOut = strTableName & " " & strPrimary & " :" & vbLf
                    intTableColumns = 0
                    intForeignColumns = 0
                    lngRow = lngRow + 3
                End If
                If Not snpDataForeign.EOF Then
                    strLastTable = snpDataForeign("Fkey Table Name")
                End If

                If lngRow > 5000 Then
                    'Protection against a very long wait
                    Exit Do
                End If
            Loop
        End If
        snpDataForeign.Close
    End If

    Set snpDataForeign = Nothing
    Set snpData = Nothing
End Function

Private Sub FindTable()
    Dim intAliasTableNames As Integer
    Dim intResult As Integer
    Dim lngResult As Long
    Dim lngRow As Long
    Dim strSQL As String
    Dim strTableName As String
    Dim snpData As ADODB.Recordset

    On Error Resume Next

    intAliasTableNames = chkAliasNames.Value
    lngRow = 6

    Sheets("ExcelQueryOfExcel").Range("A" & Format(lngRow) & ":Z" & Format(lngRow + 10000)).Delete Shift:=xlUp

    If cboTableName <> "" Then
        Application.ScreenUpdating = False

        strTableName = cboTableName
        Set snpData = New ADODB.Recordset

        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  *" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  [Data Dict Tables$]" & vbCrLf
        strSQL = strSQL & "WHERE" & vbCrLf
        strSQL = strSQL & "  [Table Name] = '" & strTableName & "'" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  [Column Id]"     '  Sort the rows by the second column in the data source
        snpData.Open strSQL, dbExcel
        If snpData.State = 1 Then
            Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = strTableName
            Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Font.Bold = True
            lngRow = lngRow + 1

            If Not snpData.EOF Then
                Do While Not snpData.EOF
                    Sheets("ExcelQueryOfExcel").Cells(lngRow, 1).Value = snpData("column name")
                    lngRow = lngRow + 1

                    snpData.MoveNext

                    'Safety net
                    If lngRow > 1000 Then
                        Exit Do
                    End If
                Loop

                lngResult = FindRelatedTables(strTableName, 1, 5, intAliasTableNames)
            End If
            snpData.Close
        Else
            intResult = MsgBox("Could not send the SQL statement to the Excel Database." & vbCrLf & Error(Err), 16, "Excel Demo")
        End If

        Set snpData = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

Private Sub cboTableName_Click()
    FindTable
End Sub

Private Sub cboTableName_GotFocus()
    cboTableName.SelStart = 0
    cboTableName.SelLength = Len(cboTableName)
End Sub

Private Sub cboTableName_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If (KeyCode = 13) Or (KeyCode = 8  ) Then
        FindTable
    End If
End Sub

Private Sub chkAliasNames_Click()
    FindTable
End Sub

I will not try here to explain how all of the above code works.  I created this demonstration for a presentation that I gave a couple months ago.  It really should be easy to understand if you step through the code starting with the cboTableName_Click Sub.





Which is Most Efficient when Selecting Rows: EXISTS, IN, or a JOIN

29 12 2009

December 29, 2009

This post is a follow up to the previous post that questioned which approach is most efficient when deleting rows.

The Oracle documentation for 10g R2 states the following

“In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.”

“Sometimes, Oracle can rewrite a subquery when used with an IN clause to take advantage of selectivity specified in the subquery. This is most beneficial when the most selective filter appears in the subquery and there are indexes on the join columns. Conversely, using EXISTS is beneficial when the most selective filter is in the parent query. This allows the selective predicates in the parent query to be applied before filtering the rows against the EXISTS criteria.”

The above quotes seems to be similar to the quotes provided in the earlier blog article.  Let’s set up the same test tables as were used in the previous blog article:

CREATE TABLE T1 (
  C1 NUMBER,
  FILLER VARCHAR2(300),
  PRIMARY KEY (C1));

CREATE TABLE T2 (
  C1 NUMBER,
  FILLER VARCHAR2(300),
  PRIMARY KEY (C1));

INSERT INTO
  T1
SELECT
  ROWNUM,
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V1,
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

INSERT INTO
  T2
SELECT
  ROWNUM*3,
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=333) V1,
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

COMMIT;

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

Next, we will set up to capture a 10053 trace for three SQL statements:

  1. Uncorrelated IN subquery
  2. Correlated IN subquery
  3. EXISTS subquery
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SELECT_METHODS';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SET TIMING ON

SPOOL select_methods.txt

SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2 WHERE T2.C1=T1.C1);

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT C1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

SPOOL OFF

ALTER SESSION SET EVENTS '10053 trace name context off';

On Oracle Database 11.1.0.7, I received the following output in the select_methods.txt file (slightly cleaned up):

SQL> SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

Elapsed: 00:00:43.54

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

SQL> SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2 WHERE T2.C1=T1.C1);

Elapsed: 00:00:43.18

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

SQL> SELECT C1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

Elapsed: 00:00:43.33

The above output seems to indicate that the correlated IN subquery completed the fastest, followed by the EXISTS subquery, and then the uncorrelated IN subquery.  Let’s check the 10053 trace file.

******************************************
----- Current SQL Statement for this session (sql_id=19gjmx8y5rcg9) -----
SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2)
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1" FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"="T2"."C1"
...
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |   576 |           |
| 1   |  NESTED LOOPS          |             |  325K | 3252K |   576 |  00:00:07 |
| 2   |   INDEX FAST FULL SCAN | SYS_C0016167|  977K | 4883K |   514 |  00:00:07 |
| 3   |   INDEX UNIQUE SCAN    | SYS_C0016168|     1 |     5 |     0 |           |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("C1"="C1")

...
...
...

******************************************
----- Current SQL Statement for this session (sql_id=5hkw5pa3stxvr) -----
SELECT C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2 WHERE T2.C1=T1.C1)
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1" FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"="T2"."C1" AND "T2"."C1"="T1"."C1"
...
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |   576 |           |
| 1   |  NESTED LOOPS          |             |  325K | 3252K |   576 |  00:00:07 |
| 2   |   INDEX FAST FULL SCAN | SYS_C0016167|  977K | 4883K |   514 |  00:00:07 |
| 3   |   INDEX UNIQUE SCAN    | SYS_C0016168|     1 |     5 |     0 |           |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("C1"="C1")

...
...
...

******************************************
----- Current SQL Statement for this session (sql_id=4x73yz9t7dazj) -----
SELECT C1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1)
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."C1" "C1" FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T2"."C1"="T1"."C1"
...
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |   576 |           |
| 1   |  NESTED LOOPS SEMI     |             |  325K | 3252K |   576 |  00:00:07 |
| 2   |   INDEX FAST FULL SCAN | SYS_C0016167|  977K | 4883K |   514 |  00:00:07 |
| 3   |   INDEX UNIQUE SCAN    | SYS_C0016168|  108K |  541K |     0 |           |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T2"."C1"="T1"."C1")

Notice anything strange about the “Final query after transformations”?  The transformed uncorrelated IN subquery and the transformed EXISTS subquery are identical (if you ignore that T1.C1= T2.C1 is actually listed as T2.C1=T1.C1.  Notice also that the transformed queries are now standard joins rather than the IN query being transformed into an EXISTS query, or vice-versa.  The transformed version of the correlated IN subquery is a bit odd – it seems that the query optimizer in 11.1.0.7 did not automatically eliminate the duplicate T1.C1= T2.C1 (if I recall correctly 10.2.0.4 does remove the duplicate predicate when writing the “Final query”).  OK, the transformed versions of the SQL statements are for all purposes identical.  The plan for the EXISTS query shows that the join is a NESTED LOOPS SEMI, while the other plans show a join of NESTED LOOPS.

Let’s look at the DBMS_XPLAN output (note that STATISTICS_LEVEL was set to the default of TYPICAL):

SET TIMING OFF
SET AUTOTRACE OFF

SET PAGESIZE 2000
SET LINESIZE 140

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SPOOL select_methods2.txt

SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2 WHERE T2.C1=T1.C1);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SPOOL OFF 

The output of the above is as follows (note that the output has been cleaned up slightly):

SQL_ID  gwd4d6sk75hhk, child number 0                                
-------------------------------------                                
SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2)                          
Plan hash value: 319633161        

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    333K|00:00:00.05 |   64418 |   2547 |
|   1 |  NESTED LOOPS         |              |      1 |    333K|    333K|00:00:00.05 |   64418 |   2547 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0016167 |      1 |   1000K|   1000K|00:00:00.02 |   24052 |   1883 |
|*  3 |   INDEX UNIQUE SCAN   | SYS_C0016168 |   1000K|      1 |    333K|00:00:00.84 |   40366 |    664 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                  
---------------------------------------------------                  
   3 - access("C1"="C1")          

-

SQL_ID  fcm7ptb886bzt, child number 0                                
-------------------------------------                                
SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE C1 IN (SELECT C1 FROM T2 WHERE T2.C1=T1.C1)        

Plan hash value: 319633161        

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    333K|00:00:00.09 |   64418 |   2547 |
|   1 |  NESTED LOOPS         |              |      1 |    333K|    333K|00:00:00.09 |   64418 |   2547 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0016167 |      1 |   1000K|   1000K|00:00:00.03 |   24052 |   1883 |
|*  3 |   INDEX UNIQUE SCAN   | SYS_C0016168 |   1000K|      1 |    333K|00:00:01.06 |   40366 |    664 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                  
---------------------------------------------------                  
   3 - access("C1"="C1")          

-

SQL_ID  4ym7p8815nquy, child number 0                                
-------------------------------------                                
SELECT /*+ GATHER_PLAN_STATISTICS */ C1 FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1)        

Plan hash value: 2371405353       

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |    333K|00:00:00.06 |   64418 |   2547 |
|   1 |  NESTED LOOPS SEMI    |              |      1 |    333K|    333K|00:00:00.06 |   64418 |   2547 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0016167 |      1 |   1000K|   1000K|00:00:00.03 |   24052 |   1883 |
|*  3 |   INDEX UNIQUE SCAN   | SYS_C0016168 |   1000K|    110K|    333K|00:00:00.94 |   40366 |    664 |
---------------------------------------------------------------------------------------------------------

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

The output captured during the 10053 trace implied: “The above output seems to indicate that the correlated IN subquery completed the fastest, followed by the EXISTS subquery, and then the uncorrelated IN subquery.”  The output captured during the DBMS_XPLAN script seems to show that the uncorrelated subquery completed the fastest, followed by the EXISTS subquery, and then the correlated subquery.

OK, so the question remains: Which is most efficient when selecting rows: EXISTS, IN, or a JOIN?  It is pretty hard to tell when the optimizer automatically re-writes the queries that we submit.

It might be interesting to recheck the output with a new test case that permits:

  1. NULL values in one or both tables.
  2. Duplicate values in one or both tables.
  3. Larger data sets.




Which is Most Efficient when Deleting Rows: EXISTS, IN, or a VIEW

29 12 2009

December 29, 2009

In the process of reading the book “Oracle SQL Recipes” I encountered a couple interesting statements.  One such statement is found on page 68 in recipe 3-5 Removing Rows Based on Data in Other Tables.  The book states the following:

“Whether you use IN or EXISTS depends on the sizes of the driving table (the outer table referenced in the SELECT, UPDATE, or DELETE) and the size of the result set in the subquery.  Using IN is most likely better if the results of the subquery are small or is a list of constants.  However, using EXISTS may run a lot more efficiently since the implicit JOIN may take advantage of indexes.”

The above comment sounds a bit like this one from the book “Expert Oracle Database 11g Administration” on page 1067 that might be describing just the behavior of SELECT statements, or might also be describing the behavior of DELETE statements:

“Subqueries perform better when you use IN rather than EXISTS.  Oracle recommends using the IN clause if the subquery has the selective WHERE clause.  If the parent query contains the selective WHERE clause, use the EXISTS clause rather than the IN clause.”

Both are interesting statements.  The “Oracle SQL Recipes” book provided three different SQL statements for removing rows in one table based on the contents of another table – those SQL statements used the demo data available for Oracle 11g.  I do not have the demo data loaded, so I wondered – what if I set up a simple test case?

Which method do you think will execute faster using the supplied test case?

The sample data, 1,000,000 rows in table T1 and 333,000 rows in T2:

CREATE TABLE T1 (
  C1 NUMBER,
  FILLER VARCHAR2(300),
  PRIMARY KEY (C1));

CREATE TABLE T2 (
  C1 NUMBER,
  FILLER VARCHAR2(300),
  PRIMARY KEY (C1));

INSERT INTO
  T1
SELECT
  ROWNUM,
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V1,
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

INSERT INTO
  T2
SELECT
  ROWNUM*3,
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=333) V1,
  (SELECT
    ROWNUM NR
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

COMMIT;

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

The sample DELETE statements:

DELETE FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

DELETE FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

DELETE (SELECT C1 FROM T1 JOIN T2 USING (C1));

——–

Do not scroll down – you will spoil the follow up.  Which do you think is the most efficient method based on what is written above, and what you know about the behavior of recent Oracle releases (say 10.1 and later)?

Let’s say you ran the following script:

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'DELETE_METHODS';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SET TIMING ON

SPOOL delete_methods.txt

DELETE FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

ROLLBACK;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

DELETE FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

ROLLBACK;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

DELETE (SELECT C1 FROM T1 JOIN T2 USING (C1));

ROLLBACK;

SPOOL OFF

ALTER SESSION SET EVENTS '10053 trace name context off';

Now which do you think is most efficient?  Do not scroll down – you will spoil the follow up.

Let’s say that in the Oracle Database 11.1.0.7 10053 trace file created by the above script, you find the following:

******************************************
----- Current SQL Statement for this session (sql_id=7mc4cvm67pns3) -----
DELETE FROM T1 WHERE C1 IN (SELECT C1 FROM T2)
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"="T2"."C1"
...
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation            | Name        | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT     |             |       |       |  2025 |           |
| 1   |  DELETE              | T1          |       |       |       |           |
| 2   |   NESTED LOOPS       |             |  325K | 3252K |  2025 |  00:00:25 |
| 3   |    INDEX FULL SCAN   | SYS_C0030271|  977K | 4883K |  1892 |  00:00:23 |
| 4   |    INDEX UNIQUE SCAN | SYS_C0030272|     1 |     5 |     0 |           |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("C1"="C1")

...
...
...

******************************************
----- Current SQL Statement for this session (sql_id=ahq5s54mw5a2g) -----
DELETE FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1)
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T2"."C1"="T1"."C1"
...
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation            | Name        | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT     |             |       |       |  2025 |           |
| 1   |  DELETE              | T1          |       |       |       |           |
| 2   |   NESTED LOOPS SEMI  |             |  325K | 3252K |  2025 |  00:00:25 |
| 3   |    INDEX FULL SCAN   | SYS_C0030271|  977K | 4883K |  1892 |  00:00:23 |
| 4   |    INDEX UNIQUE SCAN | SYS_C0030272|  108K |  541K |     0 |           |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("T2"."C1"="T1"."C1")

...
...
...

******************************************
----- Current SQL Statement for this session (sql_id=61r3vxah952fu) -----
DELETE (SELECT C1 FROM T1 JOIN T2 USING (C1))
*******************************************
...
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT 0 FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"="T2"."C1"
...
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation            | Name        | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT     |             |       |       |  2025 |           |
| 1   |  DELETE              | T1          |       |       |       |           |
| 2   |   NESTED LOOPS       |             |  325K | 3252K |  2025 |  00:00:25 |
| 3   |    INDEX FULL SCAN   | SYS_C0030271|  977K | 4883K |  1892 |  00:00:23 |
| 4   |    INDEX UNIQUE SCAN | SYS_C0030272|     1 |     5 |     0 |           |
-------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("T1"."C1"="T2"."C1")

Well, that is odd, three different queries sent in to the optimizer, and each time the optimizer showed the final query after transformation as the following:

SELECT 0 FROM "TESTUSER"."T2" "T2","TESTUSER"."T1" "T1" WHERE "T1"."C1"="T2"."C1"

Now which do you think is most efficient? 

The plans written in the 10053 trace file for Oracle 10.2.0.4 were slightly different:

Current SQL statement for this session:
DELETE FROM T1 WHERE C1 IN (SELECT C1 FROM T2)

============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation               | Name       | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT        |            |       |       |  1132 |           |
| 1   |  DELETE                 | T1         |       |       |       |           |
| 2   |   NESTED LOOPS          |            |  328K | 3283K |  1132 |  00:00:14 |
| 3   |    INDEX FAST FULL SCAN | SYS_C009783|  328K | 1642K |   173 |  00:00:03 |
| 4   |    INDEX UNIQUE SCAN    | SYS_C009782|     1 |     5 |     1 |  00:00:01 |
---------------------------------------------+-----------------------------------+

Predicate Information:
----------------------
4 - access("C1"="C1")

...
...
...
Current SQL statement for this session:
DELETE FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1)

============
Plan Table
============
----------------------------------------------+-----------------------------------+
| Id  | Operation                | Name       | Rows  | Bytes | Cost  | Time      |
----------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT         |            |       |       |  1687 |           |
| 1   |  DELETE                  | T1         |       |       |       |           |
| 2   |   NESTED LOOPS           |            |  328K | 3283K |  1687 |  00:00:21 |
| 3   |    SORT UNIQUE           |            |  328K | 1642K |   173 |  00:00:03 |
| 4   |     INDEX FAST FULL SCAN | SYS_C009783|  328K | 1642K |   173 |  00:00:03 |
| 5   |    INDEX UNIQUE SCAN     | SYS_C009782|     1 |     5 |     1 |  00:00:01 |
----------------------------------------------+-----------------------------------+

Predicate Information:
----------------------
5 - access("T2"."C1"="T1"."C1")

...
...
...
Current SQL statement for this session:
DELETE (SELECT C1 FROM T1 JOIN T2 USING (C1))

============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation               | Name       | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | DELETE STATEMENT        |            |       |       |  1132 |           |
| 1   |  DELETE                 | T1         |       |       |       |           |
| 2   |   NESTED LOOPS          |            |  328K | 3283K |  1132 |  00:00:14 |
| 3   |    INDEX FAST FULL SCAN | SYS_C009783|  328K | 1642K |   173 |  00:00:03 |
| 4   |    INDEX UNIQUE SCAN    | SYS_C009782|     1 |     5 |     1 |  00:00:01 |
---------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("T1"."C1"="T2"."C1")

Do not scroll down – you will spoil the follow up.

Let’s say that you test the performance on a system that uses direct, asynchronous I/O (no OS caching) with DBMS_XPLAN using the following script:

SET TIMING OFF
SET PAGESIZE 2000
SET LINESIZE 140

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SPOOL delete_methods.txt

DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ROLLBACK;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ROLLBACK;

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

DELETE /*+ GATHER_PLAN_STATISTICS */ (SELECT C1 FROM T1 JOIN T2 USING (C1));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

ROLLBACK;

SPOOL OFF

Here is the output when I ran the above script (slightly cleaned up):

SQL> DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE C1 IN (SELECT C1 FROM T2);

333000 rows deleted.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT           
-------------------------------------------------------------------------------------------------------
SQL_ID  3ng6d061qazr1, child number 0
-------------------------------------
DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE C1 IN (SELECT C1 FROM T2)                    

Plan hash value: 1028502382 

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |              |      1 |        |      0 |00:00:15.67 |     424K|  46351 |
|   1 |  DELETE             | T1           |      1 |        |      0 |00:00:15.67 |     424K|  46351 |
|   2 |   NESTED LOOPS      |              |      1 |    333K|    333K|00:00:01.10 |    7484 |   2577 |
|   3 |    INDEX FULL SCAN  | SYS_C0016151 |      1 |   1000K|   1000K|00:00:00.07 |    1877 |   1920 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0016152 |   1000K|      1 |    333K|00:00:01.08 |    5607 |    657 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):              
---------------------------------------------------              
   4 - access("C1"="C1")    

SQL> ROLLBACK;

Rollback complete.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1);

333000 rows deleted.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT           
-------------------------------------------------------------------------------------------------------
SQL_ID  1138j52zs4rqw, child number 0                            
-------------------------------------                            
DELETE /*+ GATHER_PLAN_STATISTICS */ FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE T2.C1=T1.C1)  

Plan hash value: 4152631912 

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |              |      1 |        |      0 |00:00:15.69 |     424K|  46355 |
|   1 |  DELETE             | T1           |      1 |        |      0 |00:00:15.69 |     424K|  46355 |
|   2 |   NESTED LOOPS SEMI |              |      1 |    333K|    333K|00:00:01.38 |    7484 |   2577 |
|   3 |    INDEX FULL SCAN  | SYS_C0016151 |      1 |   1000K|   1000K|00:00:00.02 |    1877 |   1920 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0016152 |   1000K|    110K|    333K|00:00:01.11 |    5607 |    657 |
-------------------------------------------------------------------------------------------------------

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

SQL> ROLLBACK;

Rollback complete.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> DELETE /*+ GATHER_PLAN_STATISTICS */ (SELECT C1 FROM T1 JOIN T2 USING (C1));

333000 rows deleted.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT           
-------------------------------------------------------------------------------------------------------
SQL_ID  c4xz24vawqqbw, child number 0                            
-------------------------------------                            
DELETE /*+ GATHER_PLAN_STATISTICS */ (SELECT C1 FROM T1 JOIN T2 USING (C1))                       

Plan hash value: 1028502382 

-------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT    |              |      1 |        |      0 |00:00:15.90 |     424K|  46355 |
|   1 |  DELETE             | T1           |      1 |        |      0 |00:00:15.90 |     424K|  46355 |
|   2 |   NESTED LOOPS      |              |      1 |    333K|    333K|00:00:03.69 |    7484 |   2577 |
|   3 |    INDEX FULL SCAN  | SYS_C0016151 |      1 |   1000K|   1000K|00:00:00.02 |    1877 |   1920 |
|*  4 |    INDEX UNIQUE SCAN| SYS_C0016152 |   1000K|      1 |    333K|00:00:01.13 |    5607 |    657 |
-------------------------------------------------------------------------------------------------------

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

SQL> ROLLBACK;

Rollback complete.

So, the most efficient execution plan is the first one (by 0.02 seconds), and the least efficient is the last one (0.21 seconds slower than the second SQL statement).  Oh, but there is a problem – compare the steps in the first and last execution plans.  OK, now compare the steps in the first and second execution plans.

OK, so the question remains: Which is most efficient when deleting rows: EXISTS, IN, or a VIEW?  It is pretty hard to tell when the optimizer automatically re-writes the queries that we submit.





Extract the First 4400 Images from Excel 2003 (and Above) and Transfer to a Database Table

28 12 2009

December 28, 2009

This code sample is an Excel 2003/2007 macro that extracts all of the named toolbar button pictures from Excel 2003/2007, transfers those pictures to an Oracle database (stored in a BLOB), and then retrieves each of the pictures and displays the pictures on an Excel worksheet.

First, we need to create a table to hold the pictures:

CREATE TABLE EXCEL2003_TOOLBAR_PICTURES (
  PICTURE_NAME VARCHAR2(60),
  PICTURE_SIZE NUMBER,
  PICTURE BLOB,
  PRIMARY KEY(PICTURE_NAME));

See the instructions in the previous blog article to enable macro support in Excel 2007 and add a reference to the ActiveX Data Objects in Excel 2003 and above.

The Excel macro code follows:

Private Sub ExtractAllImages2003()
    'Adapted from an example by John Walkenbach
    '    http://www.dailydoseofexcel.com/archives/2006/11/16/displaying-commandbar-faceid-images/
    'See also http://support.microsoft.com/kb/286460
    Dim i As Integer
    Dim intResult As Integer
    Dim intFileNum2 As Integer
    Dim lngNumPics As Long
    Dim sglX As Single
    Dim sglY As Single
    Dim strName As String
    Dim strSQL As String
    Dim strDatabase As String
    Dim strUserName As String
    Dim strPassword As String
    Dim bytPicture() As Byte

    Dim tbNewToolbar As CommandBar
    Dim tbcNewControl As CommandBarButton
    Dim picPicture As stdole.IPictureDisp
    Dim picMask As stdole.IPictureDisp
    Dim snpData As New ADODB.Recordset
    Dim dynData As New ADODB.Recordset
    Dim dbDatabase As New ADODB.Connection

    On Error Resume Next

    'Remove all of the previously created sheet
    Application.DisplayAlerts = False
    Sheets("BuiltInImages2003").Delete

    Sheets.Add
    ActiveSheet.Name = "BuiltInImages2003"

    'Delete existing TempFaceIds toolbar if it exists
    Application.CommandBars("TempFaceIds").Delete

    'Add an empty toolbar
    Set tbNewToolbar = Application.CommandBars.Add(Name:="TempFaceIds")

    'Create an object to act as a command bar control
    Set tbcNewControl = tbNewToolbar.Controls.Add(Type:=msoControlButton)

    Application.DisplayAlerts = True

    If Len(Dir("C:\ExcelBuiltInImages2003", vbDirectory)) < 4 Then
        'Create the folder to hold the exported pictures
        MkDir "C:\ExcelBuiltInImages2003"
    End If

    Err = 0

    strDatabase = "MyDB" 'From tnsnames.ora
    strUserName = "MyUserID"
    strPassword = "MyPassword"

    'Connect to the database
    'Oracle connection string
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    If (dbDatabase.State <> 1) Or (Err <> 0) Then
        intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
    End If

    'The table definition
    'CREATE TABLE EXCEL2003_TOOLBAR_PICTURES (
    '  PICTURE_NAME VARCHAR2(60),
    '  PICTURE_SIZE NUMBER,
    '  PICTURE BLOB,
    '  PRIMARY KEY(PICTURE_NAME));

    If Err = 0 Then
        'Remove pictures that were previously brought in
        strSQL = "DELETE FROM EXCEL2003_TOOLBAR_PICTURES"
        dbDatabase.Execute strSQL

        dbDatabase.BeginTrans

        'Prepare to add the new pictures to the database
        strSQL = "SELECT"
        strSQL = strSQL & "  PICTURE_NAME," & vbCrLf
        strSQL = strSQL & "  PICTURE_SIZE," & vbCrLf
        strSQL = strSQL & "  PICTURE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  EXCEL2003_TOOLBAR_PICTURES"
        dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic, adCmdText

        For i = 1 To 4400  'Maximum in Excel 2003 is around 10033
            'Set the picture on the command bar control to picture number i
            tbcNewControl.FaceId = i

            'Transfer the pictures from the command bar control to local variables
            Set picPicture = tbcNewControl.Picture
            Set picMask = tbcNewControl.Mask

            'Save the pictures to disk
            strName = "FaceID " & Format(i, "0000") & ".bmp"
            Application.StatusBar = strName
            stdole.SavePicture picPicture, "C:\ExcelBuiltInImages2003\temp.bmp"

            'Create a new row in the table
            dynData.AddNew
            dynData("picture_name") = strName
            dynData("picture_size") = FileLen("C:\ExcelBuiltInImages2003\temp.bmp")

            'Read the picture into the table
            intFileNum2 = FreeFile
            Open "C:\ExcelBuiltInImages2003\temp.bmp" For Binary As #intFileNum2
            'Prepare a variable of byte data type to hold the picture read from disk
            ReDim bytPicture(FileLen("C:\ExcelBuiltInImages2003\temp.bmp"))
            Get #intFileNum2, , bytPicture
            Close #intFileNum2

            'Write the picture into the table and save the row
            dynData.Fields("picture").AppendChunk bytPicture
            dynData.Update

            'Save the mask picture for the toolbar button
            stdole.SavePicture picMask, "C:\ExcelBuiltInImages2003\temp.bmp"
            strName = "FaceID " & Format(i, "0000") & " Mask.bmp"

            'Create a new row in the table
            dynData.AddNew
            dynData("picture_name") = strName
            dynData("picture_size") = FileLen("C:\ExcelBuiltInImages2003\temp.bmp")

            'Read the picture into the table
            intFileNum2 = FreeFile
            Open "C:\ExcelBuiltInImages2003\temp.bmp" For Binary As #intFileNum2
            'Prepare a variable of byte data type to hold the picture read from disk
            ReDim bytPicture(FileLen("C:\ExcelBuiltInImages2003\temp.bmp"))
            Get #intFileNum2, , bytPicture
            Close #intFileNum2

            'Write the picture into the table and save the row
            dynData.Fields("picture").AppendChunk bytPicture
            dynData.Update

            'Free the memory ffrom the local variables
            Set picPicture = Nothing
            Set picMask = Nothing

            'Allow Excel to process events every 160 pictures
            If i Mod 160 = 0 Then
                Application.ScreenUpdating = True
                DoEvents
                Application.ScreenUpdating = False
            End If
        Next i

        dbDatabase.CommitTrans
        dynData.Close

        'Delete the picture from the folder
        Kill "C:\ExcelBuiltInImages2003\temp.bmp"

        'Retrieve the pictures from the database and display in Excel
        i = 0
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  PICTURE_NAME," & vbCrLf
        strSQL = strSQL & "  PICTURE_SIZE," & vbCrLf
        strSQL = strSQL & "  PICTURE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  EXCEL2003_TOOLBAR_PICTURES" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  PICTURE_NAME"
        snpData.Open strSQL, dbDatabase

        If Not (snpData.EOF) Then
            Do While Not (snpData.EOF)
                i = i + 1
                Application.StatusBar = snpData("picture_name")

                'Retrieve the picture from the database and write to a file
                intFileNum2 = FreeFile
                ReDim bytPicture(snpData("picture_size"))
                bytPicture = snpData("picture")

                Open "C:\ExcelBuiltInImages2003\" & snpData("picture_name") For Binary As #intFileNum2
                Put #intFileNum2, , bytPicture
                Close #intFileNum2

                'There will be 20 pictures across the page, so identify the top left position of the picture
                sglX = ((i - 1) Mod 40) * 18
                sglY = Int((i - 1) / 40) * 18

                'Create the shape object and load the picture that was saved from the image object
                With Sheets("BuiltInImages2003").Shapes.AddShape(Type:=msoShapeRectangle, Left:=sglX, Top:=sglY, Width:=16, Height:=16)
                    .Line.Visible = False
                    .Fill.UserPicture "C:\ExcelBuiltInImages2003\" & snpData("picture_name")
                    .AlternativeText = snpData("picture_name")
                End With

                'Allow Excel to refresh the screen as every four rows complete
                If i Mod 160 = 0 Then
                    Application.ScreenUpdating = True
                    DoEvents
                    Application.ScreenUpdating = False
                End If

                snpData.MoveNext
            Loop
            snpData.Close
        End If
    End If

    Application.CommandBars("TempFaceIds").Delete

    Application.ScreenUpdating = True
    Application.StatusBar = ""
    dbDatabase.Close

    'Clean up
    Set snpData = Nothing
    Set dynData = Nothing
    Set dbDatabase = Nothing
End Sub

When the macro runs, it saves each of the built-in toolbar icons to a file named temp.bmp, and then inserts a row into the database table with the temp.bmp picture and the mask for the temp.bmp picture that will allow creating a semi-transparent button image.  Once all of the pictures are stored in the database, a query is run to retrieve each of the pictures, create a file in the ExcelBuiltInImages2003 folder for that picture, and then display the picture in Excel.  The screen is refreshed after every 160 pictures are displayed.  Note that most of the built-in toolbar icons are designed to be viewed at a size of 16 pixels by 16 pixels, so the icons are extracted at that size.  The picture filenames are written to the Alt Text property of each picture, which may be viewed by right-clicking a picture and selecting Size and Properties…

After the macro runs, the new worksheet will look something like the picture below:

Since the pictures are also saved to a file, they are ready to be used for other purposes:





Extract 1834 Images from Excel 2007 and Transfer to a Database Table

28 12 2009

December 28, 2009

(Portions of this code are adapted from the book “Excel 2007 Power Programming with VBA”  )

This code sample is an Excel 2007 macro that extracts all of the named toolbar button pictures from Excel 2007, transfers those pictures to an Oracle database (stored in a BLOB), and then retrieves each of the pictures and displays the pictures on an Excel worksheet.

First, we need to create a table to hold the pictures:

CREATE TABLE EXCEL2007_TOOLBAR_PICTURES (
  PICTURE_NAME VARCHAR2(60),
  PICTURE_SIZE NUMBER,
  PICTURE BLOB,
  PRIMARY KEY(PICTURE_NAME));

Download the ExcelImageList.doc file and save it using Microsoft Word as a Plain Text File with the name ExcelImageList.txt in the root of the C:\ drive.

We need to make certain that macro support is enabled in Excel 2007, by default it is disabled.  Follow steps 1 through 3 to verify that the Developer tab appears at the top of the screen:

On the Developer tab, click the Visual Basic button.  We need to add a reference to the Microsoft ActiveX Data Objects to allow the macro to interact with an Oracle database.  From the Tools menu, select References…

Locate one of the recent releases of Microsoft ActiveX Data Objects, select it, then click OK.

Finally, we create the following macro:

Private Sub ExtractAllImages()
    Dim i As Integer
    Dim intResult As Integer
    Dim intFileNum As Integer
    Dim intFileNum2 As Integer
    Dim sglX As Single
    Dim sglY As Single
    Dim strName As String
    Dim strSQL As String
    Dim strDatabase As String
    Dim strUserName As String
    Dim strPassword As String
    Dim bytPicture() As Byte

    Dim imgPicture As OLEObject
    Dim snpData As New ADODB.Recordset
    Dim dynData As New ADODB.Recordset
    Dim dbDatabase As New ADODB.Connection

    On Error Resume Next

    'Remove all of the previously created sheet
    Application.DisplayAlerts = False
    Sheets("BuiltInImages").Delete

    Sheets.Add
    ActiveSheet.Name = "BuiltInImages"

    DoEvents
    Application.DisplayAlerts = True

    If Len(Dir("C:\ExcelBuiltInImages", vbDirectory)) < 4 Then
        'Create the folder to hold the exported pictures
        MkDir "C:\ExcelBuiltInImages"
    End If

    'Create a temporary image object to hold the pictures from GetImageMso
    Set imgPicture = Sheets("BuiltInImages").OLEObjects.Add(classtype:="Forms.Image.1", Left:=800, Top:=1, Width:=32, Height:=32)
    With imgPicture.Object
        .AutoSize = True
        .BorderStyle = 0
    End With

    Err = 0
    intFileNum = FreeFile
    Open "C:\ExcelImageList.txt" For Input As #intFileNum

    If Err <> 0 Then
        intResult = MsgBox("Could not open the image list file." & vbCrLf & Error(Err), 16, "Excel Demo")
        Exit Sub
    End If

    strDatabase = "MyDB" 'From tnsnames.ora
    strUserName = "MyUserID"
    strPassword = "MyPassword"

    'Connect to the database
    'Oracle connection string
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"

    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open

    If (dbDatabase.State <> 1) Or (Err <> 0) Then
        intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
    End If

    'The table definition
    'CREATE TABLE EXCEL2007_TOOLBAR_PICTURES (
    '  PICTURE_NAME VARCHAR2(60),
    '  PICTURE BLOB,
    '  PRIMARY KEY(PICTURE_NAME));

    If Err = 0 Then
        'Remove pictures that were previously brought in
        strSQL = "DELETE FROM EXCEL2007_TOOLBAR_PICTURES"
        dbDatabase.Execute strSQL

        dbDatabase.BeginTrans

        'Prepare to add the new pictures to the database
        strSQL = "SELECT"
        strSQL = strSQL & "  PICTURE_NAME," & vbCrLf
        strSQL = strSQL & "  PICTURE_SIZE," & vbCrLf
        strSQL = strSQL & "  PICTURE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  EXCEL2007_TOOLBAR_PICTURES"
        dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic, adCmdText

        'Process each of the toolbar picture names from the text file
        Do While Not (EOF(intFileNum))
            i = i + 1
            'Read a toolbar picture name from the text file
            Line Input #intFileNum, strName
            Application.StatusBar = strName

            'Set the picture in the temporary image object, and then save to disk
            imgPicture.Object.Picture = Application.CommandBars.GetImageMso(strName, 32, 32)
            SavePicture imgPicture.Object.Picture, "C:\ExcelBuiltInImages\temp.bmp"

             'Create a new row in the table
            dynData.AddNew
            dynData("picture_name") = strName & ".bmp"
            dynData("picture_size") = FileLen("C:\ExcelBuiltInImages\temp.bmp")

            'Read the picture into the table
            intFileNum2 = FreeFile
            Open "C:\ExcelBuiltInImages\temp.bmp" For Binary As #intFileNum2

            'Prepare a variable of byte data type to hold the picture read from disk
            ReDim bytPicture(FileLen("C:\ExcelBuiltInImages\temp.bmp"))
            Get #intFileNum2, , bytPicture
            Close #intFileNum2

            'Write the picture into the table and save the row
            dynData.Fields("picture").AppendChunk bytPicture
            dynData.Update

            'Delete the picture from the folder
            Kill "C:\ExcelBuiltInImages\temp.bmp"
        Loop
        Application.StatusBar = ""

        dbDatabase.CommitTrans

        dynData.Close

        Close #intFileNum

        'Remove the temporary image object
        imgPicture.Delete

        'Retrieve the pictures from the database and display in Excel
        i = 0
        strSQL = "SELECT" & vbCrLf
        strSQL = strSQL & "  PICTURE_NAME," & vbCrLf
        strSQL = strSQL & "  PICTURE_SIZE," & vbCrLf
        strSQL = strSQL & "  PICTURE" & vbCrLf
        strSQL = strSQL & "FROM" & vbCrLf
        strSQL = strSQL & "  EXCEL2007_TOOLBAR_PICTURES" & vbCrLf
        strSQL = strSQL & "ORDER BY" & vbCrLf
        strSQL = strSQL & "  PICTURE_NAME"
        snpData.Open strSQL, dbDatabase

        If Not (snpData.EOF) Then
            Do While Not (snpData.EOF)
                i = i + 1
                Application.StatusBar = snpData("picture_name")

                'Retrieve the picture from the database and write to a file

                ReDim bytPicture(snpData("picture_size"))
                bytPicture = snpData("picture")
                intFileNum2 = FreeFile
                Open "C:\ExcelBuiltInImages\" & snpData("picture_name") For Binary As #intFileNum2
                Put #intFileNum2, , bytPicture
                Close #intFileNum2

                'There will be 20 pictures across the page, so identify the top left position of the picture
                sglX = ((i - 1) Mod 20) * 36
                sglY = Int((i - 1) / 20) * 36

                'Create the shape object and load the picture that was saved from the image object
                With Sheets("BuiltInImages").Shapes.AddShape(Type:=msoShapeRectangle, Left:=sglX, Top:=sglY, Width:=30, Height:=30)
                    .Line.Visible = False
                    .Fill.UserPicture "C:\ExcelBuiltInImages\" & snpData("picture_name")
                    .AlternativeText = snpData("picture_name")
                End With

                'Allow Excel to refresh the screen as every four rows complete
                If i Mod 80 = 0 Then
                    Application.ScreenUpdating = True
                    DoEvents
                    Application.ScreenUpdating = False
                End If

                snpData.MoveNext
            Loop
            snpData.Close
        End If
    End If
    Application.ScreenUpdating = True
    Application.StatusBar = ""
    dbDatabase.Close

    'Clean up
    Set snpData = Nothing
    Set dynData = Nothing
    Set dbDatabase = Nothing
    Set imgPicture = Nothing
End Sub

When the macro runs, it saves each of the built-in toolbar icons to a file named temp.bmp, and then inserts a row into the database table with the temp.bmp picture.  Once all of the pictures are stored in the database, a query is run to retrieve each of the pictures, create a file in ExcelBuiltInImages folder for that picture, and then display the picture in Excel.  The screen is refreshed after every 80 pictures are displayed.  Note that most of the built-in toolbar icons are designed to be viewed at a size of 16 pixels by 16 pixels, rather than 32 pixels by 32 pixels – so the images may appear a bit “blocky”.  The picture filenames are stored in the Alt Text property of each picture, which may be viewed by right-clicking a picture and selecting Size and Properties…

After the macro runs, the new worksheet will look something like the picture below:

Since the pictures are also saved to a file, they are ready to be used for other purposes:





High Value for MBRC Causes High BCHR, High CPU Usage, and Slow Performance

27 12 2009

December 27, 2009

What follows is part of a presentation that I gave in 2008. 

A sudden unexpected spike in server CPU usage was noticed while running a batch process in an ERP package that normally has most of its time captured in the SQL*Net message from client wait event. My Toy Project for Performance Tuning was actively logging database performance when the problem was noticed. Below is a marked up view of the program as it captured the spike in CPU activity:In this 83 second time period, the database instance performed 7.8 million consistent gets, and not a single physical read. The database had a 100% buffer cache hit ratio during this time period, and that is good, right? The database instance read 505.6 million rows by full table scan in the 83 seconds with relatively few rows read by index lookup, which might be a sign of a problem.

Clicking on the Rows Read by Table Scan statistic, we see the running history of the number of rows read by a full table scan, with the current statistic value in blue:If we dig a bit more, knowing that the number of rows retrieved by table scan seems high, we see that we had a quiet period, and then the number of rows read by full table scan jumped considerably. At the same time, the CPU usage also jumped. We should dig into the system a bit more deeply to understand why this is a problem.

Switching over the the SQL Monitor, we start to investigate:If we look at the SQL statements as they are executed, we see one SQL statement that seems to be performing a large number of buffer gets, or logical reads – about 5.1 million per minute when fetching 123 times. The execution plans for the SQL statement are shown in the bottom pane. Note that there are two execution plans for this SQL statement, with the second created due to a bind mismatch – the initial parse call did not initialize the bind variables which caused Oracle to treat the bind variable as a VARCHAR2(2000), the second parse initialized the bind variable as a CHAR.

Zooming in on the grid at the top:If we look at the SQL statements as they are executed, we see one SQL statement that seems to be performing a large number of buffer gets, or logical reads – about 5.1 million per minute when fetching 123 times with 120 executions. Something seems a bit odd.

Zooming in on the execution plan at the bottom:

Taking a look at the execution plan, we see that the query optimizer decided to perform a full table scan when executing this SQL statement, rather than using the selective index on the PART_ID column. Oracle is applying a filter against the table rows to find only those with a specific PART_ID having a QTY greater than the COSTED_QTY, and with a TYPE of O.

If we submit the SQL statement with the STATISTICS_LEVEL parameter set to ALL at the session level, and retrieve the actual execution timing by passing in ‘ALLSTATS LAST’ as the format parameter for DBMS_XPLAN.DISPLAY_CURSOR, we see the following:The above slide shows that a single execution of the query required roughly 0.5 seconds (10% of the estimated time), required 42,751 consistent gets, and returned 1 row when the optimizer predicted that 943 rows would be returned. The good news is that the BCHR is 100%, or maybe that is not good news, depending on who you ask.

The final slide shows the expected execution plan output by DBMS_XPLAN.DISPLAY_CURSOR:The above output shows that the query should be executing in 0.01 seconds or less with the same value specified for the PART_ID, with just 16 consistent gets. While not checked, the clustering factor for the X_INV_TRANS_1 index on the PART_ID column will certainly be high, perhaps close to the number of rows in the table.

What caused the above problem? Assume that the OPTIMIZER_INDEX_COST_ADJ parameter is left at its default value of 100, and system statistics were collected with a command like the following:

EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>30)

The above command captures system (CPU) statistics with a 30 minute time interval. The statistics describe the performance of the server, including CPU speed (possibly a measure of the number of consistent gets per second), single block physical read time, multi-block physical read time, average number of blocks read by multi-block reads, as well as several other statistics – these statistics are visible in the SYS.AUX_STATS$ view. Assume that the data is stored in ASSM tablespaces with AUTO segment space management, which gradually ramps up the extent size from 64KB (for the first 1MB of data) to 1MB and beyond. If DB_FILE_MULTIBLOCK_READ_COUNT is set to allow 1MB multi-block reads (or auto-tuned to allow 1MB multi-block reads), it is quite possible that the MBRC statistic in SYS.AUX_STATS$ could be set to a very high value, possibly close to 100, if system statistics are gathered shortly after bouncing the database. It is also possible that a clever DBA might have executed something like the following to force the MBRC statistic to a value that is close to what would be expected if full table scans involving physical reads might be required (DB_FILE_MULTIBLOCK_READ_COUNT was auto-tuned to 128):

EXEC DBMS_STATS.SET_SYSTEM_STATS('MBRC',100)

Since the optimizer assumes that every block needed for a query will result in a physical read, odd side-effects, such as high CPU usage with long execution times, may happen when every block needed is satisfied by blocks already in the buffer cache, and a popular value (possibly identifying 0.98% of the rows in the table) is specified on the initial hard parse when bind peeking is enabled (the default starting with Oracle 9.0.1).








Follow

Get every new post delivered to your Inbox.

Join 142 other followers