SESSION_CACHED_CURSORS – Possibly Interesting Details

21 07 2011

July 21, 2011

Have you ever wondered about the V$OPEN_CURSOR view, the SESSION_CACHED_CURSORS parameter, and the two session-level statistics “session cursor cache count” and “session cursor cache hits”?  I did after reading from two different sources that stated essentially that a larger shared pool would be required when the value for the SESSION_CACHED_CURSORS parameter is increased.  The shared pool? – but the session’s cursors are cached in the UGA, which is typically in the PGA (unless shared server is used), and as far as I am aware the PGA is not in the shared pool.

So, what do we (think that we) know about the session cached cursors?

  • The per session limit for the number of cached cursors is specified by the SESSION_CACHED_CURSORS parameter.
  • Prior to the release of the Oracle Database 9.2.0.5 patchset the OPEN_CURSORS parameter controlled the number of cached cursors for PL/SQL execution, and as of 9.2.0.5 the SESSION_CACHED_CURSORS parameter controls the number of PL/SQL cursors that are cached per session (source).
  • In recent Oracle Database release versions the SESSION_CACHED_CURSORS parameter defaults to a value of 50.
  • Assuming that the SESSION_CACHED_CURSORS parameter is set to a value greater than 0, a SQL statement that is executed three times in the same session will be added to that session’s cursor cache and will remain open, even when explicitly closed by the session.
  • Starting in Oracle Database 11.1, if a 10046 trace is enabled for a session and a cursor is closed, a CLOSE # line will be written to the 10046 trace file, with the type= parameter indicating whether or not the cursor was added to the session cursor cache (and was thus actually not closed).
  • In V$MYSTAT, the session-level “session cursor cache count” statistic indicates the number of cursors that are cached for the current session, while the “session cursor cached hits” statistic indicates the number of times the current session has accessed those cached cursors.
  • V$OPEN_CURSOR shows the cursors that are currently open for all connected sessions.  Those cursors that are in the session cursor cache will also appear in V$OPEN_CURSOR.

The above bullet points are probably common knowledge, but is it all true?

Let’s check the Oracle Database 11.2 documentation for V$OPEN_CURSOR:

“V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed, or cached.”

Nothing out of the ordinary with the above quote – it seems to be well aligned with the last of the above bullet points.

Let’s check the Oracle Database 11.2 documentation for SESSION_CACHED_CURSORS:

Default value: 50

“SESSION_CACHED_CURSORS specifies the number of session cursors to cache. Repeated parse calls of the same SQL (including recursive SQL) or PL/SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.”

The above confirms bullet points one and three that were listed above, and almost bullet point two.

Let’s check the behavior changes listed for Oracle Database 11.2 in the documentation library:

“Starting with Oracle Database 10g Release 1 (10.1), the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter. In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter.”

Although the above quote states Oracle Database 10.1 rather than 9.2.0.5, the quote confirms the change mentioned in the second of the above bullet points.

Let’s take a look at part of the Performance Tuning Guide from the 11.2 documentation set:

“Oracle checks the library cache to determine whether more than three parse requests have been issued on a given statement. If so, then Oracle assumes that the session cursor associated with the statement should be cached and moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session then find the cursor in the session cursor cache.”

Although the above states that a SQL statement must be parsed more than three times before it is added to the session cursor cache rather than executed three times, the quote basically confirms the fourth of the above bullet points.

Checking the statistic descriptions for Oracle Database 11.2:

session cursor cache count: Total number of cursors cached. This statistic is incremented only if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the SESSION_CACHED_CURSORS parameter, the value of the parameter should be increased.

session cursor cache hits: Number of hits in the session cursor cache. A hit means that the SQL statement did not have to be reparsed. Subtract this statistic from “parse count (total)” to determine the real number of parses that occurred.”

The above quote seems to confirm the second to the last of the above bullet points.

Everyone happy, or should we test?

We need a table for a bit of experimentation, and we might as well flush the shared pool too:

CREATE TABLE T4 AS
SELECT
  *
FROM
  ALL_OBJECTS
WHERE
  ROWNUM<=10;

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

ALTER SYSTEM FLUSH SHARED_POOL;

Now we need some sort of an elegant way to test the bulleted items that are listed at the start of this article.  Of course, a VBS script that is executed on the poor Windows client computer that is sitting under the finger smudged iPad is the perfect way to run a test.  In the script below replace MyUsername with a valid username for your database, replace MyPassword with the password for that user, and replace MyDatabase with the database SID as it appears in the TNSNAMES.ORA file.  The script performs the following actions:

  1. Connects to the database
  2. Determines the SID and SERIAL# for the session and writes those values to the C:\SessionCursorCache.txt text file.
  3. Enables a 10046 trace at level 1 (minimal detail with no wait events)
  4. Writes the session’s current values for the “session cursor cache hits” and “session cursor cache count” to the text file.
  5. Writes to the text file all SQL statements from V$OPEN_CURSOR where the SID matches the SID for the current session.
  6. Opens two cursors for the SQL statement “SELECT * FROM T4”
  7. Writes the session’s current values for the “session cursor cache hits” and “session cursor cache count” to the text file.  This should indicate if opening the cursors in step 6 triggered a hit on the cached cursors.
  8. Retrieves all rows from the two cursors and explicitly closes those cursors (potentially alternating between fetches from the two cursors if table T4 contained more than 1,000 rows).
  9. Repeat steps 4 through 8 two more times to see how repeated executions affect the statistics and the cursors that are cached.
  10. Disconnects from the database.

The script follows (script download, save with .vbs extension SessionCachedCursorsTest.vbs):

Dim strSQL
Dim strSQL2
Dim strSQL3
Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim snpData2
Dim snpDataCursorCache
Dim snpDataOpenSQL
Dim snpDataSID
Dim dbDatabase
Dim lngCnt
Dim intPass
Dim objFSO
Dim objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile=objFSO.CreateTextFile("C:\SessionCursorCache.txt", True)

Set snpData = CreateObject("ADODB.Recordset")
Set snpData2 = CreateObject("ADODB.Recordset")
Set snpDataCursorCache = CreateObject("ADODB.Recordset")
Set snpDataOpenSQL = CreateObject("ADODB.Recordset")
Set snpDataSID = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDatabase"
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";FetchSize=5000;"

dbDatabase.Open

strSQL = "SELECT" & VBCrLf
strSQL = strSQL & "  S.SID," & VBCrLf
strSQL = strSQL & "  S.SERIAL# SERIALN" & VBCrLf
strSQL = strSQL & "FROM" & VBCrLf
strSQL = strSQL & "  V$SESSION S," & VBCrLf
strSQL = strSQL & "  (SELECT" & VBCrLf
strSQL = strSQL & "    SID" & VBCrLf
strSQL = strSQL & "  FROM" & VBCrLf
strSQL = strSQL & "    V$MYSTAT" & VBCrLf
strSQL = strSQL & "  WHERE" & VBCrLf
strSQL = strSQL & "    ROWNUM=1) M" & VBCrLf
strSQL = strSQL & "WHERE" & VBCrLf
strSQL = strSQL & "  S.SID=M.SID" & VBCrLf

snpDataSID.Open strSQL, dbDatabase
If snpDataSID.State = 1 Then
  Do While Not(snpDataSID.EOF)
    objFile.Write "SID: " & snpDataSID("sid") & ",  SERIAL#: " & snpDataSID("serialn") & VBCrLf
    snpDataSID.MoveNext
  Loop
  snpDataSID.Close
  objFile.Write " " & VBCrLf
End If

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

strSQL2 = "SELECT" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME STAT_NAME," & VBCrLf
strSQL2 = strSQL2 & "  M.VALUE" & VBCrLf
strSQL2 = strSQL2 & "FROM" & VBCrLf
strSQL2 = strSQL2 & "  V$MYSTAT M," & VBCrLf
strSQL2 = strSQL2 & "  V$STATNAME SN" & VBCrLf
strSQL2 = strSQL2 & "WHERE" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME IN ('session cursor cache hits','session cursor cache count')" & VBCrLf
strSQL2 = strSQL2 & "  AND SN.STATISTIC#=M.STATISTIC#" & VBCrLf
strSQL2 = strSQL2 & "ORDER BY" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME"

strSQL3 = "SELECT" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT" & VBCrLf
strSQL3 = strSQL3 & "FROM" & VBCrLf
strSQL3 = strSQL3 & "  V$OPEN_CURSOR" & VBCrLf
strSQL3 = strSQL3 & "WHERE" & VBCrLf
strSQL3 = strSQL3 & "  SID=" & VBCrLf
strSQL3 = strSQL3 & "    (SELECT" & VBCrLf
strSQL3 = strSQL3 & "       SID" & VBCrLf
strSQL3 = strSQL3 & "     FROM" & VBCrLf
strSQL3 = strSQL3 & "       V$MYSTAT" & VBCrLf
strSQL3 = strSQL3 & "     WHERE" & VBCrLf
strSQL3 = strSQL3 & "       ROWNUM=1)" & VBCrLf
strSQL3 = strSQL3 & "ORDER BY" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT"

For intPass = 1 to 3
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "Before Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

snpDataOpenSQL.Open strSQL3, dbDatabase
lngCnt = 0
If snpDataOpenSQL.State = 1 Then
  Do While Not(snpDataOpenSQL.EOF)
    lngCnt = lngCnt + 1
    objFile.Write lngCnt & "  " & snpDataOpenSQL("sql_text") & VBCrLf
    snpDataOpenSQL.MoveNext
  Loop
  snpDataOpenSQL.Close
  objFile.Write " " & VBCrLf
End If

strSQL = "SELECT * FROM T4"
snpData.Open strSQL, dbDatabase
snpData2.Open strSQL, dbDatabase
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "After Open Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

lngCnt = 0
If snpData.State = 1 Then
  Do While Not(snpData.EOF)
    Do While (lngCnt < 1000) and Not(snpData.EOF)
      lngCnt = lngCnt + 1
      snpData.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData2.EOF)
      lngCnt = lngCnt + 1
      snpData2.MoveNext
    Loop
    lngCnt = 0
  Loop
End If

snpData.Close
snpData2.Close

Next

dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'"
objFile.Close

Set snpData = Nothing
Set snpData2 = Nothing
Set snpDataCursorCache = Nothing
Set snpDataOpenSQL = Nothing
Set snpDataSID = Nothing

dbDatabase.Close
Set dbDatabase = Nothing
Set objFile = Nothing
Set objFSO = Nothing

If you save the script as SessionCachedCursorsTest.vbs you can execute it from a Windows command line with the following command:

CSCRIPT SessionCachedCursorsTest.vbs

The script will generate a file named C:\SessionCursorCache.txt (if User Access Control is enabled on the Windows computer, you will need to modify the script to create the file in a different location).  What might you find in the generated file?  This is what I saw after the script ran shortly after flushing the shared pool (64 bit Oracle Database 11.2.0.2):

SID: 66,  SERIAL#: 7863

Before Pass #1
session cursor cache count 49
session cursor cache hits 240

1  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
2  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
3  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
4  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
5  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
6  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
9  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#,intcol#,charsetid,charsetform from col$ where ob
12  select col#,intcol#,charsetid,charsetform from col$ where ob
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
19  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
20  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
21  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
22  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
23  select cols,audit$,textlength,intcols,property,flags,rowid f
24  select cols,audit$,textlength,intcols,property,flags,rowid f
25  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
26  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
29  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
33  select intcol#, toid, version#, intcols, intcol#s, flags, sy
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#, toid, version#, intcols, intcol#s, flags, sy
36  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select order#,columns,types from access$ where d_obj#=:1
47  select owner#,name,namespace,remoteowner,linkname,p_timestam
48  select rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, analy
49  select text from view$ where rowid=:1
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select timestamp, flags from fixed_obj$ where obj#=:1

After Open Pass #1
session cursor cache count 49
session cursor cache hits 279

Before Pass #2
session cursor cache count 49
session cursor cache hits 279

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
4  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
5  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
6  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
9  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#,intcol#,charsetid,charsetform from col$ where ob
12  select col#,intcol#,charsetid,charsetform from col$ where ob
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
19  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
20  select cols,audit$,textlength,intcols,property,flags,rowid f
21  select cols,audit$,textlength,intcols,property,flags,rowid f
22  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
23  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
24  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
25  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
26  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
29  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
33  select intcol#, toid, version#, intcols, intcol#s, flags, sy
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
36  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
47  select order#,columns,types from access$ where d_obj#=:1
48  select owner#,name,namespace,remoteowner,linkname,p_timestam
49  select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select type#,blocks,extents,minexts,maxexts,extsize,extpct,u

After Open Pass #2
session cursor cache count 50
session cursor cache hits 280

Before Pass #3
session cursor cache count 50
session cursor cache hits 281

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ rule */ bucket, endpoint, col#, epvalue from hist
7  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
8  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
9  select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt,
10  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
11  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
12  select col#, grantee#, privilege#,max(mod(nvl(option$,0),2))
13  select col#,intcol#,charsetid,charsetform from col$ where ob
14  select col#,intcol#,charsetid,charsetform from col$ where ob
15  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
16  select col#,intcol#,ntab# from ntab$ where obj#=:1 order by
17  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
18  select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
19  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
20  select col#,intcol#,toid,version#,packed,intcols,intcol#s,fl
21  select cols,audit$,textlength,intcols,property,flags,rowid f
22  select cols,audit$,textlength,intcols,property,flags,rowid f
23  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
24  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
25  select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 fr
26  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
27  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
28  select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
29  select distinct(-privilege#),nvl(option$,0) from sysauth$ wh
30  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
31  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
32  select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
33  select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
34  select intcol#, toid, version#, intcols, intcol#s, flags, sy
35  select intcol#, toid, version#, intcols, intcol#s, flags, sy
36  select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
37  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
38  select intcol#,type,flags,lobcol,objcol,extracol,schemaoid,
39  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
40  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
41  select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
42  select node,owner,name from syn$ where obj#=:1
43  select node,owner,name from syn$ where obj#=:1
44  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
45  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
46  select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
47  select order#,columns,types from access$ where d_obj#=:1
48  select owner#,name,namespace,remoteowner,linkname,p_timestam
49  select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t
50  select text from view$ where rowid=:1
51  select timestamp, flags from fixed_obj$ where obj#=:1
52  select type#,blocks,extents,minexts,maxexts,extsize,extpct,u

After Open Pass #3
session cursor cache count 50
session cursor cache hits 284

Reading the above, before our newly created session even selected from table T4, the statistics indicate that somehow there were already 49 cursors in that session’s cursor cache (many of the SQL statements appeared multiple times) and there were already 240 hits on those cached cursors.  Did this session really perform more than 387 (49*3 + 240) executions of SQL statements before our first selection from T4, or does V$OPEN_CURSOR perform differently than described in the documentation?  By the time the two cursors selecting from T4 were opened, the number of cursor cache hits jumped by 39.  After the two cursors were closed (Before Pass #2) we are able to confirm that the select from table T4 was not added to the session cursor cache and there were still 49 cursors in that cache.

On the second pass, we are able to confirm that the select from table T4 is not yet in the session cursor cache.  Immediately after the two cursors selected from table T2 were opened, we see that the session experienced one more cursor cache hit, and another cursor was added to the session cursor cache.

At the start of the third pass, we see that our select from T2 is now in the session cursor cache, not once, but twice, there were 50 cursors in the session cursor cache, and there was one more session cursor cache hit.  When the two cursors selecting from table T4 were opened, we see that the session experienced three more cursor cache hits.

Let’s take a quick look at portions of the 10046 trace file that are related to the SQL statements that were executed by our script (just primarily focusing on the parse and close calls):

PARSING IN CURSOR #429272512 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769436992465 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT
CLOSE #429272512:c=0,e=16,dep=0,type=0,tim=2769436996834

PARSING IN CURSOR #429272512 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437006931 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #429272512:c=0,e=19,dep=0,type=0,tim=2769437013363

PARSING IN CURSOR #429272512 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437025761 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #441175000 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437031138 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #440835720 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769437036683 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT

CLOSE #441175000:c=0,e=11,dep=0,type=0,tim=2769437040272
CLOSE #429272512:c=0,e=8,dep=0,type=0,tim=2769437040299
CLOSE #440835720:c=0,e=6,dep=0,type=0,tim=2769437040318
=====================
PARSING IN CURSOR #429272512 len=212 dep=0 uid=64 oct=3 lid=64 tim=2769437040366 hv=3255970530 ad='3ed509198' sqlid='c2a4dv3114ar2'
SELECT
  SN.NAME STAT_NAME,
  M.VALUE
FROM
  V$MYSTAT M,
  V$STATNAME SN
WHERE
  SN.NAME IN ('session cursor cache hits','session cursor cache count')
  AND SN.STATISTIC#=M.STATISTIC#
ORDER BY
  SN.NAME
END OF STMT

CLOSE #429272512:c=0,e=13,dep=0,type=1,tim=2769437042471
=====================
PARSING IN CURSOR #441175000 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437042547 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #441175000:c=0,e=19,dep=0,type=0,tim=2769437051038
=====================
PARSING IN CURSOR #441175000 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437051137 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

PARSING IN CURSOR #440600648 len=16 dep=0 uid=64 oct=3 lid=64 tim=2769437053863 hv=1886046789 ad='3eba6b810' sqlid='49fgb3ts6pkk5'
SELECT * FROM T4
END OF STMT

CLOSE #440600648:c=0,e=10,dep=0,type=1,tim=2769437060397
CLOSE #441175000:c=0,e=14,dep=0,type=1,tim=2769437060431
CLOSE #429272512:c=0,e=14,dep=0,type=3,tim=2769437060459
PARSE #429272512:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1338012530,tim=2769437060499
CLOSE #429272512:c=0,e=31,dep=0,type=3,tim=2769437062969
=====================
PARSING IN CURSOR #440639688 len=161 dep=0 uid=64 oct=3 lid=64 tim=2769437063067 hv=3838443513 ad='3eb511630' sqlid='babcumbkcmzzt'
SELECT
  SQL_TEXT
FROM
  V$OPEN_CURSOR
WHERE
  SID=
    (SELECT
       SID
     FROM
       V$MYSTAT
     WHERE
       ROWNUM=1)
ORDER BY
  SQL_TEXT
END OF STMT
CLOSE #440639688:c=0,e=20,dep=0,type=1,tim=2769437071488

PARSE #440600648:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2560505625,tim=2769437071547
PARSE #441175000:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2560505625,tim=2769437073533
PARSE #429272512:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1338012530,tim=2769437076014
CLOSE #441175000:c=0,e=7,dep=0,type=3,tim=2769437079257
CLOSE #440600648:c=0,e=4,dep=0,type=3,tim=2769437079281

What does the above show?  Notice that the numbers following type= on the CLOSE lines seem to change – what do those numbers mean?  If we refer to page 284 of the book “Secrets of the Oracle Database“, we find the following information:

type=0: cursor is actually closed.
type=1: cursor is added to the session cursor cache without pushing an existing cursor out of the session cursor cache.
type=2: cursor is added to the session cursor cache, in the process pushing an existing cursor out of the session cursor cache.
type=3: cursor was already in the session cursor cache

So, the above indicates that the cursors are actually added to the session cursor cache NOT during a parse call, but instead when the cursor is attempted to be explicitly closed.  The documentation describing the SESSION_CACHED_CURSORS parameter, referenced above, appears to be misleading.  It is a bit odd that our select from T4 was added to the session cursor cache twice.  It is also a bit odd that the large cursor numbers, introduced with Oracle Database 11.2.0.2 that I understand to represent the address of the cursor, were reused for different SQL statements (this might have significance in a later blog article).

Just for fun, let’s re-execute the VBS script:

CSCRIPT SessionCachedCursorsTest.vbs

This is now the script’s output:

SID: 192,  SERIAL#: 693

Before Pass #1
session cursor cache count 2
session cursor cache hits 1

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
4  select /*+ connect_by_filtering */ privilege#,level from sys
5  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #1
session cursor cache count 4
session cursor cache hits 2

Before Pass #2
session cursor cache count 6
session cursor cache hits 3

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #2
session cursor cache count 6
session cursor cache hits 7

Before Pass #3
session cursor cache count 6
session cursor cache hits 8

1  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
2  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
3  SELECT * FROM T4
4  SELECT * FROM T4
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select privilege# from sysauth$ where (grantee#=:1 or grante

After Open Pass #3
session cursor cache count 6
session cursor cache hits 12

In the above, you will likely first notice that the SID and SERIAL# for our newly created session is not the same as it was earlier.  At the start of the output, before the first execution of the select from T4, we see that there were two cursors in the session’s cursor cache, and one hit on one of those two cursors – quite a change from the first execution of the script.  Immediately after opening cursors that selected from T4 we see that there were 4 cursors in the session cursor count, and 2 hits on those cursors.

Now another big change before the start of the second pass, we see that there were 6 cursors in the session cursor cache, including two selects from T4 –  previously, those cursors were added to the session cursor cache just before the start of the third pass.  After opening the two cursors selecting from T4, there were 4 more hits on the session cursor cache.

So, what are the apparent take-aways from this test case:

  • After flushing the shared pool a session’s cursor cache may contain a large number of SQL statements, duplicated in some cases, that may have only been executed a single time by the session at a depth greater than 0 (as indicated in a 10046 trace file).
  • After a SQL statement is parsed and closed 3 times the SQL statement is eligible for the session cursor cache when the SQL statement is closed for the third time.
  • If a SQL statement was previously parsed and closed 3 times by another session, the SQL statement is eligible for a different session’s cursor cache the first time that a cursor with that SQL statement is closed in the session.
  • Oracle Database 11.2.0.2’s huge cursor numbers that are written to 10046 trace files may be reused for entirely different SQL statements, and that does not necessarily indicate a cursor leak.
  • If a SQL statement is opened multiple times in a session without being closed between opens, it is quite possible that all of the cursors using the SQL statement will be added to the session cached cursors (the SQL statement will appear multiple times in V$OPEN_CURSOR) when those cursors are explicitly closed.

At this point, you are probably wondering what might happen when we open 12 cursors that select from T4.  Here is the modified script (script download, save with .vbs extension SessionCachedCursorsTest2.vbs):

Dim strSQL
Dim strSQL2
Dim strSQL3
Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim snpData2
Dim snpData3
Dim snpData4
Dim snpData5
Dim snpData6
Dim snpData7
Dim snpData8
Dim snpData9
Dim snpData10
Dim snpData11
Dim snpData12
Dim snpDataCursorCache
Dim snpDataOpenSQL
Dim snpDataSID
Dim dbDatabase
Dim lngCnt
Dim intPass
Dim objFSO
Dim objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile=objFSO.CreateTextFile("C:\SessionCursorCache2.txt", True)

Set snpData = CreateObject("ADODB.Recordset")
Set snpData2 = CreateObject("ADODB.Recordset")
Set snpData3 = CreateObject("ADODB.Recordset")
Set snpData4 = CreateObject("ADODB.Recordset")
Set snpData5 = CreateObject("ADODB.Recordset")
Set snpData6 = CreateObject("ADODB.Recordset")
Set snpData7 = CreateObject("ADODB.Recordset")
Set snpData8 = CreateObject("ADODB.Recordset")
Set snpData9 = CreateObject("ADODB.Recordset")
Set snpData10 = CreateObject("ADODB.Recordset")
Set snpData11 = CreateObject("ADODB.Recordset")
Set snpData12 = CreateObject("ADODB.Recordset")
Set snpDataCursorCache = CreateObject("ADODB.Recordset")
Set snpDataOpenSQL = CreateObject("ADODB.Recordset")
Set snpDataSID = CreateObject("ADODB.Recordset")
Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDatabase"
dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";FetchSize=5000;"

dbDatabase.Open

strSQL = "SELECT" & VBCrLf
strSQL = strSQL & "  S.SID," & VBCrLf
strSQL = strSQL & "  S.SERIAL# SERIALN" & VBCrLf
strSQL = strSQL & "FROM" & VBCrLf
strSQL = strSQL & "  V$SESSION S," & VBCrLf
strSQL = strSQL & "  (SELECT" & VBCrLf
strSQL = strSQL & "    SID" & VBCrLf
strSQL = strSQL & "  FROM" & VBCrLf
strSQL = strSQL & "    V$MYSTAT" & VBCrLf
strSQL = strSQL & "  WHERE" & VBCrLf
strSQL = strSQL & "    ROWNUM=1) M" & VBCrLf
strSQL = strSQL & "WHERE" & VBCrLf
strSQL = strSQL & "  S.SID=M.SID" & VBCrLf

snpDataSID.Open strSQL, dbDatabase
If snpDataSID.State = 1 Then
  Do While Not(snpDataSID.EOF)
    objFile.Write "SID: " & snpDataSID("sid") & ",  SERIAL#: " & snpDataSID("serialn") & VBCrLf
    snpDataSID.MoveNext
  Loop
  snpDataSID.Close
  objFile.Write " " & VBCrLf
End If

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

strSQL2 = "SELECT" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME STAT_NAME," & VBCrLf
strSQL2 = strSQL2 & "  M.VALUE" & VBCrLf
strSQL2 = strSQL2 & "FROM" & VBCrLf
strSQL2 = strSQL2 & "  V$MYSTAT M," & VBCrLf
strSQL2 = strSQL2 & "  V$STATNAME SN" & VBCrLf
strSQL2 = strSQL2 & "WHERE" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME IN ('session cursor cache hits','session cursor cache count')" & VBCrLf
strSQL2 = strSQL2 & "  AND SN.STATISTIC#=M.STATISTIC#" & VBCrLf
strSQL2 = strSQL2 & "ORDER BY" & VBCrLf
strSQL2 = strSQL2 & "  SN.NAME"

strSQL3 = "SELECT" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT" & VBCrLf
strSQL3 = strSQL3 & "FROM" & VBCrLf
strSQL3 = strSQL3 & "  V$OPEN_CURSOR" & VBCrLf
strSQL3 = strSQL3 & "WHERE" & VBCrLf
strSQL3 = strSQL3 & "  SID=" & VBCrLf
strSQL3 = strSQL3 & "    (SELECT" & VBCrLf
strSQL3 = strSQL3 & "       SID" & VBCrLf
strSQL3 = strSQL3 & "     FROM" & VBCrLf
strSQL3 = strSQL3 & "       V$MYSTAT" & VBCrLf
strSQL3 = strSQL3 & "     WHERE" & VBCrLf
strSQL3 = strSQL3 & "       ROWNUM=1)" & VBCrLf
strSQL3 = strSQL3 & "ORDER BY" & VBCrLf
strSQL3 = strSQL3 & "  SQL_TEXT"

For intPass = 1 to 3
snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "Before Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

snpDataOpenSQL.Open strSQL3, dbDatabase
lngCnt = 0
If snpDataOpenSQL.State = 1 Then
  Do While Not(snpDataOpenSQL.EOF)
    lngCnt = lngCnt + 1
    objFile.Write lngCnt & "  " & snpDataOpenSQL("sql_text") & VBCrLf
    snpDataOpenSQL.MoveNext
  Loop
  snpDataOpenSQL.Close
  objFile.Write " " & VBCrLf
End If

strSQL = "SELECT * FROM T4"
snpData.Open strSQL, dbDatabase
snpData2.Open strSQL, dbDatabase
snpData3.Open strSQL, dbDatabase
snpData4.Open strSQL, dbDatabase
snpData5.Open strSQL, dbDatabase
snpData6.Open strSQL, dbDatabase
snpData7.Open strSQL, dbDatabase
snpData8.Open strSQL, dbDatabase
snpData9.Open strSQL, dbDatabase
snpData10.Open strSQL, dbDatabase
snpData11.Open strSQL, dbDatabase
snpData12.Open strSQL, dbDatabase

snpDataCursorCache.Open strSQL2, dbDatabase
If snpDataCursorCache.State = 1 Then
  objFile.Write "After Open Pass #" & intPass & VBCrLf
  Do While Not(snpDataCursorCache.EOF)
    objFile.Write snpDataCursorCache("stat_name") & " " & snpDataCursorCache("value") & VBCrLf
    snpDataCursorCache.MoveNext
  Loop
  snpDataCursorCache.Close
  objFile.Write " " & VBCrLf
End If

lngCnt = 0
If snpData.State = 1 Then
  Do While Not(snpData.EOF)
    Do While (lngCnt < 1000) and Not(snpData.EOF)
      lngCnt = lngCnt + 1
      snpData.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData2.EOF)
      lngCnt = lngCnt + 1
      snpData2.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData3.EOF)
      lngCnt = lngCnt + 1
      snpData3.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData4.EOF)
      lngCnt = lngCnt + 1
      snpData4.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData5.EOF)
      lngCnt = lngCnt + 1
      snpData5.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData6.EOF)
      lngCnt = lngCnt + 1
      snpData6.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData7.EOF)
      lngCnt = lngCnt + 1
      snpData7.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData8.EOF)
      lngCnt = lngCnt + 1
      snpData8.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData9.EOF)
      lngCnt = lngCnt + 1
      snpData9.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData10.EOF)
      lngCnt = lngCnt + 1
      snpData10.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData11.EOF)
      lngCnt = lngCnt + 1
      snpData11.MoveNext
    Loop
    lngCnt = 0

    Do While (lngCnt < 1000) and Not(snpData12.EOF)
      lngCnt = lngCnt + 1
      snpData12.MoveNext
    Loop
    lngCnt = 0
  Loop
End If

snpData.Close
snpData2.Close
snpData3.Close
snpData4.Close
snpData5.Close
snpData6.Close
snpData7.Close
snpData8.Close
snpData9.Close
snpData10.Close
snpData11.Close
snpData12.Close

Next

dbDatabase.Execute "ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'"
objFile.Close

Set snpData = Nothing
Set snpData2 = Nothing
Set snpData3 = Nothing
Set snpData4 = Nothing
Set snpData5 = Nothing
Set snpData6 = Nothing
Set snpData7 = Nothing
Set snpData8 = Nothing
Set snpData9 = Nothing
Set snpData10 = Nothing
Set snpData11 = Nothing
Set snpData12 = Nothing
Set snpDataCursorCache = Nothing
Set snpDataOpenSQL = Nothing
Set snpDataSID = Nothing

dbDatabase.Close
Set dbDatabase = Nothing
Set objFile = Nothing
Set objFSO = Nothing

The following shows the output that I received on the first execution (the shared pool was not flushed prior to executing the script):

SID: 159,  SERIAL#: 5853

Before Pass #1
session cursor cache count 7
session cursor cache hits 1

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
5  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
6  select /*+ connect_by_filtering */ privilege#,level from sys
7  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
8  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
9  select privilege# from sysauth$ where (grantee#=:1 or grante
10  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #1
session cursor cache count 9
session cursor cache hits 2

Before Pass #2
session cursor cache count 21
session cursor cache hits 3

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT * FROM T4
5  SELECT * FROM T4
6  SELECT * FROM T4
7  SELECT * FROM T4
8  SELECT * FROM T4
9  SELECT * FROM T4
10  SELECT * FROM T4
11  SELECT * FROM T4
12  SELECT * FROM T4
13  SELECT * FROM T4
14  SELECT * FROM T4
15  SELECT * FROM T4
16  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
17  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
18  select /*+ connect_by_filtering */ privilege#,level from sys
19  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
20  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
21  select privilege# from sysauth$ where (grantee#=:1 or grante
22  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #2
session cursor cache count 21
session cursor cache hits 17

Before Pass #3
session cursor cache count 21
session cursor cache hits 18

1  SELECT    S.SID,    S.SERIAL# SERIALN  FROM    V$SESSION S,
2  SELECT    SN.NAME STAT_NAME,    M.VALUE  FROM    V$MYSTAT M,
3  SELECT    SQL_TEXT  FROM    V$OPEN_CURSOR  WHERE    SID=   
4  SELECT * FROM T4
5  SELECT * FROM T4
6  SELECT * FROM T4
7  SELECT * FROM T4
8  SELECT * FROM T4
9  SELECT * FROM T4
10  SELECT * FROM T4
11  SELECT * FROM T4
12  SELECT * FROM T4
13  SELECT * FROM T4
14  SELECT * FROM T4
15  SELECT * FROM T4
16  SELECT PARAMETER, VALUE FROM SYS.NLS_DATABASE_PARAMETERS WHE
17  insert into sys.aud$( sessionid,entryid,statement,ntimestamp
18  select /*+ connect_by_filtering */ privilege#,level from sys
19  select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U
20  select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECON
21  select privilege# from sysauth$ where (grantee#=:1 or grante
22  select value$ from props$ where name = 'GLOBAL_DB_NAME'

After Open Pass #3
session cursor cache count 21
session cursor cache hits 32

Is that what you expected to see?

We still have one remaining question – is a larger shared pool required when increasing the SESSION_CACHED_CURSORS parameter (the parameter was set to the default value of 50 for this blog article).  To help you decide, refer to the following:

AskTom Article

Blog article written by Jonathan Lewis, specifically this comment:

“Every individiual holding a cursor open has an entry in x$kgllk – which is in the SGA – and these entries seem to be 172 bytes long in 10g (152 in 9i). So, clearly, if you hold more cursors open, you will be using more memory for these structures.”





V$SESSION’s ROW_WAIT_OBJ# Column Shows the DATA_OBJECT_ID – Verify the Documentation Challenge

7 06 2011

June 7, 2011

While reading through the alpha copy of a performance tuning book, I noticed a set of SQL statements that were identical to those found in the Oracle Database 11.2 Performance Tuning Guide.  The set of SQL statements from the Performance Tuning Guide follow:

10.3.1.1 Causes
To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#
  FROM V$SESSION
 WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

In an earlier article we found that V$SESSION’s ROW_WAIT_OBJ# column indicated the OBJECT_ID, not the DATA_OBJECT_ID of the object (as found in DBA_OBJECTS) and the same was true for the obj# entry found in a 10046 trace file.  When you first create an index the OBJECT_ID and the DATA_OBJECT_ID values will be the same for the index, as displayed in DBA_OBJECTS.  However, when you rebuild the index the DATA_OBJECT_ID value for the index will change while the OBJECT_ID value remains unchanged.  Likewise, when you first create a table the OBJECT_ID and the DATA_OBJECT_ID values will be the same, as displayed in DBA_OBJECTS.  If you TRUNCATE a table, the OBJECT_ID value will remain the same while the DATA_OBJECT_ID value for the table will change.

The challenge is to build a test case that either confirms or refutes the claim made by the Oracle Database Performance Tuning Guide.  Possible references:

  • AskTom thread
  • Metalink (MOS) ID 15476.1 “FAQ about Detecting and Resolving Locking Conflicts”
  • Metalink (MOS) Doc ID 603661.1 “ORA-01410: Invalid Rowid When Using rowid Generated from V$Session and Dba_objects”
  • Another article found on this blog (take a look at the Other Resources links)




Unique Index Result Error with Ref Cursor on Oracle 11g but Not on 10g

6 06 2011

June 6, 2011

You might expect to see some differences when upgrading from one release version of Oracle Database to another release version (edit June 6, 2011: see this article for expected changes when upgrading from 10g to 11g).  For instance, you might find that Oracle Database 11.1.0.6 deadlock when Oracle Database 10.2.0.5 and below do not.  You might find that Oracle Database 11.1.0.6 and above throw error messages when ROWNUM is used when Oracle Database 10.2.0.5 and below do not throw error messages.  You might find that the following SQL statement (based on the contents of this article) returns different values for the SYS user depending on the Oracle Database release version:

SELECT SYS_CONTEXT('USERENV', 'SESSIONID') USERENV FROM DUAL; 

For example, on Oracle Database 11.2.0.2 when SYS connects as SYSDBA, you will see something like this for the output of that SQL statement:

USERENV
----------
4294967295

If the O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE on 11.2.0.2, and the SYS user connects without specifying AS SYSDBA, you might see something like this for the output of that SQL statement:

USERENV
-------
344841

If the SYS user connects on Oracle Database 9i, you will probably see something like this for the output of that SQL statement:

USERENV
-------
      0 

The above are all minor changes in bahavior.  If you are brave, go ahead and blindly upgrade to a new version of Oracle Database.  :-)

While browsing a couple of web forums I found another example of changed results caused by upgrading from Oracle Database 10g (possibly 10.2.0.4 or lower) to 11g (not stated, but possibly 11.2.0.2).  To be kind, my PL/SQL coding abilities are rusty, but I think that I follow the logic in that forum thread.  Let’s see if we are able to reproduce the problem with a couple of slight modifications.  First, let’s create a table with three rows:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  ORDER_ID VARCHAR2(20),
  PROCESSED NUMBER);

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Now create a PL/SQL package with an embedded COMMIT (as stated in the forum thread linked to above, this embedded COMMIT is included because this example is from a stateless web application):

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR; 

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Hopefully, I haven’t lost anyone yet, in the above: select a row, return the row as a ref cursor, and then update the row to show that the row has been processed.  Now let’s process the three orders using three anonymous PL/SQL blocks (it is not necessary to use three anonymous PL/SQL blocks, but this was done to verify that the problem happens on multiple executions also):

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/ 

The output from Oracle Database 11.2.0.2 looks like this:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.
 

The above worked as expected, the anonymous PL/SQL blocks output TESTING, TESTING2, and TESTING3.  Not particularly useful as written, but it executes and outputs the expected information.

Let’s create a non-unique index on the ORDER_ID column and repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE INDEX IND_T1 ON T1(ORDER_ID); 

The output from Oracle Database 11.2.0.2 showing the anonymous PL/SQL:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

The above test seemed to work as expected… TESTING, TESTING2, TESTING3.  Let’s create a primary key constraint on the ORDER_ID column, which will use the existing IND_T1 index to enforce the primary key constraint, then we will repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 ADD PRIMARY KEY(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Nothing unusual yet – you are probably wondering about the point of this article by now, almost there.  Let’s create a unique function based index on UPPER(ORDER_ID) and then repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID));

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion of the output:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1
IND_T1_FB

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Still nothing unusual yet.  Let’s try one more time, removing the primary key constraint, dropping the two indexes, and creating a single unique index on the ORDER_ID column (in case you are wondering, with just the function-based unique index on the table there were no problems):

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 DROP PRIMARY KEY;
DROP INDEX IND_T1;
DROP INDEX IND_T1_FB;
CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 (and 10.2.0.5):

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

Note that this time the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Oracle Database 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 all exhibit this unexpected behavior when a unique index is present on table T1’s ORDER_ID column. 

Oracle Database 10.2.0.4 does not exhibit this problem.  Below is the output from Oracle Database 10.2.0.4 for the last portion of the script:

SQL> CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

Index created.

SQL>
SQL> SELECT
  2    BANNER
  3  FROM
  4    V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Well, that is a little interesting.  Let’s try another test – remember that we had no trouble with the unique function-based index.  Let’s quickly revisit that example.  First, reset the T1 test table, drop the unique index, and then recreate the unique function-based index:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

DROP INDEX IND_T1_UNIQUE;
CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID)); 

Now let’s modify the package so that it will likely use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Let’s retry the anonymous PL/SQL blocks:

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

The output of the anonymous PL/SQL blocks:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed. 

Note that the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Let’s try fixing the package so that it will not use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT /*+ NO_INDEX(T1 IND_T1_FB) */
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Now, reset the table’s rows:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Finally, the output from the anonymous PL/SQL blocks on Oracle Database 11.2.0.2:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed. 

Testing 1, 2, 3.  I think that I just fixed a problem by using a hint… although we could have avoided the problem by creating a non-unique normal index with a primary key constraint, or by creating a non-unique index with a second unique function-based index to enforce the uniqueness in the column.

Added June 6, 2011:

The linked forum thread suggests that the problem might be identified in Metalink (MOS) Bug 10425196: “PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5”.  I was able to reproduce the problem in Oracle Database 10.2.0.5 on the Windows platform.  Also, note in the above tests that it is not necessarily the presence of a primary key index on the table that could cause the problem – as shown above, the problem is apparently caused by cases where a unique index is used to retrieve the rows for the ref cursor.





Insert Error on Oracle 11g but Not on 10g

2 06 2011

June 2, 2011

The most popular article on this blog includes a script that demonstrates generating a deadlock on Oracle Database 11g R1 and R2 when the exact same script on Oracle Database 10g R2 does not trigger a deadlock.  I came across another interesting change between Oracle Database 10.2.0.5 and 11.2.0.2 (the change also appears in 11.1.0.7) that affects the results of Insert statements that seemingly worked fine for years.

We will use the following simple script for this blog article:

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

First, a pop quiz:

What is the expected output of the select from table T2?

 Answer A:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

10 rows selected.

Answer B:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

11 rows selected.

Answer C:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0

12 rows selected.

Answer D:

no rows selected

Answer E:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10
02-JUN-11         11
02-JUN-11         12

12 rows selected.

Answer F:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          1

2 rows selected.

—————————————————–

Think about your answer for a minute… you might even want to try the experiment on an Oracle Database.  Scroll down when you think that you have the answer.

Script Output from Oracle Database 10.2.0.5:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

SQL> SELECT * FROM T2;

C1                C2
--------- ----------
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0

12 rows selected.

Script Output from Oracle Database 11.2.0.2:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);
INSERT INTO T1 VALUES(SYSDATE-10,-10)
            *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;
  T1
  *
ERROR at line 2:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);
INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM)
                              *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.

SQL> SELECT * FROM T2;

no rows selected

The results for Oracle Database 11.1.0.7 are identical to those of Oracle Database 11.2.0.2.

The answer to the quiz question is therefore… C AND D, which is of course logically impossible.  Disappointed?  Maybe there is a bug ID in Metalink (MOS) for this particular problem.  :-)  If you search the web, you can find similar SQL statements in various Internet forums, for example on the OTN forums.





Reading Material On Order 2

29 05 2011

May 29, 2011

A year ago I wrote an article that had a couple of topics, one of which described three books that I put on order – ordered with the intention of writing reviews for all three books.  Two of the books arrived in a timely fashion, while the third book has yet to arrive despite being ordered twice from Amazon (I had to rely on the Google Books viewer for the review).

I just recently purchased three electronic formatted books (otherwise known as eBooks), and I plan to put the Motorola Xoom to use while reading those books.  The books include: “Troubleshooting Oracle Performance“, “Beginning Oracle SQL“, and “Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach”.  I will likely review the second and third of the above books, and if I find time I will try to improve the level of detail found in the review of the first of the above books so that the review matches the level of detail found in the book reviews that I wrote a couple of months later.  That said, here are a couple of quick observations about the books:

Troubleshooting Oracle Performance

I tested the PDF and ePUB (apparently used by the Nook) versions of this book.  While the PDF version of the book accurately reproduces the book contents, I recommend staying away from the ePUB version, if possible.  You can read about my adventures with the PDF version and ePUB version of that book at the end of one of my previous articles.  It has been almost three years since my first read through of this book, and while I found a couple of minor errors (yet somewhat obvious errors that the author quickly addressed before I had a chance to read those sections of the book), I did not find anything negative worth mentioning in the original review.  About a year ago I started re-reading the book, but only made it about 100 to 120 pages into the book before I had to break-away to something else.  I do not recall taking any notes in the first 100 to 120 pages, but I do recall taking fairly extensive notes in later parts of the book during the initial read.

Beginning Oracle SQL

This book is intended as an update to the “Mastering Oracle SQL and SQL*Plus” book that was originally written by Lex DeHaan, and was apparently intended both and as an introduction to SQL as well as a brief demonstration of more advanced techniques.  I was impressed with the quality and accuracy of the original book, and I was excited to see a follow-up book titled “Pro Oracle SQL“.  The original “Mastering Oracle SQL and SQL*Plus” book did have a couple of problems: most of the pages lacked page numbers, various formatting problems that resulted in dropped characters, and a missing Oracle Database 10.1.0.2 CD that was promised on the book’s cover.  I have not had a lot of time to examine the “Beginning Oracle SQL” book.  However, I did notice that every page that should have a page number now has a page number, I have not noticed any formatting problems that resulted in dropped characters, and the front cover no longer advertises that the book includes Oracle Database on CD.  I initially thought that all mention of analytic queries had been dropped from the “Beginning Oracle SQL” book due to the extended discussion of this topic in the “Pro Oracle SQL” book, and a search for the word analytic finds only a single page containing that word.  Thankfully, it does appear that a couple of the analytic functions are mentioned in the updated book.  It does not appear that this is just a simple reprint of the original book – the new book includes descriptions of Oracle Database 11.1 and possibly 11.2 features.  More information will follow if I have a chance to read the entire book.  The Google Books viewer application crashes when attempting to show book pages 33 and 34 side-by-side (pages 56 and 57 as displayed in Google Books), but the problem is not present when the Xoom is held in portrait orientation.

Oracle Database 11g Performance Tuning Recipes: A Problem-Solution Approach

This book is set to be released in the middle of August 2011, so I bought the alpha copy of the book that currently includes chapters 3, 5, 6, 7, and 14.  I bought this book because I was curious to see how the recipe format works for a book written on the topic of performance tuning.  That format worked very well for one of the books that I reviewed, and mostly/almost worked for another book that I reviewed (with the notable exception of the SQL statements that were Oracle Database performance related).  How well will the format work for a book that is specifically written about Oracle Database 11g performance tuning?  Let’s just say that I hope that there is a good technical reviewer involved in this book project, that the authors listen to the technical reviewer, and that the alpha copy of the chapters were captured before the technical reviewer had a chance to examine the chapters.  If the alpha copy of the book chapters actually shows the results after the technical reviewers provided recommendations, this will very likely be the first Apress title that I have read which will receive a 3 star, or very likely lower, rating on a 5 star scale when I write a review of the book.  I do not want to go into a lot of specifics after a very quick examination of a couple of alpha chapters of a book, but I will mention a couple of examples of problems that I identified:

  • The introduction to chapter 3 states that the results of SQL queries and PL/SQL functions may be stored in the shared pool when a results cache is used.  It might be a better idea to be much more specific, and state that Oracle Database is using the Result Cache Memory in the shared pool as indicated in the Oracle Database documentation, but maybe this is explained later.
  • In recipe 3-1 the authors mention that they would set the MEMORY_MAX_TARGET to 1500M, and then show a SQL statement that sets that parameter to 2G.  This particular inconsistency will likely be caught in a later review of the book material.  This recipe seems to be mirroring a page from the Oracle documentation library, only that the order of a couple commands were specified incorrectly in the book.  The comment regarding the _TARGET parameters does not seem to apply to the SGA_TARGET parameter.  This recipe states that “automatic memory management is the recommended approach to managing Oracle’s memory allocation,” but does not describe any of the potential pitfalls or operating system specific problem areas/limitations (reference1 reference2 reference3 reference4 reference5 reference6).  Beyond telling the reader to simply add the current PGA_AGGREGATE_TARGET and SGA_TARGET values to determine a starting point for MEMORY_MAX_TARGET, the recipe gives little insight into the “best” value for this parameter.  For now I will ignore the numerous spelling mistakes, because the book editors will likely find and address those issues.
  • In recipe 3-2 the authors state, “if you want a segment to be cached (or pinned) in the buffer pool, you must specify the KEEP buffer pool [when creating the segment].”  Something just doesn’t seem to be right (or complete) about this statement – for one, I wonder what the CACHE and  NOCACHE clauses might affect?  Secondly, this statement might be implying that blocks belonging to an object will only remain in the buffer cache if those objects are assigned to the KEEP buffer cache.  The recipe states that the approximate size of the KEEP buffer pool can be determined by “summing up the size of all candidate objects for this pool” – great, now what happens when Oracle Database creates up to six (and potentially many more) consistent read versions of the objects’ blocks (reference)?  The last two paragraphs of this recipe seem to indicate that it is the segments themselves (apparently all blocks) that are aged out of the buffer cache when space is needed to cache other blocks, rather than the segments’ blocks that are aged out of the cache – additional clarity is needed in these two paragraphs.  The second to last paragraph also makes a claim that I am not sure how to interpret: “Oracle figures that if a single segment takes up more than 10% of (nonsequential) physical reads, it’s a large segment, for the purpose of deciding if it needs to use the KEEP or RECYCLE buffer pools.”
  • (Skipping around a bit) In recipe 3-13 we learn how to tune the redo log buffer.  The book states, “since there’s no cost whatsoever to increasing the LOG_BUFFER size, feel free to set it to higher than the suggested maximum of 1MB for this parameter.”  Earlier the book showed how to set the LOG_BUFFER parameter to a value of 4,096,000.  I think that I recall that Oracle Database 11.1 was released after Oracle Database 10.2 ( :-) ), where the LOG_BUFFER parameter started being auto-set to a value slightly smaller than the granule size – the most common granule size for Oracle Database 10.2 databases was likely 16MB, while only databases with a SGA size less then about 1GB saw granule sizes of 4MB.  The granule size rules changed again with the release of Oracle Database 11.1 and again with the release of Oracle Database 11.2.  If you check the comments section of this blog article, one reader commented about the LOG_BUFFER being auto-tuned to nearly 512MB in size.  I wonder how useful the ratio of ‘redo entries’ statistic value divided by the the ‘redo log space requests’ statistic value might be when trying to find the ideal value for the LOG_BUFFER parameter?
  • In recipe 5-1, how does one see a “latch on a log file”?  The recipe also describes a view that has a name that ends with the suffix _HISTORY, but the book failed to mention the licensing requirements to access this view.  When describing the V$SESSION_EVENT view, the book states, “The data in this view are available only so long as a session is active.” – considering that one of the columns in V$SESSION is named ACTIVE, I can see where a statement like this one could lead to confusion.  The last paragraph of the recipe states, “Note that you can query the V$WAITSTAT view for the same information as well.” – that view was not previously introduced and in actuality, the V$WAITSTAT view does not produce information similar to the V$SYSTEM_EVENT view that was described a sentence or two earlier.
  • Recipe 5-2 queries V$ACTIVE_SESSION_HISTORY without first providing a warning regarding the licensing requirements to access this view.  I have not experimented enough with ASH data, but I wonder if SUM(WAIT_TIME + TIME_WAITED) when grouped by SQL_ID and USER_ID could lead to incorrect assumptions regarding the wait time per SQL statement for the last 30 minutes.  Is there a risk of double-counting, or counting incidental CPU usage immediately after a wait event completes, as part of the time spent in wait events for a specific SQL statement?  I am left wondering why the authors did not suggest checking the APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, PLSQL_EXEC_TIME, and JAVA_EXEC_TIME columns of V$SQL (or V$SQLAREA) periodically, and calculating the delta (change) values for specific SQL statements – those columns have existed as long as ASH (since the release of Oracle Database 10.1).

I guess that the above is a bit more lengthy than I had originally intended.  Best of luck to the technical reviewers of the book.





How to Eliminate Wait Events in Oracle Database?

13 05 2011

May 13, 2011

In the last couple of days I have been busy with a couple of non-Oracle Database specific tasks, and I started wondering what types of wait events I might be posting during this time period if I had to report what I was doing.  Waiting on the server to reboot for the 20th time during a software deployment, waiting for replacement hard drives to be shipped, waiting for the darn technology gadgets to download a file or recover from the latest force close (a nice way of saying CRASH!), waiting for a software vendor to return a call, waiting for thoughts to magically appear on PowerPoint slides, etc.

While I was processing all of these wait events, I noticed a couple of recent search keywords being used to find pages on my blog.  One of the sets of search keywords included:
How to Eliminate Wait Events Oracle

I know that I covered that topic in an earlier blog article, but I thought that I would open this topic again for discussion.

While we are at it, let’s try to answer a couple of additional sets of search keywords that lead people to blog articles on this site:
How to Determine Why a CPU is Busy
How to Stop Dead Locks in Oracle

Just a fair warning – the first result returned by a search engine is not necessarily the result that you want in a production environment.





How Do You Find Information in the Oracle Documentation Library?

22 02 2011

February 22, 2011

A recent thread in the OTN forums mentioned Metalink (MOS) Doc ID 1203353.1 – “How to find Oracle Database Documentation on OTN Web Site” and lists four easy steps for finding information in the Oracle documentation library, with the starting point being this link.  I think that it is great having the list of steps in such as easily read form.  There is very useful thread in another OTN forum, and that thread is titled “SQL and PL/SQL FAQ” (I really like this thread, and hope that a similar thread makes its way into the Database – General forum also).  Among other helpful topics mentioned in the thread is how to access the Oracle Database Documentation using another website (this is the site that I have seen specified most often as the starting point for accessing the Oracle documentation). 

As I mentioned in the first of the above OTN threads, I use a slightly different method to access the Oracle documentation that seems to be a bit more efficient for my needs – Google searches. For example, if I am looking for the definition of V$SESSION_EVENT in a specific Oracle Database release version, I will use the following search keywords:

For Oracle Database 8i (8.1.5 through 8.1.7): site:download.oracle.com 815 V$SESSION_EVENT
For Oracle Database 9.0.1: site:download.oracle.com 901 V$SESSION_EVENT
For Oracle Database 9.2.0: site:download.oracle.com 920 V$SESSION_EVENT
For Oracle Database 10.1.0: site:download.oracle.com 101 V$SESSION_EVENT
For Oracle Database 10.2.0: site:download.oracle.com 102 V$SESSION_EVENT
For Oracle Database 11.1.0: site:download.oracle.com 111 V$SESSION_EVENT
For Oracle Database 11.2.0: site:download.oracle.com 112 V$SESSION_EVENT

Once in a while, it might be necessary to add the term database to the search keywords to eliminate search results that are not Oracle Database specific.

The custom search engine on the Miracle Finland site also seems to do a good job of taking me directly to the Oracle documentation library.








Follow

Get every new post delivered to your Inbox.

Join 174 other followers