Full Table Scan when Selecting Null Values

29 07 2010

July 29, 2010

Another interesting thread appeared on the OTN forums this week.  The original poster in the thread stated that Oracle Database was performing a full table scan for a SQL statement like this:

SELECT
  COUNT(*)
FROM
  T1
WHERE
  COL1 IS NULL;

While a SQL statement like this following did not perform a full table scan:

SELECT
  COUNT(*)
FROM
  T1;

The original poster stated that there is an index on the column COL1 (COL1 is actually ID_NUMBER in the SQL statement posted by the OP).  Full table scans are not always the worst execution path, however, the OP stated that only 0.1% (average of 1 in 1000) of the table’s rows contain a NULL value in the column.  For a standard B*Tree index, NULL values are not included in the index structure if the values for all columns in the index definition are NULL – thus Oracle Database could not be able to use a standard single column B*Tree index to locate all of the rows where that column contains a NULL value.

Several suggestions were offered in the thread:

  • Sybrand Bakker suggested creating a function based index similar to NVL(COL1, <somevalue>) and then modifying the WHERE clause to specify NVL(COL1, <somevalue>) = <somevalue>  – This is a good suggestion if the original query may be modified.
  • SomeoneElse suggested creating a bipmap index for COL1 since bitmap indexes do store NULL values for rows where all columns in the index structure contain NULL values.  – This is a good suggestion if the table is not subject to many changes.
  • I suggested creating a standard, composite B*Tree index on COL1, ‘ ‘, which is a technique I found in one of Richard Foote’s blog articles some time ago.  This approach requires roughly 2 additional bytes per index entry.
  • David Aldridge suggested creating a function based index that only includes the NULL values contained in COL1 using the following for the index definition CASE WHEN COL1 IS NULL THEN 1 END and then modifying the WHERE clause in the SQL statement to CASE WHEN COL1 IS NULL THEN 1 END = 1  – this is a good suggestion that will create a very small index structure, useful when it is possible to modify the original SQL statement.

A lot of great ideas in the thread (Richard Foote’s blog articles demonstrate several of these approaches).  Hemant K Chitale pointed out that there are potential problems with the suggestion that I offered, where a constant is used for the second column in the index so that NULL values in the first column are included in the index structure.  He referenced Metalink (My Oracle Support) Doc ID 551754.1 that described Bug 6737251 and one of his blog articles.  That Metalink article, as well as another mentioned by Amardeep Sidhu which caused problems for DBMS_STATS (Metalink Doc ID 559389.1, not mentioned in the thread, suggests a work around for the bug 6737251), essentially state to NOT use a constant for the second column in the composite index, but instead to use another column in the table.  I reviewed Metalink Doc ID 551754.1, and found it a bit lacking in detail (maybe it is just me?).  Below are my comments from the thread regarding that Metalink document:

I see a couple of problems with that Metalink (My Oracle Support) article… it lacks a little clarity (then again I could be seeing things that do not exist). First, the suggestion to use a pair of columns rather than a constant has at least four potential problems that I am able to identify:

  1. The second column must have a NOT NULL constraint (not mentioned in the article) – it cannot be just any secondary column.
  2. The secondary column will likely increase the index size a bit more than a single character used for the second column in the index.
  3. The secondary column will likely affect the clustering factor calculation for the index.
  4. The secondary column could affect the cardinality estimates for the index access path.

The second problem with the Metalink article is that, while it does demonstrate a bug, it does not show why the bug affects the results, nor does it explore other possibilities – like the one that Richard Foote used in his blog article. Here is a quick test case, loosely based on the Metalink test case, to demonstrate (note that I have not copied the statistics output from AUTOTRACE so that I may improve the clarity of the output):

First, the setup:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (C1 NUMBER, C2 NUMBER);

INSERT INTO T1 VALUES (NULL, 1);

COMMIT;

CREATE INDEX IND_T1_1 ON T1(C1,1);
CREATE INDEX IND_T1_2 ON T1(C1,' ');

We now have a table containing a single row and two indexes – the first index uses a numeric constant for the second column in the index, while the second index uses a blank space for the second column in the index. Now continuing:

SET LINESIZE 140
SET PAGESIZE 1000
SET TRIMSPOOL ON
SET AUTOTRACE ON

SELECT /*+ INDEX(T1 IND_T1_1) */
  *
FROM
  T1
WHERE
  C1 IS NULL;

        C1         C2
---------- ----------
                    1

Execution Plan
----------------------------------------------------------
Plan hash value: 2805969644

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1" IS NULL)

Note
-----
   - dynamic sampling used for this statement

The above worked as expected. Continuing:

SELECT /*+ INDEX(T1 IND_T1_1) */
  *
FROM
  T1
WHERE
  C1 IS NULL
  AND ROWNUM<=1;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 404994253

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    26 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - access("C1" IS NULL AND ROWNUM<=1)
       filter(ROWNUM<=1)

Note
-----
   - dynamic sampling used for this statement

Note that this time we encountered the bug – take a close look at the Predicate Information section of the execution plan to see why.

Now the test continues with the suggestion from Richard’s blog:

SELECT /*+ INDEX(T1 IND_T1_2) */
  *
FROM
  T1
WHERE
  C1 IS NULL;

        C1         C2
---------- ----------
                    1

Execution Plan
----------------------------------------------------------
Plan hash value: 348287884

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1_2 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1" IS NULL)

Note
-----
   - dynamic sampling used for this statement

We obtained the same result as before [the correct results], continuing:

SELECT /*+ INDEX(T1 IND_T1_2) */
  *
FROM
  T1
WHERE
  C1 IS NULL
  AND ROWNUM<=1;

        C1         C2
---------- ----------
                    1

Execution Plan
----------------------------------------------------------
Plan hash value: 2383334138

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |    26 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1       |     1 |    26 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_T1_2 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1)
   3 - access("C1" IS NULL)

Note
-----
   - dynamic sampling used for this statement

Note that this time we did not encounter the bug [we received the correct results], and a row was returned. Compare the Predicate Information section of the execution plan with the one that failed to produce the correct result.

Let’s remove the “dynamic sampling used for this statement” note by gathering statistics:

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1')
BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1'); END;

*
ERROR at line 1:
ORA-03001: unimplemented feature
ORA-06512: at "SYS.DBMS_STATS", line 13159
ORA-06512: at "SYS.DBMS_STATS", line 13179
ORA-06512: at line 1

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

PL/SQL procedure successfully completed.

Well, it appears that we hit another bug [likely the one mentioned by Amardeep Sidhu]. Note that I successfully gathered statistics on another table just to demonstrate that there was not a problem with my statistics gathering syntax. Let’s fix that problem [removing the problem that triggered the bug in the Oracle Database software]:

SQL> DROP INDEX IND_T1_1;

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

PL/SQL procedure successfully completed.

That is better. Apparently, Oracle Database (at least 10.2.0.2) has problems when the second column in an index definition is a number constant, but not when the second column is a character constant.

Mohamed Houri asked a very good question about the impact on the calculated clustering factor for an index when the index definition is changed to have a blank space for the second column, which will allow the rows containing NULL values to be indexed.  A change in the clustering factor may affect the optimizer’s decision to select an index access path using that index.

Without performing a test, I would estimate that each NULL value to be included in the index structure will increase the clustering factor by a value of 1, unless all of those rows containing the NULL values are contained within a couple of table blocks, in which case the clustering factor should increase by less than 1 per NULL value – such a case would likely assume that the rows were inserted at roughly the same time.

Confusing? The NULL values in the index structure will be logically grouped together in logically adjacent index leaf blocks, when this is considered as well as the algorithm for calculating the clustering factor, the maximum increase in the clustering for this approach should be fairly easy to guess. See the clustering factor chapter in the book “Cost-Based Oracle Fundamentals”.

Let’s create a test case (note that this test case differs slightly from the test case in the thread) that hopefully explores some of the possibilities, and tries to determine the potential impact on the clustering factor.  The setup:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  COL1 NUMBER NOT NULL,
  COL2 NUMBER,
  COL3 NUMBER,
  EMPLOYEE_ID VARCHAR2(20),
  SHIFT_DATE DATE NOT NULL,
  ROLLING_DATE DATE NOT NULL,
  INDIRECT_ID VARCHAR2(20));

INSERT INTO
  T1
SELECT
  ROWNUM COL1,
  DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM) COL2,
  DECODE(SIGN(999001-ROWNUM),1,ROWNUM,NULL) COL3,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,5)),
          0,'MIKE',
          1,'ROB',
          2,'SAM',
          3,'JOE',
          4,'ERIC') EMPLOYEE_ID,
  TRUNC(SYSDATE)-ROUND(DBMS_RANDOM.VALUE(0,1000)) SHIFT_DATE,
  TRUNC(SYSDATE) + (1/1000)*ROWNUM ROLLING_DATE,
  DECODE(TRUNC(DBMS_RANDOM.VALUE(0,10)),
          0,'VAC',
          1,'HOL',
          2,'BEREAVE',
          3,'JURY',
          4,'ABS',
          5,'EXCUSE',
          6,'MIL',
          'OTHER') INDIRECT_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

COMMIT;

We now have 1,000,000 rows.  Columns COL2 and COL3 each contain 0.1% NULL values (COL2 has the NULL values evenly dispursed, while COL3 has the NULL values in the last 1,000 rows inserted).  Now, let’s demonstrate an example of over-indexing – do not do this in a production environment.

CREATE INDEX IND_T1_COL1 ON T1(COL1);
CREATE INDEX IND_T1_COL2 ON T1(COL2);
CREATE INDEX IND_T1_EMPL ON T1(EMPLOYEE_ID);
CREATE INDEX IND_T1_SHIFT_DATE ON T1(SHIFT_DATE);
CREATE INDEX IND_T1_ROLL_DATE ON T1(ROLLING_DATE);

CREATE INDEX IND_T1_COL2_NOT_NULL ON T1(COL2,' ');
CREATE INDEX IND_T1_COL3_NOT_NULL ON T1(COL3,' ');
CREATE INDEX IND_T1_EMPL_NOT_NULL ON T1(EMPLOYEE_ID,' ');

CREATE INDEX IND_T1_COL2_NOT_NULL2 ON T1(COL2,COL1);
CREATE INDEX IND_T1_COL2_NOT_NULL3 ON T1(COL2,SHIFT_DATE);
CREATE INDEX IND_T1_COL2_NOT_NULL4 ON T1(COL2,ROLLING_DATE);
CREATE INDEX IND_T1_COL2_NOT_NULL5 ON T1(COL2,EMPLOYEE_ID);
CREATE INDEX IND_T1_COL2_NOT_NULL6 ON T1(NVL(COL2,-1));
CREATE INDEX IND_T1_COL2_NOT_NULL7 ON T1(CASE WHEN COL2 IS NULL THEN 1 END);

CREATE INDEX IND_T1_COL3_NOT_NULL2 ON T1(COL3,COL1);
CREATE INDEX IND_T1_COL3_NOT_NULL3 ON T1(COL3,SHIFT_DATE);
CREATE INDEX IND_T1_COL3_NOT_NULL4 ON T1(COL3,ROLLING_DATE);
CREATE INDEX IND_T1_COL3_NOT_NULL5 ON T1(COL3,EMPLOYEE_ID);
CREATE INDEX IND_T1_COL3_NOT_NULL6 ON T1(NVL(COL3,-1));
CREATE INDEX IND_T1_COL3_NOT_NULL7 ON T1(CASE WHEN COL3 IS NULL THEN 1 END);

CREATE INDEX IND_T1_EMPL_NOT_NULL2 ON T1(EMPLOYEE_ID,COL1);
CREATE INDEX IND_T1_EMPL_NOT_NULL3 ON T1(NVL(EMPLOYEE_ID,'UNKNOWN'));
CREATE INDEX IND_T1_EMPL_NOT_NULL4 ON T1(CASE WHEN EMPLOYEE_ID IS NULL THEN 'UNKNOWN' END);

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

The statistics collection script was specified to sample 100% of the table and indexes.  For my test run on Oracle Database 11.1.0.7, I received the following results (I added the index definition to the end of each line of output):

SELECT
  INDEX_NAME,
  DISTINCT_KEYS,
  BLEVEL,
  LEAF_BLOCKS,
  CLUSTERING_FACTOR,
  SAMPLE_SIZE
FROM
  USER_INDEXES
WHERE
  TABLE_NAME='T1'
ORDER BY
  INDEX_NAME;

INDEX_NAME            DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR SAMPLE_SIZE
--------------------- ------------- ------ ----------- ----------------- -----------
IND_T1_COL1                 1000000      2        2226              6330     1000000  (COL1)
IND_T1_COL2                  999000      2        2224              6330      999000  (COL2)
IND_T1_COL2_NOT_NULL         999001      2        2505              7330     1000000  (COL2,' ')
IND_T1_COL2_NOT_NULL2       1000000      2        2921              7330     1000000  (COL2,COL1)
IND_T1_COL2_NOT_NULL3        999640      2        3343              7330     1000000  (COL2,SHIFT_DATE)
IND_T1_COL2_NOT_NULL4       1000000      2        3343              7330     1000000  (COL2,ROLLING_DATE)
IND_T1_COL2_NOT_NULL5        999005      2        2841              7330     1000000  (COL2,EMPLOYEE_ID)
IND_T1_COL2_NOT_NULL6        999001      2        2226              7330     1000000  (NVL(COL2,-1))
IND_T1_COL2_NOT_NULL7             1      1           2              1000        1000  (CASE WHEN COL2 IS NULL THEN 1 END)
IND_T1_COL3_NOT_NULL         999001      2        2505              6331     1000000  (COL3,' ')
IND_T1_COL3_NOT_NULL2       1000000      2        2921              6330     1000000  (COL3,COL1)
IND_T1_COL3_NOT_NULL3        999638      2        3343              7150     1000000  (COL3,SHIFT_DATE)
IND_T1_COL3_NOT_NULL4       1000000      2        3343              6330     1000000  (COL3,ROLLING_DATE)
IND_T1_COL3_NOT_NULL5        999005      2        2841              6359     1000000  (COL3,EMPLOYEE_ID)
IND_T1_COL3_NOT_NULL6        999001      2        2226              6331     1000000  (NVL(COL3,-1))
IND_T1_COL3_NOT_NULL7             1      1           2                 7        1000  (CASE WHEN COL3 IS NULL THEN 1 END)
IND_T1_EMPL                       5      2        2147             31585     1000000  (EMPLOYEE_ID)
IND_T1_EMPL_NOT_NULL              5      2        2425             31585     1000000  (EMPLOYEE_ID,' ')
IND_T1_EMPL_NOT_NULL2       1000000      2        2840             31598     1000000  (EMPLOYEE_ID,COL1)
IND_T1_EMPL_NOT_NULL3             5      2        2147             31585     1000000  (NVL(EMPLOYEE_ID,'UNKNOWN'))
IND_T1_EMPL_NOT_NULL4             0      0           0                 0           0  (CASE WHEN EMPLOYEE_ID IS NULL THEN 'UNKNOWN' END)
IND_T1_ROLL_DATE            1000000      2        2646              6330     1000000  (ROLLING_DATE)
IND_T1_SHIFT_DATE              1001      2        2646            925286     1000000  (SHIFT_DATE)

SELECT
  BLOCKS
FROM
  USER_TABLES
WHERE
  TABLE_NAME='T1';

BLOCKS
------
  6418

For the 1,000,000 row table, 1 of every 1,000 rows contains a NULL value in column COL2, and the clustering factor increased by exactly 1,000 (1,000,000/1,000) for the index on that column that used a single space for the second column in the index (IND_T1_COL2_NOT_NULL). The results were different when the majority of the NULL values were grouped together, as was the case for column COL3, where the clustering factor increased by a value of 1. In this particular test case, with the NULL values evenly dispursed, the clustering factor is about the same regardless of whether a blank space is used for the second column, or an actual table column.  The clustering factor calculation for the index definition NVL(COL2, -1) and NVL(COL3, -1) was identical to that for the index definition COL2, ‘ ‘ and COL3, ‘ ‘, respectively.  The index structure using the CASE syntax was extremely small when 0.1% of the rows in the table contained NULL values, and thus produced a very small clustering factor because there were few index entries.  The test case did not include a bitmap index, because the clustering factor statistic for such an index has a different meaning.  A clustering factor of 6,330 is the lowest possible value for the indexes not using the CASE syntax (I expected it to be closer to the number of blocks in the table). 

Your results in a production environment may be very different from those in my simple test case, so be certain to test your solution.  So, the question: What would you do if you were faced with the problem identified by the original poster in the OTN thread?





SQL – Programmatic Row By Row to MERGE INTO

27 07 2010

July 27, 2010

A question in an email from an ERP mailing list combined with Cary Millsap’s latest blog article inspired this blog article.  The question from the ERP mailing list asked the following question:

Does anyone have Oracle syntax for the ‘upsert‘ command?  I have found a few examples, but little success yet.

Using VB.net, I want to have one command which will see if data exists, and if yes, update, if no, then insert.

There are several ways to approach this particular problem, some of which may be more efficient than others.  For example, assume that we have a table defined like this:

CREATE TABLE T2(
  ID NUMBER,
  COL2 NUMBER,
  COL3 NUMBER,
  COL4 NUMBER,
  PRIMARY KEY (ID));

Then we insert 5 rows using the following SQL statement (if you receive a primary key violation, just try executing the INSERT statement again) and then create a table that will allow us to quickly restore the original values for various repeated tests:

INSERT INTO
  T2
SELECT
  TRUNC(DBMS_RANDOM.VALUE(1,30)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000)),
  TRUNC(DBMS_RANDOM.VALUE(1,1000))
FROM
  DUAL
CONNECT BY
  LEVEL<=5;

CREATE TABLE
  T2_BACKUP
AS
SELECT
  *
FROM
  T2;

The five rows created by the above will have random numeric values in the COL2, COL3, and COL4 columns.  The rows might look something like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1        993        718        103
10        583        924        458
13         27        650        861
16        141        348        813
28        716        517        204

Now we want to fill in the missing rows, so that ID values 1 through 30 appear in the table, but if the row already exists, we will modify the column values as follows:

  • COL2 will be set to a value of 0
  • COL3 will be set to a value of COL2 + COL3
  • COL4 will be set to a random value

How might we make these changes?  Well, we might do something silly, as demonstrated by the following VB Script code (this code may be executed with wscript or cscript on the Windows platform – it is also compatible with Visual Basic 6 and the Excel macro language, but the late binding should be changed to early binding, and variable types should be declared):

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim dbDatabase
Dim dynData
Dim intS_ID
Dim intS_C2
Dim intS_C3
Dim intS_C4
Dim i
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")
Set dynData = CreateObject("ADODB.Recordset")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code

dbDatabase.BeginTrans

For i = 1 To 30
    strSQL = "SELECT" & vbCrLf
    strSQL = strSQL & "  ID," & vbCrLf
    strSQL = strSQL & "  COL2," & vbCrLf
    strSQL = strSQL & "  COL3," & vbCrLf
    strSQL = strSQL & "  COL4" & vbCrLf
    strSQL = strSQL & "FROM" & vbCrLf
    strSQL = strSQL & "  T2" & vbCrLf
    strSQL = strSQL & "WHERE" & vbCrLf
    strSQL = strSQL & "  ID=" & CStr(i)

    dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic

    intS_ID = i
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If Not (dynData.BOF And dynData.EOF) Then
        dynData("col2") = 0
        dynData("col3") = dynData("col2") + dynData("col3")
        dynData("col4") = intS_C4
    Else
        'No row found, need to add
        dynData.AddNew

        dynData("id") = i
        dynData("col2") = intS_C2
        dynData("col3") = intS_C3
        dynData("col4") = intS_C4
    End If
    dynData.Update

    dynData.Close
Next

dbDatabase.CommitTrans

dbDatabase.Close

Set dynData = Nothing
Set dbDatabase = Nothing

There are a couple of problems with the above, beyond the lack of bind variable usage.  At least 30 SQL statements are sent to the database.  If a row is tested to exist (the recordset’s BOF and EOF properties are not both true) then the row’s values are updated, otherwise a row is inserted.  This is the row by row (slow by slow) method of accomplishing the task.  When the script is executed, the table contents might look like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0        718        580
 2        290        302        775
 3         15        761        815
 4        710         46        415
 5        863        791        374
 6        962        872         57
 7        950        365        525
 8        768         54        593
 9        469        299        623
10          0        924        280
11        830        825        590
12        987        911        227
13          0        650        244
14        534        107       1000
15        677         16        576
16          0        348        799
17        285         46        296
18        383        301        949
19        980        402        279
20        161        163        647
21        411        413        713
22        327        634        208
23        187        584         81
24        458        906        262
25        786        379        290
26        920        632        628
27        429         98        562
28          0        517        835
29         23        544        917
30        431        678        503

Let’s return to the original starting point for table T2 so that we may try another test:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

Let’s eliminate the majority of the 30+ SQL statements that are sent to the database by modifying the VBS script:

Const adOpenKeyset = 1
Const adLockOptimistic = 3

Dim dbDatabase
Dim dynData
Dim intS_ID
Dim intS_C2
Dim intS_C3
Dim intS_C4
Dim i
Dim intMissing(30)
Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")
Set dynData = CreateObject("ADODB.Recordset")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code

dbDatabase.BeginTrans

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  ID," & vbCrLf
strSQL = strSQL & "  COL2," & vbCrLf
strSQL = strSQL & "  COL3," & vbCrLf
strSQL = strSQL & "  COL4" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  ID"

dynData.Open strSQL, dbDatabase, adOpenKeyset, adLockOptimistic

For i = 1 To 30
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If Not (dynData.EOF) Then
        If i = CInt(dynData("id")) Then
            intMissing(i) = False
            dynData("col2") = 0
            dynData("col3") = dynData("col2") + dynData("col3")
            dynData("col4") = intS_C4
            dynData.Update

            dynData.MoveNext
        Else
            intMissing(i) = True
        End If
    Else
        intMissing(i) = True
    End If
Next

'Add the missing rows
For i = 1 To 30
    intS_C2 = Int(Rnd * 1000) + 1
    intS_C3 = Int(Rnd * 1000) + 1
    intS_C4 = Int(Rnd * 1000) + 1

    If intMissing(i) = True Then
        dynData.AddNew
        dynData("id") = i
        dynData("col2") = intS_C2
        dynData("col3") = intS_C3
        dynData("col4") = intS_C4
        dynData.Update
    End If
Next

dynData.Close
dbDatabase.CommitTrans

dbDatabase.Close
Set dynData = Nothing
Set dbDatabase = Nothing

That certainly is better.  Here is the output showing the table’s contents:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0        718        580
 2        405        270         56
 3        244        980         61
 4        391        365        490
 5        156        475        258
 6        629        543        157
 7        939        655        507
 8        391        108        784
 9        460        754        597
10          0        924        280
11         74        106        332
12        129          1        537
13          0        650        244
14         82        192        679
15        455        358        150
16          0        348        799
17         90        758        402
18        462        493        208
19        330         96        590
20        170        928         98
21        444        273        873
22        751        273        674
23        257         90         31
24        323        791        298
25        236        481        255
26        341         45        483
27        207        865        589
28          0        517        835
29        543         81        635
30        411        961        115

Better, but not good enough.  There are too many round-trips between the client and server.  Let’s reset the T2 test table and try again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

A third attempt collapses a lot of client-side code into two SQL statement:

Dim dbDatabase

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code

dbDatabase.BeginTrans

strSQL = "UPDATE" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "SET" & vbCrLf
strSQL = strSQL & "  COL2=0," & vbCrLf
strSQL = strSQL & "  COL3=COL2+COL3," & vbCrLf
strSQL = strSQL & "  COL4=TRUNC(DBMS_RANDOM.VALUE(1,1000))"
dbDatabase.Execute strSQL

strSQL = "INSERT INTO" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "SELECT" & vbCrLf
strSQL = strSQL & "  S.S_ID," & vbCrLf
strSQL = strSQL & "  S.S_C2," & vbCrLf
strSQL = strSQL & "  S.S_C3," & vbCrLf
strSQL = strSQL & "  S.S_C4" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    ROWNUM S_ID," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    DUAL" & vbCrLf
strSQL = strSQL & "  CONNECT BY" & vbCrLf
strSQL = strSQL & "    LEVEL<=30) S," & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  S.S_ID=T2.ID(+)" & vbCrLf
strSQL = strSQL & "  AND T2.ID IS NULL"
dbDatabase.Execute strSQL

dbDatabase.CommitTrans

Set dbDatabase = Nothing

Here is the output:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        202
 2        944        284        604
 3        612        909        576
 4        828        606        970
 5        433        868        446
 6        304        770        397
 7        502        257        474
 8        541        906        761
 9        283        614        819
10          0       1507        841
11        772         52        635
12        325         45        792
13          0        677        320
14        691        433        234
15        733        673        416
16          0        489        483
17        257         50         99
18        429        861        108
19        244          4        858
20        323        697        493
21        565        384        960
22        211        153        651
23        762        231        488
24         85        994        204
25        630        235        930
26        890        778        374
27         64        540        663
28          0       1233        955
29         70         16         56
30        493        647        742

Look closely at the above output.  Are you able to spot the “logic bug” in the first two code examples?

I like the above code sample, but we are able to improve it a bit by using a single SQL statement.  First, let’s reset the test table again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

Now the code sample that uses a single SQL statement:

Dim dbDatabase

Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase

Set dbDatabase = CreateObject("ADODB.Connection")

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
dbDatabase.Open
'Should verify that the connection attempt was successful, but I will leave that for someone else to code

dbDatabase.BeginTrans

strSQL = "MERGE INTO" & vbCrLf
strSQL = strSQL & "  T2" & vbCrLf
strSQL = strSQL & "USING" & vbCrLf
strSQL = strSQL & "  (SELECT" & vbCrLf
strSQL = strSQL & "    ROWNUM S_ID," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3," & vbCrLf
strSQL = strSQL & "    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4" & vbCrLf
strSQL = strSQL & "  FROM" & vbCrLf
strSQL = strSQL & "    DUAL" & vbCrLf
strSQL = strSQL & "  CONNECT BY" & vbCrLf
strSQL = strSQL & "    LEVEL<=30) S" & vbCrLf
strSQL = strSQL & "ON" & vbCrLf
strSQL = strSQL & "  (T2.ID=S.S_ID)" & vbCrLf
strSQL = strSQL & "WHEN MATCHED THEN" & vbCrLf
strSQL = strSQL & "  UPDATE SET" & vbCrLf
strSQL = strSQL & "    T2.COL2=0," & vbCrLf
strSQL = strSQL & "    T2.COL3=T2.COL2+T2.COL3," & vbCrLf
strSQL = strSQL & "    T2.COL4=S.S_C4" & vbCrLf
strSQL = strSQL & "WHEN NOT MATCHED THEN" & vbCrLf
strSQL = strSQL & "  INSERT (ID, COL2, COL3, COL4) VALUES" & vbCrLf
strSQL = strSQL & "    (S.S_ID," & vbCrLf
strSQL = strSQL & "    S.S_C2," & vbCrLf
strSQL = strSQL & "    S.S_C3," & vbCrLf
strSQL = strSQL & "    S.S_C4)"
dbDatabase.Execute strSQL

dbDatabase.CommitTrans

Set dbDatabase = Nothing

The output of the above looks like this:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        286
 2        419         68        698
 3        849        296        986
 4         92         87        433
 5        425        786        802
 6        758        862        868
 7        450        327        978
 8        102        618        382
 9        276        563        620
10          0       1507        629
11        292        591        300
12        521        599        941
13          0        677        438
14        182        905        135
15        716        121        964
16          0        489        165
17        552        661         95
18        332        572        255
19        126        624        463
20        906        422        368
21        328        141        886
22        286        612        685
23        375        868        904
24        240        940        768
25          4        166        447
26        942        754        124
27        547        828        225
28          0       1233        872
29        883        417        215
30        762        427         21

At this point you are probably wondering why I even bothered to use VBScript for such a simple SQL statement.  Let’s reset the test table again:

DELETE FROM T2;

INSERT INTO
  T2
SELECT
  *
FROM
  T2_BACKUP;

COMMIT;

If I was trying to be as efficient as possible, I probably should have just executed the following in SQL*Plus:

MERGE INTO
  T2
USING
  (SELECT
    ROWNUM S_ID,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C2,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C3,
    TRUNC(DBMS_RANDOM.VALUE(1,1000)) S_C4
  FROM
    DUAL
  CONNECT BY
    LEVEL<=30) S
ON
  (T2.ID=S.S_ID)
WHEN MATCHED THEN
  UPDATE SET
    T2.COL2=0,
    T2.COL3=T2.COL2+T2.COL3,
    T2.COL4=S.S_C4
WHEN NOT MATCHED THEN
  INSERT (ID, COL2, COL3, COL4) VALUES
    (S.S_ID,
    S.S_C2,
    S.S_C3,
    S.S_C4);

The following shows the modifications made by the above:

SELECT
  *
FROM
  T2
ORDER BY
  ID;

ID       COL2       COL3       COL4
-- ---------- ---------- ----------
 1          0       1711        849
 2        502        487        567
 3        273        966        847
 4        236        544        198
 5        191        970        986
 6        820        316        468
 7        833        651         82
 8         46        583        368
 9         63        685        148
10          0       1507        249
11        111        409         88
12        219        795        409
13          0        677        571
14        771         26        313
15        373        962        186
16          0        489        514
17        230        970        824
18         92        715        131
19        355        220        206
20        996         87        841
21        815        384        375
22        935        455        339
23        606        190        720
24        558        591        341
25        780        207        614
26        267        430        371
27        881        292        655
28          0       1233         70
29        379        466        628
30        293        216        881

We are certainly able to arrive at the correct answer many different ways (and the incorrect answer at least once), but what is the right way to achieve the task placed in front of us is not always easy to see.  The MERGE INTO syntax is one that I have not used often enough, and probably deserves a greater investment of experimentation.

Have you found the logic bug with the first two code samples yet?

dynData("col2") = 0
dynData("col3") = dynData("col2") + dynData("col3")
dynData("col4") = intS_C4

The above works correctly when the columns are updated in that order in a SQL statement, but VBScript requires a minor adjustment to produce the correct, expected results:

dynData("col3") = dynData("col2") + dynData("col3")
dynData("col2") = 0
dynData("col4") = intS_C4

Picky, picky, picky…  :-)





SQL_TYPE_MISMATCH in V$SQL_SHARED_CURSOR

23 07 2010

July 23, 2010

While reading the OTN forums a couple of days ago I found a simple request regarding the meaning of the SQL_TYPE_MISMATCH column in V$SQL_SHARED_CURSOR.  The Oracle Database 8i documentation and the Oracle Database 11.2 documentation both state the following very limited description for that column in V$SQL_SHARED_CURSOR:

“The SQL type does not match the existing child cursor”

Just what does the above mean?  Analysis indicates that the SQL_TYPE_MISMATCH column indicates that something caused a setting change originating from the client (possibly Oracle client version from one client computer to the next or a change from DAO, ADO, RDO, OO4O, etc.), and that change triggered a hard parse resulting in the generation of a new child cursor with a different value in the SQLTYPE column of V$SQL.  I have not found the triggering element that causes apparently the same client computer with the same username and with the same ERP package to generate multiple child cursors for the same SQL statement, with just different values displayed in the SQLTYPE column.

For example (do not execute this SQL statement in a busy database):

SELECT
  SS1.SQL_TEXT,
  SS1.SQL_ID,
  SS1.CHILD_NUMBER,
  SS2.CHILD_NUMBER,
  SS1.SQLTYPE,
  SS2.SQLTYPE,
  SS1.PARSING_SCHEMA_NAME,
  SS2.PARSING_SCHEMA_NAME
FROM
  V$SQL SS1,
  V$SQL SS2
WHERE
  SS1.SQL_ID=SS2.SQL_ID
  AND SS1.SQLTYPE < SS2.SQLTYPE
ORDER BY
  SS1.SQL_TEXT,
  SS1.CHILD_NUMBER,
  SS2.CHILD_NUMBER;

A sampling of the output from a production database: 

SQL_ID        CHILD_NUMBER CHILD_NUMBER    SQLTYPE    SQLTYPE PARSING_SCHEMA_NAME            PARSING_SCHEMA_NAME
------------- ------------ ------------ ---------- ---------- ------------------------------ ------------------------------
SELECT BANNER FROM V$VERSION
39tw34mramfdv            0            1          2          6 USER3                          USER4  

  select SYSDATE from APPLICATION_GLOBAL
crmxa1g4nrc89            0            2          2          6 USER1                          USER1

SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"',  decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#),
  decode (rl.ectx#, 0, decode(rm.ectx#, 0,    (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1,    obj$ o1 where
o1.obj# = rs.ectx# and o1.owner# = u1.user#),   (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2,    obj$ o2 where
 o2.obj# = rm.ectx# and o2.owner# = u2.user#)),   (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3,    obj$ o3 where
 o3.obj# = rl.ectx# and o3.owner# = u3.user#))   from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl,   rule_set$ rs
   where su.name = :1 and so.name = :2 and so.owner# = su.user# and   so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# =
ru.user#   and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj#
3hgxzypxz2xpg            3            1          0          2 SYS                            SYS

SELECT ro.obj#, '"'||ru.name||'"' || '.' || '"'||ro.name||'"',  decode(rl.ectx#, 0, decode(rm.ectx#, 0, rs.ectx#, rm.ectx#), rl.ectx#),
  decode (rl.ectx#, 0, decode(rm.ectx#, 0,    (select '"'||u1.name||'"' || '.' || '"'||o1.name||'"' from user$ u1,    obj$ o1 where
o1.obj# = rs.ectx# and o1.owner# = u1.user#),   (select '"'||u2.name||'"' || '.' || '"'||o2.name||'"' from user$ u2,    obj$ o2 where
 o2.obj# = rm.ectx# and o2.owner# = u2.user#)),   (select '"'||u3.name||'"' || '.' || '"'||o3.name||'"' from user$ u3,    obj$ o3 where
 o3.obj# = rl.ectx# and o3.owner# = u3.user#))   from obj$ so, user$ su, rule_map$ rm, obj$ ro, user$ ru, rule$ rl,   rule_set$ rs
   where su.name = :1 and so.name = :2 and so.owner# = su.user# and   so.obj# = rm.rs_obj# and rm.r_obj# = ro.obj# and ro.owner# =
ru.user#   and rl.obj# = rm.r_obj# and rs.obj# = rm.rs_obj#
3hgxzypxz2xpg            3            2          0          2 SYS                            SYS

alter session set events 'immediate trace name krb_options level 5'
fw1b57tbvz13j            1            0          0          6 SYS                            SYS

delete from source$ where obj#=:1
5t480bb4uh8hw            1            0          0          2 SYS                            SYS

delete from source$ where obj#=:1
5t480bb4uh8hw            3            0          0          2 SYS                            SYS

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            0          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            1          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            2          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            3          2          6 USER5                          USER9

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            4          2          6 USER5                          USER5

update next_number_gen set next_number = :1             where table_name = :2          and column_name = :3
0jw74fwfdkv1x            6            5          2          6 USER5                          USER10

...

Checking V$SQL_SHARED_CURSOR for several of the above SQL statements to determine why the additional child cursors were created:

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='39tw34mramfdv'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
39tw34mramfdv 00000001847D4CA0 00000001847D3D70            0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
39tw34mramfdv 00000001847D4CA0 00000001655BB248            1 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

(only SQL_TYPE_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='crmxa1g4nrc89'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
crmxa1g4nrc89 000000018460A3F8 00000001846094C8            0 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
crmxa1g4nrc89 000000018460A3F8 0000000161263120            2 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N

(only SQL_TYPE_MISMATCH and ROLL_INVALID_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='3hgxzypxz2xpg'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
3hgxzypxz2xpg 000000018CFE1950 0000000185222478            1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
3hgxzypxz2xpg 000000018CFE1950 0000000184C8AFE0            2 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
3hgxzypxz2xpg 000000018CFE1950 0000000160FA5908            3 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N

(only SQL_TYPE_MISMATCH and OPTIMIZER_MODE_MISMATCH)

 

SELECT
  SSC.*
FROM
  V$SQL S,
  V$SQL_SHARED_CURSOR SSC
WHERE
  S.SQL_ID='0jw74fwfdkv1x'
  AND S.SQL_ID=SSC.SQL_ID
  AND S.CHILD_ADDRESS=SSC.CHILD_ADDRESS
ORDER BY
  SSC.CHILD_NUMBER;

SQL_ID        ADDRESS          CHILD_ADDRESS    CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0jw74fwfdkv1x 0000000167748680 000000017CC367C0            0 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000016F344C28            1 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000162D93820            2 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
0jw74fwfdkv1x 0000000167748680 0000000154E037E0            3 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000176A5B698            4 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000016B9DEA38            5 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 000000018D8A5370            6 N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000187D7A1C8            7 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N
0jw74fwfdkv1x 0000000167748680 0000000171C634F0            8 N Y N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N N

(SQL_TYPE_MISMATCH, BIND_MISMATCH and ROLL_INVALID_MISMATCH)

The last of the above SQL statements which was investigated included bind variables, and V$SQL_SHARED_CURSOR indicated that the additional child cursors were created in part due to a bind mismatch.  Let’s take a look at the bind variable definitions for the SQL statement:

SELECT
  S.CHILD_NUMBER,
  SBM.POSITION,
  SBM.DATATYPE,
  SBM.MAX_LENGTH,
  SBM.BIND_NAME
FROM
  V$SQL S,
  V$SQL_BIND_METADATA SBM
WHERE
  S.SQL_ID='0jw74fwfdkv1x'
  AND S.CHILD_ADDRESS=SBM.ADDRESS
ORDER BY
  S.CHILD_NUMBER,
  SBM.POSITION;

CHILD_NUMBER   POSITION   DATATYPE MAX_LENGTH BIND_NAME
------------ ---------- ---------- ---------- ---------
           0          1          1         32 1
           0          2         96         32 2
           0          3         96         32 3
           1          1          1         32 1
           1          2         96         32 2
           1          3         96         32 3
           2          1          1         32 1
           2          2         96         32 2
           2          3         96         32 3
           3          1          1         32 1
           3          2         96         32 2
           3          3         96         32 3
           4          1          1         32 1
           4          2         96         32 2
           4          3         96         32 3
           5          1          1         32 1
           5          2         96         32 2
           5          3         96         32 3
           6          1          1         32 1
           6          2         96         32 2
           6          3         96         32 3
           7          1          1         32 1
           7          2         96         32 2
           7          3         96         32 3
           8          1          1         32 1
           8          2         96         32 2
           8          3         96         32 3

The bind variable definitions appear to be consistent from one child cursor to the next even though V$SQL_SHARED_CURSOR reported a difference in the bind variable definitions or data lengths.  The commercial ERP application, which is the primary source of SQL statements in this database, typically does not pre-initialize bind variable values during the initial parse call, which results in the MAX_LENGTH of VARCHAR2 bind variables being set to 2000.  The above shows that what should be a numeric bind variable is defined as a VARCHAR2 and what should be a VARCHAR2 is defined as CHAR – those child cursors with non-initialized bind variables might have aged out of the libary cache.

From the above analysis we see that the change in the SQLTYPE is not just confined to just SQL statements with bind variables, not confined to a specific username (in some cases the same username appears for both child cursors with different SQLTYPE values), and is not specific to just application level SQL.  It is still a bit of a mystery to me what causes the SQLTYPE column in V$SQL to change for internal SQL statements and SQL statements that are executed by the same client computer.

Any idea what triggers the change in the SQLTYPE column?  For this particular database, values of 0 and 2 appear to be used when SQL is parsed by SYS, while values of 2 and 6 appear to be used when SQL is parsed by application code.  This is likely more of a odd behavior than an actual problem.





Demonstration of Oracle “Ignoring” an Index Hint

19 07 2010

July 19, 2010 (Modified July 26, 2010, January 18, 2011)

In an earlier blog article I showed that adding an index hint is sufficient to allow Oracle to use a normal (non-function based) index on a column when that column is included in the WHERE clause inside a function.  Another blog article listed reasons why the Oracle optimizer may select not to use an index, with the help of the book “Expert One-On-One Oracle”.  I also wrote a blog article about the various hints that are available in 11g R1 and 11g R2.  I just stumbled across a forum post of mine from a year ago that included a test case regarding Oracle ignoring hints, so I thought that I would include that test case here (one of the initial justifications for setting up this blog is that I had difficulty with locating my previously created test cases).

First, hints are directives – Oracle’s optimizer cannot ignore hints unless:

  • The hint is invalid due to the wrong alias used in the hint
  • The hint is malformed
  • The hint is incompatible with another hint
  • The query was transformed by the optimizer into a form that is incompatible with the hint before the optimizer applied the hint
  • The hint, if followed, would cause the wrong results to be returned (see the forum thread for an explanation)

(Late Additions to the List, added July 26, 2010:)

  • Bugs in Oracle Database cause the hint to be lost (see Jonathan Lewis’ comment in the above forum post, and the examples on his blog – for example)
  • The hint, which appears like a comment, is removed before the query is sent to Oracle Database (see Mark W. Farnham’s comment below)
  • The hint specifies the use of a feature that is explicitly disabled by an initialization parameter
  • The _OPTIMIZER_IGNORE_HINTS initialization parameter is set to TRUE
  • An off-shoot of bullet point #5, an INSERT statement includes an APPEND hint and the table to be modified includes triggers and/or foreign key constraints (see Mohamed Houri’s comments below)

(Late Addition to the List, added January 18, 2011:)

Here is the setup for the test case from the forum thread:

CREATE TABLE T15(
  C1 NUMBER NOT NULL,
  C2 VARCHAR2(50) NOT NULL,
  C3 NUMBER,
  C4 VARCHAR2(300));

INSERT INTO T15
SELECT
  ROWNUM,
  TO_CHAR(ROWNUM,'0000000')||'A',
  DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM),
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 10000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 1000) V2;

CREATE INDEX IND_T15_C1_C2 ON T15(C1,C2);
CREATE INDEX IND_T15_C3 ON T15(C3);

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

The above created the table T15, the composite index IND_T15_C1_C2 on the columns C1 and C2, and the index IND_T15_C3 on just the C3 column. The table is large enough due to column C4 that Oracle will probably select to use an index, when possible, rather than performing a full table scan. Now, let’s see what happens when we try to determine the number of rows in table T15:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

The optimizer selected to use the composite index IND_T15_C1_C2 rather than a full table scan or the much smaller index IND_T15_C3 on just the column C3. Let’s try a hint to use the index IND_T15_C3:

SELECT /*+ INDEX(T1 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

Oracle still used the IND_T15_C1_C2 index, even though I hinted to use the IND_T15_C3 index. But wait, there is a problem. The table is actually T15, not T1. I have included this example, as it is an easy mistake to make when typing SQL statements. Let’s try again with a correctly formed hint in the SQL statement:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

SET AUTOTRACE OFF

Oracle still ignored the hint to use the IND_T15_C3 index, and selected to use the IND_T15_C1_C2 index instead (again). Oh, Oracle does not index NULL values in a non-composite B*Tree index (or when alll values are NULL in a composite index), so using that index may yield the wrong result. We can fix that problem:

UPDATE
  T15
SET
  C3=0
WHERE
  C3 IS NULL;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T15',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

Let’s try again:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer did not select to use the IND_T15_C3 index, let’s help it with a hint:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer still ignored the index hint and used the larger index. Let’s see if we can help the optimizer by telling it that column C3 cannot hold a NULL value:

ALTER TABLE T15 MODIFY (C3 NUMBER NOT NULL);

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 877827156

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |  6700   (1)| 00:01:21 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |    10M|  6700   (1)| 00:01:21 |
----------------------------------------------------------------------------

Oracle finally used the correct smaller index, without needing a hint.

——

What else might cause Oracle’s optimizer to “ignore” a hint?  Last year Jonathan Lewis posed this question, with a lot of interesting responses showing possible reasons for the FULL hint to be ignored.  I supplied two test cases in the comments of that blog article, so I thought that I would reproduce those test cases here.

Test Case #1:

CREATE TABLE T2 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T2(C1);
CREATE UNIQUE INDEX T1_N2 ON T2(C1,C2);

ALTER TABLE T2 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

CREATE OR REPLACE VIEW T1 AS
SELECT /*+ INDEX(T2) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T2) T2;

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

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 1213398864

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   101 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_PK | 48000 |   101   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_PK;

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 824454759

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   134 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_N2 | 48000 |   134   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_N2;

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

Test Case #2:

In schema 1:

CREATE TABLE T1 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T1(C1);
CREATE UNIQUE INDEX T1_N2 ON T1(C1,C2);

ALTER TABLE T1 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

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

CREATE OR REPLACE VIEW T1_VIEW AS
SELECT /*+ INDEX_FFS(T1_V) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T1) T1_V;

CREATE OR REPLACE PUBLIC SYNONYM T1 FOR T1_VIEW;

GRANT SELECT ON T1_VIEW TO PUBLIC;
GRANT SELECT ON T1 TO PUBLIC;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 1018460547

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    29 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_PK | 48000 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------- 

In schema 1:

DROP INDEX T1_PK;

In schema 2:

select /*+ full(t) no_index(t) */ count(*) from t1 t;

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

Plan hash value: 177081169

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 48000 |    38   (0)| 00:00:01 |
-----------------------------------------------------------------------

In schema 1:

DROP INDEX T1_N2;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

Jonathan pointed out in his blog article that there is another very sensible reason for Oracle’s optimizer to “ignore” the FULL hint… the table was defined as an index organized table.





Windows as an OS Platform for Oracle Database – Where Do I Start?

13 07 2010

July 13, 2010 (Modified July 14, 2010, July 22, 2010, February 22, 2011)

I try to follow the discussions on the Oracle-L mailing list, even though I do not participate in that mailing list.  Just a couple of days ago I saw an interesting discussion thread in the group that forced me to think for a moment… maybe one should use a shared server configuration rather than a dedicated server configuration when Oracle Database is running on Windows XP?  Think about it for 10 seconds – why should someone do this, and why not?

While you think about it, let’s chain together a couple of forum threads to compile a collection of notes about the combination of Windows and Oracle Database.

I have worked with the Windows operating system a bit, having read the Windows 95 Resource Kit book cover to cover roughly 15 years ago (and a book about the Windows 95 registry from the same time period), the Windows Vista Resource Kit book a couple of years ago, and several books that described Microsoft’s server operating systems.  I have also posted a couple of responses to various forums, answering questions about the sometimes unexpected behavior of the Windows operating systems.

Take, for example, this thread where the original poster asked what are the advantages of running Oracle Database on a server edition of Windows compared to a desktop edition of Windows.  This is the response that I provided (the italics section should have been included in the original post):

There are a couple of important differences between the two operating systems:

  • Windows XP is limited to 10 simultaneous external connections. It is easy to reach that limit even when other computers are not attempting to remotely connect to the Windows XP computer. The System event log will display a message “TCP/IP has reached the security limit imposed on the number of concurrent TCP connect attempts.” when the 10 connection limit is attempted to be exceeded. No such limit exists on Windows 2003.
  • Memory handling algorithms are different between the two operating systems. There are memory limits in the operating systems that are independent of the amount of physical memory installed. For example, what do you do if the following message appears in the System event log: “The server was unable to allocate a work item 2 times in the last 60 seconds.” On Windows 2003, the problem can be fixed by creating a MaxWorkItems entry in the Windows registry.
  • The Home and Home Starter (I am not sure if that is the correct name) versions of Windows XP do not fully support Oracle Database, because those versions of the operating system do not permit user controlled/configurable local user groups, user permissions (logon as batch), nor file level permissions (file permissions/file ownership are there – they just cannot be modified through the user interface).
  • [Server version supports large memory pages, while the desktop version does not.]
  • Yes, there are other differences [between the server and desktop operating systems], even though the two operating systems share the same kernel. The R2 version of Windows 2003 is preferred over the original release.

With the above bullet points in mind, how would you answer the original poster in the Oracle-L discussion thread (incidentally, Windows 95 did not impose the 10 simultaneous external connection limit)?

Let’s take a look at memory, since it was mentioned in the above quote.  From another OTN thread that I participated in:

> I have a vendor recommending shared server due to a windows thread limitation.  Upon reading up on windows threads, it seems to be more an issue of an application not properly managing memory withing the memory stack.
> Does anyone know of a way to monitor thread utilization on a per session basis?
> Has anyone ever seen an Oracle based application hit a windows thread limit?
> Thanks in advance.
—————-

A quick Google search finds this link that suggests that 500,000 threads are possible, but the author was only able to reach 13,000 threads:
http://blogs.msdn.com/oldnewthing/archive/2005/07/29/444912.aspx
32 bit Windows: “Because the default stack size assigned by the linker is 1MB, and 2000 stacks times 1MB per stack equals around 2GB, which is how much address space is available to user-mode programs.”

Metalink Note:46001.1: “Oracle Database and the Windows NT memory architecture, Technical Bulletin” confirms that by default Oracle allocates 1MB per thread. The number of possible threads is thus limited by either the maximum per process limit for memory, or the combination of system RAM and swap space, with each session requiring a minimum of 1MB just to connect.

The number of threads can be viewed in Task Manager on the server – it is necessary to manually add the “Thread Count” column to Task Manager to see the current number of threads in a process. There is typically one thread per session, plus several background threads.

Niall Litchfield, who authored the chapter Running Oracle on Windows in the “Expert Oracle Practices” book provided a brief follow-up to my post in that thread.

Continuing with the discussion of memory on Windows, another one of my posts on the OTN forums mentioned the following:

The Standard Edition of 64 bit Windows 2003/2008 Server is artifically limited to 32GB in a flat memory model. The Enterprise Edition of 64 bit Windows 2003/2008 Server supports more memory than you can physically connect to the server’s motherboard. 64 bit Oracle Database Standard Edition One and greater is able to take advantage all of the memory in the server that is provided within the flat memory model without any special configuration – other than enabling large page support to decrease the overhead of managing a lot of memory.

The problem that you are encountering with 32 bit Windows is a limitation of 32 bit software – it can only address 2^32 GB of memory – that is 4GB. To access more than 4GB, a memory region is set aside to act as a translation window to peek into the upper memory region which can contain only the Oracle buffer cache. Articles on Metalink describe how this works. The 64 bit flat memory model does not have this limitation. With 64GB in the server [on a 64 bit operating system], you could specify a 32GB SGA_TARGET and 20GB PGA_AGGREGATE_TARGET, if you so desire.

Incidentally, the desktop 64 bit Windows operating systems do not impose the 32GB limit like the 64 bit Standard Edition of the server operating systems.

If you have run Oracle Database Database 10.1 or higher on Windows you might have seen a warning in Enterprise Manager stating that significant virtual memory paging is happening in the server.  There have been several threads on the OTN and other forums that mentioned this particular error message.  One of the threads that I participated in that is found on the OTN forums mentioned the following and included a link to one of my Usenet posts on the same topic:

The message may be a false alarm – even checking task manager on Windows 2003 may be misleading. On Windows 2003, the “PF Usage” statistic in task manager is not page file usage, but instead total virtual memory usage, which includes RAM and page file. Oracle Enterprise Manager, I believe, is showing the “PF Usage” statistic, and is making the assumption that this is page file usage.
For additional analysis see the discussion in this link:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/b62c4d761cace2a6

If I recall correctly, enabling large page support on Windows causes an incorrect amount of memory to be reported in Task Manager and by the TASKLIST command executed at the Windows command line.  However, enabling large page support may reduce kernel (system) mode CPU usage and the amount of memory needed for managing memory, so it might make sense to enable large page support.  I described the process in an OTN thread a couple of years ago:

Below are the steps that I used to enable large page support for 64 bit Windows.
Set up for Large Page Support (cannot use LOCK_SGA in init.ora/spfile):

  1. Choose Start > Settings > Control Panel
  2. Double-click Administrative Tools.
  3. Double-click Local Security Policy.
  4. Expand Local Policies and select User Rights Assignment.
  5. Select Lock pages in memory and choose Action > Security…
  6. Click Add…
  7. Select the username that was used to install Oracle and the ora_dba group from the Name list.
  8. Click Add.
  9. Click OK to close the Select Users or Groups dialog.
  10. Click OK to close the Local Security Policy Setting dialog.
  11. Open Regedit
  12. Navigate to:
    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
  13. Create a registry key (REG_SZ) named ORA_LPENABLE and set the value to 1
  14. Reboot the server.

Tuning Windows for Oracle… I might have replied to a couple of forum threads over the years in this topic.  One of those threads is this one, where I unfortunately had to correct a couple of suggestions that were quoted from another resource (just another reason why one should not blindly copy the work of others).

A couple notes about the above setting suggestions:

  • I believe that you will find that NtfsDisable8dot3NameCreation is already set to 1 on 64 bit Windows server operating systems. The reasoning is that only 16 bit programs need the 8.3 names, and 64 bit Windows cannot run 16 bit applications.
  • LargeSystemCache = 1 – this does not look correct, see:
    http://technet.microsoft.com/en-us/library/cc784562(WS.10).aspx

I suggest taking a look at the following document:
http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document)
Do not follow the directions related to TcpAckFrequency in the above document [think 45 minutes to transfer a file over a gigabit connection when the transfer should have completed in three to four seconds].

A couple other documents which you might find helpful (pick the version specific for your release of Oracle):
http://download.oracle.com/docs/html/B13831_01/tuning.htm (edit: the documentation site appears to be down at the moment, this link applies to Oracle Database 11.2.0.1 and Windows server operating systems and will be accessible when the documenation site comes back online – in the mean time the Google cache version)
http://download.oracle.com/docs/html/B13831_01/architec.htm

Tuning, of course, leads to questions, “Is Oracle Slower on Windows than on Linux”  as asked in another OTN thread.  I provided a couple of comments in that thread, with the following as the most relevant to this blog article:

There are a lot of factors which may have contributed to the slower than expected performance on Windows 2003 that you noticed. Those factors include:

  • Not collecting system (CPU) statistics on Oracle 9i or higher
  • Not collecting data dictionary statistics on Oracle 9i or higher
  • Not configuring large page support, if it is able to be used
  • Not taking advantage of a battery backed cache to improve write performance
  • Leaving the default db_file_multiblock_read_count set to 16 (I believe that is what the DBCA sets on 10g and above) rather than allowing Oracle to auto-tune the parameter. Limiting extent sizes to smaller than 1MB could also cause performance problems.
  • Installing a virus scanner on the server, especially if it is permitted to scan program and data files used by Oracle.
  • Using inappropriate parameters for memory allocations.
  • If a database release upgrade was involved, not directly attacking the specific performance problem which is the source of the problem – could just be just a couple bad execution plans, possibly caused by the upgraded query optimizer.
  • … (Niall Litchfield, or someone else might be able to provide additional causes)

A couple things to keep in mind about Windows:

  • Oracle on Windows uses direct, asynchronous I/O, which helps minimize concurrency problems. But, this also means that the operating system’s file cache probably is of little use to Oracle on Windows.
  • Oracle on Windows uses a thread model, while Oracle on Linux (and other Unix like operating systems) uses a process model. The thread model is less negatively impacted by context switches than is the process model, which should help improve performance in some cases, such as repeatedly calling a PL/SQL function from a SQL statement.
  • Windows uses a single CPU run queue, while the Linux 2.6 and later kernel uses a separate run queue for each CPU. That means that processes (and threads) tend to float between CPUs more frequently on Windows than on Linux, which may reduce the benefit of large L1 and L2 caches built into the CPUs on Windows. The Linux 2.6 kernel is also able to automatically throttle very CPU intensive processes, which might provide a little more headroom for multiple concurrent Oracle sessions.
  • There is a paper on Microsoft’s website titled “Performance Tuning Guidelines for Windows Server 2003″ http://download.microsoft.com/download/2/8/0/2800a518-7ac6-4aac-bd85-74d2c52e1ec6/tuning.doc (edit Feb 22, 2011: note that the preceding link is for Windows 2003, use this link for the Windows 2008 version of the document) – take a look at that document, but do not implement the suggested TcpAckFrequency Windows registry modification.
  • Try to minimize the amount of memory used by the operating system for file caching.
  • Properly configured, the same server running 64 bit Windows should be able to achieve roughly the same performance as the same server running 64 bit Linux (assuming direct, asynchronous I/O is enabled on Linux).
  • … (Niall Litchfield, or someone else might be able to provide additional things to keep in mind)

The general advice that you will likely receive is to use the operating system that you know best for Oracle.

Niall Litchfield as well as several others provided responses in the above OTN thread – it is an interesting read.

—–

Late addition July 22, 2010:

Another OTN forum thread that I recently participated in:

When I tested it [Windows XP Home running Oracle Database] a while ago (maybe a year or two in the past) certain features did not work.  Because there are certain Oracle Database features that require the ORA_DBA security group and the Local Security Policy control panel, neither of which exist in Windows XP Home.  If you are able to get Oracle Database installed and a database created (I was able to do both), you might encounter the limitations or you might not depending on which features you use.  This is what I recall from the testing:

  • “CONNECT / AS SYSDBA” will not work – the logged in user must be in the ORA_DBA group which does not exist
  • Datapump import/export does not work.
  • Cannot schedule jobs using Enterprise Manager Database Control – the OS user specfiied must have the LOGON AS BATCH permission which is assigned in the Local Security Policy control panel (I experimented with borrowing this control panel item from XP Pro but it refused to run on XP Home).
  • RMAN from the command line works to backup and restore databases, but of course it is not possible to schedule the RMAN backups using Enterprise Manager Database Control.
  • File level permissions cannot be adjusted without removing the hard drive and installing it as a secondary drive in a Windows XP Pro (or higher) computer, at which point the user logged into the Windows XP Pro computer will need to take ownership of the files before modifying file permissions.

I suspect that there might be problems with UTL_FILE, BFILE datatypes, and some of the other features.

I did not perform much testing.  When one considers the time involvement, the Windows Anytime upgrade that I mentioned earlier is the least expensive solution.

—–

Back to the Oracle-L thread… shared or dedicated?





Oracle Statistics Chart Viewer

11 07 2010

July 11, 2010

Last week I provided a blog article that created a horizonal orientation auto-scaling HTML-based bar chart using nothing more than a SQL statement.  At first glace the final SQL statement would likely appear to be very complicated, however if you follow the blog article from the start, the solution really is quite straightforward.  Today’s blog article will build on the final result of last week’s blog article, however the path to the final solution will be very different, and multiple vertical orientation bar charts will appear on a single web page.

The inspiration for today’s blog article comes from the Real Time Monitor in my Toy Project for Performance Tuning:

Since this project is a bit more advanced than last week’s project, we will use a programming language to perform the calculations and write out the HTML directly to a web browser.  We will use VBScript, which is available on most computers running Windows, so that will make it easy for you to modify the script for your needs:

  • Changing the statistics to be displayed
  • Changing the sampling frequency 
  • Changing the number of charts displayed per row and the number of rows
  • Changing the spacing between the charts
  • Changing the bar colors
  • Changing the background color
  • Adding grid lines

The actual script may be a little difficult to understand.  Basically, the script queries several views, displays the historical delta values of the specified statistics in chart form, and then waits for the sampling frequency counter to tick down before collecting the next set of statistics.  The statistics collection process ends when the user clicks the Close button.  The following views are queried every time the statistics are gathered:

  • V$OSSTAT
  • V$SYS_TIME_MODEL
  • V$WAITSTAT
  • V$SYSSTAT
  • V$SYSTEM_EVENT

The statistic names from some of the views are prefixed to reduce the chances of name collisions between statistics from the different views.  For example, the statistics from V$SYS_TIME_MODEL are prefixed with “SYS TIME MODEL ” and the statistics from V$WAITSTAT are prefixed with “WAITSTAT TIME “.  The current value is displayed in the left-most bar, with up to the previous 29 values displayed in the remaining bars.

Sample Charts:

Flat appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

-

Flat appearance with six horizontal and four vertical bar charts with the refresh interval set at 10 seconds:

-

3D appearance with three horizontal and two vertical bar charts with the refresh interval set at 10 seconds:

—–

So, what changes do you need to make to use the script?  First, if you are running a release of Oracle Database prior to 10.1 you will need to remove the queries of V$SYS_TIME_MODEL and V$OSSTAT.  Second, you need to have an Oracle user other than a SYSDBA user (SYS) that is able to query the various performance views.  Third, you need to specify the username and password of that user in the script:

'Database configuration
strUsername = "MyUsername"
strPassword = "MyPassword"
strDatabase = "MyDB"

Next, decide which statistics you would like to display in the charts and in what order (sequenced across the page and then down the page).  The statistic names are all recorded in uppercase, with the following as defaults in the script:

'The statistics to be displayed in the charts
strStatToChart(1) = "BUSY_TIME"
strStatToChart(2) = "CPU USED BY THIS SESSION"
strStatToChart(3) = "USER CALLS"
strStatToChart(4) = "SQL*NET ROUNDTRIPS TO/FROM CLIENT"
strStatToChart(5) = "USER COMMITS"
strStatToChart(6) = "USER ROLLBACKS"
strStatToChart(7) = "TRANSACTION ROLLBACKS"
strStatToChart(8) = "DB FILE SEQUENTIAL READ"
strStatToChart(9) = "DB FILE SCATTERED READ"
strStatToChart(10) = "READ BY OTHER SESSION"
strStatToChart(11) = "SYS TIME MODEL DB TIME"
strStatToChart(12) = "SYS TIME MODEL DB CPU"
strStatToChart(13) = "SYS TIME MODEL BACKGROUND CPU TIME"
strStatToChart(14) = "PARSE TIME CPU"
strStatToChart(15) = "RECURSIVE CPU USAGE"
strStatToChart(16) = "CONSISTENT GETS"
strStatToChart(17) = "DB BLOCK GETS"
strStatToChart(18) = "PHYSICAL READS CACHE"
strStatToChart(19) = "DATA BLOCKS CONSISTENT READS - UNDO RECORDS APPLIED"
strStatToChart(20) = "REDO SIZE"
strStatToChart(21) = "REDO WRITE TIME"
strStatToChart(30) = "WAITSTAT TIME DATA BLOCK"

Next, decide how the charts should appear on the page.  For example, for six charts, you might decide to use the following settings (the defaults)

'Large Charts
intChartsHorizontal = 3
intChartsVertical = 2
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 50
lngChartSpacingY = 50
lngChartWidth = 300
lngChartHeight = 300
strBar3DEffect = "border-style:outset;border-width:thin;"

For 24 charts, you might use the following settings (the defaults):

'Small Charts
intChartsHorizontal = 6
intChartsVertical = 4
lngLeftMargin = 50
lngTopMargin = 50
lngChartSpacingX = 10
lngChartSpacingY = 20
lngChartWidth = 150
lngChartHeight = 150
strBar3DEffect = "border-style:none;border-width:inherit;"

Finally, decide the color scheme of the charts, the update frequency, and whether delta values or absolute values should be displayed.  The following are the defaults:

strBarColorStandard = "#AAAAAA"
strBarColorCurrent = "#1010FF"
strBarTextColor = "#000000"
strChartBackgroundColor = "#EEEEEE"
strChart3DEffect = "border-style:inset;"
intShowDelta = True
intRefreshSeconds = 60 'Refresh interval of the chart data

I have found the book “CSS: The Definitive Guide” to be very helpful when adjusting the various appearance options.

Please post any improvements that you make to the script.  It might be interesting to note that this code can be incorporated into the Database Inspector Gadget that I created a while ago if the computer is running Windows Vista or Windows 7.

Script download: OracleStatisticChartViewer.vbs (save as OracleStatisticChartViewer.vbs)

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

Partially related side note: There are still a couple of seats remaining for the Michigan OakTable Symposium, which will certainly have the most entertaining and in-depth training sessions for DBAs and developers in 2010.  There are 20+ OakTable Network members from around the world converging on Ann Arbor, MI. in mid-September 2010.  I will be in attendance, but not presenting (Randolf Geist, who co-wrote two chapters in the Expert Oracle Practices book with me is flying in from Germany to present two sessions).  More information may be found here: Michigan OakTable Symposium.





Create an Auto-Scaling HTML Chart using Only SQL

8 07 2010

July 8, 2010 (Modified July 9, 2010)

I thought that I would try something a little different today – build an auto-scaling HTML bar chart using nothing more than a SQL statement.  I mentioned in this book review that I was impressed with the HTML chart that was included in the book, but I felt that it might be more interesting if the example used absolute positioning, rather than an HTML table.  So, I built an example using dynamic positioning that is not based on what appears in that book.

We will use the sample table from this blog article (side note: this is an interesting article that shows how a VBS script can generate a 10046 trace file, and then transform that trace file back into a VBS script), just with the table renamed to T1.

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

COMMIT;

Now that we have 1000 rows in the sample table, let’s see how many entries fall into each week in the table (the week starts on a Monday) for those indirect entries that are either VAC, ABS, or EXCUSE:

SELECT
  NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
  COUNT(*) IND
FROM
  T1
WHERE
  INDIRECT_ID IN ('VAC','ABS','EXCUSE')
GROUP BY
  NEXT_DAY(SHIFT_DATE,'MONDAY')-7
ORDER BY
  1;

Your results of course will be different from what follows due to the randomization of the data, but this is what was returned from my database:

WEEK_OF   IND
--------- ---
08-OCT-07   1
15-OCT-07   2
22-OCT-07   4
29-OCT-07   3
05-NOV-07   3
03-DEC-07   2
10-DEC-07   3
24-DEC-07   2
...
05-JAN-09   1
12-JAN-09   3
19-JAN-09   7
02-FEB-09   1
...
21-JUN-10   3
28-JUN-10   2
05-JUL-10   2

The above SQL statement should work for the base query, now we need to start manipulating the data so that we are able to calculate the size and location of the bars in the chart.  We will slide the above SQL statement into an inline view:

SELECT
  WEEK_OF,
  IND,
  MAX(IND) OVER () MAX_IND,
  COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
  ROWNUM RN
FROM
  (SELECT
    NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
    COUNT(*) IND
  FROM
    T1
  WHERE
    INDIRECT_ID IN ('VAC','ABS','EXCUSE')
  GROUP BY
    NEXT_DAY(SHIFT_DATE,'MONDAY')-7
  ORDER BY
    1);

In addition to returning the original data from the SQL statement, we are now also returning the maximum data value, the total number of weeks with at least one entry, and a row counter:

WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN
--------- --- ------- ------------- -----
08-OCT-07   1       7           126     1
15-OCT-07   2       7           126     2
22-OCT-07   4       7           126     3
29-OCT-07   3       7           126     4
05-NOV-07   3       7           126     5
03-DEC-07   2       7           126     6
10-DEC-07   3       7           126     7
...
05-JAN-09   1       7           126    57
12-JAN-09   3       7           126    58
19-JAN-09   7       7           126    59
02-FEB-09   1       7           126    60
...
14-JUN-10   2       7           126   123
21-JUN-10   3       7           126   124
28-JUN-10   2       7           126   125
05-JUL-10   2       7           126   126

Next, we need to calculate the position and size of each of the bars in the chart, so we will again slide the above into an inline view:

SELECT
  WEEK_OF,
  IND,
  MAX_IND,
  COUNT_WEEK_OF,
  RN,
  TRUNC(300 * IND/MAX_IND) BAR_WIDTH,
  TRUNC(800 * 1/COUNT_WEEK_OF) BAR_HEIGHT,
  TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1)) BAR_TOP,
  100 BAR_LEFT
FROM
  (SELECT
    WEEK_OF,
    IND,
    MAX(IND) OVER () MAX_IND,
    COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
    ROWNUM RN
  FROM
    (SELECT
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
      COUNT(*) IND
    FROM
      T1
    WHERE
      INDIRECT_ID IN ('VAC','ABS','EXCUSE')
    GROUP BY
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7
    ORDER BY
      1));

You might notice in the above that I specified that the maximum width of the chart will be 300 (pixels) and the maximum height will be 800 (pixels).  Here is the output:

WEEK_OF   IND MAX_IND COUNT_WEEK_OF    RN  BAR_WIDTH BAR_HEIGHT BAR_TOP BAR_LEFT
--------- --- ------- ------------- ----- ---------- ---------- ------- --------
08-OCT-07   1       7           126     1         42          6       0      100
15-OCT-07   2       7           126     2         85          6       6      100
22-OCT-07   4       7           126     3        171          6      12      100
29-OCT-07   3       7           126     4        128          6      19      100
05-NOV-07   3       7           126     5        128          6      25      100
03-DEC-07   2       7           126     6         85          6      31      100
10-DEC-07   3       7           126     7        128          6      38      100
24-DEC-07   2       7           126     8         85          6      44      100
...
05-JAN-09   1       7           126    57         42          6     355      100
12-JAN-09   3       7           126    58        128          6     361      100
19-JAN-09   7       7           126    59        300          6     368      100
02-FEB-09   1       7           126    60         42          6     374      100
...
14-JUN-10   2       7           126   123         85          6     774      100
21-JUN-10   3       7           126   124        128          6     780      100
28-JUN-10   2       7           126   125         85          6     787      100
05-JUL-10   2       7           126   126         85          6     793      100

Now what?  We need to convert the above into HTML using DIV tags to position the bars as calculated.  Prior to the first row we need to write a couple of HTML tags to set the page title, and after the last row we need to write a couple more HTML tags to close the BODY and HTML section of the document.  The transformed SQL statement looks like this:

SET TRIMSPOOL ON
SET LINESIZE 400
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET PAGESIZE 0
SET SQLPROMPT ''

SPOOL C:\CUSTOM_CHART.HTM

SELECT
  DECODE(RN,1,'<html><head><title>Custom Chart</title></head><body>' || CHR(13) || CHR(10),' ') ||
  '<div style="position:absolute;' ||
    'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
    'left:' || TO_CHAR(5) || 'px;' ||
    'width:' || TO_CHAR(100) || 'px;' ||
    'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
    '"><font size="1px" color="#0000FF">' || TO_CHAR(WEEK_OF,'MM/DD/YY') ||
      REPLACE('     ',' ',CHR(38) || 'nbsp;') || TO_CHAR(IND) || '</font></div>' ||
  '<div style="background:#444466;position:absolute;' ||
    'top:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF * (RN-1))) || 'px;' ||
    'left:' || TO_CHAR(100) || 'px;' ||
    'width:' || TO_CHAR(TRUNC(300 * IND/MAX_IND)) || 'px;' ||
    'height:' || TO_CHAR(TRUNC(800 * 1/COUNT_WEEK_OF)) || 'px;' ||
    '"><font size="1px" color="#FFFFFF"></font></div>' ||
  DECODE(RN,COUNT_WEEK_OF, CHR(13) || CHR(10) || '</body></html>',' ') HTML_LINE
FROM
  (SELECT
    WEEK_OF,
    IND,
    MAX(IND) OVER () MAX_IND,
    COUNT(WEEK_OF) OVER () COUNT_WEEK_OF,
    ROWNUM RN
  FROM
    (SELECT
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7 WEEK_OF,
      COUNT(*) IND
    FROM
      T1
    WHERE
      INDIRECT_ID IN ('VAC','ABS','EXCUSE')
    GROUP BY
      NEXT_DAY(SHIFT_DATE,'MONDAY')-7
    ORDER BY
      1));

SPOOL OFF

There is a slight problem with the above, the SQL statement and SPOOL OFF are printed in the resulting HTML file – if someone knows how to avoid that behavior (without placing the above into another script file), I would like to see how it is done (Oracle’s documentation did not help).

This is what the resulting HTML file looks like:

The number of result rows from the query was a bit high (126) so the bars are significantly compressed in height.  Just to see what happens, let’s add the following to the WHERE clause in the inner-most inline view:

AND SHIFT_DATE >= TO_DATE('01-JAN-2010','DD-MON-YYYY')

The resulting chart now looks like this:

Of course it is possible to adjust the colors of the font (#0000FF) and the bars (#444466), which are specified in hex in the format of RRGGBB (red green blue).  It is also possible to adjust the color of the bars to reflect the value represented by the bar, but that is an exercise for the reader.  For those who need to feel creative, it is also possible to display pictures in the bars, but that is also an exercise left for the reader.

—-

Edit: The sample output from the SQL statement displays correctly on Red Hat Enterprise Linux 3 using Firefox 0.8:








Follow

Get every new post delivered to your Inbox.

Join 144 other followers