The New Order Oracle Coding Challenge 1

31 07 2011

July 31, 2011

(Forward to the Next Post in the Series)

Years ago I played a couple of different games with letters, rather than numbers, on dice – attempting to form words from the letters displayed on top of the dice.  I was not very good with those games, and I recall attempting to create a computer program to help me with the challenge.  The screaming fast 1 MHz CPU and 64KB of memory proved to be no match for the words listed in the paper-bound dictionary sitting on the shelf.  Computers are significantly faster now, with wide-spread access to Internet based word lists, so it probably would not be much of a challenge today to build a solution for one of those dice letter games.

Finding different combinations with number digits is a bit easier than working with letters – we are able to use math rules to determine if the specified conditions are met rather than a dictionary.  We will start with an easy problem that I found on the web, but I will keep the source of that problem a secret for now.  Consider the number 989,901.  If we write the digits in that number from right to left, we obtain the new number 109,989.  What is special about that new number?  The number is evenly divisible by 3 and 9, but more interesting is that the new number divides evenly into the original number (989,901).

With the help of Oracle Database, find all of the numbers from 1 to 1,000,000 where the digits in the number when listed from left to right are evenly divisible by those same digits listed from right to left.  The numbers that end with 0 are a special case, reversing the order of the digits in those numbers will result in the 0 digit effectively disappearing – as such, exclude any number that ends with 0 from being tested.

There are several methods to swap the order of the digits in the number.  Would you use a different method to test all of the numbers between 1 and 10,000, or to test all of the numbers up to 10,000,000?

Might it work to store the numbers in a reverse key index, and then dump the resulting index values – is that the fourth method to switch the order of the digits?  ;-)





The Unique Result Oracle Database Coding Challenge

28 07 2011

July 28, 2011

I must say that I am impressed with the number of unique solutions that were developed for the previous coding challenge (FizzBuzz).  While not all solutions were extremely efficient (a couple were intentionally designed to be as inefficient as possible), the various techniques provide views of different approaches to solving a problem that was not well defined (even though at first glance it appeared to be well defined).  While not all of the solutions presented are optimal for the FizzBuzz challenge, derivatives of those solutions might be perfect for real-world problems (for instance, side-tracking a pesky DBA or developer with a performance challenge).

Time for another coding challenge.  This challenge is adapted from one that was posed in a Usenet thread several years ago.  Remember that if a specification is not well defined, feel free to interpret the specification – one of the goals of this blog article is to see a variety of solutions, but a secondary goal is to determine what might happen when weak specifications are provided in a request for a solution.

Assume that there are two tables, T1 and T2, as designed and populated below.  The two tables contain the results from experiments performed by two different analysts.

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1 (
  C1 VARCHAR2(10),
  C2 VARCHAR2(10),
  C3 VARCHAR2(10),
  C4 VARCHAR2(10));

CREATE TABLE T2 (
  C1 VARCHAR2(10),
  C2 VARCHAR2(10),
  C3 VARCHAR2(10),
  C4 VARCHAR2(10));

INSERT INTO T1 VALUES ('TEST1A','TEST1B','TEST1C','TEST1D');
INSERT INTO T1 VALUES ('TEST2A','TEST2B','TEST2C','TEST2D');
INSERT INTO T1 VALUES ('TEST3A','TEST3B','TEST3C','TEST3D');
INSERT INTO T1 VALUES ('TEST4A','TEST4B','TEST4C','TEST4D');
INSERT INTO T1 VALUES ('TEST5A','TEST5B','TEST5C','TEST5D');

INSERT INTO T2 VALUES ('TEST1A','TEST1B','TEST1C','TEST1D');
INSERT INTO T2 VALUES ('TEST2A','TEST1B','TEST1C','TEST2D');
INSERT INTO T2 VALUES ('TEST3A','TEST3B','TEST3C','TEST3D');
INSERT INTO T2 VALUES ('TEST5A','TEST5B','TEST5C','TEST5D');
INSERT INTO T2 VALUES ('TEST6A','TEST5B','TEST5C','TEST6D');  

This is a three part challenge.

Part 1: Return all rows from table T1 where the C2 value is not found in table T2′s column C2 values, and return all rows from table T2 where the C2 value is not found in table T1′s column C2 values.  In short, we want the rows that have unique results found in column C2 so that we may identify the differences found by the two analysts.

Part 2: Using a single select statement, return all rows found in table T1 that are not duplicated in table T2, and all rows found in table T2 that are not duplicated in table T1.  The resulting output must identify the table from which the row was found.

Part 3: A third analyst performed experiments and entered his results in table T3, as designed and populated below.  Using a single SQL statement, retrieve the rows from tables T1, T2, and T3 that are not duplicated in the other tables – find the unique rows from the three tables.  Each row should identify its source table (T1, T2, or T3).

DROP TABLE T3 PURGE;

CREATE TABLE T3 (
  C1 VARCHAR2(10),
  C2 VARCHAR2(10),
  C3 VARCHAR2(10),
  C4 VARCHAR2(10));

INSERT INTO T3 VALUES ('TEST1A','TEST1B','TEST1C','TEST1D');
INSERT INTO T3 VALUES ('TEST2A','TEST1B','TEST1C','TEST2D');
INSERT INTO T3 VALUES ('TEST6A','TEST6B','TEST6C','TEST6D'); 

When you post your solutions, please identify Part 1, Part 2, or Part 3 next to the solution.

General tips for comments:

  • Include your code portion of the solution in a monospaced font (Courier) to retain the alignment spaces in the solution.  To do this, use a <pre> tag just before the code portion of the solution and a </pre> tag just after the solution ([s ourcecode] and [/s ourcecode] tags (without the space between the first two letters) should have the same result, just with a smaller font size and a non-scrolling code area).
  • Less than (<) and greater than (>) signs have a special meaning in HTML web pages.  As such, specify &lt; for a less than sign and &gt; for a greater than sign in code sections to avoid having portions of your code sections magically disappear.

Think about how you would build the solutions before scrolling down to the comments section.

-

-

-

-

-

-

-

-

Part 2:

SELECT  NVL(T1.C1,T2.C1) C1, NVL(T1.C2,T2.C2) C2, NVL(T1.C3,T2.C3) C3, NVL(T1.C4,T2.C4) C4, NVL2(T1.C1,'T1','T2') FROM_TABLE FROM T1 FULL OUTER JOIN T2 ON T1.C1=T2.C1 AND T1.C2=T2.C2 AND T1.C3=T2.C3 AND T1.C4=T2.C4 WHERE T1.C1 IS NULL OR T2.C1 IS NULL; 

-

-

-

-

-

-





The FizzBuzz Oracle Database Coding Challenge

26 07 2011

July 26, 2011

Through a web search I located a page titled “Coding Horror: Why Can’t Programmers.. Program?”  A simple question was asked in an interview, and apparently 199 of 200 programmers struggled to build a solution for the problem in less than ten minutes.  The problem must be that the 199 people who did not succeed did not have access to an Oracle Database.  The same question was posed to SQL Server developers in the form of a quiz.  Before looking at the articles, see if you are able to solve the following problem with the help of Oracle Database:

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.

How many different solutions are there for this problem?  Think about the problem before scrolling down.

-

-

-

-

-

-

-

-

-

-

-

-

-

My solution:

SELECT
  NVL(DECODE(ROWNUM/3,TRUNC(ROWNUM/3),'Fizz',NULL)||DECODE(ROWNUM/5,TRUNC(ROWNUM/5),'Buzz',NULL),TO_CHAR(ROWNUM)) FIZZBUZZ
FROM
  DUAL
CONNECT BY
  LEVEL<=100; 

-

-

-

-

-

-

-

I think that I remember solving a similar problem using an IBM PC Jr. using BASICA years ago.  How may ways can this problem be solved with the help of Oracle Database?  Before you answer, you might be thinking to yourself why would someone ask such a simple question?  Could there be an overly complex solution, something that the interviewer had never seen before, that was the intended response to the question?





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.”





How Many Ways to Solve this Problem? Generate a Calendar that Displays Average Hours Per Day

18 07 2011

July 18, 2011

I am surprised at the significant number of unique solutions to the SQL problems that I have previously posed on this blog.  For fun I thought that I would give people another chance to demonstrate their unique approaches to solving another problem.

Supposed that a transaction table exists with the following definition:

CREATE TABLE T1 (
  TRANS_ID NUMBER,
  EMPLOYEE_ID VARCHAR2(10),
  PART_ID VARCHAR2(20),
  CLOCK_IN DATE,
  CLOCK_OUT DATE,
  QTY_COMPLETE NUMBER,
  PRIMARY KEY (TRANS_ID));  

The above table simulates a transaction table that might capture production run times for people (or even robots) to produce sets of parts.  The requirement is rather simple – we want to know the average number of hours required to produce a particular part by day in a calendar type layout.  The only restriction is that the output must appear in a SQL*Plus window (or SQL*Plus may be used to spool the result to a text file).  For example, the output might look like this (feel free to be creative):

PART_ID    W     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY   SATURDAY
-------- --- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PART1      1        .98        .56        .67       1.21        .23        .98        .02
PART1      2        .57        .87        .76        .01        .45        .55        .77
PART1      3        .01        .77        .66        .54        .02        .44        .92
PART1      4        .55        .02        .88        .11        .05        .33        .89
PART1      5        .11        .43        .55        .14        .03        .90        .78
PART1      6        .87        .45        .63        .02        .82        .78        .55
PART1      7        .02                                         .01 

If there was no transaction for a particular part on a particular date, a blank (NULL) should appear; to prevent division by zero errors, the hours should be output divided by one if the total QTY_COMPLETE is zero.

Let’s create some reproducible sample data in table T1:

DROP TABLE TABLE T1_EMP PURGE;

CREATE TABLE T1_EMP AS
SELECT
  'EMP'||TO_CHAR(ROWNUM) EMPLOYEE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

DROP TABLE T1_PART PURGE;

CREATE TABLE T1_PART AS
SELECT
  'PART'||TO_CHAR(ROWNUM) PART_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=200;

INSERT INTO
  T1
SELECT /*+ LEADING(P E) */
  ROWNUM TRANS_ID,
  E.EMPLOYEE_ID,
  P.PART_ID,
  TO_DATE('01-JAN-2000','DD-MON-YYYY')+ROWNUM/500 CLOCK_IN,
  TO_DATE('01-JAN-2000','DD-MON-YYYY')+ROWNUM/500 + ((MOD(ROWNUM,20) + 1) * 0.5) / 24 CLOCK_OUT,
  ROUND(ABS(SIN(ROWNUM/180*3.141592)*10)) QTY_COMPLETE
FROM
  (SELECT
     EMPLOYEE_ID,
     ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID) RN
   FROM
     T1_EMP) E,
  (SELECT
     PART_ID,
     ROW_NUMBER() OVER (ORDER BY PART_ID) RN
   FROM
     T1_PART) P,
  (SELECT
     ROWNUM RN
   FROM
     DUAL
   CONNECT BY
     LEVEL<=10) D
WHERE
  MOD(E.RN,8)=MOD(P.RN,8);

COMMIT; 

Taking a quick look at the sample data in table T1:

COLUMN PART_ID FORMAT A8
COLUMN HOURS FORMAT 90.00
SET PAGESIZE 1000
SET LINESIZE 140
SET TRIMSPOOL ON

SELECT
  PART_ID,
  EMPLOYEE_ID,
  TRUNC(CLOCK_IN) SHIFT_DATE,
  CLOCK_OUT-CLOCK_IN HOURS,
  QTY_COMPLETE
FROM
  T1
WHERE
  PART_ID='PART1'
ORDER BY
  CLOCK_IN;

PART_ID  EMPLOYEE_I SHIFT_DAT  HOURS QTY_COMPLETE
-------- ---------- --------- ------ ------------
PART1    EMP811     31-JAN-01   0.19            9
PART1    EMP811     31-JAN-01   0.21            9
PART1    EMP811     31-JAN-01   0.23            9
PART1    EMP819     04-FEB-01   0.04           10
PART1    EMP819     04-FEB-01   0.06           10
PART1    EMP819     04-FEB-01   0.08           10
PART1    EMP819     04-FEB-01   0.10           10
PART1    EMP819     04-FEB-01   0.13           10
PART1    EMP819     04-FEB-01   0.15           10
...
PART1    EMP912     28-MAR-01   0.19            2
PART1    EMP912     28-MAR-01   0.21            2
PART1    EMP912     28-MAR-01   0.23            2
PART1    EMP92      01-APR-01   0.04            0
PART1    EMP92      01-APR-01   0.06            0
PART1    EMP92      01-APR-01   0.08            1
PART1    EMP92      01-APR-01   0.10            1
...
PART1    EMP992     11-MAY-01   0.19            5
PART1    EMP992     11-MAY-01   0.21            5
PART1    EMP992     11-MAY-01   0.23            5

1250 rows selected. 

Now just a quick example of how you might calculate the average hours per piece (note that the HOURS column actually shows the number of days, NOT hours – multiply the numbers in that column by 24 to convert to hours):

SELECT
  PART_ID,
  TRUNC(CLOCK_IN) SHIFT_DATE,
  SUM(CLOCK_OUT-CLOCK_IN) HOURS,
  SUM(QTY_COMPLETE) QTY_COMPLETE
FROM
  T1
WHERE
  PART_ID='PART1'
GROUP BY
  PART_ID,
  TRUNC(CLOCK_IN)
ORDER BY
  TRUNC(CLOCK_IN);

PART_ID  SHIFT_DAT  HOURS QTY_COMPLETE
-------- --------- ------ ------------
PART1    01-JAN-00   1.35           90
PART1    05-JAN-00   1.35          100
PART1    09-JAN-00   1.35           98
PART1    13-JAN-00   1.35           81
PART1    17-JAN-00   1.35           56
PART1    21-JAN-00   1.35           25
PART1    25-JAN-00   1.35           10
PART1    29-JAN-00   1.35           44
...
PART1    29-APR-01   1.35           56
PART1    03-MAY-01   1.35           25
PART1    07-MAY-01   1.35           10
PART1    11-MAY-01   1.35           44

125 rows selected. 

Interesting how the HOURS column always sums to 1.35 per day…

OK, for you self-starters, create the calendar output.  Think about any other kinds of analysis that might be done with this data – is it possible to determine which PART_ID takes the least (or the most) average time per piece on any given day.

-

-

-

For the non-self starters, scroll down.

-

-

-

-

-

-

-

-

-

-

-

-

-

One of the challenges that we face is determining the calendar row and column for any given shift date (the date associated with the CLOCK_IN date/time stamp).  Since in this case we know that the first row of data has a date of 01-JAN-2000, we need to determine the Sunday of that week.  One way to do that is to use the NEXT_DAY function, like this:

SELECT
  NEXT_DAY(TO_DATE('01-JAN-2000','DD-MON-YYYY')-7,'SUNDAY') S
FROM
  DUAL;

S
---------
26-DEC-99 

The next step is to divide the shift dates into rows and columns based on the zero date of 26-DEC-99.  TRUNC and MOD could be used for this calculation, but to keep things interesting I will use TO_CHAR rather than MOD:

COLUMN R FORMAT 99
COLUMN C FORMAT 9

SELECT
  PART_ID,
  NEXT_DAY(TO_DATE('01-JAN-2000','DD-MON-YYYY')-7,'SUNDAY') S,
  TRUNC(CLOCK_IN) SHIFT_DATE,
  TRUNC((TRUNC(CLOCK_IN)-NEXT_DAY(TO_DATE('01-JAN-2000','DD-MON-YYYY')-7,'SUNDAY'))/7)+1 R,
  TO_NUMBER(TO_CHAR(TRUNC(CLOCK_IN),'D')) C,
  SUM(CLOCK_OUT-CLOCK_IN) HOURS,
  SUM(QTY_COMPLETE) QTY_COMPLETE
FROM
  T1
WHERE
  PART_ID='PART1'
GROUP BY
  PART_ID,
  TRUNC(CLOCK_IN)
ORDER BY
  TRUNC(CLOCK_IN);

PART_ID  S         SHIFT_DAT   R  C  HOURS QTY_COMPLETE
-------- --------- --------- --- -- ------ ------------
PART1    26-DEC-99 01-JAN-00   1  7   1.35           90
PART1    26-DEC-99 05-JAN-00   2  4   1.35          100
PART1    26-DEC-99 09-JAN-00   3  1   1.35           98
PART1    26-DEC-99 13-JAN-00   3  5   1.35           81
PART1    26-DEC-99 17-JAN-00   4  2   1.35           56
PART1    26-DEC-99 21-JAN-00   4  6   1.35           25
PART1    26-DEC-99 25-JAN-00   5  3   1.35           10
...
PART1    26-DEC-99 16-NOV-00  47  5   1.35           72
PART1    26-DEC-99 20-NOV-00  48  2   1.35           90
PART1    26-DEC-99 24-NOV-00  48  6   1.35          100
PART1    26-DEC-99 28-NOV-00  49  3   1.35           98
PART1    26-DEC-99 02-DEC-00  49  7   1.35           81
...
PART1    26-DEC-99 25-APR-01  70  4   1.35           81
PART1    26-DEC-99 29-APR-01  71  1   1.35           56
PART1    26-DEC-99 03-MAY-01  71  5   1.35           25
PART1    26-DEC-99 07-MAY-01  72  2   1.35           10
PART1    26-DEC-99 11-MAY-01  72  6   1.35           44

125 rows selected. 

The next step is to collapse all of the rows with the same R value into a single row – we will do this with a combination of the MAX and DECODE functions:

SELECT
  PART_ID,
  R,
  ROUND(MAX(DECODE(C,1,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) SUNDAY,
  ROUND(MAX(DECODE(C,2,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) MONDAY,
  ROUND(MAX(DECODE(C,3,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) TUESDAY,
  ROUND(MAX(DECODE(C,4,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) WEDNESDAY,
  ROUND(MAX(DECODE(C,5,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) THURSDAY,
  ROUND(MAX(DECODE(C,6,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) FRIDAY,
  ROUND(MAX(DECODE(C,7,DECODE(QTY_COMPLETE,0,HOURS,HOURS/QTY_COMPLETE),NULL)),2) SATURDAY
FROM
  (SELECT
    PART_ID,
    TRUNC(CLOCK_IN) SHIFT_DATE,
    TRUNC((TRUNC(CLOCK_IN)-NEXT_DAY(TO_DATE('01-JAN-2000','DD-MON-YYYY')-7,'SUNDAY'))/7)+1 R,
    TO_NUMBER(TO_CHAR(TRUNC(CLOCK_IN),'D')) C,
    SUM(CLOCK_OUT-CLOCK_IN) HOURS,
    SUM(QTY_COMPLETE) QTY_COMPLETE
  FROM
    T1
  WHERE
    PART_ID='PART1'
  GROUP BY
    PART_ID,
    TRUNC(CLOCK_IN))
GROUP BY
  PART_ID,
  R
ORDER BY
  PART_ID,
  R;

PART_ID    R     SUNDAY     MONDAY    TUESDAY  WEDNESDAY   THURSDAY     FRIDAY   SATURDAY
-------- --- ---------- ---------- ---------- ---------- ---------- ---------- ----------
PART1      1                                                                          .02
PART1      2                                         .01
PART1      3        .01                                         .02
PART1      4                   .02                                         .05
PART1      5                              .14                                         .03
PART1      6                                         .02
PART1      7        .02                                         .01
PART1      8                   .01                                         .02
...
PART1     69                              .01                                         .01
PART1     70                                         .02
PART1     71        .02                                         .05
PART1     72                   .14                                         .03

72 rows selected. 

Of course there is a problem with the above – the scale of the time is actually in days, rather than hours, but that is easy enough to fix.

-

-

-

-

-

-

-

Anyone care to share their approach to solving the original problem?

-

-

Assume that the output will be used by a production supervisor – what other kinds of analysis can be performed?  For example, could you produce an analysis like the one below that compares recent transactions with those from prior periods and color codes those prior periods based on how the current period (the Avg Hrs Pc column in the below screen capture) compares with the prior periods:

Be creative – performance, while important, is not the primary objective.





How Many Ways to Solve this Problem? Add the Sequential Numbers x Through y

13 07 2011

July 13, 2011

I am not entirely sure why, however a couple of days ago the following search keywords were used to access one or more articles on this blog:

the sum of 1+2+3+4...+98+99+100

The above request I found to be a bit interesting, and there is a 50/50 chance that the person found the right answer to the sum of the numbers between 1 and 100. 

If you had to solve this problem with the help of Oracle Database, how would you accomplish the task?  If it helps, I think that I recall that the mathematical notation representing the problem posed by the searcher is as follows:

Would your answer be any different if the person needed to know the sum of the numbers between 6 and 105:

What about generalizing the problem even further:

Think about the problem before scrolling down.  How many unique solutions are able to produce the answer?

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

-

————————————

If you think about the problem, in its simplist form it is really just a matter of repeatedly adding the set of the highest and lowest unmatched numbers (all of those results should be the same, otherwise your calculator needs new batteries) and then multiplying by the number of pairs (1/2 as many numbers are in the sequence to be summed):

01 + 100 = 101
02 + 99 = 101
03 + 98 = 101
04 + 97 = 101
05 + 96 = 101
...
50 + 51 = 101

So, the general formula is:

(max - min + 1) / 2 * (min + max)

And the SQL statements to produce the results:

SELECT   (100 - 1 + 1) /2 * (1 + 100) FROM   DUAL;  
SELECT   (105 - 6 + 1) /2 * (6 + 105) FROM   DUAL;

————————————

-

-

-

-

-

-

-

-

-

-

-

-

For now, ignore the above section.  How many ways can this particular problem be solved with the help of Oracle Database?  Are there any built-in functions that will help?





What Would Cause a NO_INDEX Hint to Not Work as Expected?

11 07 2011

July 11, 2011

Recently, the following search keywords were used to access an article on my site, and that search triggered an idea for another blog article:

no_index hint oracle 10g not working 

In Oracle Database, hints are directives that must be obeyed (with a couple of minor exceptions that include bugs).  I started wondering what might cause a NO_INDEX hint to not work as expected.  Let’s create a test table for a couple of experiments:

CREATE TABLE T3(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 VARCHAR2(300));

INSERT INTO
  T3
SELECT
  ROWNUM C1,
  TRUNC(ROWNUM/10000) C2,
  MOD(ROWNUM,10000) C3,
  LPAD('A',300,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

CREATE INDEX IND_T3_C1 ON T3(C1);
CREATE INDEX IND_T3_C2 ON T3(C2);
CREATE INDEX IND_T3_C3 ON T3(C3);

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

SET LINESIZE 140
SET PAGESIZE 1000 

Let’s try a simple query that accesses the table, and display the execution plan for that query (note that these test SQL statements are being executed on Oracle Database 11.2.0.2):

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  0s5xrvx04309f, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

The IND_T3_C1 index was automatically selected to assist data retrieval.

Let’s try a NO_INDEX hint just to verify that the hint can work as expected:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  d0gpwhvg7629r, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 4161002650

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     20 |00:00:00.01 |   45583 |
|*  1 |  TABLE ACCESS FULL| T3   |      1 |     20 |     20 |00:00:00.01 |   45583 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=20) 

As can be seen above, a full table scan is performed for data retrieval, rather than the index that was used in the previous example.

Let’s try another example with the NO_INDEX hint:

SELECT /*+ NO_INDEX(IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  4p5xpu625cw5a, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

That did not work quite as someone might think – you must specify the table/view name in the NO_INDEX hint.

Let’s try another example:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3 T
WHERE
  C1<=20;

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

SQL_ID  fnjc3pc41a2mh, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 T WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

Again, the index was used despite the NO_INDEX hint – if you alias a table/view, you must specify the alias in the NO_INDEX hint.  This seems to be a common problem when people report in Internet forums that Oracle hints do not work as expected.

Another example:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

Plan hash value: 587667290

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |     20 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI              |           |      1 |     20 |     20 |00:00:00.01 |      13 |   705K|   705K| 1125K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       4 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       3 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T3        |      1 |     20 |     20 |00:00:00.01 |       9 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   3 - access("C1"<=20)
   4 - filter("C1"<=20) 

As seen by the above, the IND_T3_C1 index was still used even though the hint was correctly formed.  What is wrong?  The scope of the index hint is only in the subquery found in the WHERE clause and that hint does not apply to the main portion of the SQL statement – the hint did work in the scope of the subquery.  Note that the execution plan shows that the query was transformed into a simple join.

A similar SQL statement, with the NO_INDEX hint relocated:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+  */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

SQL_ID  3n76qa6km68r2, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+  */
      C1      FROM        T3      WHERE        C1<=20)

Plan hash value: 3266157401

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     20 |00:00:00.07 |   45586 |       |       |          |
|*  1 |  HASH JOIN SEMI    |           |      1 |     20 |     20 |00:00:00.07 |   45586 |   705K|   705K| 1140K (0)|
|*  2 |   TABLE ACCESS FULL| T3        |      1 |     20 |     20 |00:00:00.01 |   45581 |       |       |          |
|*  3 |   INDEX RANGE SCAN | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C1"<=20)
   3 - access("C1"<=20) 

As can be seen above, the IND_T3_C1 index was still used because the NO_INDEX scope is only in the main body of the SQL statement, not in the subquery found in the WHERE clause.  Once again, the execution plan shows that the query was transformed into a simple join.

OK, so we saw in the previous examples that query transformations happened and Oracle’s optimizer was able to keep track of the scope of the NO_INDEX hint, even when the query was tranformed into a simple join.  Let’s try another example, this time with a NO_QUERY_TRANSFORMATION hint, a NO_INDEX hint, and an INDEX hint: 

SELECT /*+ INDEX(T3 IND_T3_C1) NO_QUERY_TRANSFORMATION GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

SQL_ID  bbs14bbywgfq2, child number 0
-------------------------------------
SELECT /*+ INDEX(T3 IND_T3_C1) NO_QUERY_TRANSFORMATION
GATHER_PLAN_STATISTICS */   C1,   C2,   C3,   SUBSTR(C4,1,10) C4 FROM
T3 WHERE   C1 IN     (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */        C1
  FROM        T3      WHERE        C1<=20)

Plan hash value: 371539318

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     20 |00:00:00.01 |   45723 |
|*  1 |  FILTER             |      |      1 |        |     20 |00:00:00.01 |   45723 |
|   2 |   TABLE ACCESS FULL | T3   |      1 |   1000K|   1000K|00:00:00.16 |   45583 |
|*  3 |   FILTER            |      |   1000K|        |     20 |00:00:00.11 |     140 |
|*  4 |    TABLE ACCESS FULL| T3   |     20 |      1 |     20 |00:00:00.01 |     140 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter(:B1<=20)
   4 - filter(("C1"=:B1 AND "C1"<=20)) 

Note in the above that no query transformation happened, but also notice that two full table scans were performed – it *appears* that the INDEX hint was ignored.  For extra credit, explain why the optimizer could not apply the INDEX hint.

What if we create a view with an embedded hint?

CREATE VIEW V3 AS
SELECT /*+ INDEX(TV3 IND_T3_C2) */
  C1,
  C2,
  C3,
  C4
FROM
  T3 TV3
WHERE
  C2 <= 20; 

Now a query with a NO_INDEX hint that uses that view:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(V3 IND_T3_C2) */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  agjapbkt2n8av, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+ NO_INDEX(V3
IND_T3_C2) */        C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 1309751330

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.01 |      54 |    |          |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.01 |      54 |  1452K|  1452K| 1282K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |      13 |    |          |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |       3 |    |          |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |    |          |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |    |          |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C2"<=20)
   3 - access("C1"<=200)
   5 - access("C1"<=200) 

As seen by the above, the NO_INDEX hint was applied and the conflicting hint that was embedded in the view was not applied.

For fun, let’s reverse the location of the INDEX and NO_INDEX hints to see if the NO_INDEX hint always overrides the INDEX hint.  First, the view definition:

CREATE OR REPLACE VIEW V3 AS
SELECT /*+ NO_INDEX(TV3 IND_T3_C2) */
  C1,
  C2,
  C3,
  C4
FROM
  T3 TV3
WHERE
  C2 <= 20; 

Now the query:

 SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ INDEX(V3 IND_T3_C2) */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  5j745zr4dmqzx, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+ INDEX(V3
IND_T3_C2) */        C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 3864333899

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.06 |   10017 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.06 |   10017 |  1452K|  1452K| 1269K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |    9976 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C2 |      1 |    209K|    209K|00:00:00.02 |     410 |       |       |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C1"<=200)
   3 - access("C2"<=20)
   5 - access("C1"<=200)

As can be seen by the above, the NO_INDEX hint in the view was not applied due to the conflicting INDEX hint in the main query.  So, that is another case where the NO_INDEX hint could appear to not work as expected.  Just for confirmation that the NO_INDEX hint in the view works as expected, we will re-execute the query without the INDEX hint:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+  */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  fq3g6pr7ffj2f, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+  */
C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 1309751330

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.01 |      54 |    |          |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.01 |      54 |  1452K|  1452K| 1232K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |      13 |    |          |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |       3 |    |          |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |    |          |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |    |          |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C2"<=20)
   3 - access("C1"<=200)
   5 - access("C1"<=200) 

  1. By executing the above test scripts on Oracle Database 11.1, 10.2, 10.1, 9.2, or 9.0.1 do you see different results?  I am trying to understand why the person performing the search might have included 10g in the search keywords.  Is it possible that the hint appeared to work correctly in 9.2 simply by coincidence, and a query transformation in 10.1 or 10.2 exposed the fact that the hint was malformed?
  2. Is it possible that a query transformation can cause a NO_INDEX hint to be ignored?  If yes, please provide a test case that demonstrates a NO_INDEX hint being ignored due to a transformation.
  3. Are there any other examples where a NO_INDEX hint will appear to not work properly?  Could an index organized table cause problems for this hint?




How Many Ways to Solve this SQL Problem?

6 07 2011

July 6, 2011

Since there were so many unique solutions to the last blog article that posed a SQL challenge, I thought that I would try another blog article that asks a similar type of question.  Assume that someone showed you the following output:

 C2   D
--- ---
100   0
150  50
200  50
201   1
300  99
350  50
400  50
500 100 

You have the following table definition, and rows in the table:

CREATE TABLE T2 (
  C1 NUMBER,
  C2 NUMBER);

INSERT INTO T2 VALUES (1,100);
INSERT INTO T2 VALUES (4,150);
INSERT INTO T2 VALUES (7,200);
INSERT INTO T2 VALUES (8,201);
INSERT INTO T2 VALUES (10,300);
INSERT INTO T2 VALUES (14,350);
INSERT INTO T2 VALUES (18,400);
INSERT INTO T2 VALUES (24,500);

COMMIT;

Assume that you know nothing other than the fact that the C2 values are listed in ascending order when sorted by column C1.  How many different ways can this particular problem be solved.  Yes, there is an easy way, but assume that you were trying to “help educate” the person who provided the requested output.

My least-shortest-path solution follows:

SELECT
  C2,
  0 D
FROM
  T2
WHERE
  C1=(SELECT
        MIN(C1)
      FROM
        T2)
UNION ALL
SELECT
  V2.C2,
  V2.C2-MAX(T2.C2) D
FROM
  T2,
  (SELECT
    C1,
    C2
  FROM
    T2) V2
WHERE
  T2.C1<V2.C1
GROUP BY
  V2.C2
ORDER BY
  C2;

  C2    D
---- ----
 100    0
 150   50
 200   50
 201    1
 300   99
 350   50
 400   50
 500  100

8 rows selected. 

In the above, the row with the 0 in the D column was the hardest part of the solution.  Why would I use UNION ALL and not UNION – what was not in the specification?

This blog article was inspired by an old question found in a Usenet group from 1998 – if you were answering the question in 1998, would your answer be any different?  Be creative with your solution.  While you are thinking about a solution, take a look at this old Usenet thread and consider how difficult it was to find the “50 highest paid workers” in the last century.





The CURSOR_SHARING Parameter is a Silver Bullet – What is Wrong with this Quote?

3 07 2011

July 3, 2011 (Modified July 5, 2011)

Define Yah-but: Almost like yeah but, but with one missing letter.

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, specifically recipe 5-19, I found a couple of interesting comments about the CURSOR_SHARING parameter, specifically the FORCE and SIMILAR parameter values.  The quotes (as usual, trying to minimize the amount of material that is quoted without losing the context of the quotes):

“The CURSOR_SHARING parameter is one of the few Oracle Silver bullets that’ll improve database performance immediately by eliminating latch contention. Use it with confidence when dealing with library cache latch contention.”

“Thus, the SIMILAR setting seems a safer way to go about forcing the database to use bind variables instead of literals. Although there are some concerns about the safety of setting of the CURSOR_SHARING parameter to FORCE, we haven’t seen any real issues with using this setting.”

I would have expected to find the phrase silver bullet in another book, such as the book “Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition“, specifically page 48:

“Note: There are no silver bullets, none. If there were any, they would be the default behavior and you would never hear about them.”

Or possibly in a presentation titled “Playing Russian Roulette with Silver Bullets”, specifically Page 14 (slide 27)

The phrase might also appear in the book “Oracle Tuning the Definitive Reference, Second Edition” specifically page 105 (the phrase silver bullet actually appears on page 102, this quote is related to the second half of the quote at the start of this article):

“Note that in Oracle 11g, cursor_sharing=similar has been debugged, and it is now possible to use cursor sharing with bind variable peeking.”

Strangely, the phrase silver bullet might also appear on a page found in the toadworld.com domain (Edit: July 5, 2011: I suspect that the same advice might also be found in that author’s recent book Per the book author’s comment attached below, this assumption is incorrect):

“The CURSOR_SHARING is one of the few Oracle parameters that can have a ‘silver bullet’ effect – instantly and dramatically increasing throughput on a parse-constrained database.”

I still have fond memories of the problems caused by the October 2006 patch for Oracle Database 10.2.0.2 that were related to setting the CURSOR_SHARING parameter to FORCE.  That problem seems to be related to this article: Metalink (MOS) Doc ID 7272297.8, Bug 7272297 – “Memory corruption / OERI[17114] / OERI[17125] with literal replacement”.

Of course there are plenty of other resources that suggest utilizing the CURSOR_SHARING parameter to tune performance, including this article that suggests changing that parameter value to SIMILAR. 

Is changing the CURSOR_SHARING parameter from EXACT to either FORCE or SIMILAR a good idea, much less a silver bullet?  Was it only a problem with Oracle Database release versions prior to 11.1? 

A couple of additonal resources to help you decide:

  • Carol Dacko reports that the SIMILAR parameter value for the CURSOR_SHARING parameter is deprecated (obsolete) as of Oracle Database 11.1 (and it will be removed in 12.1) per Metalink (MOS) Doc ID: 1169017.1
  • The Oracle Optimizer Group answers the question: “Why do I have hundreds of child cursors when cursor_sharing is set to similar in 10g”
  • The Oracle Optimizer Group explains adaptive cursor sharing behavior with cursor_sharing = similar and force
  • Kyle Hailey and Randolf Geist discuss the CURSOR_SHARING parameter in a blog article titled “Cursor_sharing: a picture is worth a 1000 words




Finding Rows with Common Attributes – Roman to Find a Solution in Something New

1 07 2011

July 1, 1011

A recent thread in the comp.databases.oracle.server Usenet group (actually two threads) asked an interesting question.  Assume that you had a detail table that contained several attributes for each of the unique key values.  How would one go about finding all of the unique key values that share the same set of attributes?  The sample set provided by the OP looks like this:

COL1   COL2
----  -----
I         a
I         b
I         c
II        a
II        b
III       a
III       b
III       c 

For the above, assume that the OP was interested in the attributes of “I”: a,b,c.  “II” lacks a “c” attribute, while “III” has the required “a”, “b”, and “c” attributes.  So, the OP would like to return C1 value “III” but not “II”.  I wonder if there is a simple solution for the OP?

First, let’s create our test data.  COL1 appears to contain Roman numbers – if we go beyond the number 3, those could be tricky to generate (unless of course you find the RN format parameter for the TO_CHAR function).  Let’s first create a temporary work table that contains the Roman numbers from 1 to 100 and a random number between 1 and 10:

CREATE TABLE T1_TEMP AS
SELECT
  TRIM(TO_CHAR(ROWNUM,'RN')) C1,
  TRUNC(DBMS_RANDOM.VALUE(1,10)+1) C2,
  ROWNUM C3
FROM
  DUAL
CONNECT BY
  LEVEL<=100; 

Let’s see what is in the T1_TEMP table:

COLUMN C1 FORMAT A10

SELECT
  *
FROM
  T1_TEMP
ORDER BY
  C3;

C1                 C2         C3
---------- ---------- ----------
I                  10          1
II                  4          2
III                 7          3
IV                  9          4
V                   8          5
VI                 10          6
VII                 9          7
VIII                4          8
IX                  4          9
X                  10         10
...
XCV                 5         95
XCVI                4         96
XCVII               8         97
XCVIII              7         98
XCIX               10         99
C                   4        100

100 rows selected. 

The row with the value “I” in column C1 has the number 10 in column C2, but that number might be a bit different in your temporary work table.  Column C2 will determine the number of attributes that are added for each of the values found in column C1 when we create the table T1 (note that we could have defined column C2 with the function CHR(96 + COUNTER) to place lowercase letters in that column, rather than numbers, to help reproduce the OP’s dataset):

CREATE TABLE T1 AS
SELECT
  T1_TEMP.C1,
  V1.COUNTER C2
FROM
  T1_TEMP,
  (SELECT
     ROWNUM COUNTER
   FROM
     DUAL
   CONNECT BY
     LEVEL<=10) V1
WHERE
  T1_TEMP.C2>=V1.COUNTER; 

Let’s see what is in table T1:

SELECT
  *
FROM
  T1
ORDER BY
  C1,
  C2;

C1                 C2
---------- ----------
C                   1
C                   2
C                   3
C                   4
I                   1
I                   2
I                   3
I                   4
I                   5
I                   6
I                   7
I                   8
I                   9
I                  10
...
XXXVII              1
XXXVII              2
XXXVII              3
XXXVIII             1
XXXVIII             2
XXXVIII             3
XXXVIII             4
XXXVIII             5
XXXVIII             6

634 rows selected.  

From the above output, you can see that we now have the number of rows in table T1 for each distinct value of C1 as was specified in table T1_TEMP.  An interesting side-note, the Roman number 100 (C) is less than the Roman number 1 (I) – I guess that explains why computers do not natively use Roman numbers for calculations.  :-)

For the next step, we need to collapse the different C2 values for each of the unique C1 values into a single row.  Oracle Database 11.2.0.1 introduced the LISTAGG function that makes easy work of this task, as shown in this earlier blog article.

COLUMN C2_LISTING FORMAT A22

SELECT
  C1,
  LISTAGG(TO_CHAR(C2), ',') WITHIN GROUP (ORDER BY C2) C2_LISTING
FROM
  T1
GROUP BY
  C1
ORDER BY
  C1;

C1         C2_LISTING
---------- --------------------
C          1,2,3,4
I          1,2,3,4,5,6,7,8,9,10
II         1,2,3,4
III        1,2,3,4,5,6,7
IV         1,2,3,4,5,6,7,8,9
IX         1,2,3,4
...
XXXV       1,2,3,4,5,6,7,8,9
XXXVI      1,2,3,4,5
XXXVII     1,2,3
XXXVIII    1,2,3,4,5,6

100 rows selected. 

The question remains, how can I find all of the unique C1 values that have all of the same attributes as the C1 value “I” – in this case 1,2,3,4,5,6,7,8,9,10?  One method slides the above query into a WITH block and then the WITH block is referenced twice in the main query:

WITH MY_VIEW AS
(SELECT
  C1,
  LISTAGG(TO_CHAR(C2), ',') WITHIN GROUP (ORDER BY C2) C2_LISTING
FROM
  T1
GROUP BY
  C1)
SELECT
  V2.C1,
  V2.C2_LISTING
FROM
  MY_VIEW V1,
  MY_VIEW V2
WHERE
  V1.C1='I'
  AND V1.C1<>V2.C1
  AND V1.C2_LISTING=V2.C2_LISTING
ORDER BY
  V2.C1;

C1         C2_LISTING
---------- --------------------
LVII       1,2,3,4,5,6,7,8,9,10
LXXI       1,2,3,4,5,6,7,8,9,10
LXXIII     1,2,3,4,5,6,7,8,9,10
VI         1,2,3,4,5,6,7,8,9,10
X          1,2,3,4,5,6,7,8,9,10
XCIX       1,2,3,4,5,6,7,8,9,10
XV         1,2,3,4,5,6,7,8,9,10
XXIX       1,2,3,4,5,6,7,8,9,10
XXXI       1,2,3,4,5,6,7,8,9,10

9 rows selected. 

How else might you solve the problem posted by the OP in the Usenet thread?








Follow

Get every new post delivered to your Inbox.

Join 137 other followers